Debian Codegraph @

mysql-test/suite/compat/oracle/t/custom_aggregate_functions.test

raw
1 SET sql_mode=ORACLE;
2
3 delimiter |;
4 --error ER_INVALID_AGGREGATE_FUNCTION
5 create aggregate function f1(x INT) return INT AS
6 begin
7 insert into t1(sal) values (x);
8 return x;
9 end|
10
11 --error ER_NOT_AGGREGATE_FUNCTION
12 create function f1(x INT) return INT AS
13 begin
14 set x=5;
15 fetch group next row;
16 return x+1;
17 end |
18
19 DELIMITER ;|
20
21
22 CREATE TABLE marks(stud_id INT, grade_count INT);
23 INSERT INTO marks VALUES (1,6), (2,4), (3,7), (4,5), (5,8);
24 SELECT * FROM marks;
25
26 --echo # Using PL/SQL syntax: EXCEPTION WHEN NO_DATA_FOUND
27
28 DELIMITER //;
29 CREATE AGGREGATE FUNCTION IF NOT EXISTS aggregate_count(x INT) RETURN INT AS
30 count_students INT DEFAULT 0;
31 BEGIN
32 LOOP
33 FETCH GROUP NEXT ROW;
34 IF x THEN
35 count_students:= count_students + 1;
36 END IF;
37 END LOOP;
38 EXCEPTION
39 WHEN NO_DATA_FOUND THEN
40 RETURN count_students;
41 END aggregate_count //
42 DELIMITER ;//
43 SELECT aggregate_count(stud_id) FROM marks;
44 DROP FUNCTION IF EXISTS aggregate_count;
45
46
47 --echo # Using SQL/PSM systax: CONTINUE HANDLER
48
49 DELIMITER //;
50 CREATE AGGREGATE FUNCTION IF NOT EXISTS aggregate_count(x INT) RETURN INT AS
51 count_students INT DEFAULT 0;
52 CONTINUE HANDLER FOR NOT FOUND RETURN count_students;
53 BEGIN
54 LOOP
55 FETCH GROUP NEXT ROW;
56 IF x THEN
57 SET count_students= count_students + 1;
58 END IF;
59 END LOOP;
60 END //
61 DELIMITER ;//
62 SELECT aggregate_count(stud_id) FROM marks;
63 DROP FUNCTION IF EXISTS aggregate_count;
64
65
66 DROP TABLE marks;
67
68
69 --echo #
70 --echo # MDEV-18813 PROCEDURE and anonymous blocks silently ignore FETCH GROUP NEXT ROW
71 --echo #
72
73
74 DELIMITER $$;
75 --error ER_NOT_AGGREGATE_FUNCTION
76 CREATE PROCEDURE p1 AS
77 BEGIN
78 FETCH GROUP NEXT ROW;
79 END;
80 $$
81 DELIMITER ;$$
82
83
84 DELIMITER $$;
85 --error ER_NOT_AGGREGATE_FUNCTION
86 BEGIN NOT ATOMIC
87 FETCH GROUP NEXT ROW;
88 END;
89 $$
90 DELIMITER ;$$
91
92
93 DELIMITER $$;
94 --error ER_NOT_AGGREGATE_FUNCTION
95 CREATE DEFINER=root@localhost FUNCTION f1 RETURN INT AS
96 BEGIN
97 FETCH GROUP NEXT ROW;
98 RETURN 0;
99 END;
100 $$
101 DELIMITER ;$$
102
103
104 CREATE TABLE t1 (a INT);
105 --error ER_NOT_AGGREGATE_FUNCTION
106 CREATE TRIGGER tr1
107 AFTER INSERT ON t1 FOR EACH ROW
108 FETCH GROUP NEXT ROW;
109 DROP TABLE t1;
110
111
112 --error ER_NOT_AGGREGATE_FUNCTION
113 CREATE EVENT ev1
114 ON SCHEDULE EVERY 1 HOUR
115 STARTS CURRENT_TIMESTAMP + INTERVAL 1 MONTH
116 ENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTH + INTERVAL 1 WEEK
117 DO FETCH GROUP NEXT ROW;
118
119
120 DELIMITER $$;
121 CREATE PACKAGE pkg1 AS
122 PROCEDURE p1;
123 FUNCTION f1 RETURN INT;
124 END;
125 $$
126
127 --error ER_NOT_AGGREGATE_FUNCTION
128 CREATE PACKAGE BODY pkg1 AS
129 PROCEDURE p1 AS
130 BEGIN
131 FETCH GROUP NEXT ROW; -- In a package procedure
132 END;
133 FUNCTION f1 RETURN INT AS
134 BEGIN
135 RETURN 0;
136 END;
137 END;
138 $$
139
140 --error ER_NOT_AGGREGATE_FUNCTION
141 CREATE PACKAGE BODY pkg1 AS
142 PROCEDURE p1 AS
143 BEGIN
144 NULL;
145 END;
146 FUNCTION f1 RETURN INT AS
147 BEGIN
148 FETCH GROUP NEXT ROW; -- In a package function
149 RETURN 0;
150 END;
151 END;
152 $$
153
154 --error ER_NOT_AGGREGATE_FUNCTION
155 CREATE PACKAGE BODY pkg1 AS
156 PROCEDURE p1 AS
157 BEGIN
158 NULL;
159 END;
160 FUNCTION f1 RETURN INT AS
161 BEGIN
162 RETURN 0;
163 END;
164 BEGIN
165 FETCH GROUP NEXT ROW; -- In a package executable section
166 END;
167 $$
168
169 DELIMITER ;$$
170 DROP PACKAGE pkg1;