Merge mysql.com:/home/psergey/mysql-5.0-bug8804-r12

into  mysql.com:/home/psergey/mysql-5.1-bug8804-r12-merge


sql/item_cmpfunc.cc:
  Auto merged
sql/item_cmpfunc.h:
  Auto merged
sql/item_subselect.h:
  Auto merged
sql/mysql_priv.h:
  Auto merged
sql/mysqld.cc:
  Auto merged
sql/sql_lex.h:
  Auto merged
sql/sql_select.cc:
  Auto merged
sql/sql_select.h:
  Auto merged
mysql-test/r/ndb_subquery.result:
  Manual merge
mysql-test/r/subselect.result:
  Manual merge
mysql-test/r/subselect3.result:
  Manual merge
sql/item_subselect.cc:
  Manual merge

1 files changed, 491 insertions, 14 deletions

diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result
index 10cc2729f3e..0b88b78198d 100644
--- a/mysql-test/r/subselect3.result
+++ b/mysql-test/r/subselect3.result
@@ -15,9 +15,8 @@ insert into t2 values
 (4, NULL),
 (2, NULL);
 select a, oref, a in (select max(ie) 
-from t1 where oref=t2.oref group by grp) from t2;
-a	oref	a in (select max(ie) 
-from t1 where oref=t2.oref group by grp)
+from t1 where oref=t2.oref group by grp) Z from t2;
+a	oref	Z
 1	1	1
 2	2	0
 3	3	NULL
@@ -25,14 +24,13 @@ NULL	4	0
 NULL	2	NULL
 explain extended
 select a, oref, a in (select max(ie) 
-from t1 where oref=t2.oref group by grp) from t2;
+from t1 where oref=t2.oref group by grp) Z from t2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	
 2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using temporary; Using filesort
 Warnings:
 Note	1276	Field or reference '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 `a in (select max(ie) 
-from t1 where oref=t2.oref group by grp)` from `test`.`t2`
+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 
 where a in (select max(ie) from t1 where oref=t2.oref group by grp);
@@ -42,6 +40,16 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 Warnings:
 Note	1276	Field or reference '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
+select max(ie) from t1 where oref=t2.oref group by grp
+) Z from t2;
+a	oref	Z
+1	1	1
+2	2	0
+3	3	NULL
+NULL	4	0
+NULL	2	NULL
 create table t3 (a int);
 insert into t3 values (NULL), (NULL);
 flush status;
@@ -79,12 +87,12 @@ oref	a	Z
 4	NULL	0
 explain extended 
 select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	100.00	
-2	DEPENDENT SUBQUERY	t1	index_subquery	a	a	5	func	2	100.00	Using index; Using where
+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	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`)))) AS `Z` from `test`.`t2`
+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);
 oref	a
@@ -143,11 +151,480 @@ explain extended
 select a, oref, 
 t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z 
 from t3;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	3	100.00	
-2	DEPENDENT SUBQUERY	t1	ref_or_null	a	a	5	func	4	100.00	Using where
-2	DEPENDENT SUBQUERY	t2	ref	a	a	5	test.t1.b	1	100.00	Using where
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	3	
+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	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));
+insert into t1 values 
+(0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
+create table t2 like t1;
+insert into t2 select * from t1;
+update t2 set b=1;
+create table t3 (a int, oref int);
+insert into t3 values (1, 1), (NULL,1), (NULL,0);
+select a, oref, 
+t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z 
+from t3;
+a	oref	Z
+1	1	1
+NULL	1	NULL
+NULL	0	0
+This must show a trig_cond:
+explain extended
+select a, oref, 
+t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z 
+from t3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	3	
+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	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);
+insert into t1 (oref, grp) values
+(1, 1),
+(1, 1);
+create table t2 (oref int, a int);
+insert into t2 values 
+(1, NULL),
+(2, NULL);
+select a, oref, 
+a in (select count(*) from t1 group by grp having grp=t2.oref) Z from t2;
+a	oref	Z
+NULL	1	NULL
+NULL	2	0
+This must show a trig_cond:
+explain extended
+select a, oref, 
+a in (select count(*) from t1 group by grp having grp=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	2	
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
+Warnings:
+Note	1276	Field or reference '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 count(0) AS `count(*)` from `test`.`t1` group by `test`.`t1`.`grp` having ((`test`.`t1`.`grp` = `test`.`t2`.`oref`) and trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(count(0))))))) AS `Z` from `test`.`t2`
+drop table t1, t2;
+create table t1 (a int, b int, primary key (a));
+insert into t1 values (1,1), (3,1),(100,1);
+create table t2 (a int, b int);
+insert into t2 values (1,1),(2,1),(NULL,1),(NULL,0);
+select a,b, a in (select a from t1 where t1.b = t2.b union select a from
+t1 where t1.b = t2.b) Z from t2 ;
+a	b	Z
+1	1	1
+2	1	0
+NULL	1	NULL
+NULL	0	0
+select a,b, a in (select a from t1 where t1.b = t2.b) Z from t2 ;
+a	b	Z
+1	1	1
+2	1	0
+NULL	1	NULL
+NULL	0	0
+drop table t1, t2;
+create table t3 (a int);
+insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2 (a int, b int, oref int);
+insert into t2 values (NULL,1, 100), (NULL,2, 100);
+create table t1 (a int, b int, c int, key(a,b));
+insert into t1 select 2*A, 2*A, 100 from t3;
+explain extended select a,b, oref, (a,b) in (select a,b from t1 where c=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	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	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
+NULL	1	100	0
+NULL	2	100	NULL
+create table t4 (x int);
+insert into t4 select A.a + 10*B.a from t1 A, t1 B;
+explain extended 
+select a,b, oref, 
+(a,b) in (select a,b from t1,t4 where c=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	2	
+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	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 
+from t2;
+a	b	oref	Z
+NULL	1	100	0
+NULL	2	100	NULL
+drop table t1,t2,t3,t4;
+create table t1 (oref char(4), grp int, ie1 int, ie2 int);
+insert into t1 (oref, grp, ie1, ie2) values
+('aa', 10, 2, 1),
+('aa', 10, 1, 1),
+('aa', 20, 2, 1),
+('bb', 10, 3, 1),
+('cc', 10, 4, 2),
+('cc', 20, 3, 2),
+('ee', 10, 2, 1),
+('ee', 10, 1, 2),
+('ff', 20, 2, 2),
+('ff', 20, 1, 2);
+create table t2 (oref char(4), a int, b int);
+insert into t2 values 
+('ee', NULL, 1),
+('bb', 2, 1),
+('ff', 2, 2),
+('cc', 3, NULL),
+('bb', NULL, NULL),
+('aa', 1, 1),
+('dd', 1, NULL);
+alter table t1 add index idx(ie1,ie2);
+select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=3 and b is null ;
+oref	a	b	Z
+cc	3	NULL	NULL
+insert into t2 values ('new1', 10,10);
+insert into t1 values ('new1', 1234, 10, NULL);
+select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=10 and b=10;
+oref	a	b	Z
+new1	10	10	NULL
+explain extended
+select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=10 and b=10;
+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	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);
+insert into t1 (oref, grp, ie) values
+('aa', 10, 2),
+('aa', 10, 1),
+('aa', 20, NULL),
+('bb', 10, 3),
+('cc', 10, 4),
+('cc', 20, NULL),
+('ee', 10, NULL),
+('ee', 10, NULL),
+('ff', 20, 2),
+('ff', 20, 1);
+create table t2 (oref char(4), a int);
+insert into t2 values 
+('ee', NULL),
+('bb', 2),
+('ff', 2),
+('cc', 3),
+('aa', 1),
+('dd', NULL),
+('bb', NULL);
+select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+oref	a	Z
+ee	NULL	NULL
+bb	2	0
+ff	2	1
+cc	3	NULL
+aa	1	1
+dd	NULL	0
+bb	NULL	NULL
+select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
+oref	a
+ff	2
+aa	1
+select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
+oref	a
+bb	2
+dd	NULL
+select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
+oref	a	Z
+ee	NULL	NULL
+bb	2	0
+ff	2	0
+cc	3	NULL
+aa	1	1
+dd	NULL	0
+bb	NULL	NULL
+select oref, a from t2 where 
+a in (select min(ie) from t1 where oref=t2.oref group by grp);
+oref	a
+aa	1
+select oref, a from t2 where 
+a not in (select min(ie) from t1 where oref=t2.oref group by grp);
+oref	a
+bb	2
+ff	2
+dd	NULL
+update t1 set ie=3 where oref='ff' and ie=1;
+select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by
+grp) Z from t2;
+oref	a	Z
+ee	NULL	NULL
+bb	2	0
+ff	2	1
+cc	3	NULL
+aa	1	1
+dd	NULL	0
+bb	NULL	NULL
+select oref, a from t2 where a in (select min(ie) from t1 where
+oref=t2.oref group by grp);
+oref	a
+ff	2
+aa	1
+select oref, a from t2 where a not in (select min(ie) from t1 where
+oref=t2.oref group by grp);
+oref	a
+bb	2
+dd	NULL
+select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by
+grp having min(ie) > 1) Z from t2;
+oref	a	Z
+ee	NULL	0
+bb	2	0
+ff	2	1
+cc	3	0
+aa	1	0
+dd	NULL	0
+bb	NULL	NULL
+select oref, a from t2 where a in (select min(ie) from t1 where
+oref=t2.oref group by grp having min(ie) > 1);
+oref	a
+ff	2
+select oref, a from t2 where a not in (select min(ie) from t1 where
+oref=t2.oref group by grp having min(ie) > 1);
+oref	a
+ee	NULL
+bb	2
+cc	3
+aa	1
+dd	NULL
+alter table t1 add index idx(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	index_subquery	idx	idx	5	func	4	Using where; 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
+bb	2	0
+ff	2	1
+cc	3	NULL
+aa	1	1
+dd	NULL	0
+bb	NULL	NULL
+select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
+oref	a
+ff	2
+aa	1
+select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
+oref	a
+bb	2
+dd	NULL
+alter table t1 drop index idx;
+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
+select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
+oref	a	Z
+ee	NULL	NULL
+bb	2	0
+ff	2	1
+cc	3	NULL
+aa	1	1
+dd	NULL	0
+bb	NULL	NULL
+select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
+oref	a
+ff	2
+aa	1
+select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
+oref	a
+bb	2
+dd	NULL
+explain 
+select oref, a, 
+a in (select min(ie) from t1 where oref=t2.oref 
+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
+select oref, a, 
+a in (select min(ie) from t1 where oref=t2.oref 
+group by grp having min(ie) > 1) Z 
+from t2;
+oref	a	Z
+ee	NULL	0
+bb	2	0
+ff	2	1
+cc	3	0
+aa	1	0
+dd	NULL	0
+bb	NULL	NULL
+select oref, a from t2 where a in (select min(ie) from t1 where oref=t2.oref 
+group by grp having min(ie) > 1);
+oref	a
+ff	2
+select oref, a from t2 where a not in (select min(ie) from t1 where oref=t2.oref 
+group by grp having min(ie) > 1);
+oref	a
+ee	NULL
+bb	2
+cc	3
+aa	1
+dd	NULL
+drop table t1,t2;
+create table t1 (oref char(4), grp int, ie1 int, ie2 int);
+insert into t1 (oref, grp, ie1, ie2) values
+('aa', 10, 2, 1),
+('aa', 10, 1, 1),
+('aa', 20, 2, 1),
+('bb', 10, 3, 1),
+('cc', 10, 4, 2),
+('cc', 20, 3, 2),
+('ee', 10, 2, 1),
+('ee', 10, 1, 2),
+('ff', 20, 2, 2),
+('ff', 20, 1, 2);
+create table t2 (oref char(4), a int, b int);
+insert into t2 values 
+('ee', NULL, 1),
+('bb', 2, 1),
+('ff', 2, 2),
+('cc', 3, NULL),
+('bb', NULL, NULL),
+('aa', 1, 1),
+('dd', 1, NULL);
+select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
+oref	a	b	Z
+ee	NULL	1	NULL
+bb	2	1	0
+ff	2	2	1
+cc	3	NULL	NULL
+bb	NULL	NULL	NULL
+aa	1	1	1
+dd	1	NULL	0
+select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref);
+oref	a	b
+ff	2	2
+aa	1	1
+select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref);
+oref	a	b
+bb	2	1
+dd	1	NULL
+select oref, a, b, 
+(a,b) in (select min(ie1),max(ie2) from t1 
+where oref=t2.oref group by grp) Z 
+from t2;
+oref	a	b	Z
+ee	NULL	1	0
+bb	2	1	0
+ff	2	2	0
+cc	3	NULL	NULL
+bb	NULL	NULL	NULL
+aa	1	1	1
+dd	1	NULL	0
+select oref, a, b from t2 where 
+(a,b) in (select min(ie1), max(ie2) from t1 where oref=t2.oref group by grp);
+oref	a	b
+aa	1	1
+select oref, a, b from t2 where
+(a,b) not in (select min(ie1), max(ie2) from t1 where oref=t2.oref group by grp);
+oref	a	b
+ee	NULL	1
+bb	2	1
+ff	2	2
+dd	1	NULL
+alter table t1 add index idx(ie1,ie2);
+explain select oref, a, b, (a,b) in (select ie1,ie2 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	index_subquery	idx	idx	5	func	4	Using where; Full scan on NULL key
+select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
+oref	a	b	Z
+ee	NULL	1	NULL
+bb	2	1	0
+ff	2	2	1
+cc	3	NULL	NULL
+bb	NULL	NULL	NULL
+aa	1	1	1
+dd	1	NULL	0
+select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref);
+oref	a	b
+ff	2	2
+aa	1	1
+select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref);
+oref	a	b
+bb	2	1
+dd	1	NULL
+explain extended 
+select oref, a, b, (a,b) in (select ie1,ie2 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	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	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);
+insert into t1 (oref, grp, ie) values
+('aa', 10, 2),
+('aa', 10, 1),
+('bb', 10, 3),
+('cc', 10, 4),
+('cc', 20, 5),
+('cc', 10, 6);
+create table t2 (oref char(4), a int);
+insert into t2 values 
+('ee', NULL),
+('bb', 2),
+('cc', 5),
+('cc', 2),
+('cc', NULL),
+('aa', 1),
+('bb', NULL);
+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	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using where; 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	0
+bb	2	0
+cc	5	1
+cc	2	0
+cc	NULL	NULL
+aa	1	1
+bb	NULL	NULL
+select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
+oref	a
+cc	5
+aa	1
+select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
+oref	a
+ee	NULL
+bb	2
+cc	2
+explain 
+select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) 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	ALL	NULL	NULL	NULL	NULL	6	Using where; Using temporary; Using filesort
+select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
+oref	a	Z
+ee	NULL	0
+bb	2	0
+cc	5	1
+cc	2	0
+cc	NULL	NULL
+aa	1	1
+bb	NULL	NULL
+drop table t1,t2;