summaryrefslogtreecommitdiff
path: root/mysql-test/r/view.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r/view.result')
-rw-r--r--mysql-test/r/view.result153
1 files changed, 140 insertions, 13 deletions
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 613939bfdf6..a54db7240a4 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -1104,6 +1104,9 @@ select * from t2;
ERROR HY000: Table 't2' was not locked with LOCK TABLES
drop view v1;
drop table t1, t2;
+ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
+unlock tables;
+drop table t1, t2;
create table t1 (a int);
create view v1 as select * from t1 where a < 2 with check option;
insert into v1 values(1);
@@ -1111,8 +1114,8 @@ insert into v1 values(3);
ERROR HY000: CHECK OPTION failed 'test.v1'
insert ignore into v1 values (2),(3),(0);
Warnings:
-Error 1369 CHECK OPTION failed 'test.v1'
-Error 1369 CHECK OPTION failed 'test.v1'
+Warning 1369 CHECK OPTION failed 'test.v1'
+Warning 1369 CHECK OPTION failed 'test.v1'
select * from t1;
a
1
@@ -1125,8 +1128,8 @@ create table t2 (a int);
insert into t2 values (2),(3),(0);
insert ignore into v1 SELECT a from t2;
Warnings:
-Error 1369 CHECK OPTION failed 'test.v1'
-Error 1369 CHECK OPTION failed 'test.v1'
+Warning 1369 CHECK OPTION failed 'test.v1'
+Warning 1369 CHECK OPTION failed 'test.v1'
select * from t1 order by a desc;
a
1
@@ -1148,7 +1151,7 @@ a
update v1 set a=a+1;
update ignore v1,t2 set v1.a=v1.a+1 where v1.a=t2.a;
Warnings:
-Error 1369 CHECK OPTION failed 'test.v1'
+Warning 1369 CHECK OPTION failed 'test.v1'
select * from t1;
a
1
@@ -1182,7 +1185,7 @@ insert into v1 values (1) on duplicate key update a=2;
ERROR HY000: CHECK OPTION failed 'test.v1'
insert ignore into v1 values (1) on duplicate key update a=2;
Warnings:
-Error 1369 CHECK OPTION failed 'test.v1'
+Warning 1369 CHECK OPTION failed 'test.v1'
select * from t1;
a
1
@@ -1283,7 +1286,7 @@ insert ignore into v1 values (6);
ERROR HY000: CHECK OPTION failed 'test.v1'
insert ignore into v1 values (6),(3);
Warnings:
-Error 1369 CHECK OPTION failed 'test.v1'
+Warning 1369 CHECK OPTION failed 'test.v1'
select * from t1;
s1
3
@@ -1328,9 +1331,9 @@ delete from t1;
load data infile '../../std_data/loaddata3.dat' ignore into table v1 fields terminated by '' enclosed by '' ignore 1 lines;
Warnings:
Warning 1366 Incorrect integer value: 'error ' for column 'a' at row 3
-Error 1369 CHECK OPTION failed 'test.v1'
+Warning 1369 CHECK OPTION failed 'test.v1'
Warning 1366 Incorrect integer value: 'wrong end ' for column 'a' at row 4
-Error 1369 CHECK OPTION failed 'test.v1'
+Warning 1369 CHECK OPTION failed 'test.v1'
select * from t1 order by a,b;
a b
1 row 1
@@ -1354,7 +1357,7 @@ concat('|',a,'|') concat('|',b,'|')
delete from t1;
load data infile '../../std_data/loaddata2.dat' ignore into table v1 fields terminated by ',' enclosed by '''';
Warnings:
-Error 1369 CHECK OPTION failed 'test.v1'
+Warning 1369 CHECK OPTION failed 'test.v1'
Warning 1261 Row 2 doesn't contain data for all columns
select concat('|',a,'|'), concat('|',b,'|') from t1;
concat('|',a,'|') concat('|',b,'|')
@@ -2342,11 +2345,11 @@ CREATE VIEW v2 AS SELECT t3.* FROM t1,t3 WHERE t1.a=t3.a;
EXPLAIN SELECT t1.* FROM t1 JOIN t2 WHERE t1.a=t2.a AND t1.b=t2.b AND t1.a=1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref a a 5 const 1 Using where; Using index
-1 SIMPLE t2 ref a a 10 const,test.t1.b 2 Using where; Using index
+1 SIMPLE t2 ref a a 10 const,test.t1.b 1 Using where; Using index
EXPLAIN SELECT * FROM v1 WHERE a=1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref a a 5 const 1 Using where; Using index
-1 SIMPLE t2 ref a a 10 const,test.t1.b 2 Using where; Using index
+1 SIMPLE t2 ref a a 10 const,test.t1.b 1 Using where; Using index
EXPLAIN SELECT * FROM v2 WHERE a=1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref a a 5 const 1 Using where; Using index
@@ -2587,7 +2590,7 @@ CREATE VIEW v1 AS SELECT SQRT(a) my_sqrt FROM t1;
SELECT my_sqrt FROM v1 ORDER BY my_sqrt;
my_sqrt
1
-1.4142135623731
+1.4142135623730951
DROP VIEW v1;
DROP TABLE t1;
CREATE TABLE t1 (id int PRIMARY KEY);
@@ -3699,6 +3702,117 @@ SELECT * FROM v1 IGNORE INDEX (c2) WHERE c2=2;
ERROR 42000: Key 'c2' doesn't exist in table 'v1'
DROP VIEW v1;
DROP TABLE t1;
+#
+# Bug #45806 crash when replacing into a view with a join!
+#
+CREATE TABLE t1(a INT UNIQUE);
+CREATE VIEW v1 AS SELECT t1.a FROM t1, t1 AS a;
+INSERT INTO t1 VALUES (1), (2);
+REPLACE INTO v1(a) SELECT 1 FROM t1,t1 AS c;
+SELECT * FROM v1;
+a
+1
+2
+1
+2
+REPLACE INTO v1(a) SELECT 3 FROM t1,t1 AS c;
+SELECT * FROM v1;
+a
+1
+2
+3
+1
+2
+3
+1
+2
+3
+DELETE FROM t1 WHERE a=3;
+INSERT INTO v1(a) SELECT 1 FROM t1,t1 AS c
+ON DUPLICATE KEY UPDATE `v1`.`a`= 1;
+SELECT * FROM v1;
+a
+1
+2
+1
+2
+CREATE VIEW v2 AS SELECT t1.a FROM t1, v1 AS a;
+REPLACE INTO v2(a) SELECT 1 FROM t1,t1 AS c;
+SELECT * FROM v2;
+a
+1
+2
+1
+2
+1
+2
+1
+2
+REPLACE INTO v2(a) SELECT 3 FROM t1,t1 AS c;
+SELECT * FROM v2;
+a
+1
+2
+3
+1
+2
+3
+1
+2
+3
+1
+2
+3
+1
+2
+3
+1
+2
+3
+1
+2
+3
+1
+2
+3
+1
+2
+3
+INSERT INTO v2(a) SELECT 1 FROM t1,t1 AS c
+ON DUPLICATE KEY UPDATE `v2`.`a`= 1;
+SELECT * FROM v2;
+a
+1
+2
+3
+1
+2
+3
+1
+2
+3
+1
+2
+3
+1
+2
+3
+1
+2
+3
+1
+2
+3
+1
+2
+3
+1
+2
+3
+DROP VIEW v1;
+DROP VIEW v2;
+DROP TABLE t1;
+# -- End of test case for Bug#45806
# -----------------------------------------------------------------
# -- Bug#40825: Error 1356 while selecting from a view
# -- with a "HAVING" clause though query works
@@ -3847,3 +3961,16 @@ DROP TABLE t1;
# -----------------------------------------------------------------
# -- End of 5.1 tests.
# -----------------------------------------------------------------
+drop table if exists t_9801;
+drop view if exists v_9801;
+create table t_9801 (s1 int);
+create view v_9801 as
+select sum(s1) from t_9801 with check option;
+ERROR HY000: CHECK OPTION on non-updatable view 'test.v_9801'
+create view v_9801 as
+select sum(s1) from t_9801 group by s1 with check option;
+ERROR HY000: CHECK OPTION on non-updatable view 'test.v_9801'
+create view v_9801 as
+select sum(s1) from t_9801 group by s1 with rollup with check option;
+ERROR HY000: CHECK OPTION on non-updatable view 'test.v_9801'
+drop table t_9801;