FM12: SQL syntax bug (ExecuteSQL)

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.

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.

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.

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.

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.

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.

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.