Magical FetchMode property

What does the FetchMode property on a query do? I will appreciate if someone will be able to give me very precise answer.
I know only implication of this parameter. It matters when exist/notexist joined datasources on the query are forming a tree - not a chain. If the tree of datasources is quite complex then your query has a very good chance not to work if FetchMode is not set to One2One.
To my knowledge it is related somehow to the way the kernel stores query datasources.
So, my rule of thumb is the following: if a query datasources form tree-like structure, FetchMode must be set to One2One everywhere exist/notexist join is used.

Example:
Query query;
QueryBuildDataSource qbdsInventTable;
QueryBuildDataSource qbdsInventItemGroup;
QueryBuildDataSource qbdsInventModelGroup;
;

query = new Query();
qbdsInventTable = query.addDataSource(tablenum(InventTable));

qbdsInventItemGroup = qbdsInventTable.addDataSource(tablenum(InventItemGroup));
qbdsInventItemGroup.relations(true);
qbdsInventItemGroup.joinMode(JoinMode::ExistsJoin);
qbdsInventItemGroup.fetchMode(QueryFetchMode::One2One);

qbdsInventModelGroup = qbdsInventTable.addDataSource(tablenum(InventModelGroup));
qbdsInventModelGroup.relations(true);
qbdsInventModelGroup.joinMode(JoinMode::NoExistsJoin);
qbdsInventModelGroup.fetchMode(QueryFetchMode::One2One);

Union queries

In AX 2009 a great enhancement was introduced to the queries – support for union operation was added. A new property was added to the AOT queries – QueryType. It has two values:

• Join – regular query


• Union – union query

In the case of union query several datasources of the same type (the same table) can be placed on the query datasource root node. Note, that all datasources except the first one will have a property called UnionType, which specifies what should be done with duplicated records that may appear because of union:

• Union – remove duplicated records


• UnionAll – keep duplicated records

Additional datasources can be added to the deeper levels of the query under any datasource that participates in union. Those will be translated into joins. The only allowed join types for the union query are exists and notexists join.

Query.pack method parameter

The comment from here forced me to pay attention to the Boolean parameter on the Query.pack() method which I didn't mention before. It is not obvious what it does by looking at its name - _doCheck. I tried to figure out what it does and I've got the following:
If a query contains DynaLink then calling Query.pack(true) will throw the error: Attempting to pack a query that contains one or more dynamic links.
If a query contains DynaLink then calling Query.pack(false) will succeed and DynaLink will not be stored in the package - it will not appear on a new query created from the package.

Does anybody have more information on this parameter?

Example:
public static void testQueryPack()
{
    InventItemGroup itemGroup;
    Query q1 = new Query();
    Query q2;
    QueryBuildDataSource qbds;

    qbds = q1.addDataSource(tablenum(InventTable));
    qbds.addDynalink(fieldnum(InventTable, ItemGroupId), itemGroup, fieldnum(InventItemGroup, ItemGroupId));
    info(qbds.toString());

    q2 = new Query(q1.pack(false));
    qbds = q2.dataSourceTable(tablenum(InventTable));
    info(qbds.toString());
}
Result:

Instantiating query object

Constructor of the Query class has default anytype parameter named _source. To my knowledge there are four different ways to use it:

1. Instantiating an empty query – no parameter value should be specified:
   Query q = new Query();


2. Instantiating a query based on the AOT query:
   Query q = new Query(querystr(Alertsetup));


3. Instantiating a query as the copy of another query:
   Query q1 = new Query();
   Query q2 = new Query(q1);


4. Instantiating a query from the container with the packed query
   Query q1 = new Query();
   Query q2 = new Query(q1.pack());

Is there anything else that can be used as a source for constructing a query?

Global::queryValue method

This method translates anything into the correct query range value, so you don't have to care about how UTCDateTime value should be converted into a string or which symbols require slash before them. It is the best practice to always use this method when query range is assigned a value, even if the value is string.

Note, that queryValue('') will return the following string '""' (two double quotes) which will give empty string range value. If you want to clear the range you'll have to assign it's value with empty string directly.

, and ,! operators in query ranges

If a query range value is "A,B" it will be translated into the SQL expression (ItemId='A') OR (ItemId='B'), so both items with ID A and B will be selected. If a query range is "!A" than it will be translated into SQL expression NOT (ItemId='A'), so items with ID not equal to A will be selected. But, if a query range is "A,!B" it will be translated not into (ItemId='A') OR NOT (ItemId='B') as it seems to be correct. It will be translated into (ItemId='A') AND NOT (ItemId='B'). Despite that such behavior looks strange it seems to be a feature, not a bug. So it is just better to understand "," and ",!" as different operators - OR and AND NOT.

SysQueryRangeUtil class

The SysQueryRangeUtil class is just the great enhancement to the AOT queries made in AX 2009. It is possible now to specify a range on the AOT query with the value taken from a method's return. The requirements for such range method are quite simple: the method should be static, return string, be a member of the SysQueryRangeUtil class and not be related on the data fetched by the query.

There are quite a lot of useful methods on the SysQueryRangeUtil class available out of the box, like currentEmployeeId() - returns current employee ID, dayRange() - allows to specify date interval around current date, etc. And it is possible to add a new custom method to this class as well.

Indexes and data fetching

Indexes can speed up not only record lookup but data fetching too. If all fields that participate in a query are present in the index, database will not start retrieving actual table data but will use index values only. It is quite important factor to consider during table’s indexes tuning.
Example:


select maxof(Quantity) from table1
    where  table1.Key >= 100000
        && table1.Key <= 300000;

The query above runs around 320 ms on my machine with 1M records in Table1 when index on the Table1 contains only Key field. If Quantity field is added to the index execution time drops to 70 ms.

Note: It is important to mention that the difference appears only if lookup happens using non-cluster index. Otherwise query execution time will be almost the same because cluster index is stored together with table data and it is cheap to retrieve it.

Intrinsic functions in queries

The following code won't compile in AX.

select extCodeTable
    where extCodeTable.ExtCodeTableId == tablenum(CompanyInfo)
    join extCodeValueTable
        where extCodeValueTable.ExtCodeId == extCodeTable.ExtCodeId;

The reason for that is the call to tablenum(CompanyInfo) - compiler doesn't not allow it inside queries. The simplest workaround is to define a variable, assign it with tablenum(CompanyInfo) value and use it in the query.