[MDEV-5344] LEFT OUTER JOIN table data is lost in ON DUPLICATE KEY UPDATE section Created: 2013-11-26  Updated: 2014-01-29  Resolved: 2013-11-27

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.3.12, 5.5.33a, 5.5.34, 10.0.6
Fix Version/s: 5.5.35, 10.0.7, 5.3.13

Type: Bug Priority: Major
Reporter: Tim Ruhl Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: None
Environment:

CentOS 64 bits


 Description   

The following sequence of statements gives the wrong result:

drop table if exists t1;
 
create table t1 (
 
	id int(10) unsigned NOT NULL DEFAULT '0',
 
	v int(10) unsigned DEFAULT '0',
 
	PRIMARY KEY (id)
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
 
 
 
drop table if exists t2;
 
create table t2 (
 
	id int(10) unsigned NOT NULL DEFAULT '0',
 
	PRIMARY KEY (id)
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
 
 
 
drop table if exists t3;
 
create table t3 (
 
	id int(10) unsigned NOT NULL DEFAULT '0',
 
	v int(10) unsigned DEFAULT '0',
 
	PRIMARY KEY (id)
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
 
 
 
 
 
insert into t1 values (1, 10), (2, 10);
 
insert into t2 values (1), (2);
 
insert into t3 values (1, 20);
 
 
 
insert into t1 
select t2.id, 5 from t2 LEFT OUTER JOIN t3 ON t2.id = t3.id
 
on duplicate key update t1.v = t3.v;
 
 
 
select * from t1;

The result is:

MariaDB [tim]> select * from t1;
 
+----+------+
 
| id | v    |
 
+----+------+
 
|  1 | NULL |
 
|  2 | NULL |
 
+----+------+
 
2 rows in set (0.00 sec)

While on MySQL 5.1.41 the result is as expected:

mysql> select * from t1;
 
+----+------+
 
| id | v    |
 
+----+------+
 
|  1 |   20 |
 
|  2 | NULL |
 
+----+------+
 
2 rows in set (0.00 sec)

It seems that the value t3.v is always NULL in the ON DUPLICATE KEY UPDATE section, instead of the value returned from the left outer join.

With a regular JOIN the statements work as expected and give the same result as on MySQL (but in that case we loose the update on t1 record with id 2)

 Comments   
Comment by Elena Stepanova [ 2013-11-26 ]

Thanks for reporting this.
For a workaround until the problem is fixed, you can try setting

SET optimizer_switch = 'table_elimination=off'.

Comment by Sergei Petrunia [ 2013-11-26 ]

Elenst, you're right about table elimination being the cause of the problem. If I try to debug the INSERT .. SELECT statement, I can see that table t3 is eliminated. This is incorrect, because it is used in the ON DUPLICATE KEY UPDATE clause.

Comment by Sergei Petrunia [ 2013-11-27 ]

Fix pushed into 5.3 tree. It will be merged into 5.5 together with other fixes.

Tim, thanks for taking time to report this.

Comment by Daniel Bartholomew [ 2014-01-29 ]

http://bazaar.launchpad.net/~maria-captains/maria/5.5/revision/3991

http://bazaar.launchpad.net/~maria-captains/maria/5.5/revision/2502.567.172

Generated at Thu Feb 08 07:03:33 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.