diff options
Diffstat (limited to 'mysql-test/r/subselect3.result')
| -rw-r--r-- | mysql-test/r/subselect3.result | 514 |
1 files changed, 512 insertions, 2 deletions
diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index ea2c3fc75af..d431906d3af 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -1,4 +1,4 @@ -drop table if exists t0, t1, t2, t3, t4, t5; +drop table if exists t0, t1, t2, t3, t4, t5, t11, t12, t21, t22; create table t1 (oref int, grp int, ie int) ; insert into t1 (oref, grp, ie) values (1, 1, 1), @@ -779,6 +779,15 @@ SELECT 1 FROM t1 WHERE t1.a NOT IN (SELECT 1 FROM t1, t2 WHERE 0); 1 1 DROP TABLE t1, t2; +create table t1 (a int, b decimal(13, 3)); +insert into t1 values (1, 0.123); +select a, (select max(b) from t1) into outfile "subselect.out.file.1" from t1; +delete from t1; +load data infile "subselect.out.file.1" into table t1; +select * from t1; +a b +1 0.123 +drop table t1; CREATE TABLE t1 ( pk INT PRIMARY KEY, int_key INT, @@ -864,11 +873,18 @@ Level Code Message Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2 Note 1276 Field or reference 'test.t1.c' of SELECT #3 was resolved in SELECT #2 Error 1054 Unknown column 'c' in 'field list' -Note 1003 select `c` AS `c` from (select (select count(`test`.`t1`.`a`) AS `COUNT(a)` from (select count(`test`.`t1`.`b`) AS `COUNT(b)` from `test`.`t1`) `x` group by `t1`.`c`) AS `(SELECT COUNT(a) FROM +Note 1003 select `c` AS `c` from (select (select count(`test`.`t1`.`a`) AS `COUNT(a)` from dual group by `c`) AS `(SELECT COUNT(a) FROM (SELECT COUNT(b) FROM t1) AS x GROUP BY c )` from `test`.`t1` group by `test`.`t1`.`b`) `y` DROP TABLE t1; End of 5.0 tests +# +# BUG#36896: Server crash on SELECT FROM DUAL +# +create table t1 (a int); +select 1 as res from dual where (1) in (select * from t1); +res +drop table t1; create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 ( @@ -895,3 +911,497 @@ t1.a < (select t4.a+10 from t4, t5 limit 2)); ERROR 21000: Subquery returns more than 1 row drop table t0, t1, t2, t3, t4, t5; +# +# BUG#48177 - SELECTs with NOT IN subqueries containing NULL +# values return too many records +# +CREATE TABLE t1 ( +i1 int DEFAULT NULL, +i2 int DEFAULT NULL +) ; +INSERT INTO t1 VALUES (1, NULL); +INSERT INTO t1 VALUES (2, 3); +INSERT INTO t1 VALUES (4, NULL); +INSERT INTO t1 VALUES (4, 0); +INSERT INTO t1 VALUES (NULL, NULL); +CREATE TABLE t2 ( +i1 int DEFAULT NULL, +i2 int DEFAULT NULL +) ; +INSERT INTO t2 VALUES (4, NULL); +INSERT INTO t2 VALUES (5, 0); + +Data in t1 +SELECT i1, i2 FROM t1; +i1 i2 +1 NULL +2 3 +4 NULL +4 0 +NULL NULL + +Data in subquery (should be filtered out) +SELECT i1, i2 FROM t2 ORDER BY i1; +i1 i2 +4 NULL +5 0 +FLUSH STATUS; + +SELECT i1, i2 +FROM t1 +WHERE (i1, i2) +NOT IN (SELECT i1, i2 FROM t2); +i1 i2 +1 NULL +2 3 + +# Check that the subquery only has to be evaluated once +# for all-NULL values even though there are two (NULL,NULL) records +# Baseline: +SHOW STATUS LIKE '%Handler_read_rnd_next'; +Variable_name Value +Handler_read_rnd_next 17 + +INSERT INTO t1 VALUES (NULL, NULL); +FLUSH STATUS; + +SELECT i1, i2 +FROM t1 +WHERE (i1, i2) +NOT IN (SELECT i1, i2 FROM t2); +i1 i2 +1 NULL +2 3 + +# Handler_read_rnd_next should be one more than baseline +# (read record from t1, but do not read from t2) +SHOW STATUS LIKE '%Handler_read_rnd_next'; +Variable_name Value +Handler_read_rnd_next 18 +DROP TABLE t1,t2; +End of 5.1 tests +CREATE TABLE t1 ( +a int(11) NOT NULL, +b int(11) NOT NULL, +c datetime default NULL, +PRIMARY KEY (a), +KEY idx_bc (b,c) +); +INSERT INTO t1 VALUES +(406989,67,'2006-02-23 17:08:46'), (150078,67,'2005-10-26 11:17:45'), +(406993,67,'2006-02-27 11:20:57'), (245655,67,'2005-12-08 15:59:08'), +(406994,67,'2006-02-27 11:26:46'), (256,67,NULL), +(398341,67,'2006-02-20 04:48:44'), (254,67,NULL),(1120,67,NULL), +(406988,67,'2006-02-23 17:07:22'), (255,67,NULL), +(398340,67,'2006-02-20 04:38:53'),(406631,67,'2006-02-23 10:49:42'), +(245653,67,'2005-12-08 15:59:07'),(406992,67,'2006-02-24 16:47:18'), +(245654,67,'2005-12-08 15:59:08'),(406995,67,'2006-02-28 11:55:00'), +(127261,67,'2005-10-13 12:17:58'),(406991,67,'2006-02-24 16:42:32'), +(245652,67,'2005-12-08 15:58:27'),(398545,67,'2006-02-20 04:53:13'), +(154504,67,'2005-10-28 11:53:01'),(9199,67,NULL),(1,67,'2006-02-23 15:01:35'), +(223456,67,NULL),(4101,67,NULL),(1133,67,NULL), +(406990,67,'2006-02-23 18:01:45'),(148815,67,'2005-10-25 15:34:17'), +(148812,67,'2005-10-25 15:30:01'),(245651,67,'2005-12-08 15:58:27'), +(154503,67,'2005-10-28 11:52:38'); +create table t11 select * from t1 where b = 67 AND (c IS NULL OR c > NOW()) order by 3 asc; +create table t12 select * from t1 where b = 67 AND (c IS NULL OR c > NOW()) order by 3 desc; +create table t21 select * from t1 where b = 67 AND (c IS NULL OR c > '2005-12-08') order by 3 asc; +create table t22 select * from t1 where b = 67 AND (c IS NULL OR c > '2005-12-08') order by 3 desc; +update t22 set c = '2005-12-08 15:58:27' where a = 255; +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 t11 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort; Start materialize; Scan +1 PRIMARY t12 ALL NULL NULL NULL NULL 8 Using where; End materialize; Using join buffer +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 +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 +drop table t1, t11, t12, t21, t22; +create table t1(a int); +insert into t1 values (0),(1); +set @@optimizer_switch='firstmatch=off'; +explain +select (select max(Y.a) from t1 Y where a in (select a from t1 Z) and a < X.a) as subq from t1 X; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY X ALL NULL NULL NULL NULL 2 +2 DEPENDENT SUBQUERY Y ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY Z ALL NULL NULL NULL NULL 2 Materialize +select (select max(Y.a) from t1 Y where a in (select a from t1 Z) and a < X.a) as subq from t1 X; +subq +NULL +0 +set @@optimizer_switch=default; +drop table t1; +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 as select * from t0; +insert into t1 select a+10 from t0; +set @@optimizer_switch='firstmatch=off,materialization=off'; +insert into t0 values(2); +explain select * from t1 where 2 in (select a from t0); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t0 ALL NULL NULL NULL NULL 11 Using where; Start temporary; End temporary +1 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using join buffer +select * from t1 where 2 in (select a from t0); +a +0 +1 +2 +3 +4 +5 +6 +7 +8 +9 +10 +11 +12 +13 +14 +15 +16 +17 +18 +19 +set @@optimizer_switch='default,materialization=off'; +explain select * from t1 where 2 in (select a from t0); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t0 ALL NULL NULL NULL NULL 11 Using where; FirstMatch +1 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using join buffer +select * from t1 where 2 in (select a from t0); +a +0 +1 +2 +3 +4 +5 +6 +7 +8 +9 +10 +11 +12 +13 +14 +15 +16 +17 +18 +19 +set @@optimizer_switch=default; +explain select * from (select a from t0) X where a in (select a from t1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 11 +1 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using where; FirstMatch(<derived2>) +2 DERIVED t0 ALL NULL NULL NULL NULL 11 +drop table t0, t1; +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (kp1 int, kp2 int, c int, filler char(100), key(kp1, kp2)); +insert into t1 select A.a+10*(B.a+10*C.a), 0, 0, 'filler' from t0 A, t0 B, t0 C; +insert into t1 select * from t1 where kp1 < 20; +create table t3 (a int); +insert into t3 select A.a + 10*B.a from t0 A, t0 B; +explain select * from t3 where a in (select kp1 from t1 where kp1<20); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; Using index; LooseScan +1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer +create table t4 (pk int primary key); +insert into t4 select a from t3; +explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20 +and t4.pk=t1.c); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using index condition; Using where; Using MRR; LooseScan +1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index; FirstMatch(t1) +1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer +drop table t1, t3, t4; +create table t1 (a int) as select * from t0 where a < 5; +set @save_max_heap_table_size=@@max_heap_table_size; +set @@optimizer_switch='firstmatch=off,materialization=off'; +set @@max_heap_table_size= 16384; +explain select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY E ALL NULL NULL NULL NULL 5 Start temporary +1 PRIMARY A ALL NULL NULL NULL NULL 10 Using join buffer +1 PRIMARY B ALL NULL NULL NULL NULL 10 Using join buffer +1 PRIMARY C ALL NULL NULL NULL NULL 10 Using join buffer +1 PRIMARY D ALL NULL NULL NULL NULL 10 Using where; End temporary; Using join buffer +flush status; +select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E); +count(*) +5000 +show status like 'Created_tmp_disk_tables'; +Variable_name Value +Created_tmp_disk_tables 1 +set @save_max_heap_table_size=@@max_heap_table_size; +set @@optimizer_switch=default; +drop table t0, t1; +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2(a int); +insert into t2 values (1),(2); +create table t3 ( a int , filler char(100), key(a)); +insert into t3 select A.a + 10*B.a, 'filler' from t0 A, t0 B; +explain select * from t3 where a in (select a from t2) and (a > 5 or a < 10); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Materialize; Scan +1 PRIMARY t3 ref a a 5 test.t2.a 1 +select * from t3 where a in (select a from t2); +a filler +1 filler +2 filler +drop table t0, t2, t3; +set @@optimizer_switch='firstmatch=off,materialization=off'; +create table t1 (a date); +insert into t1 values ('2008-01-01'),('2008-01-01'),('2008-02-01'),('2008-02-01'); +create table t2 (a int); +insert into t2 values (1),(2); +create table t3 (a char(10)); +insert into t3 select * from t1; +insert into t3 values (1),(2); +explain select * from t2 where a in (select a from t1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Start temporary +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; End temporary; Using join buffer +explain select * from t2 where a in (select a from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Start temporary +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer +explain select * from t2 where a in (select a from t3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Start temporary +1 PRIMARY t3 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer +explain select * from t1 where a in (select a from t3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Start temporary +1 PRIMARY t3 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer +drop table t1, t2, t3; +create table t1 (a decimal); +insert into t1 values (1),(2); +explain select * from t1 where a in (select a from t1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Start temporary +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer +drop table t1; +set @@optimizer_switch=default; +create table t1 (a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2 as select * from t1; +create table t3 (a int, b int, filler char(100), key(a)); +insert into t3 select A.a + 10*B.a, A.a + 10*B.a, 'filler' from t1 A, t1 B, t1 C; +explain select * from t1, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30) and t1.a =3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where; Materialize; Scan +1 PRIMARY t3 ref a a 5 test.t2.a 10 +explain select straight_join * from t1 A, t1 B where A.a in (select a from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY A ALL NULL NULL NULL NULL 10 Using where +1 PRIMARY B ALL NULL NULL NULL NULL 10 Using join buffer +2 SUBQUERY t2 ALL NULL NULL NULL NULL 10 +explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where +2 SUBQUERY A ALL NULL NULL NULL NULL 10 +2 SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer +explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where +2 SUBQUERY A ALL NULL NULL NULL NULL 10 +2 SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer +explain select straight_join * from t2 X, t2 Y +where X.a in (select straight_join A.a from t1 A, t1 B); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY X ALL NULL NULL NULL NULL 10 Using where +1 PRIMARY Y ALL NULL NULL NULL NULL 10 Using join buffer +2 SUBQUERY A ALL NULL NULL NULL NULL 10 +2 SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer +create table t0 (a int, b int); +insert into t0 values(1,1); +explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t0 system NULL NULL NULL NULL 1 +1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where; Materialize; Scan +1 PRIMARY t3 ref a a 5 test.t2.a 10 +create table t4 as select a as x, a as y from t1; +explain select * from t0, t3 where (t3.a, t3.b) in (select x,y from t4) and (t3.a < 10 or t3.a >30); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t0 system NULL NULL NULL NULL 1 +1 PRIMARY t4 ALL NULL NULL NULL NULL 10 Using where; Materialize; Scan +1 PRIMARY t3 ref a a 5 test.t4.x 10 Using where +drop table t0,t1,t2,t3,t4; +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (a int, b int, filler char(100), key(a,b)); +insert into t1 select A.a, B.a, 'filler' from t0 A, t0 B; +create table t2 as select * from t1; +explain select * from t2 where a in (select b from t1 where a=3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 range a a 5 NULL 8 Using where; Using index; LooseScan +1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer +explain select * from t2 where (b,a) in (select a,b from t1 where a=3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 range a a 5 NULL 8 Using where; Using index; LooseScan +1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer +drop table t1,t2; +create table t1 (a int, b int); +insert into t1 select a,a from t0; +create table t2 (a int, b int); +insert into t2 select A.a + 10*B.a, A.a + 10*B.a from t0 A, t0 B; +set @@optimizer_switch='firstmatch=off'; +explain select * from t1 where (a,b) in (select a,b from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 10 +1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Materialize +set @save_optimizer_search_depth=@@optimizer_search_depth; +set @@optimizer_search_depth=63; +explain select * from t1 where (a,b) in (select a,b from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 10 +1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Materialize +set @@optimizer_search_depth=@save_optimizer_search_depth; +set @@optimizer_switch=default; +drop table t0, t1, t2; +create table t0 (a decimal(4,2)); +insert into t0 values (10.24), (22.11); +create table t1 as select * from t0; +insert into t1 select * from t0; +explain select * from t0 where a in (select a from t1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t0 ALL NULL NULL NULL NULL 2 +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; FirstMatch(t0) +select * from t0 where a in (select a from t1); +a +10.24 +22.11 +drop table t0, t1; +create table t0(a date); +insert into t0 values ('2008-01-01'),('2008-02-02'); +create table t1 as select * from t0; +insert into t1 select * from t0; +explain select * from t0 where a in (select a from t1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t0 ALL NULL NULL NULL NULL 2 +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; FirstMatch(t0) +select * from t0 where a in (select a from t1); +a +2008-01-01 +2008-02-02 +drop table t0, t1; +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 as select a as a, a as b, a as c from t0 where a < 3; +create table t2 as select a as a, a as b from t0 where a < 3; +insert into t2 select * from t2; +explain select * from t1 where (a,b,c) in (select X.a, Y.a, Z.a from t2 X, t2 Y, t2 Z where X.b=33); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 +1 PRIMARY X ALL NULL NULL NULL NULL 6 Using where; Start materialize +1 PRIMARY Y ALL NULL NULL NULL NULL 6 Using join buffer +1 PRIMARY Z ALL NULL NULL NULL NULL 6 End materialize; Using join buffer +drop table t0,t1,t2; + +BUG#37842: Assertion in DsMrr_impl::dsmrr_init, at handler.cc:4307 + +CREATE TABLE t1 ( +`pk` int(11) NOT NULL AUTO_INCREMENT, +`int_key` int(11) DEFAULT NULL, +PRIMARY KEY (`pk`), +KEY `int_key` (`int_key`) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,9),(2,3),(3,8),(4,6),(5,9),(6,5),(7,5),(8,9),(9,1),(10,10); +SELECT `pk` FROM t1 AS OUTR WHERE `int_key` = ALL ( +SELECT `int_key` FROM t1 AS INNR WHERE INNR . `pk` >= 9 +); +pk +DROP TABLE t1; + +BUG#40118 Crash when running Batched Key Access and requiring one match for each key + +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (a int, key(a)); +insert into t1 select * from t0; +alter table t1 add b int not null, add filler char(200); +insert into t1 select * from t1; +insert into t1 select * from t1; +set @save_join_cache_level=@@join_cache_level; +set join_cache_level=6; +select * from t0 where t0.a in (select t1.a from t1 where t1.b=0); +a +0 +1 +2 +3 +4 +5 +6 +7 +8 +9 +set join_cache_level=@save_join_cache_level; +drop table t0, t1; +# +# BUG#32665 Query with dependent subquery is too slow +# +create table t1 ( +idIndividual int primary key +); +insert into t1 values (1),(2); +create table t2 ( +idContact int primary key, +contactType int, +idObj int +); +insert into t2 values (1,1,1),(2,2,2),(3,3,3); +create table t3 ( +idAddress int primary key, +idContact int, +postalStripped varchar(100) +); +insert into t3 values (1,1, 'foo'), (2,2,'bar'); +The following must be converted to a semi-join: +explain extended SELECT a.idIndividual FROM t1 a +WHERE a.idIndividual IN +( SELECT c.idObj FROM t3 cona +INNER JOIN t2 c ON c.idContact=cona.idContact +WHERE cona.postalStripped='T2H3B2' + ); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY cona ALL NULL NULL NULL NULL 2 100.00 Using where; Start materialize; Scan +1 PRIMARY c eq_ref PRIMARY PRIMARY 4 test.cona.idContact 1 100.00 End materialize +1 PRIMARY a index PRIMARY PRIMARY 4 NULL 2 100.00 Using where; Using index; Using join buffer +Warnings: +Note 1003 select `test`.`a`.`idIndividual` AS `idIndividual` from `test`.`t1` `a` semi join (`test`.`t3` `cona` join `test`.`t2` `c`) where ((`test`.`c`.`idContact` = `test`.`cona`.`idContact`) and (`test`.`a`.`idIndividual` = `test`.`c`.`idObj`) and (`test`.`cona`.`postalStripped` = 'T2H3B2')) +drop table t1,t2,t3; +# +# BUG#47367 Crash in Name_resolution_context::process_error +# +SET SESSION optimizer_switch = 'default,semijoin=off'; +CREATE TABLE t1 (f1 INTEGER); +CREATE TABLE t2 LIKE t1; +CREATE PROCEDURE p1 () BEGIN SELECT f1 FROM t1 WHERE f1 IN (SELECT f1 FROM t2); END| +CALL p1; +f1 +ALTER TABLE t2 CHANGE COLUMN f1 my_column INT; +CALL p1; +f1 +DROP PROCEDURE p1; +# Restore the original column list of table t2: +ALTER TABLE t2 CHANGE COLUMN my_column f1 INT; +SET SESSION optimizer_switch = 'semijoin=on'; +# Recreate procedure so that we eliminate any caching effects +CREATE PROCEDURE p1 () BEGIN SELECT f1 FROM t1 WHERE f1 IN (SELECT f1 FROM t2); END| +CALL p1; +f1 +ALTER TABLE t2 CHANGE COLUMN f1 my_column INT; +CALL p1; +ERROR 42S22: Unknown column 'f1' in 'where clause' +DROP PROCEDURE p1; +DROP TABLE t1, t2; |