MySQL Bugs: #11298: insert into select from VIEW produces incorrect result when using ORDER BY

Bug #11298	insert into select from VIEW produces incorrect result when using ORDER BY
Submitted:	13 Jun 2005 20:07	Modified:	22 Jun 2005 0:17
Reporter:	Stefan Bähring	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.6	OS:	Linux (linux)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
In my environment a INSERT INTO SELECT * FROM View Where ... ORDER BY ... 
produces incorrect result sets.

How to repeat:
CREATE TABLE `a` (
  `id_a` smallint(5) unsigned NOT NULL,
  `value_a` varchar(10) NOT NULL,
  UNIQUE KEY `id_a` (`id_a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `a` VALUES (1, 'BMW');
INSERT INTO `a` VALUES (2, 'Mercedes');
INSERT INTO `a` VALUES (3, 'AUDI');
INSERT INTO `a` VALUES (4, 'Alfa Romeo');
INSERT INTO `a` VALUES (5, 'Ferrari');

CREATE TABLE `b` (
  `id_b` smallint(5) unsigned NOT NULL,
  `value_b` varchar(10) NOT NULL,
  UNIQUE KEY `id_b` (`id_b`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `b` VALUES (1, 'red');
INSERT INTO `b` VALUES (2, 'blue');
INSERT INTO `b` VALUES (3, 'black');
INSERT INTO `b` VALUES (4, 'black');
INSERT INTO `b` VALUES (5, 'black');

CREATE TABLE `c` (
  `id_c` smallint(5) unsigned NOT NULL,
  `value_c` varchar(10) NOT NULL,
  UNIQUE KEY `id_b` (`id_c`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `c` VALUES (1, '170PS');
INSERT INTO `c` VALUES (2, '200PS');
INSERT INTO `c` VALUES (3, '150PS');
INSERT INTO `c` VALUES (4, '240PS');
INSERT INTO `c` VALUES (5, '450PS');

CREATE TABLE `t_car` (
  `id` smallint(5) unsigned NOT NULL,
  `cart` varchar(20) NOT NULL,
  `color` varchar(20) NOT NULL,
  `power` varchar(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--------------------------------------------------------
I created a view which should join the data of 3 different tables a,b,c. That is working fine.

---------------------------------------------------------
CREATE VIEW v_test SELECT a.id_a, a.value_a, b.value_b, c.value_c
FROM a
LEFT JOIN b ON ( a.id_a = b.id_b ) 
LEFT JOIN c ON ( a.id_a = c.id_c );

Now I tried to do a 
-----------------------
INSERT INTO t_car SELECT * from v_test where value_b='black' ORDER BY value_c;
--------------------------
What I get is the same result row 3 times. I should get 3 different rows:
-------------------
SELECT a.id_a, a.value_a, b.value_b, c.value_c FROM a LEFT JOIN b ON ( a.id_a = b.id_b ) LEFT JOIN c ON ( a.id_a = c.id_c ); where value_b='black' ORDER BY value_c;

It is working if I omit the View and do the join directly. It also did not happen if I do the INSERT INTO Table SELECT without ORDER BY.

What I see is a incompatibility between
 INSERT INTO Table SELECT FROM View and the ORDER BY clause. 

Suggested fix:
no idea

mysql> SELECT * from v_test where value_b='black' ORDER BY value_c;
+------+------------+---------+---------+
| id_a | value_a    | value_b | value_c |
+------+------------+---------+---------+
|    3 | AUDI       | black   | 150PS   |
|    4 | Alfa Romeo | black   | 240PS   |
|    5 | Ferrari    | black   | 450PS   |
+------+------------+---------+---------+
3 rows in set (0.00 sec)

mysql> insert into t_car SELECT * from v_test where value_b='black' ORDER BY value_c;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t_car;
+----+---------+-------+-------+
| id | cart    | color | power |
+----+---------+-------+-------+
|  5 | Ferrari | black | 450PS |
|  5 | Ferrari | black | 450PS |
|  5 | Ferrari | black | 450PS |
+----+---------+-------+-------+
3 rows in set (0.00 sec)

Insert were inserting data from last record fetched instead of inserting from 
temporary table.

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/internals/26152

Fixed in 5.0.8, cset 1.1976

Documented in 5.0.8 changelog:

<listitem><para>INSERT into SELECT from VIEW produces incorrect result when using ORDER BY. (Bug #11298)</para></listitem>