Description:
A CTE containing two EXCEPT ALL operations is empty for the data below. However, two identical scalar expressions that count this CTE return different values when combined with UNION. The query returns both 2 and 3; it should return only 2.
How to repeat:
CREATE TABLE t1_query_expression (i INT);
CREATE TABLE t2_query_expression (i INT);
CREATE TABLE t3_query_expression (i INT);
INSERT INTO t1_query_expression VALUES (1), (1), (1);
INSERT INTO t2_query_expression VALUES (2), (2), (1), (1);
INSERT INTO t3_query_expression VALUES (2), (3), (3), (1), (1);
-- Actual: 0.
WITH ccte3 AS (
SELECT * FROM t1_query_expression
EXCEPT ALL
SELECT * FROM t2_query_expression
EXCEPT ALL
SELECT * FROM t3_query_expression
)
SELECT COUNT(*) FROM ccte3;
-- Actual: 1 row, col1 = 2.
WITH ccte3 AS (
SELECT * FROM t1_query_expression
EXCEPT ALL
SELECT * FROM t2_query_expression
EXCEPT ALL
SELECT * FROM t3_query_expression
)
SELECT DISTINCT (CAST((SELECT COUNT(*) FROM ccte3) AS SIGNED) + 1) + 1 AS col1;
-- Actual: 2 rows, col1 = 2 and col1 = 3.
WITH ccte3 AS (
SELECT * FROM t1_query_expression
EXCEPT ALL
SELECT * FROM t2_query_expression
EXCEPT ALL
SELECT * FROM t3_query_expression
)
SELECT (CAST((SELECT COUNT(*) FROM ccte3) AS SIGNED) + 1) + 1 AS col1
UNION
SELECT (CAST((SELECT COUNT(*) FROM ccte3) AS SIGNED) + 1) + 1 AS col1;
```
Expected result: The last query returns one row, `col1 = 2`. Both `UNION` operands are identical, and `UNION` removes duplicate rows.
