MySQL Bugs: #3397: Wrong Union Behaviour

Bug #3397	Wrong Union Behaviour
Submitted:	5 Apr 2004 23:33	Modified:	16 Apr 2004 8:22
Reporter:	Sergei Kulakov (Candidate Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.18 Source	OS:	Windows (Windows)
Assigned to:		CPU Architecture:	Any

Description:

Studying "13.1.7.2 UNION Syntax" I decided to test the query given in the example:

SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10)

and got the expected

REPEAT('a',1) 
a 
b

since my version 4.0.18 is lower than 4.1.1
Then I, just out of interest, tried this:

SELECT REPEAT('b',10) UNION SELECT REPEAT('a',1)

that is I swapped the expressions. I thought I'd get something like 'bbbbbbbbbb' yet the result was the same as from the first query!

REPEAT('b',10) 
a 
b

Then I tried this:

SELECT REPEAT('b',10) UNION SELECT REPEAT('a',10)

Now both the expressions were equal in length and should be returned. What I got disappointed me a lot:

REPEAT('b',10) 
aaaaaaaaaa
baaaaaaaaa

Here MySQL uses bytes from the second select ('aaaaaaaaaa') to fill those in the first select for all bytes except the first one. Then I did this:

SELECT 123 as A UNION SELECT 456

The result was

A
353 
354 
379 
456

Then SELECT 'AAA' as A UNION SELECT 'CCC' produced

A 
aCC 
bCC 
{CC 
ÈCC 
CCC

Now MySQL started giving some random/binary data, it seemed something got broken in the server and the very first query

SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10)

now produced this:

REPEAT('a',1) 
a 
b 
{ 
È 
C

Any other query of the kind - I also tried triple selects:

SELECT REPEAT('a',10) as A 
UNION SELECT REPEAT('b', 10) 
UNION SELECT REPEAT('c', 10)

produced similar stuff. Note that I tried the same queries with 4.0.1, which is alpha (similarly complied from source), and all of them worked fine.

How to repeat:
See description

Suggested fix:
None

Thank you for the bug report I was able to repeat with 4.0.18
however with a server 4.0.19 built from BK source
I got the expected results:

C:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.19-max-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);
+---------------+
| REPEAT('a',1) |
+---------------+
| a             |
| b             |
+---------------+
2 rows in set (0.01 sec)

mysql> SELECT REPEAT('b',10) UNION SELECT REPEAT('a',1);
+----------------+
| REPEAT('b',10) |
+----------------+
| bbbbbbbbbb     |
| a              |
+----------------+
2 rows in set (0.00 sec)

mysql> SELECT REPEAT('b',10) UNION SELECT REPEAT('a',10);
+----------------+
| REPEAT('b',10) |
+----------------+
| bbbbbbbbbb     |
| aaaaaaaaaa     |
+----------------+
2 rows in set (0.01 sec)

<cut>

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html