MySQL Bugs: #45907: weird UNION behaviour

Bug #45907	weird UNION behaviour
Submitted:	2 Jul 2009 12:04	Modified:	2 Jul 2009 12:30
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.1.36, 5.4.1	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I do not get UNION'ed results as I would expect.

How to repeat:
SELECT `host`, `user` FROM mysql.user LIMIT 0,2;
/*
host    user  
------  ------
%       andrew
%       root  
*/

SELECT `host`, `user` FROM mysql.user LIMIT 0,1;
/*
host    user  
------  ------
%       andrew
*/

SELECT `host`, `user` FROM mysql.user LIMIT 1,1;
/*
host    user  
------  ------
%       root      
*/

SELECT `host`, `user` FROM mysql.user  LIMIT 0,1
UNION 
SELECT `host`, `user` FROM mysql.user LIMIT 1,1;
/*
host    user  
------  ------
%       root      
*/

SELECT `host`, `user` FROM mysql.user  LIMIT 0,1
UNION ALL
SELECT `host`, `user` FROM mysql.user LIMIT 1,1;
/*
host    user  
------  ------
%       andrew   
*/

SELECT `host`, `user` FROM mysql.user  LIMIT 0,1
UNION DISTINCT
SELECT `host`, `user` FROM mysql.user LIMIT 1,1;

/*
host    user  
------  ------
%       root   
*/

Suggested fix:
In my understanding all 3 UNION queries should return the same - and the same as the first (LIMIT 0,2) query.

IMO, this is not a bug.
The second LIMIT is read as part of the whole UNIONed  query, not as part of the single one.

Try this:
(SELECT `host`, `user` FROM mysql.user  LIMIT 0,1)
UNION 
(SELECT `host`, `user` FROM mysql.user LIMIT 1,1);
+-----------+----------+
| host      | user     |
+-----------+----------+
| %         | msandbox | 
| localhost | root     | 
+-----------+----------+

What you were doing corresponds to the following:

(SELECT `host`, `user` FROM mysql.user  LIMIT 0,1)
UNION 
(SELECT `host`, `user` FROM mysql.user) LIMIT 1,1;
+-----------+------+
| host      | user |
+-----------+------+
| localhost | root | 
+-----------+------+

OK! I was also somewhat surprised if such bug should exist.  

But the statement is ambiguous at least, I think. I am not sure if such should not raise an error.

I also still do not understand why DISTINCT|ALL produces different results (only that some internal sorting order must be different?)

This is not a bug. The manual explains this case: 
http://dev.mysql.com/doc/refman/5.1/en/union.html

"To use an ORDER BY or LIMIT clause to sort or limit the entire UNION result, parenthesize the individual SELECT statements and place the ORDER BY or LIMIT after the last one. The following example uses both clauses:

(SELECT a FROM t1 WHERE a=10 AND B=1)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2)
ORDER BY a LIMIT 10;
"

With UNION DISTINCT you would say a difference only if the whole query would yield duplicates.
Please use MySQL Forums for further questions.

I have to object to the tone here "Please use MySQL Forums for further questions."

FYI when I posted my last post it was seconds after it was closed as *not a bug*.  I had no chance to see it!

Sorry it sounded harsh. It was not meant to be.
It was not a complaint about your latest comment. It was a suggestion about *further* requests. I Actually saw your additional comment after I closed the bug report.