12 Replies Latest reply on May 18, 2012 10:15 AM by TSGal

    FM12: SQL syntax bug (ExecuteSQL)

    arnoldkegebein

      Summary

      FM12: SQL syntax bug (ExecuteSQL)

      Product

      FileMaker Pro

      Version

      FM12.0v1

      Operating system version

      OS X 10.6.8

      Description of the issue

      The SQL syntax changed. With a SQL plugin I could run certain queries in FM10. But in FM12 the queries return only '?'.

      (1) SELECT only query:
      SELECT 'Hello World!', USER, CURRENT_DATE

      (2) In-line comment at the end of the query:
      SELECT TableName FROM FileMaker_Tables --Comment

      (3) Exists function:
      SELECT p.* FROM     Professor p
      WHERE EXISTS (
        SELECT * FROM Seminar s WHERE p.ProfNo = s.ProfNo
      )

      (4) Extract function in group clause:
      SELECT EXTRACT( YEAR FROM birthday ), COUNT(*)
      FROM Student
      GROUP BY EXTRACT( YEAR FROM birthday )

      (5) * plus additional columns
      SELECT COUNT(*), * FROM FileMaker_Tables

      Steps to reproduce the problem

      In the data viewer create a new expression:
      ExecuteSQL( "query"; ""; "" )
      Replace »query« with the queries from above.

      Expected result

      (1)
      Hello World!,Admin,2012-05-06

      (2)
      A list with the names of all defined tables occurrences in the current database.
      There is no problems if the inline comment is before the last line.

      (3)
      All professor records with a reference in table seminar.

      (4)
      A list with year numbers and the amount of students having birthday in these years.

      (5)
      A list with all records from FileMaker_Tables with the total number of tables in front of each line.
      You can use * alone, but as soon you will add another column

      Actual result

      In all five queries, the result in FM12 is always '?'.

      Configuration information

      In FM10, where I get proper results, I am using the FMSQL plugin from qutic development.

        • 1. Re: FM12: SQL syntax bug (ExecuteSQL)
          TSGal

          AKegebein:

          Thank you for your post.

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

          TSGal
          FileMaker, Inc.

          • 2. Re: FM12: SQL syntax bug (ExecuteSQL)
            TSGal

            AKegebein:

            Issue #1 has been reproducible by Testing.  It worked in FileMaker Pro 10, but failed in FileMaker Pro 11 and FileMaker Pro 12.  It has been forwarded to Development for review.

            Issue #2 has also been reproducible by Testing.  Again. it worked in FileMaker Pro 10, but failed in FileMaker Pro 11 and FileMaker Pro 12.  On a side note, it works fine with C-styles (/* ... */) but does not work with braces ( { } ) nor double-hyphen.

            Issue #3 could not be reproduced.  I have a test file that I can send you.  Send your email privately, and I'll send it to you.

            Issue #4 is reproducible.  The current SQL driver does not support subqueries in the FROM clause.

            Issue #5 is reproducible, but although this may have worked in FileMaker Pro 10, it does not work in FileMaker Pro 11 (or 12) because the statement is semantically incorrect.  The issue in this case is FileMaker Pro does not return an error about a missing GROUP BY clause.

            I'll continue to keep you updated as information becomes available.

            TSGal
            FileMaker, Inc.

            • 3. Re: FM12: SQL syntax bug (ExecuteSQL)
              arnoldkegebein

              TSGal,

              Thank you for your response.

              #1, #2 – okay.

              #3, #4 – I think, you (or Testing) mixed up #3 and #4. In my original post #3 is using a subquery, but your subquery comment is with #4. Nevertheless, I send you my email address.

              #5 – This probably requires a better example. The problem I see, as soon as you use an asterisks, you cannot add any other column. Query: SELECT user, * FROM FileMaker_Tables

              You say, the #5 query is semantcally incorrect. I am not aware of any SQL syntax that SELECT COUNT(*) requires a GROUP statement. Or, if I misunderstand the problem, may I ask you to elaborate about the semantic error?

              By the way, I remember from some time ago (FM10), that FileMaker used a subset of the SQL 92 sytax. Is that still the case with FM12?

              Regards, Arnold Kegebein

              • 4. Re: FM12: SQL syntax bug (ExecuteSQL)
                arnoldkegebein

                TSGal:

                Thank youu, I received your file. And you are right, issue #3 is running as expected. When I run the query today it delivers the proper result. When I posted the problem I must have had a slightly different query, perhaps mixing it with some of the other issues.

                I found another problem with EXTRACT (issue #4). The following queroies expect a table Student with a field Birthday. For some records, the field Birthday is empty.

                This query with EXTRACT is ok:
                SELECT EXTRACT( YEAR FROM Birthday ) FROM Student

                This query with COALESCE is ok:
                SELECT COALESCE( Birthday, 'unknown' ) FROM Student

                This query with COALESCE and EXTRACT creates an error in FM12, but runs fine in FM10:
                SELECT COALESCE( EXTRACT( YEAR FROM Birthday ), 'unknown' ) FROM Student

                Regards, Arnold Kegebein

                • 5. Re: FM12: SQL syntax bug (ExecuteSQL)
                  TSGal

                  AKegebein:

                  With Issue #5, the report back from Testing and Development is mixing an aggregate function with a field without a GROUP BY clause.  They did mention that "SELECT COUNT(*),Contacts.* FROM Contacts" appears to work.  However, when I tried it, my machine froze.  Close all files before you try this.

                  Your example of "SELECT USER,* FROM <table>" makes sense, but this mixes Issue #1 with Issue #5.  Regardless, I've sent this example back.

                  Thanks for the additional example combining COALESCE and EXTRACT.  This has also been sent back to the tester.

                  I'll continue to keep you updated.

                  TSGal
                  FileMaker, Inc.

                  • 6. Re: FM12: SQL syntax bug (ExecuteSQL)
                    arnoldkegebein

                    TSGal:

                    Why is the GROUP BY clause required with an aggregate function? How do you calculate the total sum of value of all records?
                    Standard SQL: SELECT SUM(value) FROM invoice

                    What would be an appropriate SQL query in FileMaker?

                    Arnold Kegebein

                    • 7. Re: FM12: SQL syntax bug (ExecuteSQL)
                      TSGal

                      AKegebein:

                      The GROUP BY clause is required if using a aggregate function along with a field.  That is,

                      SELECT SUM (Value) FROM Invoice

                      ... by itself is fine.  This will give a value for the entire table.  However, if using this with a non-aggregate field,

                      SELECT SUM (Value) FROM Invoice GROUP BY SalesPerson

                      This gives you an amount for each SalesPerson value.

                      Using your original example:   SELECT COUNT(*), * FROM FileMaker_Tables

                      SELECT COUNT(*),Name FROM FileMaker_Tables GROUP BY Name

                      This will give you a count for each Name grouping, followed by the Name

                      If you need more than one column to group by, then the SQL syntax would be:

                      SELECT COUNT(*),First_Name,Last_Name FROM FileMaker_Tables GROUP BY Last_Name,First_Name

                      Does that help?

                      TSGal
                      FileMaker, Inc.

                      • 8. Re: FM12: SQL syntax bug (ExecuteSQL)
                        arnoldkegebein

                        TSGal:

                        SQL does not require a GROUP BY clause with an aggregate function. I do not know of any SQL syntax where this is defined as a requirement.

                        The other way around has some restriction. If you use a GROUP BY clause, the select list can contain only certain expressions: constants, aggregate function, expressions identical to those in the GROUP BY clause, ...

                        I know, my previous sample were no real world examples. I wrote them to make it easy for Testing to replicate the issue without having to create dozends of tables. A real life example (perhaps for a log file):

                        SELECT COUNT(*), CURRENT_DATE FROM table

                        This query counts the number of records in a table and adds the date when the count was done.

                        Have a great weekend, Arnold Kegebein

                        • 9. Re: FM12: SQL syntax bug (ExecuteSQL)
                          TSGal

                          AKegebein:

                          Again, if you want to display an aggregate value along with a field, you use a GROUP BY clause.  Your latest example is combining an aggregate function (COUNT) with another function (CURRENT_DATE; not a field), so GROUP BY is not needed.  However, this is combining Issue #1 into the fold, and this has already been reported.

                          Doing a quick search with my browser, I found:

                          http://www.w3schools.com/sql/sql_groupby.asp

                          This explanation is fairly similar to what I previously described.

                          TSGal
                          FileMaker, Inc.

                          • 10. Re: FM12: SQL syntax bug (ExecuteSQL)
                            arnoldkegebein

                            TSGal:

                            I found another SQL bug. Shall I start a new post or just add it to this list?

                            Issue #6: Reference to column alias in GROUP BY clause fails
                            SELECT COUNT(*), INT(value/16) AS X FROM table GROUP BY X
                            SELECT COUNT(*), INT(value/16) FROM table GROUP BY 1

                            Neither query works in FileMaker Pro 12, but in FileMaker Pro 10.

                            Arnold Kegebein

                            • 11. Re: FM12: SQL syntax bug (ExecuteSQL)
                              TSGal

                              AKegebein:

                              In the above two examples, neither X nor 1 is a field, so the SQL is invalid.

                              It may have worked in FileMaker Pro 10, but starting with FileMaker Pro 11, a new driver was used and the SQL is more strict.

                              TSGal
                              FileMaker, Inc.

                              • 12. Re: FM12: SQL syntax bug (ExecuteSQL)
                                TSGal

                                AKegebein:

                                Regarding Issue #2, if you embed a carriage return character after the comment, it will then work.  For example:

                                ExecuteSQL("SELECT * FROM Contacts -- This is a comment" & Char (13); ""; "")

                                TSGal
                                FileMaker, Inc.