Comparisons...

jan larsen

Found an interesting feature in Microsoft Dynamics Ax 2012 R2:

static void Job40(Args _args)
{
smmBusRelTable bus;
int i;

select count(RecId) from bus where
    bus.BusRelAccount == bus.TopLevelAccount
;

info(strFmt("From count: %1", bus.RecId));

i = 0;
while select bus where
    bus.BusRelAccount == bus.TopLevelAccount
{
    i++;
}

info(strFmt("bus.BusRelAccount == bus.TopLevelAccount: %1", i));

i = 0;
while select bus where
    bus.TopLevelAccount == bus.BusRelAccount
{
    i++;
}

info(strFmt("bus.TopLevelAccount == bus.BusRelAccount: %1", i));

}

Running the job results in this output:

From count: 31597
bus.BusRelAccount == bus.TopLevelAccount: 1
bus.TopLevelAccount == bus.BusRelAccount: 31597

:omg:

"God doesn't play dice" - Albert Einstein "God not only plays dice, He sometimes throws the dices where they cannot be seen" - Niels Bohr

Bernhard Hiller

A badly designed operator override? At least fun to see that (bus.BusRelAccount == bus.TopLevelAccount) != (bus.TopLevelAccount == bus.BusRelAccount)

jan larsen

I wish it was that easy. But X++ does not have overrides, the Dynamics Ax VM creates SQL statements from this code. By tracing statements in the SQL Server I may be able to see the different statements created, but the bottom line is, that this should be a very very simple comparison, and having a failure here, gives me the creeps.

"God doesn't play dice" - Albert Einstein "God not only plays dice, He sometimes throws the dices where they cannot be seen" - Niels Bohr

Bernhard Hiller

Ah, I see. Reminds me of an old "obfuscted" Access database, where

SELECT * FROM A, B where A.ID=B.A_ID

was different from

SELECT * FROM A JOIN B ON A.ID=B.A_ID

The trick was that in one case padding with spaces was relevant, in the other case not.

jan larsen

Ha! I found the problem. DAX uses a caching mechanism to avoid too many roundtrips to the SQL Server. If you're using the primary key in a query, then DAX will search an in-memory cache to retrieve a single record. Apparently, DAX gets confused when I use the primary key field busRelAccount on the left hand side of the comparison. Using bus.disableCache(true) fixes this problem. I hope they get rid of this bug soon, I really don't like to think of all the places I could have trusted this to just work.

"God doesn't play dice" - Albert Einstein "God not only plays dice, He sometimes throws the dices where they cannot be seen" - Niels Bohr