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.result290
1 files changed, 267 insertions, 23 deletions
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 2d89c66a226..966b88f0018 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -841,7 +841,7 @@ ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function
show table status;
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
t1 MyISAM 10 Fixed 0 0 0 # 1024 0 NULL # # NULL latin1_swedish_ci NULL
-v1 NULL NULL NULL NULL NULL NULL # NULL NULL NULL # # NULL NULL NULL NULL View 'test.v1' references invalid table(s) or column(s) or function(s) or define
+v1 NULL NULL NULL NULL NULL NULL # NULL NULL NULL # # NULL NULL NULL NULL View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Warnings:
Warning 1356 View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
drop view v1;
@@ -1106,6 +1106,7 @@ select * from v1;
a
select * from t2;
ERROR HY000: Table 't2' was not locked with LOCK TABLES
+unlock tables;
drop view v1;
drop table t1, t2;
create table t1 (a int);
@@ -1115,8 +1116,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
@@ -1129,8 +1130,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
@@ -1152,7 +1153,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
@@ -1186,7 +1187,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
@@ -1287,7 +1288,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
@@ -1332,9 +1333,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
@@ -1358,7 +1359,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,'|')
@@ -2089,7 +2090,7 @@ CREATE TABLE t1 ( bug_table_seq INTEGER NOT NULL);
CREATE OR REPLACE VIEW v1 AS SELECT * from t1;
DROP PROCEDURE IF EXISTS p1;
Warnings:
-Note 1305 PROCEDURE p1 does not exist
+Note 1305 PROCEDURE test.p1 does not exist
CREATE PROCEDURE p1 ( )
BEGIN
DO (SELECT @next := IFNULL(max(bug_table_seq),0) + 1 FROM v1);
@@ -2591,7 +2592,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);
@@ -3689,7 +3690,7 @@ c1 c2
2 2
CREATE VIEW v1 AS SELECT c1, c2 FROM t1;
SHOW INDEX FROM v1;
-Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
SELECT * FROM v1 USE INDEX (PRIMARY) WHERE c1=2;
ERROR 42000: Key 'PRIMARY' doesn't exist in table 'v1'
SELECT * FROM v1 FORCE INDEX (PRIMARY) WHERE c1=2;
@@ -3704,6 +3705,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
@@ -3839,6 +3951,8 @@ create view a as select 1;
end|
call p();
call p();
+Warnings:
+Error 1050 Table 'a' already exists
drop view a;
drop procedure p;
#
@@ -3847,6 +3961,7 @@ drop procedure p;
CREATE TABLE t1 (a INT);
CREATE VIEW v1 AS SELECT a FROM t1;
ALTER TABLE v1;
+ERROR HY000: 'test.v1' is not BASE TABLE
DROP VIEW v1;
DROP TABLE t1;
#
@@ -4013,6 +4128,7 @@ SHOW STATUS LIKE 'Handler_read_%';
Variable_name Value
Handler_read_first 0
Handler_read_key 1
+Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_rnd 0
@@ -4035,6 +4151,7 @@ SHOW STATUS LIKE 'Handler_read_%';
Variable_name Value
Handler_read_first 0
Handler_read_key 1
+Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_rnd 0
@@ -4147,7 +4264,7 @@ SELECT * FROM v1 WHERE a > -1 OR a > 6 AND a = 3;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 7 100.00 Using where
Warnings:
-Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > -(1))
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > <cache>(-(1)))
SELECT * FROM v1 WHERE a > -1 OR a AND a = 0;
a
2
@@ -4162,7 +4279,7 @@ SELECT * FROM v1 WHERE a > -1 OR a AND a = 0;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 7 100.00 Using where
Warnings:
-Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > -(1))
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > <cache>(-(1)))
CREATE VIEW v2 AS SELECT * FROM v1;
SELECT * FROM v2 WHERE a > -1 OR a AND a = 0;
a
@@ -4178,7 +4295,7 @@ SELECT * FROM v2 WHERE a > -1 OR a AND a = 0;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 7 100.00 Using where
Warnings:
-Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > -(1))
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > <cache>(-(1)))
DROP VIEW v1,v2;
DROP TABLE t1;
CREATE TABLE t1 (a varchar(10), KEY (a)) ;
@@ -4300,7 +4417,7 @@ WHERE f1<>0 OR f2<>0 AND f4='v' AND (f2<>0 OR f3<>0 AND f5<>0 OR f4 LIKE '%b%');
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
Warnings:
-Note 1003 select 'r' AS `f4` from `test`.`t1` where (20 <> 0)
+Note 1003 select 'r' AS `f4` from dual where (20 <> 0)
DROP VIEW v1;
DROP TABLE t1;
#
@@ -4390,7 +4507,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 2 100.00 Using where
Warnings:
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
-Note 1003 select `test`.`t1`.`a` AS `a`,10 AS `a` from `test`.`t1` join `test`.`t2` where (not(<expr_cache><10,`test`.`t1`.`a`>(<in_optimizer>(10,<exists>(select NULL from `test`.`t4` left join `test`.`t3` on(multiple equal(NULL, `test`.`t4`.`a`)) where ((`test`.`t4`.`a` >= `test`.`t1`.`a`) and trigcond(((<cache>(10) = NULL) or isnull(NULL)))) having trigcond(<is_not_null_test>(NULL)))))))
+Note 1003 select `test`.`t1`.`a` AS `a`,10 AS `a` from `test`.`t1` where (not(<expr_cache><10,`test`.`t1`.`a`>(<in_optimizer>(10,<exists>(select NULL from `test`.`t4` where ((`test`.`t4`.`a` >= `test`.`t1`.`a`) and trigcond(((<cache>(10) = NULL) or <cache>(isnull(NULL))))) having trigcond(<is_not_null_test>(NULL)))))))
SELECT * FROM t1, t2
WHERE t2.a NOT IN (SELECT t3.b FROM t3 RIGHT JOIN t4 ON (t4.a = t3.a)
WHERE t4.a >= t1.a);
@@ -4406,7 +4523,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 2 100.00 Using where
Warnings:
Note 1276 Field or reference 'v1.a' of SELECT #2 was resolved in SELECT #1
-Note 1003 select `test`.`t1`.`a` AS `a`,10 AS `a` from `test`.`t1` join `test`.`t2` where (not(<expr_cache><10,`test`.`t1`.`a`>(<in_optimizer>(10,<exists>(select NULL from `test`.`t4` left join `test`.`t3` on(multiple equal(NULL, `test`.`t4`.`a`)) where ((`test`.`t4`.`a` >= `test`.`t1`.`a`) and trigcond(((<cache>(10) = NULL) or isnull(NULL)))) having trigcond(<is_not_null_test>(NULL)))))))
+Note 1003 select `test`.`t1`.`a` AS `a`,10 AS `a` from `test`.`t1` where (not(<expr_cache><10,`test`.`t1`.`a`>(<in_optimizer>(10,<exists>(select NULL from `test`.`t4` where ((`test`.`t4`.`a` >= `test`.`t1`.`a`) and trigcond(((<cache>(10) = NULL) or <cache>(isnull(NULL))))) having trigcond(<is_not_null_test>(NULL)))))))
SELECT * FROM v1, t2
WHERE t2.a NOT IN (SELECT t3.b FROM t3 RIGHT JOIN t4 ON (t4.a = t3.a)
WHERE t4.a >= v1.a);
@@ -4437,7 +4554,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
Note 1276 Field or reference 'test.t4.b' of SELECT #2 was resolved in SELECT #1
-Note 1003 select 0 AS `c`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t4`.`c` AS `c` from `test`.`t3` join `test`.`t4` where (`test`.`t4`.`c` <= <expr_cache><`test`.`t4`.`b`>((select 0 from `test`.`t2` left join `test`.`t1` on(0) where (7 > `test`.`t4`.`b`))))
+Note 1003 select 0 AS `c`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t4`.`c` AS `c` from `test`.`t4` where (`test`.`t4`.`c` <= <expr_cache><`test`.`t4`.`b`>((select 0 from dual where (7 > `test`.`t4`.`b`))))
SELECT * FROM t3 , t4
WHERE t4.c <= (SELECT t2.e FROM t2 LEFT JOIN t1 ON ( t1.a = t2.d )
WHERE t2.b > t4.b);
@@ -4454,7 +4571,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
Note 1276 Field or reference 'v4.b' of SELECT #2 was resolved in SELECT #1
-Note 1003 select 0 AS `c`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t4`.`c` AS `c` from `test`.`t3` join `test`.`t4` where (`test`.`t4`.`c` <= <expr_cache><`test`.`t4`.`b`>((select 0 from `test`.`t2` left join `test`.`t1` on(0) where (7 > `test`.`t4`.`b`))))
+Note 1003 select 0 AS `c`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t4`.`c` AS `c` from `test`.`t4` where (`test`.`t4`.`c` <= <expr_cache><`test`.`t4`.`b`>((select 0 from dual where (7 > `test`.`t4`.`b`))))
SELECT * FROM t3, v4
WHERE v4.c <= (SELECT t2.e FROM t2 LEFT JOIN t1 ON ( t1.a = t2.d )
WHERE t2.b > v4.b);
@@ -4462,8 +4579,93 @@ c a b c
0 93 1 0
DROP VIEW v4;
DROP TABLE t1,t2,t3,t4;
+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;
+#
+# Bug #47335 assert in get_table_share
+#
+DROP TABLE IF EXISTS t1;
+DROP VIEW IF EXISTS v1;
+CREATE TEMPORARY TABLE t1 (id INT);
+ALTER VIEW t1 AS SELECT 1 AS f1;
+ERROR 42S02: Table 'test.t1' doesn't exist
+DROP TABLE t1;
+CREATE VIEW v1 AS SELECT 1 AS f1;
+CREATE TEMPORARY TABLE v1 (id INT);
+ALTER VIEW v1 AS SELECT 2 AS f1;
+DROP TABLE v1;
+SELECT * FROM v1;
+f1
+2
+DROP VIEW v1;
+#
+# Bug #47635 assert in start_waiting_global_read_lock
+# during CREATE VIEW
+#
+DROP TABLE IF EXISTS t1, t2;
+DROP VIEW IF EXISTS t2;
+CREATE TABLE t1 (f1 integer);
+CREATE TEMPORARY TABLE IF NOT EXISTS t1 (f1 integer);
+CREATE TEMPORARY TABLE t2 (f1 integer);
+DROP TABLE t1;
+FLUSH TABLES WITH READ LOCK;
+CREATE VIEW t2 AS SELECT * FROM t1;
+ERROR HY000: Can't execute the query because you have a conflicting read lock
+UNLOCK TABLES;
+DROP TABLE t1, t2;
+#
+# Bug#48315 Metadata lock is not taken for merged views that
+# use an INFORMATION_SCHEMA table
+#
+DROP TABLE IF EXISTS t1;
+DROP VIEW IF EXISTS v1;
+DROP PROCEDURE IF EXISTS p1;
+# Connection default
+CREATE VIEW v1 AS SELECT schema_name FROM information_schema.schemata;
+CREATE TABLE t1 (str VARCHAR(50));
+CREATE PROCEDURE p1() INSERT INTO t1 SELECT * FROM v1;
+# CALL p1() so the view is merged.
+CALL p1();
+# Connection 3
+LOCK TABLE t1 READ;
+# Connection default
+# Try to CALL p1() again, this time it should block for t1.
+# Sending:
+CALL p1();
+# Connection 2
+# ... then try to drop the view. This should block.
+# Sending:
+DROP VIEW v1;
+# Connection 3
+# Now allow CALL p1() to complete
+UNLOCK TABLES;
+# Connection default
+# Reaping: CALL p1()
+# Connection 2
+# Reaping: DROP VIEW v1
+# Connection default
+DROP PROCEDURE p1;
+DROP TABLE t1;
#
-# BUG#833600: Wrong result with view + outer join + uncorrelated subquery (non-semijoin)
+# Bug#12626844: WRONG ERROR MESSAGE WHILE CREATING A VIEW ON A
+# NON EXISTING DATABASE
+#
+DROP DATABASE IF EXISTS nodb;
+CREATE VIEW nodb.a AS SELECT 1;
+ERROR 42000: Unknown database 'nodb'
+#
+# lp:833600 Wrong result with view + outer join + uncorrelated subquery (non-semijoin)
#
CREATE TABLE t1 ( a int, b int );
INSERT INTO t1 VALUES (0,0),(0,0);
@@ -4525,6 +4727,7 @@ DROP TABLE t1,t2,t3;
# LP bug#1007622 Server crashes in handler::increment_statistics on
# inserting into a view over a view
#
+flush status;
CREATE TABLE t1 (a INT);
CREATE ALGORITHM=MERGE VIEW v1 AS SELECT a1.* FROM t1 AS a1, t1 AS a2;
CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM v1;
@@ -4534,6 +4737,15 @@ a
1
drop view v2,v1;
drop table t1;
+show status like '%view%';
+Variable_name Value
+Com_create_view 2
+Com_drop_view 1
+Opened_views 3
+show status like 'Opened_table%';
+Variable_name Value
+Opened_table_definitions 2
+Opened_tables 3
#
# MDEV-486 LP BUG#1010116 Incorrect query results in
# view and derived tables
@@ -4666,4 +4878,36 @@ drop tables t1,t2;
# -----------------------------------------------------------------
# -- End of 5.3 tests.
# -----------------------------------------------------------------
+#
+# MDEV-3874: Server crashes in Item_field::print on a SELECT
+# from a MERGE view with materialization+semijoin, subquery, ORDER BY
+#
+SET @save_optimizer_switch_MDEV_3874=@@optimizer_switch;
+SET optimizer_switch = 'materialization=on,semijoin=on';
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(7);
+CREATE TABLE t2 (b INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (4),(6);
+CREATE TABLE t3 (c INT) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (1),(2);
+CREATE ALGORITHM=MERGE VIEW v1 AS SELECT
+( SELECT a FROM t1 WHERE ( 1, 1 ) IN (
+SELECT b, c FROM t2, t3 HAVING c > 2 ) ) AS field1,
+b + c AS field2
+FROM t2, t3 AS table1
+GROUP BY field1, field2 ORDER BY field1;
+Warnings:
+Warning 1354 View merge algorithm can't be used here for now (assumed undefined algorithm)
+SELECT * FROM v1;
+field1 field2
+NULL 5
+NULL 7
+NULL 6
+NULL 8
+drop view v1;
+drop table t1,t2,t3;
+SET optimizer_switch=@save_optimizer_switch_MDEV_3874;
+# -----------------------------------------------------------------
+# -- End of 5.5 tests.
+# -----------------------------------------------------------------
SET optimizer_switch=@save_optimizer_switch;
View Lorry Controller Status