The result of any UNION is always expressed using the data type of the selected value of the first SELECT. This means that SELECT A UNION SELECT B does not always return the same result as SELECT B UNION SELECT A unless you explicitly convert the output data type to ensure the same result in either case.
Consider the following complex unioned queries:
SELECT MIN(X8.i1) FROM t8 X8 LEFT JOIN t1 X1 ON X8.i1=X1.i1 AND X8.i1 IN (SELECT COUNT(*) FROM t8 X8 LEFT JOIN t1 X1 ON X8.i1=X1.i1 AND X8.i1 = ANY (SELECT COUNT(*) FROM t7 X7 WHERE X7.i1 = ANY (SELECT AVG(X1.i1) FROM t1 X1))) UNION SELECT AVG(X4.i1) FROM t4 X4 WHERE X4.i1 = ANY (SELECT (X8.i1) FROM t1 X1 RIGHT JOIN t8 X8 ON X8.i1=X1.i1 AND X8.i1 = IN (SELECT MAX(X8.i1) FROM t8 X8 LEFT JOIN t1 X1 ON X8.i1=X1.i1 AND (SELECT (X4.i1) FROM t6 X6 RIGHT JOIN t4 X4 ON X6.i1=i1))));
The result is the following report.
Minimum(i1) ----------- -2 0
You might intuitively expect that reversing the order of the queries on either side of the UNION would produce the same result. Because the data types of the selected value of the first SELECT can differ, this is not always true, as the following query on the same database demonstrates.
SELECT AVG(X4.i1) FROM t4 X4 WHERE X4.i1 = ANY (SELECT (X8.i1) FROM t1 X1 RIGHT JOIN t8 X8 ON X8.i1 = X1.i1 AND X8.i1 = ANY (SELECT MAX(X8.i1) FROM t8 X8 LEFT JOIN t1 X1 ON X8.i1 = X1.i1 AND (SELECT (X4.i1) FROM t6 X6 RIGHT JOIN t4 X4 ON X6.i1 = i ) ) ) UNION SELECT MIN(X8.i1) FROM t8 X8 LEFT JOIN t1 X1 ON X8.i1 = X1.i1 AND X8.i1 IN (SELECT COUNT(*) FROM t8 X8 LEFT JOIN t1 X1 ON X8.i1 = X1.i1 AND X8.i1 = ANY (SELECT COUNT(*) FROM t7 X7 WHERE X7.i1 = ANY (SELECT AVG(X1.i1) FROM t1 X1 ) );
The result is the following report.
Average(i1) ----------- -2 1
The actual average is < 0.5. Why the difference when the order of SELECTs in the UNION is reversed? The following table explains the seemingly paradoxical results.
WHEN the first SELECT specifies this function … | The result data type is … | AND the value returned as the result is … |
---|---|---|
AVG | REAL | 1 |
MIN | INTEGER | truncated to 0 |