Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-5394

JSON_ARRAYAGG in MCS works differently than in InnoDB.

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • None
    • Icebox
    • None
    • None

    Description

      Here's the SQL to demonstrate the problem:

      DROP DATABASE IF EXISTS json_arrayagg_db;CREATE DATABASE json_arrayagg_db;
      USE json_arrayagg_db;CREATE TABLE t1 (a INT, b VARCHAR(80));
      INSERT INTO t1 VALUES
             (1, "Hello"),(1, "World"), (2, "This"),(2, "Will"), (2, "Work"),(2, "!"), (3, NULL),
             (1, "Hello"),(1, "World"), (2, "This"),(2, "Will"), (2, "Work"),(2, "!"), (3, NULL);
      SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b) FROM t1 GROUP BY a;
      DROP TABLE t1;
      CREATE TABLE t1 (a INT, b VARCHAR(80))ENGINE=COLUMNSTORE;
      INSERT INTO t1 VALUES
             (1, "Hello"),(1, "World"), (2, "This"),(2, "Will"), (2, "Work"),(2, "!"), (3, NULL),
             (1, "Hello"),(1, "World"), (2, "This"),(2, "Will"), (2, "Work"),(2, "!"), (3, NULL);
      SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b) FROM t1 GROUP BY a;
      

      These queries return different results for row containing "[3,3]" in first column: InnoDBreturns the string "[null,null]" and MCS currently returns NULL (which is empty string in actuality).

      The code in question is this:

      void JsonArrayAggOrderBy::processRow(const rowgroup::Row& row)
      {
        // check if this is a distinct row
        if (fDistinct && fDistinctMap->find(row.getPointer()) != fDistinctMap->end())
          return;
       
        // this row is skipped if any concatenated column is null.
        if (concatColIsNull(row))
          return;
      ...
      

      It actively skips NULL rows when doing group by aggregation.

      Attachments

        Activity

          People

            Unassigned Unassigned
            sergey.zefirov Sergey Zefirov
            Votes:
            0 Vote for this issue
            Watchers:
            1 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.