Question Status

Verified
Xavier C asked a question on 10 Apr 2013 12:42 AM

Hello,

I work with Dynamics AX2009 and I'm face to a problem that I can't understand. It seems that AX have a problem to correctly generate SQL query when we do some calculations in the WHERE.

I want to execute this X++ query :

WHILE SELECT salesId, LineNum, salesQty, SalesPrice, linePercent, LineAmount
FROM SalesLine
WHERE SalesLine.salesId >= 'K0100000'
&& salesLine.LineAmount > (salesLine.SalesPrice*salesLine.salesQty*(1 - salesLine.linePercent/100) + 0.01)
{
}

But if I create a trace in SQL Server Profiler for survey what is send to it, I can see this :

declare @p1 int
set @p1=1076836550
declare @p2 int
set @p2=195416459
declare @p5 int
set @p5=16
declare @p6 int
set @p6=1
declare @p7 int
set @p7=2
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 nvarchar(5),@P2 nvarchar(21),@P3 bigint,@P4 numeric(28, 12),@P5 bigint',N'SELECT A.SALESID,A.LINENUM,A.SALESQTY,A.ITEMID,A.LINEAMOUNT,A.SALESPRICE,A.LINEPERCENT,A.RECID FROM SALESLINE A WHERE ((DATAAREAID=@P1) AND ((SALESID>=@P2) AND (LINEAMOUNT>(((SALESPRICE*SALESQTY)*(@P3-(LINEPERCENT/@P4)))+@P5))))',@p5 output,@p6 output,@p7 output,N'kl',N'K0100000',100,1.000000000000,254
select @p1, @p2, @p5, @p6, @p7

if I rebuild the query it give :

SELECT A.SALESID,A.LINENUM,A.SALESQTY,A.ITEMID,A.LINEAMOUNT,A.SALESPRICE,A.LINEPERCENT,A.RECID
FROM SALESLINE A
WHERE DATAAREAID = N'kl'
  AND SALESID >= N'K0100000'
  AND LINEAMOUNT > (SALESPRICE*SALESQTY* (100-LINEPERCENT/1.00)) + 254

Which is not the same at all ! We can see that the 1 and the 100 have been exchange (i did some tests, it seems really to be an exchange) and the 0.01 and been change into 254 ?!

Does someone ever seen something like that ? And how can I be sure that ALL of the other queries send are OK ?


Thanks.

Ps : Sorry for mistakes, I'm French

Reply
Verified Answer
Xavier C responded on 13 Sep 2013 3:32 AM

Hi,

I re-open this post because I had the solution.

I send this problem to our Hotline, and they first answered that there was already a fix for that. We installed it and upgrade the kernel version from 5.0.1500.6491 to 5.0.1600.1616. But finally this corrected only half of the problem. After that, I didn't have anymore the replacement of 0.01 by 254. But I still had the exchange between 1 and 100. So I was in the same case of Tommy Skaue.

So I re-mailed them which escalate it to the Microsoft development team. After several months they finally publish a new Hotfix which correct the bug. We are now in Kernel version 5.0.1600.2031.

Thank you all for having tried to help me.

Reply
Suggested Answer
Tommy Skaue responded on 10 Apr 2013 3:36 AM

Try to Synchronize the table from the AOT.

You could also try flush the Procedure Cache on the SQL Server, tho not during business hours.

SQL Server command is:

DBCC FREEPROCCACHE

(blog.sqlauthority.com/.../sql-server-stored-procedure-clean-cache-and-clean-buffer)

Not sure tho.

Tommy Skaue | Dynamics AX Developer from Norway | http://yetanotherdynamicsaxblog.blogspot.no/ | www.axdata.no

Reply
Suggested Answer
Tommy Skaue responded on 11 Apr 2013 11:50 AM

It seemed to work if I moved the parameter around:

See how I change the "1 -" to after dividing the percentage. The result is the same, but now it works.
 

Tommy Skaue | Dynamics AX Developer from Norway | http://yetanotherdynamicsaxblog.blogspot.no/ | www.axdata.no

Reply
Suggested Answer
Tommy Skaue responded on 12 Apr 2013 1:42 AM

The formula is supposed to be:

A*B*(C/100 -1) + 1.011

The only thing I changed was switching the place of "minus 1" to after C/100 instead of in front.

In other words:

(1 - (salesLine.LinePercent/100) )

vs

((salesLine.LinePercent/100) - 1)

Regarding forceliterals I don't think they matter, as it seems to be a problem on the AOS side, as Dick says. Actually I had to bind the variables when running the Trace within AX2012 in order to see the values being used in the query.

So in conclusion, try:

WHILE SELECTsalesId, LineNum, salesQty, SalesPrice, linePercent, LineAmount
FROM SalesLine
WHERE SalesLine.salesId >= 'K0100000'
&& salesLine.LineAmount > (salesLine.SalesPrice*salesLine.salesQty*((salesLine.linePercent/100)-1) + 0.01)
{
}

Tommy Skaue | Dynamics AX Developer from Norway | http://yetanotherdynamicsaxblog.blogspot.no/ | www.axdata.no

Reply
Suggested Answer
Tommy Skaue responded on 10 Apr 2013 3:36 AM

Try to Synchronize the table from the AOT.

You could also try flush the Procedure Cache on the SQL Server, tho not during business hours.

SQL Server command is:

DBCC FREEPROCCACHE

(blog.sqlauthority.com/.../sql-server-stored-procedure-clean-cache-and-clean-buffer)

Not sure tho.

Tommy Skaue | Dynamics AX Developer from Norway | http://yetanotherdynamicsaxblog.blogspot.no/ | www.axdata.no

Reply
Xavier C responded on 10 Apr 2013 7:22 AM

Thanks for reply.

I've tried DBCC FREEPROCCACHE but nothing changed, I have always the problem.

I tried the same query but on the table custInvoiceTrans instead of salesLine and the problem is the same. I tried too on a new table with only the fields I need, again the same. So it's not related to the table.

One thing interesting : If I add "forceLiterals" on my query the result is a bit different, I always have the exchange between 1 and 100, but the 0.01 is good. But anyway, it counldn't be a solution because the final objective is to create an alerte based on a specific filter (in which we can't specify forceLiterals).

Reply
Tommy Skaue responded on 10 Apr 2013 9:17 AM

Could you try, just as a sanity check, wrap your atomic values with query range expressions?

www.axaptapedia.com/Expressions_in_query_ranges

It's a bit of hazzle to convert your query using the Query object, but could you try and see if it makes any difference?

It really does sound like the SQL Server is picking a bad execution plan, but I don't see why.

Tommy Skaue | Dynamics AX Developer from Norway | http://yetanotherdynamicsaxblog.blogspot.no/ | www.axdata.no

Reply
Daniel Weichsel responded on 10 Apr 2013 3:49 PM

I checked into this on AX2012 R2 and the issue exists there as well.  By using the generateonly keyword on the select statement (also with forceLiterals), it shows that the generated SQL statement is incorrect (100 and 1 are exchanged, 0.01 is correct):

    select firstOnly generateonly forceLiterals SalesId from salesLine
        where salesLine.SalesId >= '1000'
            && salesLine.LineAmount > (salesLine.SalesPrice * salesLine.SalesQty * (1 - (salesLine.LinePercent / 100)) + 0.01);

SELECT TOP 1 T1.SALESID,T1.RECID FROM SALESLINE T1 WHERE (((PARTITION=5637144576) AND (DATAAREAID=N'usmf')) AND ((SALESID>=N'1000') AND (LINEAMOUNT>(((SALESPRICE*SALESQTY)*(100-(LINEPERCENT/1.E0)))+1.E-2))))

When capturing a trace with SQL Server Profiler while running this code, I could not find any reference to this hitting the SQL server.  It looks like it is an AOS issue.

Reply
Xavier C responded on 11 Apr 2013 12:36 AM

 I've already tried that, in fact it's the origin of my problem.

Not in a queryBuildDataSource with queryBuildRange, but in filter on an alert rule. I made a filter with the syntax describe in Advanced filtering and query options (SQL statement between parenthesis) so it's the same thing of expression in query ranges but it doesn't work. From there, I prospected farther, and that's how I discovered this problem...

Reply
Xavier C responded on 11 Apr 2013 12:51 AM

OK, so same problem in AX2012, it's not really comforting...

So what can we do face to that kind of bug ?

If I change "(1 - (salesLine.LinePercent / 100)) "  by "(salesLine.LinePercent * (-0.01) + 1)" it works, but it's not really a good solution...

And I always have the transformation of "+ 0.01" in "+ 254" when I use it in a standard filter. Here, I would think it's byte problem (254 = 2^8 - 2 ?).

Reply
Tommy Skaue responded on 11 Apr 2013 7:26 AM

Wow. That is a really kewl bug (not)...

I did a quick test and a trace as well. Sure enough, the kernel does something weird with those numbers.

Something for the Support Team to have a look at. ;-)

Tommy Skaue | Dynamics AX Developer from Norway | http://yetanotherdynamicsaxblog.blogspot.no/ | www.axdata.no

Reply
Dick Wenning responded on 11 Apr 2013 10:08 AM

the expression with salesline.a < salane.b+salesline.c, etc can't work.

in case you would like to do it follow steps in next link

www.axaptapedia.com/.../Expressions_in_query_ranges

Kind regards, 

Kaya Solutions

Dick Wenning

+31 6 147 989 53 

Landjuweel 5

3905 PE - Veenendaal

 

OTHER CONTACT INFORMATION

Reply
Suggested Answer
Tommy Skaue responded on 11 Apr 2013 11:50 AM

It seemed to work if I moved the parameter around:

See how I change the "1 -" to after dividing the percentage. The result is the same, but now it works.
 

Tommy Skaue | Dynamics AX Developer from Norway | http://yetanotherdynamicsaxblog.blogspot.no/ | www.axdata.no

Reply
Xavier C responded on 12 Apr 2013 1:34 AM

Thank you to take time for me.

Tommy, your formula is not the equal to mine :

A*B*(C/100 +1) + 1.011 != A*B*(1-C/100) + 0.01 (just take A=B=C=1, and you will see it doesn't work)

Dick, I read the article on Axaptapedia, but I don't how I could do the same comparison without this calculation. And first, the X++ code compiles and second, if I remove number in my calculation, it works fine...

Tommy, something appeals to me, in your screen capture, I see that you don't put forceLiterals and the last number 0.01 is good in SQL. If I do exactly the same thing, me, I have 254 instead of 0.01. So it might have difference somewhere...

Reply
Suggested Answer
Tommy Skaue responded on 12 Apr 2013 1:42 AM

The formula is supposed to be:

A*B*(C/100 -1) + 1.011

The only thing I changed was switching the place of "minus 1" to after C/100 instead of in front.

In other words:

(1 - (salesLine.LinePercent/100) )

vs

((salesLine.LinePercent/100) - 1)

Regarding forceliterals I don't think they matter, as it seems to be a problem on the AOS side, as Dick says. Actually I had to bind the variables when running the Trace within AX2012 in order to see the values being used in the query.

So in conclusion, try:

WHILE SELECTsalesId, LineNum, salesQty, SalesPrice, linePercent, LineAmount
FROM SalesLine
WHERE SalesLine.salesId >= 'K0100000'
&& salesLine.LineAmount > (salesLine.SalesPrice*salesLine.salesQty*((salesLine.linePercent/100)-1) + 0.01)
{
}

Tommy Skaue | Dynamics AX Developer from Norway | http://yetanotherdynamicsaxblog.blogspot.no/ | www.axdata.no

Reply
Dick Wenning responded on 12 Apr 2013 2:15 AM

i did some checks

these work fine, I know in ax 2009 and before it did not work

OK

static void Job1(Args _args)

{

   CustInvoiceJour CustInvoiceJour;

   while select CustInvoiceJour where CustInvoiceJour.SalesBalanceMST < (1 + (CustInvoiceJour.SalesBalanceMST ) )

   {

    info(   CustInvoiceJour.InvoiceAccount);

   }

}

roundoffs

 while select CustInvoiceJour where CustInvoiceJour.SalesBalanceMST == (1 + (CustInvoiceJour.SalesBalanceMST/100  )*100 -1 )

   {

    info(   CustInvoiceJour.InvoiceAccount);

   }

but just a check

incase you move the where condition to if condition in the while loop, just to be Shure, do you get the expected data?

Kind regards, 

Kaya Solutions

Dick Wenning

+31 6 147 989 53 

Landjuweel 5

3905 PE - Veenendaal

 

OTHER CONTACT INFORMATION

Reply
Xavier C responded on 15 Apr 2013 1:09 AM

Dick, I did the check and the query doesn't work :

while SELECT salesId, LineNum, salesQty, LineAmount, SalesPrice, linePercent
FROM salesLine
WHERE salesLine.salesId >= 'K0100000'
   && salesLine.LineAmount > (salesLine.SalesPrice*salesLine.salesQty*(1 - salesLine.linePercent/100) + 0.01)
{
    info(salesLine.SalesId + "/" + int2str(salesLine.LineNum));
}

Even though this works :

while SELECT salesId, LineNum, salesQty, LineAmount, SalesPrice, linePercent
FROM salesLine
WHERE salesLine.salesId >= 'K0100000'
{
    if (salesLine.LineAmount > (salesLine.SalesPrice*salesLine.salesQty*(1 - salesLine.linePercent/100) + 0.01))
    {
        info(salesLine.SalesId + "/" + int2str(salesLine.LineNum));
    }
}

Tommy, (1 - (salesLine.LinePercent/100) ) is not the same as ((salesLine.LinePercent/100) - 1). In the first case you get a positive value, in the second you get a negative. If you want the same you must muplicate by -1.

And if I execute this :

while SELECT salesId, LineNum, salesQty, LineAmount, SalesPrice, linePercent
FROM salesLine
WHERE salesLine.salesId >= 'K0100000'
   && salesLine.LineAmount > (salesLine.SalesPrice*salesLine.salesQty*(salesLine.linePercent/100 - 1)*(-1) + 0.01)
{
    info(salesLine.SalesId + "/" + int2str(salesLine.LineNum));
}

In SQL Trace, the division by 100 and the subtraction by 1 is OK but the multiplication by -1 is changed in 4294967295 and the addition of 0.01 is even changed in 254 :

exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 nvarchar(5),@P2 nvarchar(21),@P3 numeric(28, 12),@P4 bigint,@P5 bigint,@P6 bigint',N'SELECT A.SALESID,A.LINENUM,A.SALESQTY,A.LINEAMOUNT,A.SALESPRICE,A.LINEPERCENT,A.CUSTACCOUNT,A.RECID FROM SALESLINE A WHERE ((DATAAREAID=@P1) AND ((SALESID>=@P2) AND (LINEAMOUNT>((((SALESPRICE*SALESQTY)*((LINEPERCENT/@P3)-@P4))*@P5)+@P6))))',@p5 output,@p6 output,@p7 output,N'kl',N'K0100000',100.000000000000,1,4294967295,254

Reply
Dick Wenning responded on 15 Apr 2013 1:31 AM

I forwarded this discussion to MS core team. there response was "looks BAD"  So I think they say, we have a bug

Kind regards, 

Kaya Solutions

Dick Wenning

+31 6 147 989 53 

Landjuweel 5

3905 PE - Veenendaal

 

OTHER CONTACT INFORMATION

Reply
Tommy Skaue responded on 15 Apr 2013 1:35 AM

@Xavier: You're absolutely right. It needs to be multiplied with -1 again. Stupid mistake from me.

My tests were on AX2012 RTM (6.0.1108.670).

+1 to the MS core team. ;-)

Tommy Skaue | Dynamics AX Developer from Norway | http://yetanotherdynamicsaxblog.blogspot.no/ | www.axdata.no

Reply