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
| Field | Original Value | New Value |
|---|---|---|
| Fix Version/s | 11.0 [ 28320 ] |
| Affects Version/s | N/A [ 14700 ] |
| Assignee | Sergei Petrunia [ psergey ] |
| Labels | rowid_filtering |
Note: for non-partitioned table, the query plan is the same but there's no crash.
Do partitioned tables even support rowid filtering? IIRC they do not support Index Condition Pushdown...
ha_partition indicates that it does support Rowid Filter Pushdown:
In TABLE::can_use_rowid_filter
|
(gdb) p this->file
|
$3 = (ha_partition *) 0x7ffc68945b10
|
(gdb) p (key_info[index].index_flags & HA_DO_RANGE_FILTER_PUSHDOWN)
|
$4 = 1024
|
(gdb) p (key_info[index].index_flags & HA_CLUSTERED_INDEX)
|
$5 = 0
|
But ha_partition does not overload rowid_filter_push(), it inherits handler::rowid_filter_push() which does nothing.
... and yet, the SQL layer thinks that the table is using the rowid filter. subselect_single_select_engine::exec tries to disable the rowid filter :
if (cond_guard && !*cond_guard) |
{
|
/* Change the access method to full table scan */ |
tab->save_read_first_record= tab->read_first_record;
|
tab->save_read_record= tab->read_record.read_record_func;
|
tab->read_record.read_record_func= rr_sequential;
|
tab->read_first_record= read_first_record_seq;
|
if (tab->rowid_filter) |
=> tab->table->file->disable_pushed_rowid_filter();
|
and inside that call we hit the assert:
virtual void disable_pushed_rowid_filter() |
{
|
=> DBUG_ASSERT(pushed_rowid_filter != NULL &&
|
save_pushed_rowid_filter == NULL);
|
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>
|
|
|
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.
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Assignee | Sergei Petrunia [ psergey ] | Michael Widenius [ monty ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Affects Version/s | 11.0 [ 28320 ] | |
| Affects Version/s | N/A [ 14700 ] |
| Affects Version/s | 10.4 [ 22408 ] | |
| Affects Version/s | 10.5 [ 23123 ] | |
| Affects Version/s | 10.6 [ 24028 ] | |
| Affects Version/s | 10.7 [ 24805 ] | |
| Affects Version/s | 10.8 [ 26121 ] | |
| Affects Version/s | 10.9 [ 26905 ] | |
| Affects Version/s | 10.10 [ 27530 ] |
10.4 doesn't crash but does show incorrect explain (shows "Using rowid filter" where it won't be used).
| Assignee | Michael Widenius [ monty ] | Sergei Petrunia [ psergey ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Fix Version/s | 10.4 [ 22408 ] | |
| Fix Version/s | 10.5 [ 23123 ] | |
| Fix Version/s | 10.6 [ 24028 ] | |
| Fix Version/s | 10.7 [ 24805 ] | |
| Fix Version/s | 10.8 [ 26121 ] | |
| Fix Version/s | 10.9 [ 26905 ] | |
| Fix Version/s | 10.10 [ 27530 ] | |
| Fix Version/s | 10.11 [ 27614 ] |
| Fix Version/s | 10.4.29 [ 28510 ] | |
| Fix Version/s | 10.5.20 [ 28512 ] | |
| Fix Version/s | 10.6.13 [ 28514 ] | |
| Fix Version/s | 10.8.8 [ 28518 ] | |
| Fix Version/s | 10.9.6 [ 28520 ] | |
| Fix Version/s | 10.10.4 [ 28522 ] | |
| Fix Version/s | 10.11.2 [ 28523 ] | |
| Fix Version/s | 11.0.1 [ 28548 ] | |
| Fix Version/s | 10.7.8 [ 28515 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Fix Version/s | 10.5 [ 23123 ] | |
| Fix Version/s | 10.6 [ 24028 ] | |
| Fix Version/s | 10.7 [ 24805 ] | |
| Fix Version/s | 10.8 [ 26121 ] | |
| Fix Version/s | 10.9 [ 26905 ] | |
| Fix Version/s | 10.10 [ 27530 ] | |
| Fix Version/s | 10.11 [ 27614 ] | |
| Fix Version/s | 11.0 [ 28320 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
The EXPLAIN shows
Full scan on NULL key; Using rowid filterLooks like the interplay between these two features.
The crash happens when trying to execute the Full scan on NULL key part.