MySQL Bugs: #25123: insert into view on dup key, allows cross table reference

Bug #25123	insert into view on dup key, allows cross table reference
Submitted:	17 Dec 2006 19:28	Modified:	8 Feb 2007 18:40
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.0.30/5.1BK	OS:	FreeBSD (freebsd)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any
Tags:	VIEW

Description:
inserts into a join-based views are only allowed/possible, if only on of the undderlaying tables is used.

This check is not performed for "on duplicate key" clauses.

both updates below willbe executed, without error. the 2nd one (hitting the duplicate) will also report 2 rows affected.

Except for the "rows affected" count, the (invalid) update is silently ignored. The 2nd table is not updated.

How to repeat:
drop table if exists b1; drop table if exists b2; drop view if exists v1;

create table b1 (a int, b int, unique (a)); insert into b1 values (1,11),(2,22);
create table b2 (c int, d int); insert into b2 values (1,10),(3,30);

create view v1 as select * from b1, b2 where a=c;

insert into v1 (a)  values (5) on duplicate key update d = d+1;
insert into v1 (a)  values (2) on duplicate key update d = d+1;

select * from b2;

 

Suggested fix:
queries like this should produce an error like:

ERROR 1393 (HY000): Can not modify more than one base table through a join view 'db.v1'

Just seen, hittng the dup for a=2, cannot modify d, as there is no row in table b2

However hitting a=1 as duplicate, which has a row in b2, does neither update b2. 
 insert into v1 (a)  values (1) on duplicate key update d = d+1;

As I said, I dont actually espect it to update b2 (even multi-table updates are posssible.)

Reading from b2 and updating b1 does work partially (but seems to ignore te where
 insert into v1 (a)  values (1) on duplicate key update b = d+1000;

I does indeed read from b2, but not always the correct value.
Try:
 insert into v1 (a)  values (7) on duplicate key update b = d+1000;
 insert into v1 (a)  values (7) on duplicate key update b = d+1000;
 select * from b1; # b does have a value 1010 or 1030 (the later for me)
The 2nd should (if at all allowed) read a null for d. it returns one of the numeric values from the column
(But then, it is not documented, that you can read b2 in this way at all)

Thank you for the bug report.

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/17365

ChangeSet@1.2351, 2006-12-24 00:18:42+03:00, evgen@moonbone.local +3 -0
  Bug#25123: ON DUPLICATE KEY clause allows fields not from the insert table.
  
  When inserting into a join-based view update fields from the ON DUPLICATE
  KEY UPDATE wasn't checked to be from the table being inserted into and was
  silently ignored.
  
  The new check_view_single_update() function is added. It performs check for
  insert/update fields being from the same single table of the view.

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/18535

ChangeSet@1.2389, 2007-01-22 15:14:38+03:00, evgen@moonbone.local +3 -0
  Bug#25123: ON DUPLICATE KEY clause allows fields not from the insert table.
  
  When inserting into a join-based view the update fields from the ON DUPLICATE
  KEY UPDATE wasn't checked to be from the table being inserted into and were
  silently ignored.
  
  The new check_view_single_update() function is added to check that
  insert/update fields are being from the same single table of the view.

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/18575

ChangeSet@1.2390, 2007-01-22 22:08:51+03:00, evgen@moonbone.local +1 -0
  sql_prepare.cc:
    Post fix for bug#25123.

The fix has been pushed into 5.0.36, 5.1.16-beta main trees.

Noted in 5.0.36, 5.1.16 changelogs.

Use of ON DUPLICATE KEY UPDATE defeated the usual restriction against
inserting into a join-based view unless only one of the underlying
tables is used.