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: | |
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
[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.