Union doesn"t work on single innodb table
am 25.01.2004 04:11:51 von Jeremy MarchIncomplete results are returned when the union of two columns of the same
innodb table is selected.
It works fine when the same union is performed on a myisam table.
It also works fine when selecting a union from two separate innodb tables.
I'm using mysql 4.1.1 on red hat linux 9.0.
create table t1 (col1 tinyint unsigned, col2 tinyint unsigned) type=innodb;
create table t2 (col1 tinyint unsigned, col2 tinyint unsigned) type=innodb;
insert into t1 values (1,2),(3,4),(5,6),(7,8),(9,10);
insert into t2 values (1,2),(3,4),(5,6),(7,8),(9,10);
This is the bug. It should return 10 rows:
select col1 n from t1 union select col2 n from t1 order by n;
+------+
| n |
+------+
| NULL |
| 2 |
| 4 |
| 6 |
| 8 |
| 10 |
+------+
6 rows in set (0.00 sec)
Works fine with two innodb tables:
select col1 n from t1 union select col2 n from t2 order by n;
+------+
| n |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+------+
10 rows in set (0.00 sec)
Works fine with a single myisam table:
alter table t1 type=myisam;
select col1 n from t1 union select col2 n from t1 order by n;
+------+
| n |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+------+
10 rows in set (0.00 sec)
Thanks,
Jeremy March
____________________________________________________________ _____
There are now three new levels of MSN Hotmail Extra Storage! Learn more.
http://join.msn.com/?pgmarket=en-us&page=hotmail/es2&ST=1
--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org