MySQL Bugs: #20836: Selecting into variables results in wrong results being returned

Bug #20836	Selecting into variables results in wrong results being returned
Submitted:	3 Jul 2006 23:14	Modified:	13 Dec 2006 19:38
Reporter:	Morgan Tocker	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1, 5.0, 5.1	OS:	Any (ALL)
Assigned to:	Iggy Galarza	CPU Architecture:	Any

Description:
The wrong results are returned when selecting into variables.

The workaround seems to store to a temporary table before selecting into variables.  The fruit 'PEAR' shouldn't be selected in the second query at all, since the where clause eliminates it.

Verified in 5.0.22 and 4.1.20 and 5.1.9-beta.

How to repeat:
DROP TABLE IF EXISTS `a`;

CREATE TABLE `a` (
  `id` INT NOT NULL,
  `fruit_id` INT NOT NULL,
  `fruit_name` varchar(50) default NULL
);

INSERT INTO `a` VALUES (1,1,'ORANGE');
INSERT INTO `a` VALUES (2,2,'APPLE');
INSERT INTO `a` VALUES (3,2,'APPLE');
INSERT INTO `a` VALUES (4,3,'PEAR');

mysql> SELECT @fruit_id, @fruit_name;
+-----------+-------------+
| @fruit_id | @fruit_name |
+-----------+-------------+
| NULL      | NULL        |
+-----------+-------------+
1 row in set (0.00 sec)

This is the correct result:

mysql> SELECT DISTINCT fruit_id, fruit_name FROM a WHERE fruit_name = 'APPLE';
+----------+------------+
| fruit_id | fruit_name |
+----------+------------+
|        2 | APPLE      |
+----------+------------+
1 row in set (0.00 sec)

This should return the same result as above, but doesn't:

mysql> SELECT DISTINCT fruit_id, fruit_name INTO @fruit_id, @fruit_name FROM a WHERE fruit_name = 'APPLE';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @fruit_id, @fruit_name;
+-----------+-------------+
| @fruit_id | @fruit_name |
+-----------+-------------+
| 3         | PEAR        |
+-----------+-------------+
1 row in set (0.00 sec)

Thank you for a bug report. Verified just as described with 5.0.24-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.24

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

mysql> DROP TABLE IF EXISTS `a`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `a` (
    ->   `id` INT NOT NULL,
    ->   `fruit_id` INT NOT NULL,
    ->   `fruit_name` varchar(50) default NULL
    -> );

Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `a` VALUES (1,1,'ORANGE');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `a` VALUES (2,2,'APPLE');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `a` VALUES (3,2,'APPLE');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `a` VALUES (4,3,'PEAR');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT DISTINCT fruit_id, fruit_name FROM a WHERE fruit_name = 'APPLE';
+----------+------------+
| fruit_id | fruit_name |
+----------+------------+
|        2 | APPLE      |
+----------+------------+
1 row in set (0.00 sec)

mysql> SELECT DISTINCT fruit_id, fruit_name INTO @fruit_id, @fruit_name FROM a
    -> WHERE fruit_name = 'APPLE';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @fruit_id, @fruit_name;
+-----------+-------------+
| @fruit_id | @fruit_name |
+-----------+-------------+
| 3         | PEAR        |
+-----------+-------------+
1 row in set (0.00 sec)

also verified on latest 5.1.12-bk

<cut>
mysql> SELECT @fruit_id, @fruit_name;
+-----------+-------------+
| @fruit_id | @fruit_name |
+-----------+-------------+
| 3         | PEAR        |
+-----------+-------------+
1 row in set (0.00 sec)

I am still working on this issue and need more time.  In the meantime, a possible workaround to this issue is to use the LIMIT command in place of the DISTINCT directive.  

master> SELECT fruit_id, fruit_name INTO @v1, @v2 FROM a WHERE fruit_name = 'APPLE' LIMIT 0,1;
Query OK, 1 row affected (0.00 sec)

master> select @v1, @v2;
+------+-------+
| @v1  | @v2   |
+------+-------+
| 2    | APPLE |
+------+-------+
1 row in set (0.00 sec)

bug#21720 is a duplicate.

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/16051

ChangeSet@1.2555, 2006-11-28 17:21:39-05:00, iggy@rolltop.ignatz42.dyndns.org +4 -0
  Bug#20836 Selecting into variables results in wrong results being returned
  This error is displayed anytime the SELECT statement needs a temp table to
  return correct results because the object (select_dumpvar) that represents
  variables named in the INTO clause stored the results before the temp
  table was considered.  The problem was fixed by creating the necessary
  Item_func_set_user_var objects once the correct data is ready.

The 5.0 version of the patch for this bug depends on a correct fix for Bug#16861 "User variables and ORDER BY RAND() LIMIT 1 product unexpected result"

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/16240

ChangeSet@1.2296, 2006-11-30 13:09:48-05:00, iggy@rolltop.ignatz42.dyndns.org +3 -0
  Bug#20836 Selecting into variables results in wrong results being returned
  - Post-merge cleanup.
  - Disabled this bug's test for 5.x until 16861 is resolved.

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/16457

ChangeSet@1.2556, 2006-12-05 09:29:32-05:00, iggy@rolltop.ignatz42.dyndns.org +1 -0
  Bug#20836 Selecting into variables results in wrong results being returned
  - Comment Cleanup.

Noted in 4.1.23, 5.0.32, 5.1.15 changelogs.

Selecting into variables sometimes returned incorrect wrong results.