MySQL Bugs: #73414: SELECT UNION malforms result set

Bug #73414	SELECT UNION malforms result set - returns fewer rows
Submitted:	28 Jul 2014 19:53	Modified:	29 Jul 2014 15:13
Reporter:	Van Stokes	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S1 (Critical)
Version:	5.6.19 x64	OS:	Any (Windows,Linux)
Assigned to:		CPU Architecture:	Any
Tags:	DML, SELECT, UNION

Description:
MySQL: 5.6.19 x64
OS: Ubuntu 12.04.03 LTS x64, Windows 2008R2 x64

When using a UNION and the second SELECT returns null first SELECT result set is being truncated.

I have tested this on several 5.6.19 servers running on both Linux and Windows and I am able to repeat this every time.

How to repeat:
Step 01:

mysql> SELECT db.host, db.db FROM db;

Note the number of rows returned. I get 122 rows - which is correct.

Step 02:

mysql> SELECT db.host, db.db FROM db UNION SELECT NULL, NULL;

Note the number of rows returned is LESS than the first query. I get 58 rows - which is incorrect.

Step 03: (Optional)

mysql> SELECT db.host, db.db FROM db UNION SELECT host.Host, host.Db FROM HOST;

Note the number of rows returned is LESS than the first query. I get 57 rows which is incorrect.

Suggested fix:
In this case, the UNION should not be returning less rows.

Hi,

This is actually not a bug. If you specify UNION without all the addendum ALL, then the entire query will bi a DISTINCT query. That is how all SQL standards have defined it.

So, use : UNION ALL and see if you get what you are looking for.