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
10.4 doesn't crash but does show incorrect explain (shows "Using rowid filter" where it won't be used).
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.
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';
|
explain
|
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);
|
+------+--------------------+-------+--------------------+---------------+------+---------+---------------+-----------+--------------------------------------------------------+
|
| 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>
|
|
|
Got ok to push