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

Select from view fails if definition of view has aggregate function ( like 'GROUP_CONCAT(COUNT(a))' ) in query

Details

    Description

      Select from view fails with error "Invalid use of group function" if definition of view has 'GROUP_CONCAT(COUNT(a))' in query.

      Test:

      CREATE TABLE t1 (a int, b int);
      		 
      CREATE TABLE t2 (m int, n int);
      		 
      INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
      		 
      INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
      		 
       
      		 
      CREATE VIEW v1 AS SELECT (SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a) AS c
      		 
        FROM t1 GROUP BY a;
      		 
       
      		 
      SELECT * FROM v1;
      		 
       
      		 
      DROP VIEW v1;
      		 
      DROP table t1,t2;

      Actual result:

      At line 9: query 'SELECT * FROM v1' failed: 1111: Invalid use of group function

      Expected result:

      c
      		 
      2
      		 
      3
      		 
      1,1

      Attachments

        Issue Links

          duplicates

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30668 Set function aggregated in outer select used in view definition

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed
          is part of

          Technical task - A technical task. MDEV-27691 make working view-protocol

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            lstartseva Lena Startseva added a comment - - edited

            The same problem with MAX:
            Test:

            CREATE TABLE t1 (a int, b INT, d INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));
            		 
            INSERT INTO t1 VALUES (1,1,0,'a'), (1,2,0,'b'), (1,3,0,'c'), (1,4,0,'d'),
            		 
            (1,5,0,'e'), (2,1,0,'f'), (2,2,0,'g'), (2,3,0,'h'), (3,4,0,'i'), (3,3,0,'j'),
            		 
            (3,2,0,'k'), (3,1,0,'l'), (1,9,0,'m'), (1,0,10,'n'), (2,0,5,'o'), (3,0,7,'p');
            		 
             
            		 
            CREATE VIEW v1 AS SELECT tt.a, MAX(
            		 
             (SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
            		 
              LIMIT 1)
            		 
              FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1)) as test
            		 
              FROM t1 as tt GROUP BY tt.a;
            		 
             
            		 
            SELECT * FROM v1;
            		 
             
            		 
            DROP VIEW v1;
            		 
            DROP TABLE t1;

            Actual result:

            At line 12: query 'SELECT * FROM v1' failed: 1111: Invalid use of group function

            Expected result:

            a	test
            		 
            1	n
            		 
            2	o
            		 
            3	p

            lstartseva Lena Startseva added a comment - - edited The same problem with MAX: Test: CREATE TABLE t1 (a int , b INT , d INT , c CHAR (10) NOT NULL , PRIMARY KEY (a, b)); INSERT INTO t1 VALUES (1,1,0, 'a' ), (1,2,0, 'b' ), (1,3,0, 'c' ), (1,4,0, 'd' ), (1,5,0, 'e' ), (2,1,0, 'f' ), (2,2,0, 'g' ), (2,3,0, 'h' ), (3,4,0, 'i' ), (3,3,0, 'j' ), (3,2,0, 'k' ), (3,1,0, 'l' ), (1,9,0, 'm' ), (1,0,10, 'n' ), (2,0,5, 'o' ), (3,0,7, 'p' );   CREATE VIEW v1 AS SELECT tt.a, MAX ( ( SELECT ( SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d= MAX (t1.b + tt.a) LIMIT 1) FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1)) as test FROM t1 as tt GROUP BY tt.a;   SELECT * FROM v1;   DROP VIEW v1; DROP TABLE t1; Actual result: At line 12: query 'SELECT * FROM v1' failed: 1111: Invalid use of group function Expected result: a test 1 n 2 o 3 p
            igor Igor Babaev (Inactive) added a comment -

            Note that if we use CTE with the same specification query as for the view we get the expected result

            MariaDB [test]> WITH cte AS
            		 
                -> (SELECT (SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a) AS c
            		 
                -> FROM t1
            		 
                -> GROUP BY a)
            		 
                -> SELECT * FROM cte;
            		 
            +------+
            		 
            | c    |
            		 
            +------+
            		 
            | 2    |
            		 
            | 3    |
            		 
            | 1,1  |
            		 
            +------+
            		 
            3 rows in set (0.007 sec)

            igor Igor Babaev (Inactive) added a comment - Note that if we use CTE with the same specification query as for the view we get the expected result MariaDB [test]> WITH cte AS -> (SELECT (SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a) AS c -> FROM t1 -> GROUP BY a) -> SELECT * FROM cte; +------+ | c | +------+ | 2 | | 3 | | 1,1 | +------+ 3 rows in set (0.007 sec)
            igor Igor Babaev (Inactive) added a comment -

            The reported test case has been added in patch for MDEV-30668

            igor Igor Babaev (Inactive) added a comment - The reported test case has been added in patch for MDEV-30668

            People

              igor Igor Babaev (Inactive)
              lstartseva Lena Startseva
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.