From c058117c6c857ecb41960bd705104adc42348645 Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Fri, 20 Apr 2018 23:15:27 +0400 Subject: Adding more tests for IN/EXIST subqueries for better coverage Adding tests with IN/EXISTS subqueries in various rarely unexpected places: - non-SELECT statements - SP control - cursors etc. --- mysql-test/suite/compat/oracle/r/sp-expr.result | 158 ++++++++++++++++++++ .../suite/compat/oracle/r/statement-expr.result | 69 +++++++++ mysql-test/suite/compat/oracle/t/sp-expr.test | 165 +++++++++++++++++++++ .../suite/compat/oracle/t/statement-expr.test | 86 +++++++++++ 4 files changed, 478 insertions(+) create mode 100644 mysql-test/suite/compat/oracle/r/sp-expr.result create mode 100644 mysql-test/suite/compat/oracle/r/statement-expr.result create mode 100644 mysql-test/suite/compat/oracle/t/sp-expr.test create mode 100644 mysql-test/suite/compat/oracle/t/statement-expr.test (limited to 'mysql-test/suite') diff --git a/mysql-test/suite/compat/oracle/r/sp-expr.result b/mysql-test/suite/compat/oracle/r/sp-expr.result new file mode 100644 index 00000000000..bb0c1a5c5c8 --- /dev/null +++ b/mysql-test/suite/compat/oracle/r/sp-expr.result @@ -0,0 +1,158 @@ +SET sql_mode=ORACLE; +# +# Start of 10.3 tests +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1); +BEGIN +CASE ((1) IN (SELECT a FROM t1)) WHEN 1 THEN SELECT 1; +ELSE SELECT NULL; +END CASE; +END; +$$ +1 +1 +BEGIN +CASE (EXISTS (SELECT a FROM t1)) WHEN 1 THEN SELECT 1; +ELSE SELECT NULL; +END CASE; +END; +$$ +1 +1 +BEGIN +IF ((1) IN (SELECT a FROM t1)) THEN SELECT 1; +ELSE SELECT NULL; +END IF; +END; +$$ +1 +1 +BEGIN +IF (EXISTS (SELECT a FROM t1)) THEN SELECT 1; +ELSE SELECT NULL; +END IF; +END; +$$ +1 +1 +BEGIN +WHILE ((1234) IN (SELECT * FROM t1)) LOOP +SELECT 1; +END LOOP; +END; +$$ +BEGIN +WHILE (EXISTS (SELECT * FROM t1 WHERE a=1234)) LOOP +SELECT 1; +END LOOP; +END; +$$ +BEGIN +REPEAT +SELECT 1; +UNTIL (1 IN (SELECT * FROM t1)) +END REPEAT; +END; +$$ +1 +1 +BEGIN +REPEAT +SELECT 1; +UNTIL EXISTS (SELECT * FROM t1 WHERE a=1) +END REPEAT; +END; +$$ +1 +1 +BEGIN +FOR i IN 0..(1 IN (SELECT * FROM t1)) +LOOP +SELECT i; +END LOOP; +END; +$$ +i +0 +i +1 +BEGIN +FOR i IN 0..EXISTS (SELECT * FROM t1 WHERE a=1) +LOOP +SELECT i; +END LOOP; +END; +$$ +i +0 +i +1 +DROP TABLE t1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (10); +DECLARE +a INT DEFAULT ((10) IN (SELECT * FROM t1)); +BEGIN +SELECT a; +END; +$$ +a +1 +DECLARE +a INT DEFAULT EXISTS (SELECT * FROM t1); +BEGIN +SELECT a; +END; +$$ +a +1 +DROP TABLE t1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1); +CREATE FUNCTION f1() RETURN INT AS +BEGIN +RETURN ((1) IN (SELECT * FROM t1)); +END; +$$ +CREATE FUNCTION f2() RETURN INT AS +BEGIN +RETURN EXISTS (SELECT * FROM t1 WHERE a=1); +END; +$$ +SELECT f1(); +f1() +1 +SELECT f2(); +f2() +1 +DROP FUNCTION f1; +DROP FUNCTION f2; +DROP TABLE t1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); +DECLARE +va INT; +CURSOR cur(amin INT) IS SELECT a FROM t1 WHERE a>amin ORDER BY a; +BEGIN +OPEN cur(1 IN (SELECT * FROM t1)); +FETCH cur INTO va; +SELECT va; +CLOSE cur; +END; +$$ +va +2 +DECLARE +va INT; +CURSOR cur(amin INT) IS SELECT a FROM t1 WHERE a>amin ORDER BY a; +BEGIN +OPEN cur(EXISTS (SELECT * FROM t1)); +FETCH cur INTO va; +SELECT va; +CLOSE cur; +END; +$$ +va +2 +DROP TABLE t1; diff --git a/mysql-test/suite/compat/oracle/r/statement-expr.result b/mysql-test/suite/compat/oracle/r/statement-expr.result new file mode 100644 index 00000000000..ea3bd5232d3 --- /dev/null +++ b/mysql-test/suite/compat/oracle/r/statement-expr.result @@ -0,0 +1,69 @@ +SET sql_mode=ORACLE; +# +# Start of 10.3 tests +# +CREATE TABLE t1 (id INT, id1 INT); +INSERT INTO t1 VALUES (1,7); +INSERT INTO t1 VALUES (1,8); +SELECT ROW(1,7) IN (SELECT id, id1 FROM t1 WHERE id1= 8); +ROW(1,7) IN (SELECT id, id1 FROM t1 WHERE id1= 8) +0 +EXECUTE IMMEDIATE 'SELECT ROW(1, 7) IN (SELECT id, id1 FROM t1 WHERE id1= 8)'; +ROW(1, 7) IN (SELECT id, id1 FROM t1 WHERE id1= 8) +0 +DROP TABLE t1; +EXECUTE IMMEDIATE 'SELECT ?' USING (1 IN (SELECT * FROM t1)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT * FROM t1))' at line 1 +EXECUTE IMMEDIATE 'SELECT ?' USING (SELECT * FROM t1); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT * FROM t1)' at line 1 +CREATE TABLE t1 (id INT); +INSERT INTO t1 VALUES (10); +CREATE PROCEDURE p1(a INT) AS BEGIN NULL; END; +$$ +CALL p1((1) IN (SELECT * FROM t1)); +CALL p1(EXISTS (SELECT * FROM t1)); +DROP PROCEDURE p1; +DROP TABLE t1; +SIGNAL SQLSTATE '01000' SET MYSQL_ERRNO=(1 IN (SELECT * FROM t1)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(1 IN (SELECT * FROM t1))' at line 1 +SIGNAL SQLSTATE '01000' SET MYSQL_ERRNO=EXISTS (SELECT * FROM t1); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'EXISTS (SELECT * FROM t1)' at line 1 +BEGIN NOT ATOMIC +DECLARE CONTINUE HANDLER FOR SQLWARNING +RESIGNAL SET MYSQL_ERRNO=(1 IN (SELECT * FROM t1)); +SIGNAL SQLSTATE '01000'; +END; +$$ +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(1 IN (SELECT * FROM t1)); +SIGNAL SQLSTATE '01000'; +END' at line 3 +BEGIN NOT ATOMIC +DECLARE CONTINUE HANDLER FOR SQLWARNING +RESIGNAL SET MYSQL_ERRNO=EXISTS (SELECT * FROM t1); +SIGNAL SQLSTATE '01000'; +END; +$$ +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'EXISTS (SELECT * FROM t1); +SIGNAL SQLSTATE '01000'; +END' at line 3 +PREPARE stmt FROM (1 IN (SELECT * FROM t1)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT * FROM t1))' at line 1 +PREPARE stmt FROM EXISTS (SELECT * FROM t1); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(SELECT * FROM t1)' at line 1 +EXECUTE IMMEDIATE (1 IN (SELECT * FROM t1)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT * FROM t1))' at line 1 +EXECUTE IMMEDIATE EXISTS (SELECT * FROM t1); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(SELECT * FROM t1)' at line 1 +GET DIAGNOSTICS CONDITION (1 IN (SELECT * FROM t1)) @errno=MYSQL_ERRNO; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(1 IN (SELECT * FROM t1)) @errno=MYSQL_ERRNO' at line 1 +GET DIAGNOSTICS CONDITION EXISTS (SELECT * FROM t1) @errno=MYSQL_ERRNO; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'EXISTS (SELECT * FROM t1) @errno=MYSQL_ERRNO' at line 1 +PURGE BINARY LOGS BEFORE (1 IN (SELECT * FROM t1)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT * FROM t1))' at line 1 +PURGE BINARY LOGS BEFORE EXISTS (SELECT * FROM t1); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(SELECT * FROM t1)' at line 1 +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); +DO 1 IN (SELECT * FROM t1); +DO EXISTS (SELECT * FROM t1); +DROP TABLE t1; diff --git a/mysql-test/suite/compat/oracle/t/sp-expr.test b/mysql-test/suite/compat/oracle/t/sp-expr.test new file mode 100644 index 00000000000..06a5c59b80c --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/sp-expr.test @@ -0,0 +1,165 @@ +# Testing expressions of different kinds in various parts of SP syntax + +SET sql_mode=ORACLE; + +--echo # +--echo # Start of 10.3 tests +--echo # + +# +# Subselects in SP control structures +# + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1); +DELIMITER $$; + +BEGIN + CASE ((1) IN (SELECT a FROM t1)) WHEN 1 THEN SELECT 1; + ELSE SELECT NULL; + END CASE; +END; +$$ +BEGIN + CASE (EXISTS (SELECT a FROM t1)) WHEN 1 THEN SELECT 1; + ELSE SELECT NULL; + END CASE; +END; +$$ + +BEGIN + IF ((1) IN (SELECT a FROM t1)) THEN SELECT 1; + ELSE SELECT NULL; + END IF; +END; +$$ +BEGIN + IF (EXISTS (SELECT a FROM t1)) THEN SELECT 1; + ELSE SELECT NULL; + END IF; +END; +$$ + +BEGIN + WHILE ((1234) IN (SELECT * FROM t1)) LOOP + SELECT 1; + END LOOP; +END; +$$ +BEGIN + WHILE (EXISTS (SELECT * FROM t1 WHERE a=1234)) LOOP + SELECT 1; + END LOOP; +END; +$$ + +BEGIN + REPEAT + SELECT 1; + UNTIL (1 IN (SELECT * FROM t1)) + END REPEAT; +END; +$$ +BEGIN + REPEAT + SELECT 1; + UNTIL EXISTS (SELECT * FROM t1 WHERE a=1) + END REPEAT; +END; +$$ + +BEGIN + FOR i IN 0..(1 IN (SELECT * FROM t1)) + LOOP + SELECT i; + END LOOP; +END; +$$ +BEGIN + FOR i IN 0..EXISTS (SELECT * FROM t1 WHERE a=1) + LOOP + SELECT i; + END LOOP; +END; +$$ +DELIMITER ;$$ +DROP TABLE t1; + + +# +# Subselects as SP variable default values +# + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (10); +DELIMITER $$; +DECLARE + a INT DEFAULT ((10) IN (SELECT * FROM t1)); +BEGIN + SELECT a; +END; +$$ +DECLARE + a INT DEFAULT EXISTS (SELECT * FROM t1); +BEGIN + SELECT a; +END; +$$ +DELIMITER ;$$ +DROP TABLE t1; + + +# +# Subselects SP function return values +# + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1); +DELIMITER $$; +CREATE FUNCTION f1() RETURN INT AS +BEGIN + RETURN ((1) IN (SELECT * FROM t1)); +END; +$$ +CREATE FUNCTION f2() RETURN INT AS +BEGIN + RETURN EXISTS (SELECT * FROM t1 WHERE a=1); +END; +$$ +DELIMITER ;$$ +SELECT f1(); +SELECT f2(); +DROP FUNCTION f1; +DROP FUNCTION f2; +DROP TABLE t1; + + +# +# Subselects in CURSOR parameters +# + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); +DELIMITER $$; +DECLARE + va INT; + CURSOR cur(amin INT) IS SELECT a FROM t1 WHERE a>amin ORDER BY a; +BEGIN + OPEN cur(1 IN (SELECT * FROM t1)); + FETCH cur INTO va; + SELECT va; + CLOSE cur; +END; +$$ +DECLARE + va INT; + CURSOR cur(amin INT) IS SELECT a FROM t1 WHERE a>amin ORDER BY a; +BEGIN + OPEN cur(EXISTS (SELECT * FROM t1)); + FETCH cur INTO va; + SELECT va; + CLOSE cur; +END; +$$ +DELIMITER ;$$ +DROP TABLE t1; diff --git a/mysql-test/suite/compat/oracle/t/statement-expr.test b/mysql-test/suite/compat/oracle/t/statement-expr.test new file mode 100644 index 00000000000..f4a6a25eff3 --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/statement-expr.test @@ -0,0 +1,86 @@ +# Testing expressions of different kinds in various non-SELECT statements + +SET sql_mode=ORACLE; + +--echo # +--echo # Start of 10.3 tests +--echo # + +# +# Subselects in non-SELECT statements +# + +CREATE TABLE t1 (id INT, id1 INT); +INSERT INTO t1 VALUES (1,7); +INSERT INTO t1 VALUES (1,8); +SELECT ROW(1,7) IN (SELECT id, id1 FROM t1 WHERE id1= 8); +EXECUTE IMMEDIATE 'SELECT ROW(1, 7) IN (SELECT id, id1 FROM t1 WHERE id1= 8)'; +DROP TABLE t1; + +--error ER_PARSE_ERROR +EXECUTE IMMEDIATE 'SELECT ?' USING (1 IN (SELECT * FROM t1)); +--error ER_PARSE_ERROR +EXECUTE IMMEDIATE 'SELECT ?' USING (SELECT * FROM t1); + + +CREATE TABLE t1 (id INT); +INSERT INTO t1 VALUES (10); +DELIMITER $$; +CREATE PROCEDURE p1(a INT) AS BEGIN NULL; END; +$$ +DELIMITER ;$$ +CALL p1((1) IN (SELECT * FROM t1)); +CALL p1(EXISTS (SELECT * FROM t1)); +DROP PROCEDURE p1; +DROP TABLE t1; + +--error ER_PARSE_ERROR +SIGNAL SQLSTATE '01000' SET MYSQL_ERRNO=(1 IN (SELECT * FROM t1)); +--error ER_PARSE_ERROR +SIGNAL SQLSTATE '01000' SET MYSQL_ERRNO=EXISTS (SELECT * FROM t1); + +DELIMITER $$; +--error ER_PARSE_ERROR +BEGIN NOT ATOMIC + DECLARE CONTINUE HANDLER FOR SQLWARNING + RESIGNAL SET MYSQL_ERRNO=(1 IN (SELECT * FROM t1)); + SIGNAL SQLSTATE '01000'; +END; +$$ +--error ER_PARSE_ERROR +BEGIN NOT ATOMIC + DECLARE CONTINUE HANDLER FOR SQLWARNING + RESIGNAL SET MYSQL_ERRNO=EXISTS (SELECT * FROM t1); + SIGNAL SQLSTATE '01000'; +END; +$$ +DELIMITER ;$$ + + +--error ER_PARSE_ERROR +PREPARE stmt FROM (1 IN (SELECT * FROM t1)); +--error ER_PARSE_ERROR +PREPARE stmt FROM EXISTS (SELECT * FROM t1); + +--error ER_PARSE_ERROR +EXECUTE IMMEDIATE (1 IN (SELECT * FROM t1)); +--error ER_PARSE_ERROR +EXECUTE IMMEDIATE EXISTS (SELECT * FROM t1); + +--error ER_PARSE_ERROR +GET DIAGNOSTICS CONDITION (1 IN (SELECT * FROM t1)) @errno=MYSQL_ERRNO; +--error ER_PARSE_ERROR +GET DIAGNOSTICS CONDITION EXISTS (SELECT * FROM t1) @errno=MYSQL_ERRNO; + +--error ER_PARSE_ERROR +PURGE BINARY LOGS BEFORE (1 IN (SELECT * FROM t1)); +--error ER_PARSE_ERROR +PURGE BINARY LOGS BEFORE EXISTS (SELECT * FROM t1); + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); +DO 1 IN (SELECT * FROM t1); +DO EXISTS (SELECT * FROM t1); +DROP TABLE t1; + + -- cgit v1.2.1