diff options
Diffstat (limited to 'mysql-test/r/subselect_no_scache.result')
| -rw-r--r-- | mysql-test/r/subselect_no_scache.result | 40 |
1 files changed, 26 insertions, 14 deletions
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index ea11c39ab65..16ebf7a4199 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -343,11 +343,11 @@ patient_uq clinic_uq 2 2 explain extended select * from t6 where exists (select * from t7 where uq = clinic_uq); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t6 ALL NULL NULL NULL NULL 4 100.00 Using where -2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 test.t6.clinic_uq 1 100.00 Using index +1 PRIMARY t7 index PRIMARY PRIMARY 4 NULL 2 100.00 Using index +1 PRIMARY t6 ALL i1 NULL NULL NULL 4 75.00 Using where; Using join buffer (flat, BNL join) Warnings: Note 1276 Field or reference 'test.t6.clinic_uq' of SELECT #2 was resolved in SELECT #1 -Note 1003 select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS `clinic_uq` from `test`.`t6` where exists(select 1 from `test`.`t7` where (`test`.`t7`.`uq` = `test`.`t6`.`clinic_uq`)) +Note 1003 select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS `clinic_uq` from `test`.`t7` join `test`.`t6` where (`test`.`t6`.`clinic_uq` = `test`.`t7`.`uq`) select * from t1 where a= (select a from t2,t4 where t2.b=t4.b); ERROR 23000: Column 'a' in field list is ambiguous drop table t1,t2,t3; @@ -1893,7 +1893,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.tt.id 1 100.00 Using where; Using index Warnings: Note 1276 Field or reference 'test.tt.id' of SELECT #2 was resolved in SELECT #1 -Note 1003 select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where (not(exists(select `test`.`t1`.`id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null)))) +Note 1003 select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where (not(<in_optimizer>(1,exists(select `test`.`t1`.`id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null))))) 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'); @@ -2425,11 +2425,12 @@ a b 3 4 explain extended select * from t1 up where exists (select * from t1 where t1.a=up.a); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY up ALL NULL NULL NULL NULL 2 100.00 Using where -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where +1 PRIMARY up ALL NULL NULL NULL NULL 2 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 100.00 Warnings: Note 1276 Field or reference 'test.up.a' of SELECT #2 was resolved in SELECT #1 -Note 1003 select `test`.`up`.`a` AS `a`,`test`.`up`.`b` AS `b` from `test`.`t1` `up` where exists(select 1 from `test`.`t1` where (`test`.`t1`.`a` = `test`.`up`.`a`)) +Note 1003 select `test`.`up`.`a` AS `a`,`test`.`up`.`b` AS `b` from `test`.`t1` `up` semi join (`test`.`t1`) where 1 drop table t1; CREATE TABLE t1 (t1_a int); INSERT INTO t1 VALUES (1); @@ -4434,11 +4435,12 @@ SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a)); EXPLAIN EXTENDED SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a)); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00 Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 -Note 1003 select 2 AS `2` from `test`.`t1` where exists(select 1 from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) +Note 1003 select 2 AS `2` from `test`.`t1` semi join (`test`.`t2`) where 1 EXPLAIN EXTENDED SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION (SELECT 1 FROM t2 WHERE t1.a = t2.a)); @@ -5676,8 +5678,9 @@ EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 USE INDEX() WHERE t2.a = t1.a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a); a @@ -5686,8 +5689,9 @@ EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where -2 DEPENDENT SUBQUERY t2 ref idx idx 5 test.t1.a 2 Using index +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 index idx idx 5 NULL 3 Using index DROP TABLE t1,t2; # # BUG#752992: Wrong results for a subquery with 'semijoin=on' @@ -7013,6 +7017,14 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t1 index a a 5 NULL 2 Using where; Using index 2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index DROP TABLE t1,t2; +# +# MDEV-5991: crash in Item_field::used_tables +# +create table t1 (c int); +select exists(select 1 from t1 group by `c` in (select `c` from t1)); +exists(select 1 from t1 group by `c` in (select `c` from t1)) +0 +drop table t1; set optimizer_switch=default; select @@optimizer_switch like '%subquery_cache=on%'; @@optimizer_switch like '%subquery_cache=on%' |