MySQL Bugs: #2996: update on left join can change non matching record in second table.

Bug #2996	update on left join can change non matching record in second table.
Submitted:	27 Feb 2004 11:57	Modified:	11 Mar 2004 12:23
Reporter:	Joseph Martin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.18	OS:	Linux (Linux 2.4.25 (Slackware 9.1+))
Assigned to:		CPU Architecture:	Any

Description:
This may be related to the "join ordering" problem mentioned by [name withheld]. I was trying updates on joins, and I found this with an update on a left join.

Example update:

update parent left join child2 on p_id = c2_p_id set c2_note = 'hhhh' where p_id = 3;

With no matching row in child2, I got different behavior depending on whether there was only one record in the child2 table or more than one:

With only one record in child2, which didn't match the record in parent with p_id = 3, the update gave a status of one record matched and changed: it changed the record in child2 that *did not* match.

The select:

select * from parent left join child2 on p_id = c2_p_id where p_id = 3;

still shows NULLs for the fields from child2.

If there are two or three records in child2, all not matching, then the update gives a result of no records updated, which is expected.

If a further condition is added to the update's where condition:

update parent left join child2 on p_id = c2_p_id set c2_note = 'hhhh' where p_id = 3 and c2_p_id is not null;

then 0 rows match and none are changed, as expected.

The wrong update still happens if I delete records in child2 leaving only one record: the first update above updates the one non-matching record (claiming one matched), and adding more non-matching rows again makes the update change no records.

How to repeat:
CREATE TABLE `parent` (
  `p_id` int(10) unsigned NOT NULL auto_increment,
  `p_code` varchar(20) NOT NULL default '',
  `p_created` datetime NOT NULL default '0000-00-00 00:00:00',
  `p_active` tinyint(1) unsigned NOT NULL default '1',
  PRIMARY KEY  (`p_id`)
) TYPE=MyISAM COMMENT='test parent table';

CREATE TABLE `child2` (
  `c2_id` int(10) unsigned NOT NULL auto_increment,
  `c2_p_id` int(10) unsigned NOT NULL default '0',
  `c2_note` text NOT NULL,
  `c2_created` datetime NOT NULL default '0000-00-00 00:00:00',
  `c2_active` tinyint(1) unsigned NOT NULL default '1',
  PRIMARY KEY  (`c2_id`),
  KEY `c2_p_id` (`c2_p_id`)
) TYPE=MyISAM COMMENT='test child table 2';

insert into parent values (0,'A01-Comp',now(),1);
insert into parent values (0,'B01-Comp',now(),1);

insert into child2 values (0,1,'A Note',now(),1);

update parent left join child2 on p_id = c2_p_id set c2_note = 'asdf-1' where p_id = 2; # this changes the row with c2_p_id = 1

# note that using a different field in parent doesn't do this:
update parent left join child2 on p_id = c2_p_id set c2_note = 'asdf-2' where p_code = 'B01-Comp'; # this doesn't change any row

# note that adding another non-matching row to child2 changed the result.
insert into child2 values (0,1,'Another Note',now(),1);

# this now changes no record in child2
update parent left join child2 on p_id = c2_p_id set c2_note = 'asdf-3' where p_id = 2;

Suggested fix:
A workarouund is to include the test on the id field of child2 being not null.

I looked at the code in sql_update.cc around line 280, but I haven't tried to add any tracing to it to see how the flow changes.

Verified against 4.0.19.  Thank you for the test case.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Fix will come in 4.0.19

Fixed in 4.0.19 with the following patch:

===== /mnt/work/mysql-4.0/sql/sql_select.cc 1.276 vs edited =====
*** /tmp/sql_select.cc-1.276-28277      Wed Mar 10 11:50:15 2004
--- edited//mnt/work/mysql-4.0/sql/sql_select.cc        Thu Mar 11 16:39:27 2004
***************
*** 4897,4904 ****
    if (tab->on_expr && !table->null_row)
    {
      if ((table->null_row= test(tab->on_expr->val_int() == 0)))
!       empty_record(table);
!     }
    if (!table->null_row)
      table->maybe_null=0;
    DBUG_RETURN(0);
--- 4897,4904 ----
    if (tab->on_expr && !table->null_row)
    {
      if ((table->null_row= test(tab->on_expr->val_int() == 0)))
!       mark_as_null_row(table);  
!   }
    if (!table->null_row)
      table->maybe_null=0;
    DBUG_RETURN(0);

ChangeSet info:
1.1745: +3 -0 = 10639