UNION fails on MyIsam tables when index on second column from same
am 15.02.2004 03:07:39 von Jeremy MarchHi!
Problem: UNION queries fail on MyIsam tables when both selects are from
the same table and there is an index on the column selected from the
second select.
This works fine with innodb tables thanks to your recent bug fix:)
This was tested with mysql-4.1.2, latest changes pulled today.
See the test cases below:
mysql> create table t (col1 tinyint unsigned, col2 tinyint unsigned);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t values (1,2),(3,4),(5,6),(7,8),(9,10);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select col1 n from t union select col2 n from t order by n;
+------+
| n |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+------+
10 rows in set (0.00 sec)
mysql> alter table t add index myindex (col2);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select col1 n from t union select col2 n from t order by n;
+------+
| n |
+------+
| NULL |
| 2 |
| 4 |
| 6 |
| 8 |
| 10 |
+------+
6 rows in set (0.00 sec)
mysql> alter table t modify col1 tinyint unsigned not null, modify col2
tinyint unsigned not null;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select col1 n from t union select col2 n from t order by n;
+------+
| n |
+------+
| 0 |
| 2 |
| 4 |
| 6 |
| 8 |
| 10 |
+------+
6 rows in set (0.00 sec)
mysql> alter table t drop index myindex;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select col1 n from t union select col2 n from t order by n;
+------+
| n |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+------+
10 rows in set (0.00 sec)
--
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