MySQL Bugs: #41760: Inserting into multiple-table views is not working

Bug #41760	Inserting into multiple-table views is not working
Submitted:	27 Dec 2008 2:01	Modified:	20 Oct 2009 7:46
Reporter:	Michael Widenius	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.0 and up	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Inserting into multiple-table views is not working

During insert, we are not reading the rows in a referring table but instead using the last read row that happens to be in table->record[0].

I have added a part fix for this into the MySQL-maria tree, that fixes the problem with insert. However, CHECK OPTION is not yet fixed.

How to repeat:
Problem 1 (from insert.test)

create table t1 (f1 int unique, f2 int);
create table t2 (f3 int, f4 int);
create view v1 as select * from t1, t2 where f1= f3;
insert into t1 values (1,11), (2,22), (3, NULL);
insert into t2 values (1,12), (2,24);
insert into v1 (f1) values (3) on duplicate key update f1= f3 + 10;
select * from t1;

This returns a value that is depending on the last inserted row in t2.
You will get another value for the select if you change the insert into t2 to:
insert into t2 values (1,12), (2,24), (9,99);

Problem 2 (from subselect.test)

CREATE TABLE t1 (id int);
CREATE TABLE t2 (id int, c int);
INSERT INTO t1 (id) VALUES (1);
INSERT INTO t2 (id) VALUES (1);
INSERT INTO t1 (id) VALUES (1);
INSERT INTO t2 (id) VALUES (1);
CREATE VIEW v1 AS
  SELECT t2.c AS c FROM t1, t2
    WHERE t1.id=t2.id AND 1 IN (SELECT id FROM t1) WITH CHECK OPTION;
UPDATE v1 SET c=1;
CREATE VIEW v2 (a,b) AS
  SELECT t2.id, t2.c AS c FROM t1, t2
    WHERE t1.id=t2.id AND 1 IN (SELECT id FROM t1) WITH CHECK OPTION;

INSERT INTO v2(a,b) VALUES (1,2);

During the above insert, we are checking the WHERE with the old values of t1->record[0].  The last statement will not anymore succeed if you add:
INSERT INTO t1 values(5);
to the list of inserts.

Suggested fix:
For problem 1) there is a solution in the Maria tree
For problem 2), we should for now disable inserts for multi-tables views with CHECK OPTION

Monty's fixes about this, which are only in 5.1-maria (null-merged to 6.0-maria for now, left for the runtime team to evaluate/re-use), are the changes to non-Maria code and test files described in:
http://lists2.mysql.com/maria/370
http://lists2.mysql.com/maria/380

Pushed into 6.0.10-alpha (revid:serg@mysql.com-20090217113558-vpsqsyjule7nz0gk) (version source revid:guilhem@mysql.com-20090213163054-rsg204z5qzcekbfe) (merge vers: 6.0.10-alpha) (pib:6)

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

2906 Evgeny Potemkin	2009-10-20 [merge]
      Auto-merged fix for the bug#41760.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091031081410-qkxmjsdzjmj840aq) (version source revid:alik@sun.com-20091022131451-3iy8pr9lpv0ypvrm) (merge vers: 6.0.14-alpha) (pib:13)

Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:mikael@mysql.com-20091102100915-a2nbfxaqprpgptfw) (merge vers: 5.5.0-beta) (pib:13)