Debian Codegraph @

mysql-test/suite/compat/oracle/r/sp-expr.result

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