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

LP:872735 - Assertion `tab->ref.use_count' failed in join_read_key_unlock_row() with derived_merge + InnoDB + DISTINCT

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • None
    • None
    • None

    Description

      Query:

      SELECT *
      FROM t1 , t2
      WHERE ( t2.a , t1.b ) NOT IN (
      SELECT DISTINCT c , a
      FROM ( SELECT * FROM t3 ) AS SQ1_alias1
      );

      asserts as follows:

      mysqld: sql_select.cc:15736: void join_read_key_unlock_row(st_join_table*): Assertion `tab->ref.use_count' failed.

      #8 0x006b0d98 in __assert_fail () from /lib/libc.so.6
      #9 0x0833ee94 in join_read_key_unlock_row (tab=0xb85a2e0) at sql_select.cc:15736
      #10 0x0833e093 in evaluate_join_record (join=0xb81cfe8, join_tab=0xb85a2e0, error=0) at sql_select.cc:15331
      #11 0x0833d8dc in sub_select (join=0xb81cfe8, join_tab=0xb85a2e0, end_of_records=false) at sql_select.cc:15107
      #12 0x0833d12f in do_select (join=0xb81cfe8, fields=0xb82218c, table=0x0, procedure=0x0) at sql_select.cc:14770
      #13 0x08321a61 in JOIN::exec (this=0xb81cfe8) at sql_select.cc:2679
      #14 0x0824c7ed in subselect_single_select_engine::exec (this=0xb804bb0) at item_subselect.cc:2982
      #15 0x08246539 in Item_subselect::exec (this=0xb804aa8) at item_subselect.cc:586
      #16 0x082469e0 in Item_in_subselect::exec (this=0xb804aa8) at item_subselect.cc:739
      #17 0x08248534 in Item_in_subselect::val_bool (this=0xb804aa8) at item_subselect.cc:1452
      #18 0x081e21e8 in Item::val_bool_result (this=0xb804aa8) at item.h:842
      #19 0x0820e62e in Item_in_optimizer::val_int (this=0xb816670) at item_cmpfunc.cc:1717
      #20 0x081ca44c in Item::val_bool (this=0xb816670) at item.cc:197
      #21 0x0820b1dc in Item_func_not::val_int (this=0xb815d78) at item_cmpfunc.cc:333
      #22 0x0833dc71 in evaluate_join_record (join=0xb817d60, join_tab=0xb81712c, error=0) at sql_select.cc:15202
      #23 0x0833d8dc in sub_select (join=0xb817d60, join_tab=0xb81712c, end_of_records=false) at sql_select.cc:15107
      #24 0x0833d12f in do_select (join=0xb817d60, fields=0xb795cdc, table=0x0, procedure=0x0) at sql_select.cc:14770
      #25 0x08321a61 in JOIN::exec (this=0xb817d60) at sql_select.cc:2679
      #26 0x0832228e in mysql_select (thd=0xb794208, rref_pointer_array=0xb795d84, tables=0xb802e48, wild_num=1, fields=..., conds=0xb815d78, og_num=0, order=0x0,
      group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0xb815e10, unit=0xb795968, select_lex=0xb795c48) at sql_select.cc:2900
      #27 0x0831a05f in handle_select (thd=0xb794208, lex=0xb79590c, result=0xb815e10, setup_tables_done_option=0) at sql_select.cc:283
      #28 0x082b482c in execute_sqlcom_select (thd=0xb794208, all_tables=0xb802e48) at sql_parse.cc:5112
      #29 0x082ab5e9 in mysql_execute_command (thd=0xb794208) at sql_parse.cc:2250
      #30 0x082b6e5b in mysql_parse (thd=0xb794208,
      rawbuf=0xb802c40 "SELECT *\nFROM t1 , t2 \nWHERE ( t2.a , t1.b ) NOT IN (\nSELECT DISTINCT c , a \nFROM ( SELECT * FROM t3 ) AS SQ1_alias1\n)",
      length=119, found_semicolon=0xa0afe228) at sql_parse.cc:6112
      #31 0x082a9238 in dispatch_command (command=COM_QUERY, thd=0xb794208, packet=0xb830529 "", packet_length=119) at sql_parse.cc:1221
      #32 0x082a8693 in do_command (thd=0xb794208) at sql_parse.cc:916
      #33 0x082a5677 in handle_one_connection (arg=0xb794208) at sql_connect.cc:1191
      #34 0x00821919 in start_thread () from /lib/libpthread.so.0
      #35 0x0076acce in clone () from /lib/libc.so.6

      explain:

      id select_type table type possible_keys key key_len ref rows filtered Extra
      1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00
      1 PRIMARY t1 ALL NULL NULL NULL NULL 1 100.00 Using where
      2 DEPENDENT SUBQUERY t3 index PRIMARY,c c 4 NULL 1 100.00 Using where
      Warnings:
      Note 1003 select `test`.`t1`.`b` AS `b`,1 AS `a` from `test`.`t1` join `test`.`t2` where (not(<in_optimizer>((1,`test`.`t1`.`b`),<exists>(select distinct `test`.`t3`.`c`,`test`.`t3`.`a` from `test`.`t3` where (((<cache>(1) = `test`.`t3`.`c`) or isnull(`test`.`t3`.`c`)) and ((<cache>(`test`.`t1`.`b`) = `test`.`t3`.`a`) or isnull(`test`.`t3`.`a`))) having (<is_not_null_test>(`test`.`t3`.`c`) and <is_not_null_test>(`test`.`t3`.`a`))))))

      minimal optimizer_switch: derived_merge=ON
      full optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

      bzr version-info:

      revision-id: <email address hidden>
      date: 2011-10-12 02:04:03 +0400
      build-date: 2011-10-12 11:49:39 +0300
      revno: 3224
      branch-nick: maria-5.3

      test case:

      --source include/have_innodb.inc
      CREATE TABLE t1 ( b int NOT NULL) ENGINE=InnoDB;
      INSERT IGNORE INTO t1 VALUES (9);

      CREATE TABLE t2 ( a int NOT NULL ) ;
      INSERT IGNORE INTO t2 VALUES (1);

      CREATE TABLE t3 ( a int NOT NULL , c int NOT NULL, d varchar(1) NOT NULL, KEY (c) , PRIMARY KEY (a)) ENGINE=InnoDB;
      INSERT IGNORE INTO t3 VALUES (14,4,'a'),(15,7,'b'),(16,4,'c'),(17,1,'d'),(18,9,'e'),(19,4,'f'),(20,8,'g');

      SET SESSION optimizer_switch='derived_merge=ON,subquery_cache=off';

      SELECT *
      FROM t1 , t2
      WHERE ( t2.a , t1.b ) NOT IN (
      SELECT DISTINCT c , a
      FROM ( SELECT * FROM t3 ) AS SQ1_alias1
      );

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23596 Assertion `tab->ref.use_count' failed in join_read_key_unlock_row

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            igor Igor Babaev (Inactive) added a comment -

            Re: Assertion `tab->ref.use_count' failed in join_read_key_unlock_row() with derived_merge + InnoDB + DISTINCT
            I failed to reproduce this bug with rev3224:

            igor@sophia:~/maria/maria-5.3-r3224/mysql-test> ../client/mysql test
            Welcome to the MariaDB monitor. Commands end with ; or \g.
            Your MariaDB connection id is 1
            Server version: 5.3.2-MariaDB-beta-debug Source distribution

            This software comes with ABSOLUTELY NO WARRANTY. This is free software,
            and you are welcome to modify and redistribute it under the GPL v2 license

            Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

            MariaDB [test]> CREATE TABLE t1 ( b int NOT NULL) ENGINE=InnoDB;
            Query OK, 0 rows affected (0.02 sec)

            MariaDB [test]> INSERT IGNORE INTO t1 VALUES (9);
            Query OK, 1 row affected (0.00 sec)

            MariaDB [test]>
            MariaDB [test]> CREATE TABLE t2 ( a int NOT NULL ) ;
            Query OK, 0 rows affected (0.01 sec)

            MariaDB [test]> INSERT IGNORE INTO t2 VALUES (1);
            Query OK, 1 row affected (0.00 sec)

            MariaDB [test]>
            MariaDB [test]> CREATE TABLE t3 ( a int NOT NULL , c int NOT NULL, d varchar(1) NOT NULL, KEY (c) , PRIMARY KEY (a)) ENGINE=InnoDB;
            Query OK, 0 rows affected (0.01 sec)

            MariaDB [test]> INSERT IGNORE INTO t3 VALUES (14,4,'a'),(15,7,'b'),(16,4,'c'),(17,1,'d'),(18,9,'e'),(19,4,'f'),(20,8,'g');
            Query OK, 7 rows affected (0.00 sec)
            Records: 7 Duplicates: 0 Warnings: 0

            MariaDB [test]>
            MariaDB [test]> SET SESSION optimizer_switch='derived_merge=ON,subquery_cache=off';
            Query OK, 0 rows affected (0.00 sec)

            MariaDB [test]>
            MariaDB [test]> SELECT *
            -> FROM t1 , t2
            -> WHERE ( t2.a , t1.b ) NOT IN (
            -> SELECT DISTINCT c , a
            -> FROM ( SELECT * FROM t3 ) AS SQ1_alias1
            -> );
            ----+

            b a

            ----+

            9 1

            ----+
            1 row in set (0.01 sec)

            MariaDB [test]> alter table t2 engine=innodb;
            Query OK, 1 row affected (0.02 sec)
            Records: 1 Duplicates: 0 Warnings: 0

            MariaDB [test]> SELECT * FROM t1 , t2 WHERE ( t2.a , t1.b ) NOT IN ( SELECT DISTINCT c , a FROM ( SELECT * FROM t3 ) AS SQ1_alias1 );
            ----+

            b a

            ----+

            9 1

            ----+
            1 row in set (0.00 sec)

            igor Igor Babaev (Inactive) added a comment - Re: Assertion `tab->ref.use_count' failed in join_read_key_unlock_row() with derived_merge + InnoDB + DISTINCT I failed to reproduce this bug with rev3224: igor@sophia:~/maria/maria-5.3-r3224/mysql-test> ../client/mysql test Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 1 Server version: 5.3.2-MariaDB-beta-debug Source distribution This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [test] > CREATE TABLE t1 ( b int NOT NULL) ENGINE=InnoDB; Query OK, 0 rows affected (0.02 sec) MariaDB [test] > INSERT IGNORE INTO t1 VALUES (9); Query OK, 1 row affected (0.00 sec) MariaDB [test] > MariaDB [test] > CREATE TABLE t2 ( a int NOT NULL ) ; Query OK, 0 rows affected (0.01 sec) MariaDB [test] > INSERT IGNORE INTO t2 VALUES (1); Query OK, 1 row affected (0.00 sec) MariaDB [test] > MariaDB [test] > CREATE TABLE t3 ( a int NOT NULL , c int NOT NULL, d varchar(1) NOT NULL, KEY (c) , PRIMARY KEY (a)) ENGINE=InnoDB; Query OK, 0 rows affected (0.01 sec) MariaDB [test] > INSERT IGNORE INTO t3 VALUES (14,4,'a'),(15,7,'b'),(16,4,'c'),(17,1,'d'),(18,9,'e'),(19,4,'f'),(20,8,'g'); Query OK, 7 rows affected (0.00 sec) Records: 7 Duplicates: 0 Warnings: 0 MariaDB [test] > MariaDB [test] > SET SESSION optimizer_switch='derived_merge=ON,subquery_cache=off'; Query OK, 0 rows affected (0.00 sec) MariaDB [test] > MariaDB [test] > SELECT * -> FROM t1 , t2 -> WHERE ( t2.a , t1.b ) NOT IN ( -> SELECT DISTINCT c , a -> FROM ( SELECT * FROM t3 ) AS SQ1_alias1 -> ); -- --+ b a -- --+ 9 1 -- --+ 1 row in set (0.01 sec) MariaDB [test] > alter table t2 engine=innodb; Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0 MariaDB [test] > SELECT * FROM t1 , t2 WHERE ( t2.a , t1.b ) NOT IN ( SELECT DISTINCT c , a FROM ( SELECT * FROM t3 ) AS SQ1_alias1 ); -- --+ b a -- --+ 9 1 -- --+ 1 row in set (0.00 sec)
            philipstoev Philip Stoev (Inactive) added a comment -

            Re: Assertion `tab->ref.use_count' failed in join_read_key_unlock_row() with derived_merge + InnoDB + DISTINCT
            It turns out this bug requires --key_buffer_size=1048576 . This is the difference between starting the server directly and starting it using MTRv1

            philipstoev Philip Stoev (Inactive) added a comment - Re: Assertion `tab->ref.use_count' failed in join_read_key_unlock_row() with derived_merge + InnoDB + DISTINCT It turns out this bug requires --key_buffer_size=1048576 . This is the difference between starting the server directly and starting it using MTRv1
            igor Igor Babaev (Inactive) added a comment -

            Re: Assertion `tab->ref.use_count' failed in join_read_key_unlock_row() with derived_merge + InnoDB + DISTINCT
            The bug can be easily reproduced with the following test case:

            CREATE TABLE t1 (b int NOT NULL);
            INSERT INTO t1 VALUES (9), (7);

            CREATE TABLE t2 (a int NOT NULL) ;
            INSERT INTO t2 VALUES (1), (2);

            CREATE TABLE t3 (
            a int NOT NULL , c int NOT NULL, d varchar(1) NOT NULL,
            KEY (c,a) , PRIMARY KEY (a)
            );
            INSERT INTO t3 VALUES
            (14,4,'a'), (15,7,'b'), (16,4,'c'), (17,1,'d'), (18,9,'e'),
            (19,4,'f'), (20,8,'g');

            SET SESSION optimizer_switch='derived_merge=on,subquery_cache=off';

            SELECT * FROM t1 , t2
            WHERE (t2.a ,t1.b) NOT IN (SELECT DISTINCT c,a FROM (SELECT * FROM t3) t);

            DROP TABLE t1,t2,t3;

            igor Igor Babaev (Inactive) added a comment - Re: Assertion `tab->ref.use_count' failed in join_read_key_unlock_row() with derived_merge + InnoDB + DISTINCT The bug can be easily reproduced with the following test case: CREATE TABLE t1 (b int NOT NULL); INSERT INTO t1 VALUES (9), (7); CREATE TABLE t2 (a int NOT NULL) ; INSERT INTO t2 VALUES (1), (2); CREATE TABLE t3 ( a int NOT NULL , c int NOT NULL, d varchar(1) NOT NULL, KEY (c,a) , PRIMARY KEY (a) ); INSERT INTO t3 VALUES (14,4,'a'), (15,7,'b'), (16,4,'c'), (17,1,'d'), (18,9,'e'), (19,4,'f'), (20,8,'g'); SET SESSION optimizer_switch='derived_merge=on,subquery_cache=off'; SELECT * FROM t1 , t2 WHERE (t2.a ,t1.b) NOT IN (SELECT DISTINCT c,a FROM (SELECT * FROM t3) t); DROP TABLE t1,t2,t3;
            ratzpo Rasmus Johansson (Inactive) added a comment -

            Launchpad bug id: 872735

            ratzpo Rasmus Johansson (Inactive) added a comment - Launchpad bug id: 872735

            People

              igor Igor Babaev (Inactive)
              philipstoev Philip Stoev (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              0 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.