Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-19919

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

Details

    Description

      --source include/have_innodb.inc
       
      SET @stats.save= @@innodb_stats_persistent;
      SET GLOBAL innodb_stats_persistent= ON;
       
      CREATE TABLE t1 (
          a INT,
          b VARCHAR(10),
          c VARCHAR(1024),
          KEY (b),
          KEY (c)
      ) ENGINE=InnoDB;
       
      INSERT INTO t1 VALUES
        (1,'w','z'), (1,'X','o'), (1,'q','c'), (5,'w','c'), (2,'j','m'),
        (2,'Q','s'), (9,'e','J'), (2,'p','W'), (9,'o','F'), (2,'g','S'),
        (1,'Y','a'), (NULL,'Y','p'), (NULL,'s','x'), (NULL,'i','S'),
        (1,'l','q'), (7,'r','e'), (4,'b','h'), (NULL,'E','c'),
        (NULL,'M','a'), (3,'e','X'), (NULL,'p','r'), (9,'e','i');
       
      CREATE TABLE t2 (d INT) ENGINE=MyISAM;
       
      SELECT a FROM t1 LEFT JOIN t2 ON a = d WHERE c < 'k' AND b > 't' ORDER BY a;
       
      # Cleanup
      DROP TABLE t1, t2;
      SET GLOBAL innodb_stats_persistent= @stats.save;
      

      10.4 223c550d

      mysqld: /data/src/10.4/storage/innobase/row/row0sel.cc:3906: ICP_RESULT row_search_idx_cond_check(byte*, row_prebuilt_t*, const rec_t*, const ulint*): Assertion `!prebuilt->index->is_primary()' failed.
      190702  2:52:40 [ERROR] mysqld got signal 6 ;
       
      #7  0x00007fd21b70df12 in __GI___assert_fail (assertion=0x55bbcd9d4ca8 "!prebuilt->index->is_primary()", file=0x55bbcd9d3be0 "/data/src/10.4/storage/innobase/row/row0sel.cc", line=3906, function=0x55bbcd9d7dc0 <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  0x000055bbcd263c8f in row_search_idx_cond_check (mysql_rec=0x7fd1b8131658 "\370\001", prebuilt=0x7fd1b8131f28, rec=0x7fd2009881ca "", offsets=0x7fd218159db0) at /data/src/10.4/storage/innobase/row/row0sel.cc:3906
      #9  0x000055bbcd2675e7 in row_search_mvcc (buf=0x7fd1b8131658 "\370\001", mode=PAGE_CUR_GE, prebuilt=0x7fd1b8131f28, match_mode=1, direction=0) at /data/src/10.4/storage/innobase/row/row0sel.cc:5220
      #10 0x000055bbcd0c1785 in ha_innobase::index_read (this=0x7fd1b8130e98, buf=0x7fd1b8131658 "\370\001", key_ptr=0x7fd1b8063440 "", key_len=6, find_flag=HA_READ_KEY_EXACT) at /data/src/10.4/storage/innobase/handler/ha_innodb.cc:9339
      #11 0x000055bbcd0c28e7 in ha_innobase::rnd_pos (this=0x7fd1b8130e98, buf=0x7fd1b8131658 "\370\001", pos=0x7fd1b8063440 "") at /data/src/10.4/storage/innobase/handler/ha_innodb.cc:9843
      #12 0x000055bbcce92314 in handler::ha_rnd_pos (this=0x7fd1b8130e98, buf=0x7fd1b8131658 "\370\001", pos=0x7fd1b8063440 "") at /data/src/10.4/sql/handler.cc:2866
      #13 0x000055bbcd022343 in rr_from_pointers (info=0x7fd1b8065828) at /data/src/10.4/sql/records.cc:543
      #14 0x000055bbccaa7fe7 in READ_RECORD::read_record (this=0x7fd1b8065828) at /data/src/10.4/sql/records.h:69
      #15 0x000055bbccbcd4bd in join_init_read_record (tab=0x7fd1b8065760) at /data/src/10.4/sql/sql_select.cc:21055
      #16 0x000055bbccbcb141 in sub_select (join=0x7fd1b80163d0, join_tab=0x7fd1b8065760, end_of_records=false) at /data/src/10.4/sql/sql_select.cc:20111
      #17 0x000055bbccbca6d0 in do_select (join=0x7fd1b80163d0, procedure=0x0) at /data/src/10.4/sql/sql_select.cc:19652
      #18 0x000055bbccba0aad in JOIN::exec_inner (this=0x7fd1b80163d0) at /data/src/10.4/sql/sql_select.cc:4391
      #19 0x000055bbccb9fc5a in JOIN::exec (this=0x7fd1b80163d0) at /data/src/10.4/sql/sql_select.cc:4173
      #20 0x000055bbccba12ff in mysql_select (thd=0x7fd1b8000b00, tables=0x7fd1b80135c0, wild_num=0, fields=..., conds=0x7fd1b8015758, og_num=1, order=0x7fd1b80159d0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fd1b80163a8, unit=0x7fd1b8004a28, select_lex=0x7fd1b8012ff8) at /data/src/10.4/sql/sql_select.cc:4605
      #21 0x000055bbccb918ee in handle_select (thd=0x7fd1b8000b00, lex=0x7fd1b8004960, result=0x7fd1b80163a8, setup_tables_done_option=0) at /data/src/10.4/sql/sql_select.cc:425
      #22 0x000055bbccb5b48f in execute_sqlcom_select (thd=0x7fd1b8000b00, all_tables=0x7fd1b80135c0) at /data/src/10.4/sql/sql_parse.cc:6340
      #23 0x000055bbccb512d6 in mysql_execute_command (thd=0x7fd1b8000b00) at /data/src/10.4/sql/sql_parse.cc:3882
      #24 0x000055bbccb5f223 in mysql_parse (thd=0x7fd1b8000b00, rawbuf=0x7fd1b8012ef8 "SELECT a FROM t1 LEFT JOIN t2 ON a = d WHERE c < 'k' AND b > 't' ORDER BY a", length=75, parser_state=0x7fd21815c1d0, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:7892
      #25 0x000055bbccb4b4cd in dispatch_command (command=COM_QUERY, thd=0x7fd1b8000b00, packet=0x7fd1b8142621 "", packet_length=75, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:1827
      #26 0x000055bbccb49c8b in do_command (thd=0x7fd1b8000b00) at /data/src/10.4/sql/sql_parse.cc:1360
      #27 0x000055bbcccc2f81 in do_handle_one_connection (connect=0x55bbd0803b40) at /data/src/10.4/sql/sql_connect.cc:1403
      #28 0x000055bbcccc2ce5 in handle_one_connection (arg=0x55bbd0803b40) at /data/src/10.4/sql/sql_connect.cc:1306
      #29 0x00007fd21d2824a4 in start_thread (arg=0x7fd21815d700) at pthread_create.c:456
      #30 0x00007fd21b7cad0f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:97
      

      Not reproducible with the provided test case on 10.3.

      Attachments

        Issue Links

          Activity

            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 Marko Mäkelä added a comment - 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.
            alice Alice Sherepa added a comment -

            igor, could you please check if the following patch fixes the problem from MDEV-20629:

            --source include/have_innodb.inc
             
            CREATE TABLE `services` (
              `service_id` char(36) NOT NULL,
              `service_supplier_id` char(64) DEFAULT NULL,
              `billing_group_id` char(64) DEFAULT NULL,
              `seq` tinyint(4) unsigned NOT NULL,
              `service_descrip` varchar(216) NOT NULL,
              `provisioned` enum('S','U','C','H','M','R','F','D') NOT NULL DEFAULT 'S',
              KEY `service_id` (`service_id`),
              KEY `provisioned` (`provisioned`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8  ;
             
            INSERT INTO `services` VALUES ('189c2d2e-017f-40b8-9555-4ea30f0ae351',NULL,NULL,1,'','S'),('189c2d2e-017f-40b8-9555-4ea30f0ae351',NULL,NULL,2,'','S'),('045b1b0e-cf53-4249-b062-35149e587e48',NULL,NULL,0,'','C'),('bb63e272-d312-40f9-ac17-2c3660842363',NULL,NULL,0,'','C'),('189c2d2e-017f-40b8-9555-4ea30f0ae351',NULL,NULL,0,'','S'),('142f9f75-506c-4e6e-b625-0e1ca3b19dd8',NULL,NULL,0,'','C'),('c4af6e73-7fe7-4d48-a61d-d5a01f88b07f',NULL,NULL,0,'','C'),('0ae942eb-7cf1-4669-afe0-7707af632ce2',NULL,NULL,0,'','C'),('15df6311-9a12-4465-b8d5-1cf17487162d',NULL,NULL,0,'','C'),('a4cb7786-fc04-4e01-927a-7c428bb55627',NULL,NULL,0,'','C'),('ae7416e7-5fc3-47a2-96e8-eb3efe3a8bd7',NULL,NULL,0,'','C'),('17b774c7-2092-4565-9671-5387977fe197',NULL,NULL,0,'','C'),('f42836ab-73c1-4942-8b1e-72fff02b1ffe',NULL,NULL,0,'','C'),('ade04016-def2-4845-b0ea-7f02a3341b7e',NULL,NULL,0,'','C'),('1f0e6e01-b9d8-41f4-9831-c08d47059c5e',NULL,NULL,0,'','C'),('b16d8cb8-ae69-422a-a371-8b164bbf711f',NULL,NULL,0,'','C'),('7da1d364-d849-4508-a7c9-04c2f6ee730a',NULL,NULL,0,'','C'),('6080d648-e561-4e6d-b59b-1489726ecb98',NULL,NULL,0,'','C');
             
            SELECT * FROM services
            WHERE service_id='189c2d2e-017f-40b8-9555-4ea30f0ae351' AND provisioned IN ('S', 'R')
            ORDER BY seq;
             
            SELECT * FROM services
            WHERE service_id='189c2d2e-017f-40b8-9555-4ea30f0ae351' AND provisioned IN ('S', 'R')
            ORDER BY seq;
            

            alice Alice Sherepa added a comment - igor , could you please check if the following patch fixes the problem from MDEV-20629 : --source include/have_innodb.inc   CREATE TABLE `services` ( `service_id` char (36) NOT NULL , `service_supplier_id` char (64) DEFAULT NULL , `billing_group_id` char (64) DEFAULT NULL , `seq` tinyint(4) unsigned NOT NULL , `service_descrip` varchar (216) NOT NULL , `provisioned` enum( 'S' , 'U' , 'C' , 'H' , 'M' , 'R' , 'F' , 'D' ) NOT NULL DEFAULT 'S' , KEY `service_id` (`service_id`), KEY `provisioned` (`provisioned`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; INSERT INTO `services` VALUES ( '189c2d2e-017f-40b8-9555-4ea30f0ae351' , NULL , NULL ,1, '' , 'S' ),( '189c2d2e-017f-40b8-9555-4ea30f0ae351' , NULL , NULL ,2, '' , 'S' ),( '045b1b0e-cf53-4249-b062-35149e587e48' , NULL , NULL ,0, '' , 'C' ),( 'bb63e272-d312-40f9-ac17-2c3660842363' , NULL , NULL ,0, '' , 'C' ),( '189c2d2e-017f-40b8-9555-4ea30f0ae351' , NULL , NULL ,0, '' , 'S' ),( '142f9f75-506c-4e6e-b625-0e1ca3b19dd8' , NULL , NULL ,0, '' , 'C' ),( 'c4af6e73-7fe7-4d48-a61d-d5a01f88b07f' , NULL , NULL ,0, '' , 'C' ),( '0ae942eb-7cf1-4669-afe0-7707af632ce2' , NULL , NULL ,0, '' , 'C' ),( '15df6311-9a12-4465-b8d5-1cf17487162d' , NULL , NULL ,0, '' , 'C' ),( 'a4cb7786-fc04-4e01-927a-7c428bb55627' , NULL , NULL ,0, '' , 'C' ),( 'ae7416e7-5fc3-47a2-96e8-eb3efe3a8bd7' , NULL , NULL ,0, '' , 'C' ),( '17b774c7-2092-4565-9671-5387977fe197' , NULL , NULL ,0, '' , 'C' ),( 'f42836ab-73c1-4942-8b1e-72fff02b1ffe' , NULL , NULL ,0, '' , 'C' ),( 'ade04016-def2-4845-b0ea-7f02a3341b7e' , NULL , NULL ,0, '' , 'C' ),( '1f0e6e01-b9d8-41f4-9831-c08d47059c5e' , NULL , NULL ,0, '' , 'C' ),( 'b16d8cb8-ae69-422a-a371-8b164bbf711f' , NULL , NULL ,0, '' , 'C' ),( '7da1d364-d849-4508-a7c9-04c2f6ee730a' , NULL , NULL ,0, '' , 'C' ),( '6080d648-e561-4e6d-b59b-1489726ecb98' , NULL , NULL ,0, '' , 'C' );   SELECT * FROM services WHERE service_id= '189c2d2e-017f-40b8-9555-4ea30f0ae351' AND provisioned IN ( 'S' , 'R' ) ORDER BY seq;   SELECT * FROM services WHERE service_id= '189c2d2e-017f-40b8-9555-4ea30f0ae351' AND provisioned IN ( 'S' , 'R' ) ORDER BY seq;
            Ellerbrockr richardeaxon added a comment -

            Hi folks, any idea when this will get attention? I get isolated incorrect query results on 10.4 with our application which is bad, but I am not sure what other incorrect results we have lurking due to this.

            Ellerbrockr richardeaxon added a comment - Hi folks, any idea when this will get attention? I get isolated incorrect query results on 10.4 with our application which is bad, but I am not sure what other incorrect results we have lurking due to this.
            igor Igor Babaev added a comment -

            Elena,
            Here's what I have on my laptop for the current state of 10.4:

            igor@stephan:~/maria-git/10.4/mysql-test> ../client/mysql test
            Welcome to the MariaDB monitor.  Commands end with ; or \g.
            Your MariaDB connection id is 8
            Server version: 10.4.11-MariaDB-debug Source distribution
             
            Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
             
            Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
             
            MariaDB [test]> SET @stats.save= @@innodb_stats_persistent;
            Query OK, 0 rows affected (0.001 sec)
             
            MariaDB [test]> SET GLOBAL innodb_stats_persistent= ON;
            Query OK, 0 rows affected (0.000 sec)
             
            MariaDB [test]> 
            MariaDB [test]> CREATE TABLE t1 (
                ->     a INT,
                ->     b VARCHAR(10),
                ->     c VARCHAR(1024),
                ->     KEY (b),
                ->     KEY (c)
                -> ) ENGINE=InnoDB;
            Query OK, 0 rows affected (0.034 sec)
             
            MariaDB [test]> 
            MariaDB [test]> INSERT INTO t1 VALUES
                ->   (1,'w','z'), (1,'X','o'), (1,'q','c'), (5,'w','c'), (2,'j','m'),
                ->   (2,'Q','s'), (9,'e','J'), (2,'p','W'), (9,'o','F'), (2,'g','S'),
                ->   (1,'Y','a'), (NULL,'Y','p'), (NULL,'s','x'), (NULL,'i','S'),
                ->   (1,'l','q'), (7,'r','e'), (4,'b','h'), (NULL,'E','c'),
                ->   (NULL,'M','a'), (3,'e','X'), (NULL,'p','r'), (9,'e','i');
            Query OK, 22 rows affected (0.029 sec)
            Records: 22  Duplicates: 0  Warnings: 0
             
            MariaDB [test]> CREATE TABLE t2 (d INT) ENGINE=MyISAM;
            Query OK, 0 rows affected (0.009 sec)
             
            MariaDB [test]> SELECT a FROM t1 LEFT JOIN t2 ON a = d WHERE c < 'k' AND b > 't' ORDER BY a;
            +------+
            | a    |                                                                                                             
            +------+
            |    1 |
            |    5 |
            +------+
            2 rows in set (0.005 sec)
            

            I other words I've failed to reproduce the problem with your test case.

            igor Igor Babaev added a comment - Elena, Here's what I have on my laptop for the current state of 10.4: igor@stephan:~/maria-git/10.4/mysql-test> ../client/mysql test Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 8 Server version: 10.4.11-MariaDB-debug Source distribution   Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.   MariaDB [test]> SET @stats.save= @@innodb_stats_persistent; Query OK, 0 rows affected (0.001 sec)   MariaDB [test]> SET GLOBAL innodb_stats_persistent= ON; Query OK, 0 rows affected (0.000 sec)   MariaDB [test]> MariaDB [test]> CREATE TABLE t1 ( -> a INT, -> b VARCHAR(10), -> c VARCHAR(1024), -> KEY (b), -> KEY (c) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.034 sec)   MariaDB [test]> MariaDB [test]> INSERT INTO t1 VALUES -> (1,'w','z'), (1,'X','o'), (1,'q','c'), (5,'w','c'), (2,'j','m'), -> (2,'Q','s'), (9,'e','J'), (2,'p','W'), (9,'o','F'), (2,'g','S'), -> (1,'Y','a'), (NULL,'Y','p'), (NULL,'s','x'), (NULL,'i','S'), -> (1,'l','q'), (7,'r','e'), (4,'b','h'), (NULL,'E','c'), -> (NULL,'M','a'), (3,'e','X'), (NULL,'p','r'), (9,'e','i'); Query OK, 22 rows affected (0.029 sec) Records: 22 Duplicates: 0 Warnings: 0   MariaDB [test]> CREATE TABLE t2 (d INT) ENGINE=MyISAM; Query OK, 0 rows affected (0.009 sec)   MariaDB [test]> SELECT a FROM t1 LEFT JOIN t2 ON a = d WHERE c < 'k' AND b > 't' ORDER BY a; +------+ | a | +------+ | 1 | | 5 | +------+ 2 rows in set (0.005 sec) I other words I've failed to reproduce the problem with your test case.
            igor Igor Babaev added a comment - - edited

            If I run the test case with mtr I have the reported failure.
            This happens because with mtr a plan that uses a rowid_filter is chosen:

            +EXPLAIN EXTENDED
            +SELECT * FROM t1;
            +id     select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
            +1      SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    23      100.00
            +Warnings:
            +Note   1003    select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1`
            +EXPLAIN EXTENDED
            +SELECT a FROM t1 LEFT JOIN t2 ON a = d WHERE c < 'k' AND b > 't' ORDER BY a;
            +id     select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
            +1      SIMPLE  t2      system  NULL    NULL    NULL    NULL    0       0.00    Const row not found
            +1      SIMPLE  t1      range|filter    b,c     b|c     13|1027 NULL    5 (43%) 43.48   Using index condition; Using where; Using filesort; Using rowid filter
            +Warnings:
            +Note   1003    select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`c` < 'k' and `test`.`t1`.`b` > 't' order by `test`.`t1`.`a`
            

            While when running the query from the mysql client a plan with table scan is chosen:

            MariaDB [test]> EXPLAIN EXTENDED
                -> SELECT a FROM t1 LEFT JOIN t2 ON a = d WHERE c < 'k' AND b > 't' ORDER BY a;
            +------+-------------+-------+--------+---------------+------+---------+------+------+----------+-----------------------------+
            | id   | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
            +------+-------------+-------+--------+---------------+------+---------+------+------+----------+-----------------------------+
            |    1 | SIMPLE      | t2    | system | NULL          | NULL | NULL    | NULL | 0    |     0.00 | Const row not found         |
            |    1 | SIMPLE      | t1    | ALL    | b,c           | NULL | NULL    | NULL | 22   |    10.33 | Using where; Using filesort |
            +------+-------------+-------+--------+---------------+------+---------+------+------+----------+-----------------------------+
            2 rows in set, 1 warning (0.002 sec)
             
            MariaDB [test]> EXPLAIN EXTENDED
                -> SELECT * FROM t1;
            +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
            | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
            +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
            |    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 22   |   100.00 |       |
            +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
            1 row in set, 1 warning (0.001 sec)
            

            We can see that the estimate for number of rows for these two runs are different 23 and 23.
            This explains the different choices of plans.

            Now if I add these two rows to the table t1

             INSERT INTO t1 VALUES (3,'g','x'), (2,'h','y');
            

            I have the plan that uses rowid filter for mysql client as well though the estimate of the table rows are still different (24 - for mysql client, 25 - for mtr).

            Now the query fails with mysql client as well.

            Apparently the second table is not needed to get the failure.

            igor Igor Babaev added a comment - - edited If I run the test case with mtr I have the reported failure. This happens because with mtr a plan that uses a rowid_filter is chosen: +EXPLAIN EXTENDED +SELECT * FROM t1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 23 100.00 +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` +EXPLAIN EXTENDED +SELECT a FROM t1 LEFT JOIN t2 ON a = d WHERE c < 'k' AND b > 't' ORDER BY a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 system NULL NULL NULL NULL 0 0.00 Const row not found +1 SIMPLE t1 range|filter b,c b|c 13|1027 NULL 5 (43%) 43.48 Using index condition; Using where; Using filesort; Using rowid filter +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`c` < 'k' and `test`.`t1`.`b` > 't' order by `test`.`t1`.`a` While when running the query from the mysql client a plan with table scan is chosen: MariaDB [test]> EXPLAIN EXTENDED -> SELECT a FROM t1 LEFT JOIN t2 ON a = d WHERE c < 'k' AND b > 't' ORDER BY a; +------+-------------+-------+--------+---------------+------+---------+------+------+----------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+--------+---------------+------+---------+------+------+----------+-----------------------------+ | 1 | SIMPLE | t2 | system | NULL | NULL | NULL | NULL | 0 | 0.00 | Const row not found | | 1 | SIMPLE | t1 | ALL | b,c | NULL | NULL | NULL | 22 | 10.33 | Using where; Using filesort | +------+-------------+-------+--------+---------------+------+---------+------+------+----------+-----------------------------+ 2 rows in set, 1 warning (0.002 sec)   MariaDB [test]> EXPLAIN EXTENDED -> SELECT * FROM t1; +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 22 | 100.00 | | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.001 sec) We can see that the estimate for number of rows for these two runs are different 23 and 23. This explains the different choices of plans. Now if I add these two rows to the table t1 INSERT INTO t1 VALUES (3,'g','x'), (2,'h','y'); I have the plan that uses rowid filter for mysql client as well though the estimate of the table rows are still different (24 - for mysql client, 25 - for mtr). Now the query fails with mysql client as well. Apparently the second table is not needed to get the failure.
            igor Igor Babaev added a comment - - edited

            Using the command

             ANALYZE TABLE t1;
            

            allows to get the same estimate for number of rows in t1 with any run of the above test case.

            igor Igor Babaev added a comment - - edited Using the command ANALYZE TABLE t1; allows to get the same estimate for number of rows in t1 with any run of the above test case.
            igor Igor Babaev added a comment -

            A fix for this bug was pushed into 10.4

            igor Igor Babaev added a comment - A fix for this bug was pushed into 10.4

            People

              igor Igor Babaev
              elenst Elena Stepanova
              Votes:
              2 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.