[MDEV-30596] Assertion `pushed_rowid_filter != __null && save_pushed_rowid_filter == __null' failed in handler::disable_pushed_rowid_filter Created: 2023-02-07  Updated: 2023-02-14  Resolved: 2023-02-14

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 11.0
Fix Version/s: 10.11.2, 11.0.1, 10.4.29, 10.5.20, 10.6.13, 10.7.8, 10.8.8, 10.9.6, 10.10.4

Type: Bug Priority: Major
Reporter: Alice Sherepa Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: rowid_filtering


 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))))



 Comments   
Comment by Sergei Petrunia [ 2023-02-10 ]

The EXPLAIN shows

Full scan on NULL key; Using rowid filter

Looks like the interplay between these two features.
The crash happens when trying to execute the Full scan on NULL key part.

Comment by Sergei Petrunia [ 2023-02-10 ]

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...

Comment by Sergei Petrunia [ 2023-02-12 ]

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.

Comment by Sergei Petrunia [ 2023-02-13 ]

... 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);

Comment by Sergei Petrunia [ 2023-02-13 ]

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> 


 

Comment by Sergei Petrunia [ 2023-02-13 ]

bb-11.0-mdev30596

Comment by Sergei Petrunia [ 2023-02-13 ]

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.

Comment by Sergei Petrunia [ 2023-02-13 ]

10.4 doesn't crash but does show incorrect explain (shows "Using rowid filter" where it won't be used).

Comment by Sergei Petrunia [ 2023-02-14 ]

Got ok to push

Generated at Thu Feb 08 10:17:26 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.