AnsweredAssumed Answered

SQL engine buggy - Correlated subquery in HAVING statement

Question asked by electric_soul on Jan 8, 2013
Latest reply on Jan 11, 2013 by philmodjunk

Summary

SQL engine buggy - Correlated subquery in HAVING statement

Product

FileMaker Pro

Version

12.0v3 & 11.0v3

Operating system version

Windows xp SP3

Description of the issue

SQL Statement gives wrong results.

With "Filemaker Pro 11 Adv" I used the baselements plugin to access the sql engine. With "FM Pro 12 Adv" I used the executeSQL() function. Both versions gave the same results.

Steps to reproduce the problem

Download this file
http://www.peejeshare.com/files/363438503/testsql.fp7.html

open dataviewer and run the following sql statement.

SELECT oli.ID_Product, sum( oli.qty )
FROM material oli
WHERE oli.ID_Order = 'SQ-000568-01'
GROUP BY oli.ID_Product
HAVING sum( oli.qty ) > (SELECT sum( ap2.Quantity) FROM Artikelposten ap2 WHERE ap2.id = oli.ID_Product)
")

Expected result

1
B-FC-5114     10
B-FC-5115     10
B-FC-5120     10
B-FC-5200     10
B-FC-5201     1
B-FC-5250     1
B-FC-5300     3

Actual result

1
APS-KIT0     25
B-FC-5114     10
B-FC-5115     10
B-FC-5120     10
B-FC-5200     10
B-FC-5201     1
B-FC-5250     1
B-FC-5300     3

The ID APS-KIT0 should not appear.


I've tested it with MySQL on XAMPP. MySQL gives the following result.

B-FC-5114     10
B-FC-5115     10
B-FC-5120     10
B-FC-5200     10
B-FC-5201     1
B-FC-5250     1
B-FC-5300     3

Outcomes