Example: Effect of the Order of SELECT Statements on Data Type - Teradata Database - Teradata Vantage NewSQL Engine

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.