MariaDB [bug836491]> explain select * from t100 where (a,b) in (select a,b from t101);
+----+-------------+-------------+--------+---------------+--------------+---------+-----------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+--------+---------------+--------------+---------+-----------+------+-------+
| 1 | PRIMARY | t100 | ALL | NULL | NULL | NULL | NULL | 10 | |
| 1 | PRIMARY | <subquery2> | eq_ref | distinct_key | distinct_key | 10 | func,func | 1 | |
| 2 | SUBQUERY | t101 | ALL | NULL | NULL | NULL | NULL | 10 | |
+----+-------------+-------------+--------+---------------+--------------+---------+-----------+------+-------+
3 rows in set (0.03 sec)
Breakpoint 21, create_tmp_table (thd=0xba91280, param=0xbaf535c, fields=@0xbaf53d8, group=0x0, distinct=true, save_sum_fields=true, select_options=2147768832, rows_limit=18446744073709551615, table_alias=0x89ac970 "sj-materialize", do_not_open=false) at sql_select.cc:13130
(gdb) fini
Run till exit from #0 create_tmp_table (thd=0xba91280, param=0xbaf535c, fields=@0xbaf53d8, group=0x0, distinct=true, save_sum_fields=true, select_options=2147768832, rows_limit=18446744073709551615, table_alias=0x89ac970 "sj-materialize", do_not_open=false) at sql_select.cc:13130
0x08424f59 in setup_sj_materialization_part1 (sjm_tab=0xbb18b7c) at opt_subselect.cc:2959
Value returned is $77 = (TABLE *) 0xbb1a1f0
(gdb) p sjm->table->key_info[0].key_part[0].fieldnr
$80 = 0
(gdb) p sjm->table->key_info[0].key_part[1].fieldnr
$81 = 0
(gdb) p sjm->table->key_info[0].key_parts
$82 = 2
One can see that the key has two key parts, and key_part[1].fieldnr==key_part[0].fieldnr, which is apparently wrong (a column can't be present in the key two times)
It seems, the problem is that KEY_PART::fieldnr is not set for temporary tables. It is most apparent when materialization table's key has two columns:
MariaDB [bug836491]> create table t100 (a int, b int) ;
Query OK, 0 rows affected (0.12 sec)
MariaDB [bug836491]> insert into t100 select a,a from test.ten;
Query OK, 10 rows affected (0.05 sec)
Records: 10 Duplicates: 0 Warnings: 0
MariaDB [bug836491]> create table t101 as select * from t100;
Query OK, 10 rows affected (0.09 sec)
Records: 10 Duplicates: 0 Warnings: 0
MariaDB [bug836491]> explain select * from t100 where (a,b) in (select a,b from t101); ------- -----+- ------- -----+- ------- +------ ------- --+---- ------- ---+--- ------+ ------- ----+-- ----+-- -----+ ------- -----+- ------- -----+- ------- +------ ------- --+---- ------- ---+--- ------+ ------- ----+-- ----+-- -----+ ------- -----+- ------- -----+- ------- +------ ------- --+---- ------- ---+--- ------+ ------- ----+-- ----+-- -----+
+----+-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-
| 1 | PRIMARY | t100 | ALL | NULL | NULL | NULL | NULL | 10 | |
| 1 | PRIMARY | <subquery2> | eq_ref | distinct_key | distinct_key | 10 | func,func | 1 | |
| 2 | SUBQUERY | t101 | ALL | NULL | NULL | NULL | NULL | 10 | |
+----+-
3 rows in set (0.03 sec)
Breakpoint 21, create_tmp_table (thd=0xba91280, param=0xbaf535c, fields=@0xbaf53d8, group=0x0, distinct=true, save_sum_ fields= true, select_ options= 2147768832, rows_limit= 184467440737095 51615, table_alias= 0x89ac970 "sj-materialize", do_not_open=false) at sql_select.cc:13130 fields= true, select_ options= 2147768832, rows_limit= 184467440737095 51615, table_alias= 0x89ac970 "sj-materialize", do_not_open=false) at sql_select.cc:13130 materialization _part1 (sjm_tab=0xbb18b7c) at opt_subselect. cc:2959
(gdb) fini
Run till exit from #0 create_tmp_table (thd=0xba91280, param=0xbaf535c, fields=@0xbaf53d8, group=0x0, distinct=true, save_sum_
0x08424f59 in setup_sj_
Value returned is $77 = (TABLE *) 0xbb1a1f0
(gdb) p sjm->table- >key_info[ 0].key_ part[0] .fieldnr >key_info[ 0].key_ part[1] .fieldnr >key_info[ 0].key_ parts
$80 = 0
(gdb) p sjm->table-
$81 = 0
(gdb) p sjm->table-
$82 = 2
One can see that the key has two key parts, and key_part[ 1].fieldnr= =key_part[ 0].fieldnr, which is apparently wrong (a column can't be present in the key two times)