Details
Major Description
The test case from MDEV-29014 fails on bb-11.0, but on 10.5-10.11 failure is not reproducible after 5fc172fd43 (Merge branch '10.5' into bb-10.5-release)--most likely after fixing MDEV-28846 ( Poor performance when rowid filter contains no elements)
--source include/have_partition.inc
|
|
CREATE TABLE t1 (a INT); |
INSERT INTO t1 () VALUES (1),(NULL),(2); |
|
CREATE TABLE t2 (b INT, c INT, KEY(b), KEY(c)) PARTITION BY HASH(b); |
INSERT INTO t2 VALUES (NULL,4),(NULL,6),(7,8); |
|
SELECT * FROM t1 WHERE a NOT IN (SELECT c FROM t2 WHERE a IN (SELECT c FROM t2) AND b != 9); |
|
# Cleanup
|
DROP TABLE t1, t2; |
|
bb-11.0 527cc3e2c51e5bfb6899dd6bc65c |
mariadbd: /bb-11.0/sql/handler.h:4656: virtual void handler::disable_pushed_rowid_filter(): Assertion `pushed_rowid_filter != __null && save_pushed_rowid_filter == __null' failed.
|
230207 10:38:45 [ERROR] mysqld got signal 6 ;
|
|
|
Server version: 11.0.1-MariaDB-debug-log source revision: 527cc3e2c51e5bfb6899dd6bc65c25f92f0fe0bc
|
|
|
??:0(__assert_fail)[0x7fadabacffd6]
|
sql/handler.h:4658(handler::disable_pushed_rowid_filter())[0x560b0de12a60]
|
sql/item_subselect.cc:4099(subselect_single_select_engine::exec())[0x560b0e26e0ba]
|
sql/item_subselect.cc:812(Item_subselect::exec())[0x560b0e24a0b1]
|
sql/item_subselect.cc:994(Item_in_subselect::exec())[0x560b0e24b6b3]
|
sql/item_subselect.cc:1947(Item_in_subselect::val_bool())[0x560b0e25652f]
|
sql/item.h:1791(Item::val_bool_result())[0x560b0d305700]
|
sql/item_cmpfunc.cc:1637(Item_in_optimizer::val_int())[0x560b0e095116]
|
sql/item.h:1787(Item::val_int_result())[0x560b0d3055a4]
|
sql/item.cc:10166(Item_cache_int::cache_value())[0x560b0e064c2e]
|
sql/item.cc:8921(Item_cache_wrapper::cache())[0x560b0e07c6bd]
|
sql/item.cc:9107(Item_cache_wrapper::val_bool())[0x560b0e05be2f]
|
sql/item_cmpfunc.cc:203(Item_func_not::val_int())[0x560b0e0842fb]
|
sql/sql_select.cc:22858(evaluate_join_record(JOIN*, st_join_table*, int))[0x560b0d8573e1]
|
sql/sql_select.cc:22794(sub_select(JOIN*, st_join_table*, bool))[0x560b0d856ccb]
|
sql/sql_select.cc:22289(do_select(JOIN*, Procedure*))[0x560b0d8543d2]
|
sql/sql_select.cc:4864(JOIN::exec_inner())[0x560b0d7d59ec]
|
sql/sql_select.cc:4643(JOIN::exec())[0x560b0d7d2eec]
|
sql/sql_select.cc:5124(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x560b0d7d745b]
|
sql/sql_select.cc:608(handle_select(THD*, LEX*, select_result*, unsigned long long))[0x560b0d7a73a1]
|
sql/sql_parse.cc:6265(execute_sqlcom_select(THD*, TABLE_LIST*))[0x560b0d6ca8d5]
|
sql/sql_parse.cc:3949(mysql_execute_command(THD*, bool))[0x560b0d6b91ce]
|
sql/sql_parse.cc:8000(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x560b0d6d5658]
|
sql/sql_parse.cc:1896(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x560b0d6ab904]
|
sql/sql_parse.cc:1407(do_command(THD*, bool))[0x560b0d6a8640]
|
sql/sql_connect.cc:1416(do_handle_one_connection(CONNECT*, bool))[0x560b0db72463]
|
sql/sql_connect.cc:1320(handle_one_connection)[0x560b0db71dc0]
|
perfschema/pfs.cc:2203(pfs_spawn_thread)[0x560b0e7ac70e]
|
nptl/pthread_create.c:478(start_thread)[0x7fadabfea609]
|
|
|
Query (0x6290000e62a8): SELECT * FROM t1 WHERE a NOT IN (SELECT c FROM t2 WHERE a IN (SELECT c FROM t2) AND b != 9)
|
|
|
explain extended SELECT * FROM t1 WHERE a NOT IN (SELECT c FROM t2 WHERE a IN (SELECT c FROM t2) AND b != 9);
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
|
2 DEPENDENT SUBQUERY t2 ref c c 5 test.t1.a 1 100.00 Using index; FirstMatch
|
2 DEPENDENT SUBQUERY t2 ref_or_null|filter b,c c|b 5|5 func 2 (67%) 66.67 Using where; Full scan on NULL key; Using rowid filter
|
Warnings:
|
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `test`.`t2`.`c` from `test`.`t2` semi join (`test`.`t2`) where `test`.`t2`.`b` <> 9 and `test`.`t1`.`a` = `test`.`t2`.`c` and trigcond(<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c` or `test`.`t2`.`c` is null) having trigcond(`test`.`t2`.`c` is null))))
|
Attachments
Activity
Once this is reviewed: it probably affects the versions below 11.0 too. Need to find the least version (10.4 where rowid filter was introduced?) and push the fix where.
10.4 doesn't crash but does show incorrect explain (shows "Using rowid filter" where it won't be used).
A testcase that shows this problem on the current 10.11 vanilla:
create table t1 (a int);insert into t1 values (NULL),(1),(2);create table t2 (a int);insert into t2 select seq from seq_1_to_1000;create table t3 (a1 int,a2 int,b int,c int,filler1 char(200),filler2 char(200),key(a1,a2),key(b)) partition by hash(a1) partitions 2;insert into t3 select seq/100, seq/100, seq, seq, seq, seq from seq_1_to_10000;analyze table t3 persistent for all;set optimizer_switch='materialization=off';explainselect * from t1 where t1.a not in (select straight_join t3.a1 from t2, t3 where t3.b < 3000 and t3.a2=t2.a);+------+--------------------+-------+--------------------+---------------+------+---------+---------------+-----------+--------------------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+------+--------------------+-------+--------------------+---------------+------+---------+---------------+-----------+--------------------------------------------------------+| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 3 | Using where || 2 | DEPENDENT SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 1000 | Using where || 2 | DEPENDENT SUBQUERY | t3 | ref_or_null|filter | a1,b | a1|b | 10|5 | func,j12.t2.a | 198 (31%) | Using where; Full scan on NULL key; Using rowid filter |+------+--------------------+-------+--------------------+---------------+------+---------+---------------+-----------+--------------------------------------------------------+select * from t1 where t1.a not in (select straight_join t3.a1 from t2, t3 where t3.b < 3000 and t3.a2=t2.a);<Assertion failure in the same place as in the report>