Skip to Content
-1

Unex​pected behavior of UNION operator

Feb 04 at 02:49 PM

53

avatar image

Hi, I encounter an unexpected behavior of UNION operator.

It unions columns based on the order of columns of the first table, instead of union by column name.

for example- this statement returns expected results of column A: 100, 200

and column B: 1, 2

SELECT A,B FROM (SELECT 100 AS "A", 1 AS "B" FROM DUMMY)A 

UNION 

SELECT A,B FROM (SELECT 2 AS "B", 200 AS "A" FROM DUMMY)B

but below queries return unexpected behavior, mixing the columns

(e.g. column A: 100, 2 and column B: 1, 200)

SELECT 100 AS "A", 1 AS "B" FROM DUMMY 
UNION 
SELECT 2 AS "B", 200 AS "A" FROM DUMMY; 


SELECT * FROM (SELECT 100 AS "A", 1 AS "B" FROM DUMMY)A 
UNION 
SELECT * FROM (SELECT 2 AS "B", 200 AS "A" FROM DUMMY)B; 

(using HANA version 1.00.102.00.1442292917)

union-bug.jpg (56.0 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Florian Pfeffer
Feb 04 at 05:31 PM
1

This is not bug. It works as the UNION statement is designed. It does not union by column name, but by column order. As long as the types are convertible the values can be unioned. The colum names are taken from the first select statement in the union chain.

In your last statement you get "your expected result", because you do an explicit re-ordering of the columns.

Regards,
Florian

Share
10 |10000 characters needed characters left characters exceeded
Zahid Yener Feb 05 at 05:15 AM
-2

Florian is right. You should also be careful using UNION statement. Check out the difference between UNION and UNION ALL as well.

Share
10 |10000 characters needed characters left characters exceeded