AnsweredAssumed Answered

DISTINCT or UNION destroys order in FQL queries

Question asked by VincentL on Jun 2, 2015
Latest reply on Jun 12, 2015 by philmodjunk

Summary

DISTINCT or UNION destroys order in FQL queries

Product

FileMaker Pro

Version

13v5, 14.0.1

Operating system version

Yosemite 10.10.3

Description of the issue

In filemaker SQL aka FQL (meaning the internal SQL used with ExceuteSQL).

If you sort with an ORDER BY, and use the DISTINCT statement. The Distinct will destroys the order by order, as if Filemaker would resort the result itself after the query execution.

Plenty details here :

https://community.filemaker.com/thread/84889

UPDATED example file here (please make sure you expand dialog boxes a lot, use the script's in the scrip menu they explain everything)

https://www.dropbox.com/s/sj1mcdmi43jh4i4/DistinctBug_UPDATED.fmp12?dl=0


Hi here's a table

TheValue     TheOrder
C              99
B              30
B              30
A              10
A              2
B              1
A              1

SELECT DISTINCT TheValue FROM Table ORDER BY TheOrder DESC

I expect C B A

But I get B A C



OK according to http://www.programmerinterview.com/index.php/database-sql/sql-select-distinct-and-order-by/
This query is actually not correct

So the correct query should be

ExecuteSQL ( "SELECT Distinct A.TheValue FROM DistinctBug A
WHERE A.TheOrder>0 GROUP BY A.TheValue ORDER BY Max(A.TheOrder) DESC"  ; Char(9) ; "¶" )

it gives A B C

So it also fails (Please not that I may not fail depending on the dataset, but that's not reliable)


So Disctinct doesn't respect the ORDER BY clause. That's very bad IMHO, am I wrong.


Same thing with union


Union will destroy order, Union ALL won't but of course you'll have duplicates. That's not explained in the documentation.


ExecuteSQL ( "SELECT TheValue  FROM DistinctBug A
WHERE TheOrder=30
UNION SELECT TheValue  FROM DistinctBug A
WHERE TheOrder=10"  ; Char(9) ; "¶" )

This Gives A B instead of B A

you have to write

ExecuteSQL ( "SELECT TheValue  FROM DistinctBug A
WHERE TheOrder=30
UNION ALL SELECT TheValue  FROM DistinctBug A
WHERE TheOrder=10"  ; Char(9) ; "¶" )

it give B B A

But then you'll end up with unwanted extra B line

Steps to reproduce the problem

Use my file

Expected result

DISTINCT or UNION respecting the order

Actual result

Filemaker resorts the result using it's own "logic" discarding the order clause or the order of the union queries

Exact text of any error message(s) that appear

none

Workaround

No workaround

This is a major issue, since for instance you can't get the salesman ranking based on their biggest invoice. You can't have sorted distinct results by another field order.

Moreover none of those unexpected statements are documented by the way.

So this is a bug to me as there's no workaround and can't be predicted by documentation. But do not fix the doc, fix the bug as many many usages ae impossible due to this bug (and as it's unexpected, user can have their test query be ok in testing, but then end up with issue in the real world. That happened to me).

Outcomes