MySQL Bugs: #23345: Incorrect check when SELECT INTO + UNION, moreover invalid data

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:	None
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

Description:
Using SELECT INTO + UNION gives an error that result set is not one row, but it actually is. Apparently the check for one row result set is made for the UNION itself and not for every subquery in the statement glued with UNION.

mysql> select version();
+-------------------------------+
| version()                     |
+-------------------------------+
| 4.1.22-valgrind-max-debug-log |
+-------------------------------+
1 row in set (0.00 sec)

-- create first table 
mysql> create table t1(a int);
Query OK, 0 rows affected (0.07 sec)

-- fill with values
mysql> insert into t1 values (1), (3), (5);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

-- create second table
mysql> create table t2(b int);
Query OK, 0 rows affected (0.07 sec)

-- fill with values
mysql> insert into t2 values (2), (4), (6);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

-- try simple SELECT INTO
mysql> (select a from t1 limit 1 into @foobar);
Query OK, 1 row affected (0.00 sec)

-- check the value
mysql> select @foobar;
+---------+
| @foobar |
+---------+
| 1       |
+---------+
1 row in set (0.00 sec)

-- another simple SELECT INTO, this time start from offset 1
mysql> (select a from t1 limit 1,1 into @foobar);
Query OK, 1 row affected (0.00 sec)

-- check result
mysql> select @foobar;
+---------+
| @foobar |
+---------+
| 3       |
+---------+
1 row in set (0.03 sec)

-- next round for table t2
mysql> (select b from t2 limit 1,1 into @foobar);
Query OK, 1 row affected (0.00 sec)

-- it works
mysql> select @foobar;
+---------+
| @foobar |
+---------+
| 4       |
+---------+
1 row in set (0.00 sec)

-- but now..try with union - error - two different tables
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

-- another try, two different tables simpler LIMIT clause, two diff vars
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

-- and again but the same variable @foobar, no luck!
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

-- let's try the same query two times and the same variable - it works
mysql> (select a from t1 limit 1 into @foobar) union (select a from t1 limit 1 into @foobar);
Query OK, 1 row affected (0.00 sec)

-- one more round but with different variables, it works!
mysql> (select a from t1 limit 1 into @foobar) union (select a from t1 limit 1 into @foobar2);
Query OK, 1 row affected (0.01 sec)

mysql> select @foobar, @foobar2;
+---------+----------+
| @foobar | @foobar2 |
+---------+----------+
| 1       | 1        |
+---------+----------+
1 row in set (0.00 sec)

-- Hope! One table, different offsets - no way :(
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

-- Similar query, but have the same offset in both subqueries - works!
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)

-- Ok, let's have common value in both tables, to prove that the check is made on the most upper level - the UNION, instead for every subquery
mysql> delete from t2;
Query OK, 3 rows affected (0.00 sec)

-- 5 is the common value
mysql> insert into t2 values (2),(4),(5);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

-- Ok, when two times 5 is selected from both tables the union has one row
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.00 sec)

-- In this case also SELECT INTO works
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.00 sec)

How to repeat:
select version();
create table t1(a int);
insert into t1 values (1), (3), (5);
create table t2(b int);
insert into t2 values (2), (4), (6);
(select a from t1 limit 1 into @foobar);
select @foobar;
(select a from t1 limit 1,1 into @foobar);
select @foobar;
(select b from t2 limit 1,1 into @foobar);
(select a from t1 limit 1 into @foobar) union (select b from t2 limit 1,1 into @foobar2);
(select a from t1 limit 1 into @foobar) union (select b from t2 limit 1 into @foobar2);
(select a from t1 limit 1 into @foobar) union (select b from t2 limit 1 into @foobar);
(select a from t1 limit 1 into @foobar) union (select a from t1 limit 1 into @foobar);
select @foobar, @foobar2;
(select a from t1 limit 1 into @foobar) union (select a from t1 limit 2.1 into @foobar2);
(select a from t1 limit 2,1 into @foobar) union (select a from t1 limit 2,1 into @foobar2);
delete from t2;
insert into t2 values (2),(4),(5);
(select a from t1 order by a limit 2,1) union (select b from t2 order by b limit 2,1);
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);

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)

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)

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.

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.

Pushed in 5.0.40 and 5.1.18

Noted in 5.0.40, 5.1.18 changelogs.