Bug#23800: Outer fields in correlated subqueries is used in a temporary table

created for sorting.

Any outer reference in a subquery was represented by an Item_field object.
If the outer select employs a temporary table all such fields should be
replaced with fields from that temporary table in order to point to the 
actual data. This replacement wasn't done and that resulted in a wrong
subquery evaluation and a wrong result of the whole query.

Now any outer field is represented by two objects - Item_field placed in the
outer select and Item_outer_ref in the subquery. Item_field object is
processed as a normal field and the reference to it is saved in the
ref_pointer_array. Thus the Item_outer_ref is always references the correct
field. The original field is substituted for a reference in the
Item_field::fix_outer_field() function.

New function called fix_inner_refs() is added to fix fields referenced from
inner selects and to fix references (Item_ref objects) to these fields.

The new Item_outer_ref class is a descendant of the Item_direct_ref class.
It additionally stores a reference to the original field and designed to
behave more like a field.


sql/item.cc:
  Bug#23800: Correlated sub query returning incorrect results when operated upon.
  Now all outer fields are substituted with references to them (Item_outer_ref objects)
  in the Item_field::fix_outer_field() function.
  The original field is saved in the Item_outer_ref object.
sql/item.h:
  Bug#23800: Correlated sub query returning incorrect results when operated upon.
  Added the Item_outer_ref class.
sql/mysql_priv.h:
  Bug#23800: Correlated sub query returning incorrect results when operated upon.
  Added the fix_inner_refs() function prototype.
sql/sql_delete.cc:
  Bug#23800: Correlated sub query returning incorrect results when operated upon.
  Added call to the fix_inner_refs() function.
sql/sql_select.cc:
  Bug#23800: Correlated sub query returning incorrect results when operated upon.
  The new function called fix_inner_refs() is added.
mysql-test/r/subselect.result:
  Added a test case for bug#23800: Correlated sub query returning incorrect results when
  operated upon.
sql/sql_update.cc:
  Bug#23800: Correlated sub query returning incorrect results when operated upon.
  Added call to the fix_inner_refs() function.
mysql-test/r/subselect3.result:
  Corrected test cases result after fix for bug#23800: Correlated sub query returning
  incorrect results when operated upon.
mysql-test/t/subselect.test:
  Added a test case for bug#23800: Correlated sub query returning incorrect results when
  operated upon.
sql/sql_lex.cc:
  Bug#23800: Correlated sub query returning incorrect results when operated upon.
  Added cleanup of the inner_refs_list.
sql/sql_lex.h:
  Bug#23800: Correlated sub query returning incorrect results when operated upon.
  The inner_refs_list is added to the SELECT_LEX class.

1 files changed, 11 insertions, 11 deletions

diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result
index 29143b9e504..b975ea8cbdc 100644
--- a/mysql-test/r/subselect3.result
+++ b/mysql-test/r/subselect3.result
@@ -29,7 +29,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	5	
 2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	6	Using where; Using temporary; Using filesort
 Warnings:
-Note	1276	Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
+Note	1276	Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `Z` from `test`.`t2`
 explain extended
 select a, oref from t2 
@@ -38,7 +38,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
 2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	6	Using where; Using temporary; Using filesort
 Warnings:
-Note	1276	Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
+Note	1276	Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having (<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))
 select a, oref, a in (
 select max(ie) from t1 where oref=t2.oref group by grp union
@@ -91,7 +91,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	
 2	DEPENDENT SUBQUERY	t1	index_subquery	a	a	5	func	2	Using where; Full scan on NULL key
 Warnings:
-Note	1276	Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
+Note	1276	Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,<in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a checking NULL where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) having trigcond(<is_not_null_test>(`test`.`t1`.`a`))))) AS `Z` from `test`.`t2`
 flush status;
 select oref, a from t2 where a in (select a from t1 where oref=t2.oref);
@@ -156,7 +156,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 2	DEPENDENT SUBQUERY	t1	ref_or_null	a	a	5	func	4	Using where; Full scan on NULL key
 2	DEPENDENT SUBQUERY	t2	ref	a	a	5	test.t1.b	1	Using where
 Warnings:
-Note	1276	Field or reference 't3.oref' of SELECT #2 was resolved in SELECT #1
+Note	1276	Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<in_optimizer>(`test`.`t3`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond(((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)))) having trigcond(<is_not_null_test>(`test`.`t1`.`a`)))) AS `Z` from `test`.`t3`
 drop table t1, t2, t3;
 create table t1 (a int NOT NULL, b int NOT NULL, key(a));
@@ -184,7 +184,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 2	DEPENDENT SUBQUERY	t1	ref	a	a	4	func	2	Using where; Full scan on NULL key
 2	DEPENDENT SUBQUERY	t2	ref	a	a	4	test.t1.b	1	Using where
 Warnings:
-Note	1276	Field or reference 't3.oref' of SELECT #2 was resolved in SELECT #1
+Note	1276	Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<in_optimizer>(`test`.`t3`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`))))) AS `Z` from `test`.`t3`
 drop table t1,t2,t3;
 create table t1 (oref int, grp int);
@@ -240,7 +240,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
 2	DEPENDENT SUBQUERY	t1	index_subquery	a	a	5	func	2	Using where; Full scan on NULL key
 Warnings:
-Note	1276	Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
+Note	1276	Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a checking NULL where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`)))))) AS `Z` from `test`.`t2`
 select a,b, oref, (a,b) in (select a,b from t1 where c=t2.oref) Z from t2;
 a	b	oref	Z
@@ -257,7 +257,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 2	DEPENDENT SUBQUERY	t1	ref_or_null	a	a	5	func	2	Using where; Full scan on NULL key
 2	DEPENDENT SUBQUERY	t4	ALL	NULL	NULL	NULL	NULL	100	Using where
 Warnings:
-Note	1276	Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
+Note	1276	Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t4` where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`))))) AS `Z` from `test`.`t2`
 select a,b, oref, 
 (a,b) in (select a,b from t1,t4 where c=t2.oref) Z 
@@ -302,7 +302,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	8	Using where
 2	DEPENDENT SUBQUERY	t1	index_subquery	idx	idx	5	func	4	Using where; Full scan on NULL key
 Warnings:
-Note	1276	Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
+Note	1276	Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on idx checking NULL where ((`test`.`t1`.`oref` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`ie1`) or isnull(`test`.`t1`.`ie1`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`ie2`) or isnull(`test`.`t1`.`ie2`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`ie1`)) and trigcond(<is_not_null_test>(`test`.`t1`.`ie2`)))))) AS `Z` from `test`.`t2` where ((`test`.`t2`.`b` = 10) and (`test`.`t2`.`a` = 10))
 drop table t1, t2;
 create table t1 (oref char(4), grp int, ie int);
@@ -432,7 +432,7 @@ alter table t1 add index idx(oref,ie);
 explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	7	
-2	DEPENDENT SUBQUERY	t1	ref_or_null	idx	idx	10	test.t2.oref,func	4	Using where; Using index; Full scan on NULL key
+2	DEPENDENT SUBQUERY	t1	ref_or_null	idx	idx	10	t2.oref,func	4	Using where; Using index; Full scan on NULL key
 select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
 oref	a	Z
 ee	NULL	NULL
@@ -457,7 +457,7 @@ group by grp having min(ie) > 1) Z
 from t2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	7	
-2	DEPENDENT SUBQUERY	t1	ref	idx	idx	5	test.t2.oref	2	Using where; Using temporary; Using filesort
+2	DEPENDENT SUBQUERY	t1	ref	idx	idx	5	t2.oref	2	Using where; Using temporary; Using filesort
 select oref, a, 
 a in (select min(ie) from t1 where oref=t2.oref 
 group by grp having min(ie) > 1) Z 
@@ -572,7 +572,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	7	
 2	DEPENDENT SUBQUERY	t1	index_subquery	idx	idx	5	func	4	Using where; Full scan on NULL key
 Warnings:
-Note	1276	Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
+Note	1276	Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on idx checking NULL where ((`test`.`t1`.`oref` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`ie1`) or isnull(`test`.`t1`.`ie1`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`ie2`) or isnull(`test`.`t1`.`ie2`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`ie1`)) and trigcond(<is_not_null_test>(`test`.`t1`.`ie2`)))))) AS `Z` from `test`.`t2`
 drop table t1,t2;
 create table t1 (oref char(4), grp int, ie int primary key);