Debian Codegraph @

mysql-test/suite/compat/oracle/r/custom_aggregate_functions.result

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