1# Testing expressions of different kinds in various non-SELECT statements 2 3SET sql_mode=ORACLE; 4 5--echo # 6--echo # Start of 10.3 tests 7--echo # 8 9# 10# Subselects in non-SELECT statements 11# 12 13CREATE TABLE t1 (id INT, id1 INT); 14INSERT INTO t1 VALUES (1,7); 15INSERT INTO t1 VALUES (1,8); 16SELECT ROW(1,7) IN (SELECT id, id1 FROM t1 WHERE id1= 8); 17EXECUTE IMMEDIATE 'SELECT ROW(1, 7) IN (SELECT id, id1 FROM t1 WHERE id1= 8)'; 18DROP TABLE t1; 19 20--error ER_PARSE_ERROR 21EXECUTE IMMEDIATE 'SELECT ?' USING (1 IN (SELECT * FROM t1)); 22--error ER_PARSE_ERROR 23EXECUTE IMMEDIATE 'SELECT ?' USING (SELECT * FROM t1); 24 25 26CREATE TABLE t1 (id INT); 27INSERT INTO t1 VALUES (10); 28DELIMITER $$; 29CREATE PROCEDURE p1(a INT) AS BEGIN NULL; END; 30$$ 31DELIMITER ;$$ 32CALL p1((1) IN (SELECT * FROM t1)); 33CALL p1(EXISTS (SELECT * FROM t1)); 34DROP PROCEDURE p1; 35DROP TABLE t1; 36 37--error ER_PARSE_ERROR 38SIGNAL SQLSTATE '01000' SET MYSQL_ERRNO=(1 IN (SELECT * FROM t1)); 39--error ER_PARSE_ERROR 40SIGNAL SQLSTATE '01000' SET MYSQL_ERRNO=EXISTS (SELECT * FROM t1); 41 42DELIMITER $$; 43--error ER_PARSE_ERROR 44BEGIN NOT ATOMIC 45 DECLARE CONTINUE HANDLER FOR SQLWARNING 46 RESIGNAL SET MYSQL_ERRNO=(1 IN (SELECT * FROM t1)); 47 SIGNAL SQLSTATE '01000'; 48END; 49$$ 50--error ER_PARSE_ERROR 51BEGIN NOT ATOMIC 52 DECLARE CONTINUE HANDLER FOR SQLWARNING 53 RESIGNAL SET MYSQL_ERRNO=EXISTS (SELECT * FROM t1); 54 SIGNAL SQLSTATE '01000'; 55END; 56$$ 57DELIMITER ;$$ 58 59 60--error ER_PARSE_ERROR 61PREPARE stmt FROM (1 IN (SELECT * FROM t1)); 62--error ER_PARSE_ERROR 63PREPARE stmt FROM EXISTS (SELECT * FROM t1); 64 65--error ER_PARSE_ERROR 66EXECUTE IMMEDIATE (1 IN (SELECT * FROM t1)); 67--error ER_PARSE_ERROR 68EXECUTE IMMEDIATE EXISTS (SELECT * FROM t1); 69 70--error ER_PARSE_ERROR 71GET DIAGNOSTICS CONDITION (1 IN (SELECT * FROM t1)) @errno=MYSQL_ERRNO; 72--error ER_PARSE_ERROR 73GET DIAGNOSTICS CONDITION EXISTS (SELECT * FROM t1) @errno=MYSQL_ERRNO; 74 75--error ER_PARSE_ERROR 76PURGE BINARY LOGS BEFORE (1 IN (SELECT * FROM t1)); 77--error ER_PARSE_ERROR 78PURGE BINARY LOGS BEFORE EXISTS (SELECT * FROM t1); 79 80CREATE TABLE t1 (a INT); 81INSERT INTO t1 VALUES (1),(2),(3); 82DO 1 IN (SELECT * FROM t1); 83DO EXISTS (SELECT * FROM t1); 84DROP TABLE t1; 85 86 87