Dependent query cannot refer aggregate expr in more than 1 nesting level

(1.1) By vorimi on 2023-11-01 22:03:51 edited from 1.0 [link] [source]

Hello devs,

I have discovered the following SQLite limitation:

here is the same repro working for MySQL: dbfiddle.uk/wa8Kc48N and for PostgreSQL: dbfiddle.uk/6Z-IfLMx

I tested SQLite v3.39 and the latest v3.44.

The failing query patten is important to be supported. In complex queries, it allows to deduplicate subqueries used later more than one without having to put them in the main select.

I would be grateful if the SQLite limitation can be lifted.

Michael

(2) By David Raymond (dvdraymond) on 2023-11-02 12:44:59 in reply to 1.1 [link] [source]

Do you have an example where it would actually by useful? Because while simple examples are indeed usually best to show things, what you've shown is that your insane sub-queries are unnecessary and can be replaced with something much more simple.

Why would they support...
having (select v > 6 from (select sum(amount) v) t)
when you can just write...
having sum(amount) > 6

or

having (select v = 'John' from (select name v) t)
when you can just write
having name = 'John'

?

(3) By vorimi on 2023-11-02 14:09:39 in reply to 2 [source]

To the exact/atk4 data usecase: I need to select an aggregate of non-trivial expression (ie. computing it can be costly and the expr can be even unstable) and use it multiple times like:

select * from t having sum(subquery) = v1 or sum(subquery) > v2

as long the sum(subquery) is not meant to be returned (cannot be in the main select) nor put into WITH, then the, currently unsupported, SQL like:

select * from t having (select s = v1 or s > v2 from (select sum(subquery) s) tmp)

is perfectly reasonable query and when understood correctly by the DB driver also optimal.

(4) By Richard Hipp (drh) on 2023-11-03 10:05:35 in reply to 3 [link] [source]

Beginning with check-in 4470f657d206997, I think SQLite now gives the same answer as PostgreSQL. Please try out the changes and confirm. The latest code can be tested on-line at https://sqlite.org/fiddle.

(5) By vorimi on 2023-11-03 14:53:01 in reply to 4 [link] [source]

It works now, thank you very much for fixing it!

Michael