[MDEV-24875] Nested GROUP_CONCAT in stored function gives "ERROR 1111 (HY000) : Invalid use of group function" Created: 2021-02-15  Updated: 2023-12-28

Status: Confirmed
Project: MariaDB Server
Component/s: N/A
Affects Version/s: 10.5.8, 10.4, 10.5
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2, 11.3

Type: Bug Priority: Major
Reporter: Thomas G. Jensen Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 3
Labels: GROUP_CONCAT, function, regression
Environment:

Ubuntu 20.04 LTS

Issue Links:
Relates
relates to MDEV-11867 Invalid use of group function - Closed

 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();



 Comments   
Comment by Alice Sherepa [ 2021-02-16 ]

Also with other aggregated functions. MariaDB 10.4,10.5 returns error "Invalid use of group function"

select sum((select sum(1)));
 
create function f1() returns int return (select sum((select sum(1))));
 
select f1();


MariaDB [test]> select sum( (select sum(1))   );
 
+--------------------------+
 
| sum( (select sum(1))   ) |
 
+--------------------------+
 
|                        1 |
 
+--------------------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [test]> select f1();
 
ERROR 1111 (HY000): Invalid use of group function
 
Error (Code 1111): Invalid use of group function
 
Note (Code 4094): At line 2 in test.f1

Comment by Jan Schmidt [ 2023-05-05 ]

I can confirm this error even with MariaDB 10.6 and 10.11.2. Tested with the simple example from the comment of Alice Sherepa.

This prevents me from upgrading to a newer version than 10.3.

Comment by Jan Schmidt [ 2023-06-26 ]

This is a problematic issue for me because 10.3 is EOL and I have multiple functions using something like 

SELECT avg(
 
  CASE WHEN
 
   (SELECT sum(x) ...) > y
 
  THEN 1.0
 
  ELSE 0.0
 
  END
 
)

Is there a better solution for this?

Comment by Geisel [ 2023-12-28 ]

11.2.2 is problematic too.

MariaDB [gss]> create function f1() returns int return (select sum((select sum(1))));
 
Query OK, 0 rows affected (0.014 sec)
 
 
 
MariaDB [gss]> select f1();
 
ERROR 1111 (HY000): Invalid use of group function
 
 
 
MariaDB [gss]> show variables like '%version%';
 
+-----------------------------------+------------------------------------------+
 
| Variable_name                     | Value                                    |
 
+-----------------------------------+------------------------------------------+
 
| in_predicate_conversion_threshold | 1000                                     |
 
| protocol_version                  | 10                                       |
 
| slave_type_conversions            |                                          |
 
| system_versioning_alter_history   | ERROR                                    |
 
| system_versioning_asof            | DEFAULT                                  |
 
| system_versioning_insert_history  | OFF                                      |
 
| tls_version                       | TLSv1.2,TLSv1.3                          |
 
| version                           | 11.2.2-MariaDB-1:11.2.2+maria~ubu2204    |
 
| version_comment                   | mariadb.org binary distribution          |
 
| version_compile_machine           | x86_64                                   |
 
| version_compile_os                | debian-linux-gnu                         |
 
| version_malloc_library            | system                                   |
 
| version_source_revision           | 929532a9426d085111c24c63de9c23cc54382259 |
 
| version_ssl_library               | OpenSSL 3.0.2 15 Mar 2022                |
 
| wsrep_patch_version               | wsrep_26.22                              |
 
+-----------------------------------+------------------------------------------+
 
15 rows in set (0.001 sec)

Comment by Geisel [ 2023-12-28 ]

Hi @Alice Sherepa.
How I use the fixed version 1.2 [ 28603 ] ?
Thanks a lot!

Comment by Sergei Golubchik [ 2023-12-28 ]

Caused by

commit de745ecf297
 
Author: Oleksandr Byelkin <sanja@mariadb.com>
 
Date:   Tue May 22 19:08:39 2018 +0200
 
 
 
    MDEV-11953: support of brackets in UNION/EXCEPT/INTERSECT operations

Generated at Thu Feb 08 09:33:21 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.