summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/derived_view.result2
-rw-r--r--mysql-test/r/subselect.result41
-rw-r--r--mysql-test/r/subselect_mat.result2
-rw-r--r--mysql-test/r/subselect_no_mat.result41
-rw-r--r--mysql-test/r/subselect_no_opts.result41
-rw-r--r--mysql-test/r/subselect_no_scache.result41
-rw-r--r--mysql-test/r/subselect_no_semijoin.result41
-rw-r--r--mysql-test/r/subselect_sj_mat.result2
-rw-r--r--mysql-test/r/view_alias.result10
-rw-r--r--mysql-test/t/subselect.test24
-rw-r--r--sql/item_subselect.cc21
-rw-r--r--sql/sql_lex.cc5
12 files changed, 228 insertions, 43 deletions
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index 412786c48e2..73a9306c37b 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -1288,7 +1288,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where
Warnings:
Note 1276 Field or reference 't.a' of SELECT #3 was resolved in SELECT #1
-Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(exists(select 28 from `test`.`t3` where ('j' < `test`.`t1`.`a`)))
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(exists(select 28 from `test`.`t3` where ('j' < `test`.`t1`.`a`) limit 1))
SELECT * FROM (SELECT * FROM t1) AS t
WHERE EXISTS (SELECT t2.a FROM t3 RIGHT JOIN t2 ON (t3.a = t2.a)
WHERE t2.b < t.a);
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 2999f4cfc8f..0aa8a8192f0 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -341,7 +341,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 test.t6.clinic_uq 1 100.00 Using index
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 <expr_cache><`test`.`t6`.`clinic_uq`>(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`.`t6` where <expr_cache><`test`.`t6`.`clinic_uq`>(exists(select 1 from `test`.`t7` where (`test`.`t7`.`uq` = `test`.`t6`.`clinic_uq`) limit 1))
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;
@@ -1887,7 +1887,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(<expr_cache><`test`.`tt`.`id`>(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(<expr_cache><`test`.`tt`.`id`>(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) 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');
@@ -2423,7 +2423,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
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 <expr_cache><`test`.`up`.`a`>(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` where <expr_cache><`test`.`up`.`a`>(exists(select 1 from `test`.`t1` where (`test`.`t1`.`a` = `test`.`up`.`a`) limit 1))
drop table t1;
CREATE TABLE t1 (t1_a int);
INSERT INTO t1 VALUES (1);
@@ -4432,7 +4432,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
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 <expr_cache><`test`.`t1`.`a`>(exists(select 1 from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)))
+Note 1003 select 2 AS `2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(exists(select 1 from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`) limit 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));
@@ -4444,7 +4444,7 @@ NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
-Note 1003 select 2 AS `2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(exists((select 1 from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) union (select 1 from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`))))
+Note 1003 select 2 AS `2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(exists((select 1 from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) union (select 1 from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) limit 1))
DROP TABLE t1,t2;
create table t0(a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
@@ -7187,4 +7187,35 @@ id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
drop table t1,t2;
+#
+# MDEV-19429: Wrong query result with EXISTS and LIMIT 0
+#
+create table t10 (a int);
+insert into t10 values (1),(2),(3);
+create table t12 (a int);
+insert into t12 values (1),(2),(3);
+select * from t10 where exists (select * from t12 order by a limit 0);
+a
+explain select * from t10 where exists (select * from t12 order by a limit 0);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Zero limit
+prepare stmt1 from "select * from t10 where exists (select * from t12 order by a limit ?)";
+set @l=1;
+execute stmt1 using @l;
+a
+1
+2
+3
+set @l=2;
+execute stmt1 using @l;
+a
+1
+2
+3
+set @l=0;
+execute stmt1 using @l;
+a
+deallocate prepare stmt1;
+drop table t10, t12;
End of 5.5 tests
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index 0e10800331a..79d5fa37f16 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -2219,7 +2219,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
3 MATERIALIZED t3 ALL NULL NULL NULL NULL 8 100.00
Warnings:
Note 1276 Field or reference 'test.t1.f1' of SELECT #2 was resolved in SELECT #1
-Note 1003 select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where <expr_cache><`test`.`t1`.`f1`>(exists(select 1 from `test`.`t2` semi join (`test`.`t3`) join `test`.`t3` where ((`test`.`t3`.`i3` = `test`.`t2`.`i2`) and (`test`.`t1`.`f1` = `test`.`t3`.`f3`))))
+Note 1003 select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where <expr_cache><`test`.`t1`.`f1`>(exists(select 1 from `test`.`t2` semi join (`test`.`t3`) join `test`.`t3` where ((`test`.`t3`.`i3` = `test`.`t2`.`i2`) and (`test`.`t1`.`f1` = `test`.`t3`.`f3`)) limit 1))
# this checks the result set above
set @save_optimizer_switch= @@optimizer_switch;
set optimizer_switch= 'materialization=off,semijoin=off';
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index 5c21e746b60..7662e416a66 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -348,7 +348,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 test.t6.clinic_uq 1 100.00 Using index
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 <expr_cache><`test`.`t6`.`clinic_uq`>(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`.`t6` where <expr_cache><`test`.`t6`.`clinic_uq`>(exists(select 1 from `test`.`t7` where (`test`.`t7`.`uq` = `test`.`t6`.`clinic_uq`) limit 1))
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;
@@ -1894,7 +1894,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(<expr_cache><`test`.`tt`.`id`>(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(<expr_cache><`test`.`tt`.`id`>(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) 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');
@@ -2430,7 +2430,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
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 <expr_cache><`test`.`up`.`a`>(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` where <expr_cache><`test`.`up`.`a`>(exists(select 1 from `test`.`t1` where (`test`.`t1`.`a` = `test`.`up`.`a`) limit 1))
drop table t1;
CREATE TABLE t1 (t1_a int);
INSERT INTO t1 VALUES (1);
@@ -4436,7 +4436,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
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 <expr_cache><`test`.`t1`.`a`>(exists(select 1 from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)))
+Note 1003 select 2 AS `2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(exists(select 1 from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`) limit 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));
@@ -4448,7 +4448,7 @@ NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
-Note 1003 select 2 AS `2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(exists((select 1 from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) union (select 1 from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`))))
+Note 1003 select 2 AS `2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(exists((select 1 from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) union (select 1 from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) limit 1))
DROP TABLE t1,t2;
create table t0(a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
@@ -7184,6 +7184,37 @@ id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
drop table t1,t2;
+#
+# MDEV-19429: Wrong query result with EXISTS and LIMIT 0
+#
+create table t10 (a int);
+insert into t10 values (1),(2),(3);
+create table t12 (a int);
+insert into t12 values (1),(2),(3);
+select * from t10 where exists (select * from t12 order by a limit 0);
+a
+explain select * from t10 where exists (select * from t12 order by a limit 0);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Zero limit
+prepare stmt1 from "select * from t10 where exists (select * from t12 order by a limit ?)";
+set @l=1;
+execute stmt1 using @l;
+a
+1
+2
+3
+set @l=2;
+execute stmt1 using @l;
+a
+1
+2
+3
+set @l=0;
+execute stmt1 using @l;
+a
+deallocate prepare stmt1;
+drop table t10, t12;
End of 5.5 tests
set optimizer_switch=default;
select @@optimizer_switch like '%materialization=on%';
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index 8ab1f1bd3f9..0242eac3edd 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -344,7 +344,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 test.t6.clinic_uq 1 100.00 Using index
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`.`t6` where exists(select 1 from `test`.`t7` where (`test`.`t7`.`uq` = `test`.`t6`.`clinic_uq`) limit 1)
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;
@@ -1890,7 +1890,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(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) 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');
@@ -2426,7 +2426,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
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` where exists(select 1 from `test`.`t1` where (`test`.`t1`.`a` = `test`.`up`.`a`) limit 1)
drop table t1;
CREATE TABLE t1 (t1_a int);
INSERT INTO t1 VALUES (1);
@@ -4432,7 +4432,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
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` where exists(select 1 from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`) limit 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));
@@ -4444,7 +4444,7 @@ NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
Note 1276 Field or reference 'test.t1.a' of SELECT #3 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`)) union (select 1 from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)))
+Note 1003 select 2 AS `2` from `test`.`t1` where exists((select 1 from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) union (select 1 from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) limit 1)
DROP TABLE t1,t2;
create table t0(a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
@@ -7182,5 +7182,36 @@ id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
drop table t1,t2;
+#
+# MDEV-19429: Wrong query result with EXISTS and LIMIT 0
+#
+create table t10 (a int);
+insert into t10 values (1),(2),(3);
+create table t12 (a int);
+insert into t12 values (1),(2),(3);
+select * from t10 where exists (select * from t12 order by a limit 0);
+a
+explain select * from t10 where exists (select * from t12 order by a limit 0);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Zero limit
+prepare stmt1 from "select * from t10 where exists (select * from t12 order by a limit ?)";
+set @l=1;
+execute stmt1 using @l;
+a
+1
+2
+3
+set @l=2;
+execute stmt1 using @l;
+a
+1
+2
+3
+set @l=0;
+execute stmt1 using @l;
+a
+deallocate prepare stmt1;
+drop table t10, t12;
End of 5.5 tests
set @optimizer_switch_for_subselect_test=null;
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index f0afa7ef19c..15f29c55d10 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -347,7 +347,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 test.t6.clinic_uq 1 100.00 Using index
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`.`t6` where exists(select 1 from `test`.`t7` where (`test`.`t7`.`uq` = `test`.`t6`.`clinic_uq`) limit 1)
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(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) 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');
@@ -2429,7 +2429,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
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` where exists(select 1 from `test`.`t1` where (`test`.`t1`.`a` = `test`.`up`.`a`) limit 1)
drop table t1;
CREATE TABLE t1 (t1_a int);
INSERT INTO t1 VALUES (1);
@@ -4438,7 +4438,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
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` where exists(select 1 from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`) limit 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));
@@ -4450,7 +4450,7 @@ NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
Note 1276 Field or reference 'test.t1.a' of SELECT #3 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`)) union (select 1 from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)))
+Note 1003 select 2 AS `2` from `test`.`t1` where exists((select 1 from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) union (select 1 from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) limit 1)
DROP TABLE t1,t2;
create table t0(a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
@@ -7193,6 +7193,37 @@ id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
drop table t1,t2;
+#
+# MDEV-19429: Wrong query result with EXISTS and LIMIT 0
+#
+create table t10 (a int);
+insert into t10 values (1),(2),(3);
+create table t12 (a int);
+insert into t12 values (1),(2),(3);
+select * from t10 where exists (select * from t12 order by a limit 0);
+a
+explain select * from t10 where exists (select * from t12 order by a limit 0);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Zero limit
+prepare stmt1 from "select * from t10 where exists (select * from t12 order by a limit ?)";
+set @l=1;
+execute stmt1 using @l;
+a
+1
+2
+3
+set @l=2;
+execute stmt1 using @l;
+a
+1
+2
+3
+set @l=0;
+execute stmt1 using @l;
+a
+deallocate prepare stmt1;
+drop table t10, t12;
End of 5.5 tests
set optimizer_switch=default;
select @@optimizer_switch like '%subquery_cache=on%';
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index 0d5fbc3498a..c75f335c6ef 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -344,7 +344,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 test.t6.clinic_uq 1 100.00 Using index
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 <expr_cache><`test`.`t6`.`clinic_uq`>(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`.`t6` where <expr_cache><`test`.`t6`.`clinic_uq`>(exists(select 1 from `test`.`t7` where (`test`.`t7`.`uq` = `test`.`t6`.`clinic_uq`) limit 1))
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;
@@ -1890,7 +1890,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(<expr_cache><`test`.`tt`.`id`>(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(<expr_cache><`test`.`tt`.`id`>(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) 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');
@@ -2426,7 +2426,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
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 <expr_cache><`test`.`up`.`a`>(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` where <expr_cache><`test`.`up`.`a`>(exists(select 1 from `test`.`t1` where (`test`.`t1`.`a` = `test`.`up`.`a`) limit 1))
drop table t1;
CREATE TABLE t1 (t1_a int);
INSERT INTO t1 VALUES (1);
@@ -4432,7 +4432,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
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 <expr_cache><`test`.`t1`.`a`>(exists(select 1 from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)))
+Note 1003 select 2 AS `2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(exists(select 1 from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`) limit 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));
@@ -4444,7 +4444,7 @@ NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
-Note 1003 select 2 AS `2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(exists((select 1 from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) union (select 1 from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`))))
+Note 1003 select 2 AS `2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(exists((select 1 from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) union (select 1 from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) limit 1))
DROP TABLE t1,t2;
create table t0(a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
@@ -7182,6 +7182,37 @@ id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
drop table t1,t2;
+#
+# MDEV-19429: Wrong query result with EXISTS and LIMIT 0
+#
+create table t10 (a int);
+insert into t10 values (1),(2),(3);
+create table t12 (a int);
+insert into t12 values (1),(2),(3);
+select * from t10 where exists (select * from t12 order by a limit 0);
+a
+explain select * from t10 where exists (select * from t12 order by a limit 0);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Zero limit
+prepare stmt1 from "select * from t10 where exists (select * from t12 order by a limit ?)";
+set @l=1;
+execute stmt1 using @l;
+a
+1
+2
+3
+set @l=2;
+execute stmt1 using @l;
+a
+1
+2
+3
+set @l=0;
+execute stmt1 using @l;
+a
+deallocate prepare stmt1;
+drop table t10, t12;
End of 5.5 tests
set @optimizer_switch_for_subselect_test=null;
set @join_cache_level_for_subselect_test=NULL;
diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result
index cb4b63047e5..1e123f17cd3 100644
--- a/mysql-test/r/subselect_sj_mat.result
+++ b/mysql-test/r/subselect_sj_mat.result
@@ -2259,7 +2259,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
3 MATERIALIZED t3 ALL NULL NULL NULL NULL 8 100.00
Warnings:
Note 1276 Field or reference 'test.t1.f1' of SELECT #2 was resolved in SELECT #1
-Note 1003 select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where <expr_cache><`test`.`t1`.`f1`>(exists(select 1 from `test`.`t2` semi join (`test`.`t3`) join `test`.`t3` where ((`test`.`t3`.`i3` = `test`.`t2`.`i2`) and (`test`.`t1`.`f1` = `test`.`t3`.`f3`))))
+Note 1003 select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where <expr_cache><`test`.`t1`.`f1`>(exists(select 1 from `test`.`t2` semi join (`test`.`t3`) join `test`.`t3` where ((`test`.`t3`.`i3` = `test`.`t2`.`i2`) and (`test`.`t1`.`f1` = `test`.`t3`.`f3`)) limit 1))
# this checks the result set above
set @save_optimizer_switch= @@optimizer_switch;
set optimizer_switch= 'materialization=off,semijoin=off';
diff --git a/mysql-test/r/view_alias.result b/mysql-test/r/view_alias.result
index 72c4bf29f25..fc05e4f2276 100644
--- a/mysql-test/r/view_alias.result
+++ b/mysql-test/r/view_alias.result
@@ -90,22 +90,22 @@ CREATE TABLE t2 LIKE t1;
# Test alias in subquery
CREATE VIEW v1 AS SELECT a FROM t1 WHERE EXISTS (SELECT 1 FROM t2 AS b WHERE b.a = 0);
DROP VIEW v1;
-CREATE VIEW v1 AS select `test`.`t1`.`a` AS `a` from `test`.`t1` where exists(select 1 from `test`.`t2` `b` where (`b`.`a` = 0));
+CREATE VIEW v1 AS select `test`.`t1`.`a` AS `a` from `test`.`t1` where exists(select 1 from `test`.`t2` `b` where (`b`.`a` = 0) limit 1);
DROP VIEW v1;
# Test column alias in subquery
CREATE VIEW v1 AS SELECT a FROM t1 WHERE EXISTS (SELECT a AS alias FROM t1 GROUP BY alias);
SHOW CREATE VIEW v1;
View Create View character_set_client collation_connection
-v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` where exists(select `t1`.`a` AS `alias` from `t1` group by `t1`.`a`) latin1 latin1_swedish_ci
+v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` where exists(select `t1`.`a` AS `alias` from `t1` group by `t1`.`a` limit 1) latin1 latin1_swedish_ci
DROP VIEW v1;
-CREATE VIEW v1 AS select `test`.`t1`.`a` AS `a` from `test`.`t1` where exists(select `test`.`t1`.`a` AS `alias` from `test`.`t1` group by `test`.`t1`.`a`);
+CREATE VIEW v1 AS select `test`.`t1`.`a` AS `a` from `test`.`t1` where exists(select `test`.`t1`.`a` AS `alias` from `test`.`t1` group by `test`.`t1`.`a` limit 1);
DROP VIEW v1;
# Alias as the expression column name.
CREATE VIEW v1 AS SELECT a FROM t1 WHERE EXISTS (SELECT ' a ' AS alias FROM t1 GROUP BY alias);
SHOW CREATE VIEW v1;
View Create View character_set_client collation_connection
-v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` where exists(select ' a ' AS `alias` from `t1` group by ' a ') latin1 latin1_swedish_ci
+v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` where exists(select ' a ' AS `alias` from `t1` group by ' a ' limit 1) latin1 latin1_swedish_ci
DROP VIEW v1;
-CREATE VIEW v1 AS select `test`.`t1`.`a` AS `a` from `test`.`t1` where exists(select ' a ' AS `alias` from `test`.`t1` group by ' a ');
+CREATE VIEW v1 AS select `test`.`t1`.`a` AS `a` from `test`.`t1` where exists(select ' a ' AS `alias` from `test`.`t1` group by ' a ' limit 1);
DROP VIEW v1;
DROP TABLE t1, t2;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 4e35032a789..387935c2d5d 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -6075,4 +6075,28 @@ explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3;
drop table t1,t2;
+
+--echo #
+--echo # MDEV-19429: Wrong query result with EXISTS and LIMIT 0
+--echo #
+create table t10 (a int);
+insert into t10 values (1),(2),(3);
+create table t12 (a int);
+insert into t12 values (1),(2),(3);
+select * from t10 where exists (select * from t12 order by a limit 0);
+explain select * from t10 where exists (select * from t12 order by a limit 0);
+
+prepare stmt1 from "select * from t10 where exists (select * from t12 order by a limit ?)";
+
+set @l=1;
+execute stmt1 using @l;
+set @l=2;
+execute stmt1 using @l;
+set @l=0;
+execute stmt1 using @l;
+
+deallocate prepare stmt1;
+
+drop table t10, t12;
+
--echo End of 5.5 tests
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index afc42dc08d5..9d6fe5ac5ab 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -1432,13 +1432,20 @@ void Item_exists_subselect::fix_length_and_dec()
{
DBUG_ENTER("Item_exists_subselect::fix_length_and_dec");
init_length_and_dec();
- /*
- We need only 1 row to determine existence (i.e. any EXISTS that is not
- an IN always requires LIMIT 1)
- */
- thd->change_item_tree(&unit->global_parameters->select_limit,
- new Item_int((int32) 1));
- DBUG_PRINT("info", ("Set limit to 1"));
+ // If limit is not set or it is constant more than 1
+ if (!unit->global_parameters->select_limit ||
+ (unit->global_parameters->select_limit->basic_const_item() &&
+ unit->global_parameters->select_limit->val_int() > 1))
+ {
+ /*
+ We need only 1 row to determine existence (i.e. any EXISTS that is not
+ an IN always requires LIMIT 1)
+ */
+ thd->change_item_tree(&unit->global_parameters->select_limit,
+ new Item_int((int32) 1));
+ unit->global_parameters->explicit_limit= 1; // we set the limit
+ DBUG_PRINT("info", ("Set limit to 1"));
+ }
DBUG_VOID_RETURN;
}
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 28f56282bad..fe4dcfd1524 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -2456,14 +2456,13 @@ void st_select_lex::print_limit(THD *thd,
if (item && unit->global_parameters == this)
{
Item_subselect::subs_type subs_type= item->substype();
- if (subs_type == Item_subselect::EXISTS_SUBS ||
- subs_type == Item_subselect::IN_SUBS ||
+ if (subs_type == Item_subselect::IN_SUBS ||
subs_type == Item_subselect::ALL_SUBS)
{
return;
}
}
- if (explicit_limit)
+ if (explicit_limit && select_limit)
{
str->append(STRING_WITH_LEN(" limit "));
if (offset_limit)
View Lorry Controller Status