MySQL GROUP_CONCAT 'Invalid use of Group function' error that DOESN'T involve an aggr-CodePudding

I have a MySQL stored procedure (shown below) that's supposed to build a list of IDs from a table of hierarchically related records. I'm having to re-engineer an older stored procedure to switch from using a simple CONCAT function to GROUP_CONCAT because the former couldn't handle the sizes of the lists being generated (i.e., the lists are going well over the 1024 character limit of the CONCAT function).

DELIMITER $$
CREATE PROCEDURE SPTest (top_id INT)
BEGIN
    DECLARE ids_all TEXT;
    SET SESSION group_concat_max_len = 1000000;
    SET ids_all = top_id;
    SET @str = GROUP_CONCAT('SELECT GROUP_CONCAT(id SEPARATOR \', \') ',
        'FROM tbl WHERE nha_id = ', top_id, ' INTO @ids_tmp' SEPARATOR '');
    PREPARE stmt FROM @str;
    EXECUTE stmt;
    WHILE @ids_tmp != "" DO
        SET ids_all = GROUP_CONCAT(ids_all, @ids_tmp SEPARATOR ', ');
        SET @str = GROUP_CONCAT('SELECT GROUP_CONCAT(id SEPARATOR \', \') ',
            'FROM tbl WHERE nha_id IN (', @ids_tmp, ') INTO @ids_tmp' SEPARATOR '');
        PREPARE stmt FROM @str;
        EXECUTE stmt;
    END WHILE;
    SELECT ids_all AS ids;
END $$
DELIMITER ;

The problem is this routine is generating the following error when I try to call it (and sometimes it returns this error when I try to create the Stored Procedure):

ERROR 1111 (HY000): Invalid use of group function

When I manually create the same kinds of queries this code would build and run them at the command-line, they work perfectly fine -- no errors of any kind, and I get the results I expect. I've seen posts (both here on Stack Exchange and elsewhere) which say that MySQL doesn't support nested aggregate functions, but the concatenation here is just being done to strings. So I thought that maybe somehow it was seeing the GROUP_CONCAT in the string and burping because of that, so I tried putting "XXXX" in place of "GROUP_CONCAT" in the string and then using the REPLACE function to switch it back, but that made no difference. I also tried it with WHERE and HAVING for the criteria clause, but neither one worked. I've also done an extensive web search and was unable to find anything that was in any way helpful.

I don't know what else to try, mainly because I can't see what's wrong with the syntax here. Any help would be appreciated.

Update 1:

I have since tried a modified version of the script where the GROUP_CONCAT merges data from a subquery like this:

SET @qrystr = GROUP_CONCAT('SELECT GROUP_CONCAT(c SEPARATOR ", ") ',
    'FROM (SELECT id AS c FROM tbl WHERE nha_id IN (', @ids_tmp, 
    ') AS d INTO @ids_tmp' SEPARATOR '');

but that made no difference either.

CodePudding user response：

You can't use GROUP_CONCAT() as a scalar function; it must be used in the context of a set of rows. Similarly, you can't use any other aggregate function without a table reference:

SET @x = MAX(<expr>); -- makes no sense

Ideally you should upgrade to MySQL 8.0 if you haven't already, and use a recursive CTE query instead:

WITH RECURSIVE hierarchy AS (
  SELECT top_id AS id
  UNION
  SELECT tbl.id FROM tbl JOIN hierarchy ON tbl.nha_id = hierarchy.id
)
SELECT GROUP_CONCAT(id SEPARATOR ', ') AS ids_all FROM hierarchy;

That would eliminate the need for using loops or prepare/execute or temp variables.

CodePudding user response：

Thanks to Bill Karwin's answer regarding the need to use SELECT with GROUP_CONCAT, I was able to see how my code needed to be changed. Rather than using a SET statement to assign the values to the variables, I need to use a SELECT ... INTO ... construct, as shown here:

DELIMITER $$
CREATE PROCEDURE ASSEMBLY_LIST_TEST (top_id INT)
BEGIN
    DECLARE ids_all TEXT DEFAULT '';
    SET SESSION group_concat_max_len = 1000000;
    SET ids_all = top_id;

    # Find the 1st-level children of 'top_id' to start building the list
    SELECT GROUP_CONCAT('SELECT GROUP_CONCAT(id SEPARATOR ", ") FROM tbl ',
        'WHERE nha_id = ', top_id, ' INTO @ids_tmp' SEPARATOR '') INTO @qrystr;
    PREPARE stmt FROM @qrystr;
    EXECUTE stmt;

    # Recursively find the children of each level of children of the previous loop & add to the list
    WHILE @ids_tmp != '' DO
        SELECT GROUP_CONCAT(ids_all, ', ', @ids_tmp SEPARATOR '') INTO ids_all;
        SELECT GROUP_CONCAT('SELECT GROUP_CONCAT(id SEPARATOR ", ") FROM tbl ',
            'WHERE nha_id IN (', @ids_tmp, ') INTO @ids_tmp' SEPARATOR '') INTO @qrystr;
        PREPARE stmt FROM @qrystr;
        EXECUTE stmt;
    END WHILE;
    SELECT ids_all AS ids;
END $$
DELIMITER ;

This now produces exactly the result I was looking for. Of course, the recursive query approach is a much better solution for what I want this to do, but maybe my solution will help someone else.