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

[Philip Stoev]

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

[Igor Babaev (igorb-seattle)]

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)

[Philip Stoev (pstoev-askmonty)]

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 Babaev (igorb-seattle)]

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;