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

group_concat result are NOT the same after enclosing by VIEW object in some condition

    XMLWordPrintable

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.6.7
    • 10.6
    • Optimizer
    • None
    • redhat 7 on vmeware

    Description

      Hi,

      Found group_concat result are NOT the same after enclosing by VIEW object in some condition. It is not critical issue in our case because we are not that syntax. But it worth to make them the same result after enclosing by VIEW. Seems the problem is the extra "SELECT" between group_concat and trim functions. Below is the test case.

      -- setup
      use testdb1 ;
      create table t1 (c1 int primary key, c2 varchar(10) );
      insert into t1 values (1, 'A') , (2, 'B') ;
      commit ;
      

      MariaDB [testdb1]> -- good case - result are the same after enclosing by VIEW object
      MariaDB [testdb1]> select trim( both ',' from ( group_concat(c2) ) ) AS output from t1 ;
      +--------+
      | output |
      +--------+
      | A,B    |
      +--------+
      1 row in set (0.001 sec)
       
      MariaDB [testdb1]> create or replace view v1 AS select trim( both ',' from ( group_concat(c2) ) ) AS output from t1 ;
      Query OK, 0 rows affected (0.004 sec)
       
      MariaDB [testdb1]> select * from v1 ;
      +--------+
      | output |
      +--------+
      | A,B    |
      +--------+
      1 row in set (0.006 sec)
       
      MariaDB [testdb1]> -- bad case - result are NOT the same after enclosing by VIEW object
      MariaDB [testdb1]> select trim( both ',' from (select group_concat(c2) ) ) AS output from t1 ;
      +--------+
      | output |
      +--------+
      | A,B    |
      +--------+
      1 row in set (0.001 sec)
       
      MariaDB [testdb1]> create or replace view v2 AS select trim( both ',' from (select group_concat(c2) ) ) AS output from t1 ;
      Query OK, 0 rows affected (0.004 sec)
       
      MariaDB [testdb1]> select * from v2 ;
      +--------+
      | output |
      +--------+
      | A      |
      | B      |
      +--------+
      2 rows in set (0.001 sec)
      

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            frelist William Wong
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.