Debian Codegraph @

mysql-test/suite/compat/oracle/t/statement-expr.test

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