I ran this code in an sqlite3 terminal (version 3.29.0), and the result is a little strange:
sqlite> select sum((select 4 union all select 2 )); 4 sqlite> select sum((select 4 union select 2 )); 2
I understand why union reverses the table, but why does sum choose the first element?
Meanwhile, this code works just as expected:
sqlite> select sum(x) from (select 4 as x union all select 2 as x); 6
Is this the intended behavior, or is this a bug in sqlite? And if it’s intended, what’s the logic (and semantics) behind it?
Advertisement
Answer
That’s expected behavior.
From the documentation (emphasis added)
A SELECT statement enclosed in parentheses is a subquery. All types of SELECT statement, including aggregate and compound SELECT queries (queries with keywords like UNION or EXCEPT) are allowed as scalar subqueries. The value of a subquery expression is the first row of the result from the enclosed SELECT statement. The value of a subquery expression is NULL if the enclosed SELECT statement returns no rows.
The UNION example just happened to end up returning.rows in a different order than the UNION ALL one. Without an ORDER BY neither one is guaranteed to use a particular order, though.