MySQL Bugs: #16052: SELECT COUNT(xxx) returns wrong value in PROCEDURE

Bug #16052	SELECT COUNT(xxx) returns wrong value in PROCEDURE
Submitted:	28 Dec 2005 22:07	Modified:	31 Jan 2006 13:57
Reporter:	Oli Sennhauser	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	mysql-5.0.17/5.0.19 BK	OS:	Linux (Linux 2.6.11.4-21.10-default)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
See Example.
SELECT COUNT(*) from information_schema.SCHEMATA in PROCEDURE returns wrong amount of schematas.
In my opinion the bad should not return 3 but 1.
Workaround see good.
Regards Oli

How to repeat:
SELECT CATALOG_NAME, SCHEMA_NAME
  FROM information_schema.SCHEMATA
;
#+--------------+--------------------+
#| CATALOG_NAME | SCHEMA_NAME        |
#+--------------+--------------------+
#| NULL         | information_schema |
#| NULL         | mysql              |
#| NULL         | test               |
#+--------------+--------------------+
#3 rows in set (0.00 sec)

SELECT COUNT(SCHEMA_NAME)
  FROM information_schema.SCHEMATA
 WHERE SCHEMA_NAME = 'test'
;
#+----------+
#| COUNT(*) |
#+----------+
#|        1 |
#+----------+
#1 row in set (0.00 sec)

delimiter //

CREATE PROCEDURE bad (IN schema_name VARCHAR(64), OUT rc INT)
DETERMINISTIC
BEGIN

  DECLARE cnt         INT;

  SELECT COUNT(SCHEMA_NAME)
    INTO cnt
    FROM information_schema.SCHEMATA
   WHERE SCHEMA_NAME = schema_name
  ;

  SET rc = cnt;

END
//

delimiter ;

CALL bad ('test', @rc);

SELECT @rc;

DROP PROCEDURE bad;

delimiter //

CREATE PROCEDURE good (IN schema_name VARCHAR(64), OUT rc INT)
DETERMINISTIC
BEGIN

  DECLARE cnt         INT;

  SELECT COUNT(DISTINCT SCHEMA_NAME)
    INTO cnt
    FROM information_schema.SCHEMATA
   WHERE SCHEMA_NAME = schema_name
  ;

  SET rc = cnt;

END
//

delimiter ;

CALL good ('test', @rc);

SELECT @rc;

DROP PROCEDURE good;

Suggested fix:
Workaround see good.

Thank you for the bug report.

miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.19-debug

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

mysql> SELECT COUNT(SCHEMA_NAME)
    ->   FROM information_schema.SCHEMATA
    ->  WHERE SCHEMA_NAME = 'test'
    -> ;
+--------------------+
| COUNT(SCHEMA_NAME) |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.01 sec)

mysql> delimiter //
mysql> CREATE PROCEDURE bad (IN schema_name VARCHAR(64), OUT rc INT)
    -> DETERMINISTIC
    -> BEGIN
    -> 
    ->   DECLARE cnt         INT;
    -> 
    ->   SELECT COUNT(SCHEMA_NAME)
    ->     INTO cnt
    ->     FROM information_schema.SCHEMATA
    ->    WHERE SCHEMA_NAME = schema_name
    ->   ;
    -> 
    ->   SET rc = cnt;
    -> 
    -> END
    -> //
Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> delimiter ;
mysql> CALL bad ('test', @rc);
Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> SELECT @rc;
+------+
| @rc  |
+------+
| 8    |
+------+
1 row in set (0.00 sec)

mysql> DROP PROCEDURE bad;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> delimiter //
mysql> 
mysql> CREATE PROCEDURE good (IN schema_name VARCHAR(64), OUT rc INT)
    -> DETERMINISTIC
    -> BEGIN
    -> 
    ->   DECLARE cnt         INT;
    -> 
    ->   SELECT COUNT(DISTINCT SCHEMA_NAME)
    ->     INTO cnt
    ->     FROM information_schema.SCHEMATA
    ->    WHERE SCHEMA_NAME = schema_name
    ->   ;
    -> 
    ->   SET rc = cnt;
    -> 
    -> END
    -> //
Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> delimiter ;
mysql> 
mysql> CALL good ('test', @rc);
Query OK, 0 rows affected (0.04 sec)

mysql> 
mysql> SELECT @rc;
+------+
| @rc  |
+------+
| 1    |
+------+
1 row in set (0.00 sec)

mysql>

This is a duplicate of BUG#5967.

The problem is that schema_name is used as the parameter name, which clashes with the column SCHEMA_NAME. (Case is not significant.)

Workaround: Qualify the column name with a table prefix in the WHERE clause, or use a different parameter name.