Hello. I am having a problem with a bug that causes cross join not to work properly when working with sum and count aggregation functions at the same time. Here is a self contained example that demonstrates it:
WITH table1 AS (
SELECT 10 stuff FROM dual
UNION ALL
SELECT 15 FROM dual
),
table2 AS (
SELECT sum(stuff) what FROM table1
),
table3 AS (
SELECT count(*) third FROM table1
)
SELECT * FROM table1
CROSS JOIN table2
CROSS JOIN table3
The expected results of this query is (tested on Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production)
stuff what third
15 25 2
10 25 2
The bug causes the contents of table1 to always be one NULL row, resulting in the following output (tested on Oracle Database 23ai Free Release 23.0.0.0.0)
stuff what third
NULL 25 2
Is there any way for me to fix this, without changing the query?