merge

sql/sql_select.cc:
  Auto merged

1 files changed, 61 insertions, 5 deletions

diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index d4cc9c3192a..1bfd74f01ed 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -1160,9 +1160,9 @@ SELECT 0 IN (SELECT 1 FROM t1 a);
 EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 Warnings:
-Note	1003	select high_priority <in_optimizer>(0,<exists>(select 1 AS `Not_used` from test.t1 a where isnull(1) having <is_not_null_test>(1) limit 1)) AS `0 IN (SELECT 1 FROM t1 a)`
+Note	1003	select high_priority <in_optimizer>(0,<exists>(select 1 AS `Not_used` from test.t1 a limit 1)) AS `0 IN (SELECT 1 FROM t1 a)`
 INSERT INTO t1 (pseudo) VALUES ('test1');
 SELECT 0 IN (SELECT 1 FROM t1 a);
 0 IN (SELECT 1 FROM t1 a)
@@ -1170,9 +1170,9 @@ SELECT 0 IN (SELECT 1 FROM t1 a);
 EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 Warnings:
-Note	1003	select high_priority <in_optimizer>(0,<exists>(select 1 AS `Not_used` from test.t1 a where isnull(1) having <is_not_null_test>(1) limit 1)) AS `0 IN (SELECT 1 FROM t1 a)`
+Note	1003	select high_priority <in_optimizer>(0,<exists>(select 1 AS `Not_used` from test.t1 a limit 1)) AS `0 IN (SELECT 1 FROM t1 a)`
 drop table t1;
 CREATE TABLE `t1` (
 `i` int(11) NOT NULL default '0',
@@ -1629,7 +1629,63 @@ insert into t1 values ('aaa'), ('bbb'),('eee'),('mmm'),('ppp');
 select count(*) from t1 as w1 where w1.val in (select w2.val from t1 as w2 where w2.val like 'm%') and w1.val in (select w3.val from t1 as w3 where w3.val like 'e%');
 count(*)
 0
-drop table if exists t1;
+drop table t1;
+create table t1 (id int not null, text varchar(20) not null default '', primary key (id));
+insert into t1 (id, text) values (1, 'text1'), (2, 'text2'), (3, 'text3'), (4, 'text4'), (5, 'text5'), (6, 'text6'), (7, 'text7'), (8, 'text8'), (9, 'text9'), (10, 'text10'), (11, 'text11'), (12, 'text12');
+select * from t1 where id not in (select id from t1 where id < 8);
+id	text
+8	text8
+9	text9
+10	text10
+11	text11
+12	text12
+select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);
+id	text
+8	text8
+9	text9
+10	text10
+11	text11
+12	text12
+explain extended select * from t1 where id not in (select id from t1 where id < 8);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
+2	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index; Using where
+Warnings:
+Note	1003	select high_priority test.t1.id AS `id`,test.t1.text AS `text` from test.t1 where not(<in_optimizer>(test.t1.id,<exists>(<primary_index_lookup>(<cache>(test.t1.id) in t1 on PRIMARY where (test.t1.id < 8)))))
+explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	tt	ALL	NULL	NULL	NULL	NULL	12	Using where
+2	DEPENDENT SUBQUERY	t1	eq_ref	PRIMARY	PRIMARY	4	test.tt.id	7	Using where; Using index
+Warnings:
+Note	1276	Field or reference 'tt.id' of SELECT #2 was resolved in SELECT #1
+Note	1003	select high_priority test.tt.id AS `id`,test.tt.text AS `text` from test.t1 tt where not(exists(select test.t1.id AS `id` from test.t1 where ((test.t1.id < 8) and ((test.t1.id = test.tt.id) or isnull(test.t1.id))) having (test.t1.id is not null) limit 1))
+insert into t1 (id, text) values (1000, 'text1000'), (1001, 'text1001');
+create table t2 (id int not null, text varchar(20) not null default '', primary key (id));
+insert into t2 (id, text) values (1, 'text1'), (2, 'text2'), (3, 'text3'), (4, 'text4'), (5, 'text5'), (6, 'text6'), (7, 'text7'), (8, 'text8'), (9, 'text9'), (10, 'text10'), (11, 'text1'), (12, 'text2'), (13, 'text3'), (14, 'text4'), (15, 'text5'), (16, 'text6'), (17, 'text7'), (18, 'text8'), (19, 'text9'), (20, 'text10'),(21, 'text1'), (22, 'text2'), (23, 'text3'), (24, 'text4'), (25, 'text5'), (26, 'text6'), (27, 'text7'), (28, 'text8'), (29, 'text9'), (30, 'text10'), (31, 'text1'), (32, 'text2'), (33, 'text3'), (34, 'text4'), (35, 'text5'), (36, 'text6'), (37, 'text7'), (38, 'text8'), (39, 'text9'), (40, 'text10'), (41, 'text1'), (42, 'text2'), (43, 'text3'), (44, 'text4'), (45, 'text5'), (46, 'text6'), (47, 'text7'), (48, 'text8'), (49, 'text9'), (50, 'text10');
+select * from t1 a left join t2 b on (a.id=b.id or b.id is null) join t1 c on (if(isnull(b.id), 1000, b.id)=c.id);
+id	text	id	text	id	text
+1	text1	1	text1	1	text1
+2	text2	2	text2	2	text2
+3	text3	3	text3	3	text3
+4	text4	4	text4	4	text4
+5	text5	5	text5	5	text5
+6	text6	6	text6	6	text6
+7	text7	7	text7	7	text7
+8	text8	8	text8	8	text8
+9	text9	9	text9	9	text9
+10	text10	10	text10	10	text10
+11	text11	11	text1	11	text11
+12	text12	12	text2	12	text12
+1000	text1000	NULL	NULL	1000	text1000
+1001	text1001	NULL	NULL	1000	text1000
+explain extended select * from t1 a left join t2 b on (a.id=b.id or b.id is null) join t1 c on (if(isnull(b.id), 1000, b.id)=c.id);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	a	ALL	NULL	NULL	NULL	NULL	14	
+1	SIMPLE	b	eq_ref	PRIMARY	PRIMARY	4	test.a.id	2	
+1	SIMPLE	c	eq_ref	PRIMARY	PRIMARY	4	func	1	Using where
+Warnings:
+Note	1003	select high_priority test.a.id AS `id`,test.a.text AS `text`,test.b.id AS `id`,test.b.text AS `text`,test.c.id AS `id`,test.c.text AS `text` from test.t1 a left join test.t2 b on(((test.a.id = test.b.id) or isnull(test.b.id))) join test.t1 c where (if(isnull(test.b.id),1000,test.b.id) = test.c.id)
+drop table t1,t2;
 create table t1 (a int);
 insert into t1 values (1);
 explain select benchmark(1000, (select a from t1 where a=sha(rand())));