[MDEV-7356] Subquery gives incorrect results Created: 2014-12-21  Updated: 2015-02-17  Resolved: 2015-02-17

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery, Optimizer
Affects Version/s: 5.3.12, 5.5, 10.0
Fix Version/s: 5.5.43

Type: Bug Priority: Critical
Reporter: TaoXu Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
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;



 Comments   
Comment by TaoXu [ 2014-12-21 ]

@Elena Stepanova, thank you for formatting the code, now the preview looks great!

Comment by Elena Stepanova [ 2014-12-21 ]

Thanks for the report and the test case.
As a workaround, you can try optimizer_switch='semijoin=off' or optimizer_switch='materialization=off'.

Comment by Elena Stepanova [ 2014-12-21 ]

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

Comment by Sergei Petrunia [ 2015-02-17 ]

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.

Comment by Sergei Petrunia [ 2015-02-17 ]

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

Comment by Sergei Petrunia [ 2015-02-17 ]

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.

Generated at Thu Feb 08 07:18:58 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.