diff options
Diffstat (limited to 'mysql-test/main/view.result')
| -rw-r--r-- | mysql-test/main/view.result | 50 |
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 +# |