diff options
Diffstat (limited to 'mysql-test/t/subselect_exists2in.test')
-rw-r--r-- | mysql-test/t/subselect_exists2in.test | 40 |
1 files changed, 40 insertions, 0 deletions
diff --git a/mysql-test/t/subselect_exists2in.test b/mysql-test/t/subselect_exists2in.test index a4fdbe5c50b..5a8ddb3612f 100644 --- a/mysql-test/t/subselect_exists2in.test +++ b/mysql-test/t/subselect_exists2in.test @@ -786,6 +786,46 @@ set optimizer_switch= @optimizer_switch_save; DROP TABLE t1; +--echo # +--echo # MDEV-14164: Unknown column error when adding aggregate to function +--echo # in oracle style procedure FOR loop +--echo # + +CREATE TABLE t1(id INT, val INT); +DELIMITER //; +CREATE PROCEDURE p1() +BEGIN + DECLARE cur1 CURSOR FOR SELECT * FROM ( + SELECT DISTINCT id FROM t1) a + WHERE NOT EXISTS (SELECT * FROM ( SELECT id FROM t1) b + WHERE a.id=b.id); + OPEN cur1; + CLOSE cur1; + OPEN cur1; + CLOSE cur1; +END; +// +DELIMITER ;// +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; + +CREATE TABLE t1(id INT, val INT); +DELIMITER //; +CREATE PROCEDURE p1() +BEGIN + SELECT * FROM (SELECT DISTINCT id FROM t1) a + WHERE NOT a.id IN (SELECT b.id FROM t1 b); + SELECT * FROM (SELECT DISTINCT id FROM t1) a + WHERE NOT EXISTS (SELECT * FROM t1 b WHERE a.id=b.id); +END; +// +DELIMITER ;// +CALL p1(); +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; + --echo # End of 10.0 tests #restore defaults |