Bug #2809 UNION fails on MyIsam tables when index on second column from same table

• Submitted: 15 Feb 2004 11:07
• Modified: 16 Feb 2004 16:53
• Reporter: Sergei Golubchik
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1.2
• Assigned to: Oleksandr Byelkin
• CPU Architecture: Any

[15 Feb 2004 11:07] Sergei Golubchik

Description:
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.

How to repeat:
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)

[16 Feb 2004 15:50] Oleksandr Byelkin

ChangeSet 
  1.1710 04/02/17 01:14:51 [e-mail] +15 -0 
  ulternative bugfix for BUG#2508 and fix for BUG#2809 - every table has its 
own TABLE structure

[16 Feb 2004 16:53] Oleksandr Byelkin

Thank you for bug report. bugfix is pushed in our internal repository and will 
be avaliable in next release