status: Open [ 1 ] => Resolved [ 5 ]

resolution: Won't Fix [ 2 ]

description: the following sql-statement should return 2 rows, but actually returns only 1
in a more complex query it can be dangerous:
if rows from different tables return the same values, UNION acts as a GROUP BY clause,
I think, this is not correct

---------------------------------------------
SELECT FieldX as Value FROM TABLE_A
UNION
SELECT FieldY as Value FROM TABLE_B

you can test immediatly - should return 2 rows too:
------------------------------------------------
SELECT 1 FROM RDB$DATABASE
UNION
SELECT 1 FROM RDB$DATABASE

=>

a simple union statement
----------------------------------
SELECT FieldX as Value FROM TABLE_A
UNION
SELECT FieldY as Value FROM TABLE_B

for example:
-------------------------------
TABLE_A has 2 rows with the same value in FieldX
TABLE_B has 1 row with another value in FieldY

The single statement "SELECT FieldX as Value FROM TABLE_A" returns 2 rows
The single statement "SELECT FieldY as Value FROM TABLE_B" returns 1 row
But the above UNION statement only returns 2 rows??

------------------------------------------------
That means, it acts implizit like:

SELECT DISTINCT FieldX as Value FROM TABLE_A
UNION
SELECT DISTINCT FieldY as Value FROM TABLE_B

I think, this is not correct, in a more complex query it can be very dangerous.

you can test it immediatly - not exactly the same, but in my opinion a bug too:
------------------------------------------------
SELECT 1, RDB$RELATION_ID FROM RDB$DATABASE
UNION
SELECT 1, RDB$RELATION_ID FROM RDB$DATABASE

compare to
------------------------------------------------
SELECT 1, RDB$RELATION_ID FROM RDB$DATABASE
UNION
SELECT 2, RDB$RELATION_ID FROM RDB$DATABASE

summary: UNION works implizit as GROUP BY => UNION works implizit with DISTINCT

status: Resolved [ 5 ] => Closed [ 6 ]