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

[3 Jul 2006 23:14] Morgan Tocker
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)
[4 Jul 2006 7:56] Valeriy Kravchuk
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)
[5 Jul 2006 16:52] MySQL Verification Team
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)
[14 Aug 2006 19:57] Iggy Galarza
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)
[14 Sep 2006 20:32] Iggy Galarza
bug#21720 is a duplicate.
[28 Nov 2006 22:21] 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/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.
[29 Nov 2006 20:19] Konstantin Osipov
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"
[30 Nov 2006 18:09] 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/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.
[5 Dec 2006 14:29] 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/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.
[13 Dec 2006 19:35] Paul DuBois
Noted in 4.1.23, 5.0.32, 5.1.15 changelogs.

Selecting into variables sometimes returned incorrect wrong results.