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

JSON_ARRAYAGG result differs when using HAVING or not

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • None
    • N/A
    • JSON
    • None

    Description

      If json_arrayagg result is used in a having clause, it is surrounded by extra brackets in the resultset.
      How to reproduce:

      CREATE TABLE tst (a INTEGER, b VARCHAR(255));
      INSERT INTO tst VALUES (1, 'test string');
      SELECT a, JSON_ARRAYAGG(b) AS js FROM tst GROUP BY 1;
      /* Correct:
      +------+-----------------+
      | a    | js              |
      +------+-----------------+
      |    1 | ["test string"] |
      +------+-----------------+
      */
      SELECT a, JSON_ARRAYAGG(b) AS js FROM tst GROUP BY 1 HAVING a=1;
      /* Correct:
      +------+-----------------+
      | a    | js              |
      +------+-----------------+
      |    1 | ["test string"] |
      +------+-----------------+
      */
      SELECT  a, JSON_ARRAYAGG(b) AS js FROM tst GROUP BY 1 HAVING js<>'';
      /* Extra brackets:
      +------+-------------------+
      | a    | js                |
      +------+-------------------+
      |    1 | [["test string"]] |
      +------+-------------------+
      */
      -- but at the same time it has correct value within HAVING conditions:
      SELECT  a, JSON_ARRAYAGG(b) AS js FROM tst GROUP BY 1 HAVING js='["test string"]';
      /*
      +------+-------------------+
      | a    | js                |
      +------+-------------------+
      |    1 | [["test string"]] |
      +------+-------------------+
      */
      SELECT  a, JSON_ARRAYAGG(b) AS js FROM tst GROUP BY 1 HAVING js='["test string"]';
      /*
      Empty set (0,002 sec)
      */
      

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              alexey.antipovsky Alexey Antipovsky
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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