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

Nested GROUP_CONCAT in stored function gives "ERROR 1111 (HY000) : Invalid use of group function"

    XMLWordPrintable

Details

    Description

      /*
      In MariaDB version 10.3.27 and possibly later versions, the below function worked.
      But in version 10.5.8, calling the function gives error: "ERROR 1111 (HY000) at line 65: Invalid use of group function".
      */ 
       
      DELIMITER $$
       
      DROP TABLE IF EXISTS t1;
      DROP TABLE IF EXISTS t2;
       
      CREATE TABLE t1 (t1_id int primary key, t1_text varchar(100));
      CREATE TABLE t2 (t2_id1 int, t2_id2 int, t2_text varchar(100), primary key(t2_id1, t2_id2) );
       
      INSERT t1 VALUES (1, 'Text 1'), (2, 'Text 2');
      INSERT t2 VALUES (1, 1, 'Text 11'), (1, 2, 'Text 12'), (2, 1, 'Text 21'), (2, 2, 'Text 22');
       
      -- This query works:
       
      SELECT GROUP_CONCAT(
        (
          SELECT GROUP_CONCAT(t2_text)
          FROM t2
          WHERE t2_id1 = t1_id
        )
      ) this_works
      FROM t1;
       
       
      DROP FUNCTION IF EXISTS fn_test;
      $$
      CREATE FUNCTION fn_test() RETURNS varchar(500) READS SQL DATA
      RETURN (
        SELECT GROUP_CONCAT(
          (
            SELECT GROUP_CONCAT(t2_text)
            FROM t2
            WHERE t2_id1 = t1_id
          )
        ) x
        FROM t1
      );
      $$
       
      SELECT fn_test();
      

      Attachments

        Issue Links

          Activity

            People

              sanja Oleksandr Byelkin
              tgj1970 Thomas G. Jensen
              Votes:
              3 Vote for this issue
              Watchers:
              6 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.