MySQL Bugs: #13771: DISTINCT with cursors doesn't work properly

Bug #13771	DISTINCT with cursors doesn't work properly
Submitted:	5 Oct 2005 12:33	Modified:	17 Nov 2005 18:01
Reporter:	Gleb Paharenko	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.13, 5.0.15-rc-BK	OS:	Linux (Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
This procedure returns wrong results:

CREATE PROCEDURE `test`.`t1` (OUT counted INT)
DETERMINISTIC
BEGIN
DECLARE countit,done INT DEFAULT 0;
DECLARE name,telephone
CHAR(255);
DECLARE cur1 CURSOR FOR
SELECT
DISTINCT
name,telephone FROM
extended WHERE centre_name != "";
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

SET countit = 0;
OPEN cur1;

REPEAT
    FETCH cur1 INTO
name,telephone;
    IF NOT done THEN
       SET countit=countit+1;
    END IF;
UNTIL done END REPEAT;
CLOSE cur1;
SET @b = countit;
SELECT @b;
SET counted=countit;

END$$

How to repeat:
CREATE PROCEDURE `test`.`t1` (OUT counted INT)
DETERMINISTIC
BEGIN
DECLARE countit,done INT DEFAULT 0;
DECLARE name,telephone
CHAR(255);
DECLARE cur1 CURSOR FOR
SELECT
DISTINCT
name,telephone FROM
extended WHERE centre_name != "";
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

SET countit = 0;
OPEN cur1;

REPEAT
    FETCH cur1 INTO
name,telephone;
    IF NOT done THEN
       SET countit=countit+1;
    END IF;
UNTIL done END REPEAT;
CLOSE cur1;
SET @b = countit;
SELECT @b;
SET counted=countit;

END$$

CREATE TABLE `extended` (
  `name` char(10) default NULL,
  `telephone` char(20) default NULL,
  `centre_name` char(10) default 'a'
) ENGINE=MyISAM DEFAULT CHARSET=utf8

INSERT INTO `extended` VALUES ('1','1','a'),('1','2','a'),('1','2','a'),('1','1'
,'a'),('1','5','a');

mysql> call t1(@a);
+------+
| @b   |
+------+
| 1    |
+------+
1 row in set (0.00 sec)

While the query returns correct number of rows - 3:
mysql> SELECT
    -> DISTINCT
    -> name,telephone FROM
    -> extended WHERE centre_name != "";
+------+-----------+
| name | telephone |
+------+-----------+
| 1    | 1         |
| 1    | 2         |
| 1    | 5         |
+------+-----------+

If I remove the DISTINCT from the CURSOR definition, procedure returns the correct number of rows:
DROP PROCEDURE IF EXISTS `test`.`t2`$$
CREATE PROCEDURE `test`.`t2` (OUT counted INT)
DETERMINISTIC
BEGIN
DECLARE countit,done INT DEFAULT 0;
DECLARE name,telephone
CHAR(255);
DECLARE cur1 CURSOR FOR
SELECT
name,telephone FROM
extended WHERE centre_name != "";
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

SET countit = 0;
OPEN cur1;

REPEAT
    FETCH cur1 INTO
name,telephone;
    IF NOT done THEN
       SET countit=countit+1;
    END IF;
UNTIL done END REPEAT;
CLOSE cur1;
SET @b = countit;
SELECT @b;
SET counted=countit;

END$$

DELIMITER ;

mysql> call t2(@a);
+------+
| @b   |
+------+
| 5    |
+------+

mysql> select name, telephone from extended WHERE centre_name != ""; 
+------+-----------+
| name | telephone |
+------+-----------+
| 1    | 1         |
| 1    | 2         |
| 1    | 2         |
| 1    | 1         |
| 1    | 5         |
+------+-----------+
5 rows in set (0.00 sec)

@b=5 - the same cound has been returned by the query.

Suggested fix:
Fix this

Verified just as described on today's -BK build on Fedora Core 1:

mysql> CREATE TABLE `extended` (
    ->   `name` char(10) default NULL,
    ->   `telephone` char(20) default NULL,
    ->   `centre_name` char(10) default 'a'
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.42 sec)

mysql> INSERT INTO `extended` VALUES
    -> (('1','1','a'),('1','2','a'),('1','2','a'),('1','1'
    -> ,'a'),('1','5','a');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> delimiter $$
mysql> CREATE PROCEDURE `test`.`t1` (OUT counted INT)
    -> DETERMINISTIC
    -> BEGIN
    -> DECLARE countit,done INT DEFAULT 0;
    -> DECLARE name,telephone
    -> CHAR(255);
    -> DECLARE cur1 CURSOR FOR
    -> SELECT
    -> DISTINCT
    -> name,telephone FROM
    -> extended WHERE centre_name != "";
    -> DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    ->
    -> SET countit = 0;
    -> OPEN cur1;
    ->
    -> REPEAT
    ->      FETCH cur1 INTO
    -> name,telephone;
    ->      IF NOT done THEN
    ->         SET countit=countit+1;
    ->     END IF;
    -> UNTIL done END REPEAT;
    -> CLOSE cur1;
    -> SET @b = countit;
    -> SELECT @b;
    -> SET counted=countit;
    ->
    -> END$$
Query OK, 0 rows affected (0.19 sec)

mysql> delimiter ;
mysql> call t1(@a);
+------+
| @b   |
+------+
| 1    |
+------+
1 row in set (0.09 sec)

Query OK, 0 rows affected (0.11 sec)

mysql> SELECT
    -> DISTINCT
    -> name,telephone FROM
    -> extended WHERE centre_name != "";
+------+-----------+
| name | telephone |
+------+-----------+
| 1    | 1         |
| 1    | 2         |
| 1    | 5         |
+------+-----------+
3 rows in set (0.01 sec)

mysql> delimiter $$
mysql> DROP PROCEDURE IF EXISTS `test`.`t2`$$
CQuery OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE PROCEDURE `test`.`t2` (OUT counted INT)
    -> DETERMINISTIC
    -> BEGIN
    -> DECLARE countit,done INT DEFAULT 0;
    -> DECLARE name,telephone
    -> CHAR(255);
    -> DECLARE cur1 CURSOR FOR
    -> SELECT
    -> name,telephone FROM
    -> extended WHERE centre_name != "";
    -> DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    ->
    -> SET countit = 0;
    -> OPEN cur1;
    ->
    -> RREPEAT
    ->      FETCH cur1 INTO
    -> name,telephone;
    ->     IF NOT done THEN
    ->        SET countit=countit+1;
    ->     END IF;
    -> UNTIL done END REPEAT;
    -> CLOSE cur1;
    -> SET @b = countit;
    -> SELECT @b;
    -> SET counted=countit;
    ->
    -> END$$
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;
mysql> call t2(@a);
+------+
| @b   |
+------+
| 5    |
+------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> select name, telephone from extended WHERE centre_name != "";
+------+-----------+
| name | telephone |
+------+-----------+
| 1    | 1         |
| 1    | 2         |
| 1    | 2         |
| 1    | 1         |
| 1    | 5         |
+------+-----------+
5 rows in set (0.01 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.15-rc |
+-----------+
1 row in set (0.01 sec)

Sorry, this is a duplicate of Bug#5967  "Stored procedure declared variable used instead of column" which we can not fix in 5.0 version.

This restriction is documented here:
http://dev.mysql.com/doc/refman/5.0/en/routine-restrictions.html

Note: If an SQL statement, such as a SELECT ... INTO statement, contains a reference to a column and a declared local variable with the same name, MySQL interprets the reference as the name of a variable. This is non-standard behavior; the order of precedence is usually column names, then SQL variables and parameters. See Section 17.2.9.3, “SELECT ... INTO Statement”.

I will write a documentation request to include normal SELECTs and cursor SELECTs into the manual as a subject of the described limitation.