Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: [FireDAC][Phys[SQLite] ERROR: misuse of aggregate: max()


This question is answered. Helpful answers available: 0. Correct answers available: 1.


Permlink Replies: 9 - Last Post: Sep 3, 2015 10:26 AM Last Post By: Tom Roberts
Tom Roberts

Posts: 102
Registered: 6/21/05
[FireDAC][Phys[SQLite] ERROR: misuse of aggregate: max()  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Aug 29, 2015 1:26 AM
The following (pointless) query is a simplified version of a query I attempted but illustrates the error.

select (select x) from
(select max(Col) as x from Tbl);

[substitute any table for Table and any column for Col].

It works fine with SQLite (via SQLite Expert) but if you try it with FireDAC it gives a "misuse of aggregate: max()' error. Anyone know why?

Some other things I've noticed using FireDAC with SQLite.

1) FDQuery1->SQL->Text='pragma table_info(UnquotedTableName)' returns the correct result set but also raises a "near '(' syntax error" exception.

2) FDQuery1->SQL->Text='select 1;' returns the correct result set but also raises a "near ';' syntax error" exception (removing the semi-colon from the SQL->Text removes the error)..

All of the above works fine in SQLiteExpert so I doubt these errors are being returned by the SQLite code.

3) FireDAC has supposedly added the string functions LEFT(Str,Len) and RIGHT(Str,Len). Calling either of them in a query results in a "near '(' syntax error" exception and no result set returned. This happens even if I define UDF's by that name myself.

Edited by: Tom Roberts on Aug 30, 2015 2:01 AM

Edited by: Tom Roberts on Aug 30, 2015 2:16 AM
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: [FireDAC][Phys[SQLite] ERROR: misuse of aggregate: max()
Helpful
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 1, 2015 10:38 PM   in response to: Tom Roberts in response to: Tom Roberts
1) FDQuery1->SQL->Text='pragma table_info(UnquotedTableName)' returns the correct result set but also raises a "near '(' syntax error" exception.
2) FDQuery1->SQL->Text='select 1;' returns the correct result set but also raises a "near ';' syntax error" exception (removing the semi-colon from the SQL->Text removes the error)..

Referring to your prior message. Do you use RecordCountMode == cmTotal ?
If yes, then you should use it carefully - explained in prior message.

3) FireDAC has supposedly added the string functions LEFT(Str,Len) and RIGHT(Str,Len). Calling either of them in a query results in a "near '(' syntax error" exception and no result set returned. This happens even if I define UDF's by that name myself.

Could you please provide full SQL command ?

--
With best regards,
Dmitry Arefiev / FireDAC Architect
Tom Roberts

Posts: 102
Registered: 6/21/05
Re: [FireDAC][Phys[SQLite] ERROR: misuse of aggregate: max()  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 2, 2015 8:04 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Dmitry Arefiev wrote:
1) FDQuery1->SQL->Text='pragma table_info(UnquotedTableName)' returns the correct result set but also raises a "near '(' syntax error" exception.
2) FDQuery1->SQL->Text='select 1;' returns the correct result set but also raises a "near ';' syntax error" exception (removing the semi-colon from the SQL->Text removes the error)..

Referring to your prior message. Do you use RecordCountMode == cmTotal ?
If yes, then you should use it carefully - explained in prior message.

Changing the RecordCountMode does solve the above problems Dimitry although I take it that's down to the fact that firedac then has no need to "misinterpret" the SQL? I continue to get the 'misuse of aggregate' error with the original query though.


3) FireDAC has supposedly added the string functions LEFT(Str,Len) and RIGHT(Str,Len). Calling either of them in a query results in a "near '(' syntax error" exception and no result set returned. This happens even if I define UDF's by that name myself.

Could you please provide full SQL command ?

select left('ab',1); (always assuming I can now safely append a semi-colon :-)


--
With best regards,
Dmitry Arefiev / FireDAC Architect
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: [FireDAC][Phys[SQLite] ERROR: misuse of aggregate: max()
Helpful
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 2, 2015 10:07 PM   in response to: Tom Roberts in response to: Tom Roberts
select left('ab',1); (always assuming I can now safely append a semi-colon :-)

Ok, clear. The problem - "LEFT" is reserved word in SQLite. To workaround that
FireDAC SQLite driver registers the function under "LEFT_" name. And FireDAC
SQL preprocessor is aware of that. So, two options:
1) "select left_('ab',1)" - with registered function name;
2) "select {left('ab',1)}" - with FireDAC escape function name.

--
With best regards,
Dmitry Arefiev / FireDAC Architect
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: [FireDAC][Phys[SQLite] ERROR: misuse of aggregate: max()  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 2, 2015 10:22 PM   in response to: Tom Roberts in response to: Tom Roberts
Changing the RecordCountMode does solve the above problems Dimitry although I take it that's down to the fact that firedac then has no need to "misinterpret" the SQL?

FireDAC SQL preprocessor is not that smart, to avoid multiple potential cases,
when SQL may be "misinterpret". Instead of that a user should understand this
fact and use preprocessor features carefully. In this sense it is more like C
preprocessor which dont know too much about C syntax.

I continue to get the 'misuse of aggregate' error with the original query though.

I tried the following SELECT using fddemo.sdb demo database:
select (select x) from 
(select max(RegionDescription) as x from "Region")

It works without errors. Could you please provide test DB and corresponding test
query to reproduce the issue ?

--
With best regards,
Dmitry Arefiev / FireDAC Architect
Tom Roberts

Posts: 102
Registered: 6/21/05
Re: [FireDAC][Phys[SQLite] ERROR: misuse of aggregate: max()  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 3, 2015 3:15 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Dmitry Arefiev wrote:
Changing the RecordCountMode does solve the above problems Dimitry although I take it that's down to the fact that firedac then has no need to "misinterpret" the SQL?

FireDAC SQL preprocessor is not that smart, to avoid multiple potential cases,
when SQL may be "misinterpret". Instead of that a user should understand this
fact and use preprocessor features carefully. In this sense it is more like C
preprocessor which dont know too much about C syntax.

I continue to get the 'misuse of aggregate' error with the original query though.

I tried the following SELECT using fddemo.sdb demo database:
select (select x) from 
(select max(RegionDescription) as x from "Region")

It works without errors. Could you please provide test DB and corresponding test
query to reproduce the issue ?

--
With best regards,
Dmitry Arefiev / FireDAC Architect

I'm getting the error using the example from the other thread Dimitry.

CREATE TABLE [MyTbl] (
[ID] INTEGER,
[Str] CHAR(4),
CONSTRAINT [] PRIMARY KEY ([ID]));

insert into MyTbl values ((1<<56)+1,'X');

select (select x)
from (select max(ID) as x from MyTbl)

I still get the error even if I set the only ID value to 1 rather than (1<<56)+1.

Edited by: Tom Roberts on Sep 3, 2015 3:16 AM
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: [FireDAC][Phys[SQLite] ERROR: misuse of aggregate: max()  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 3, 2015 5:03 AM   in response to: Tom Roberts in response to: Tom Roberts
I still get the error even if I set the only ID value to 1 rather than (1<<56)+1.

I cannot reproduce this issue. Can you upload to attachments a trivial test application, reproducing this issue ?

--
With best regards,
Dmitry Arefiev / FireDAC Architect
Tom Roberts

Posts: 102
Registered: 6/21/05
Re: [FireDAC][Phys[SQLite] ERROR: misuse of aggregate: max()  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 3, 2015 7:28 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Dmitry Arefiev wrote:
I still get the error even if I set the only ID value to 1 rather than (1<<56)+1.

I cannot reproduce this issue. Can you upload to attachments a trivial test application, reproducing this issue ?

--
With best regards,
Dmitry Arefiev / FireDAC Architect

Sorry Dimitry, I don't think I can make it any simpler than shown below. If that doesn't produce the error for you then I'm at a loss.

CREATE TABLE [MyTbl] (
[ID] INTEGER,
[Str] CHAR(4),
CONSTRAINT [] PRIMARY KEY ([ID]));

insert into MyTbl values (1,'X');

select (select x)
from (select max(ID) as x from MyTbl);

PS I also tried the above with the FetchOptions Mode and RecordCountMode back to their default values (fmOnDemand and cmVisible) but I still got the misuse of aggregate error.

Edited by: Tom Roberts on Sep 3, 2015 7:47 AM
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: [FireDAC][Phys[SQLite] ERROR: misuse of aggregate: max()  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 3, 2015 10:05 AM   in response to: Tom Roberts in response to: Tom Roberts
This may to be due to a difference in SQLite versions in XE6 and SQLite Expert.
Please execute in XE6 and in SQLite Expert:
select sqlite_version()


--
With best regards,
Dmitry Arefiev / FireDAC Architect
Tom Roberts

Posts: 102
Registered: 6/21/05
Re: [FireDAC][Phys[SQLite] ERROR: misuse of aggregate: max()  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 3, 2015 10:26 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Dmitry Arefiev wrote:
This may to be due to a difference in SQLite versions in XE6 and SQLite Expert.
Please execute in XE6 and in SQLite Expert:
select sqlite_version()


--
With best regards,
Dmitry Arefiev / FireDAC Architect

Dmitry Arefiev wrote:
This may to be due to a difference in SQLite versions in XE6 and SQLite Expert.
Please execute in XE6 and in SQLite Expert:
select sqlite_version()


--
With best regards,
Dmitry Arefiev / FireDAC Architect

Could be Dimitry

XE6 3.8.3.1

SQLiteExpert 3.8.10,2

It isn't a big problem anyway as the max subquery can be written as '(select ID as x from MyTbl order by ID desc limit 1)' so don't waste any more time on it Dimitry. Thanks for all your help.
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02