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