[MDEV-20056] Assertion `!prebuilt->index->is_primary()' failed in row_search_idx_cond_check - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.4(EOL)
Fix Version/s: 10.4.11
Component/s: Optimizer
Labels:
None

Description

--source include/have_innodb.inc

CREATE TABLE t1 (ch varchar(1),id int,id2 int) ENGINE=InnoDB;

INSERT INTO t1 VALUES ('l',3,2),('e',NULL,NULL),('r',7,3),('h',NULL,2),(NULL,4,4),('c',4,NULL),('k',NULL,NULL),('h',NULL,NULL),('b',9,NULL),('f',6,NULL);

CREATE TABLE t2 (pk int NOT NULL , col_date_key date , ch2 varchar(1) , id2 int , PRIMARY KEY (pk), KEY (col_date_key), KEY (ch2), KEY (id2)) ENGINE=InnoDB;

INSERT INTO t2 VALUES  (1,'2034-04-21','g',9),(2,'2006-09-08','y',1),(3,NULL,'h',2),(4,'1987-03-02','s',2),(5,'2019-07-02','u',NULL),(6,'2012-12-18','z',1),(7,NULL,'w',4),(8,'2005-03-10','o',8),(9,'1987-02-12','d',4);

CREATE TABLE t3 ( id int) ENGINE=InnoDB;

INSERT INTO t3 VALUES (6);

SELECT 1 FROM t3

WHERE EXISTS(SELECT 1 FROM t1 WHERE t3.id IN (

  SELECT bt1.id FROM (t2 JOIN t1 AS bt1 ON bt1.ch = t2.ch2 and bt1.id = t2.pk) WHERE t2.ch2 <= 'g')

OR t1.id2 = t1.id);

10.4 a0230bc76d78202178f43

#4 0x00007f9c1f600428 in __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:54
#5 0x00007f9c1f60202a in __GI_abort () at abort.c:89
#6 0x00007f9c1f5f8bd7 in __assert_fail_base (fmt=<optimized out>, assertion=assertion@entry=0x55c9649735a0 "!prebuilt->index->is_primary()", file=file@entry=0x55c9649724d0 "/10.4/storage/innobase/row/row0sel.cc", line=line@entry=3906, function=function@entry=0x55c9649766c0 <row_search_idx_cond_check(unsigned char, row_prebuilt_t, unsigned char const, unsigned long const)::__PRETTY_FUNCTION__> "ICP_RESULT row_search_idx_cond_check(byte, row_prebuilt_t, const rec_t, const ulint)") at assert.c:92
#7 0x00007f9c1f5f8c82 in __GI___assert_fail (assertion=0x55c9649735a0 "!prebuilt->index->is_primary()", file=0x55c9649724d0 "/10.4/storage/innobase/row/row0sel.cc", line=3906, function=0x55c9649766c0 <row_search_idx_cond_check(unsigned char, row_prebuilt_t, unsigned char const, unsigned long const)::__PRETTY_FUNCTION__> "ICP_RESULT row_search_idx_cond_check(byte, row_prebuilt_t, const rec_t, const ulint)") at assert.c:101
#8 0x000055c96431101a in row_search_idx_cond_check (mysql_rec=0x7f9bc8068900 "\375\001", prebuilt=0x7f9bc80727f8, rec=0x7f9c13d4007f "\200", offsets=0x7f9c18378f80) at /10.4/storage/innobase/row/row0sel.cc:3906
#9 0x000055c964314a24 in row_search_mvcc (buf=0x7f9bc8068900 "\375\001", mode=PAGE_CUR_G, prebuilt=0x7f9bc80727f8, match_mode=0, direction=0) at /10.4/storage/innobase/row/row0sel.cc:5220
#10 0x000055c9641707df in ha_innobase::index_read (this=0x7f9bc8069738, buf=0x7f9bc8068900 "\375\001", key_ptr=0x0, key_len=0, find_flag=HA_READ_AFTER_KEY) at /10.4/storage/innobase/handler/ha_innodb.cc:9337
#11 0x000055c964171748 in ha_innobase::index_first (this=0x7f9bc8069738, buf=0x7f9bc8068900 "\375\001") at /10.4/storage/innobase/handler/ha_innodb.cc:9710
#12 0x000055c964171920 in ha_innobase::rnd_next (this=0x7f9bc8069738, buf=0x7f9bc8068900 "\375\001") at /10.4/storage/innobase/handler/ha_innodb.cc:9803
#13 0x000055c963e01d83 in handler::ha_rnd_next (this=0x7f9bc8069738, buf=0x7f9bc8068900 "\375\001") at /10.4/sql/handler.cc:2834
#14 0x000055c963f968ec in rr_sequential (info=0x7f9bc81af090) at /10.4/sql/records.cc:477
#15 0x000055c963a14c13 in READ_RECORD::read_record (this=0x7f9bc81af090) at /10.4/sql/records.h:69
#16 0x000055c963b3b2be in join_init_read_record (tab=0x7f9bc81aefc8) at /10.4/sql/sql_select.cc:21055
#17 0x000055c963c80004 in JOIN_TAB_SCAN::open (this=0x7f9bc81b0a50) at /10.4/sql/sql_join_cache.cc:3349
#18 0x000055c963c7e44d in JOIN_CACHE::join_matching_records (this=0x7f9bc81b0920, skip_last=false) at /10.4/sql/sql_join_cache.cc:2252
#19 0x000055c963c7df7e in JOIN_CACHE::join_records (this=0x7f9bc81b0920, skip_last=false) at /10.4/sql/sql_join_cache.cc:2088
#20 0x000055c963b38b71 in sub_select_cache (join=0x7f9bc81a47c0, join_tab=0x7f9bc81aefc8, end_of_records=true) at /10.4/sql/sql_select.cc:19892
#21 0x000055c963b38d7c in sub_select (join=0x7f9bc81a47c0, join_tab=0x7f9bc81aec20, end_of_records=true) at /10.4/sql/sql_select.cc:20063
#22 0x000055c963b38546 in do_select (join=0x7f9bc81a47c0, procedure=0x0) at /10.4/sql/sql_select.cc:19654
#23 0x000055c963b0e7b3 in JOIN::exec_inner (this=0x7f9bc81a47c0) at /10.4/sql/sql_select.cc:4391
#24 0x000055c963b0d960 in JOIN::exec (this=0x7f9bc81a47c0) at /10.4/sql/sql_select.cc:4173
#25 0x000055c963ee0e71 in subselect_hash_sj_engine::exec (this=0x7f9bc81aa9c8) at /10.4/sql/item_subselect.cc:5483
#26 0x000055c963ed246e in Item_subselect::exec (this=0x7f9bc8017d50) at /10.4/sql/item_subselect.cc:745
#27 0x000055c963ed2ac0 in Item_in_subselect::exec (this=0x7f9bc8017d50) at /10.4/sql/item_subselect.cc:925
#28 0x000055c963ed596a in Item_in_subselect::val_bool (this=0x7f9bc8017d50) at /10.4/sql/item_subselect.cc:1796
#29 0x000055c9639a6b41 in Item::val_bool_result (this=0x7f9bc8017d50) at /10.4/sql/item.h:1521
#30 0x000055c963e42f08 in Item_in_optimizer::val_int (this=0x7f9bc81a4e70) at /10.4/sql/item_cmpfunc.cc:1628
#31 0x000055c9639a6ab5 in Item::val_int_result (this=0x7f9bc81a4e70) at /10.4/sql/item.h:1517
#32 0x000055c963e31ead in Item_cache_int::cache_value (this=0x7f9bc81b16b0) at /10.4/sql/item.cc:9712
#33 0x000055c963e3a10c in Item_cache_wrapper::cache (this=0x7f9bc81b15f0) at /10.4/sql/item.cc:8531
#34 0x000055c963e2f420 in Item_cache_wrapper::val_bool (this=0x7f9bc81b15f0) at /10.4/sql/item.cc:8717
#35 0x000055c963e4f0a3 in Item_cond_or::val_int (this=0x7f9bc81a78c8) at /10.4/sql/item_cmpfunc.cc:5300
#36 0x000055c963b39386 in evaluate_join_record (join=0x7f9bc81a4070, join_tab=0x7f9bc81a7178, error=0) at /10.4/sql/sql_select.cc:20209
#37 0x000055c963b38fb0 in sub_select (join=0x7f9bc81a4070, join_tab=0x7f9bc81a7178, end_of_records=false) at /10.4/sql/sql_select.cc:20114
#38 0x000055c963b384e0 in do_select (join=0x7f9bc81a4070, procedure=0x0) at /10.4/sql/sql_select.cc:19652
#39 0x000055c963b0e7b3 in JOIN::exec_inner (this=0x7f9bc81a4070) at /10.4/sql/sql_select.cc:4391
#40 0x000055c963b0d960 in JOIN::exec (this=0x7f9bc81a4070) at /10.4/sql/sql_select.cc:4173
#41 0x000055c963eddd0a in subselect_single_select_engine::exec (this=0x7f9bc8018ed0) at /10.4/sql/item_subselect.cc:3927
#42 0x000055c963ed246e in Item_subselect::exec (this=0x7f9bc8018d30) at /10.4/sql/item_subselect.cc:745
#43 0x000055c963ed5488 in Item_exists_subselect::val_int (this=0x7f9bc8018d30) at /10.4/sql/item_subselect.cc:1644
#44 0x000055c963e42c73 in Item_in_optimizer::val_int (this=0x7f9bc81a5190) at /10.4/sql/item_cmpfunc.cc:1546
#45 0x000055c963b0e1e3 in JOIN::exec_inner (this=0x7f9bc81a38f8) at /10.4/sql/sql_select.cc:4287
#46 0x000055c963b0d960 in JOIN::exec (this=0x7f9bc81a38f8) at /10.4/sql/sql_select.cc:4173
#47 0x000055c963b0f005 in mysql_select (thd=0x7f9bc8000b00, tables=0x7f9bc8013820, wild_num=0, fields=..., conds=0x7f9bc8018d30, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f9bc8019020, unit=0x7f9bc8004a30, select_lex=0x7f9bc80132d8) at /10.4/sql/sql_select.cc:4605
#48 0x000055c963aff5ce in handle_select (thd=0x7f9bc8000b00, lex=0x7f9bc8004968, result=0x7f9bc8019020, setup_tables_done_option=0) at /10.4/sql/sql_select.cc:425
#49 0x000055c963ac8f0a in execute_sqlcom_select (thd=0x7f9bc8000b00, all_tables=0x7f9bc8013820) at /10.4/sql/sql_parse.cc:6340
#50 0x000055c963abed58 in mysql_execute_command (thd=0x7f9bc8000b00) at /10.4/sql/sql_parse.cc:3882
#51 0x000055c963accce2 in mysql_parse (thd=0x7f9bc8000b00, rawbuf=0x7f9bc8013108 "SELECT 1 FROM t3\nWHERE EXISTS(SELECT 1 FROM t1 WHERE id IN (\nSELECT bt1.id FROM (t2 JOIN t1 AS bt1 ON bt1.ch = t2.ch2 and bt1.id = t2.pk) WHERE t2.ch2 <= 'g') \nOR t1.id2 = t1.id)", length=178, parser_state=0x7f9c1837c000, is_com_multi=false, is_next_command=false) at /10.4/sql/sql_parse.cc:7892
#52 0x000055c963ab8f51 in dispatch_command (command=COM_QUERY, thd=0x7f9bc8000b00, packet=0x7f9bc819dca1 "SELECT 1 FROM t3\nWHERE EXISTS(SELECT 1 FROM t1 WHERE id IN (\nSELECT bt1.id FROM (t2 JOIN t1 AS bt1 ON bt1.ch = t2.ch2 and bt1.id = t2.pk) WHERE t2.ch2 <= 'g') \nOR t1.id2 = t1.id)", packet_length=178, is_com_multi=false, is_next_command=false) at /10.4/sql/sql_parse.cc:1827
#53 0x000055c963ab76e3 in do_command (thd=0x7f9bc8000b00) at /10.4/sql/sql_parse.cc:1360
#54 0x000055c963c31994 in do_handle_one_connection (connect=0x55c9674bb080) at /10.4/sql/sql_connect.cc:1404
#55 0x000055c963c316bd in handle_one_connection (arg=0x55c9674bb080) at /10.4/sql/sql_connect.cc:1306
#56 0x000055c96455d015 in pfs_spawn_thread (arg=0x55c967439830) at /10.4/storage/perfschema/pfs.cc:1862
#57 0x00007f9c204416ba in start_thread (arg=0x7f9c1837d700) at pthread_create.c:333
#58 0x00007f9c1f6d241d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

Attachments

Issue Links

causes

MDEV-20252 Specific SQL query consistently crashes MariaDB

Closed

is duplicated by

MDEV-20851 optimizer_switch 'mrr=on' causes failing queries

Closed

MDEV-21040 InnoDB: fulltext search with ` in(...)` crashed on specific combination

Closed

relates to

MDEV-19919 Assertion `!prebuilt->index->is_primary()' failed in row_search_idx_cond_check

Closed

MDEV-21356 ERROR 1032 Can't find record when running simple, single-table query

Closed

MDEV-16188 Use in-memory PK filters built from range index scans

Closed

MDEV-19361 Assertion `marked_for_read()' failed in Field_long::val_int

Closed

MDEV-19820 Wrong result with multiple single column index request

Closed

MDEV-21446 Assertion `!prebuilt->index->is_primary()' failed in row_search_idx_cond_check with rowid_filter upon concurrent access to table

Closed

(4 relates to)

Activity

Ascending order - Click to sort in descending order

Alice Sherepa added a comment - 2019-09-12 14:43

probably the same as ~~MDEV-19919~~

Alice Sherepa added a comment - 2019-09-12 14:43 probably the same as MDEV-19919

Marko Mäkelä added a comment - 2019-09-12 15:17

The assertion was added by igor in ~~MDEV-19820~~. It looks valid to me. If the code is supposed to filter secondary index records, it will have to be adjusted.

As far as I understand, ~~MDEV-16188~~ is supposed to apply to PRIMARY KEY scans only.

Marko Mäkelä added a comment - 2019-09-12 15:17 The assertion was added by igor in MDEV-19820 . It looks valid to me. If the code is supposed to filter secondary index records, it will have to be adjusted. As far as I understand, MDEV-16188 is supposed to apply to PRIMARY KEY scans only.

Igor Babaev (Inactive) added a comment - 2019-11-25 18:19

If to run EXPLAIN EXTENDED for the query from the reported test case we can see that the optimizer builds an invalid execution plan:

+------+--------------------+-------+--------------+------------------------------+---------+---------+------+---------+----------+----------------------------------------------------------------------------------+

| id   | select_type        | table | type         | possible_keys                | key     | key_len | ref  | rows    | filtered | Extra                                                                            |

+------+--------------------+-------+--------------+------------------------------+---------+---------+------+---------+----------+----------------------------------------------------------------------------------+

|    1 | PRIMARY            | t3    | ALL          | NULL                         | NULL    | NULL    | NULL | 1       |   100.00 | Using where                                                                      |

|    2 | DEPENDENT SUBQUERY | t1    | ALL          | NULL                         | NULL    | NULL    | NULL | 10      |   100.00 | Using where                                                                      |

|    3 | MATERIALIZED       | bt1   | ALL          | NULL                         | NULL    | NULL    | NULL | 10      |   100.00 | Using where                                                                      |

|    3 | MATERIALIZED       | t2    | index|filter | PRIMARY,col_date_key,ch2,id2 | ch2|ch2 | 4|4     | NULL | 9 (22%) |    11.11 | Using where; Using index; Using join buffer (flat, BNL join); Using rowid filter |

+------+--------------------+-------+--------------+------------------------------+---------+---------+------+---------+----------+----------------------------------------------------------------------------------+

In the last line of this plan we see that the rowid filter uses the same index by which we scan the table rows. This does not make any sense.

If I simplify the test case query for:

SELECT 1 FROM t3

WHERE EXISTS ( SELECT 1 FROM t1

               WHERE t3.id IN ( SELECT bt1.id FROM t2, t1 AS bt1

                                WHERE bt1.id = t2.pk AND

                                      t2.ch2 <= 'g' ) OR

                     t1.id2 = t1.id);

the execution of it does not fail, but it still uses some nonsensical plan

MariaDB [test]> EXPLAIN EXTENDED

    -> SELECT 1 FROM t3

    -> WHERE EXISTS ( SELECT 1 FROM t1

    ->                WHERE t3.id IN ( SELECT bt1.id FROM t2, t1 AS bt1

    ->                                 WHERE bt1.id = t2.pk AND

    ->                                       t2.ch2 <= 'g' ) OR

    ->                      t1.id2 = t1.id);

+------+--------------------+-------+--------------+------------------------------+---------+---------+------+---------+----------+----------------------------------------------------------------------------------+

| id   | select_type        | table | type         | possible_keys                | key     | key_len | ref  | rows    | filtered | Extra                                                                            |

+------+--------------------+-------+--------------+------------------------------+---------+---------+------+---------+----------+----------------------------------------------------------------------------------+

|    1 | PRIMARY            | t3    | ALL          | NULL                         | NULL    | NULL    | NULL | 1       |   100.00 | Using where                                                                      |

|    2 | DEPENDENT SUBQUERY | t1    | ALL          | NULL                         | NULL    | NULL    | NULL | 10      |   100.00 | Using where                                                                      |

|    3 | MATERIALIZED       | bt1   | ALL          | NULL                         | NULL    | NULL    | NULL | 10      |   100.00 |                                                                                  |

|    3 | MATERIALIZED       | t2    | range|filter | PRIMARY,col_date_key,ch2,id2 | ch2|ch2 | 4|4     | NULL | 2 (22%) |    22.22 | Using where; Using index; Using join buffer (flat, BNL join); Using rowid filter |

+------+--------------------+-------+--------------+------------------------------+---------+---------+------+---------+----------+----------------------------------------------------------------------------------+

Igor Babaev (Inactive) added a comment - 2019-11-25 18:19 If to run EXPLAIN EXTENDED for the query from the reported test case we can see that the optimizer builds an invalid execution plan: +------+--------------------+-------+--------------+------------------------------+---------+---------+------+---------+----------+----------------------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+--------------------+-------+--------------+------------------------------+---------+---------+------+---------+----------+----------------------------------------------------------------------------------+ | 1 | PRIMARY | t3 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | t1 | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where | | 3 | MATERIALIZED | bt1 | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where | | 3 | MATERIALIZED | t2 | index|filter | PRIMARY,col_date_key,ch2,id2 | ch2|ch2 | 4|4 | NULL | 9 (22%) | 11.11 | Using where; Using index; Using join buffer (flat, BNL join); Using rowid filter | +------+--------------------+-------+--------------+------------------------------+---------+---------+------+---------+----------+----------------------------------------------------------------------------------+ In the last line of this plan we see that the rowid filter uses the same index by which we scan the table rows. This does not make any sense. If I simplify the test case query for: SELECT 1 FROM t3 WHERE EXISTS ( SELECT 1 FROM t1 WHERE t3.id IN ( SELECT bt1.id FROM t2, t1 AS bt1 WHERE bt1.id = t2.pk AND t2.ch2 <= 'g' ) OR t1.id2 = t1.id); the execution of it does not fail, but it still uses some nonsensical plan MariaDB [test]> EXPLAIN EXTENDED -> SELECT 1 FROM t3 -> WHERE EXISTS ( SELECT 1 FROM t1 -> WHERE t3.id IN ( SELECT bt1.id FROM t2, t1 AS bt1 -> WHERE bt1.id = t2.pk AND -> t2.ch2 <= 'g' ) OR -> t1.id2 = t1.id); +------+--------------------+-------+--------------+------------------------------+---------+---------+------+---------+----------+----------------------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+--------------------+-------+--------------+------------------------------+---------+---------+------+---------+----------+----------------------------------------------------------------------------------+ | 1 | PRIMARY | t3 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | t1 | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where | | 3 | MATERIALIZED | bt1 | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | | | 3 | MATERIALIZED | t2 | range|filter | PRIMARY,col_date_key,ch2,id2 | ch2|ch2 | 4|4 | NULL | 2 (22%) | 22.22 | Using where; Using index; Using join buffer (flat, BNL join); Using rowid filter | +------+--------------------+-------+--------------+------------------------------+---------+---------+------+---------+----------+----------------------------------------------------------------------------------+

Igor Babaev (Inactive) added a comment - 2019-11-25 19:07 - edited

To catch such invalid plans before the execution plan and even to catch them for EXPLAIN commands I've added
the following assertions in bool JOIN::make_range_rowid_filters():

    DBUG_ASSERT(!(tab->ref.key >= 0 &&

                  tab->ref.key == (int) tab->range_rowid_filter_info->key_no));

    DBUG_ASSERT(!(tab->ref.key == -1 && tab->quick &&

                  tab->quick->index == tab->range_rowid_filter_info->key_no));

Now both EXPLAIN EXTENDED commands fail with the second assertion.

Igor Babaev (Inactive) added a comment - 2019-11-25 19:07 - edited To catch such invalid plans before the execution plan and even to catch them for EXPLAIN commands I've added the following assertions in bool JOIN::make_range_rowid_filters(): DBUG_ASSERT(!(tab->ref.key >= 0 && tab->ref.key == (int) tab->range_rowid_filter_info->key_no)); DBUG_ASSERT(!(tab->ref.key == -1 && tab->quick && tab->quick->index == tab->range_rowid_filter_info->key_no)); Now both EXPLAIN EXTENDED commands fail with the second assertion.

Igor Babaev (Inactive) added a comment - 2019-11-27 06:04

A fix for this bug was pushed into 10.4.

Igor Babaev (Inactive) added a comment - 2019-11-27 06:04 A fix for this bug was pushed into 10.4.

MariaDB Server

Assertion `!prebuilt->index->is_primary()' failed in row_search_idx_cond_check

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration