Example: Effect of the Order of SELECT Statements on Data Type - Advanced SQL Engine - Teradata Database

SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
qtb1554762060450.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™

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