Merging upstream version 1:10.11.7.

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 50 insertions, 0 deletions

diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result
index 0f47c47b..9cab1b5f 100644
--- a/mysql-test/main/view.result
+++ b/mysql-test/main/view.result
@@ -4422,12 +4422,14 @@ SELECT * FROM v1 WHERE a > 'JJ' OR a <> 0 AND a = 'VV';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	range	a	a	13	NULL	4	100.00	Using where; Using index
 Warnings:
+Note	1105	Cannot use key `a` part[0] for lookup: `test`.`t1`.`a` of type `varchar` < "0" of type `int`
 Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 'JJ' or `test`.`t1`.`a` = 'VV' and `test`.`t1`.`a` <> 0
 EXPLAIN EXTENDED
 SELECT * FROM t1 WHERE a > 'JJ' OR a <> 0 AND a = 'VV';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	range	a	a	13	NULL	4	100.00	Using where; Using index
 Warnings:
+Note	1105	Cannot use key `a` part[0] for lookup: `test`.`t1`.`a` of type `varchar` < "0" of type `int`
 Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 'JJ' or `test`.`t1`.`a` = 'VV' and `test`.`t1`.`a` <> 0
 # t1 and v1 should return the same result set
 SELECT * FROM v1 WHERE a > 'JJ' OR a AND a = 'VV';
@@ -4448,12 +4450,14 @@ SELECT * FROM v1 WHERE a > 'JJ' OR a AND a = 'VV';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	range	a	a	13	NULL	4	100.00	Using where; Using index
 Warnings:
+Note	1105	Cannot use key `a` part[0] for lookup: `test`.`t1`.`a` of type `varchar` < "0" of type `int`
 Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 'JJ' or `test`.`t1`.`a` = 'VV' and `test`.`t1`.`a` <> 0
 EXPLAIN EXTENDED
 SELECT * FROM t1 WHERE a > 'JJ' OR a AND a = 'VV';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	range	a	a	13	NULL	4	100.00	Using where; Using index
 Warnings:
+Note	1105	Cannot use key `a` part[0] for lookup: `test`.`t1`.`a` of type `varchar` < "0" of type `int`
 Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 'JJ' or `test`.`t1`.`a` = 'VV' and `test`.`t1`.`a` <> 0
 DROP VIEW v1;
 DROP TABLE t1;
@@ -7025,3 +7029,49 @@ DROP TABLE t1, t2;
 #
 # End of 10.6 tests
 #
+#
+# MDEV-29587: Allowing insert into a view with columns that
+# are not part the table
+#
+# view with 2 the same fields
+CREATE TABLE table1 (x INT);
+CREATE VIEW view1 AS SELECT x, x as x1 FROM table1;
+INSERT INTO view1(x) VALUES (1);
+INSERT INTO view1(x1) VALUES (1);
+INSERT INTO view1(x1,x) VALUES (1,1);
+ERROR HY000: The target table view1 of the INSERT is not insertable-into
+DROP VIEW view1;
+DROP TABLE table1;
+# view with a field and expression over the field
+CREATE TABLE table1 (x INT);
+CREATE VIEW view1 AS SELECT x, x + 1 as x1 FROM table1;
+INSERT INTO view1(x) VALUES (1);
+INSERT INTO view1(x1) VALUES (1);
+ERROR HY000: The target table view1 of the INSERT is not insertable-into
+INSERT INTO view1(x1,x) VALUES (1,1);
+ERROR HY000: The target table view1 of the INSERT is not insertable-into
+DROP VIEW view1;
+DROP TABLE table1;
+# view with a field and collation expression over the field
+CREATE TABLE table1 (x char(20));
+CREATE VIEW view1 AS SELECT x, x collate latin1_german1_ci as x1 FROM table1;
+INSERT INTO view1(x) VALUES ("ua");
+# we can insert in the field with collation
+INSERT INTO view1(x1) VALUES ("ua");
+INSERT INTO view1(x1,x) VALUES ("ua","ua");
+ERROR HY000: The target table view1 of the INSERT is not insertable-into
+DROP VIEW view1;
+DROP TABLE table1;
+# view with a field and expression over other field
+CREATE TABLE table1 (x INT, y INT);
+CREATE VIEW view1 AS SELECT x, y + 1 as x1 FROM table1;
+INSERT INTO view1(x) VALUES (1);
+INSERT INTO view1(x1) VALUES (1);
+ERROR HY000: The target table view1 of the INSERT is not insertable-into
+INSERT INTO view1(x1,x) VALUES (1,1);
+ERROR HY000: The target table view1 of the INSERT is not insertable-into
+DROP VIEW view1;
+DROP TABLE table1;
+#
+# End of 10.11 test
+#