SQLite Forum

Bug: unexpected result from an empty table
Login
Hello,

For this case:

```
CREATE TABLE t0(c0);
SELECT COUNT(t1.c1), t1.c1 FROM (SELECT 1 AS c1 FROM t0) AS t1; -- 0|1
```
It says in the documentation:

> If the SELECT statement is an aggregate query without a GROUP BY clause, then each aggregate expression in the result-set is evaluated once across the entire dataset. Each non-aggregate expression in the result-set is evaluated once for an arbitrarily selected row of the dataset. The same arbitrarily selected row is used for each non-aggregate expression. Or, if the dataset contains zero rows, then each non-aggregate expression is evaluated against a row consisting entirely of NULL values.

Since the table t0 is empty, interim result of subquery `SELECT 1 AS c1 FROM t0` should be empty, and the expected result for the whole query should be `0|` instead of `0|1`.

I think the cause of the problem is still related to optimization SQLITE_QueryFlattener, when we turn off this optimization, we can get the desired result.