MySQL Bugs: #3010: UNION does not work

Bug #3010	UNION does not work
Submitted:	28 Feb 2004 15:26	Modified:	29 Feb 2004 17:00
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.1.2	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
Union does not finds all rows in the example (1) below.
Actually, it displays only the rows returned by the second query.

In the second example (2), the explain forces the connection to be closed between the server and the client.

The test is done with the "mysql" tool, with MySQL server 4.1.2-alpha-nightly-20040211 on Linux. (Mandrake 8.1)

How to repeat:
The script (1):
drop table test1;
CREATE TABLE test1 (
  id  int(8),
  name1 varchar(255),
  name2 varchar(255)
) TYPE=InnoDB DEFAULT CHARSET=utf8;
insert into test1 values (1,null,'a');
insert into test1 values (2,'b',null);

select t.id from test1 t where t.name1='b' union select t.id from test1 t where t.name2='b';

The script (2):
drop table test1;
CREATE TABLE test1 (
  id  int(8),
  name1 varchar(255),
  name2 varchar(255),
  UNIQUE KEY unq_name1 (name1),
  UNIQUE KEY unq_name2 (name2)
) TYPE=InnoDB DEFAULT CHARSET=utf8;
insert into test1 values (1,null,'a');
insert into test1 values (2,'b',null);

explain select t.id from test1 t where t.name1='b' union select t.id from test1 t where t.name2='b';

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Additional info:

In your test1
mysql> select * from test1 t;
+------+-------+-------+
| id   | name1 | name2 |
+------+-------+-------+
|    1 | NULL  | a     |
|    2 | b     | NULL  |
+------+-------+-------+

therefore
mysql> select t.id from test1 t where t.name1='b';
+------+
| id   |
+------+
|    2 |
+------+

mysql> select t.id from test1 t where t.name2='b';
Empty set (0.00 sec)

mysql> select t.id from test1 t where t.name1='b' union select t.id from test1 t where t.name2='b';
+------+
| id   |
+------+
|    2 |
+------+

In test2
Table can't be created because:

mysql> CREATE TABLE test1 (
    ->   id  int(8),
    ->   name1 varchar(255),
    ->   name2 varchar(255),
    ->   UNIQUE KEY unq_name1 (name1),
    ->   UNIQUE KEY unq_name2 (name2)
    -> ) TYPE=InnoDB DEFAULT CHARSET=utf8;
ERROR 1071 (42000): Specified key was too long. Max key length is 500

With smaller columns:

mysql> CREATE TABLE test1 (
    ->   id  int(8),
    ->   name1 varchar(128),
    ->   name2 varchar(128),
    ->   UNIQUE KEY unq_name1 (name1),
    ->   UNIQUE KEY unq_name2 (name2)
    -> ) TYPE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> explain select t.id from test1 t where t.name1='b' union select t.id from test1 t where t.name2='b';
+----+-------------+-------+-------+---------------+-----------+---------+-------+------+-----------------------------------------------------+
| id | select_type | table | type  | possible_keys | key       | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+-------+---------------+-----------+---------+-------+------+-----------------------------------------------------+
|  1 | PRIMARY     | t     | const | unq_name1     | unq_name1 |     129 | const |    1 |                                                     |
|  2 | UNION       | NULL  | NULL  | NULL          | NULL      |    NULL | NULL  | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+-------+---------------+-----------+---------+-------+------+-----------------------------------------------------+

OK, I've downloaded, compiled and checked against the latest nightly (20040229).
It works. Seems like the problem has been eliminated already.
You can close this bug.

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

Additional info:

Chance is that this is related to either

bug #2508 http://bugs.mysql.com/bug.php?id=2508
or
bug #2552 http://bugs.mysql.com/bug.php?id=2552

and was fixed along with them.