[MDEV-2277] LP:664594 - Wrong subquery result with join_cache_level=6, even in .result files Created: 2010-10-21  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug664594.xml    

 Description   

mysql-test/r/subselect3.result shows:

explain select t21.* from t21,t22 where t21.a = t22.a and
t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 8 Using temporary; Using filesort
1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using join buffer
1 PRIMARY t22 ALL NULL NULL NULL NULL 26 Using where; Using join buffer
2 SUBQUERY t11 ALL NULL NULL NULL NULL 8 Using where
2 SUBQUERY t12 ALL NULL NULL NULL NULL 8 Using where; Using join buffer
select t21.* from t21,t22 where t21.a = t22.a and
t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
a b c
256 67 NULL

while
mysql-test/r/subselect3_jcl6.result shows:

explain select t21.* from t21,t22 where t21.a = t22.a and
t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 8 Using temporary; Using filesort
1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using join buffer
1 PRIMARY t22 ALL NULL NULL NULL NULL 26 Using where; Using join buffer
2 SUBQUERY t11 ALL NULL NULL NULL NULL 8 Using where
2 SUBQUERY t12 ALL NULL NULL NULL NULL 8 Using where; Using join buffer
select t21.* from t21,t22 where t21.a = t22.a and
t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
a b c
256 67 NULL
256 67 NULL

That is, the _jcl6 variant produces two records instead of one.

 Comments   
Comment by Sergei Petrunia [ 2010-10-21 ]

Re: Wrong subquery result with join_cache_level=6, even in .result files
Igor has fixed a wrong result in mwl128 tree with this change:

=== modified file 'sql/sql_select.cc'
— sql/sql_select.cc 2010-10-18 08:55:26 +0000
+++ sql/sql_select.cc 2010-10-21 16:14:41 +0000
@@ -9315,6 +9315,11 @@ Item *eliminate_item_equal(COND *cond, C
Item_equal *upper= item_field->find_item_equal(upper_levels);
Item_field *item= item_field;
TABLE_LIST *field_sjm= embedding_sjm(item_field);
+ if (!field_sjm)
+

{ + current_sjm= NULL; + current_sjm_head= NULL; + }

/*
Check if "item_field=head" equality is already guaranteed to be true

However, applying this patch to 5.3-main doesn't cause the right query result to be produced.

Perhaps, wrong result in 5.3 is due to a different problem then one in mwl128 tree, as MWL#128 tree has a different plan for the query (for an unknown reason).

Comment by Rasmus Johansson (Inactive) [ 2011-12-13 ]

Launchpad bug id: 664594

Generated at Thu Feb 08 06:40:46 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.