Fix inconsistent datatypes in calculation after pushing joins through UNION ALL

Details

Detail name Value
Changelog Number 32341
Type Bug
Status Open
Affected Versions Exasol 8.34.0, Exasol 2025.1.0, Exasol 2025.2.0, Exasol 2026.1.0

Description

If a query has the following conditions and the Compiler pushes the join through the UNION ALL, then in very rare cases the results can be different because of expression evaluation via different datatypes.

  1. The query has a UNION ALL.
  2. The UNION ALL appears in a join with at least one other table.
  3. The conditions for pushing joins through UNION ALL apply.
  4. The UNION ALL projects a column which has a different datatype projected from different table operands. Note that this can be the same datatype name with different type parameters (e.g. both DECIMAL but with different precisions).
  5. After optimization, the table operands calculate complex expressions for this column.
  6. The result of the complex expressions depends on the datatype of its intermediate results.

For (6), for example, evaluation of addition of the same values in DECIMAL and DOUBLE can give different results because of loss of precision, as in Q1 below.

Example

-- Setup
CREATE TABLE t1 (c1 DECIMAL(9,3));
INSERT INTO t1 VALUES (1.234);
CREATE TABLE t2 (c1 DECIMAL(18,9));
INSERT INTO t2 VALUES (2.468);
CREATE TABLE t3 (
    k        DECIMAL(18,9),
    expected TIMESTAMP(9),
    flag     DECIMAL(1,0)
);
INSERT INTO t3 VALUES (1.234, FROM_POSIX_TIME(1.234000001), 1);
INSERT INTO t3 VALUES (2.468, FROM_POSIX_TIME(2.468000001), 1);

-- Q1
-- Expected: 2 rows, values (1.234), (2.468)
-- Observed: 1 row, values (2.468)
SELECT u.x
FROM (
    SELECT c1 FROM t1
    UNION ALL
    SELECT c1 FROM t2
) AS u(x)
INNER JOIN t3
    ON  u.x = t3.k
    AND ADD_SECONDS(FROM_POSIX_TIME(u.x), 0.000000001) = t3.expected
    AND t3.flag = 1;

Note that the observed results above will be the same as if you rewrite the query as below, “pushing” the join through the UNION ALL:

-- Q2
-- Observed: 1 row, values (2.468)
SELECT u.x
FROM (
    SELECT c1 FROM t1
) AS u(x)
INNER JOIN t3
    ON  u.x = t3.k
    AND ADD_SECONDS(FROM_POSIX_TIME(u.x), 0.000000001) = t3.expected
    AND t3.flag = 1
UNION ALL
SELECT u.x
FROM (
    SELECT c1 FROM t2
) AS u(x)
INNER JOIN t3
    ON  u.x = t3.k
    AND ADD_SECONDS(FROM_POSIX_TIME(u.x), 0.000000001) = t3.expected
    AND t3.flag = 1;

Workaround

  1. Use ORDER BY FALSE in the subquery with the UNION ALL:
  2. Use explicit casts:
  3. Disable the optimization that pushes joins into unions. Please contact support for details.
SELECT u.x
FROM (
    SELECT c1 FROM t1
    UNION ALL
    SELECT c1 FROM t2
    ORDER BY FALSE
) AS u(x)
INNER JOIN t3
    ON  u.x = t3.k
    AND ADD_SECONDS(FROM_POSIX_TIME(u.x), 0.000000001) = t3.expected
    AND t3.flag = 1;
SELECT u.x
FROM (
    SELECT CAST(c1 AS DECIMAL(18,9)) FROM t1
    UNION ALL
    SELECT c1 FROM t2
) AS u(x)
INNER JOIN t3
    ON  u.x = t3.k
    AND ADD_SECONDS(FROM_POSIX_TIME(u.x), 0.000000001) = t3.expected
    AND t3.flag = 1;

Fix

The query returns the correct results.