Query group missing from the output
(1.1) By Yu Liang (LY1598773890) on 2021-10-01 03:07:01 edited from 1.0 [link] [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?
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.