MySQL Bugs: #13575: SELECT id, myFunc(id) FROM aTable ORDER BY id; returns bad data

Bug #13575	SELECT id, myFunc(id) FROM aTable ORDER BY id; returns bad data
Submitted:	28 Sep 2005 16:07	Modified:	14 Mar 2006 19:05
Reporter:	Mitch Stone	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0.12-nt, 5.0.13-rc-nt	OS:	Windows (WinXP)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
SELECT id, myFunc(id) FROM aTable ORDER BY id; 

returns garbled data in the function column, where id is an auto-increment, primary key on the table.  Specifically, the data returned by the function appears to be the same length as the what the correct answer should be, but first uses the data from the next row, then pads with data from the current row.

Yet, when no ORDER BY clause is used, or when some other field is used in the ORDER BY column besides the id field, data is returned as expected.

How to repeat:
-- Execute the following

DROP TABLE IF EXISTS users;

CREATE TABLE users (
userID int(11) NOT NULL auto_increment,
userName varchar(64) NOT NULL,
firstName varchar(32) default NULL,
lastName varchar(32) default NULL,
CONSTRAINT pkUsers PRIMARY KEY (userID)
);

DROP FUNCTION IF EXISTS GetUserFullName;

DELIMITER %%;
CREATE FUNCTION GetUserFullName(
usrID INTEGER
) RETURNS varchar(32)
BEGIN
DECLARE uName VARCHAR(32) DEFAULT null;

IF EXISTS (SELECT count(*) FROM users WHERE userID = usrID) THEN
SELECT concat(firstName, ' ', lastName) INTO uName FROM users WHERE userID = usrID;
END IF;

RETURN uName;
END%%
DELIMITER ;%%

INSERT INTO users (userName, firstName, lastName) VALUES
('xena','Warrior','Princess'),
('gabrielle','Amazon','Princess'),
('batman','Bruce','Wayne'),
('spiderman','Peter','Parker');

SELECT * FROM users;
-- returns data as expected

SELECT DISTINCT userID, userName, GetUserFullName(userID) AS fullname
FROM users;
-- returns data as expected, default sorted by userID

SELECT DISTINCT userID, userName, GetUserFullName(userID) AS fullname
FROM users ORDER BY userName;
-- returns data as expected, sorted by userName
/*
+--------+-----------+------------------+
| userID | userName | fullname |
+--------+-----------+------------------+
| 3 | batman | Bruce Wayne |
| 2 | gabrielle | Amazon Princess |
| 4 | spiderman | Peter Parker |
| 1 | xena | Warrior Princess |
+--------+-----------+------------------+
*/

SELECT DISTINCT userID, userName, GetUserFullName(userID) AS fullname
FROM users ORDER BY userID;
-- data sorted by userID, but fullname column is WONKY
/*
+--------+-----------+------------------+
| userID | userName | fullname |
+--------+-----------+------------------+
| 1 | xena | Amazon Princesss |
| 2 | gabrielle | Bruce Waynecess |
| 3 | batman | Peter Parke |
| 4 | spiderman | Peter Parker |
+--------+-----------+------------------+
*/

SELECT DISTINCT userID, userName, CONCAT(firstName, ' ', lastName) AS fullname
FROM users ORDER BY userID;
-- returns data as expected, sorted by userID

I was unable to repeat. Did you got that wrong results on new fresh
install or you upgraded over an older one as 5.0.9?

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.12-beta-nt

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

mysql> SELECT DISTINCT userID, userName, GetUserFullName(userID) AS fullname
    -> FROM users ORDER BY userID;
+--------+-----------+------------------+
| userID | userName  | fullname         |
+--------+-----------+------------------+
|      1 | xena      | Warrior Princess |
|      2 | gabrielle | Amazon Princess  |
|      3 | batman    | Bruce Wayne      |
|      4 | spiderman | Peter Parker     |
+--------+-----------+------------------+
4 rows in set (0.02 sec)

mysql> -- data sorted by userID, but fullname column is WONKY
mysql> /*
   /*> +--------+-----------+------------------+
   /*> | userID | userName | fullname |
   /*> +--------+-----------+------------------+
   /*> | 1 | xena | Amazon Princesss |
   /*> | 2 | gabrielle | Bruce Waynecess |
   /*> | 3 | batman | Peter Parke |
   /*> | 4 | spiderman | Peter Parker |
   /*> +--------+-----------+------------------+
   /*> */
mysql>

I was able to repeat the described behaviour on 5.0.13-rc-nt  after upgrade from 5.0.12-nt just as described (see also private comment above).

Hi!

I am able to repeat the bug with 5.0.14 on Linux.

A MyISAM table works ok, but an InnoDB table returns garbled values for the function.

A possible reason would be that MySQL fails to tell InnoDB that InnoDB should fetch also the firstName and lastName columns. But adding the columns explicitly to the query did not help:

mysql> SELECT DISTINCT userID, userName, firstName, lastName, GetUserFullName(userID) AS fullname
    -> FROM users ORDER BY userID;
+--------+-----------+-----------+----------+------------------+
| userID | userName  | firstName | lastName | fullname         |
+--------+-----------+-----------+----------+------------------+
|      1 | xena      | Warrior   | Princess | Amazon Princesss |
|      2 | gabrielle | Amazon    | Princess | Bruce Waynecess  |
|      3 | batman    | Bruce     | Wayne    | Peter Parke      |
|      4 | spiderman | Peter     | Parker   | Peter Parker     |
+--------+-----------+-----------+----------+------------------+
4 rows in set (0.02 sec)

mysql>

Thus, maybe the bug is something else, or the column values for the function calculation are fetched at a different time than the values for the columns that are explicitly listed in the SELECT list.

Regards,

Heikki

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/2930

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/3506

See e-mail about small changes in last commit.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/3692

By default Item_sp_func::val_str() returns string from it's result_field 
internal buffer. When grouping is present Item_copy_string is used to 
store SP function result, but it doesn't additionally buffer the result.
When the next record is read, internal buffer is overwritten, due to
this Item_copy_string::val_str() will have wrong data. Thus producing
weird query result.

Fixed in 5.0.20, cset 1.2085

Noted in 5.0.20 changelog.

Use of stored functions with <literal>DISTINCT</literal> or
<literal>GROUP BY</literal> can produce incorrect results when
<literal>ORDER BY</literal> is also used. (Bug #13575)