Query group missing from the output
(1.1) By Yu Liang (LY1598773890) on 2021-10-01 03:07:01 edited from 1.0 [source]
CREATE TABLE v0 ( c1, c2 INT ) ; INSERT INTO v0 ( c1, c2 ) VALUES ( 100, 200 ), ( 127, 400 ) ; /* First Select */ SELECT a8.c1 IN ( SELECT a9.c1 FROM v0 AS a9 GROUP BY a9.c1 HAVING SUM ( a8.c2 ) > 100 ) FROM v0 AS a8; /* Outputs '1' */ /* Expected '1 \n 1 \n ' */ /* Second Select */ SELECT a8.c1 IN (SELECT a9.c1 FROM v0 AS a9 WHERE a8.c2 > 100) FROM v0 AS a8; /* Outputs '1 \n 1 \n '. Expected */ /* Third Select */ SELECT a8.c1 IN (SELECT a9.c1 FROM v0 AS a9 WHERE a9.c2 > 100) FROM v0 AS a8; /* Outputs '1 \n 1 \n '. Expected */
Since the subquery from the first SELECT statement has 'GROUP BY a9.c1', we expect two groups being created, and thus the result from the First Select statement should have 2 rows, which should be '1 1 '. However, just one row being returned from SQLite3.
We notice that the alias a8 used in the HAVING clause plays a role in this query. We change the alias from a8 to a9 in the HAVING clause, the query returns '1 1 ' as expected.
Do you have some insight why the First Select only returns one row as results?
(2) By jake on 2021-10-01 03:22:09 in reply to 1.1 [link] [source]
Sometimes a correlated aggregate subquery which references an outer table will cause the outer query to behave like an aggregate query.
Unfortunately this is not a bug - it's just the way it is. This behaviour is consistent with the way other DB engines behave.
(3) By Yu Liang (LY1598773890) on 2021-10-01 04:08:42 in reply to 2 [link] [source]
Thank you for providing the link. The linked thread has the similar question and has answered our doubts.
It is interesting to confirm that Postgres and MySQL also shares this behavior.