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

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
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