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

Group Concat with limit not working with views

    Details

      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

            • Assignee:
              varun Varun Gupta
              Reporter:
              varun Varun Gupta
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: