Description:
Create a temporary table on the replica (which is read only), it’s being created as MyISAM table. Let’s say the table has 4 varchar fields.
Inserted multiple rows with random values.
Run SELECT and trying to search all of the fields for a value, the query response is as expected. No mater in which of the fields, the value is matched.
Created 4 indexes for each of those fields.
*** The BUG happens ***
RunSELECT for a value in the table, a match is only returned if I am querying a value contained in the FIRST field. If I SELECT for a value in any of the 3 other fields, no results are returned.
When I do EXPLAIN for this SELECT, it shows "Using union” for indexing.
When I switch the “index_merge_union” flag to “off” which is found in system variable “optimizer_switch”, any SELECT query returns correct results.
How to repeat:
Create the temp table
use db1;
create temporary table stat_tmp (
f1 varchar(15) NOT NULL DEFAULT '',
f2 varchar(15) NOT NULL DEFAULT '',
f3 varchar(15) NOT NULL DEFAULT '',
f4 varchar(15) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Insert test records
insert into stat_tmp values ('1', '0', '0', '0');
insert into stat_tmp values ('1', '0', '1', '0');
insert into stat_tmp values ('0', '0', '0', '2');
insert into stat_tmp values ('0', '0', '1', '0');
insert into stat_tmp values ('0', '3', '2', '0');
Test queries
select * from stat_tmp where f1 = '1' or f2 = '1' or f3 = '1' or f4 = '1';
select * from stat_tmp where f1 = '3' or f2 = '3' or f3 = '3' or f4 = '3';
Created indexes
alter table stat_tmp add index (f1), add index (f2), add index (f3), add index (f4);
Run the same queries
select * from stat_tmp where f1 = '1' or f2 = '1' or f3 = '1' or f4 = '1';
returned the same set of records
select * from stat_tmp where f1 = '3' or f2 = '3' or f3 = '3' or f4 = '3';
return nothing
>Turn off index_merge_union
SET SESSION optimizer_switch = 'index_merge_union=off';
The results back to be expected.
This only happened when the temp table is in MyISAM and the data type are char ().
```
One iteration:
==============
```
mysql> use db1;
Database changed
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.41 |
+-----------+
1 row in set (0.00 sec)
mysql> create temporary table stat_tmp (
-> f1 varchar(15) NOT NULL DEFAULT '',
-> f2 varchar(15) NOT NULL DEFAULT '',
-> f3 varchar(15) NOT NULL DEFAULT '',
-> f4 varchar(15) NOT NULL DEFAULT ''
-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into stat_tmp values ('1', '0', '0', '0');
Query OK, 1 row affected (0.00 sec)
mysql> insert into stat_tmp values ('1', '0', '1', '0');
Query OK, 1 row affected (0.00 sec)
mysql> insert into stat_tmp values ('0', '0', '0', '2');
Query OK, 1 row affected (0.00 sec)
mysql> insert into stat_tmp values ('0', '0', '1', '0');
Query OK, 1 row affected (0.00 sec)
mysql> insert into stat_tmp values ('0', '3', '2', '0');
Query OK, 1 row affected (0.00 sec)
mysql> select * from stat_tmp where f1 = '1' or f2 = '1' or f3 = '1' or f4 = '1';
+----+----+----+----+
| f1 | f2 | f3 | f4 |
+----+----+----+----+
| 1 | 0 | 0 | 0 |
| 1 | 0 | 1 | 0 |
| 0 | 0 | 1 | 0 |
+----+----+----+----+
3 rows in set (0.00 sec)
mysql> select * from stat_tmp where f1 = '3' or f2 = '3' or f3 = '3' or f4 = '3';
+----+----+----+----+
| f1 | f2 | f3 | f4 |
+----+----+----+----+
| 0 | 3 | 2 | 0 |
+----+----+----+----+
1 row in set (0.00 sec)
mysql> alter table stat_tmp add index (f1), add index (f2), add index (f3), add index (f4);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from stat_tmp where 1 and (f1 = '1' or f2 = '1' or f3 = '1' or f4 = '1');
+----+----+----+----+
| f1 | f2 | f3 | f4 |
+----+----+----+----+
| 1 | 0 | 0 | 0 |
| 1 | 0 | 1 | 0 |
| 0 | 0 | 1 | 0 |
+----+----+----+----+
3 rows in set (0.00 sec)
mysql> select * from stat_tmp where f1 = '3' or f2 = '3' or f3 = '3' or f4 = '3';
Empty set (0.00 sec)
mysql> SET SESSION optimizer_switch = 'index_merge_union=off';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from stat_tmp where f1 = '3' or f2 = '3' or f3 = '3' or f4 = '3';
+----+----+----+----+
| f1 | f2 | f3 | f4 |
+----+----+----+----+
| 0 | 3 | 2 | 0 |
+----+----+----+----+
1 row in set (0.00 sec)
```
Description: Create a temporary table on the replica (which is read only), it’s being created as MyISAM table. Let’s say the table has 4 varchar fields. Inserted multiple rows with random values. Run SELECT and trying to search all of the fields for a value, the query response is as expected. No mater in which of the fields, the value is matched. Created 4 indexes for each of those fields. *** The BUG happens *** RunSELECT for a value in the table, a match is only returned if I am querying a value contained in the FIRST field. If I SELECT for a value in any of the 3 other fields, no results are returned. When I do EXPLAIN for this SELECT, it shows "Using union” for indexing. When I switch the “index_merge_union” flag to “off” which is found in system variable “optimizer_switch”, any SELECT query returns correct results. How to repeat: Create the temp table use db1; create temporary table stat_tmp ( f1 varchar(15) NOT NULL DEFAULT '', f2 varchar(15) NOT NULL DEFAULT '', f3 varchar(15) NOT NULL DEFAULT '', f4 varchar(15) NOT NULL DEFAULT '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Insert test records insert into stat_tmp values ('1', '0', '0', '0'); insert into stat_tmp values ('1', '0', '1', '0'); insert into stat_tmp values ('0', '0', '0', '2'); insert into stat_tmp values ('0', '0', '1', '0'); insert into stat_tmp values ('0', '3', '2', '0'); Test queries select * from stat_tmp where f1 = '1' or f2 = '1' or f3 = '1' or f4 = '1'; select * from stat_tmp where f1 = '3' or f2 = '3' or f3 = '3' or f4 = '3'; Created indexes alter table stat_tmp add index (f1), add index (f2), add index (f3), add index (f4); Run the same queries select * from stat_tmp where f1 = '1' or f2 = '1' or f3 = '1' or f4 = '1'; returned the same set of records select * from stat_tmp where f1 = '3' or f2 = '3' or f3 = '3' or f4 = '3'; return nothing >Turn off index_merge_union SET SESSION optimizer_switch = 'index_merge_union=off'; The results back to be expected. This only happened when the temp table is in MyISAM and the data type are char (). ``` One iteration: ============== ``` mysql> use db1; Database changed mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.41 | +-----------+ 1 row in set (0.00 sec) mysql> create temporary table stat_tmp ( -> f1 varchar(15) NOT NULL DEFAULT '', -> f2 varchar(15) NOT NULL DEFAULT '', -> f3 varchar(15) NOT NULL DEFAULT '', -> f4 varchar(15) NOT NULL DEFAULT '' -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.00 sec) mysql> insert into stat_tmp values ('1', '0', '0', '0'); Query OK, 1 row affected (0.00 sec) mysql> insert into stat_tmp values ('1', '0', '1', '0'); Query OK, 1 row affected (0.00 sec) mysql> insert into stat_tmp values ('0', '0', '0', '2'); Query OK, 1 row affected (0.00 sec) mysql> insert into stat_tmp values ('0', '0', '1', '0'); Query OK, 1 row affected (0.00 sec) mysql> insert into stat_tmp values ('0', '3', '2', '0'); Query OK, 1 row affected (0.00 sec) mysql> select * from stat_tmp where f1 = '1' or f2 = '1' or f3 = '1' or f4 = '1'; +----+----+----+----+ | f1 | f2 | f3 | f4 | +----+----+----+----+ | 1 | 0 | 0 | 0 | | 1 | 0 | 1 | 0 | | 0 | 0 | 1 | 0 | +----+----+----+----+ 3 rows in set (0.00 sec) mysql> select * from stat_tmp where f1 = '3' or f2 = '3' or f3 = '3' or f4 = '3'; +----+----+----+----+ | f1 | f2 | f3 | f4 | +----+----+----+----+ | 0 | 3 | 2 | 0 | +----+----+----+----+ 1 row in set (0.00 sec) mysql> alter table stat_tmp add index (f1), add index (f2), add index (f3), add index (f4); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> select * from stat_tmp where 1 and (f1 = '1' or f2 = '1' or f3 = '1' or f4 = '1'); +----+----+----+----+ | f1 | f2 | f3 | f4 | +----+----+----+----+ | 1 | 0 | 0 | 0 | | 1 | 0 | 1 | 0 | | 0 | 0 | 1 | 0 | +----+----+----+----+ 3 rows in set (0.00 sec) mysql> select * from stat_tmp where f1 = '3' or f2 = '3' or f3 = '3' or f4 = '3'; Empty set (0.00 sec) mysql> SET SESSION optimizer_switch = 'index_merge_union=off'; Query OK, 0 rows affected (0.00 sec) mysql> select * from stat_tmp where f1 = '3' or f2 = '3' or f3 = '3' or f4 = '3'; +----+----+----+----+ | f1 | f2 | f3 | f4 | +----+----+----+----+ | 0 | 3 | 2 | 0 | +----+----+----+----+ 1 row in set (0.00 sec) ```