6 Replies Latest reply on Jun 12, 2015 2:14 PM by philmodjunk

    DISTINCT or UNION destroys order in FQL queries

    VincentL

      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).

        • 1. Re: DISTINCT or UNION destroys order in FQL queries
          TSGal

          Vincent L:

          Thank you for your post.

          I have sent your entire post and sample file to our Development and Testing departments for review.  When I receive any feedback, I will let you know.

          TSGal
          FileMaker, Inc.

          • 2. Re: DISTINCT or UNION destroys order in FQL queries
            TSGal

            Vincent L:

            Testing is able to replicate the issue, and the issue has been sent to Development for review.

            The notes from Testing shows:

            ExecuteSQL ( "SELECT TheValue, MAX(TheOrder) FROM DistinctBug WHERE TheOrder>0 GROUP BY TheValue ORDER BY MAX(TheOrder) DESC"  ; Char(9) ; "¶" )

            ....does not work.

            SELECT DISTINCT A.TheValue, MAX(A.TheOrder) AS m
            FROM T A 
            GROUP BY A.TheValue 
            ORDER BY m DESC

            ... works

            -----

            TSGal
            FileMaker, Inc.

            • 3. Re: DISTINCT or UNION destroys order in FQL queries
              VincentL

              Hi TSGal,

              Thanks for the workaround, but it's juts a workaround. This needs to be fixed because it's consistent with SQL, and the workaround may be overly complicated in some more complex case. Also, people won't be able to find it, and will spend hours (like I did), to try everything, since the first syntax is the one that makes logical sense.

              Even worse, depending on your dataset, if you have bad luck, the buggy syntax will deliver you the correct result. Therefore, a developer may be confident his scripts delivers the good result, and later in the user hand after some more data exists, it won't work reliably anymore. That makes it a hidden bug that can have dire consequences (showing wrong data in a sale report for instance), a bug that's really hard to find out because the syntax is ok.

              So I hope Filemaker Inc will correct that.

              Furthermore, let's not forget the UNION issue that's related and which has no workaround.

              So I think that's a mandatory fix, please fix it

               

               

              • 4. Re: DISTINCT or UNION destroys order in FQL queries
                TSGal

                Vincent L:

                My apologies for the confusion.  This is definitely an issue.

                I thought it best to let you know what Testing had found.  The information was not meant to be a workaround or solution.

                TSGal
                FileMaker, Inc.

                • 5. Re: DISTINCT or UNION destroys order in FQL queries
                  philmodjunk

                  In these threads, you get two possible responses: a) acknowledgment that the issue exists b) possible work arounds

                  a) means that FileMaker now knows about the issue and may fix it in a future release

                  b) means that you have a way to work around the current issue in the current release so that you aren't stuck waiting for a fix to be released.

                  • 6. Re: DISTINCT or UNION destroys order in FQL queries
                    philmodjunk

                    An entry in the Known Bugs List has been linked to this Issue Report. Any Comments/Questions/Suggested Corrections should be posted here or in a new thread. Please do not post such comments to the Known Bugs List thread.