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

JSON_ARRAYAGG includes wrong TEXT row

    XMLWordPrintable

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.5.21
    • 10.5, 10.6, 10.11
    • JSON

    Description

      CREATE TABLE `yyy_mariadb_bug` (
              `group_id` INT(11) NULL DEFAULT NULL,
              `row_id` INT(11) NULL DEFAULT NULL,
              `varchar_val` VARCHAR(25) NULL DEFAULT NULL,
              `text_val` TEXT NULL DEFAULT NULL
      ) ENGINE=InnoDB;
       
      INSERT INTO `yyy_mariadb_bug` (`group_id`, `row_id`, `varchar_val`, `text_val`) VALUES (1, 1, 'varchar_group_1_row_1', 'text_group_1_row_1');
      INSERT INTO `yyy_mariadb_bug` (`group_id`, `row_id`, `varchar_val`, `text_val`) VALUES (1, 2, 'varchar_group_1_row_2', 'text_group_1_row_2');
      INSERT INTO `yyy_mariadb_bug` (`group_id`, `row_id`, `varchar_val`, `text_val`) VALUES (2, 1, 'varchar_group_2_row_1', 'text_group_2_row_1');
      INSERT INTO `yyy_mariadb_bug` (`group_id`, `row_id`, `varchar_val`, `text_val`) VALUES (2, 2, 'varchar_group_2_row_2', 'text_group_2_row_2');
       
      SELECT * FROM yyy_mariadb_bug;
       
      SELECT group_id, JSON_OBJECT(
      'arr',(SELECT JSON_ARRAYAGG(JSON_OBJECT(
      'v',varchar_val,
      't',text_val)))) as json_arr FROM yyy_mariadb_bug GROUP BY group_id;
      

      Actual result:

      group_id        row_id  varchar_val     text_val
      1       1       varchar_group_1_row_1   text_group_1_row_1
      1       2       varchar_group_1_row_2   text_group_1_row_2
      2       1       varchar_group_2_row_1   text_group_2_row_1
      2       2       varchar_group_2_row_2   text_group_2_row_2
      group_id        json_arr
      1       {"arr": [{"v": "varchar_group_1_row_1", "t": "text_group_1_row_1"},{"v": "varchar_group_1_row_2", "t": "text_group_1_row_1"}]}
      2       {"arr": [{"v": "varchar_group_2_row_1", "t": "text_group_2_row_1"},{"v": "varchar_group_2_row_2", "t": "text_group_2_row_1"}]}
      

      Expected result:

      group_id        json_arr
      1       {"arr": [{"v": "varchar_group_1_row_1", "t": "text_group_1_row_1"},{"v": "varchar_group_1_row_2", "t": "text_group_1_row_2"}]}
      2       {"arr": [{"v": "varchar_group_2_row_1", "t": "text_group_2_row_1"},{"v": "varchar_group_2_row_2", "t": "text_group_2_row_2"}]}
      

      i.e. The second element of the array should come from row 2 ...

       {"v": "varchar_group_1_row_2", "t": "text_group_1_row_2"}
       {"v": "varchar_group_2_row_2", "t": "text_group_2_row_2"}
      

      ... but instead ...

       {"v": "varchar_group_1_row_2", "t": "text_group_1_row_1"}
       {"v": "varchar_group_2_row_2", "t": "text_group_2_row_1"}
      

      ... is included - the correct varchar value from row 2 is included, but the wrong text value from row 1 is included, it should contain text_group_(1|2)_row_2 from the row 2s.

      Attachments

        Activity

          People

            rucha174 Rucha Deodhar
            adennisa15 A D
            Votes:
            0 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.