|
/*
CREATE TABLE marks(stud_id INT, grade_count INT);
INSERT INTO marks VALUES (1,6), (2,4), (3,7), (4,5), (5,8), (5,9);
SELECT * FROM marks;
/* I get:
- 1 6
- 2 4
- 3 7
- 4 5
- 5 8
- 5 9
- Now create the following aggregate function:
*/
DELIMITER //
DROP FUNCTION IF EXISTS aggregate_count //
CREATE AGGREGATE FUNCTION IF NOT EXISTS aggregate_count(x INT) RETURNS INT
BEGIN
DECLARE count_students INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND
RETURN count_students;
LOOP
FETCH GROUP NEXT ROW;
IF x THEN
SET count_students = count_students+1;
END IF;
END LOOP;
END //
DELIMITER ;
/* I can use aggregation in my queries as the following: */
select stud_id, aggregate_count(stud_id)
from marks
group by stud_id;
/*
- as expected I get:
*
- 1 1
- 2 1
- 3 1
- 4 1
- 5 2
*/
/* but when used in a correlated subquery I have wrong result:*/
select t.stud_id, (
select aggregate_count(stud_id)
from marks
where stud_id = t.stud_id
) c
from (
select 1 as stud_id
union select 6
union select 5
) t;
/*
- I get:
- 1 1
- 6 1 <---- wrong
- 5 2
- the correct value should be 0.
- I debug a bit the aggregation function:
*/
CREATE TABLE debug(msg TEXT);
DELIMITER //
DROP FUNCTION IF EXISTS aggregate_count //
CREATE AGGREGATE FUNCTION IF NOT EXISTS aggregate_count(x INT) RETURNS INT
BEGIN
DECLARE count_students INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND
RETURN count_students;
LOOP
FETCH GROUP NEXT ROW;
IF x THEN
SET count_students = count_students+1;
END IF;
insert into `debug`(msg) VALUES (concat('value: ', ifnull(x, 'null'), " -> count_students: ", count_students));
END LOOP;
END //
DELIMITER ;
select t.stud_id, (
select aggregate_count(stud_id)
from marks
where stud_id = t.stud_id
) c
from (
select 1 as stud_id
union select 6
union select 5
) t;
select * from debug;
/*
- value: 1 -> count_students: 1
- value: null -> count_students: 0
- value: 5 -> count_students: 1
- value: 5 -> count_students: 2
- Function seems to be invoked and return the correct value, but query return a wrong value.
*/
|