MySQL Bugs: #14011: SELECT DISTINCT doesn't work properly in stored procedures

Bug #14011	SELECT DISTINCT doesn't work properly in stored procedures
Submitted:	13 Oct 2005 18:01	Modified:	26 May 2006 12:56
Reporter:	Scott Hadfield	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0.13-rc	OS:	Linux (RHEL AS release 3)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
Using a SELECT DISTINCT doesn't seem to work properly in a stored procedure when using with INTO. I apologize in advance if this is something wrong that I'm doing as I'm quite new to stored procedures.

How to repeat:
The following commands can demonstrate the problem:

----
create table testing1 (`testval` int(1));
insert into testing1 values ((1), (2), (3), (4), (5), (6));

delimiter //
create procedure test_proc (OUT results1 INT) BEGIN SELECT DISTINCT(testval) FROM testing1 WHERE testval = 3 INTO results1; END //
delimiter ;

call test_proc(@a);
select @a;
----

This should return:
+------+
| @a   |
+------+
| 3    |
+------+

But instead returns:
+------+
| @a   |
+------+
| 6    |
+------+

Note that a stored proc as follows without the DISTINCT will produce the correct result:

create procedure test_proc (OUT results1 INT) BEGIN SELECT testval FROM testing1 WHERE testval = 3 INTO results1; END //

It also seems to work when INTO isn't used.

verified with the slightly modified script below that
fixes a small typo in the original report and reformated
the query for better readability:

---------------------------------------------------------------

create table testing1 (`testval` int(1));
insert into testing1 values (1), (2), (3), (4), (5), (6);

delimiter //
create procedure test_proc (OUT results1 INT) 
BEGIN 
 SELECT DISTINCT(testval)
   FROM testing1 
  WHERE testval = 3 
  INTO results1; 
END //
delimiter ;

call test_proc(@a);
select @a;

Thank you for taking the time to report a problem.  Unfortunately
you are not using a current version of the product your reported a
problem with -- the problem might already be fixed. Please download
a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with one of the latest versions,
please change the version on this bug report to the version you
tested and change the status back to "Open".  Again, thank you for
your continued support of MySQL.

Additional info:

Just tried it with 5.0.23-BK on FC5.
It seems that the script here works correctly with it