Assertion `((Item_cond *) cond)->functype() == ((Item_cond *) new_item)->functype()' fails on a query with IN and equal conditions, AND/OR, materialization+semijoin
#12 0x0000000000705899 in handle_select (thd=0x23a18c8, lex=0x23a3db8, result=0x7f6ad4031fe8, setup_tables_done_option=0) at sql_select.cc:288
#13 0x0000000000691b72 in execute_sqlcom_select (thd=0x23a18c8, all_tables=0x7f6ad4019198) at sql_parse.cc:5172
#14 0x000000000068893a in mysql_execute_command (thd=0x23a18c8) at sql_parse.cc:2305
#15 0x00000000006945fc in mysql_parse (thd=0x23a18c8, rawbuf=0x7f6ad4018f40 "SELECT * FROM t1 WHERE 8 IN ( SELECT MIN(pk) FROM t1 ) AND ( pk = a OR pk = b )", length=79, found_semicolon=0x7f6aedf207e0) at sql_parse.cc:6173
#16 0x0000000000686057 in dispatch_command (command=COM_QUERY, thd=0x23a18c8, packet=0x241bb29 "SELECT * FROM t1 WHERE 8 IN ( SELECT MIN(pk) FROM t1 ) AND ( pk = a OR pk = b )", packet_length=79) at sql_parse.cc:1243
#17 0x00000000006852c8 in do_command (thd=0x23a18c8) at sql_parse.cc:923
#18 0x0000000000682162 in handle_one_connection (arg=0x23a18c8) at sql_connect.cc:1231
#19 0x00007f6aefc38e9a in start_thread (arg=0x7f6aedf21700) at pthread_create.c:308
#20 0x00007f6aef161cbd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
Before the revision 3676, the query didn't behave quite right, either. It produced the following error and valgrind warnings, although I don't know if they are related to the new assertion failure.
130817 13:31:33 [Warning] Plugin 'MEMORY' will be forced to shutdown
130817 13:31:33 [ERROR] Plugin 'MEMORY' has ref_count=1 after shutdown.
...
==6575== 8 bytes in 1 blocks are indirectly lost in loss record 1 of 7
==6575== at 0x4C2B6CD: malloc (in /usr/lib/valgrind/vgpreload_memcheck-amd64-linux.so)
==6575== by 0xBB13F9: my_malloc (my_malloc.c:42)
==6575== by 0x8EF0B2: intern_plugin_lock(st_lex*, st_plugin_int**) (sql_plugin.cc:861)
==6575== by 0x8EF23E: plugin_lock(THD*, st_plugin_int**) (sql_plugin.cc:904)
==6575== by 0x80FB7E: ha_lock_engine(THD*, handlerton const*) (handler.cc:172)
==6575== by 0x730342: create_tmp_table(THD*, TMP_TABLE_PARAM*, List<Item>&, st_order*, bool, bool, unsigned long long, unsigned long long, char*, bool) (sql_select.cc:14324)
==6575== by 0x804994: create_dummy_tmp_table(THD*) (opt_subselect.cc:4998)
==6575== by 0x805155: setup_jtbm_semi_joins(JOIN*, List<TABLE_LIST>*, Item**) (opt_subselect.cc:5171)
==6575== by 0x70D391: JOIN::optimize() (sql_select.cc:1015)
In the debugger I see that JOIN::optimize takes
(<in_optimizer>(8,<exists>(select min(`test`.`t1`.`pk`) from `test`.`t1`)) and ((`test`.`t1`.`pk` = `test`.`t1`.`a`) or (`test`.`t1`.`pk` = `test`.`t1`.`b`)))
as conds of the main query.
To my mind this is the result of a bug:
the expression '8 IN ( SELECT MIN(pk) FROM t1 )' should not be transformed into an in_optimizer predicate,
because (SELECT MIN(pk) FROM t1) yields just a number.
I reassign this bug to you.
Maybe you'll find other problems problems here, then please contact me.
Igor Babaev (Inactive)
added a comment - Sanja,
In the debugger I see that JOIN::optimize takes
(<in_optimizer>(8,<exists>(select min(`test`.`t1`.`pk`) from `test`.`t1`)) and ((`test`.`t1`.`pk` = `test`.`t1`.`a`) or (`test`.`t1`.`pk` = `test`.`t1`.`b`)))
as conds of the main query.
To my mind this is the result of a bug:
the expression '8 IN ( SELECT MIN(pk) FROM t1 )' should not be transformed into an in_optimizer predicate,
because (SELECT MIN(pk) FROM t1) yields just a number.
I reassign this bug to you.
Maybe you'll find other problems problems here, then please contact me.
subquery resilved with help of materialization that its we we execute normal subselect (single value return detected by aggregated function without group by)
Oleksandr Byelkin
added a comment - subquery resilved with help of materialization that its we we execute normal subselect (single value return detected by aggregated function without group by)
here is path of the decision about single row:
#1 0x0000000000804d5c in setup_jtbm_semi_joins (join=0x7fffdc032088, join_list=0x1b7acc0, join_where=0x7fffdc032468) at opt_subselect.cc:5105
(gdb) frame 2
#2 0x000000000070b170 in JOIN::optimize (this=0x7fffdc032088) at sql_select.cc:1015
(gdb) frame 0
#0 Item_in_subselect::optimize (this=0x7fffdc006110, out_rows=0x7ffff5842598, cost=0x7ffff58425a0) at item_subselect.cc:612
(gdb) where
#0 Item_in_subselect::optimize (this=0x7fffdc006110, out_rows=0x7ffff5842598, cost=0x7ffff58425a0) at item_subselect.cc:612
#1 0x0000000000804d5c in setup_jtbm_semi_joins (join=0x7fffdc032088, join_list=0x1b7acc0, join_where=0x7fffdc032468) at opt_subselect.cc:5105
#2 0x000000000070b170 in JOIN::optimize (this=0x7fffdc032088) at sql_select.cc:1015
#3 0x000000000071273f in mysql_select (thd=0x1b780b8, rref_pointer_array=0x1b7ada8, tables=0x7fffdc004d58, wild_num=1, fields=..., conds=0x7fffdc031e90, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0x7fffdc032068, unit=0x1b7a648, select_lex=0x1b7ab50) at sql_select.cc:2990
Oleksandr Byelkin
added a comment - here is path of the decision about single row:
#1 0x0000000000804d5c in setup_jtbm_semi_joins (join=0x7fffdc032088, join_list=0x1b7acc0, join_where=0x7fffdc032468) at opt_subselect.cc:5105
(gdb) frame 2
#2 0x000000000070b170 in JOIN::optimize (this=0x7fffdc032088) at sql_select.cc:1015
(gdb) frame 0
#0 Item_in_subselect::optimize (this=0x7fffdc006110, out_rows=0x7ffff5842598, cost=0x7ffff58425a0) at item_subselect.cc:612
(gdb) where
#0 Item_in_subselect::optimize (this=0x7fffdc006110, out_rows=0x7ffff5842598, cost=0x7ffff58425a0) at item_subselect.cc:612
#1 0x0000000000804d5c in setup_jtbm_semi_joins (join=0x7fffdc032088, join_list=0x1b7acc0, join_where=0x7fffdc032468) at opt_subselect.cc:5105
#2 0x000000000070b170 in JOIN::optimize (this=0x7fffdc032088) at sql_select.cc:1015
#3 0x000000000071273f in mysql_select (thd=0x1b780b8, rref_pointer_array=0x1b7ada8, tables=0x7fffdc004d58, wild_num=1, fields=..., conds=0x7fffdc031e90, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0x7fffdc032068, unit=0x1b7a648, select_lex=0x1b7ab50) at sql_select.cc:2990
(Me and Sanja have discussed this bug and concluded that the wrong part is a lacking call to fix_fields() in setup_jtbm_subqueries()).
Sergei Petrunia
added a comment - (Me and Sanja have discussed this bug and concluded that the wrong part is a lacking call to fix_fields() in setup_jtbm_subqueries()).
After this fix, running the main testsuite reports warnings like this:
130827 22:13:10 [Warning] Plugin 'MEMORY' will be forced to shutdown
130827 22:13:10 [ERROR] Plugin 'MEMORY' has ref_count=2 after shutdown.
The shortest case is:
./mysql-test-run t/subselect_mat.test
....
130827 22:16:08 [Warning] Plugin 'MEMORY' will be forced to shutdown
130827 22:16:08 [ERROR] Plugin 'MEMORY' has ref_count=1 after shutdown.
...
mysql-test-run: *** ERROR: There where errors/warnings in server logs after running test cases.
Sergei Petrunia
added a comment - After this fix, running the main testsuite reports warnings like this:
130827 22:13:10 [Warning] Plugin 'MEMORY' will be forced to shutdown
130827 22:13:10 [ERROR] Plugin 'MEMORY' has ref_count=2 after shutdown.
The shortest case is:
./mysql-test-run t/subselect_mat.test
....
130827 22:16:08 [Warning] Plugin 'MEMORY' will be forced to shutdown
130827 22:16:08 [ERROR] Plugin 'MEMORY' has ref_count=1 after shutdown.
...
mysql-test-run: *** ERROR: There where errors/warnings in server logs after running test cases.
Bug
Critical
Sanja,
In the debugger I see that JOIN::optimize takes
(<in_optimizer>(8,<exists>(select min(`test`.`t1`.`pk`) from `test`.`t1`)) and ((`test`.`t1`.`pk` = `test`.`t1`.`a`) or (`test`.`t1`.`pk` = `test`.`t1`.`b`)))
as conds of the main query.
To my mind this is the result of a bug:
the expression '8 IN ( SELECT MIN(pk) FROM t1 )' should not be transformed into an in_optimizer predicate,
because (SELECT MIN(pk) FROM t1) yields just a number.
I reassign this bug to you.
Maybe you'll find other problems problems here, then please contact me.