Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Critical
-
Resolution: Fixed
-
Affects Version/s: 5.3.12, 10.0, 5.5
-
Fix Version/s: 5.5.43
-
Component/s: Data Manipulation - Subquery, Optimizer
-
Labels:None
-
Environment:Arch Linux
Description
I have two SQL statements that is logically identical but MariaDB give different results.
The first statement produces no result while the second produced one.
I have test the same data on another DBMS and the results of the two are the same.
The two SQL statements are
1.
select way_id from way_tags_test where k = 'highway' and v in ( select type from way_types ) and way_id in ( select way_id from taxi.way_tags_test where k = 'name' ) ;
2.
select way_id from way_tags_test where k = 'name' and way_id in ( select way_id from way_tags_test where k='highway' and v in ( select type from way_types ) ) ;
The table contents are as below:
way_tags_test have two rows:
99979604 highway living_street 2 99979604 name 九华山 2
way_types have only one row:
1 motorway
DDLs for table are as below:
CREATE TABLE `way_tags_test` ( `way_id` bigint(20) NOT NULL, `k` varchar(255) DEFAULT NULL, `v` varchar(255) DEFAULT NULL, `version` bigint(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `way_types` ( `id` int(11) NOT NULL, `type` varchar(32) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Thanks for the report and the test case.
As a workaround, you can try optimizer_switch='semijoin=off' or optimizer_switch='materialization=off'.
Here is an MTR test case simplified just a little further.
The query returns 2 rows, even though the inner WHERE is impossible.
InnoDB seems important.
semijoin+materialization are important.
--source include/have_innodb.inc CREATE TABLE way_tags_test (way_id int(20), v varchar(255)) ENGINE=InnoDB; INSERT INTO way_tags_test VALUES (99979604,'living_street'),(99979604,'avenue'); CREATE TABLE way_types (type varchar(32)) ENGINE=InnoDB; INSERT INTO way_types VALUES ('motorway'); select * from way_tags_test where way_id in ( select way_id from way_tags_test WHERE v in ( select type from way_types ) ); DROP TABLE way_tags_test, way_types;
EXPLAIN:
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY way_tags_test ALL NULL NULL NULL NULL 2 100.00 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 2 MATERIALIZED way_types ALL NULL NULL NULL NULL 1 100.00 2 MATERIALIZED way_tags_test ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`way_tags_test`.`way_id` AS `way_id`,`test`.`way_tags_test`.`v` AS `v` from `test`.`way_tags_test` semi join (`test`.`way_types` join `test`.`way_tags_test`) where ((`test`.`way_tags_test`.`v` = `test`.`way_types`.`type`))
Debugging execution, one can see that way_types and way_tags_test do not have an attached WHERE clause. IN-equality way_tags_test.v = way_types.type is not checked anywhere.
The condition is in join->conds, make_join_select() correctly attaches it to table way_tags_test:
(gdb) p dbug_print_item(cond) $53 = 0x14ecb20 "((`j1`.`way_tags_test`.`v` = `j1`.`way_types`.`type`))"
but then, this code removes it :
#0 remove_sj_conds (tree=0x7fff9804a528) at /home/psergey/dev2/5.5/sql/opt_subselect.cc:3802 #1 0x000000000075d501 in setup_sj_materialization_part2 (sjm_tab=0x7fff98049ba8) at /home/psergey/dev2/5.5/sql/opt_subselect.cc:3627 #2 0x000000000065c8dd in make_join_readinfo (join=0x7fff9801aa90, options=0, no_jbuf_after=3) at /home/psergey/dev2/5.5/sql/sql_select.cc:10215 #3 0x0000000000645d09 in JOIN::optimize (this=0x7fff9801aa90) at /home/psergey/dev2/5.5/sql/sql_select.cc:1658 #4 0x000000000064ad69 in mysql_select (thd=0x2f5c390, rref_pointer_array=0x2f60000, tables=0x7fff98006598, wild_num=1, fields=..., conds=0x7fff9801a7e8, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fff9801aa70, unit=0x2f5f6b0, select_lex=0x2f5fd90) at /home/psergey/dev2/5.5/sql/sql_select.cc:3080 #5 0x000000000064166c in handle_select (thd=0x2f5c390, lex=0x2f5f600, result=0x7fff9801aa70, setup_tables_done_option=0) at /home/psergey/dev2/5.5/sql/sql_select.cc:319 #6 0x00000000006199a3 in execute_sqlcom_select (thd=0x2f5c390, all_tables=0x7fff98006598) at /home/psergey/dev2/5.5/sql/sql_parse.cc:4689 #7 0x00000000006123d0 in mysql_execute_command (thd=0x2f5c390) at /home/psergey/dev2/5.5/sql/sql_parse.cc:2234 #8 0x000000000061c61c in mysql_parse (thd=0x2f5c390, rawbuf=0x7fff980062c8 "select * from way_tags_test2 where way_id in ( select way_id from way_tags_test WHERE v in ( select type from way_types ))", length=129, parser_state=0x7ffff7f824f0) at /home/psergey/dev2/5.5/sql/sql_parse.cc:5909
Ok, the check in remove_sj_conds() / is_cond_sj_in_equality() seems to be incorrect.
The check intends to remove IN-equalities from subquery's join tabs (We cant check them, as they refer to outside tables. Outside table fields are not available when one is doing materialization).
However, the check returns true for the IN-equality of the grand-child subquery, which has been merged into the child subquery.
@Elena Stepanova, thank you for formatting the code, now the preview looks great!