Non-documented feature of Dynamic property on query datasource Fields

While analysing performance issues I faced non-documented feature of Dynamic property on query datasource Fields. I made simplified and theoretical example to demonstrate problem and solution.

Problem description
More information than expected is returned form database when Fields node on query datasource has no field and Dynamic property is set to No.

Steps to reproduce
1. Create a new query in AOT like on screenshot below:

2. Create a test job to get query statement in AX and to invoke SQL statement

static void CustTableBankAccountCountry(Args _args) { Query q = new Query(queryStr(CustTableBankAccountCountry)); QueryRun qr; info (q.dataSourceNo(1).toString()); qr = new QueryRun(q); qr.next(); }

3. Setup SQL Server Profiler to catch test statement
Note: for test it is enough to trace only SP:SmtCompleted event and filter results by CustTable (I plan to write a separate post on SQL Server Profiler hints for Dynamics AX tracing)

4. Run test job

5. Compare query statement in AX with actual statement in SQL
The following statement is built in AX:

SELECT AccountNum FROM CustTable(CustTable) OUTER JOIN FROM CustBankAccount(CustBankAccount) ON CustTable.AccountNum = CustBankAccount.CustAccount OUTER JOIN CountryRegionId FROM LogisticsPostalAddress(LogisticsPostalAddress) ON CustBankAccount.Location = LogisticsPostalAddress.Location

The following statement is traced in SQL:

SELECT T1.ACCOUNTNUM,T1.RECID, T2.ACCOUNTID,T2.CUSTDOMNUMBER_BE,T2.NAME,T2.ACCOUNTNUM,T2.BANKCIN, T2.BANKACCOUNTTYPE,T2.LOCATION,T2.CURRENCYCODE,T2.REGISTRATIONNUM, T2.PHONE,T2.TELEFAX,T2.CONTACTPERSON,T2.EMAIL,T2.URL,T2.TELEX, T2.CELLULARPHONE,T2.PHONELOCAL,T2.CUSTACCOUNT,T2.BANKGROUPID, T2.CUSTPAYMENTTEXTCODE,T2.SWIFTNO,T2.MSGTOBANK,T2.EXCHRATE, T2.EXCHRATEREF,T2.BANKCONTRACTACCOUNT,T2.PAGER,T2.SMS,T2.BANKCODETYPE, T2.BANKIBAN,T2.CORRESPONDENTBANKNAME_LT,T2.CORRESPONDENTBANKADDRESS_LT, T2.CORRESPONDENTBANKSWIFT_LT,T2.INTERMEDIATEBANKNAME_LT, T2.INTERMEDIATEBANKADDRESS_LT,T2.INTERMEDIATEBANKSWIFT_LT, T2.CORRESPONDENTBANKACCOUNT_LT,T2.INTERMEDIATEBANKACCOUNT_LT, T2.FOREIGNBANK_RU,T2.FOREIGNACCOUNT_RU,T2.FOREIGNSWIFT_RU, T2.BANKSORTCODE_RU,T2.CORRACCOUNT_W,T2.LVDEFAULTBANK,T2.BANKCONSTANTSYMBOL, T2.SPECIFICSYMBOL,T2.RECVERSION,T2.PARTITION,T2.RECID, T3.COUNTRYREGIONID,T3.RECID FROM CUSTTABLE T1 LEFT OUTER JOIN CUSTBANKACCOUNT T2 ON (((T2.PARTITION=@P1) AND (T2.DATAAREAID=@P2)) AND (T1.ACCOUNTNUM=T2.CUSTACCOUNT)) LEFT OUTER JOIN LOGISTICSPOSTALADDRESS T3 ON ((T3.PARTITION=@P3) AND ((T2.LOCATION=T3.LOCATION) AND ((T3.VALIDFROM<=@P4) AND (T3.VALIDTO>=@P5)))) WHERE ((T1.PARTITION=@P6) AND (T1.DATAAREAID=@P7)) ORDER BY T1.ACCOUNTNUM

No field is expected to be selected from CustBankAccount table based on query definition. Query statement in AX proves it, but actual SQL statement selects and returns all fields from CustBankAccount. As no field is actually required from CustBankAccount table, such useless select should be omitted.

Hints
Try to add any field into Fields node.

Solution
1. Add TableId field to Fields node

2. Run test job

3. Compare statement traced in SQL

SELECT T1.ACCOUNTNUM,T1.RECID, T3.COUNTRYREGIONID,T3.RECID FROM CUSTTABLE T1 ...

Only expected fields are selected. As TableId is not a field in SQL database, nothing is selected from CustBankAccount table.

Table inheritance functions differently.

1. Crete a new query like on screenshot below:

2. Modify test job to use the new query

3. Modify SQL Server Profiler setup to catch EcoResProduct table

4. Run test job

5. Compare results
The following statement is built in AX:

SELECT DisplayProductNumber, ProductType FROM EcoResProduct(EcoResProduct) OUTER JOIN TableId FROM EcoResProductInstanceValue(EcoResProductInstanceValue) ON EcoResProduct.RecId = EcoResProductInstanceValue.Product JOIN Attribute FROM EcoResAttributeValue(EcoResAttributeValue) ON EcoResInstanceValue.RecId = EcoResAttributeValue.InstanceValue

The following statement is traced in SQL:

SELECTT1.DISPLAYPRODUCTNUMBER,T1.PRODUCTTYPE,T1.RECID,T1.INSTANCERELATIONTYPE, T2.INSTANCERELATIONTYPE,T2.RECID,T2.RECID, T3.ATTRIBUTE,T3.RECID FROM ECORESPRODUCT T1 LEFT OUTER JOIN ECORESINSTANCEVALUE T2 ON (((T2.PARTITION=@P1) AND (T1.RECID=T2.PRODUCT)) AND (T2.INSTANCERELATIONTYPE IN (4349) )) CROSS JOIN ECORESATTRIBUTEVALUE T3 WHERE (T1.PARTITION=@P2) AND ((T3.PARTITION=@P3) AND (T3.INSTANCEVALUE=T2.RECID)) ORDER BY T1.DISPLAYPRODUCTNUMBER

InstanceRelationType field, which is critical for table inheritance, is selected as well as RecId. The second RecId most probably is TableId field requested in query (I assume it is a bug in standard AX).

Find code examples in xpo attached.