Union doesn't work on single innodb table

Incomplete 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/[email protected]