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

Group concat returns incorrect result when using DISTINCT and ORDER BY

    XMLWordPrintable

Details

    • Bug
    • Status: Stalled (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.2, 10.3, 10.4, 10.5
    • 10.4, 10.5
    • Optimizer
    • None

    Description

      Data set:

      create table t1 (a varchar(1000), b varchar(1000), c varchar(1000));
      insert t1 select seq,seq, seq from seq_1_to_9;
      insert t1 select seq,seq, seq from seq_1_to_9;
      

      set tmp_table_size=1024; // set so that unique tree is dumped to the disk
      

      Now lets run the query without DISTINCT in GROUP_CONCAT

      MariaDB [test]> select group_concat(c order by a,b) from t1;
      +-------------------------------------+
      | group_concat(c order by a,b)        |
      +-------------------------------------+
      | 1,1,2,2,3,3,4,4,5,5,6,6,7,7,8,8,9,9 |
      +-------------------------------------+
      1 row in set (0.005 sec)
      

      Now running the query with DISTINCT in GROUP_CONCAT

      MariaDB [test]> select group_concat(distinct c order by a,b) from t1;
      +---------------------------------------+
      | group_concat(distinct c order by a,b) |
      +---------------------------------------+
      | 1                                     |
      +---------------------------------------+
      1 row in set (0.006 sec)
      

      This is observed on top of the branch 10.5-mdev11563 (also expected to fail on all other versions)

      So the issue here is when Unique does not fit in memory for DISTINCT we flush the unique tree to the disk but in such cases we get incorrect result

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              varun Varun Gupta (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.