Log in to create and rate content, and to follow, bookmark, and share content with other members. Not a member? Join Now!

AnsweredAssumed Answered

FM12: SQL syntax bug (ExecuteSQL)

Question asked by arnoldkegebein on May 6, 2012
Latest reply on May 18, 2012 by TSGal

Like • Show 0 Likes0
Comment • 12

Summary

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.

No one else has this question

Outcomes

12 Replies

TSGal May 8, 2012 7:50 AM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
TSGal May 9, 2012 1:19 PM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
arnoldkegebein May 9, 2012 2:58 PM
Mark Correct
Correct Answer
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
Like • Show 0 Likes0
Actions
arnoldkegebein May 10, 2012 1:09 AM
Mark Correct
Correct Answer
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
Like • Show 0 Likes0
Actions
TSGal May 10, 2012 1:11 PM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
arnoldkegebein May 10, 2012 3:25 PM
Mark Correct
Correct Answer
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
Like • Show 0 Likes0
Actions
TSGal May 11, 2012 8:40 AM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
arnoldkegebein May 11, 2012 10:02 AM
Mark Correct
Correct Answer
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
Like • Show 0 Likes0
Actions
TSGal May 11, 2012 10:28 AM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
arnoldkegebein May 14, 2012 10:14 AM
Mark Correct
Correct Answer
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
Like • Show 0 Likes0
Actions
TSGal May 14, 2012 1:07 PM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
TSGal May 18, 2012 10:15 AM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions

FM12: SQL syntax bug (ExecuteSQL)

Summary

Product

Version

Operating system version

Description of the issue

Steps to reproduce the problem

Expected result

Actual result

Configuration information

Outcomes

Related Content

Recommended Content