MySQL Bugs: #41239: The union of two queries returns one row

Bug #41239	The union of two queries returns one row
Submitted:	4 Dec 2008 18:55	Modified:	4 Dec 2008 20:59
Reporter:	Garvin Casimir	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S2 (Serious)
Version:	5.0.45	OS:	Microsoft Windows (windows xp)
Assigned to:		CPU Architecture:	Any
Tags:	combine, number, query, UNION

Description:
When doing a union of two results which the same result only one row is returned.

How to repeat:
Run any of these queries:
select 1 as t
union
select 1 as t

select 'd' as t
union
select 'd' as t

Both these only return one row where as by the definition of a union two rows should exist in the result set.

For a comparison: The following has 2 rows in the result set

select 1 as t
union
select 2 as t

 

Suggested fix:
I don't know if this is an intended feature of the union functionality of mysql server but I'd suggest that a union query even if both results have the same value show distinct rows for each select statement.

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Look at UNION ALL ...

Thanks, I completely forgot about the union all function. My apologies.