Bug #44322 stored function returns incorrect result when usin filesort or no index

• Submitted: 16 Apr 2009 13:32
• Modified: 16 Apr 2009 18:35
• Reporter: Alex Zimnitski
• Status: Not a Bug
• Category: MySQL Server: Stored Routines
• Severity: S3 (Non-critical)
• Version: 5.0.27
• OS: Any
• CPU Architecture: Any
• Tags: FUNCTION, INDEX, stored, table

[16 Apr 2009 13:32] Alex Zimnitski

Description:
I have 2 tables (in reality much more ).
Also I have a stored function that returns by id from 1st table sum by 2nd table.
When i call that function first time on connection, variable initialised in stored function is NULL, but if i 'SET @var=0' before function call, all is ok.
When i call that function on whole 1st table using index from 1st table, result is ok.
When i call that function on whole 1st table using my own artful index (external data, ...), sometime result is incorrect

How to repeat:
drop database if exists BugTest;
create database BugTest;

use BugTest;

CREATE TABLE k (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
	id_d INT NOT NULL ,
	n INT NOT NULL 
) ENGINE = MYISAM ;

CREATE TABLE d (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
	k INT NOT NULL 
) ENGINE = MYISAM ;

INSERT INTO d ( id, k )
VALUES
( 1 , 345 ),
( 3 , 234 ),
( 2 , 123 );

INSERT INTO k ( id, id_d, n )
VALUES
( NULL , 1, 1 ),
( NULL , 1, 2 ),
( NULL , 2, 1 ),
( NULL , 2, 2 ), 
( NULL , 3, 1 ),
( NULL , 3, 2 ),
( NULL , 1, 3 ),
( NULL , 2, 4 ),
( NULL , 3, 5 );

DELIMITER |
DROP FUNCTION IF EXISTS D_GET_SUM|
CREATE FUNCTION D_GET_SUM( d_id MEDIUMINT ) RETURNS INT
BEGIN
	DECLARE p_s INT DEFAULT 0;
	SELECT 	COUNT(*), SUM(n)
		INTO
		p_s, @d_n
	FROM k
	WHERE id_d=d_id;
	RETURN	p_s;
END|
DELIMITER ;

SELECT *, D_GET_SUM(d.id), @d_n
FROM d;

SET @d_n = 0;
SELECT *, D_GET_SUM(d.id), @d_n
FROM d;

SET @d_n = 0;
SELECT *, D_GET_SUM(d.id), @d_n
FROM d ORDER BY RAND();

------------------------
id	k	D_GET_SUM(d.id)	@d_n
1	345	3	NULL
3	234	3	NULL
2	123	3	NULL

id	k	D_GET_SUM(d.id)	@d_n
1	345	3	6
3	234	3	8
2	123	3	7

id	k	D_GET_SUM(d.id)	@d_n
1	345	3	7
2	123	3	7
3	234	3	7

Suggested fix:
?

[16 Apr 2009 18:35] Valeriy Kravchuk

I think this is not a bug. Our manual (http://dev.mysql.com/doc/refman/5.0/en/user-variables.html) clearly says:

"The general rule is never to assign a value to a user variable in one part of a statement and use the same variable in some other part of the same statement. You might get the results you expect, but this is not guaranteed."

Exactly this happens in your case: you assign @var in function and read it in SELECT that uses this function. Surely changing query execution plan (with ORDER BY) may lead to unexpected results.