MySQL Bugs: #3363: UNION depends on length (for varchar) of first select

Bug #3363	UNION depends on length (for varchar) of first select
Submitted:	1 Apr 2004 19:11	Modified:	2 Apr 2004 2:15
Reporter:	Andy Santosa	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.0.18/4.0.19	OS:	Windows (Windows)
Assigned to:	Sergei Golubchik	CPU Architecture:	Any

Description:
If you have a UNION in SELECT statement for example:

SELECT 't'
UNION
SELECT 'test'

the result will be only 't' ('test' will not appear). It's because the first
SELECT statement the length is only 1 ('t') and the second ('test') is more 
than 1. If the SELECT is swapped:

SELECT 'test'
UNION
SELECT 't'

the result is correct.

The problem is: If I do SELECT with columns, I don't know the length in the first SELECT will be more than the second SELECT.

I have checked in MS SQLServer and it's not a problem.

How to repeat:
SELECT 't'
UNION
SELECT 'test'

The result is:

't'

It should be

't'
'test'

Suggested fix:
The UNION should not check the length but only the data type.

Change Priority to High

Thank you for the bug report I was able to repeat:

:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.18-nt

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

mysql> SELECT 't'
    -> UNION
    -> SELECT 'test';
+---+
| t |
+---+
| t |
+---+
1 row in set (0.10 sec)
However the version 4.1.2 behaves as expected:

C:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.2-alpha-nt

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

mysql> SELECT 't'
    -> UNION
    -> SELECT 'test';
+------+
| t    |
+------+
| t    |
| test |
+------+
2 rows in set (0.17 sec)

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

Additional info:

It's not a bug but a functional limitation in the way UNION was implemented in 4.0
It is fixed in 4.1 already, but the fix is too big and too intrusive to go into the stable branch, so it cannot be ported to 4.0 :(