1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
|
# Testing expressions of different kinds in various parts of SP syntax
--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 NOT ATOMIC
CASE ((1) IN (SELECT a FROM t1)) WHEN 1 THEN SELECT 1;
ELSE SELECT NULL;
END CASE;
END;
$$
BEGIN NOT ATOMIC
CASE (EXISTS (SELECT a FROM t1)) WHEN 1 THEN SELECT 1;
ELSE SELECT NULL;
END CASE;
END;
$$
BEGIN NOT ATOMIC
IF ((1) IN (SELECT a FROM t1)) THEN SELECT 1;
ELSE SELECT NULL;
END IF;
END;
$$
BEGIN NOT ATOMIC
IF (EXISTS (SELECT a FROM t1)) THEN SELECT 1;
ELSE SELECT NULL;
END IF;
END;
$$
BEGIN NOT ATOMIC
WHILE ((1234) IN (SELECT * FROM t1)) DO
SELECT 1;
END WHILE;
END;
$$
BEGIN NOT ATOMIC
WHILE (EXISTS (SELECT * FROM t1 WHERE a=1234)) DO
SELECT 1;
END WHILE;
END;
$$
BEGIN NOT ATOMIC
REPEAT
SELECT 1;
UNTIL (1 IN (SELECT * FROM t1))
END REPEAT;
END;
$$
BEGIN NOT ATOMIC
REPEAT
SELECT 1;
UNTIL EXISTS (SELECT * FROM t1 WHERE a=1)
END REPEAT;
END;
$$
BEGIN NOT ATOMIC
FOR i IN 0..(1 IN (SELECT * FROM t1))
DO
SELECT i;
END FOR;
END;
$$
BEGIN NOT ATOMIC
FOR i IN 0..EXISTS (SELECT * FROM t1 WHERE a=1)
DO
SELECT i;
END FOR;
END;
$$
DELIMITER ;$$
DROP TABLE t1;
#
# Subselects as SP variable default values
#
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10);
DELIMITER $$;
BEGIN NOT ATOMIC
DECLARE a INT DEFAULT ((10) IN (SELECT * FROM t1));
SELECT a;
END;
$$
BEGIN NOT ATOMIC
DECLARE a INT DEFAULT EXISTS (SELECT * FROM t1);
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() RETURNS INT
BEGIN
RETURN ((1) IN (SELECT * FROM t1));
END;
$$
CREATE FUNCTION f2() RETURNS INT
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 $$;
BEGIN NOT ATOMIC
DECLARE va INT;
DECLARE cur CURSOR(amin INT) FOR SELECT a FROM t1 WHERE a>amin ORDER BY a;
OPEN cur(1 IN (SELECT * FROM t1));
FETCH cur INTO va;
SELECT va;
CLOSE cur;
END;
$$
BEGIN NOT ATOMIC
DECLARE va INT;
DECLARE cur CURSOR(amin INT) FOR SELECT a FROM t1 WHERE a>amin ORDER BY a;
OPEN cur(EXISTS (SELECT * FROM t1));
FETCH cur INTO va;
SELECT va;
CLOSE cur;
END;
$$
DELIMITER ;$$
DROP TABLE t1;
|
