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:Microsoft Windows (WinXP)
Assigned to: Evgeny Potemkin CPU Architecture:Any

[28 Sep 2005 16:07] Mitch Stone
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
[28 Sep 2005 17:01] Miguel Solorzano
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>
[28 Sep 2005 17:14] Valeriy Kravchuk
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).
[28 Sep 2005 21:51] Heikki Tuuri
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
[20 Feb 2006 19:17] Bugs System
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
[6 Mar 2006 14:08] Bugs System
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
[7 Mar 2006 12:41] Oleksandr Byelkin
See e-mail about small changes in last commit.
[10 Mar 2006 10:53] Bugs System
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
[10 Mar 2006 11:08] Evgeny Potemkin
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
[14 Mar 2006 19:05] Paul Dubois
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)