Scalar subqueries can lead to wrong results in combination with UNION and VALUES clause

Detail name	Value
Changelog Number	19606
Type	Bug
Status	Resolved
Affected Versions	Exasol 8.11.0
Fix Versions	Exasol 8.32.0
Resolution Date	2024-11-08

Description

In some cases with a filter containing a scalar subquery on a UNION ALL wrong results may occur. The results are wrong in a way that multiple or all union operands have no results such that the result of the UNION operation is empty or too small.

The following conditions trigger this problem:

• The query contains a UNION or UNION ALL.
• There is one filter on the UNION ALL that - when put into one of the operands - contains only constants and evaluates to FALSE.
• There is another filter F on the UNION ALL that contains a scalar subquery.
• The subquery that evaluates to FALSE contains another subquery which exports the columns used in the filter F and needs to be materialized.
• The scalar subquery contains a VALUES clause.

Preparation

CREATE SCHEMA test;
CREATE TABLE t1(d DATE, i INT);
CREATE TABLE t2(d DATE, i INT);
CREATE TABLE dim(i INT);
INSERT INTO t1 VALUES (DATE'2000-01-01', 1);
INSERT INTO t2 VALUES (DATE'2002-01-01', 2);
CREATE TABLE lu(d DATE);
INSERT INTO lu VALUES DATE'2001-01-01';

Example

-- Observed: empty set
-- Expected: | 2000-01-01 | abc |
SELECT * FROM
(
SELECT t1.d AS rd, CAST('abc' AS CHAR(10)) x FROM t1
UNION ALL
SELECT d AS rd, CAST('def' AS CHAR(10)) x FROM 
(SELECT d FROM t2 GROUP BY d) t2
)
WHERE 
rd <= (SELECT max(d) FROM (VALUES (DATE'2001-01-01') AS lu(d)))
AND x='abc'
;

Workaround

Remove one of the above conditions. For example, remove the VALUES clause.

SELECT * FROM
(
SELECT t1.d AS rd, CAST('abc' AS CHAR(10)) x FROM t1
UNION ALL
SELECT d AS rd, CAST('def' AS CHAR(10)) x FROM 
(SELECT d FROM t2 GROUP BY d) t2
)
WHERE 
rd <= (select max(d) from lu)
AND x='abc'
;

Fix

Those queries run as expected.