Bug #23345 | Incorrect check when SELECT INTO + UNION, moreover invalid data | ||
---|---|---|---|
Submitted: | 16 Oct 2006 20:49 | Modified: | 5 Apr 2007 19:45 |
Reporter: | Andrey Hristov | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S1 (Critical) |
Version: | 5.0.32-BK, 4.1, 5.0, 5.1 | OS: | Linux (Linux) |
Assigned to: | Evgeny Potemkin | CPU Architecture: | Any |
[16 Oct 2006 20:49]
Andrey Hristov
[16 Oct 2006 20:55]
Andrey Hristov
There is even more. No SELECT INTO but the last one is executed : -- initialize three variables mysql> set @foobar=42, @foobar2=42, @foobar3=42; Query OK, 0 rows affected (0.00 sec) -- the same value '5' should be loaded into @foobar, @foobar2 and @foobar3 mysql> (select a from t1 order by a limit 2,1 into @foobar) union (select b from t2 order by b limit 2,1 into @foobar2) union (select b from t2 order by b limit 2,1 into @foobar3); Query OK, 1 row affected (0.00 sec) -- but to our suprise, it's not! mysql> select @foobar, @foobar2, @foobar3; +---------+----------+----------+ | @foobar | @foobar2 | @foobar3 | +---------+----------+----------+ | 42 | 42 | 5 | +---------+----------+----------+ 1 row in set (0.01 sec)
[26 Nov 2006 15:44]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described, with 5.0.32-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 Server version: 5.0.32-debug Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> drop table t1; Query OK, 0 rows affected (0.00 sec) mysql> drop table t2; Query OK, 0 rows affected (0.01 sec) mysql> create table t1(a int); Query OK, 0 rows affected (0.01 sec) mysql> insert into t1 values (1), (3), (5); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> create table t2(b int); Query OK, 0 rows affected (0.02 sec) mysql> insert into t2 values (2), (4), (6); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> (select a from t1 limit 1 into @foobar); Query OK, 1 row affected (0.00 sec) mysql> select @foobar; +---------+ | @foobar | +---------+ | 1 | +---------+ 1 row in set (0.00 sec) mysql> (select a from t1 limit 1,1 into @foobar); Query OK, 1 row affected (0.01 sec) mysql> select @foobar; +---------+ | @foobar | +---------+ | 3 | +---------+ 1 row in set (0.00 sec) mysql> (select b from t2 limit 1,1 into @foobar); Query OK, 1 row affected (0.00 sec) mysql> (select a from t1 limit 1 into @foobar) union (select b from t2 limit 1, 1 into -> @foobar2); ERROR 1172 (42000): Result consisted of more than one row mysql> (select a from t1 limit 1 into @foobar) union (select b from t2 limit 1 into -> @foobar2); ERROR 1172 (42000): Result consisted of more than one row mysql> (select a from t1 limit 1 into @foobar) union (select b from t2 limit 1 into -> @foobar); ERROR 1172 (42000): Result consisted of more than one row mysql> (select a from t1 limit 1 into @foobar) union (select a from t1 limit 1 into -> @foobar); Query OK, 1 row affected (0.01 sec) mysql> select @foobar, @foobar2; +---------+----------+ | @foobar | @foobar2 | +---------+----------+ | 1 | 1 | +---------+----------+ 1 row in set (0.00 sec) mysql> (select a from t1 limit 1 into @foobar) union (select a from t1 limit 2, 1 into @foobar2); ERROR 1172 (42000): Result consisted of more than one row mysql> (select a from t1 limit 2,1 into @foobar) union (select a from t1 limit 2,1 into -> @foobar2); Query OK, 1 row affected (0.00 sec) mysql> delete from t2; Query OK, 3 rows affected (0.00 sec) mysql> insert into t2 values (2),(4),(5); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> (select a from t1 order by a limit 2,1) union (select b from t2 order by b limit -> 2,1); +------+ | a | +------+ | 5 | +------+ 1 row in set (0.01 sec) mysql> (select a from t1 order by a limit 2,1 into @foobar) union (select b from -> t2 order by b limit 2,1 into @foobar2); Query OK, 1 row affected (0.01 sec) mysql> set @foobar=42, @foobar2=42, @foobar3=42; Query OK, 0 rows affected (0.00 sec) mysql> (select a from t1 order by a limit 2,1 into @foobar) union (select b from -> t2 order by b limit 2,1 into @foobar2) union (select b from t2 order by b limit -> 2,1 into @foobar3); Query OK, 1 row affected (0.01 sec) mysql> select @foobar, @foobar2, @foobar3; +---------+----------+----------+ | @foobar | @foobar2 | @foobar3 | +---------+----------+----------+ | 42 | 42 | 5 | +---------+----------+----------+ 1 row in set (0.00 sec)
[20 Mar 2007 21:02]
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/22420 ChangeSet@1.2487, 2007-03-20 23:59:41+03:00, evgen@moonbone.local +3 -0 Bug#23345: Wrongly allowed INTO in a non-last select of a UNION. INTO clause can be specified only for the last select of a UNION and it receives the result of the whole query. But it was wrongly allowed in non-last selects of a UNION which leads to a confusing query result. Now INTO allowed only in the last select of a UNION.
[21 Mar 2007 18:56]
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/22526 ChangeSet@1.2487, 2007-03-21 21:54:38+03:00, evgen@moonbone.local +3 -0 Bug#23345: Wrongly allowed INTO in a non-last select of a UNION. INTO clause can be specified only for the last select of a UNION and it receives the result of the whole query. But it was wrongly allowed in non-last selects of a UNION which leads to a confusing query result. Now INTO allowed only in the last select of a UNION.
[23 Mar 2007 13:58]
Alexey Botchkov
Pushed in 5.0.40 and 5.1.18
[5 Apr 2007 19:45]
Paul DuBois
Noted in 5.0.40, 5.1.18 changelogs.