MySQL Bugs: #25548: Behavior change in INSERT INTO SELECT DISTINCT @var between 5.0.18 and 5.0.27

Bug #25548	Behavior change in INSERT INTO SELECT DISTINCT @var between 5.0.18 and 5.0.27
Submitted:	11 Jan 2007 14:47	Modified:	19 Jan 2007 15:13
Reporter:	Maarten Meijer	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0.34-BK, 5.0.27, 5.1	OS:	Linux (Linux, Mac OSX Intel)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	distinct, insert, INTO, regression, SELECT

Description:
I use MySQL to create configurations of technical products, based on rules. This involves calculations and storage of intermediate results.
I used 3.x and 4.x on Mac OSX PowerPC AND 5.0.18 XAMPP on Windows XP without problems plus various deployments. I recently bought a dual dual Intel Mac and installed 5.0.24a directly from the site.

In the sequence given below the results differed between MySQL <up to 5.0.18> and <5.0.24a>

Up to at least 5.0.18:
mysql>  insert into area select distinct @area := length * width from rooms;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from area;
+-------+
| area  |
+-------+
| 72.00 | 
| 60.00 | 
| 16.00 | 
+-------+
3 rows in set (0.00 sec)

So the INSERT SELECT inserts into area the same values as the SELECT:
mysql> select distinct @area := length * width from rooms;
+-------------------------+
| @area := length * width |
+-------------------------+
|                   72.00 | 
|                   60.00 | 
|                   16.00 | 
+-------------------------+
3 rows in set (0.00 sec)

In 5.0.24a the results differ!
mysql> insert into area select distinct @area := length * width from rooms;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from area;
+-------+
| area  |
+-------+
| 16.00 | 
| 16.00 | 
| 16.00 | 
+-------+
3 rows in set (0.00 sec)

The difference is in the handling of the @area variable.
It is not obvious from this example, but I NEED that variable for further calculations with JOIN's etc that would make demonstrating this changed behaviour muddled. 

How to repeat:
Using 5.0.24a:

mysql> create table rooms ( width double(5,2) default 0, length double(5,2) default 0, location int );
Query OK, 0 rows affected (0.00 sec)

mysql> insert into rooms values ( 12.0 ,6.0, 1), (15.0, 4.0, 1), (18.0, 4.0, 3), (4.0, 4.0, 2);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select @area := length * width from rooms;
+-------------------------+
| @area := length * width |
+-------------------------+
|                   72.00 | 
|                   60.00 | 
|                   72.00 | 
|                   16.00 | 
+-------------------------+
4 rows in set (0.00 sec)

mysql> select distinct @area := length * width from rooms;
+-------------------------+
| @area := length * width |
+-------------------------+
|                   72.00 | 
|                   60.00 | 
|                   16.00 | 
+-------------------------+
3 rows in set (0.00 sec)

mysql> create table area ( area double(5,2));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into area select @area := length * width from rooms;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from area;
+-------+
| area  |
+-------+
| 72.00 | 
| 60.00 | 
| 72.00 | 
| 16.00 | 
+-------+
4 rows in set (0.00 sec)

mysql> select distinct( area ) from area;
+-------+
| area  |
+-------+
| 72.00 | 
| 60.00 | 
| 16.00 | 
+-------+
3 rows in set (0.00 sec)

mysql> truncate area;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into area select distinct @area := length * width from rooms;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from area;
+-------+
| area  |
+-------+
| 16.00 | 
| 16.00 | 
| 16.00 | 
+-------+
3 rows in set (0.00 sec)

mysql> truncate area;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into area select distinct length * width from rooms;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from area;
+-------+
| area  |
+-------+
| 72.00 | 
| 60.00 | 
| 16.00 | 
+-------+
3 rows in set (0.00 sec)

Suggested fix:
Revert the behaviour to previous versions, of give an option to bypass this new optimization.
Or provide a workaround or different version to use.

The sequence as a script:

use test;
drop table if exists rooms, area;
create table rooms ( width double(5,2) default 0, length double(5,2) default 0, location int );
insert into rooms values ( 12.0 ,6.0, 1), (15.0, 4.0, 1), (18.0, 4.0, 3), (4.0, 4.0, 2);
select @area := length * width from rooms;
create table area ( area double(5,2));
insert into area select @area := length * width from rooms;
select * from area;
select distinct( area ) from area;
truncate area;
insert into area select distinct @area := length * width from rooms;
select * from area;

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.27 (we have binaries on site), and inform about the results.

I installed 5.0.27 and this new behaviour remains.

I changed the version to most recent as it's not just 5.0.24a

The script that demonstrates the different begaviours

Attachment: bugreport.sql (application/octet-stream, text), 536 bytes.

Thank you for the report.

Verified as described on Linux using last BK sources. Version 5.1 is affected too

Workaround: avoid using DISTINCT

Verified just as described with latest 5.0.34-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 2
Server version: 5.0.34-debug-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table if exists rooms, area;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> create table rooms ( width double(5,2) default 0, length double(5,2) default 0, location int );
Query OK, 0 rows affected (0.01 sec)

emysql> insert into rooms values ( 12.0 ,6.0, 1), (15.0, 4.0, 1), (18.0, 4.0, 3), (4.0, 4.0, 2);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

smysql> select @area := length * width from rooms;
c+-------------------------+
| @area := length * width |
+-------------------------+
|                   72.00 |
|                   60.00 |
|                   72.00 |
|                   16.00 |
+-------------------------+
4 rows in set (0.00 sec)

mysql> create table area ( area double(5,2));
iQuery OK, 0 rows affected (0.01 sec)

mysql> insert into area select @area := length * width from rooms;
seQuery OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from area;
+-------+
| area  |
+-------+
| 72.00 |
| 60.00 |
| 72.00 |
| 16.00 |
+-------+
4 rows in set (0.00 sec)

mysql> select distinct( area ) from area;
t+-------+
| area  |
+-------+
| 72.00 |
| 60.00 |
| 16.00 |
+-------+
3 rows in set (0.00 sec)

mysql> truncate area;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into area select distinct @area := length * width from rooms;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from area;
+-------+
| area  |
+-------+
| 16.00 |
| 16.00 |
| 16.00 |
+-------+
3 rows in set (0.01 sec)

And it indeed worked differently in 5.0.18:

...
mysql> truncate area;
Query OK, 4 rows affected (0.09 sec)

mysql> insert into area select distinct @area := length * width from rooms;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from area;
+-------+
| area  |
+-------+
| 72.00 |
| 60.00 |
| 16.00 |
+-------+
3 rows in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.18-nt |
+-----------+
1 row in set (0.00 sec)

I think, it is a bug.

Can't do without DISTINCT, it will give me to many duplicate solutions :-(

FYI see it at work with MySQL 3.x at http://fold.slisylvania.com/ or http://www.fold1.com/

Can I download 5.0.18 somewhere to get back to work while this is fixed?

There is archive page: http://downloads.mysql.com/archives.php?p=mysql-5.0

Thanks Sveta!

I found it. And for all those who want to do the same on Mac OSX:
- The intel version for 5.0.18 is NOT under MacOSX but under Other:
http://downloads.mysql.com/archives.php?p=mysql-5.0&o=other
- when you want to downgrade a MySQL installation, do a mysqldump of your data first and then
>sudo rm -R /usr/local/mysql*
>sudo rm -R /Library/Receipts/mysql-*

The second is necessary as the MySQL installer check for a higher receipt first, only cost me another hour! :-(

The good news is that my test case now returns the correct answer again and I can continue developing! Thanks everybody for the rapid replies and help!

Maarten

Duplicate of bug#16861. Fixed in additional fix.