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

Group Concat with limit not working with views

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.3(EOL), 10.4(EOL)
    • 10.3.15, 10.4.5
    • Optimizer
    • None

    Description

      DATASET

      create table t1 (grp int, a bigint unsigned, c char(10) , d char(10) not null);
      insert into t1 values (1,1,NULL,"a");
      insert into t1 values (1,10,"b","a");
      insert into t1 values (1,11,"c","a");
      insert into t1 values (2,2,"c","a");
      insert into t1 values (2,3,"b","b");
      insert into t1 values (3,4,"E","a");
      insert into t1 values (3,5,"C","b");
      insert into t1 values (3,6,"D","c");
      insert into t1 values (3,7,"E","c");
      

      MariaDB [test]> select grp,group_concat(c) from t1 group by grp;
      +------+-----------------+
      | grp  | group_concat(c) |
      +------+-----------------+
      |    1 | b,c             |
      |    2 | c,b             |
      |    3 | E,C,D,E         |
      +------+-----------------+
      3 rows in set (0.003 sec)
       
      MariaDB [test]> select grp,group_concat(c limit 1,1 ) from t1 group by grp;
      +------+----------------------------+
      | grp  | group_concat(c limit 1,1 ) |
      +------+----------------------------+
      |    1 | c                          |
      |    2 | b                          |
      |    3 | C                          |
      +------+----------------------------+
      3 rows in set (0.003 sec)
      

      When I run the same query as above but wrapped in a view, then limit does not work with GROUP_CONCAT

      MariaDB [test]> create view v1 as select grp,group_concat(c limit 1,1 ) from t1 group by grp;
      Query OK, 0 rows affected (0.003 sec)
       
      MariaDB [test]> select * from v1;
      +------+----------------------------+
      | grp  | group_concat(c limit 1,1 ) |
      +------+----------------------------+
      |    1 | b,c                        |
      |    2 | c,b                        |
      |    3 | E,C,D,E                    |
      +------+----------------------------+
      3 rows in set (0.006 sec)
      
      

      Attachments

        Activity

          People

            varun Varun Gupta (Inactive)
            varun Varun Gupta (Inactive)
            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.