Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-21161

Stored aggregate function wrong result in correlated subquery

    XMLWordPrintable

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.3.12, 10.3(EOL), 10.4(EOL), 10.5(EOL), 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL)
    • 10.6
    • None
    • None
    • Used docker image "mariadb:10.3.12-bionic"

    Description

      /*

      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.
        */

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            daniele.antonini Daniele Antonini
            Votes:
            1 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.