Bug #8817 coalesce() with max() or count() in stored proc. does not work next time

• Submitted: 26 Feb 2005 0:26
• Modified: 26 Feb 2005 1:14
• Reporter: [ name withheld ]
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.2-alpha-max-log
• OS: Windows (WinXp Professional)
• CPU Architecture: Any

[26 Feb 2005 0:26] [ name withheld ]

Description:
When you use coalesce() with group function like max() or count(), it works for the first time and do not work thereafter.
When you try to call the same procedure next time it says "Error 1247 (42S22): Reference '(null)' not supported (reference to group function)".

How to repeat:
create table t1(id int,name varchar(5));
insert into t1 values(1,'dfdfsdf');
insert into t1 values(2,'aaaa');

DELIMITER $$

DROP PROCEDURE IF EXISTS test_coalesce$$
CREATE PROCEDURE  test_coalesce()
    SQL SECURITY INVOKER
begin
      declare maxOrder int;
SELECT
        coalesce(count(id),0)+1 INTO maxOrder
    FROM
        t1;
    select maxOrder;
end$$

DELIMITER ;

Call the procedure as 
call test_coalesce();

It works fine for the first time but won't work thereafter.

Suggested fix:
Do it in two step as 

DROP PROCEDURE IF EXISTS test_coalesce_1$$
CREATE PROCEDURE  test_coalesce_1()
    SQL SECURITY INVOKER
begin
      declare maxOrder int;
SELECT
        count(id) INTO maxOrder
    FROM
        t1;
    
set maxOrder=coalesce( maxOrder,0)+1;
select maxOrder;
end$$
delimiter ;

[26 Feb 2005 1:14] MySQL Verification Team

This was already fixed in the current BK source. Thank you for the bug
report:

C:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.3-alpha-pro

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table t1(id int,name varchar(5));
Query OK, 0 rows affected (0.15 sec)

mysql> insert into t1 values(1,'dfdfsdf');
Query OK, 1 row affected, 1 warning (0.06 sec)

mysql> insert into t1 values(2,'aaaa')
    -> ;
Query OK, 1 row affected (0.00 sec)

mysql> DELIMITER $$
mysql>
mysql> DROP PROCEDURE IF EXISTS test_coalesce$$
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> CREATE PROCEDURE  test_coalesce()
    ->     SQL SECURITY INVOKER
    -> begin
    ->       declare maxOrder int;
    -> SELECT
    ->         coalesce(count(id),0)+1 INTO maxOrder
    ->     FROM
    ->         t1;
    ->     select maxOrder;
    -> end$$
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> DELIMITER ;
mysql> call test_coalesce();
+----------+
| maxOrder |
+----------+
|        3 |
+----------+
1 row in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

mysql> call test_coalesce();
+----------+
| maxOrder |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call test_coalesce();
+----------+
| maxOrder |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>