diff options
Diffstat (limited to 'mysql-test/r/view.result')
| -rw-r--r-- | mysql-test/r/view.result | 6512 |
1 files changed, 0 insertions, 6512 deletions
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result deleted file mode 100644 index e61e2d2663d..00000000000 --- a/mysql-test/r/view.result +++ /dev/null @@ -1,6512 +0,0 @@ -SET @save_optimizer_switch=@@optimizer_switch; -SET optimizer_switch='outer_join_with_cache=off'; -create view v1 (c,d) as select a,b from t1; -ERROR 42S02: Table 'test.t1' doesn't exist -create temporary table t1 (a int, b int); -create view v1 (c) as select b+1 from t1; -ERROR HY000: View's SELECT refers to a temporary table 't1' -drop table t1; -create table t1 (a int, b int); -insert into t1 values (1,2), (1,3), (2,4), (2,5), (3,10); -create view v1 (c,d) as select a,b+@@global.max_user_connections from t1; -ERROR HY000: View's SELECT contains a variable or parameter -create view v1 (c,d) as select a,b from t1 -where a = @@global.max_user_connections; -ERROR HY000: View's SELECT contains a variable or parameter -create view v1 (c) as select b+1 from t1; -select c from v1; -c -3 -4 -5 -6 -11 -select is_updatable from information_schema.views where table_name='v1'; -is_updatable -NO -create temporary table t1 (a int, b int); -select * from t1; -a b -select c from v1; -c -3 -4 -5 -6 -11 -show create table v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`b` + 1 AS `c` from `t1` latin1 latin1_swedish_ci -show create view v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`b` + 1 AS `c` from `t1` latin1 latin1_swedish_ci -show create view t1; -ERROR HY000: 'test.t1' is not of type 'VIEW' -drop table t1; -select a from v1; -ERROR 42S22: Unknown column 'a' in 'field list' -select v1.a from v1; -ERROR 42S22: Unknown column 'v1.a' in 'field list' -select b from v1; -ERROR 42S22: Unknown column 'b' in 'field list' -select v1.b from v1; -ERROR 42S22: Unknown column 'v1.b' in 'field list' -explain extended select c from v1; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 -Warnings: -Note 1003 select `test`.`t1`.`b` + 1 AS `c` from `test`.`t1` -create algorithm=temptable view v2 (c) as select b+1 from t1; -show create view v2; -View Create View character_set_client collation_connection -v2 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t1`.`b` + 1 AS `c` from `t1` latin1 latin1_swedish_ci -select c from v2; -c -3 -4 -5 -6 -11 -explain extended select c from v2; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 100.00 -2 DERIVED t1 ALL NULL NULL NULL NULL 5 100.00 -Warnings: -Note 1003 /* select#1 */ select `v2`.`c` AS `c` from `test`.`v2` -create view v3 (c) as select a+1 from v1; -ERROR 42S22: Unknown column 'a' in 'field list' -create view v3 (c) as select b+1 from v1; -ERROR 42S22: Unknown column 'b' in 'field list' -create view v3 (c) as select c+1 from v1; -select c from v3; -c -4 -5 -6 -7 -12 -explain extended select c from v3; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 -Warnings: -Note 1003 select `test`.`t1`.`b` + 1 + 1 AS `c` from `test`.`t1` -create algorithm=temptable view v4 (c) as select c+1 from v2; -select c from v4; -c -4 -5 -6 -7 -12 -explain extended select c from v4; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 100.00 -2 DERIVED <derived3> ALL NULL NULL NULL NULL 5 100.00 -3 DERIVED t1 ALL NULL NULL NULL NULL 5 100.00 -Warnings: -Note 1003 /* select#1 */ select `v4`.`c` AS `c` from `test`.`v4` -create view v5 (c) as select c+1 from v2; -select c from v5; -c -4 -5 -6 -7 -12 -explain extended select c from v5; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <derived3> ALL NULL NULL NULL NULL 5 100.00 -3 DERIVED t1 ALL NULL NULL NULL NULL 5 100.00 -Warnings: -Note 1003 /* select#1 */ select `v2`.`c` + 1 AS `c` from `test`.`v2` -create algorithm=temptable view v6 (c) as select c+1 from v1; -select c from v6; -c -4 -5 -6 -7 -12 -explain extended select c from v6; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 100.00 -2 DERIVED t1 ALL NULL NULL NULL NULL 5 100.00 -Warnings: -Note 1003 /* select#1 */ select `v6`.`c` AS `c` from `test`.`v6` -show tables; -Tables_in_test -t1 -v1 -v2 -v3 -v4 -v5 -v6 -show full tables; -Tables_in_test Table_type -t1 BASE TABLE -v1 VIEW -v2 VIEW -v3 VIEW -v4 VIEW -v5 VIEW -v6 VIEW -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 Max_index_length Temporary -t1 MyISAM 10 Fixed 5 9 45 # 1024 0 NULL # # # latin1_swedish_ci NULL # N -v1 NULL NULL NULL NULL NULL NULL # NULL NULL NULL # # # NULL NULL NULL VIEW # NULL -v2 NULL NULL NULL NULL NULL NULL # NULL NULL NULL # # # NULL NULL NULL VIEW # NULL -v3 NULL NULL NULL NULL NULL NULL # NULL NULL NULL # # # NULL NULL NULL VIEW # NULL -v4 NULL NULL NULL NULL NULL NULL # NULL NULL NULL # # # NULL NULL NULL VIEW # NULL -v5 NULL NULL NULL NULL NULL NULL # NULL NULL NULL # # # NULL NULL NULL VIEW # NULL -v6 NULL NULL NULL NULL NULL NULL # NULL NULL NULL # # # NULL NULL NULL VIEW # NULL -drop view v1,v2,v3,v4,v5,v6; -create view v1 (c,d,e,f) as select a,b, -a in (select a+2 from t1), a = all (select a from t1) from t1; -create view v2 as select c, d from v1; -select * from v1; -c d e f -1 2 0 0 -1 3 0 0 -2 4 0 0 -2 5 0 0 -3 10 1 0 -select * from v2; -c d -1 2 -1 3 -2 4 -2 5 -3 10 -create view v1 (c,d,e,f) as select a,b, a in (select a+2 from t1), a = all (select a from t1) from t1; -ERROR 42S01: Table 'v1' already exists -create or replace view v1 (c,d,e,f) as select a,b, a in (select a+2 from t1), a = all (select a from t1) from t1; -drop view v2; -alter view v2 as select c, d from v1; -ERROR 42S02: Table 'test.v2' doesn't exist -create or replace view v2 as select c, d from v1; -alter view v1 (c,d) as select a,max(b) from t1 group by a; -select * from v1; -c d -1 3 -2 5 -3 10 -select * from v2; -c d -1 3 -2 5 -3 10 -drop view v100; -ERROR 42S02: Unknown VIEW: 'test.v100' -drop view t1; -ERROR HY000: 'test.t1' is not of type 'VIEW' -drop table v1; -ERROR 42S02: 'test.v1' is a view -drop view v1,v2; -drop table t1; -create table t1 (a int); -insert into t1 values (1), (2), (3); -create view v1 (a) as select a+1 from t1; -create view v2 (a) as select a-1 from t1; -select * from t1 natural left join v1; -a -1 -2 -3 -select * from v2 natural left join t1; -a -0 -1 -2 -select * from v2 natural left join v1; -a -0 -1 -2 -drop view v1, v2; -drop table t1; -create table t1 (a int); -insert into t1 values (1), (2), (3), (1), (2), (3); -create view v1 as select distinct a from t1; -select * from v1; -a -1 -2 -3 -explain select * from v1; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 -2 DERIVED t1 ALL NULL NULL NULL NULL 6 Using temporary -select * from t1; -a -1 -2 -3 -1 -2 -3 -drop view v1; -drop table t1; -create table t1 (a int); -create view v1 as select distinct a from t1 WITH CHECK OPTION; -ERROR HY000: CHECK OPTION on non-updatable view `test`.`v1` -create view v1 as select a from t1 WITH CHECK OPTION; -create view v2 as select a from t1 WITH CASCADED CHECK OPTION; -create view v3 as select a from t1 WITH LOCAL CHECK OPTION; -drop view v3 RESTRICT; -drop view v2 CASCADE; -drop view v1; -drop table t1; -create table t1 (a int, b int); -insert into t1 values (1,2), (1,3), (2,4), (2,5), (3,10); -create view v1 (c) as select b+1 from t1; -select test.c from v1 test; -c -3 -4 -5 -6 -11 -create algorithm=temptable view v2 (c) as select b+1 from t1; -select test.c from v2 test; -c -3 -4 -5 -6 -11 -select test1.* from v1 test1, v2 test2 where test1.c=test2.c; -c -3 -4 -5 -6 -11 -select test2.* from v1 test1, v2 test2 where test1.c=test2.c; -c -3 -4 -5 -6 -11 -drop table t1; -drop view v1,v2; -create table t1 (a int); -insert into t1 values (1), (2), (3), (4); -create view v1 as select a+1 from t1 order by 1 desc limit 2; -select * from v1; -a+1 -5 -4 -explain select * from v1; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 -2 DERIVED t1 ALL NULL NULL NULL NULL 4 Using filesort -drop view v1; -drop table t1; -create table t1 (a int); -insert into t1 values (1), (2), (3), (4); -create view v1 as select a+1 from t1; -create table t2 select * from v1; -show columns from t2; -Field Type Null Key Default Extra -a+1 bigint(12) YES NULL -select * from t2; -a+1 -2 -3 -4 -5 -drop view v1; -drop table t1,t2; -create table t1 (a int, b int, primary key(a)); -insert into t1 values (10,2), (20,3), (30,4), (40,5), (50,10); -create view v1 (a,c) as select a, b+1 from t1; -create algorithm=temptable view v2 (a,c) as select a, b+1 from t1; -select is_updatable from information_schema.views where table_name='v2'; -is_updatable -NO -select is_updatable from information_schema.views where table_name='v1'; -is_updatable -YES -update v1 set c=a+c; -ERROR HY000: Column 'c' is not updatable -update v2 set a=a+c; -ERROR HY000: The target table v2 of the UPDATE is not updatable -update v1 set a=a+c; -select * from v1; -a c -13 3 -24 4 -35 5 -46 6 -61 11 -select * from t1; -a b -13 2 -24 3 -35 4 -46 5 -61 10 -drop table t1; -drop view v1,v2; -create table t1 (a int, b int, primary key(a)); -insert into t1 values (10,2), (20,3), (30,4), (40,5), (50,10); -create table t2 (x int); -insert into t2 values (10), (20); -create view v1 (a,c) as select a, b+1 from t1; -create algorithm=temptable view v2 (a,c) as select a, b+1 from t1; -update t2,v1 set v1.c=v1.a+v1.c where t2.x=v1.a; -ERROR HY000: Column 'c' is not updatable -update t2,v2 set v2.a=v2.v2.a+c where t2.x=v2.a; -ERROR HY000: The target table v2 of the UPDATE is not updatable -update t2,v1 set v1.a=v1.a+v1.c where t2.x=v1.a; -select * from v1; -a c -13 3 -24 4 -30 5 -40 6 -50 11 -select * from t1; -a b -13 2 -24 3 -30 4 -40 5 -50 10 -drop table t1,t2; -drop view v1,v2; -create table t1 (a int, b int, primary key(b)); -insert into t1 values (1,20), (2,30), (3,40), (4,50), (5,100); -create view v1 (c) as select b from t1 where a<3; -select * from v1; -c -20 -30 -explain extended select * from v1; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where -Warnings: -Note 1003 select `test`.`t1`.`b` AS `c` from `test`.`t1` where `test`.`t1`.`a` < 3 -update v1 set c=c+1; -select * from t1; -a b -1 21 -2 31 -3 40 -4 50 -5 100 -create view v2 (c) as select b from t1 where a>=3; -select * from v1, v2; -c c -21 40 -31 40 -21 50 -31 50 -21 100 -31 100 -drop view v1, v2; -drop table t1; -create table t1 (a int, b int, primary key(a)); -insert into t1 values (1,2), (2,3), (3,4), (4,5), (5,10); -create view v1 (a,c) as select a, b+1 from t1; -create algorithm=temptable view v2 (a,c) as select a, b+1 from t1; -delete from v2 where c < 4; -ERROR HY000: The target table v2 of the DELETE is not updatable -delete from v1 where c < 4; -select * from v1; -a c -2 4 -3 5 -4 6 -5 11 -select * from t1; -a b -2 3 -3 4 -4 5 -5 10 -drop table t1; -drop view v1,v2; -create table t1 (a int, b int, primary key(a)); -insert into t1 values (1,2), (2,3), (3,4), (4,5), (5,10); -create table t2 (x int); -insert into t2 values (1), (2), (3), (4); -create view v1 (a,c) as select a, b+1 from t1; -create algorithm=temptable view v2 (a,c) as select a, b+1 from t1; -delete v2 from t2,v2 where t2.x=v2.a; -ERROR HY000: The target table v2 of the DELETE is not updatable -delete v1 from t2,v1 where t2.x=v1.a; -select * from v1; -a c -5 11 -select * from t1; -a b -5 10 -drop table t1,t2; -drop view v1,v2; -create table t1 (a int, b int, c int, primary key(a,b)); -insert into t1 values (10,2,-1), (20,3,-2), (30,4,-3), (40,5,-4), (50,10,-5); -create view v1 (x,y) as select a, b from t1; -create view v2 (x,y) as select a, c from t1; -set updatable_views_with_limit=NO; -update v1 set x=x+1; -update v2 set x=x+1; -update v1 set x=x+1 limit 1; -update v2 set x=x+1 limit 1; -ERROR HY000: The target table v2 of the UPDATE is not updatable -set updatable_views_with_limit=YES; -update v1 set x=x+1 limit 1; -update v2 set x=x+1 limit 1; -Warnings: -Note 1355 View being updated does not have complete key of underlying table in it -set updatable_views_with_limit=DEFAULT; -show variables like "updatable_views_with_limit"; -Variable_name Value -updatable_views_with_limit YES -select * from t1; -a b c -15 2 -1 -22 3 -2 -32 4 -3 -42 5 -4 -52 10 -5 -drop table t1; -drop view v1,v2; -create table t1 (a int, b int, c int, primary key(a,b)); -insert into t1 values (10,2,-1), (20,3,-2); -create view v1 (x,y,z) as select c, b, a from t1; -create view v2 (x,y) as select b, a from t1; -create view v3 (x,y,z) as select b, a, b from t1; -create view v4 (x,y,z) as select c+1, b, a from t1; -create algorithm=temptable view v5 (x,y,z) as select c, b, a from t1; -insert into v3 values (-60,4,30); -ERROR HY000: The target table v3 of the INSERT is not insertable-into -insert into v4 values (-60,4,30); -ERROR HY000: The target table v4 of the INSERT is not insertable-into -insert into v5 values (-60,4,30); -ERROR HY000: The target table v5 of the INSERT is not insertable-into -insert into v1 values (-60,4,30); -insert into v1 (z,y,x) values (50,6,-100); -insert into v2 values (5,40); -select * from t1; -a b c -10 2 -1 -20 3 -2 -30 4 -60 -50 6 -100 -40 5 NULL -drop table t1; -drop view v1,v2,v3,v4,v5; -create table t1 (a int, b int, c int, primary key(a,b)); -insert into t1 values (10,2,-1), (20,3,-2); -create table t2 (a int, b int, c int, primary key(a,b)); -insert into t2 values (30,4,-60); -create view v1 (x,y,z) as select c, b, a from t1; -create view v2 (x,y) as select b, a from t1; -create view v3 (x,y,z) as select b, a, b from t1; -create view v4 (x,y,z) as select c+1, b, a from t1; -create algorithm=temptable view v5 (x,y,z) as select c, b, a from t1; -insert into v3 select c, b, a from t2; -ERROR HY000: The target table v3 of the INSERT is not insertable-into -insert into v4 select c, b, a from t2; -ERROR HY000: The target table v4 of the INSERT is not insertable-into -insert into v5 select c, b, a from t2; -ERROR HY000: The target table v5 of the INSERT is not insertable-into -insert into v1 select c, b, a from t2; -insert into v1 (z,y,x) select a+20,b+2,-100 from t2; -insert into v2 select b+1, a+10 from t2; -select * from t1; -a b c -10 2 -1 -20 3 -2 -30 4 -60 -50 6 -100 -40 5 NULL -drop table t1, t2; -drop view v1,v2,v3,v4,v5; -create table t1 (a int, primary key(a)); -insert into t1 values (1), (2), (3); -create view v1 (x) as select a from t1 where a > 1; -select t1.a, v1.x from t1 left join v1 on (t1.a= v1.x); -a x -1 NULL -2 2 -3 3 -drop table t1; -drop view v1; -create table t1 (a int, primary key(a)); -insert into t1 values (1), (2), (3), (200); -create view v1 (x) as select a from t1 where a > 1; -create view v2 (y) as select x from v1 where x < 100; -select * from v2; -y -2 -3 -drop table t1; -drop view v1,v2; -create table t1 (a int, primary key(a)); -insert into t1 values (1), (2), (3), (200); -create ALGORITHM=TEMPTABLE view v1 (x) as select a from t1; -create view v2 (y) as select x from v1; -update v2 set y=10 where y=2; -ERROR HY000: The target table v2 of the UPDATE is not updatable -drop table t1; -drop view v1,v2; -create table t1 (a int not null auto_increment, b int not null, primary key(a), unique(b)); -create view v1 (x) as select b from t1; -insert into v1 values (1); -select last_insert_id(); -last_insert_id() -0 -insert into t1 (b) values (2); -select last_insert_id(); -last_insert_id() -2 -select * from t1; -a b -1 1 -2 2 -drop view v1; -drop table t1; -set sql_mode='ansi'; -create table t1 ("a*b" int); -create view v1 as select "a*b" from t1; -show create view v1; -View Create View character_set_client collation_connection -v1 CREATE VIEW "v1" AS select "t1"."a*b" AS "a*b" from "t1" latin1 latin1_swedish_ci -drop view v1; -drop table t1; -set sql_mode=default; -create table t1 (t_column int); -create view v1 as select 'a'; -select * from v1, t1; -a t_column -drop view v1; -drop table t1; -create table `t1a``b` (col1 char(2)); -create view v1 as select * from `t1a``b`; -select * from v1; -col1 -describe v1; -Field Type Null Key Default Extra -col1 char(2) YES NULL -drop view v1; -drop table `t1a``b`; -create table t1 (col1 char(5),col2 char(5)); -create view v1 as select * from t1; -drop table t1; -create table t1 (col1 char(5),newcol2 char(5)); -insert into v1 values('a','aa'); -ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -drop table t1; -select * from v1; -ERROR HY000: 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; -create view v1 (a,a) as select 'a','a'; -ERROR 42S21: Duplicate column name 'a' -create table t1 (col1 int,col2 char(22)); -insert into t1 values(5,'Hello, world of views'); -create view v1 as select * from t1; -create view v2 as select * from v1; -update v2 set col2='Hello, view world'; -select is_updatable from information_schema.views; -is_updatable -YES -YES -select * from t1; -col1 col2 -5 Hello, view world -drop view v2, v1; -drop table t1; -create table t1 (a int, b int); -create view v1 as select a, sum(b) from t1 group by a; -select b from v1 use index (some_index) where b=1; -ERROR 42000: Key 'some_index' doesn't exist in table 'v1' -drop view v1; -drop table t1; -create table t1 (col1 char(5),col2 char(5)); -create view v1 (col1,col2) as select col1,col2 from t1; -insert into v1 values('s1','p1'),('s1','p2'),('s1','p3'),('s1','p4'),('s2','p1'),('s3','p2'),('s4','p4'); -select distinct first.col2 from t1 first where first.col2 in (select second.col2 from t1 second where second.col1<>first.col1); -col2 -p1 -p2 -p4 -select distinct first.col2 from v1 first where first.col2 in (select second.col2 from t1 second where second.col1<>first.col1); -col2 -p1 -p2 -p4 -drop view v1; -drop table t1; -create table t1 (a int); -create view v1 as select a from t1; -insert into t1 values (1); -SET @v0 = '2'; -PREPARE stmt FROM 'UPDATE v1 SET a = ?'; -EXECUTE stmt USING @v0; -DEALLOCATE PREPARE stmt; -SET @v0 = '3'; -PREPARE stmt FROM 'insert into v1 values (?)'; -EXECUTE stmt USING @v0; -DEALLOCATE PREPARE stmt; -SET @v0 = '4'; -PREPARE stmt FROM 'insert into v1 (a) values (?)'; -EXECUTE stmt USING @v0; -DEALLOCATE PREPARE stmt; -select * from t1; -a -2 -3 -4 -drop view v1; -drop table t1; -CREATE VIEW v02 AS SELECT * FROM DUAL; -ERROR HY000: No tables used -SHOW TABLES; -Tables_in_test -CREATE VIEW v1 AS SELECT EXISTS (SELECT 1 UNION SELECT 2); -select * from v1; -EXISTS (SELECT 1 UNION SELECT 2) -1 -drop view v1; -create table t1 (col1 int,col2 char(22)); -create view v1 as select * from t1; -create index i1 on v1 (col1); -ERROR HY000: 'test.v1' is not of type 'BASE TABLE' -drop view v1; -drop table t1; -CREATE VIEW v1 (f1,f2,f3,f4) AS SELECT connection_id(), pi(), current_user(), version(); -SHOW CREATE VIEW v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select connection_id() AS `f1`,pi() AS `f2`,current_user() AS `f3`,version() AS `f4` latin1 latin1_swedish_ci -drop view v1; -create table t1 (s1 int); -create table t2 (s2 int); -insert into t1 values (1), (2); -insert into t2 values (2), (3); -create view v1 as select * from t1,t2 union all select * from t1,t2; -select * from v1; -s1 s2 -1 2 -2 2 -1 3 -2 3 -1 2 -2 2 -1 3 -2 3 -drop view v1; -drop tables t1, t2; -create table t1 (col1 int); -insert into t1 values (1); -create view v1 as select count(*) from t1; -insert into t1 values (null); -select * from v1; -count(*) -2 -drop view v1; -drop table t1; -create table t1 (a int); -create table t2 (a int); -create view v1 as select a from t1; -create view v2 as select a from t2 where a in (select a from v1); -show create view v2; -View Create View character_set_client collation_connection -v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t2`.`a` AS `a` from `t2` where `t2`.`a` in (select `v1`.`a` from `v1`) latin1 latin1_swedish_ci -drop view v2, v1; -drop table t1, t2; -CREATE VIEW `v 1` AS select 5 AS `5`; -show create view `v 1`; -View Create View character_set_client collation_connection -v 1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v 1` AS select 5 AS `5` latin1 latin1_swedish_ci -drop view `v 1`; -create database mysqltest; -create table mysqltest.t1 (a int, b int); -create view mysqltest.v1 as select a from mysqltest.t1; -alter view mysqltest.v1 as select b from mysqltest.t1; -alter view mysqltest.v1 as select a from mysqltest.t1; -drop database mysqltest; -CREATE TABLE t1 (c1 int not null auto_increment primary key, c2 varchar(20), fulltext(c2)); -insert into t1 (c2) VALUES ('real Beer'),('Water'),('Kossu'),('Coca-Cola'),('Vodka'),('Wine'),('almost real Beer'); -select * from t1 WHERE match (c2) against ('Beer'); -c1 c2 -1 real Beer -7 almost real Beer -CREATE VIEW v1 AS SELECT * from t1 WHERE match (c2) against ('Beer'); -select * from v1; -c1 c2 -1 real Beer -7 almost real Beer -drop view v1; -drop table t1; -create table t1 (a int); -insert into t1 values (1),(1),(2),(2),(3),(3); -create view v1 as select a from t1; -select distinct a from v1; -a -1 -2 -3 -select distinct a from v1 limit 2; -a -1 -2 -select distinct a from t1 limit 2; -a -1 -2 -prepare stmt1 from "select distinct a from v1 limit 2"; -execute stmt1; -a -1 -2 -execute stmt1; -a -1 -2 -deallocate prepare stmt1; -drop view v1; -drop table t1; -create table t1 (tg_column bigint); -create view v1 as select count(tg_column) as vg_column from t1; -select avg(vg_column) from v1; -avg(vg_column) -0.0000 -drop view v1; -drop table t1; -create table t1 (col1 bigint not null, primary key (col1)); -create table t2 (col1 bigint not null, key (col1)); -create view v1 as select * from t1; -create view v2 as select * from t2; -insert into v1 values (1); -insert into v2 values (1); -create view v3 (a,b) as select v1.col1 as a, v2.col1 as b from v1, v2 where v1.col1 = v2.col1; -select * from v3; -a b -1 1 -show create view v3; -View Create View character_set_client collation_connection -v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `v1`.`col1` AS `a`,`v2`.`col1` AS `b` from (`v1` join `v2`) where `v1`.`col1` = `v2`.`col1` latin1 latin1_swedish_ci -drop view v3, v2, v1; -drop table t2, t1; -create function `f``1` () returns int return 5; -create view v1 as select test.`f``1` (); -show create view v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`f``1`() AS `test.``f````1`` ()` latin1 latin1_swedish_ci -select * from v1; -test.`f``1` () -5 -drop view v1; -drop function `f``1`; -create function a() returns int return 5; -create view v1 as select a(); -select * from v1; -a() -5 -drop view v1; -drop function a; -create table t2 (col1 char collate latin1_german2_ci); -create view v2 as select col1 collate latin1_german1_ci from t2; -show create view v2; -View Create View character_set_client collation_connection -v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t2`.`col1` collate latin1_german1_ci AS `col1 collate latin1_german1_ci` from `t2` latin1 latin1_swedish_ci -show create view v2; -View Create View character_set_client collation_connection -v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t2`.`col1` collate latin1_german1_ci AS `col1 collate latin1_german1_ci` from `t2` latin1 latin1_swedish_ci -drop view v2; -drop table t2; -create table t1 (a int); -insert into t1 values (1), (2); -create view v1 as select 5 from t1 order by 1; -show create view v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 5 AS `5` from `t1` order by 1 latin1 latin1_swedish_ci -select * from v1; -5 -5 -5 -drop view v1; -drop table t1; -create function x1 () returns int return 5; -create table t1 (s1 int); -create view v1 as select x1() from t1; -drop function x1; -select * from v1; -ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -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 Max_index_length Temporary -t1 MyISAM 10 Fixed 0 0 0 # 1024 0 NULL # # NULL latin1_swedish_ci NULL # N -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 # NULL -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; -drop table t1; -create table t1 (a varchar(20)); -create view v1 as select a from t1; -alter table t1 change a aa int; -select * from v1; -ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -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 Max_index_length Temporary -t1 MyISAM 10 Fixed 0 0 0 # 1024 0 NULL # # NULL latin1_swedish_ci NULL # N -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 # NULL -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 -show create view v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`t1`.`a` AS `a` from `t1` latin1 latin1_swedish_ci -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; -drop table t1; -create view v1 as select 99999999999999999999999999999999999999999999999999999 as col1; -show create view v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 99999999999999999999999999999999999999999999999999999 AS `col1` latin1 latin1_swedish_ci -drop view v1; -SET @old_cs_client = @@character_set_client; -SET @old_cs_results = @@character_set_results; -SET @old_cs_connection = @@character_set_connection; -set names utf8; -create table tü (cü char); -create view vü as select cü from tü; -insert into vü values ('ü'); -select * from vü; -cü -ü -drop view vü; -drop table tü; -SET character_set_client = @old_cs_client; -SET character_set_results = @old_cs_results; -SET character_set_connection = @old_cs_connection; -create table t1 (a int, b int); -insert into t1 values (1,2), (1,3), (2,4), (2,5), (3,10); -create view v1(c) as select a+1 from t1 where b >= 4; -select c from v1 where exists (select * from t1 where a=2 and b=c); -c -4 -drop view v1; -drop table t1; -create view v1 as select cast(1 as char(3)); -show create view v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(1 as char(3) charset latin1) AS `cast(1 as char(3))` latin1 latin1_swedish_ci -select * from v1; -cast(1 as char(3)) -1 -drop view v1; -create table t1 (a int); -create view v1 as select a from t1; -create view v3 as select a from t1; -create database mysqltest; -rename table v1 to mysqltest.v1; -ERROR HY000: Changing schema from 'test' to 'mysqltest' is not allowed -rename table v1 to v2; -rename table v3 to v1, v2 to t1; -ERROR 42S01: Table 't1' already exists -drop table t1; -drop view v2,v3; -drop database mysqltest; -create view v1 as select 'a',1; -create view v2 as select * from v1 union all select * from v1; -create view v3 as select * from v2 where 1 = (select `1` from v2); -create view v4 as select * from v3; -select * from v4; -ERROR 21000: Subquery returns more than 1 row -drop view v4, v3, v2, v1; -create view v1 as select 5 into @w; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'into @w' at line 1 -create view v1 as select 5 into outfile 'ttt'; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'into outfile 'ttt'' at line 1 -create table t1 (a int); -create view v1 as select a from t1 procedure analyse(); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'procedure analyse()' at line 1 -create view v1 as select 1 from (select 1) as d1; -drop view v1; -drop table t1; -create table t1 (s1 int, primary key (s1)); -create view v1 as select * from t1; -insert into v1 values (1) on duplicate key update s1 = 7; -insert into v1 values (1) on duplicate key update s1 = 7; -select * from t1; -s1 -7 -drop view v1; -drop table t1; -create table t1 (col1 int); -create table t2 (col1 int); -create table t3 (col1 datetime not null); -create view v1 as select * from t1; -create view v2 as select * from v1; -create view v3 as select v2.col1 from v2,t2 where v2.col1 = t2.col1; -insert into v2 values ((select max(col1) from v1)); -ERROR HY000: The definition of table 'v1' prevents operation INSERT on table 'v2' -insert into t1 values ((select max(col1) from v1)); -ERROR HY000: The definition of table 'v1' prevents operation INSERT on table 't1' -insert into v2 values ((select max(col1) from v1)); -ERROR HY000: The definition of table 'v1' prevents operation INSERT on table 'v2' -insert into v2 values ((select max(col1) from t1)); -ERROR HY000: The definition of table 'v2' prevents operation INSERT on table 'v2' -insert into t1 values ((select max(col1) from t1)); -ERROR HY000: Table 't1' is specified twice, both as a target for 'INSERT' and as a separate source for data -insert into v2 values ((select max(col1) from t1)); -ERROR HY000: The definition of table 'v2' prevents operation INSERT on table 'v2' -insert into v2 values ((select max(col1) from v2)); -ERROR HY000: Table 'v2' is specified twice, both as a target for 'INSERT' and as a separate source for data -insert into t1 values ((select max(col1) from v2)); -ERROR HY000: The definition of table 'v2' prevents operation INSERT on table 't1' -insert into v2 values ((select max(col1) from v2)); -ERROR HY000: Table 'v2' is specified twice, both as a target for 'INSERT' and as a separate source for data -insert into v3 (col1) values ((select max(col1) from v1)); -ERROR HY000: The definition of table 'v1' prevents operation INSERT on table 'v3' -insert into v3 (col1) values ((select max(col1) from t1)); -ERROR HY000: The definition of table 'v3' prevents operation INSERT on table 'v3' -insert into v3 (col1) values ((select max(col1) from v2)); -ERROR HY000: The definition of table 'v2' prevents operation INSERT on table 'v3' -insert into v3 (col1) values ((select CONVERT_TZ('20050101000000','UTC','MET') from v2)); -ERROR HY000: The definition of table 'v2' prevents operation INSERT on table 'v3' -insert into v3 (col1) values ((select CONVERT_TZ('20050101000000','UTC','MET') from t2)); -insert into t3 values ((select CONVERT_TZ('20050101000000','UTC','MET') from t2)); -ERROR 23000: Column 'col1' cannot be null -create algorithm=temptable view v4 as select * from t1; -insert into t1 values (1),(2),(3); -insert into t1 (col1) values ((select max(col1) from v4)); -select * from t1; -col1 -NULL -1 -2 -3 -3 -drop view v4,v3,v2,v1; -drop table t1,t2,t3; -create table t1 (s1 int); -create view v1 as select * from t1; -handler v1 open as xx; -ERROR HY000: 'test.v1' is not of type 'BASE TABLE' -drop view v1; -drop table t1; -create table t1(a int); -insert into t1 values (0), (1), (2), (3); -create table t2 (a int); -insert into t2 select a from t1 where a > 1; -create view v1 as select a from t1 where a > 1; -select * from t1 left join (t2 as t, v1) on v1.a=t1.a; -a a a -0 NULL NULL -1 NULL NULL -2 2 2 -2 3 2 -3 2 3 -3 3 3 -select * from t1 left join (t2 as t, t2) on t2.a=t1.a; -a a a -0 NULL NULL -1 NULL NULL -2 2 2 -2 3 2 -3 2 3 -3 3 3 -drop view v1; -drop table t1, t2; -create table t1 (s1 char); -create view v1 as select s1 collate latin1_german1_ci as s1 from t1; -insert into v1 values ('a'); -select * from v1; -s1 -a -update v1 set s1='b'; -select * from v1; -s1 -b -update v1,t1 set v1.s1='c' where t1.s1=v1.s1; -select * from v1; -s1 -c -prepare stmt1 from "update v1,t1 set v1.s1=? where t1.s1=v1.s1"; -set @arg='d'; -execute stmt1 using @arg; -select * from v1; -s1 -d -set @arg='e'; -execute stmt1 using @arg; -select * from v1; -s1 -e -deallocate prepare stmt1; -drop view v1; -drop table t1; -create table t1 (a int); -create table t2 (a int); -create view v1 as select * from t1; -lock tables t1 read, v1 read; -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); -create view v1 as select * from t1 where a < 2 with check option; -insert into v1 values(1); -insert into v1 values(3); -ERROR 44000: CHECK OPTION failed `test`.`v1` -insert ignore into v1 values (2),(3),(0); -Warnings: -Warning 1369 CHECK OPTION failed `test`.`v1` -Warning 1369 CHECK OPTION failed `test`.`v1` -select * from t1; -a -1 -0 -delete from t1; -insert into v1 SELECT 1; -insert into v1 SELECT 3; -ERROR 44000: CHECK OPTION failed `test`.`v1` -create table t2 (a int); -insert into t2 values (2),(3),(0); -insert ignore into v1 SELECT a from t2; -Warnings: -Warning 1369 CHECK OPTION failed `test`.`v1` -Warning 1369 CHECK OPTION failed `test`.`v1` -select * from t1 order by a desc; -a -1 -0 -update v1 set a=-1 where a=0; -update v1 set a=2 where a=1; -ERROR 44000: CHECK OPTION failed `test`.`v1` -select * from t1 order by a desc; -a -1 --1 -update v1 set a=0 where a=0; -insert into t2 values (1); -update v1,t2 set v1.a=v1.a-1 where v1.a=t2.a; -select * from t1 order by a desc; -a -0 --1 -update v1 set a=a+1; -update ignore v1,t2 set v1.a=v1.a+1 where v1.a=t2.a; -Warnings: -Warning 1369 CHECK OPTION failed `test`.`v1` -select * from t1; -a -1 -1 -drop view v1; -drop table t1, t2; -create table t1 (a int); -create view v1 as select * from t1 where a < 2 with check option; -create view v2 as select * from v1 where a > 0 with local check option; -create view v3 as select * from v1 where a > 0 with cascaded check option; -insert into v2 values (1); -insert into v3 values (1); -insert into v2 values (0); -ERROR 44000: CHECK OPTION failed `test`.`v2` -insert into v3 values (0); -ERROR 44000: CHECK OPTION failed `test`.`v3` -insert into v2 values (2); -insert into v3 values (2); -ERROR 44000: CHECK OPTION failed `test`.`v3` -select * from t1; -a -1 -1 -2 -drop view v3,v2,v1; -drop table t1; -create table t1 (a int, primary key (a)); -create view v1 as select * from t1 where a < 2 with check option; -insert into v1 values (1) on duplicate key update a=2; -insert into v1 values (1) on duplicate key update a=2; -ERROR 44000: CHECK OPTION failed `test`.`v1` -insert ignore into v1 values (1) on duplicate key update a=2; -Warnings: -Warning 1369 CHECK OPTION failed `test`.`v1` -select * from t1; -a -1 -drop view v1; -drop table t1; -create table t1 (s1 int); -create view v1 as select * from t1; -create view v2 as select * from v1; -alter view v1 as select * from v2; -ERROR 42S02: Table 'test.v1' doesn't exist -alter view v1 as select * from v1; -ERROR 42S02: Table 'test.v1' doesn't exist -create or replace view v1 as select * from v2; -ERROR 42S02: Table 'test.v1' doesn't exist -create or replace view v1 as select * from v1; -ERROR 42S02: Table 'test.v1' doesn't exist -drop view v2,v1; -drop table t1; -create table t1 (a int); -create view v1 as select * from t1; -show create view v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` latin1 latin1_swedish_ci -alter algorithm=undefined view v1 as select * from t1 with check option; -show create view v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` WITH CASCADED CHECK OPTION latin1 latin1_swedish_ci -alter algorithm=merge view v1 as select * from t1 with cascaded check option; -show create view v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` WITH CASCADED CHECK OPTION latin1 latin1_swedish_ci -alter algorithm=temptable view v1 as select * from t1; -show create view v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` latin1 latin1_swedish_ci -drop view v1; -drop table t1; -create table t1 (s1 int); -create table t2 (s1 int); -create view v2 as select * from t2 where s1 in (select s1 from t1); -insert into v2 values (5); -insert into t1 values (5); -select * from v2; -s1 -5 -update v2 set s1 = 0; -select * from v2; -s1 -select * from t2; -s1 -0 -alter view v2 as select * from t2 where s1 in (select s1 from t1) with check option; -insert into v2 values (5); -update v2 set s1 = 1; -ERROR 44000: CHECK OPTION failed `test`.`v2` -insert into t1 values (1); -update v2 set s1 = 1; -select * from v2; -s1 -1 -select * from t2; -s1 -0 -1 -prepare stmt1 from "select * from v2;"; -execute stmt1; -s1 -1 -insert into t1 values (0); -execute stmt1; -s1 -0 -1 -deallocate prepare stmt1; -drop view v2; -drop table t1, t2; -create table t1 (t time); -create view v1 as select substring_index(t,':',2) as t from t1; -insert into t1 (t) values ('12:24:10'); -select substring_index(t,':',2) from t1; -substring_index(t,':',2) -12:24 -select substring_index(t,':',2) from v1; -substring_index(t,':',2) -12:24 -drop view v1; -drop table t1; -create table t1 (s1 tinyint); -create view v1 as select * from t1 where s1 <> 0 with local check option; -create view v2 as select * from v1 with cascaded check option; -insert into v2 values (0); -ERROR 44000: CHECK OPTION failed `test`.`v2` -drop view v2, v1; -drop table t1; -create table t1 (s1 int); -create view v1 as select * from t1 where s1 < 5 with check option; -insert ignore into v1 values (6); -ERROR 44000: CHECK OPTION failed `test`.`v1` -insert ignore into v1 values (6),(3); -Warnings: -Warning 1369 CHECK OPTION failed `test`.`v1` -select * from t1; -s1 -3 -drop view v1; -drop table t1; -SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; -create table t1 (s1 tinyint); -create trigger t1_bi before insert on t1 for each row set new.s1 = 500; -create view v1 as select * from t1 where s1 <> 127 with check option; -insert into v1 values (0); -ERROR 44000: CHECK OPTION failed `test`.`v1` -select * from v1; -s1 -select * from t1; -s1 -drop trigger t1_bi; -drop view v1; -drop table t1; -SET sql_mode = default; -create table t1 (s1 tinyint); -create view v1 as select * from t1 where s1 <> 0; -create view v2 as select * from v1 where s1 <> 1 with cascaded check option; -insert into v2 values (0); -ERROR 44000: CHECK OPTION failed `test`.`v2` -select * from v2; -s1 -select * from t1; -s1 -drop view v2, v1; -drop table t1; -create table t1 (a int, b char(10)); -create view v1 as select * from t1 where a != 0 with check option; -load data infile '../../std_data/loaddata3.dat' into table v1 fields terminated by '' enclosed by '' ignore 1 lines; -ERROR 44000: CHECK OPTION failed `test`.`v1` -select * from t1; -a b -1 row 1 -2 row 2 -select * from v1; -a b -1 row 1 -2 row 2 -delete from t1; -load data infile '../../std_data/loaddata3.dat' ignore into table v1 fields terminated by '' enclosed by '' ignore 1 lines; -Warnings: -Note 1265 Data truncated for column 'a' at row 1 -Note 1265 Data truncated for column 'a' at row 2 -Warning 1366 Incorrect integer value: 'error ' for column 'a' at row 3 -Warning 1369 CHECK OPTION failed `test`.`v1` -Note 1265 Data truncated for column 'a' at row 3 -Warning 1366 Incorrect integer value: 'wrong end ' for column 'a' at row 4 -Warning 1369 CHECK OPTION failed `test`.`v1` -select * from t1 order by a,b; -a b -1 row 1 -2 row 2 -3 row 3 -select * from v1 order by a,b; -a b -1 row 1 -2 row 2 -3 row 3 -drop view v1; -drop table t1; -create table t1 (a text, b text); -create view v1 as select * from t1 where a <> 'Field A' with check option; -load data infile '../../std_data/loaddata2.dat' into table v1 fields terminated by ',' enclosed by ''''; -ERROR 44000: CHECK OPTION failed `test`.`v1` -select concat('|',a,'|'), concat('|',b,'|') from t1; -concat('|',a,'|') concat('|',b,'|') -select concat('|',a,'|'), concat('|',b,'|') from v1; -concat('|',a,'|') concat('|',b,'|') -delete from t1; -load data infile '../../std_data/loaddata2.dat' ignore into table v1 fields terminated by ',' enclosed by ''''; -Warnings: -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,'|') -|Field 1| |Field 2' -Field 3,'Field 4| -|Field 5' ,'Field 6| NULL -|Field 6| | 'Field 7'| -select concat('|',a,'|'), concat('|',b,'|') from v1; -concat('|',a,'|') concat('|',b,'|') -|Field 1| |Field 2' -Field 3,'Field 4| -|Field 5' ,'Field 6| NULL -|Field 6| | 'Field 7'| -drop view v1; -drop table t1; -create table t1 (s1 smallint); -create view v1 as select * from t1 where 20 < (select (s1) from t1); -insert into v1 values (30); -ERROR HY000: The target table v1 of the INSERT is not insertable-into -create view v2 as select * from t1; -create view v3 as select * from t1 where 20 < (select (s1) from v2); -insert into v3 values (30); -ERROR HY000: The target table v3 of the INSERT is not insertable-into -create view v4 as select * from v2 where 20 < (select (s1) from t1); -insert into v4 values (30); -ERROR HY000: The target table v4 of the INSERT is not insertable-into -drop view v4, v3, v2, v1; -drop table t1; -create table t1 (a int); -create view v1 as select * from t1; -check table t1,v1; -Table Op Msg_type Msg_text -test.t1 check status OK -test.v1 check status OK -check table v1,t1; -Table Op Msg_type Msg_text -test.v1 check status OK -test.t1 check status OK -drop table t1; -check table v1; -Table Op Msg_type Msg_text -test.v1 check Error Table 'test.t1' doesn't exist -test.v1 check Error View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -test.v1 check error Corrupt -drop view v1; -create table t1 (a int); -create table t2 (a int); -create table t3 (a int); -insert into t1 values (1), (2), (3); -insert into t2 values (1), (3); -insert into t3 values (1), (2), (4); -create view v3 (a,b) as select t1.a as a, t2.a as b from t1 left join t2 on (t1.a=t2.a); -select * from t3 left join v3 on (t3.a = v3.a); -a a b -1 1 1 -2 2 NULL -4 NULL NULL -explain extended select * from t3 left join v3 on (t3.a = v3.a); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where -Warnings: -Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `b` from `test`.`t3` left join (`test`.`t1` left join `test`.`t2` on(`test`.`t2`.`a` = `test`.`t3`.`a`)) on(`test`.`t1`.`a` = `test`.`t3`.`a`) where 1 -create view v1 (a) as select a from t1; -create view v2 (a) as select a from t2; -create view v4 (a,b) as select v1.a as a, v2.a as b from v1 left join v2 on (v1.a=v2.a); -select * from t3 left join v4 on (t3.a = v4.a); -a a b -1 1 1 -2 2 NULL -4 NULL NULL -explain extended select * from t3 left join v4 on (t3.a = v4.a); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where -Warnings: -Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `b` from `test`.`t3` left join (`test`.`t1` left join (`test`.`t2`) on(`test`.`t2`.`a` = `test`.`t3`.`a`)) on(`test`.`t1`.`a` = `test`.`t3`.`a`) where 1 -prepare stmt1 from "select * from t3 left join v4 on (t3.a = v4.a);"; -execute stmt1; -a a b -1 1 1 -2 2 NULL -4 NULL NULL -execute stmt1; -a a b -1 1 1 -2 2 NULL -4 NULL NULL -deallocate prepare stmt1; -drop view v4,v3,v2,v1; -drop tables t1,t2,t3; -create table t1 (a int, primary key (a), b int); -create table t2 (a int, primary key (a)); -insert into t1 values (1,100), (2,200); -insert into t2 values (1), (3); -create view v3 (a,b) as select t1.a as a, t2.a as b from t1, t2; -update v3 set a= 10 where a=1; -select * from t1; -a b -10 100 -2 200 -select * from t2; -a -1 -3 -create view v2 (a,b) as select t1.b as a, t2.a as b from t1, t2; -set updatable_views_with_limit=NO; -update v2 set a= 10 where a=200 limit 1; -ERROR HY000: The target table v2 of the UPDATE is not updatable -set updatable_views_with_limit=DEFAULT; -select * from v3; -a b -2 1 -10 1 -2 3 -10 3 -select * from v2; -a b -100 1 -200 1 -100 3 -200 3 -set @a= 10; -set @b= 100; -prepare stmt1 from "update v3 set a= ? where a=?"; -execute stmt1 using @a,@b; -select * from v3; -a b -2 1 -10 1 -2 3 -10 3 -set @a= 300; -set @b= 10; -execute stmt1 using @a,@b; -select * from v3; -a b -2 1 -300 1 -2 3 -300 3 -deallocate prepare stmt1; -drop view v3,v2; -drop tables t1,t2; -create table t1 (a int, primary key (a), b int); -create table t2 (a int, primary key (a), b int); -insert into t2 values (1000, 2000); -create view v3 (a,b) as select t1.a as a, t2.a as b from t1, t2; -insert into v3 values (1,2); -ERROR HY000: Can not insert into join view 'test.v3' without fields list -insert into v3 select * from t2; -ERROR HY000: Can not insert into join view 'test.v3' without fields list -insert into v3(a,b) values (1,2); -ERROR HY000: Can not modify more than one base table through a join view 'test.v3' -insert into v3(a,b) select * from t2; -ERROR HY000: Can not modify more than one base table through a join view 'test.v3' -insert into v3(a) values (1); -insert into v3(b) values (10); -insert into v3(a) select a from t2; -insert into v3(b) select b from t2; -Warnings: -Warning 1048 Column 'a' cannot be null -insert into v3(a) values (1) on duplicate key update a=a+10000+VALUES(a); -select * from t1; -a b -10002 NULL -10 NULL -1000 NULL -select * from t2; -a b -1000 2000 -10 NULL -2000 NULL -0 NULL -delete from v3; -ERROR HY000: Can not delete from join view 'test.v3' -delete v3,t1 from v3,t1; -ERROR HY000: Can not delete from join view 'test.v3' -delete t1,v3 from t1,v3; -ERROR HY000: Can not delete from join view 'test.v3' -delete from t1; -prepare stmt1 from "insert into v3(a) values (?);"; -set @a= 100; -execute stmt1 using @a; -set @a= 300; -execute stmt1 using @a; -deallocate prepare stmt1; -prepare stmt1 from "insert into v3(a) select ?;"; -set @a= 101; -execute stmt1 using @a; -set @a= 301; -execute stmt1 using @a; -deallocate prepare stmt1; -select * from v3; -a b -100 0 -100 10 -100 1000 -100 2000 -101 0 -101 10 -101 1000 -101 2000 -300 0 -300 10 -300 1000 -300 2000 -301 0 -301 10 -301 1000 -301 2000 -drop view v3; -drop tables t1,t2; -create table t1(f1 int); -create view v1 as select f1 from t1; -select * from v1 where F1 = 1; -f1 -drop view v1; -drop table t1; -create table t1(c1 int); -create table t2(c2 int); -insert into t1 values (1),(2),(3); -insert into t2 values (1); -SELECT c1 FROM t1 WHERE c1 IN (SELECT c2 FROM t2); -c1 -1 -SELECT c1 FROM t1 WHERE EXISTS (SELECT c2 FROM t2 WHERE c2 = c1); -c1 -1 -create view v1 as SELECT c1 FROM t1 WHERE c1 IN (SELECT c2 FROM t2); -create view v2 as SELECT c1 FROM t1 WHERE EXISTS (SELECT c2 FROM t2 WHERE c2 = c1); -select * from v1; -c1 -1 -select * from v2; -c1 -1 -select * from (select c1 from v2) X; -c1 -1 -drop view v2, v1; -drop table t1, t2; -CREATE TABLE t1 (C1 INT, C2 INT); -CREATE TABLE t2 (C2 INT); -CREATE VIEW v1 AS SELECT C2 FROM t2; -CREATE VIEW v2 AS SELECT C1 FROM t1 LEFT OUTER JOIN v1 USING (C2); -SELECT * FROM v2; -C1 -drop view v2, v1; -drop table t1, t2; -create table t1 (col1 char(5),col2 int,col3 int); -insert into t1 values ('one',10,25), ('two',10,50), ('two',10,50), ('one',20,25), ('one',30,25); -create view v1 as select * from t1; -select col1,group_concat(col2,col3) from t1 group by col1; -col1 group_concat(col2,col3) -one 1025,2025,3025 -two 1050,1050 -select col1,group_concat(col2,col3) from v1 group by col1; -col1 group_concat(col2,col3) -one 1025,2025,3025 -two 1050,1050 -drop view v1; -drop table t1; -create table t1 (s1 int, s2 char); -create view v1 as select s1, s2 from t1; -select s2 from v1 vq1 where 2 = (select count(*) from v1 vq2 having vq1.s2 = vq2.s2); -ERROR 42S22: Unknown column 'vq2.s2' in 'having clause' -select s2 from v1 vq1 where 2 = (select count(*) aa from v1 vq2 having vq1.s2 = aa); -s2 -drop view v1; -drop table t1; -CREATE TABLE t1 (a1 int); -CREATE TABLE t2 (a2 int); -INSERT INTO t1 VALUES (1), (2), (3), (4); -INSERT INTO t2 VALUES (1), (2), (3); -CREATE VIEW v1(a,b) AS SELECT a1,a2 FROM t1 JOIN t2 ON a1=a2 WHERE a1>1; -SELECT * FROM v1; -a b -2 2 -3 3 -CREATE TABLE t3 SELECT * FROM v1; -SELECT * FROM t3; -a b -2 2 -3 3 -DROP VIEW v1; -DROP TABLE t1,t2,t3; -create table t1 (a int); -create table t2 like t1; -create table t3 like t1; -create view v1 as select t1.a x, t2.a y from t1 join t2 where t1.a=t2.a; -insert into t3 select x from v1; -insert into t2 select x from v1; -drop view v1; -drop table t1,t2,t3; -CREATE TABLE t1 (col1 int PRIMARY KEY, col2 varchar(10)); -INSERT INTO t1 VALUES(1,'trudy'); -INSERT INTO t1 VALUES(2,'peter'); -INSERT INTO t1 VALUES(3,'sanja'); -INSERT INTO t1 VALUES(4,'monty'); -INSERT INTO t1 VALUES(5,'david'); -INSERT INTO t1 VALUES(6,'kent'); -INSERT INTO t1 VALUES(7,'carsten'); -INSERT INTO t1 VALUES(8,'ranger'); -INSERT INTO t1 VALUES(10,'matt'); -CREATE TABLE t2 (col1 int, col2 int, col3 char(1)); -INSERT INTO t2 VALUES (1,1,'y'); -INSERT INTO t2 VALUES (1,2,'y'); -INSERT INTO t2 VALUES (2,1,'n'); -INSERT INTO t2 VALUES (3,1,'n'); -INSERT INTO t2 VALUES (4,1,'y'); -INSERT INTO t2 VALUES (4,2,'n'); -INSERT INTO t2 VALUES (4,3,'n'); -INSERT INTO t2 VALUES (6,1,'n'); -INSERT INTO t2 VALUES (8,1,'y'); -CREATE VIEW v1 AS SELECT * FROM t1; -SELECT a.col1,a.col2,b.col2,b.col3 -FROM t1 a LEFT JOIN t2 b ON a.col1=b.col1 -WHERE b.col2 IS NULL OR -b.col2=(SELECT MAX(col2) FROM t2 b WHERE b.col1=a.col1); -col1 col2 col2 col3 -1 trudy 2 y -2 peter 1 n -3 sanja 1 n -4 monty 3 n -5 david NULL NULL -6 kent 1 n -7 carsten NULL NULL -8 ranger 1 y -10 matt NULL NULL -SELECT a.col1,a.col2,b.col2,b.col3 -FROM v1 a LEFT JOIN t2 b ON a.col1=b.col1 -WHERE b.col2 IS NULL OR -b.col2=(SELECT MAX(col2) FROM t2 b WHERE b.col1=a.col1); -col1 col2 col2 col3 -1 trudy 2 y -2 peter 1 n -3 sanja 1 n -4 monty 3 n -5 david NULL NULL -6 kent 1 n -7 carsten NULL NULL -8 ranger 1 y -10 matt NULL NULL -CREATE VIEW v2 AS SELECT * FROM t2; -SELECT a.col1,a.col2,b.col2,b.col3 -FROM v2 b RIGHT JOIN v1 a ON a.col1=b.col1 -WHERE b.col2 IS NULL OR -b.col2=(SELECT MAX(col2) FROM v2 b WHERE b.col1=a.col1); -col1 col2 col2 col3 -1 trudy 2 y -2 peter 1 n -3 sanja 1 n -4 monty 3 n -5 david NULL NULL -6 kent 1 n -7 carsten NULL NULL -8 ranger 1 y -10 matt NULL NULL -SELECT a.col1,a.col2,b.col2,b.col3 -FROM v2 b RIGHT JOIN v1 a ON a.col1=b.col1 -WHERE a.col1 IN (1,5,9) AND -(b.col2 IS NULL OR -b.col2=(SELECT MAX(col2) FROM v2 b WHERE b.col1=a.col1)); -col1 col2 col2 col3 -1 trudy 2 y -5 david NULL NULL -CREATE VIEW v3 AS SELECT * FROM t1 WHERE col1 IN (1,5,9); -SELECT a.col1,a.col2,b.col2,b.col3 -FROM v2 b RIGHT JOIN v3 a ON a.col1=b.col1 -WHERE b.col2 IS NULL OR -b.col2=(SELECT MAX(col2) FROM v2 b WHERE b.col1=a.col1); -col1 col2 col2 col3 -1 trudy 2 y -5 david NULL NULL -DROP VIEW v1,v2,v3; -DROP TABLE t1,t2; -create table t1 as select 1 A union select 2 union select 3; -create table t2 as select * from t1; -create view v1 as select * from t1 where a in (select * from t2); -select * from v1 A, v1 B where A.a = B.a; -A A -1 1 -2 2 -3 3 -create table t3 as select a a,a b from t2; -create view v2 as select * from t3 where -a in (select * from t1) or b in (select * from t2); -select * from v2 A, v2 B where A.a = B.b; -a b a b -1 1 1 1 -2 2 2 2 -3 3 3 3 -drop view v1, v2; -drop table t1, t2, t3; -CREATE TABLE t1 (a int); -CREATE TABLE t2 (b int); -INSERT INTO t1 VALUES (1), (2), (3), (4); -INSERT INTO t2 VALUES (4), (2); -CREATE VIEW v1 AS SELECT * FROM t1,t2 WHERE t1.a=t2.b; -SELECT * FROM v1; -a b -2 2 -4 4 -CREATE VIEW v2 AS SELECT * FROM v1; -SELECT * FROM v2; -a b -2 2 -4 4 -DROP VIEW v2,v1; -DROP TABLE t1, t2; -create table t1 (a int); -create view v1 as select sum(a) from t1 group by a; -create procedure p1() -begin -select * from v1; -end// -call p1(); -sum(a) -call p1(); -sum(a) -drop procedure p1; -drop view v1; -drop table t1; -CREATE TABLE t1(a char(2) primary key, b char(2)); -CREATE TABLE t2(a char(2), b char(2), index i(a)); -INSERT INTO t1 VALUES ('a','1'), ('b','2'); -INSERT INTO t2 VALUES ('a','5'), ('a','6'), ('b','5'), ('b','6'); -CREATE VIEW v1 AS -SELECT t1.b as c, t2.b as d FROM t1,t2 WHERE t1.a=t2.a; -SELECT d, c FROM v1 ORDER BY d,c; -d c -5 1 -5 2 -6 1 -6 2 -DROP VIEW v1; -DROP TABLE t1, t2; -create table t1 (s1 int); -create view v1 as select sum(distinct s1) from t1; -select * from v1; -sum(distinct s1) -NULL -drop view v1; -create view v1 as select avg(distinct s1) from t1; -select * from v1; -avg(distinct s1) -NULL -drop view v1; -drop table t1; -create view v1 as select cast(1 as decimal); -select * from v1; -cast(1 as decimal) -1 -drop view v1; -create table t1(f1 int); -create table t2(f2 int); -insert into t1 values(1),(2),(3); -insert into t2 values(1),(2),(3); -create view v1 as select * from t1,t2 where f1=f2; -create table t3 (f1 int, f2 int); -insert into t3 select * from v1 order by 1; -select * from t3; -f1 f2 -1 1 -2 2 -3 3 -drop view v1; -drop table t1,t2,t3; -create view v1 as select '\\','\\shazam'; -select * from v1; -\ \shazam -\ \shazam -drop view v1; -create view v1 as select '\'','\shazam'; -select * from v1; -' shazam -' shazam -drop view v1; -create view v1 as select 'k','K'; -select * from v1; -k My_exp_K -k K -drop view v1; -create table t1 (s1 int); -create view v1 as select s1, 's1' from t1; -select * from v1; -s1 My_exp_s1 -drop view v1; -create view v1 as select 's1', s1 from t1; -select * from v1; -My_exp_s1 s1 -drop view v1; -create view v1 as select 's1', s1, 1 as My_exp_s1 from t1; -select * from v1; -My_exp_1_s1 s1 My_exp_s1 -drop view v1; -create view v1 as select 1 as My_exp_s1, 's1', s1 from t1; -select * from v1; -My_exp_s1 My_exp_1_s1 s1 -drop view v1; -create view v1 as select 1 as s1, 's1', 's1' from t1; -select * from v1; -s1 My_exp_s1 My_exp_1_s1 -drop view v1; -create view v1 as select 's1', 's1', 1 as s1 from t1; -select * from v1; -My_exp_1_s1 My_exp_s1 s1 -drop view v1; -create view v1 as select s1, 's1', 's1' from t1; -select * from v1; -s1 My_exp_s1 My_exp_1_s1 -drop view v1; -create view v1 as select 's1', 's1', s1 from t1; -select * from v1; -My_exp_1_s1 My_exp_s1 s1 -drop view v1; -create view v1 as select 1 as s1, 's1', s1 from t1; -ERROR 42S21: Duplicate column name 's1' -create view v1 as select 's1', s1, 1 as s1 from t1; -ERROR 42S21: Duplicate column name 's1' -drop table t1; -create view v1(k, K) as select 1,2; -ERROR 42S21: Duplicate column name 'K' -create view v1 as SELECT TIME_FORMAT(SEC_TO_TIME(3600),'%H:%i') as t; -select * from v1; -t -01:00 -drop view v1; -create table t1 (a timestamp default now()); -create table t2 (b timestamp default now()); -create view v1 as select a,b,t1.a < now() from t1,t2 where t1.a < now(); -SHOW CREATE VIEW v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a`,`t2`.`b` AS `b`,`t1`.`a` < current_timestamp() AS `t1.a < now()` from (`t1` join `t2`) where `t1`.`a` < current_timestamp() latin1 latin1_swedish_ci -drop view v1; -drop table t1, t2; -CREATE TABLE t1 ( a varchar(50) ); -CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = CURRENT_USER(); -SHOW CREATE VIEW v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` where `t1`.`a` = current_user() latin1 latin1_swedish_ci -DROP VIEW v1; -CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = VERSION(); -SHOW CREATE VIEW v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` where `t1`.`a` = version() latin1 latin1_swedish_ci -DROP VIEW v1; -CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = DATABASE(); -SHOW CREATE VIEW v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` where `t1`.`a` = database() latin1 latin1_swedish_ci -DROP VIEW v1; -DROP TABLE t1; -CREATE TABLE t1 (col1 time); -CREATE TABLE t2 (col1 time); -CREATE VIEW v1 AS SELECT CONVERT_TZ(col1,'GMT','MET') FROM t1; -CREATE VIEW v2 AS SELECT CONVERT_TZ(col1,'GMT','MET') FROM t2; -CREATE VIEW v3 AS SELECT CONVERT_TZ(col1,'GMT','MET') FROM t1; -CREATE VIEW v4 AS SELECT CONVERT_TZ(col1,'GMT','MET') FROM t2; -CREATE VIEW v5 AS SELECT CONVERT_TZ(col1,'GMT','MET') FROM t1; -CREATE VIEW v6 AS SELECT CONVERT_TZ(col1,'GMT','MET') FROM t2; -DROP TABLE t1; -CHECK TABLE v1, v2, v3, v4, v5, v6; -Table Op Msg_type Msg_text -test.v1 check Error Table 'test.t1' doesn't exist -test.v1 check Error View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -test.v1 check error Corrupt -test.v2 check status OK -test.v3 check Error Table 'test.t1' doesn't exist -test.v3 check Error View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -test.v3 check error Corrupt -test.v4 check status OK -test.v5 check Error Table 'test.t1' doesn't exist -test.v5 check Error View 'test.v5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -test.v5 check error Corrupt -test.v6 check status OK -drop view v1, v2, v3, v4, v5, v6; -drop table t2; -drop function if exists f1; -drop function if exists f2; -CREATE TABLE t1 (col1 time); -CREATE TABLE t2 (col1 time); -CREATE TABLE t3 (col1 time); -create function f1 () returns int return (select max(col1) from t1); -create function f2 () returns int return (select max(col1) from t2); -CREATE VIEW v1 AS SELECT f1() FROM t3; -CREATE VIEW v2 AS SELECT f2() FROM t3; -CREATE VIEW v3 AS SELECT f1() FROM t3; -CREATE VIEW v4 AS SELECT f2() FROM t3; -CREATE VIEW v5 AS SELECT f1() FROM t3; -CREATE VIEW v6 AS SELECT f2() FROM t3; -drop function f1; -CHECK TABLE v1, v2, v3, v4, v5, v6; -Table Op Msg_type Msg_text -test.v1 check Error FUNCTION test.f1 does not exist -test.v1 check Error View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -test.v1 check error Corrupt -test.v2 check status OK -test.v3 check Error FUNCTION test.f1 does not exist -test.v3 check Error View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -test.v3 check error Corrupt -test.v4 check status OK -test.v5 check Error FUNCTION test.f1 does not exist -test.v5 check Error View 'test.v5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -test.v5 check error Corrupt -test.v6 check status OK -create function f1 () returns int return (select max(col1) from t1); -DROP TABLE t1; -CHECK TABLE v1, v2, v3, v4, v5, v6; -Table Op Msg_type Msg_text -test.v1 check status OK -test.v2 check status OK -test.v3 check status OK -test.v4 check status OK -test.v5 check status OK -test.v6 check status OK -drop function f1; -drop function f2; -drop view v1, v2, v3, v4, v5, v6; -drop table t2,t3; -create table t1 (f1 date); -insert into t1 values ('2005-01-01'),('2005-02-02'); -create view v1 as select * from t1; -select * from v1 where f1='2005.02.02'; -f1 -2005-02-02 -select * from v1 where '2005.02.02'=f1; -f1 -2005-02-02 -drop view v1; -drop table t1; -CREATE VIEW v1 AS SELECT ENCRYPT("dhgdhgd"); -SELECT * FROM v1; -drop view v1; -CREATE VIEW v1 AS SELECT SUBSTRING_INDEX("dkjhgd:kjhdjh", ":", 1); -SELECT * FROM v1; -SUBSTRING_INDEX("dkjhgd:kjhdjh", ":", 1) -dkjhgd -drop view v1; -create table t1 (f59 int, f60 int, f61 int); -insert into t1 values (19,41,32); -create view v1 as select f59, f60 from t1 where f59 in -(select f59 from t1); -update v1 set f60=2345; -ERROR HY000: The target table v1 of the UPDATE is not updatable -drop view v1; -drop table t1; -create table t1 (s1 int); -create view v1 as select var_samp(s1) from t1; -show create view v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select var_samp(`t1`.`s1`) AS `var_samp(s1)` from `t1` latin1 latin1_swedish_ci -drop view v1; -drop table t1; -set sql_mode='strict_all_tables'; -CREATE TABLE t1 (col1 INT NOT NULL, col2 INT NOT NULL); -CREATE VIEW v1 (vcol1) AS SELECT col1 FROM t1; -CREATE VIEW v2 (vcol1) AS SELECT col1 FROM t1 WHERE col2 > 2; -INSERT INTO t1 (col1) VALUES(12); -ERROR HY000: Field 'col2' doesn't have a default value -INSERT INTO v1 (vcol1) VALUES(12); -ERROR HY000: Field of view 'test.v1' underlying table doesn't have a default value -INSERT INTO v2 (vcol1) VALUES(12); -ERROR HY000: Field of view 'test.v2' underlying table doesn't have a default value -set sql_mode=default; -drop view v2,v1; -drop table t1; -create table t1 (f1 int); -insert into t1 values (1); -create view v1 as select f1 from t1; -select f1 as alias from v1; -alias -1 -drop view v1; -drop table t1; -CREATE TABLE t1 (s1 int, s2 int); -INSERT INTO t1 VALUES (1,2); -CREATE VIEW v1 AS SELECT s2 AS s1, s1 AS s2 FROM t1; -SELECT * FROM v1; -s1 s2 -2 1 -CREATE PROCEDURE p1 () SELECT * FROM v1; -CALL p1(); -s1 s2 -2 1 -ALTER VIEW v1 AS SELECT s1 AS s1, s2 AS s2 FROM t1; -CALL p1(); -s1 s2 -1 2 -DROP VIEW v1; -CREATE VIEW v1 AS SELECT s2 AS s1, s1 AS s2 FROM t1; -CALL p1(); -s1 s2 -2 1 -DROP PROCEDURE p1; -DROP VIEW v1; -DROP TABLE t1; -create table t1 (f1 int, f2 int); -create view v1 as select f1 as f3, f2 as f1 from t1; -insert into t1 values (1,3),(2,1),(3,2); -select * from v1 order by f1; -f3 f1 -2 1 -3 2 -1 3 -drop view v1; -drop table t1; -CREATE TABLE t1 (f1 char); -INSERT INTO t1 VALUES ('A'); -CREATE VIEW v1 AS SELECT * FROM t1; -INSERT INTO t1 VALUES('B'); -SELECT * FROM v1; -f1 -A -B -SELECT * FROM t1; -f1 -A -B -DROP VIEW v1; -DROP TABLE t1; -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 test.p1 does not exist -CREATE PROCEDURE p1 ( ) -BEGIN -DO (SELECT @next := IFNULL(max(bug_table_seq),0) + 1 FROM v1); -INSERT INTO t1 VALUES (1); -END // -CALL p1(); -DROP PROCEDURE p1; -DROP VIEW v1; -DROP TABLE t1; -create table t1(f1 datetime); -insert into t1 values('2005.01.01 12:0:0'); -create view v1 as select f1, subtime(f1, '1:1:1') as sb from t1; -select * from v1; -f1 sb -2005-01-01 12:00:00 2005-01-01 10:58:59 -drop view v1; -drop table t1; -CREATE TABLE t1 ( -aid int PRIMARY KEY, -fn varchar(20) NOT NULL, -ln varchar(20) NOT NULL -); -CREATE TABLE t2 ( -aid int NOT NULL, -pid int NOT NULL -); -INSERT INTO t1 VALUES(1,'a','b'), (2,'c','d'); -INSERT INTO t2 values (1,1), (2,1), (2,2); -CREATE VIEW v1 AS SELECT t1.*,t2.pid FROM t1,t2 WHERE t1.aid = t2.aid; -SELECT pid,GROUP_CONCAT(CONCAT(fn,' ',ln) ORDER BY 1) FROM t1,t2 -WHERE t1.aid = t2.aid GROUP BY pid; -pid GROUP_CONCAT(CONCAT(fn,' ',ln) ORDER BY 1) -1 a b,c d -2 c d -SELECT pid,GROUP_CONCAT(CONCAT(fn,' ',ln) ORDER BY 1) FROM v1 GROUP BY pid; -pid GROUP_CONCAT(CONCAT(fn,' ',ln) ORDER BY 1) -1 a b,c d -2 c d -DROP VIEW v1; -DROP TABLE t1,t2; -CREATE TABLE t1 (id int PRIMARY KEY, f varchar(255)); -CREATE VIEW v1 AS SELECT id, f FROM t1 WHERE id <= 2; -INSERT INTO t1 VALUES (2, 'foo2'); -INSERT INTO t1 VALUES (1, 'foo1'); -SELECT * FROM v1; -id f -1 foo1 -2 foo2 -SELECT * FROM v1; -id f -1 foo1 -2 foo2 -DROP VIEW v1; -DROP TABLE t1; -CREATE TABLE t1 (pk int PRIMARY KEY, b int); -CREATE TABLE t2 (pk int PRIMARY KEY, fk int, INDEX idx(fk)); -CREATE TABLE t3 (pk int PRIMARY KEY, fk int, INDEX idx(fk)); -CREATE TABLE t4 (pk int PRIMARY KEY, fk int, INDEX idx(fk)); -CREATE TABLE t5 (pk int PRIMARY KEY, fk int, INDEX idx(fk)); -CREATE VIEW v1 AS -SELECT t1.pk as a FROM t1,t2,t3,t4,t5 -WHERE t1.b IS NULL AND -t1.pk=t2.fk AND t2.pk=t3.fk AND t3.pk=t4.fk AND t4.pk=t5.fk; -SELECT a FROM v1; -a -DROP VIEW v1; -DROP TABLE t1,t2,t3,t4,t5; -create view v1 as select timestampdiff(day,'1997-01-01 00:00:00','1997-01-02 00:00:00') as f1; -select * from v1; -f1 -1 -drop view v1; -create table t1(a int); -create procedure p1() create view v1 as select * from t1; -drop table t1; -call p1(); -ERROR 42S02: Table 'test.t1' doesn't exist -call p1(); -ERROR 42S02: Table 'test.t1' doesn't exist -drop procedure p1; -create table t1 (f1 int); -create table t2 (f1 int); -insert into t1 values (1); -insert into t2 values (2); -create view v1 as select * from t1 union select * from t2 union all select * from t2; -select * from v1; -f1 -1 -2 -2 -drop view v1; -drop table t1,t2; -CREATE TEMPORARY TABLE t1 (a int); -CREATE FUNCTION f1 () RETURNS int RETURN (SELECT COUNT(*) FROM t1); -CREATE VIEW v1 AS SELECT f1(); -ERROR HY000: View's SELECT refers to a temporary table 't1' -DROP FUNCTION f1; -DROP TABLE t1; -DROP TABLE IF EXISTS t1; -DROP VIEW IF EXISTS v1; -CREATE TABLE t1 (f4 CHAR(5)); -CREATE VIEW v1 AS SELECT * FROM t1; -DESCRIBE v1; -Field Type Null Key Default Extra -f4 char(5) YES NULL -ALTER TABLE t1 CHANGE COLUMN f4 f4x CHAR(5); -DESCRIBE v1; -ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -DROP TABLE t1; -DROP VIEW v1; -create table t1 (f1 char); -create view v1 as select strcmp(f1,'a') from t1; -select * from v1; -strcmp(f1,'a') -drop view v1; -drop table t1; -create table t1 (f1 int, f2 int,f3 int); -insert into t1 values (1,10,20),(2,0,0); -create view v1 as select * from t1; -select if(sum(f1)>1,f2,f3) from v1 group by f1; -if(sum(f1)>1,f2,f3) -20 -0 -drop view v1; -drop table t1; -create table t1 ( -r_object_id char(16) NOT NULL, -group_name varchar(32) NOT NULL -); -create table t2 ( -r_object_id char(16) NOT NULL, -i_position int(11) NOT NULL, -users_names varchar(32) default NULL -); -create view v1 as select r_object_id, group_name from t1; -create view v2 as select r_object_id, i_position, users_names from t2; -create unique index r_object_id on t1(r_object_id); -create index group_name on t1(group_name); -create unique index r_object_id_i_position on t2(r_object_id,i_position); -create index users_names on t2(users_names); -insert into t1 values('120001a080000542','tstgroup1'); -insert into t2 values('120001a080000542',-1, 'guser01'); -insert into t2 values('120001a080000542',-2, 'guser02'); -select v1.r_object_id, v2.users_names from v1, v2 -where (v1.group_name='tstgroup1') and v2.r_object_id=v1.r_object_id -order by users_names; -r_object_id users_names -120001a080000542 guser01 -120001a080000542 guser02 -drop view v1, v2; -drop table t1, t2; -create table t1 (s1 int); -create view abc as select * from t1 as abc; -drop table t1; -drop view abc; -flush status; -create table t1(f1 char(1)); -create view v1 as select * from t1; -select * from (select f1 as f2, f1 as f3 from v1) v where v.f2='a'; -f2 f3 -show status like "Created_tmp%"; -Variable_name Value -Created_tmp_disk_tables 0 -Created_tmp_files 0 -Created_tmp_tables 0 -drop view v1; -drop table t1; -set @tmp=@@optimizer_switch; -set @@optimizer_switch='derived_merge=OFF'; -create table t1(f1 char(1)); -create view v1 as select * from t1; -select * from (select f1 as f2, f1 as f3 from v1) v where v.f2='a'; -f2 f3 -show status like "Created_tmp%"; -Variable_name Value -Created_tmp_disk_tables 0 -Created_tmp_files 0 -Created_tmp_tables 1 -drop view v1; -drop table t1; -set @@optimizer_switch=@tmp; -create view v1 as SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET'); -select * from v1; -CONVERT_TZ('2004-01-01 12:00:00','GMT','MET') -NULL -drop view v1; -CREATE TABLE t1 (date DATE NOT NULL); -INSERT INTO t1 VALUES ('2005-09-06'); -CREATE VIEW v1 AS SELECT DAYNAME(date) FROM t1; -SHOW CREATE VIEW v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select dayname(`t1`.`date`) AS `DAYNAME(date)` from `t1` latin1 latin1_swedish_ci -CREATE VIEW v2 AS SELECT DAYOFWEEK(date) FROM t1; -SHOW CREATE VIEW v2; -View Create View character_set_client collation_connection -v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select dayofweek(`t1`.`date`) AS `DAYOFWEEK(date)` from `t1` latin1 latin1_swedish_ci -CREATE VIEW v3 AS SELECT WEEKDAY(date) FROM t1; -SHOW CREATE VIEW v3; -View Create View character_set_client collation_connection -v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select weekday(`t1`.`date`) AS `WEEKDAY(date)` from `t1` latin1 latin1_swedish_ci -SELECT DAYNAME('2005-09-06'); -DAYNAME('2005-09-06') -Tuesday -SELECT DAYNAME(date) FROM t1; -DAYNAME(date) -Tuesday -SELECT * FROM v1; -DAYNAME(date) -Tuesday -SELECT DAYOFWEEK('2005-09-06'); -DAYOFWEEK('2005-09-06') -3 -SELECT DAYOFWEEK(date) FROM t1; -DAYOFWEEK(date) -3 -SELECT * FROM v2; -DAYOFWEEK(date) -3 -SELECT WEEKDAY('2005-09-06'); -WEEKDAY('2005-09-06') -1 -SELECT WEEKDAY(date) FROM t1; -WEEKDAY(date) -1 -SELECT * FROM v3; -WEEKDAY(date) -1 -DROP TABLE t1; -DROP VIEW v1, v2, v3; -CREATE TABLE t1 ( a int, b int ); -INSERT INTO t1 VALUES (1,1),(2,2),(3,3); -CREATE VIEW v1 AS SELECT a,b FROM t1; -SELECT t1.a FROM t1 GROUP BY t1.a HAVING a > 1; -a -2 -3 -SELECT v1.a FROM v1 GROUP BY v1.a HAVING a > 1; -a -2 -3 -DROP VIEW v1; -DROP TABLE t1; -CREATE TABLE t1 ( a int, b int ); -INSERT INTO t1 VALUES (1,1),(2,2),(3,3); -CREATE VIEW v1 AS SELECT a,b FROM t1; -SELECT t1.a FROM t1 GROUP BY t1.a HAVING t1.a > 1; -a -2 -3 -SELECT v1.a FROM v1 GROUP BY v1.a HAVING v1.a > 1; -a -2 -3 -SELECT t_1.a FROM t1 AS t_1 GROUP BY t_1.a HAVING t_1.a IN (1,2,3); -a -1 -2 -3 -SELECT v_1.a FROM v1 AS v_1 GROUP BY v_1.a HAVING v_1.a IN (1,2,3); -a -1 -2 -3 -DROP VIEW v1; -DROP TABLE t1; -CREATE TABLE t1 (a INT, b INT, INDEX(a,b)); -CREATE TABLE t2 LIKE t1; -CREATE TABLE t3 (a INT); -INSERT INTO t1 VALUES (1,1),(2,2),(3,3); -INSERT INTO t2 VALUES (1,1),(2,2),(3,3); -INSERT INTO t3 VALUES (1),(2),(3); -CREATE VIEW v1 AS SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a AND t1.b=t2.b; -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 1 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 1 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 index -1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) -DROP VIEW v1,v2; -DROP TABLE t1,t2,t3; -create table t1 (f1 int); -create view v1 as select t1.f1 as '123 -456' from t1; -select * from v1; -123 -456 -drop view v1; -drop table t1; -create table t1 (f1 int, f2 int); -insert into t1 values(1,1),(1,2),(1,3); -create view v1 as select f1 ,group_concat(f2 order by f2 asc) from t1 group by f1; -create view v2 as select f1 ,group_concat(f2 order by f2 desc) from t1 group by f1; -select * from v1; -f1 group_concat(f2 order by f2 asc) -1 1,2,3 -select * from v2; -f1 group_concat(f2 order by f2 desc) -1 3,2,1 -drop view v1,v2; -drop table t1; -create table t1 (x int, y int); -create table t2 (x int, y int, z int); -create table t3 (x int, y int, z int); -create table t4 (x int, y int, z int); -create view v1 as -select t1.x -from ( -(t1 join t2 on ((t1.y = t2.y))) -join -(t3 left join t4 on (t3.y = t4.y) and (t3.z = t4.z)) -); -prepare stmt1 from "select count(*) from v1 where x = ?"; -set @parm1=1; -execute stmt1 using @parm1; -count(*) -0 -execute stmt1 using @parm1; -count(*) -0 -drop view v1; -drop table t1,t2,t3,t4; -CREATE TABLE t1(id INT); -CREATE VIEW v1 AS SELECT id FROM t1; -OPTIMIZE TABLE v1; -Table Op Msg_type Msg_text -test.v1 optimize Error 'test.v1' is not of type 'BASE TABLE' -test.v1 optimize status Operation failed -ANALYZE TABLE v1; -Table Op Msg_type Msg_text -test.v1 analyze Error 'test.v1' is not of type 'BASE TABLE' -test.v1 analyze status Operation failed -REPAIR TABLE v1; -Table Op Msg_type Msg_text -test.v1 repair Error 'test.v1' is not of type 'BASE TABLE' -test.v1 repair status Operation failed -DROP TABLE t1; -OPTIMIZE TABLE v1; -Table Op Msg_type Msg_text -test.v1 optimize Error 'test.v1' is not of type 'BASE TABLE' -test.v1 optimize status Operation failed -ANALYZE TABLE v1; -Table Op Msg_type Msg_text -test.v1 analyze Error 'test.v1' is not of type 'BASE TABLE' -test.v1 analyze status Operation failed -REPAIR TABLE v1; -Table Op Msg_type Msg_text -test.v1 repair Error 'test.v1' is not of type 'BASE TABLE' -test.v1 repair status Operation failed -DROP VIEW v1; -create definer = current_user() sql security invoker view v1 as select 1; -show create view v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `v1` AS select 1 AS `1` latin1 latin1_swedish_ci -drop view v1; -create definer = current_user sql security invoker view v1 as select 1; -show create view v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `v1` AS select 1 AS `1` latin1 latin1_swedish_ci -drop view v1; -create table t1 (id INT, primary key(id)); -insert into t1 values (1),(2); -create view v1 as select * from t1; -explain select id from v1 order by id; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 4 NULL 2 Using index -drop view v1; -drop table t1; -create table t1(f1 int, f2 int); -insert into t1 values (null, 10), (null,2); -select f1, sum(f2) from t1 group by f1; -f1 sum(f2) -NULL 12 -create view v1 as select * from t1; -select f1, sum(f2) from v1 group by f1; -f1 sum(f2) -NULL 12 -drop view v1; -drop table t1; -drop procedure if exists p1; -create procedure p1 () deterministic -begin -create view v1 as select 1; -end; -// -call p1(); -show create view v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `1` latin1 latin1_swedish_ci -drop view v1; -drop procedure p1; -CREATE VIEW v1 AS SELECT 42 AS Meaning; -DROP FUNCTION IF EXISTS f1; -CREATE FUNCTION f1() RETURNS INTEGER -BEGIN -DECLARE retn INTEGER; -SELECT Meaning FROM v1 INTO retn; -RETURN retn; -END -// -CREATE VIEW v2 AS SELECT f1(); -select * from v2; -f1() -42 -drop view v2,v1; -drop function f1; -create table t1 (id numeric, warehouse_id numeric); -create view v1 as select id from t1; -create view v2 as -select t1.warehouse_id, v1.id as receipt_id -from t1, v1 where t1.id = v1.id; -insert into t1 (id, warehouse_id) values(3, 2); -insert into t1 (id, warehouse_id) values(4, 2); -insert into t1 (id, warehouse_id) values(5, 1); -select v2.receipt_id as alias1, v2.receipt_id as alias2 from v2 -order by v2.receipt_id; -alias1 alias2 -3 3 -4 4 -5 5 -drop view v2, v1; -drop table t1; -CREATE TABLE t1 (a int PRIMARY KEY, b int); -INSERT INTO t1 VALUES (2,20), (3,10), (1,10), (0,30), (5,10); -CREATE VIEW v1 AS SELECT * FROM t1; -SELECT MAX(a) FROM t1; -MAX(a) -5 -SELECT MAX(a) FROM v1; -MAX(a) -5 -EXPLAIN SELECT MAX(a) FROM t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away -EXPLAIN SELECT MAX(a) FROM v1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away -SELECT MIN(a) FROM t1; -MIN(a) -0 -SELECT MIN(a) FROM v1; -MIN(a) -0 -EXPLAIN SELECT MIN(a) FROM t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away -EXPLAIN SELECT MIN(a) FROM v1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away -DROP VIEW v1; -DROP TABLE t1; -CREATE TABLE t1 (x varchar(10)); -INSERT INTO t1 VALUES (null), ('foo'), ('bar'), (null); -CREATE VIEW v1 AS SELECT * FROM t1; -SELECT IF(x IS NULL, 'blank', 'not blank') FROM v1 GROUP BY x; -IF(x IS NULL, 'blank', 'not blank') -blank -not blank -not blank -SELECT IF(x IS NULL, 'blank', 'not blank') AS x FROM t1 GROUP BY x; -x -blank -not blank -not blank -Warnings: -Warning 1052 Column 'x' in group statement is ambiguous -SELECT IF(x IS NULL, 'blank', 'not blank') AS x FROM v1; -x -blank -not blank -not blank -blank -SELECT IF(x IS NULL, 'blank', 'not blank') AS y FROM v1 GROUP BY y; -y -blank -not blank -SELECT IF(x IS NULL, 'blank', 'not blank') AS x FROM v1 GROUP BY x; -x -blank -not blank -not blank -Warnings: -Warning 1052 Column 'x' in group statement is ambiguous -DROP VIEW v1; -DROP TABLE t1; -drop table if exists t1; -drop view if exists v1; -create table t1 (id int); -create view v1 as select * from t1; -drop table t1; -show create view v1; -drop view v1; -// -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`t1`.`id` AS `id` from `t1` latin1 latin1_swedish_ci -create table t1(f1 int, f2 int); -create view v1 as select ta.f1 as a, tb.f1 as b from t1 ta, t1 tb where ta.f1=tb -.f1 and ta.f2=tb.f2; -insert into t1 values(1,1),(2,2); -create view v2 as select * from v1 where a > 1 with local check option; -select * from v2; -a b -2 2 -update v2 set b=3 where a=2; -select * from v2; -a b -3 3 -drop view v2, v1; -drop table t1; -CREATE TABLE t1 (a int); -INSERT INTO t1 VALUES (1), (2); -CREATE VIEW v1 AS SELECT SQRT(a) my_sqrt FROM t1; -SELECT my_sqrt FROM v1 ORDER BY my_sqrt; -my_sqrt -1 -1.4142135623730951 -DROP VIEW v1; -DROP TABLE t1; -CREATE TABLE t1 (id int PRIMARY KEY); -CREATE TABLE t2 (id int PRIMARY KEY); -INSERT INTO t1 VALUES (1), (3); -INSERT INTO t2 VALUES (1), (2), (3); -CREATE VIEW v2 AS SELECT * FROM t2; -SELECT COUNT(*) FROM t1 LEFT JOIN t2 ON t1.id=t2.id; -COUNT(*) -2 -SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id; -id id -1 1 -3 3 -SELECT COUNT(*) FROM t1 LEFT JOIN v2 ON t1.id=v2.id; -COUNT(*) -2 -DROP VIEW v2; -DROP TABLE t1, t2; -CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, -td date DEFAULT NULL, KEY idx(td)); -INSERT INTO t1 VALUES -(1, '2005-01-01'), (2, '2005-01-02'), (3, '2005-01-02'), -(4, '2005-01-03'), (5, '2005-01-04'), (6, '2005-01-05'), -(7, '2005-01-05'), (8, '2005-01-05'), (9, '2005-01-06'); -CREATE VIEW v1 AS SELECT * FROM t1; -SELECT * FROM t1 WHERE td BETWEEN CAST('2005.01.02' AS DATE) AND CAST('2005.01.04' AS DATE); -id td -2 2005-01-02 -3 2005-01-02 -4 2005-01-03 -5 2005-01-04 -SELECT * FROM v1 WHERE td BETWEEN CAST('2005.01.02' AS DATE) AND CAST('2005.01.04' AS DATE); -id td -2 2005-01-02 -3 2005-01-02 -4 2005-01-03 -5 2005-01-04 -DROP VIEW v1; -DROP TABLE t1; -create table t1 (a int); -create view v1 as select * from t1; -create view v2 as select * from v1; -drop table t1; -rename table v2 to t1; -select * from v1; -ERROR HY000: `test`.`v1` contains view recursion -drop view t1, v1; -create table t1 (a int); -create function f1() returns int -begin -declare mx int; -select max(a) from t1 into mx; -return mx; -end// -create view v1 as select f1() as a; -create view v2 as select * from v1; -drop table t1; -rename table v2 to t1; -select * from v1; -ERROR HY000: Recursive stored functions and triggers are not allowed -drop function f1; -drop view t1, v1; -create table t1 (dt datetime); -insert into t1 values (20040101000000), (20050101000000), (20060101000000); -create view v1 as select convert_tz(dt, 'UTC', 'Europe/Moscow') as ldt from t1; -select * from v1; -ldt -2004-01-01 03:00:00 -2005-01-01 03:00:00 -2006-01-01 03:00:00 -drop view v1; -create view v1 as select * from t1 where convert_tz(dt, 'UTC', 'Europe/Moscow') >= 20050101000000; -select * from v1; -dt -2005-01-01 00:00:00 -2006-01-01 00:00:00 -create view v2 as select * from v1 where dt < 20060101000000; -select * from v2; -dt -2005-01-01 00:00:00 -drop view v2; -create view v2 as select convert_tz(dt, 'UTC', 'Europe/Moscow') as ldt from v1; -select * from v2; -ldt -2005-01-01 03:00:00 -2006-01-01 03:00:00 -drop view v1, v2; -drop table t1; -CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, d datetime); -CREATE VIEW v1 AS -SELECT id, date(d) + INTERVAL TIME_TO_SEC(d) SECOND AS t, COUNT(*) -FROM t1 GROUP BY id, t; -SHOW CREATE VIEW v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`id` AS `id`,cast(`t1`.`d` as date) + interval time_to_sec(`t1`.`d`) second AS `t`,count(0) AS `COUNT(*)` from `t1` group by `t1`.`id`,cast(`t1`.`d` as date) + interval time_to_sec(`t1`.`d`) second latin1 latin1_swedish_ci -SELECT * FROM v1; -id t COUNT(*) -DROP VIEW v1; -DROP TABLE t1; -CREATE TABLE t1 (i INT, j BIGINT); -INSERT INTO t1 VALUES (1, 2), (2, 2), (3, 2); -CREATE VIEW v1 AS SELECT MIN(j) AS j FROM t1; -CREATE VIEW v2 AS SELECT MIN(i) FROM t1 WHERE j = ( SELECT * FROM v1 ); -SELECT * FROM v2; -MIN(i) -1 -DROP VIEW v2, v1; -DROP TABLE t1; -CREATE TABLE t1( -fName varchar(25) NOT NULL, -lName varchar(25) NOT NULL, -DOB date NOT NULL, -test_date date NOT NULL, -uID int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY); -INSERT INTO t1(fName, lName, DOB, test_date) VALUES -('Hank', 'Hill', '1964-09-29', '2007-01-01'), -('Tom', 'Adams', '1908-02-14', '2007-01-01'), -('Homer', 'Simpson', '1968-03-05', '2007-01-01'); -CREATE VIEW v1 AS -SELECT (year(test_date)-year(DOB)) AS Age -FROM t1 HAVING Age < 75; -SHOW CREATE VIEW v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select year(`t1`.`test_date`) - year(`t1`.`DOB`) AS `Age` from `t1` having `Age` < 75 latin1 latin1_swedish_ci -SELECT (year(test_date)-year(DOB)) AS Age FROM t1 HAVING Age < 75; -Age -43 -39 -SELECT * FROM v1; -Age -43 -39 -DROP VIEW v1; -DROP TABLE t1; -CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, a char(6) DEFAULT 'xxx'); -INSERT INTO t1(id) VALUES (1), (2), (3), (4); -INSERT INTO t1 VALUES (5,'yyy'), (6,'yyy'); -SELECT * FROM t1; -id a -1 xxx -2 xxx -3 xxx -4 xxx -5 yyy -6 yyy -CREATE VIEW v1(a, m) AS SELECT a, MIN(id) FROM t1 GROUP BY a; -SELECT * FROM v1; -a m -xxx 1 -yyy 5 -CREATE TABLE t2 SELECT * FROM v1; -INSERT INTO t2(m) VALUES (0); -SELECT * FROM t2; -a m -xxx 1 -yyy 5 -xxx 0 -DROP VIEW v1; -DROP TABLE t1,t2; -CREATE TABLE t1 (id int PRIMARY KEY, e ENUM('a','b') NOT NULL DEFAULT 'b'); -INSERT INTO t1(id) VALUES (1), (2), (3); -INSERT INTO t1 VALUES (4,'a'); -SELECT * FROM t1; -id e -1 b -2 b -3 b -4 a -CREATE VIEW v1(m, e) AS SELECT MIN(id), e FROM t1 GROUP BY e; -CREATE TABLE t2 SELECT * FROM v1; -SELECT * FROM t2; -m e -4 a -1 b -DROP VIEW v1; -DROP TABLE t1,t2; -CREATE TABLE t1 (a INT NOT NULL, b INT NULL DEFAULT NULL); -CREATE VIEW v1 AS SELECT a, b FROM t1; -INSERT IGNORE INTO v1 (b) VALUES (2); -Warnings: -Warning 1423 Field of view 'test.v1' underlying table doesn't have a default value -SET SQL_MODE = STRICT_ALL_TABLES; -INSERT INTO v1 (b) VALUES (4); -ERROR HY000: Field of view 'test.v1' underlying table doesn't have a default value -SET SQL_MODE = ''; -SELECT * FROM t1; -a b -0 2 -DROP VIEW v1; -DROP TABLE t1; -CREATE TABLE t1 (firstname text, surname text); -INSERT INTO t1 VALUES -("Bart","Simpson"),("Milhouse","van Houten"),("Montgomery","Burns"); -CREATE VIEW v1 AS SELECT CONCAT(firstname," ",surname) AS name FROM t1; -SELECT CONCAT(LEFT(name,LENGTH(name)-INSTR(REVERSE(name)," ")), -LEFT(name,LENGTH(name)-INSTR(REVERSE(name)," "))) AS f1 -FROM v1; -f1 -BartBart -Milhouse vanMilhouse van -MontgomeryMontgomery -DROP VIEW v1; -DROP TABLE t1; -CREATE TABLE t1 (i int, j int); -CREATE VIEW v1 AS SELECT COALESCE(i,j) FROM t1; -DESCRIBE v1; -Field Type Null Key Default Extra -COALESCE(i,j) int(11) YES NULL -CREATE TABLE t2 SELECT COALESCE(i,j) FROM t1; -DESCRIBE t2; -Field Type Null Key Default Extra -COALESCE(i,j) int(11) YES NULL -DROP VIEW v1; -DROP TABLE t1,t2; -CREATE TABLE t1 (s varchar(10)); -INSERT INTO t1 VALUES ('yadda'), ('yady'); -SELECT TRIM(BOTH 'y' FROM s) FROM t1; -TRIM(BOTH 'y' FROM s) -adda -ad -CREATE VIEW v1 AS SELECT TRIM(BOTH 'y' FROM s) FROM t1; -SELECT * FROM v1; -TRIM(BOTH 'y' FROM s) -adda -ad -DROP VIEW v1; -SELECT TRIM(LEADING 'y' FROM s) FROM t1; -TRIM(LEADING 'y' FROM s) -adda -ady -CREATE VIEW v1 AS SELECT TRIM(LEADING 'y' FROM s) FROM t1; -SELECT * FROM v1; -TRIM(LEADING 'y' FROM s) -adda -ady -DROP VIEW v1; -SELECT TRIM(TRAILING 'y' FROM s) FROM t1; -TRIM(TRAILING 'y' FROM s) -yadda -yad -CREATE VIEW v1 AS SELECT TRIM(TRAILING 'y' FROM s) FROM t1; -SELECT * FROM v1; -TRIM(TRAILING 'y' FROM s) -yadda -yad -DROP VIEW v1; -DROP TABLE t1; -CREATE TABLE t1 (x INT, y INT); -CREATE ALGORITHM=TEMPTABLE SQL SECURITY INVOKER VIEW v1 AS SELECT x FROM t1; -SHOW CREATE VIEW v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `v1` AS select `t1`.`x` AS `x` from `t1` latin1 latin1_swedish_ci -ALTER VIEW v1 AS SELECT x, y FROM t1; -SHOW CREATE VIEW v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `v1` AS select `t1`.`x` AS `x`,`t1`.`y` AS `y` from `t1` latin1 latin1_swedish_ci -DROP VIEW v1; -DROP TABLE t1; -CREATE TABLE t1 (s1 char); -INSERT INTO t1 VALUES ('Z'); -CREATE VIEW v1 AS SELECT s1 collate latin1_german1_ci AS col FROM t1; -CREATE VIEW v2 (col) AS SELECT s1 collate latin1_german1_ci FROM t1; -INSERT INTO v1 (col) VALUES ('b'); -INSERT INTO v2 (col) VALUES ('c'); -SELECT s1 FROM t1; -s1 -Z -b -c -DROP VIEW v1, v2; -DROP TABLE t1; -CREATE TABLE t1 (id INT); -CREATE VIEW v1 AS SELECT id FROM t1; -SHOW TABLES; -Tables_in_test -t1 -v1 -DROP VIEW v2,v1; -ERROR 42S02: Unknown VIEW: 'test.v2' -SHOW TABLES; -Tables_in_test -t1 -CREATE VIEW v1 AS SELECT id FROM t1; -DROP VIEW t1,v1; -ERROR HY000: 'test.t1' is not of type 'VIEW' -SHOW TABLES; -Tables_in_test -t1 -DROP TABLE t1; -DROP VIEW IF EXISTS v1; -set GLOBAL sql_mode=""; -set LOCAL sql_mode=""; -CREATE DATABASE bug21261DB; -USE bug21261DB; -connect root,localhost,root,,bug21261DB; -connection root; -CREATE TABLE t1 (x INT); -CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT x FROM t1; -GRANT INSERT, UPDATE ON v1 TO 'user21261'@'localhost'; -GRANT INSERT, UPDATE ON t1 TO 'user21261'@'localhost'; -CREATE TABLE t2 (y INT); -GRANT SELECT ON t2 TO 'user21261'@'localhost'; -connect user21261, localhost, user21261,, bug21261DB; -connection user21261; -INSERT INTO v1 (x) VALUES (5); -UPDATE v1 SET x=1; -connection root; -GRANT SELECT ON v1 TO 'user21261'@'localhost'; -GRANT SELECT ON t1 TO 'user21261'@'localhost'; -connection user21261; -UPDATE v1,t2 SET x=1 WHERE x=y; -connection root; -SELECT * FROM t1; -x -1 -REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user21261'@'localhost'; -DROP USER 'user21261'@'localhost'; -DROP VIEW v1; -DROP TABLE t1; -DROP DATABASE bug21261DB; -connection default; -USE test; -disconnect root; -disconnect user21261; -set GLOBAL sql_mode=default; -set LOCAL sql_mode=default; -create table t1 (f1 datetime); -create view v1 as select * from t1 where f1 between now() and now() + interval 1 minute; -show create view v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f1` AS `f1` from `t1` where `t1`.`f1` between current_timestamp() and current_timestamp() + interval 1 minute latin1 latin1_swedish_ci -drop view v1; -drop table t1; -DROP TABLE IF EXISTS t1; -DROP VIEW IF EXISTS v1; -DROP VIEW IF EXISTS v2; -CREATE TABLE t1(a INT, b INT); -CREATE DEFINER=longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost -VIEW v1 AS SELECT a FROM t1; -ERROR HY000: String 'longer_than_80_4567890123456789012345678901234567890123456789012345678' is too long for user name (should be no longer than 80) -CREATE DEFINER=some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY -VIEW v2 AS SELECT b FROM t1; -ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY' is too long for host name (should be no longer than 60) -DROP TABLE t1; -DROP FUNCTION IF EXISTS f1; -DROP FUNCTION IF EXISTS f2; -DROP VIEW IF EXISTS v1, v2; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 (i INT); -CREATE VIEW v1 AS SELECT * FROM t1; -CREATE FUNCTION f1() RETURNS INT -BEGIN -INSERT INTO v1 VALUES (0); -RETURN 0; -END | -SELECT f1(); -f1() -0 -CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t1; -CREATE FUNCTION f2() RETURNS INT -BEGIN -INSERT INTO v2 VALUES (0); -RETURN 0; -END | -SELECT f2(); -ERROR HY000: The target table v2 of the INSERT is not insertable-into -DROP FUNCTION f1; -DROP FUNCTION f2; -DROP VIEW v1, v2; -DROP TABLE t1; -CREATE TABLE t1 (s1 int); -CREATE VIEW v1 AS SELECT * FROM t1; -EXPLAIN SELECT * FROM t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 system NULL NULL NULL NULL 0 Const row not found -EXPLAIN SELECT * FROM v1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 system NULL NULL NULL NULL 0 Const row not found -INSERT INTO t1 VALUES (1), (3), (2); -EXPLAIN SELECT * FROM t1 t WHERE t.s1+1 < (SELECT MAX(t1.s1) FROM t1); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t ALL NULL NULL NULL NULL 3 Using where -2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 -EXPLAIN SELECT * FROM v1 t WHERE t.s1+1 < (SELECT MAX(t1.s1) FROM t1); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where -2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 -DROP VIEW v1; -DROP TABLE t1; -create table t1 (s1 int); -create view v1 as select s1 as a, s1 as b from t1; -insert into v1 values (1,1); -ERROR HY000: The target table v1 of the INSERT is not insertable-into -update v1 set a = 5; -drop view v1; -drop table t1; -CREATE TABLE t1(pk int PRIMARY KEY); -CREATE TABLE t2(pk int PRIMARY KEY, fk int, ver int, org int); -CREATE ALGORITHM=MERGE VIEW v1 AS -SELECT t1.* -FROM t1 JOIN t2 -ON t2.fk = t1.pk AND -t2.ver = (SELECT MAX(t.ver) FROM t2 t WHERE t.org = t2.org); -SHOW WARNINGS; -Level Code Message -SHOW CREATE VIEW v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`pk` AS `pk` from (`t1` join `t2` on(`t2`.`fk` = `t1`.`pk` and `t2`.`ver` = (select max(`t`.`ver`) from `t2` `t` where `t`.`org` = `t2`.`org`))) latin1 latin1_swedish_ci -DROP VIEW v1; -DROP TABLE t1, t2; -DROP FUNCTION IF EXISTS f1; -DROP VIEW IF EXISTS v1; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 (i INT); -INSERT INTO t1 VALUES (1); -CREATE VIEW v1 AS SELECT MAX(i) FROM t1; -CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW -SET NEW.i = (SELECT * FROM v1) + 1; -INSERT INTO t1 VALUES (1); -CREATE FUNCTION f1() RETURNS INT RETURN (SELECT * FROM v1); -UPDATE t1 SET i= f1(); -DROP FUNCTION f1; -DROP VIEW v1; -DROP TABLE t1; -CREATE TABLE t1(id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, val INT UNSIGNED NOT NULL); -CREATE VIEW v1 AS SELECT id, val FROM t1 WHERE val >= 1 AND val <= 5 WITH CHECK OPTION; -INSERT INTO v1 (val) VALUES (2); -INSERT INTO v1 (val) VALUES (4); -INSERT INTO v1 (val) VALUES (6); -ERROR 44000: CHECK OPTION failed `test`.`v1` -UPDATE v1 SET val=6 WHERE id=2; -ERROR 44000: CHECK OPTION failed `test`.`v1` -DROP VIEW v1; -DROP TABLE t1; -DROP VIEW IF EXISTS v1, v2; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 (i INT AUTO_INCREMENT PRIMARY KEY, j INT); -CREATE VIEW v1 AS SELECT j FROM t1; -CREATE VIEW v2 AS SELECT * FROM t1; -INSERT INTO t1 (j) VALUES (1); -SELECT LAST_INSERT_ID(); -LAST_INSERT_ID() -1 -INSERT INTO v1 (j) VALUES (2); -# LAST_INSERT_ID() should not change. -SELECT LAST_INSERT_ID(); -LAST_INSERT_ID() -1 -INSERT INTO v2 (j) VALUES (3); -# LAST_INSERT_ID() should be updated. -SELECT LAST_INSERT_ID(); -LAST_INSERT_ID() -3 -INSERT INTO v1 (j) SELECT j FROM t1; -# LAST_INSERT_ID() should not change. -SELECT LAST_INSERT_ID(); -LAST_INSERT_ID() -3 -SELECT * FROM t1; -i j -1 1 -2 2 -3 3 -4 1 -5 2 -6 3 -DROP VIEW v1, v2; -DROP TABLE t1; -CREATE VIEW v AS SELECT !0 * 5 AS x FROM DUAL; -SHOW CREATE VIEW v; -View Create View character_set_client collation_connection -v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select !0 * 5 AS `x` latin1 latin1_swedish_ci -SELECT !0 * 5 AS x FROM DUAL; -x -5 -SELECT * FROM v; -x -5 -DROP VIEW v; -DROP VIEW IF EXISTS v1; -CREATE VIEW v1 AS SELECT 'The\ZEnd'; -SELECT * FROM v1; -TheEnd -TheEnd -SHOW CREATE VIEW v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 'The\ZEnd' AS `TheEnd` latin1 latin1_swedish_ci -DROP VIEW v1; -CREATE TABLE t1 (mydate DATETIME); -INSERT INTO t1 VALUES -('2007-01-01'), ('2007-01-02'), ('2007-01-30'), ('2007-01-31'); -CREATE VIEW v1 AS SELECT mydate from t1; -SELECT * FROM t1 WHERE mydate BETWEEN '2007-01-01' AND '2007-01-31'; -mydate -2007-01-01 00:00:00 -2007-01-02 00:00:00 -2007-01-30 00:00:00 -2007-01-31 00:00:00 -SELECT * FROM v1 WHERE mydate BETWEEN '2007-01-01' AND '2007-01-31'; -mydate -2007-01-01 00:00:00 -2007-01-02 00:00:00 -2007-01-30 00:00:00 -2007-01-31 00:00:00 -DROP VIEW v1; -DROP TABLE t1; -CREATE TABLE t1 (a int); -CREATE TABLE t2 (b int); -INSERT INTO t1 VALUES (1), (2); -INSERT INTO t2 VALUES (1), (2); -CREATE VIEW v1 AS -SELECT t2.b FROM t1,t2 WHERE t1.a = t2.b WITH CHECK OPTION; -SELECT * FROM v1; -b -1 -2 -UPDATE v1 SET b=3; -ERROR 44000: CHECK OPTION failed `test`.`v1` -SELECT * FROM v1; -b -1 -2 -SELECT * FROM t1; -a -1 -2 -SELECT * FROM t2; -b -1 -2 -DROP VIEW v1; -DROP TABLE t1,t2; -create table t1(f1 int, f2 int); -insert into t1 values(1,2),(1,3),(1,1),(2,3),(2,1),(2,2); -select * from t1; -f1 f2 -1 2 -1 3 -1 1 -2 3 -2 1 -2 2 -create view v1 as select * from t1 order by f2; -select * from v1; -f1 f2 -1 1 -2 1 -1 2 -2 2 -1 3 -2 3 -explain extended select * from v1; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 6 100.00 Using filesort -Warnings: -Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` order by `test`.`t1`.`f2` -select * from v1 order by f1; -f1 f2 -1 2 -1 3 -1 1 -2 3 -2 1 -2 2 -explain extended select * from v1 order by f1; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 6 100.00 Using filesort -Warnings: -Note 1926 View 'test'.'v1' ORDER BY clause ignored because there is other ORDER BY clause already -Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` order by `test`.`t1`.`f1` -drop view v1; -drop table t1; -CREATE TABLE t1 ( -id int(11) NOT NULL PRIMARY KEY, -country varchar(32), -code int(11) default NULL -); -INSERT INTO t1 VALUES -(1,'ITALY',100),(2,'ITALY',200),(3,'FRANCE',100), (4,'ITALY',100); -CREATE VIEW v1 AS SELECT * FROM t1; -SELECT code, COUNT(DISTINCT country) FROM t1 GROUP BY code ORDER BY MAX(id); -code COUNT(DISTINCT country) -200 1 -100 2 -SELECT code, COUNT(DISTINCT country) FROM v1 GROUP BY code ORDER BY MAX(id); -code COUNT(DISTINCT country) -200 1 -100 2 -DROP VIEW v1; -DROP TABLE t1; -DROP VIEW IF EXISTS v1; -SELECT * FROM (SELECT 1) AS t into @w; -CREATE VIEW v1 AS SELECT * FROM (SELECT 1) AS t into @w; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'into @w' at line 1 -# Previously the following would fail. -SELECT * FROM (SELECT 1) AS t into @w; -drop view if exists view_24532_a; -drop view if exists view_24532_b; -drop table if exists table_24532; -create table table_24532 ( -a int, -b bigint, -c int(4), -d bigint(48) -); -create view view_24532_a as -select -a IS TRUE, -a IS NOT TRUE, -a IS FALSE, -a IS NOT FALSE, -a IS UNKNOWN, -a IS NOT UNKNOWN, -a is NULL, -a IS NOT NULL, -ISNULL(a), -b IS TRUE, -b IS NOT TRUE, -b IS FALSE, -b IS NOT FALSE, -b IS UNKNOWN, -b IS NOT UNKNOWN, -b is NULL, -b IS NOT NULL, -ISNULL(b), -c IS TRUE, -c IS NOT TRUE, -c IS FALSE, -c IS NOT FALSE, -c IS UNKNOWN, -c IS NOT UNKNOWN, -c is NULL, -c IS NOT NULL, -ISNULL(c), -d IS TRUE, -d IS NOT TRUE, -d IS FALSE, -d IS NOT FALSE, -d IS UNKNOWN, -d IS NOT UNKNOWN, -d is NULL, -d IS NOT NULL, -ISNULL(d) -from table_24532; -describe view_24532_a; -Field Type Null Key Default Extra -a IS TRUE int(1) NO 0 -a IS NOT TRUE int(1) NO 0 -a IS FALSE int(1) NO 0 -a IS NOT FALSE int(1) NO 0 -a IS UNKNOWN int(1) NO 0 -a IS NOT UNKNOWN int(1) NO 0 -a is NULL int(1) NO 0 -a IS NOT NULL int(1) NO 0 -ISNULL(a) int(1) NO 0 -b IS TRUE int(1) NO 0 -b IS NOT TRUE int(1) NO 0 -b IS FALSE int(1) NO 0 -b IS NOT FALSE int(1) NO 0 -b IS UNKNOWN int(1) NO 0 -b IS NOT UNKNOWN int(1) NO 0 -b is NULL int(1) NO 0 -b IS NOT NULL int(1) NO 0 -ISNULL(b) int(1) NO 0 -c IS TRUE int(1) NO 0 -c IS NOT TRUE int(1) NO 0 -c IS FALSE int(1) NO 0 -c IS NOT FALSE int(1) NO 0 -c IS UNKNOWN int(1) NO 0 -c IS NOT UNKNOWN int(1) NO 0 -c is NULL int(1) NO 0 -c IS NOT NULL int(1) NO 0 -ISNULL(c) int(1) NO 0 -d IS TRUE int(1) NO 0 -d IS NOT TRUE int(1) NO 0 -d IS FALSE int(1) NO 0 -d IS NOT FALSE int(1) NO 0 -d IS UNKNOWN int(1) NO 0 -d IS NOT UNKNOWN int(1) NO 0 -d is NULL int(1) NO 0 -d IS NOT NULL int(1) NO 0 -ISNULL(d) int(1) NO 0 -create view view_24532_b as -select -a IS TRUE, -if(ifnull(a, 0), 1, 0) as old_istrue, -a IS NOT TRUE, -if(ifnull(a, 0), 0, 1) as old_isnottrue, -a IS FALSE, -if(ifnull(a, 1), 0, 1) as old_isfalse, -a IS NOT FALSE, -if(ifnull(a, 1), 1, 0) as old_isnotfalse -from table_24532; -describe view_24532_b; -Field Type Null Key Default Extra -a IS TRUE int(1) NO 0 -old_istrue int(1) NO 0 -a IS NOT TRUE int(1) NO 0 -old_isnottrue int(1) NO 0 -a IS FALSE int(1) NO 0 -old_isfalse int(1) NO 0 -a IS NOT FALSE int(1) NO 0 -old_isnotfalse int(1) NO 0 -show create view view_24532_b; -View Create View character_set_client collation_connection -view_24532_b CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_24532_b` AS select `table_24532`.`a` is true AS `a IS TRUE`,if(ifnull(`table_24532`.`a`,0),1,0) AS `old_istrue`,`table_24532`.`a` is not true AS `a IS NOT TRUE`,if(ifnull(`table_24532`.`a`,0),0,1) AS `old_isnottrue`,`table_24532`.`a` is false AS `a IS FALSE`,if(ifnull(`table_24532`.`a`,1),0,1) AS `old_isfalse`,`table_24532`.`a` is not false AS `a IS NOT FALSE`,if(ifnull(`table_24532`.`a`,1),1,0) AS `old_isnotfalse` from `table_24532` latin1 latin1_swedish_ci -insert into table_24532 values (0, 0, 0, 0); -select * from view_24532_b; -a IS TRUE old_istrue a IS NOT TRUE old_isnottrue a IS FALSE old_isfalse a IS NOT FALSE old_isnotfalse -0 0 1 1 1 1 0 0 -update table_24532 set a=1; -select * from view_24532_b; -a IS TRUE old_istrue a IS NOT TRUE old_isnottrue a IS FALSE old_isfalse a IS NOT FALSE old_isnotfalse -1 1 0 0 0 0 1 1 -update table_24532 set a=NULL; -select * from view_24532_b; -a IS TRUE old_istrue a IS NOT TRUE old_isnottrue a IS FALSE old_isfalse a IS NOT FALSE old_isnotfalse -0 0 1 1 0 0 1 1 -drop view view_24532_a; -drop view view_24532_b; -drop table table_24532; -CREATE TABLE t1 ( -lid int NOT NULL PRIMARY KEY, -name char(10) NOT NULL -); -INSERT INTO t1 (lid, name) VALUES -(1, 'YES'), (2, 'NO'); -CREATE TABLE t2 ( -id int NOT NULL PRIMARY KEY, -gid int NOT NULL, -lid int NOT NULL, -dt date -); -INSERT INTO t2 (id, gid, lid, dt) VALUES -(1, 1, 1, '2007-01-01'),(2, 1, 2, '2007-01-02'), -(3, 2, 2, '2007-02-01'),(4, 2, 1, '2007-02-02'); -SELECT DISTINCT t2.gid AS lgid, -(SELECT t1.name FROM t1, t2 -WHERE t1.lid = t2.lid AND t2.gid = lgid -ORDER BY t2.dt DESC LIMIT 1 -) as clid -FROM t2; -lgid clid -1 NO -2 YES -CREATE VIEW v1 AS -SELECT DISTINCT t2.gid AS lgid, -(SELECT t1.name FROM t1, t2 -WHERE t1.lid = t2.lid AND t2.gid = lgid -ORDER BY t2.dt DESC LIMIT 1 -) as clid -FROM t2; -SELECT * FROM v1; -lgid clid -1 NO -2 YES -DROP VIEW v1; -DROP table t1,t2; -CREATE TABLE t1 (a INT); -INSERT INTO t1 VALUES (1),(2),(3); -CREATE VIEW v1 AS SELECT a FROM t1 ORDER BY a; -SELECT * FROM t1 UNION SELECT * FROM v1; -a -1 -2 -3 -EXPLAIN SELECT * FROM t1 UNION SELECT * FROM v1; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 -2 UNION t1 ALL NULL NULL NULL NULL 3 -NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL -SELECT * FROM v1 UNION SELECT * FROM t1; -a -1 -2 -3 -EXPLAIN SELECT * FROM v1 UNION SELECT * FROM t1; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 -2 UNION t1 ALL NULL NULL NULL NULL 3 -NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL -SELECT * FROM t1 UNION SELECT * FROM v1 ORDER BY a; -a -1 -2 -3 -EXPLAIN SELECT * FROM t1 UNION SELECT * FROM v1 ORDER BY a; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 -2 UNION t1 ALL NULL NULL NULL NULL 3 -NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL Using filesort -DROP VIEW v1; -DROP TABLE t1; -CREATE VIEW v1 AS SELECT CAST( 1.23456789 AS DECIMAL( 7,5 ) ) AS col; -SELECT * FROM v1; -col -1.23457 -DESCRIBE v1; -Field Type Null Key Default Extra -col decimal(7,5) NO 0.00000 -DROP VIEW v1; -CREATE VIEW v1 AS SELECT CAST(1.23456789 AS DECIMAL(8,0)) AS col; -SHOW CREATE VIEW v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(1.23456789 as decimal(8,0)) AS `col` latin1 latin1_swedish_ci -DROP VIEW v1; -CREATE TABLE t1 (a INT); -CREATE TABLE t2 (b INT, c INT DEFAULT 0); -INSERT INTO t1 (a) VALUES (1), (2); -INSERT INTO t2 (b) VALUES (1), (2); -CREATE VIEW v1 AS SELECT t2.b,t2.c FROM t1, t2 -WHERE t1.a=t2.b AND t2.b < 3 WITH CHECK OPTION; -SELECT * FROM v1; -b c -1 0 -2 0 -UPDATE v1 SET c=1 WHERE b=1; -SELECT * FROM v1; -b c -1 1 -2 0 -DROP VIEW v1; -DROP TABLE t1,t2; -CREATE TABLE t1 (id int); -CREATE TABLE t2 (id int, c int DEFAULT 0); -INSERT INTO t1 (id) VALUES (1); -INSERT INTO t2 (id) VALUES (1); -CREATE VIEW v1 AS -SELECT t2.c FROM t1, t2 -WHERE t1.id=t2.id AND 1 IN (SELECT id FROM t1) WITH CHECK OPTION; -UPDATE v1 SET c=1; -DROP VIEW v1; -DROP TABLE t1,t2; -CREATE TABLE t1 (a1 INT, c INT DEFAULT 0); -CREATE TABLE t2 (a2 INT); -CREATE TABLE t3 (a3 INT); -CREATE TABLE t4 (a4 INT); -INSERT INTO t1 (a1) VALUES (1),(2); -INSERT INTO t2 (a2) VALUES (1),(2); -INSERT INTO t3 (a3) VALUES (1),(2); -INSERT INTO t4 (a4) VALUES (1),(2); -CREATE VIEW v1 AS -SELECT t1.a1, t1.c FROM t1 JOIN t2 ON t1.a1=t2.a2 AND t1.c < 3 -WITH CHECK OPTION; -SELECT * FROM v1; -a1 c -1 0 -2 0 -UPDATE v1 SET c=3; -ERROR 44000: CHECK OPTION failed `test`.`v1` -PREPARE t FROM 'UPDATE v1 SET c=3'; -EXECUTE t; -ERROR 44000: CHECK OPTION failed `test`.`v1` -EXECUTE t; -ERROR 44000: CHECK OPTION failed `test`.`v1` -INSERT INTO v1(a1, c) VALUES (3, 3); -ERROR 44000: CHECK OPTION failed `test`.`v1` -UPDATE v1 SET c=1 WHERE a1=1; -SELECT * FROM v1; -a1 c -1 1 -2 0 -SELECT * FROM t1; -a1 c -1 1 -2 0 -CREATE VIEW v2 AS SELECT t1.a1, t1.c -FROM (t1 JOIN t2 ON t1.a1=t2.a2 AND t1.c < 3) -JOIN (t3 JOIN t4 ON t3.a3=t4.a4) -ON t2.a2=t3.a3 WITH CHECK OPTION; -SELECT * FROM v2; -a1 c -1 1 -2 0 -UPDATE v2 SET c=3; -ERROR 44000: CHECK OPTION failed `test`.`v2` -PREPARE t FROM 'UPDATE v2 SET c=3'; -EXECUTE t; -ERROR 44000: CHECK OPTION failed `test`.`v2` -EXECUTE t; -ERROR 44000: CHECK OPTION failed `test`.`v2` -INSERT INTO v2(a1, c) VALUES (3, 3); -ERROR 44000: CHECK OPTION failed `test`.`v2` -UPDATE v2 SET c=2 WHERE a1=1; -SELECT * FROM v2; -a1 c -1 2 -2 0 -SELECT * FROM t1; -a1 c -1 2 -2 0 -DROP VIEW v1,v2; -DROP TABLE t1,t2,t3,t4; -CREATE TABLE t1 (a int, b int); -INSERT INTO t1 VALUES (1,2), (2,2), (1,3), (1,2); -CREATE VIEW v1 AS SELECT a, b+1 as b FROM t1; -SELECT b, SUM(a) FROM v1 WHERE b=3 GROUP BY b; -b SUM(a) -3 4 -EXPLAIN SELECT b, SUM(a) FROM v1 WHERE b=3 GROUP BY b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where -SELECT a, SUM(b) FROM v1 WHERE b=3 GROUP BY a; -a SUM(b) -1 6 -2 3 -EXPLAIN SELECT a, SUM(b) FROM v1 WHERE b=3 GROUP BY a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where; Using temporary; Using filesort -SELECT a, SUM(b) FROM v1 WHERE a=1 GROUP BY a; -a SUM(b) -1 10 -EXPLAIN SELECT a, SUM(b) FROM v1 WHERE a=1 GROUP BY a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where -DROP VIEW v1; -DROP TABLE t1; -CREATE TABLE t1 ( -person_id int NOT NULL PRIMARY KEY, -username varchar(40) default NULL, -status_flg char(1) NOT NULL default 'A' -); -CREATE TABLE t2 ( -person_role_id int NOT NULL auto_increment PRIMARY KEY, -role_id int NOT NULL, -person_id int NOT NULL, -INDEX idx_person_id (person_id), -INDEX idx_role_id (role_id) -); -CREATE TABLE t3 ( -role_id int NOT NULL auto_increment PRIMARY KEY, -role_name varchar(100) default NULL, -app_name varchar(40) NOT NULL, -INDEX idx_app_name(app_name) -); -CREATE VIEW v1 AS -SELECT profile.person_id AS person_id -FROM t1 profile, t2 userrole, t3 role -WHERE userrole.person_id = profile.person_id AND -role.role_id = userrole.role_id AND -profile.status_flg = 'A' - ORDER BY profile.person_id,role.app_name,role.role_name; -INSERT INTO t1 VALUES -(6,'Sw','A'), (-1136332546,'ols','e'), (0,' *\n','0'), -(-717462680,'ENTS Ta','0'), (-904346964,'ndard SQL\n','0'); -INSERT INTO t2 VALUES -(1,3,6),(2,4,7),(3,5,8),(4,6,9),(5,1,6),(6,1,7),(7,1,8),(8,1,9),(9,1,10); -INSERT INTO t3 VALUES -(1,'NUCANS_APP_USER','NUCANSAPP'),(2,'NUCANS_TRGAPP_USER','NUCANSAPP'), -(3,'IA_INTAKE_COORDINATOR','IACANS'),(4,'IA_SCREENER','IACANS'), -(5,'IA_SUPERVISOR','IACANS'),(6,'IA_READONLY','IACANS'), -(7,'SOC_USER','SOCCANS'),(8,'CAYIT_USER','CAYITCANS'), -(9,'RTOS_DCFSPOS_SUPERVISOR','RTOS'); -EXPLAIN SELECT t.person_id AS a, t.person_id AS b FROM v1 t WHERE t.person_id=6; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE profile const PRIMARY PRIMARY 4 const 1 Using temporary; Using filesort -1 SIMPLE userrole ref idx_person_id,idx_role_id idx_person_id 4 const 2 -1 SIMPLE role eq_ref PRIMARY PRIMARY 4 test.userrole.role_id 1 -SELECT t.person_id AS a, t.person_id AS b FROM v1 t WHERE t.person_id=6; -a b -6 6 -6 6 -DROP VIEW v1; -DROP TABLE t1,t2,t3; -create table t1 (i int); -insert into t1 values (1), (2), (1), (3), (2), (4); -create view v1 as select distinct i from t1; -select * from v1; -i -1 -2 -3 -4 -select table_name, is_updatable from information_schema.views -where table_name = 'v1'; -table_name is_updatable -v1 NO -drop view v1; -drop table t1; -CREATE TABLE t1 (a INT); -INSERT INTO t1 VALUES (1),(2); -CREATE VIEW v1 AS SELECT * FROM t1; -SELECT * FROM v1 USE KEY(non_existant); -ERROR 42000: Key 'non_existant' doesn't exist in table 'v1' -SELECT * FROM v1 FORCE KEY(non_existant); -ERROR 42000: Key 'non_existant' doesn't exist in table 'v1' -SELECT * FROM v1 IGNORE KEY(non_existant); -ERROR 42000: Key 'non_existant' doesn't exist in table 'v1' -DROP VIEW v1; -DROP TABLE t1; -CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT, b INT NOT NULL DEFAULT 0, -PRIMARY KEY(a), KEY (b)); -INSERT INTO t1 VALUES (),(),(),(),(),(),(),(),(),(),(),(),(),(),(); -CREATE VIEW v1 AS SELECT * FROM t1 FORCE KEY (PRIMARY,b) ORDER BY a; -SHOW CREATE VIEW v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` FORCE INDEX (PRIMARY) FORCE INDEX (`b`) order by `t1`.`a` latin1 latin1_swedish_ci -EXPLAIN SELECT * FROM v1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 4 NULL 15 -CREATE VIEW v2 AS SELECT * FROM t1 USE KEY () ORDER BY a; -SHOW CREATE VIEW v2; -View Create View character_set_client collation_connection -v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` USE INDEX () order by `t1`.`a` latin1 latin1_swedish_ci -EXPLAIN SELECT * FROM v2; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 15 Using filesort -CREATE VIEW v3 AS SELECT * FROM t1 IGNORE KEY (b) ORDER BY a; -SHOW CREATE VIEW v3; -View Create View character_set_client collation_connection -v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` IGNORE INDEX (`b`) order by `t1`.`a` latin1 latin1_swedish_ci -EXPLAIN SELECT * FROM v3; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 15 Using filesort -DROP VIEW v1; -DROP VIEW v2; -DROP VIEW v3; -DROP TABLE t1; -# -# Bug#29477 Not all fields of the target table were checked to have -# a default value when inserting into a view. -# -create table t1(f1 int, f2 int not null); -create view v1 as select f1 from t1; -insert ignore into v1 values(1); -Warnings: -Warning 1423 Field of view 'test.v1' underlying table doesn't have a default value -set @old_mode=@@sql_mode; -set @@sql_mode=traditional; -insert into v1 values(1); -ERROR HY000: Field of view 'test.v1' underlying table doesn't have a default value -set @@sql_mode=@old_mode; -drop view v1; -drop table t1; -create table t1 (a int, key(a)); -create table t2 (c int); -create view v1 as select a b from t1; -create view v2 as select 1 a from t2, v1 where c in -(select 1 from t1 where b = a); -insert into t1 values (1), (1); -insert into t2 values (1), (1); -prepare stmt from "select * from v2 where a = 1"; -execute stmt; -a -1 -1 -1 -1 -drop view v1, v2; -drop table t1, t2; -CREATE TABLE t1 (a INT); -CREATE VIEW v1 AS SELECT p.a AS a FROM t1 p, t1 q; -INSERT INTO t1 VALUES (1), (1); -SELECT MAX(a), COUNT(DISTINCT a) FROM v1 GROUP BY a; -MAX(a) COUNT(DISTINCT a) -1 1 -DROP VIEW v1; -DROP TABLE t1; -# ----------------------------------------------------------------- -# -- Bug#34337 Server crash when Altering a view using a table name. -# ----------------------------------------------------------------- - -DROP TABLE IF EXISTS t1; - -CREATE TABLE t1(c1 INT); - -SELECT * FROM t1; -c1 -ALTER ALGORITHM=TEMPTABLE SQL SECURITY INVOKER VIEW t1 (c2) AS SELECT (1); -ERROR HY000: 'test.t1' is not of type 'VIEW' - -DROP TABLE t1; - -# -- End of test case for Bug#34337. - -# ----------------------------------------------------------------- -# -- Bug#35193 VIEW query is rewritten without "FROM DUAL", -# -- causing syntax error -# ----------------------------------------------------------------- - -CREATE VIEW v1 AS SELECT 1 FROM DUAL WHERE 1; - -SELECT * FROM v1; -1 -1 -SHOW CREATE TABLE v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `1` from DUAL where 1 latin1 latin1_swedish_ci - -DROP VIEW v1; - -# -- End of test case for Bug#35193. - -CREATE VIEW v1 AS SELECT 1; -DROP VIEW v1; -CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT, INDEX (c2)); -INSERT INTO t1 VALUES (1,1), (2,2), (3,3); -SELECT * FROM t1 USE INDEX (PRIMARY) WHERE c1=2; -c1 c2 -2 2 -SELECT * FROM t1 USE INDEX (c2) WHERE c2=2; -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 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; -ERROR 42000: Key 'PRIMARY' doesn't exist in table 'v1' -SELECT * FROM v1 IGNORE INDEX (PRIMARY) WHERE c1=2; -ERROR 42000: Key 'PRIMARY' doesn't exist in table 'v1' -SELECT * FROM v1 USE INDEX (c2) WHERE c2=2; -ERROR 42000: Key 'c2' doesn't exist in table 'v1' -SELECT * FROM v1 FORCE INDEX (c2) WHERE c2=2; -ERROR 42000: Key 'c2' doesn't exist in table 'v1' -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 -# ----------------------------------------------------------------- - -CREATE TABLE t1 (c INT); - -CREATE VIEW v1 (view_column) AS SELECT c AS alias FROM t1 HAVING alias; -SHOW CREATE VIEW v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`c` AS `view_column` from `t1` having `view_column` <> 0 latin1 latin1_swedish_ci -SELECT * FROM v1; -view_column - -DROP VIEW v1; -DROP TABLE t1; - -# -- End of test case for Bug#40825 - -# ----------------------------------------------------------------- -# -- End of 5.0 tests. -# ----------------------------------------------------------------- -DROP DATABASE IF EXISTS `d-1`; -CREATE DATABASE `d-1`; -USE `d-1`; -CREATE TABLE `t-1` (c1 INT); -CREATE VIEW `v-1` AS SELECT c1 FROM `t-1`; -SHOW TABLES; -Tables_in_d-1 -t-1 -v-1 -RENAME TABLE `t-1` TO `t-2`; -RENAME TABLE `v-1` TO `v-2`; -SHOW TABLES; -Tables_in_d-1 -t-2 -v-2 -DROP TABLE `t-2`; -DROP VIEW `v-2`; -DROP DATABASE `d-1`; -USE test; - -# -# Bug#26676 VIEW using old table schema in a session. -# - -DROP VIEW IF EXISTS v1; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1(c1 INT, c2 INT); -INSERT INTO t1 VALUES (1, 2), (3, 4); - -SELECT * FROM t1; -c1 c2 -1 2 -3 4 - -CREATE VIEW v1 AS SELECT * FROM t1; - -SELECT * FROM v1; -c1 c2 -1 2 -3 4 - -ALTER TABLE t1 ADD COLUMN c3 INT AFTER c2; - -SELECT * FROM t1; -c1 c2 c3 -1 2 NULL -3 4 NULL - -SELECT * FROM v1; -c1 c2 -1 2 -3 4 - -SHOW CREATE VIEW v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`c1` AS `c1`,`t1`.`c2` AS `c2` from `t1` latin1 latin1_swedish_ci - -DROP VIEW v1; -DROP TABLE t1; - -# End of test case for Bug#26676. - -# ----------------------------------------------------------------- -# -- Bug#32538 View definition picks up character set, but not collation -# ----------------------------------------------------------------- - -DROP VIEW IF EXISTS v1; - -SET collation_connection = latin1_general_ci; -CREATE VIEW v1 AS SELECT _latin1 'text1' AS c1, 'text2' AS c2; - -SELECT COLLATION(c1), COLLATION(c2) FROM v1; -COLLATION(c1) COLLATION(c2) -latin1_swedish_ci latin1_general_ci - -SHOW CREATE VIEW v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select _latin1'text1' AS `c1`,'text2' AS `c2` latin1 latin1_general_ci - -SELECT * FROM v1 WHERE c1 = 'text1'; -ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin1_general_ci,COERCIBLE) for operation '=' - -SELECT * FROM v1 WHERE c2 = 'text2'; -c1 c2 -text1 text2 - -use test; -SET names latin1; - -SELECT COLLATION(c1), COLLATION(c2) FROM v1; -COLLATION(c1) COLLATION(c2) -latin1_swedish_ci latin1_general_ci - -SELECT * FROM v1 WHERE c1 = 'text1'; -c1 c2 -text1 text2 - -SELECT * FROM v1 WHERE c2 = 'text2'; -ERROR HY000: Illegal mix of collations (latin1_general_ci,COERCIBLE) and (latin1_swedish_ci,COERCIBLE) for operation '=' - -DROP VIEW v1; - -# -- End of test case for Bug#32538. - -drop view if exists a; -drop procedure if exists p; -create procedure p() -begin -declare continue handler for sqlexception begin end; -create view a as select 1; -end| -call p(); -call p(); -drop view a; -drop procedure p; -# -# Bug #44860: ALTER TABLE on view crashes server -# -CREATE TABLE t1 (a INT); -CREATE VIEW v1 AS SELECT a FROM t1; -ALTER TABLE v1; -ERROR HY000: 'test.v1' is not of type 'BASE TABLE' -DROP VIEW v1; -DROP TABLE t1; -# -# Bug#48449: hang on show create view after upgrading when -# view contains function of view -# -DROP VIEW IF EXISTS v1,v2; -DROP TABLE IF EXISTS t1,t2; -DROP FUNCTION IF EXISTS f1; -CREATE TABLE t1 (a INT); -CREATE TABLE t2 (a INT); -CREATE FUNCTION f1() RETURNS INT -BEGIN -SELECT a FROM v2 INTO @a; -RETURN @a; -END// -# Trigger pre-locking when opening v2. -CREATE VIEW v1 AS SELECT f1() FROM t1; -SHOW CREATE VIEW v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `f1`() AS `f1()` from `t1` latin1 latin1_swedish_ci -Warnings: -Note 1599 View `test`.`v2` has no creation context -DROP VIEW v1,v2; -DROP TABLE t1,t2; -DROP FUNCTION f1; -CREATE TABLE t1(f1 INT); -INSERT INTO t1 VALUES (); -CREATE VIEW v1 AS SELECT 1 FROM t1 WHERE -ROW(1,1) >= ROW(1, (SELECT 1 FROM t1 WHERE f1 >= ANY ( SELECT '1' ))); -DROP VIEW v1; -DROP TABLE t1; -# -# Bug#52120 create view cause Assertion failed: 0, file .\item_subselect.cc, line 817 -# -CREATE TABLE t1 (a CHAR(1) CHARSET latin1, b CHAR(1) CHARSET utf8); -CREATE VIEW v1 AS SELECT 1 from t1 -WHERE t1.b <=> (SELECT a FROM t1 WHERE a < SOME(SELECT '1')); -DROP VIEW v1; -DROP TABLE t1; -# -# Bug#57703 create view cause Assertion failed: 0, file .\item_subselect.cc, line 846 -# -CREATE TABLE t1(a int); -CREATE VIEW v1 AS SELECT 1 FROM t1 GROUP BY -SUBSTRING(1 FROM (SELECT 3 FROM t1 WHERE a >= ANY(SELECT 1))); -DROP VIEW v1; -DROP TABLE t1; -# -# Bug#57352 valgrind warnings when creating view -# -CREATE VIEW v1 AS SELECT 1 IN (1 LIKE 2,0) AS f; -DROP VIEW v1; -# -# Bug 11829681 - 60295: ERROR 1356 ON VIEW THAT EXECUTES FINE AS A QUERY -# -CREATE TABLE t1 (a INT); -CREATE VIEW v1 AS SELECT s.* FROM t1 s, t1 b HAVING a; -SELECT * FROM v1; -a -DROP VIEW v1; -DROP TABLE t1; -# -# LP BUG#777809 (a retrograded condition for view ON) -# -CREATE TABLE t1 ( f1 int NOT NULL , f6 int NOT NULL ) ; -INSERT IGNORE INTO t1 VALUES (20, 2); -CREATE TABLE t2 ( f3 int NOT NULL ) ; -INSERT IGNORE INTO t2 VALUES (7); -CREATE OR REPLACE VIEW v2 AS SELECT * FROM t2; -PREPARE prep_stmt FROM 'SELECT t1.f6 FROM t1 RIGHT JOIN v2 ON v2.f3 WHERE t1.f1 != 0'; -EXECUTE prep_stmt; -f6 -2 -EXECUTE prep_stmt; -f6 -2 -drop view v2; -drop table t1,t2; -# ----------------------------------------------------------------- -# -- End of 5.1 tests. -# ----------------------------------------------------------------- -# -# Bug #794005: crash in st_table::mark_virtual_columns_for_write -# -CREATE TABLE t1 (a int); -insert into t1 values (1); -CREATE TABLE t2 (a int); -insert into t2 values (1); -CREATE VIEW v2 AS SELECT * FROM t2; -CREATE VIEW v1 AS SELECT * FROM v2; -CREATE VIEW v3 AS SELECT t2.a,v1.a as b FROM t2,v1 where t2.a=v1.a; -CREATE OR REPLACE ALGORITHM = TEMPTABLE VIEW v2 AS SELECT * FROM t1; -UPDATE v1 SET a = 10; -ERROR HY000: The target table v1 of the UPDATE is not updatable -REPLACE v1 SET a = 10; -ERROR HY000: The target table v1 of the INSERT is not insertable-into -INSERT into v1 values (20); -ERROR HY000: The target table v1 of the INSERT is not insertable-into -DELETE from v1; -ERROR HY000: The target table v1 of the DELETE is not updatable -UPDATE v3 SET b= 10; -ERROR HY000: The target table v3 of the UPDATE is not updatable -REPLACE v3 SET b= 10; -ERROR HY000: The target table v3 of the INSERT is not insertable-into -INSERT into v3(b) values (20); -ERROR HY000: The target table v3 of the INSERT is not insertable-into -DELETE from v3 where b=20; -ERROR HY000: Can not delete from join view 'test.v3' -DELETE from v3 where a=20; -ERROR HY000: Can not delete from join view 'test.v3' -DELETE v1 from v1,t1 where v1.a=t1.a; -ERROR HY000: The target table v1 of the DELETE is not updatable -UPDATE v3 SET a = 10; -REPLACE v3 SET a = 11; -INSERT INTO v3(a) values (20); -select * from t1; -a -1 -select * from t2; -a -10 -11 -20 -CREATE OR REPLACE ALGORITHM = MERGE VIEW v2 AS SELECT * FROM t2; -DELETE from v1 where a=11; -DELETE v1 from v1,t1 where v1.a=t1.a; -select * from t1; -a -1 -select * from t2; -a -10 -20 -DROP VIEW v1,v2,v3; -DROP TABLE t1,t2; -# -# MDEV-6251: SIGSEGV in query optimizer (in set_check_materialized -# with MERGE view) -# -CREATE TABLE t1 (a1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); -CREATE TABLE t2 (b1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); -CREATE TABLE t3 (c1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); -CREATE TABLE t4 (d1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); -CREATE TABLE t5 (e1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); -CREATE TABLE t6 (f1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); -CREATE OR REPLACE view v1 AS -SELECT 1 -FROM t1 a_alias_1 -LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 -LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 -LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 -LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 -LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 -LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -; -SELECT 1 -FROM (( SELECT 1 -FROM t1 a_alias_1 -LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 -LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 -LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 -LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 -LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 -LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t1) -LEFT OUTER JOIN (( SELECT 1 -FROM t1 a_alias_1 -LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 -LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 -LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 -LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 -LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 -LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t2) ON 1=1 -LEFT OUTER JOIN (( SELECT 1 -FROM t1 a_alias_1 -LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 -LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 -LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 -LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 -LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 -LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t3) ON 1=1 -LEFT OUTER JOIN (( SELECT 1 -FROM t1 a_alias_1 -LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 -LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 -LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 -LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 -LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 -LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t4) ON 1=1 -LEFT OUTER JOIN (( SELECT 1 -FROM t1 a_alias_1 -LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 -LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 -LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 -LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 -LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 -LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t5) ON 1=1 -LEFT OUTER JOIN (( SELECT 1 -FROM t1 a_alias_1 -LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 -LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 -LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 -LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 -LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 -LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t6) ON 1=1 -LEFT OUTER JOIN (( SELECT 1 -FROM t1 a_alias_1 -LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 -LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 -LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 -LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 -LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 -LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t7) ON 1=1 -LEFT OUTER JOIN (( SELECT 1 -FROM t1 a_alias_1 -LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 -LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 -LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 -LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 -LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 -LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t8) ON 1=1 -; -1 -SELECT 1 -FROM (v1 t1) -LEFT OUTER JOIN (v1 t2) ON 1=1 -LEFT OUTER JOIN (v1 t3) ON 1=1 -LEFT OUTER JOIN (v1 t4) ON 1=1 -LEFT OUTER JOIN (v1 t5) ON 1=1 -LEFT OUTER JOIN (v1 t6) ON 1=1 -LEFT OUTER JOIN (v1 t7) ON 1=1 -LEFT OUTER JOIN (v1 t8) ON 1=1 -; -1 -drop view v1; -drop table t1,t2,t3,t4,t5,t6; -# ----------------------------------------------------------------- -# -- End of 5.2 tests. -# ----------------------------------------------------------------- -# -# Bug #59696 Optimizer does not use equalities for conditions over view -# -CREATE TABLE t1 (a int NOT NULL); -INSERT INTO t1 VALUES -(9), (2), (8), (1), (3), (4), (2), (5), -(9), (2), (8), (1), (3), (4), (2), (5); -CREATE TABLE t2 (pk int PRIMARY KEY, c int NOT NULL); -INSERT INTO t2 VALUES -(9,90), (16, 160), (11,110), (1,10), (18,180), (2,20), -(14,140), (15, 150), (12,120), (3,30), (17,170), (19,190); -EXPLAIN EXTENDED -SELECT t1.a,t2.c FROM t1,t2 WHERE t2.pk = t1.a AND t2.pk > 8; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 16 100.00 Using where -1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 -Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`pk` = `test`.`t1`.`a` and `test`.`t1`.`a` > 8 -FLUSH STATUS; -SELECT t1.a,t2.c FROM t1,t2 WHERE t2.pk = t1.a AND t2.pk > 8; -a c -9 90 -9 90 -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_retry 0 -Handler_read_rnd 0 -Handler_read_rnd_deleted 0 -Handler_read_rnd_next 17 -CREATE VIEW v AS SELECT * FROM t2; -EXPLAIN EXTENDED -SELECT t1.a,v.c FROM t1,v WHERE v.pk = t1.a AND v.pk > 8; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 16 100.00 Using where -1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 -Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`pk` = `test`.`t1`.`a` and `test`.`t1`.`a` > 8 -FLUSH STATUS; -SELECT t1.a,v.c FROM t1,v WHERE v.pk = t1.a AND v.pk > 8; -a c -9 90 -9 90 -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_retry 0 -Handler_read_rnd 0 -Handler_read_rnd_deleted 0 -Handler_read_rnd_next 17 -DROP VIEW v; -DROP TABLE t1, t2; -# -# Bug#702403: crash with multiple equalities and a view -# -CREATE TABLE t1 (a int); -INSERT INTO t1 VALUES (10); -CREATE TABLE t2 (pk int PRIMARY KEY, b int, INDEX idx (b)); -INSERT INTO t2 VALUES (1,2), (3,4); -CREATE TABLE t3 (pk int PRIMARY KEY, b int, INDEX idx (b)); -INSERT INTO t3 VALUES (1,2), (3,4); -CREATE VIEW v1 AS SELECT * FROM t1; -EXPLAIN -SELECT * FROM v1, t2, t3 -WHERE t3.pk = v1.a AND t2.b = 1 AND t2.b = t3.pk AND v1.a BETWEEN 2 AND 5; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE -SELECT * FROM v1, t2, t3 -WHERE t3.pk = v1.a AND t2.b = 1 AND t2.b = t3.pk AND v1.a BETWEEN 2 AND 5; -a pk b pk b -DROP VIEW v1; -DROP TABLE t1, t2, t3; -# -# Bug#717577: substitution for best field in a query over a view and -# with OR in the WHERE condition -# -create table t1 (a int, b int); -insert into t1 values (2,4), (1,3); -create table t2 (c int); -insert into t2 values (6), (4), (1), (3), (8), (3), (4), (2); -select * from t1,t2 where t2.c=t1.a and t2.c < 3 or t2.c=t1.b and t2.c >=4; -a b c -2 4 4 -1 3 1 -2 4 4 -2 4 2 -explain extended -select * from t1,t2 where t2.c=t1.a and t2.c < 3 or t2.c=t1.b and t2.c >=4; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where; Using join buffer (flat, BNL join) -Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`c` = `test`.`t1`.`a` and `test`.`t1`.`a` < 3 or `test`.`t2`.`c` = `test`.`t1`.`b` and `test`.`t1`.`b` >= 4 -create view v1 as select * from t2; -select * from t1,v1 where v1.c=t1.a and v1.c < 3 or v1.c=t1.b and v1.c >=4; -a b c -2 4 4 -1 3 1 -2 4 4 -2 4 2 -explain extended -select * from t1,v1 where v1.c=t1.a and v1.c < 3 or v1.c=t1.b and v1.c >=4; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where; Using join buffer (flat, BNL join) -Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`c` = `test`.`t1`.`a` and `test`.`t1`.`a` < 3 or `test`.`t2`.`c` = `test`.`t1`.`b` and `test`.`t1`.`b` >= 4 -create view v2 as select * from v1; -select * from t1,v2 where v2.c=t1.a and v2.c < 3 or v2.c=t1.b and v2.c >=4; -a b c -2 4 4 -1 3 1 -2 4 4 -2 4 2 -explain extended -select * from t1,v2 where v2.c=t1.a and v2.c < 3 or v2.c=t1.b and v2.c >=4; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where; Using join buffer (flat, BNL join) -Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`c` = `test`.`t1`.`a` and `test`.`t1`.`a` < 3 or `test`.`t2`.`c` = `test`.`t1`.`b` and `test`.`t1`.`b` >= 4 -create view v3 as select * from t1; -select * from v3,v2 where v2.c=v3.a and v2.c < 3 or v2.c=v3.b and v2.c >=4; -a b c -2 4 4 -1 3 1 -2 4 4 -2 4 2 -explain extended -select * from v3,v2 where v2.c=v3.a and v2.c < 3 or v2.c=v3.b and v2.c >=4; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where; Using join buffer (flat, BNL join) -Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`c` = `test`.`t1`.`a` and `test`.`t1`.`a` < 3 or `test`.`t2`.`c` = `test`.`t1`.`b` and `test`.`t1`.`b` >= 4 -drop view v1,v2,v3; -drop table t1,t2; -# -# Bug#724942: substitution of the constant into a view field -# -CREATE TABLE t1 (a int); -INSERT INTO t1 VALUES (2), (9), (9), (6), (5), (4), (7); -CREATE VIEW v1 AS SELECT * FROM t1; -SELECT * FROM v1 WHERE a > -1 OR a > 6 AND a = 3; -a -2 -9 -9 -6 -5 -4 -7 -EXPLAIN EXTENDED -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 -SELECT * FROM v1 WHERE a > -1 OR a AND a = 0; -a -2 -9 -9 -6 -5 -4 -7 -EXPLAIN EXTENDED -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 -CREATE VIEW v2 AS SELECT * FROM v1; -SELECT * FROM v2 WHERE a > -1 OR a AND a = 0; -a -2 -9 -9 -6 -5 -4 -7 -EXPLAIN EXTENDED -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 -DROP VIEW v1,v2; -DROP TABLE t1; -CREATE TABLE t1 (a varchar(10), KEY (a)) ; -INSERT INTO t1 VALUES -('DD'), ('ZZ'), ('ZZ'), ('KK'), ('FF'), ('HH'),('MM'); -CREATE VIEW v1 AS SELECT * FROM t1; -# t1 and v1 should return the same result set -SELECT * FROM v1 WHERE a > 'JJ' OR a <> 0 AND a = 'VV'; -a -KK -MM -ZZ -ZZ -SELECT * FROM t1 WHERE a > 'JJ' OR a <> 0 AND a = 'VV'; -a -KK -MM -ZZ -ZZ -# t1 and v1 should propagate constants in the same way -EXPLAIN EXTENDED -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 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 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'; -a -KK -MM -ZZ -ZZ -SELECT * FROM t1 WHERE a > 'JJ' OR a AND a = 'VV'; -a -KK -MM -ZZ -ZZ -# t1 and v1 should propagate constants in the same way -EXPLAIN EXTENDED -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 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 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; -# -# Bug#777745: crash with equality propagation -# over view fields -# -CREATE TABLE t1 (a int NOT NULL ) ; -INSERT INTO t1 VALUES (2), (1); -CREATE TABLE t2 (a int NOT NULL , b int NOT NULL) ; -INSERT INTO t2 VALUES (2,20),(2,30); -CREATE VIEW v2 AS SELECT * FROM t2; -EXPLAIN -SELECT * FROM t1,v2 -WHERE v2.a = t1.a AND v2.a = 2 AND v2.a IS NULL AND t1.a != 0; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE -SELECT * FROM t1,v2 -WHERE v2.a = t1.a AND v2.a = 2 AND v2.a IS NULL AND t1.a != 0; -a a b -EXPLAIN -SELECT * FROM t1,v2 -WHERE v2.a = t1.a AND v2.a = 2 AND v2.a+1 > 2 AND t1.a != 0; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) -SELECT * FROM t1,v2 -WHERE v2.a = t1.a AND v2.a = 2 AND v2.a+1 > 2 AND t1.a != 0; -a a b -2 2 20 -2 2 30 -DROP VIEW v2; -DROP TABLE t1,t2; -# -# Bug#794038: crash with INSERT/UPDATE/DELETE -# over a non-updatable view -# -CREATE TABLE t1 (a int); -CREATE ALGORITHM = TEMPTABLE VIEW v1 AS SELECT * FROM t1; -CREATE ALGORITHM = MERGE VIEW v2 AS SELECT * FROM v1; -CREATE ALGORITHM = TEMPTABLE VIEW v3 AS SELECT * FROM v2; -INSERT INTO v3 VALUES (1); -ERROR HY000: The target table v3 of the INSERT is not insertable-into -UPDATE v3 SET a=0; -ERROR HY000: The target table v3 of the UPDATE is not updatable -DELETE FROM v3; -ERROR HY000: The target table v3 of the DELETE is not updatable -DROP VIEW v1,v2,v3; -DROP TABLE t1; -# -# Bug#798621: crash with a view string field equal -# to a constant -# -CREATE TABLE t1 (a varchar(32), b int) ; -INSERT INTO t1 VALUES ('j', NULL), ('c', 8), ('c', 1); -CREATE VIEW v1 AS SELECT * FROM t1; -CREATE TABLE t2 (a varchar(32)) ; -INSERT INTO t2 VALUES ('j'), ('c'); -SELECT * FROM v1 LEFT JOIN t2 ON t2.a = v1.a -WHERE v1.b = 1 OR v1.a = 'a' AND LENGTH(v1.a) >= v1.b; -a b a -c 1 c -EXPLAIN EXTENDED -SELECT * FROM v1 LEFT JOIN t2 ON t2.a = v1.a -WHERE v1.b = 1 OR v1.a = 'a' AND LENGTH(v1.a) >= v1.b; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where -Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a` from `test`.`t1` left join `test`.`t2` on(`test`.`t2`.`a` = `test`.`t1`.`a`) where `test`.`t1`.`b` = 1 or `test`.`t1`.`a` = 'a' and octet_length(`test`.`t1`.`a`) >= `test`.`t1`.`b` -DROP VIEW v1; -DROP TABLE t1,t2; -# Bug#798625: duplicate of the previous one, but without crash -CREATE TABLE t1 (f1 int NOT NULL, f2 int, f3 int, f4 varchar(32), f5 int) ; -INSERT INTO t1 VALUES (20,5,2,'r', 0); -CREATE VIEW v1 AS SELECT * FROM t1; -SELECT v1.f4 FROM v1 -WHERE f1<>0 OR f2<>0 AND f4='v' AND (f2<>0 OR f3<>0 AND f5<>0 OR f4 LIKE '%b%'); -f4 -r -EXPLAIN EXTENDED -SELECT v1.f4 FROM v1 -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 dual where 1 -DROP VIEW v1; -DROP TABLE t1; -# -# Bug#798576: abort on a GROUP BY query over a view with left join -# that can be converted to inner join -# -CREATE TABLE t1 (a int NOT NULL , b int NOT NULL) ; -INSERT INTO t1 VALUES (214,0), (6,6), (6,0), (7,0); -CREATE TABLE t2 (b int) ; -INSERT INTO t2 VALUES (88), (78), (6); -CREATE ALGORITHM=MERGE VIEW v1 AS -SELECT t1.a, t2.b FROM (t2 LEFT JOIN t1 ON t2.b > t1.a) WHERE t1.b <= 0; -SELECT * FROM v1; -a b -6 88 -6 78 -7 88 -7 78 -SELECT a, MIN(b) FROM v1 GROUP BY a; -a MIN(b) -6 78 -7 78 -DROP VIEW v1; -DROP TABLE t1,t2; -# -# LP bug #793386: unexpected 'Duplicate column name ''' error -# at the second execution of a PS using a view -# -CREATE TABLE t1 (f1 int, f2 int, f3 int, f4 int); -CREATE VIEW v1 AS -SELECT t.f1, t.f2, s.f3, s.f4 FROM t1 t, t1 s -WHERE t.f4 >= s.f2 AND s.f3 < 0; -PREPARE stmt1 FROM -"SELECT s.f1 AS f1, s.f2 AS f2, s.f3 AS f3, t.f4 AS f4 - FROM v1 AS t LEFT JOIN v1 AS s ON t.f4=s.f4 WHERE t.f2 <> 1225"; -EXECUTE stmt1; -f1 f2 f3 f4 -EXECUTE stmt1; -f1 f2 f3 f4 -DEALLOCATE PREPARE stmt1; -DROP VIEW v1; -DROP TABLE t1; -# -# LP BUG#806071 (2 views with ORDER BY) -# -CREATE TABLE t1 (f1 int); -INSERT INTO t1 VALUES (1),(1); -CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT f1 FROM t1; -CREATE ALGORITHM=MERGE VIEW v2 AS SELECT f1 FROM v1 ORDER BY f1; -SELECT * FROM v2 AS a1, v2 AS a2; -f1 f1 -1 1 -1 1 -1 1 -1 1 -EXPLAIN EXTENDED SELECT * FROM v2 AS a1, v2 AS a2; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <derived3> ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort -1 PRIMARY <derived5> ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join) -5 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 -3 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 -Warnings: -Note 1926 View 'test'.'v2' ORDER BY clause ignored because there is other ORDER BY clause already -Note 1003 /* select#1 */ select `v1`.`f1` AS `f1`,`v1`.`f1` AS `f1` from `test`.`v1` join `test`.`v1` order by `v1`.`f1` -DROP VIEW v1, v2; -DROP TABLE t1; -# -# LP bug #823189: dependent subquery with RIGHT JOIN -# referencing view in WHERE -# -CREATE TABLE t1 (a varchar(32)); -INSERT INTO t1 VALUES ('y'), ('w'); -CREATE TABLE t2 (a int); -INSERT INTO t2 VALUES (10); -CREATE TABLE t3 (a varchar(32), b int); -CREATE TABLE t4 (a varchar(32)); -INSERT INTO t4 VALUES ('y'), ('w'); -CREATE VIEW v1 AS SELECT * FROM t1; -EXPLAIN EXTENDED -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); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where -2 DEPENDENT SUBQUERY t3 system NULL NULL NULL NULL 0 0.00 Const row not found -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#1 */ select `test`.`t1`.`a` AS `a`,10 AS `a` from `test`.`t1` where !<expr_cache><10,`test`.`t1`.`a`>(<in_optimizer>(10,<exists>(/* select#2 */ select NULL from `test`.`t4` where `test`.`t4`.`a` >= `test`.`t1`.`a` and trigcond(<cache>(10) = NULL or <cache>(NULL is null)) having trigcond(NULL is 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); -a a -EXPLAIN EXTENDED -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); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where -2 DEPENDENT SUBQUERY t3 system NULL NULL NULL NULL 0 0.00 Const row not found -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#1 */ select `test`.`t1`.`a` AS `a`,10 AS `a` from `test`.`t1` where !<expr_cache><10,`test`.`t1`.`a`>(<in_optimizer>(10,<exists>(/* select#2 */ select NULL from `test`.`t4` where `test`.`t4`.`a` >= `test`.`t1`.`a` and trigcond(<cache>(10) = NULL or <cache>(NULL is null)) having trigcond(NULL is 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); -a a -DROP VIEW v1; -DROP TABLE t1,t2,t3,t4; -# -# LP bug #823237: dependent subquery with LEFT JOIN -# referencing view in WHERE -# (duplicate of LP bug #823189) -# -CREATE TABLE t1 (a int); -CREATE TABLE t2 ( b int, d int, e int); -INSERT INTO t2 VALUES (7,8,0); -CREATE TABLE t3 ( c int); -INSERT INTO t3 VALUES (0); -CREATE TABLE t4 (a int, b int, c int); -INSERT INTO t4 VALUES (93,1,0), (95,NULL,0); -CREATE VIEW v4 AS SELECT * FROM t4; -EXPLAIN EXTENDED -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); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t3 system NULL NULL NULL NULL 1 100.00 -1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 1 100.00 -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#1 */ 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#2 */ 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); -c a b c -0 93 1 0 -EXPLAIN EXTENDED -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); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t3 system NULL NULL NULL NULL 1 100.00 -1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 1 100.00 -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#1 */ 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#2 */ 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); -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 VIEW IF EXISTS v1; -DROP PROCEDURE IF EXISTS p1; -connect con2, localhost, root; -connect con3, localhost, root; -connection default; -CREATE VIEW v1 AS SELECT schema_name FROM information_schema.schemata; -CREATE PROCEDURE p1() SELECT COUNT(*), GET_LOCK('blocker', 100) FROM v1; -# CALL p1() so the view is merged. -CALL p1(); -SELECT RELEASE_LOCK('blocker'); -RELEASE_LOCK('blocker') -1 -connection con3; -SELECT GET_LOCK('blocker', 100); -GET_LOCK('blocker', 100) -1 -connection default; -# Try to CALL p1() again, this time it should block on "blocker". -# Sending: -CALL p1(); -connection con2; -# ... then try to drop the view. This should block. -# Sending: -DROP VIEW v1; -connection con3; -# Now allow CALL p1() to complete -SELECT RELEASE_LOCK('blocker'); -RELEASE_LOCK('blocker') -1 -connection default; -# Reaping: CALL p1() -SELECT RELEASE_LOCK('blocker'); -RELEASE_LOCK('blocker') -1 -connection con2; -# Reaping: DROP VIEW v1 -connection default; -DROP PROCEDURE p1; -disconnect con2; -disconnect con3; -# -# 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' -# -# BUG#14117018 - MYSQL SERVER CREATES INVALID VIEW DEFINITION -# BUG#18405221 - SHOW CREATE VIEW OUTPUT INCORRECT -# -CREATE VIEW v1 AS (SELECT '' FROM DUAL); -CREATE VIEW v2 AS (SELECT 'BUG#14117018' AS col1 FROM DUAL) UNION ALL -(SELECT '' FROM DUAL); -CREATE VIEW v3 AS (SELECT 'BUG#14117018' AS col1 FROM DUAL) UNION ALL -(SELECT '' FROM DUAL) UNION ALL -(SELECT '' FROM DUAL); -CREATE VIEW v4 AS (SELECT 'BUG#14117018' AS col1 FROM DUAL) UNION ALL -(SELECT '' AS col2 FROM DUAL) UNION ALL -(SELECT '' FROM DUAL); -CREATE VIEW v5 AS (SELECT 'buggy' AS col1, 'fix' as col2 FROM DUAL) UNION ALL -(SELECT 'buggy' as a, 'fix' as a FROM DUAL); -# Name for the column in select1 is set properly with or -# without this fix. -SHOW CREATE VIEW v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select '' AS `Name_exp_1`) latin1 latin1_swedish_ci -# Name for the column in select2 is set with this fix. -# Without this fix, name would not have set for the -# columns in select2. -SHOW CREATE VIEW v2; -View Create View character_set_client collation_connection -v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS (select 'BUG#14117018' AS `col1`) union all (select '' AS `Name_exp_1`) latin1 latin1_swedish_ci -# Name for the field item in select2 & select3 is set with this fix. -# Without this fix, name would not have set for the -# columns in select2 & select3. -SHOW CREATE VIEW v3; -View Create View character_set_client collation_connection -v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS (select 'BUG#14117018' AS `col1`) union all (select '' AS `Name_exp_1`) union all (select '' AS `Name_exp_1`) latin1 latin1_swedish_ci -# Name for the field item in select3 is set with this fix. -# Without this fix, name would not have set for the -# columns in select3. -SHOW CREATE VIEW v4; -View Create View character_set_client collation_connection -v4 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v4` AS (select 'BUG#14117018' AS `col1`) union all (select '' AS `col2`) union all (select '' AS `Name_exp_1`) latin1 latin1_swedish_ci -DROP VIEW v1, v2, v3, v4, v5; -# -# BUG#19886430: VIEW CREATION WITH NAMED COLUMNS, OVER UNION, -# IS REJECTED -# Without the patch, reports an error. -CREATE VIEW v1 (fld1, fld2) AS -SELECT 1 AS a, 2 AS b -UNION ALL -SELECT 1 AS a, 1 AS a; -# The column names are explicitly specified and not duplicates, hence -# succeeds. -CREATE VIEW v2 (fld1, fld2) AS -SELECT 1 AS a, 2 AS a -UNION ALL -SELECT 1 AS a, 1 AS a; -# The column name in the first SELECT are not duplicates, hence succeeds. -CREATE VIEW v3 AS -SELECT 1 AS a, 2 AS b -UNION ALL -SELECT 1 AS a, 1 AS a; -# Should report an error, since the explicitly specified column names are -# duplicates. -CREATE VIEW v4 (fld1, fld1) AS -SELECT 1 AS a, 2 AS b -UNION ALL -SELECT 1 AS a, 1 AS a; -ERROR 42S21: Duplicate column name 'fld1' -# Should report an error, since duplicate column name is specified in the -# First SELECT. -CREATE VIEW v4 AS -SELECT 1 AS a, 2 AS a -UNION ALL -SELECT 1 AS a, 1 AS a; -ERROR 42S21: Duplicate column name 'a' -# Cleanup -DROP VIEW v1, v2, v3; -# -# 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); -CREATE TABLE t2 ( a int, b int ); -INSERT IGNORE INTO t2 VALUES (1,0),(1,0); -CREATE TABLE t3 ( b int ); -INSERT IGNORE INTO t3 VALUES (0),(0); -CREATE OR REPLACE VIEW v2 AS SELECT * FROM t2; -SELECT * FROM t1 RIGHT JOIN v2 ON ( v2.a = t1.a ) WHERE v2.b IN ( SELECT b FROM t3 ) AND t1.b IS NULL ; -a b a b -NULL NULL 1 0 -NULL NULL 1 0 -SELECT * FROM t1 RIGHT JOIN v2 ON ( v2.a = t1.a ) WHERE v2.b IN ( SELECT b FROM t3 ) AND t1.b IS NULL ; -a b a b -NULL NULL 1 0 -NULL NULL 1 0 -DROP VIEW v2; -DROP TABLE t1, t2, t3; -# -# BUG#915222: Valgrind complains or crashes with INSERT SELECT -# within a trigger that uses a view -# -CREATE TABLE t1 (a char(1)); -CREATE TABLE t2 (d int, e char(1)); -INSERT INTO t2 VALUES (13,'z'); -CREATE TRIGGER tr AFTER UPDATE ON t2 -FOR EACH ROW -REPLACE INTO t3 -SELECT f, a AS alias FROM t3, v; -CREATE TABLE t3 (f int, g char(8)); -CREATE VIEW v AS SELECT a, e FROM t2, t1; -UPDATE t2 SET d=7; -UPDATE t2 SET d=7; -UPDATE t2 SET d=7; -UPDATE t2 SET d=7; -DROP TRIGGER tr; -DROP VIEW v; -DROP TABLE t1,t2,t3; -# -# BUG#972943: Assertion failure with INSERT SELECT within a trigger -# that uses derived table and materialized view -# -CREATE TABLE t1 (a int, b int); -INSERT INTO t1 VALUES (1,0), (2,8); -CREATE ALGORITHM=TEMPTABLE VIEW v1 -AS SELECT * FROM t1; -CREATE TABLE t2 (c int); -CREATE TABLE t3 (d int, e int); -CREATE TRIGGER tr BEFORE INSERT ON t2 FOR EACH ROW -INSERT INTO t3 -SELECT t1.* -FROM (SELECT * FROM t1 WHERE b IN (SELECT b FROM v1)) AS alias1, t1 -WHERE t1.a = 3 OR t1.a > 5; -INSERT INTO t2 VALUES (1); -DROP TRIGGER tr; -DROP VIEW v1; -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; -INSERT INTO v2 (a) VALUES (1) ; -select * from t1; -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 2 -# -# MDEV-486 LP BUG#1010116 Incorrect query results in -# view and derived tables -# -SELECT -`Derived1`.`id`, -`Derived2`.`Val1` -FROM (select 30631 as `id`) AS `Derived1` LEFT OUTER JOIN (SELECT -2 as `id`, -1 AS `Val1` -FROM (select 30631 as `id`) AS `Derived3`) AS `Derived2` ON `Derived1`.`id` = `Derived2`.`id`; -id Val1 -30631 NULL -create table t1 ( id int ); -insert into t1 values (30631); -create table t2 ( id int ); -insert into t2 values (30631); -create algorithm=MERGE view v2 as select 2 as id, 1 as val1 from t2; -select t1.*, v2.* from t1 left join v2 on t1.id = v2.id; -id id val1 -30631 NULL NULL -drop view v2; -drop table t1,t2; -create table t1 ( id int ); -insert into t1 values (30631); -create table t2 ( id int ); -insert into t2 values (30631); -create algorithm=MERGE view v2 as select 2 as id, id is null as bbb, id as iddqd, 1 as val1 from t2; -select t1.*, v2.* from t1 left join v2 on t1.id = v2.id; -id id bbb iddqd val1 -30631 NULL NULL NULL NULL -drop view v2; -drop table t1,t2; -# -# MDEV-3914: Wrong result (NULLs instead of real values) -# with INNER and RIGHT JOIN in a FROM subquery, derived_merge=on -# (fix of above MDEV-486 fix) -# -SET @save_optimizer_switch_MDEV_3914=@@optimizer_switch; -SET optimizer_switch = 'derived_merge=on'; -CREATE TABLE t1 (a INT) ENGINE=MyISAM; -INSERT INTO t1 VALUES (1),(2); -CREATE TABLE t2 (b INT) ENGINE=MyISAM; -INSERT INTO t2 VALUES (3),(4); -CREATE TABLE t3 (c INT) ENGINE=MyISAM; -INSERT INTO t3 VALUES (5),(6); -SELECT * FROM ( SELECT c FROM ( t1 INNER JOIN t2 ) RIGHT JOIN t3 ON a = c ) AS alias; -c -5 -6 -SET optimizer_switch = 'derived_merge=off'; -SELECT * FROM ( SELECT c FROM ( t1 INNER JOIN t2 ) RIGHT JOIN t3 ON a = c ) AS alias; -c -5 -6 -SET optimizer_switch=@save_optimizer_switch_MDEV_3914; -drop table t1,t2,t3; -# -# MDEV-589 (LP BUG#1007647) : -# Assertion `vcol_table == 0 || vcol_table == table' failed in -# fill_record(THD*, List<Item>&, List<Item>&, bool) -# -CREATE TABLE t1 (f1 INT, f2 INT); -CREATE TABLE t2 (f1 INT, f2 INT); -CREATE ALGORITHM=MERGE VIEW v1 AS SELECT a1.f1, a2.f2 FROM t1 AS a1, t1 AS a2; -CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM v1; -CREATE ALGORITHM=MERGE VIEW v3 AS SELECT a1.f1, a2.f2 FROM t1 AS a1, t2 AS a2; -CREATE ALGORITHM=MERGE VIEW v4 AS SELECT * FROM v3; -INSERT INTO v3 (f1, f2) VALUES (1, 2); -ERROR HY000: Can not modify more than one base table through a join view 'test.v3' -INSERT INTO v1 (f1, f2) VALUES (1, 2); -ERROR HY000: Can not modify more than one base table through a join view 'test.v1' -INSERT INTO v4 (f1, f2) VALUES (1, 2); -ERROR HY000: Can not modify more than one base table through a join view 'test.v4' -INSERT INTO v2 (f1, f2) VALUES (1, 2); -ERROR HY000: Can not modify more than one base table through a join view 'test.v2' -drop view v4,v3,v2,v1; -drop table t1,t2; -# -# MDEV-3799 fix of above bugfix (MDEV-589) -# Wrong result (NULLs instead of real values) with RIGHT JOIN -# in a FROM subquery and derived_merge=on -# -CREATE TABLE t1 (f1 INT) ENGINE=MyISAM; -INSERT INTO t1 VALUES (4),(6); -CREATE TABLE t2 (f2 INT) ENGINE=MyISAM; -INSERT INTO t2 VALUES (7),(8); -SELECT * FROM ( -SELECT * FROM t1 RIGHT JOIN t2 ON f1 = f2 -) AS alias; -f1 f2 -NULL 7 -NULL 8 -SELECT * FROM ( -SELECT * FROM t2 LEFT JOIN t1 ON f1 = f2 -) AS alias; -f2 f1 -7 NULL -8 NULL -drop tables t1,t2; -# -# MDEV-3876 Wrong result (extra rows) with ALL subquery -# from a MERGE view (duplicate of MDEV-3873) -# -CREATE TABLE t1 (a INT NOT NULL) ENGINE=MyISAM; -INSERT INTO t1 VALUES (1),(2); -CREATE TABLE t2 (b INT NOT NULL) ENGINE=MyISAM; -INSERT INTO t2 VALUES (1),(3); -CREATE OR REPLACE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t2; -SELECT a FROM t1 AS alias -WHERE a >= ALL ( -SELECT b FROM t1 LEFT JOIN v1 ON (a = b) -WHERE a = alias.a ); -a -1 -drop view v1; -drop table t1,t2; -# -# MDEV-4593: p_s: crash in simplify_joins with delete using subselect -# from view -# -create table `t1`(`a` int); -create table `t2`(`a` int); -create or replace view `v1` as select `a` from `t1`; -prepare s from "delete from `t2` order by (select 1 from `v1`)"; -execute s; -deallocate prepare s; -drop view v1; -drop tables t1,t2; -# -# MDEV-5034 (duplicate of MDEV-5107): -# Left Join Yields All Nulls Instead of Appropriate Matches -# -# test #1 -CREATE TABLE t1 (state VARCHAR(32), INDEX(state)); -INSERT INTO t1 VALUES ('Indiana'),('Vermont'); -CREATE TABLE t2 (state VARCHAR(32)); -INSERT INTO t2 VALUES ('Hawaii'),('Oregon'),('Vermont'); -CREATE ALGORITHM=MERGE VIEW v1 AS SELECT t1.* FROM t2, t1; -SELECT * FROM t1 AS outer_t1 LEFT JOIN v1 AS joined_t1 -ON (joined_t1.state = outer_t1.state AND joined_t1.state IN ( SELECT 'Vermont' UNION SELECT 'Florida' ) ); -state state -Indiana NULL -Vermont Vermont -Vermont Vermont -Vermont Vermont -SELECT * FROM t1 AS outer_t1 LEFT JOIN (SELECT t1.* FROM t2, t1) AS joined_t1 ON (joined_t1.state = outer_t1.state AND joined_t1.state IN ( SELECT 'Vermont' UNION SELECT 'Florida' ) ); -state state -Indiana NULL -Vermont Vermont -Vermont Vermont -Vermont Vermont -drop view v1; -drop table t1, t2; -# test #1 -CREATE TABLE t1 (a INT, b VARCHAR(1), INDEX(b,a)); -INSERT INTO t1 VALUES (4,'p'),(1,'q'),(9,'w'); -CREATE TABLE t2 (c VARCHAR(1), INDEX(c)); -INSERT INTO t2 VALUES ('q'),('a'); -CREATE ALGORITHM=MERGE VIEW v1 AS SELECT t1a.* FROM t1, t1 AS t1a; -SELECT * FROM t2 LEFT JOIN v1 ON ( c=b AND a IN ( 1,6 ) ); -c a b -a NULL NULL -q 1 q -q 1 q -q 1 q -CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT t1a.* FROM t1, t1 AS t1a; -SELECT * FROM t2 LEFT JOIN v1 ON ( c=b AND a IN ( 1,6 ) ); -c a b -a NULL NULL -q 1 q -q 1 q -q 1 q -drop view v1; -drop table t1,t2; -# -# MDEV-5153: Server crashes in Item_ref::fix_fields on 2nd execution -# of PS with LEFT JOIN and MERGE view or SELECT SQ -# -CREATE TABLE t1 (i1 INT, c1 VARCHAR(6)) ENGINE=MyISAM; -INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); -CREATE TABLE t2 (c2 VARCHAR(6)) ENGINE=MyISAM; -INSERT INTO t2 VALUES ('foobar'),('qux'); -CREATE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1 WHERE ( c1 ) IN ( SELECT c2 FROM t2 ) AND i1 <= 2 ; -PREPARE stmt FROM 'SELECT * FROM t1 LEFT JOIN v1 ON (v1.i1 = t1.i1)'; -EXECUTE stmt; -i1 c1 i1 c1 -1 foo NULL NULL -2 bar NULL NULL -EXECUTE stmt; -i1 c1 i1 c1 -1 foo NULL NULL -2 bar NULL NULL -drop view v1; -CREATE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1 WHERE ( c1, c1 ) IN ( SELECT c2, c2 FROM t2 ) AND i1 <= 2 ; -EXECUTE stmt; -i1 c1 i1 c1 -1 foo NULL NULL -2 bar NULL NULL -EXECUTE stmt; -i1 c1 i1 c1 -1 foo NULL NULL -2 bar NULL NULL -deallocate prepare stmt; -drop view v1; -drop table t1,t2; -create table t1 (a int); -insert into t1 values (1),(2); -create view v1 (a,r) as select a,rand() from t1; -create table t2 select a, r as r1, r as r2, r as r3 from v1; -select a, r1 = r2, r2 = r3 from t2; -a r1 = r2 r2 = r3 -1 1 1 -2 1 1 -drop view v1; -drop table t1,t2; -# -# MDEV-5515: 2nd execution of a prepared statement returns wrong results -# -CREATE TABLE t1 (i1 INT, j1 INT NOT NULL, PRIMARY KEY (i1)); -INSERT INTO t1 VALUES (30,300),(40,400); -CREATE TABLE t2 (i2 INT); -INSERT INTO t2 VALUES (50),(60); -CREATE TABLE t3 (c3 VARCHAR(20), i3 INT); -INSERT INTO t3 VALUES ('a',10),('b',2); -CREATE TABLE t4 (i4 INT); -INSERT INTO t4 VALUES (1),(2); -DROP VIEW IF EXISTS v1; -Warnings: -Note 4092 Unknown VIEW: 'test.v1' -CREATE VIEW v1 AS select coalesce(j1,i3) AS v1_field1 from t2 join t3 left join t1 on ( i1 = i2 ); -CREATE VIEW v2 AS select v1_field1 from t4 join v1; -prepare my_stmt from "select v1_field1 from v2"; -execute my_stmt; -v1_field1 -10 -10 -10 -10 -2 -2 -2 -2 -execute my_stmt; -v1_field1 -10 -10 -10 -10 -2 -2 -2 -2 -deallocate prepare my_stmt; -DROP VIEW v1,v2; -DROP TABLE t1,t2,t3,t4; -# -#MDEV-5717: Server crash with insert statement containing DEFAULT into -#view -# -CREATE TABLE t1 ( -`id` int(10) unsigned NOT NULL AUTO_INCREMENT, -`test` tinyint(3) unsigned NOT NULL DEFAULT '0', -PRIMARY KEY (`id`) -); -CREATE VIEW v1 AS (select t1.id AS id, t1.test AS test from t1); -INSERT INTO v1 SET test = DEFAULT; -select * from v1; -id test -1 0 -drop view v1; -drop table t1; -# -# MDEV-5981: name resolution issues with views and multi-update -# in ps-protocol -# -create table t1 (id1 int primary key, val1 varchar(20)); -insert into t1 values (1, 'test1'); -create table t2 (id2 int primary key, val2 varchar(20)); -insert into t2 values (1, 'test2'); -create algorithm=merge view v1 as select id1 as id1v1, val1 as val1v1 from t1; -create algorithm=merge view v2 as -select t2.id2 as id2v2, t2.val2 as val2v2 -from t2, v1 -where t2.id2 = v1.id1v1; -prepare stmt1 from "update v2 set val2v2 = 'test19' where 1 = id2v2"; -execute stmt1; -deallocate prepare stmt1; -drop view v1,v2; -drop table 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; -CREATE TABLE `t1` ( -`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, -`f0` int(11) unsigned NOT NULL DEFAULT '0', -`f1` int(11) unsigned NOT NULL DEFAULT '0', -PRIMARY KEY (`id`), -UNIQUE KEY `id` (`id`) -); -CREATE TABLE `t2` ( -`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, -`f02` bigint(20) unsigned NOT NULL DEFAULT '0', -`f03` int(11) unsigned NOT NULL DEFAULT '0', -PRIMARY KEY (`id`), -UNIQUE KEY `id` (`id`) -); -CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `v1` AS -SELECT -`t1`.`f0` AS `f0`, -`t1`.`f1` AS `f1`, -`t2`.`f02` AS `f02`, -`t2`.`f03` AS `f03` -FROM -(`t1` LEFT JOIN `t2` ON((`t1`.`id` = `t2`.`f02`))); -CREATE FUNCTION `f1`( -p0 BIGINT(20) UNSIGNED -) -RETURNS bigint(20) unsigned -DETERMINISTIC -CONTAINS SQL -SQL SECURITY DEFINER -COMMENT '' -BEGIN -DECLARE k0 INTEGER UNSIGNED DEFAULT 0; -DECLARE lResult INTEGER UNSIGNED DEFAULT 0; -SET k0 = 0; -WHILE k0 < 1 DO -SELECT COUNT(*) as `f00` INTO lResult FROM `v1` WHERE `v1`.`f0` = p0; -- BUG -SET k0 = k0 + 1; -END WHILE; -RETURN(k0); -END| -SELECT `f1`(1); -`f1`(1) -1 -SELECT `f1`(1); -`f1`(1) -1 -SELECT `f1`(1); -`f1`(1) -1 -SELECT `f1`(1); -`f1`(1) -1 -DROP FUNCTION f1; -DROP VIEW v1; -DROP TABLE t1, t2; -create view v1 as select 1; -FOUND 1 /mariadb-version/ in v1.frm -drop view v1; -# -# MDEV-7260: Crash in get_best_combination when executing multi-table -# UPDATE with nested views -# -CREATE TABLE `t1` (`id` bigint(20)); -INSERT INTO `t1` VALUES (1),(2); -CREATE TABLE `t2` (`id` bigint(20)); -CREATE TABLE `t3` (`id` bigint(20), `flag` tinyint(4)); -create view v1 as select id from t1; -create view v2 as select t2.* from (t2 left join v1 using (id)); -update t3 left join v2 using (id) set flag=flag+1; -drop view v2, v1; -drop table t1, t2, t3; -# -# MDEV-7207 - ALTER VIEW does not change ALGORITM -# -create table t1 (a int, b int); -create algorithm=temptable view v2 (c) as select b+1 from t1; -show create view v2; -View Create View character_set_client collation_connection -v2 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t1`.`b` + 1 AS `c` from `t1` latin1 latin1_swedish_ci -alter algorithm=undefined view v2 (c) as select b+1 from t1; -show create view v2; -View Create View character_set_client collation_connection -v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t1`.`b` + 1 AS `c` from `t1` latin1 latin1_swedish_ci -alter algorithm=merge view v2 (c) as select b+1 from t1; -show create view v2; -View Create View character_set_client collation_connection -v2 CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t1`.`b` + 1 AS `c` from `t1` latin1 latin1_swedish_ci -drop view v2; -drop table t1; -# -# MDEV-8554: Server crashes in base_list_iterator::next_fast on 1st execution of PS with a multi-table update -# -CREATE TABLE t1 (a INT) ENGINE=MyISAM; -INSERT INTO t1 VALUES (1),(2); -CREATE TABLE t2 (b INT) ENGINE=MyISAM; -INSERT INTO t2 VALUES (3),(4); -CREATE TABLE t3 (c INT) ENGINE=MyISAM; -INSERT INTO t3 VALUES (5),(6); -CREATE OR REPLACE ALGORITHM=MERGE VIEW v3 AS SELECT * FROM t3; -PREPARE stmt FROM 'UPDATE t1, t2 SET a = 1 WHERE a IN ( SELECT 0 FROM t3 )'; -UPDATE t1, t2 SET a = 1 WHERE a IN ( SELECT 0 FROM v3 ); -EXECUTE stmt; -DROP TABLE t1, t2, t3; -DROP VIEW v3; -# -# MDEV-8632: Segmentation fault on INSERT -# -CREATE TABLE `t1` ( -`id` int(10) unsigned NOT NULL, -`r` float NOT NULL, -PRIMARY KEY (`id`) -) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -create view v1 as select id, if(r=r,1,2) as d from t1; -create view v2 as -select id, -d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d as p -from v1; -insert into t1 (id, r) -select id,p from -( -select id, -d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d as p -from ( -select id, if(r=r,1,2) as d -from t1 -) a -) b -on duplicate key update r=p; -insert into t1 (id, r) -select id,p from v2 -on duplicate key update r=p; -prepare stmt from "insert into t1 (id, r) select id,p from v2 on duplicate key update r=p"; -execute stmt; -execute stmt; -deallocate prepare stmt; -drop view v1,v2; -drop table `t1`; -create table t1 (a int, b int); -create view v1 as select a+b from t1; -alter table v1 check partition p1; -Table Op Msg_type Msg_text -test.v1 check Error 'test.v1' is not of type 'BASE TABLE' -test.v1 check status Operation failed -drop view v1; -drop table t1; -# -# MDEV-10419: crash in mariadb 10.1.16-MariaDB-1~trusty -# -CREATE TABLE t1 (c1 CHAR(13)); -CREATE TABLE t2 (c2 CHAR(13)); -CREATE FUNCTION f() RETURNS INT RETURN 0; -CREATE OR REPLACE VIEW v1 AS select f() from t1 where c1 in (select c2 from t2); -DROP FUNCTION f; -SHOW CREATE VIEW v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `f`() AS `f()` from `t1` where `test`.`t1`.`c1` in (select `test`.`t2`.`c2` from `t2`) latin1 latin1_swedish_ci -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; -drop table t1,t2; -# -# MDEV-12099: usage of mergeable view with LEFT JOIN -# that can be converted to INNER JOIN -# -create table t1 (a int, b int, key(a)) engine=myisam; -insert into t1 values -(3,20), (7,10), (2,10), (4,30), (8,70), -(7,70), (9,100), (9,60), (8,80), (7,60); -create table t2 (c int, d int, key (c)) engine=myisam; -insert into t2 values -(50,100), (20, 200), (10,300), -(150,100), (120, 200), (110,300), -(250,100), (220, 200), (210,300); -create table t3(e int, f int not null, key(e), unique (f)) engine=myisam; -insert into t3 values -(100, 3), (300, 5), (400, 4), (300,7), -(300,2), (600, 13), (800, 15), (700, 14), -(600, 23), (800, 25), (700, 24); -create view v1 as -select * from t2 left join t3 on t3.e=t2.d where t3.f is not null; -select * -from t1 left join v1 on v1.c=t1.b -where t1.a < 5; -a b c d e f -2 10 10 300 300 5 -2 10 10 300 300 7 -2 10 10 300 300 2 -3 20 NULL NULL NULL NULL -4 30 NULL NULL NULL NULL -select * -from t1 left join ( t2 left join t3 on t3.e=t2.d ) -on t2.c=t1.b and t3.f is not null -where t1.a < 5; -a b c d e f -2 10 10 300 300 5 -2 10 10 300 300 7 -2 10 10 300 300 2 -3 20 NULL NULL NULL NULL -4 30 NULL NULL NULL NULL -explain extended -select * -from t1 left join v1 on v1.c=t1.b -where t1.a < 5; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range a a 5 NULL 3 100.00 Using index condition -1 SIMPLE t2 ref c c 5 test.t1.b 2 100.00 Using where -1 SIMPLE t3 ref f,e e 5 test.t2.d 2 100.00 Using where -Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(`test`.`t2`.`c` = `test`.`t1`.`b` and `test`.`t3`.`e` = `test`.`t2`.`d` and `test`.`t3`.`f` is not null and `test`.`t1`.`b` is not null and `test`.`t2`.`d` is not null) where `test`.`t1`.`a` < 5 -explain extended -select * -from t1 left join ( t2 left join t3 on t3.e=t2.d ) -on t2.c=t1.b and t3.f is not null -where t1.a < 5; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range a a 5 NULL 3 100.00 Using index condition -1 SIMPLE t2 ref c c 5 test.t1.b 2 100.00 Using where -1 SIMPLE t3 ref f,e e 5 test.t2.d 2 100.00 Using where -Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(`test`.`t2`.`c` = `test`.`t1`.`b` and `test`.`t3`.`e` = `test`.`t2`.`d` and `test`.`t3`.`f` is not null and `test`.`t1`.`b` is not null and `test`.`t2`.`d` is not null) where `test`.`t1`.`a` < 5 -explain extended -select * -from t1 left join v1 on v1.c=t1.b and v1.f=t1.a -where t1.a < 5; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range a a 5 NULL 3 100.00 Using index condition -1 SIMPLE t3 eq_ref f,e f 4 test.t1.a 1 100.00 Using where -1 SIMPLE t2 ref c c 5 test.t1.b 2 100.00 Using where -Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(`test`.`t2`.`c` = `test`.`t1`.`b` and `test`.`t3`.`f` = `test`.`t1`.`a` and `test`.`t2`.`d` = `test`.`t3`.`e` and `test`.`t1`.`a` is not null and `test`.`t1`.`a` is not null and `test`.`t1`.`b` is not null) where `test`.`t1`.`a` < 5 -explain extended -select * -from t1 left join ( t2 left join t3 on t3.e=t2.d ) -on t2.c=t1.b and t3.f=t1.a and t3.f is not null -where t1.a < 5; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range a a 5 NULL 3 100.00 Using index condition -1 SIMPLE t3 eq_ref f,e f 4 test.t1.a 1 100.00 Using where -1 SIMPLE t2 ref c c 5 test.t1.b 2 100.00 Using where -Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(`test`.`t2`.`c` = `test`.`t1`.`b` and `test`.`t3`.`f` = `test`.`t1`.`a` and `test`.`t2`.`d` = `test`.`t3`.`e` and `test`.`t1`.`a` is not null and `test`.`t1`.`a` is not null and `test`.`t1`.`b` is not null) where `test`.`t1`.`a` < 5 -drop view v1; -drop table t1,t2,t3; -# -# MDEV-11240: Server crashes in check_view_single_update or -# Assertion `derived->table' failed in mysql_derived_merge_for_insert -# -CREATE TABLE t3 (a INT); -CREATE ALGORITHM = MERGE VIEW v1 AS SELECT t2.a FROM t3 AS t1, t3 AS t2; -CREATE ALGORITHM = MERGE VIEW v2 AS SELECT * FROM v1; -PREPARE stmt FROM 'REPLACE INTO v2 SELECT a FROM t3'; -ERROR HY000: Can not insert into join view 'test.v2' without fields list -drop view v1,v2; -drop table t3; -# -# MDEV-14619: VIEW and GROUP_CONCAT -# -CREATE TABLE t1 (str text); -INSERT INTO t1 VALUES ("My"),("SQL"); -CREATE VIEW v1 AS SELECT GROUP_CONCAT(str SEPARATOR '\\') FROM t1; -SELECT * FROM v1; -GROUP_CONCAT(str SEPARATOR '\\') -My\SQL -SHOW CREATE VIEW v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select group_concat(`t1`.`str` separator '\\') AS `GROUP_CONCAT(str SEPARATOR '\\')` from `t1` latin1 latin1_swedish_ci -drop view v1; -drop table t1; -# ----------------------------------------------------------------- -# -- End of 5.5 tests. -# ----------------------------------------------------------------- -# some subqueries in SELECT list test -create table t1 (a int, b int); -create table t2 (a int, b int); -insert into t1 values (1,2), (3,4), (3,3), (5,6), (7,8), (9,10); -insert into t2 values (1,2), (3,4), (3,3), (5,6), (7,8), (9,10); -create algorithm=merge view v1 as select t1.a as a, (select max(b) from t2 where t1.a=t2.a) as c from t1; -explain extended -select * from v1; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 -3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 6 100.00 Using where -Warnings: -Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2 -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`>((/* select#3 */ select max(`test`.`t2`.`b`) from `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a`)) AS `c` from `test`.`t1` -select * from v1; -a c -1 2 -3 4 -3 4 -5 6 -7 8 -9 10 -explain extended -select * from t2, v1 where t2.a=v1.a; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 ALL NULL NULL NULL NULL 6 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) -3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 6 100.00 Using where -Warnings: -Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2 -Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`>((/* select#3 */ select max(`test`.`t2`.`b`) from `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a`)) AS `c` from `test`.`t2` join `test`.`t1` where `test`.`t1`.`a` = `test`.`t2`.`a` -select * from t2, v1 where t2.a=v1.a; -a b a c -1 2 1 2 -3 4 3 4 -3 3 3 4 -3 4 3 4 -3 3 3 4 -5 6 5 6 -7 8 7 8 -9 10 9 10 -explain extended -select * from t1, v1 where t1.a=v1.a; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) -3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 6 100.00 Using where -Warnings: -Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2 -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`>((/* select#3 */ select max(`test`.`t2`.`b`) from `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a`)) AS `c` from `test`.`t1` join `test`.`t1` where `test`.`t1`.`a` = `test`.`t1`.`a` -select * from t1, v1 where t1.a=v1.a; -a b a c -1 2 1 2 -3 4 3 4 -3 3 3 4 -3 4 3 4 -3 3 3 4 -5 6 5 6 -7 8 7 8 -9 10 9 10 -explain extended -select * from t1, v1 where t1.b=v1.c; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) -3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 6 100.00 Using where -Warnings: -Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2 -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`>((/* select#3 */ select max(`test`.`t2`.`b`) from `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a`)) AS `c` from `test`.`t1` join `test`.`t1` where `test`.`t1`.`b` = <expr_cache><`test`.`t1`.`a`>((/* select#3 */ select max(`test`.`t2`.`b`) from `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a`)) -select * from t1, v1 where t1.b=v1.c; -a b a c -1 2 1 2 -3 4 3 4 -3 4 3 4 -5 6 5 6 -7 8 7 8 -9 10 9 10 -explain extended -select * from t2, t1, v1 where t1.a=t2.a and t1.a=v1.a; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 ALL NULL NULL NULL NULL 6 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (incremental, BNL join) -3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 6 100.00 Using where -Warnings: -Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2 -Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`>((/* select#3 */ select max(`test`.`t2`.`b`) from `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a`)) AS `c` from `test`.`t2` join `test`.`t1` join `test`.`t1` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t1`.`a` = `test`.`t2`.`a` -select * from t2, t1, v1 where t1.a=t2.a and t1.a=v1.a; -a b a b a c -1 2 1 2 1 2 -3 4 3 4 3 4 -3 3 3 4 3 4 -3 4 3 3 3 4 -3 3 3 3 3 4 -3 4 3 4 3 4 -3 3 3 4 3 4 -3 4 3 3 3 4 -3 3 3 3 3 4 -5 6 5 6 5 6 -7 8 7 8 7 8 -9 10 9 10 9 10 -drop view v1; -drop table t1,t2; -create table t1 (i int not null); -insert into t1 values (1),(2); -create table t2 (j int not null); -insert into t2 values (11),(12); -create algorithm=merge view v3 as select t1.* from t2 left join t1 on (t2.j = t1.i); -prepare stmt from 'select count(v3.i) from t1, v3'; -execute stmt; -count(v3.i) -0 -execute stmt; -count(v3.i) -0 -drop table t1, t2; -drop view v3; -# -# MDEV-8525: mariadb 10.0.20 crashing when data is read by Kodi -# media center (http://kodi.tv). -# -CREATE TABLE `t1` ( -`idSong` int(11) NOT NULL AUTO_INCREMENT, -`idAlbum` int(11) DEFAULT NULL, -`idPath` int(11) DEFAULT NULL, -`strArtists` text, -`strGenres` text, -`strTitle` varchar(512) DEFAULT NULL, -`iTrack` int(11) DEFAULT NULL, -`iDuration` int(11) DEFAULT NULL, -`iYear` int(11) DEFAULT NULL, -`dwFileNameCRC` text, -`strFileName` text, -`strMusicBrainzTrackID` text, -`iTimesPlayed` int(11) DEFAULT NULL, -`iStartOffset` int(11) DEFAULT NULL, -`iEndOffset` int(11) DEFAULT NULL, -`idThumb` int(11) DEFAULT NULL, -`lastplayed` varchar(20) DEFAULT NULL, -`rating` char(1) DEFAULT '0', -`comment` text, -`mood` text, -PRIMARY KEY (`idSong`), -UNIQUE KEY `idxSong7` (`idAlbum`,`strMusicBrainzTrackID`(36)), -KEY `idxSong` (`strTitle`(255)), -KEY `idxSong1` (`iTimesPlayed`), -KEY `idxSong2` (`lastplayed`), -KEY `idxSong3` (`idAlbum`), -KEY `idxSong6` (`idPath`,`strFileName`(255)) -) DEFAULT CHARSET=utf8; -INSERT INTO `t1` VALUES (1,1,1,'strArtists1','strGenres1','strTitle1',1,100,2000,NULL,'strFileName1','strMusicBrainzTrackID1',0,0,0,NULL,NULL,'0','',''),(2,2,2,'strArtists2','strGenres2','strTitle2',2,200,2001,NULL,'strFileName2','strMusicBrainzTrackID2',0,0,0,NULL,NULL,'0','',''); -CREATE TABLE `t2` ( -`idAlbum` int(11) NOT NULL AUTO_INCREMENT, -`strAlbum` varchar(256) DEFAULT NULL, -`strMusicBrainzAlbumID` text, -`strArtists` text, -`strGenres` text, -`iYear` int(11) DEFAULT NULL, -`idThumb` int(11) DEFAULT NULL, -`bCompilation` int(11) NOT NULL DEFAULT '0', -`strMoods` text, -`strStyles` text, -`strThemes` text, -`strReview` text, -`strImage` text, -`strLabel` text, -`strType` text, -`iRating` int(11) DEFAULT NULL, -`lastScraped` varchar(20) DEFAULT NULL, -`dateAdded` varchar(20) DEFAULT NULL, -`strReleaseType` text, -PRIMARY KEY (`idAlbum`), -UNIQUE KEY `idxAlbum_2` (`strMusicBrainzAlbumID`(36)), -KEY `idxAlbum` (`strAlbum`(255)), -KEY `idxAlbum_1` (`bCompilation`) -) DEFAULT CHARSET=utf8; -INSERT INTO `t2` VALUES (1,'strAlbum1','strMusicBrainzAlbumID1','strArtists1','strGenres1',2000,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'album'); -CREATE TABLE `t3` ( -`idArtist` int(11) DEFAULT NULL, -`idAlbum` int(11) DEFAULT NULL, -`strJoinPhrase` text, -`boolFeatured` int(11) DEFAULT NULL, -`iOrder` int(11) DEFAULT NULL, -`strArtist` text, -UNIQUE KEY `idxAlbumArtist_1` (`idAlbum`,`idArtist`), -UNIQUE KEY `idxAlbumArtist_2` (`idArtist`,`idAlbum`), -KEY `idxAlbumArtist_3` (`boolFeatured`) -) DEFAULT CHARSET=utf8; -INSERT INTO `t3` VALUES (1,1,'',0,0,'strArtist1'); -CREATE TABLE `t4` ( -`idArtist` int(11) NOT NULL AUTO_INCREMENT, -`strArtist` varchar(256) DEFAULT NULL, -`strMusicBrainzArtistID` text, -`strBorn` text, -`strFormed` text, -`strGenres` text, -`strMoods` text, -`strStyles` text, -`strInstruments` text, -`strBiography` text, -`strDied` text, -`strDisbanded` text, -`strYearsActive` text, -`strImage` text, -`strFanart` text, -`lastScraped` varchar(20) DEFAULT NULL, -`dateAdded` varchar(20) DEFAULT NULL, -PRIMARY KEY (`idArtist`), -UNIQUE KEY `idxArtist1` (`strMusicBrainzArtistID`(36)), -KEY `idxArtist` (`strArtist`(255)) -) DEFAULT CHARSET=utf8; -INSERT INTO `t4` VALUES (1,'strArtist1','strMusicBrainzArtistID',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); -CREATE VIEW `v1` AS select `t2`.`idAlbum` AS `idAlbum`,`t2`.`strAlbum` AS `strAlbum`,`t2`.`strMusicBrainzAlbumID` AS `strMusicBrainzAlbumID`,`t2`.`strArtists` AS `strArtists`,`t2`.`strGenres` AS `strGenres`,`t2`.`iYear` AS `iYear`,`t2`.`strMoods` AS `strMoods`,`t2`.`strStyles` AS `strStyles`,`t2`.`strThemes` AS `strThemes`,`t2`.`strReview` AS `strReview`,`t2`.`strLabel` AS `strLabel`,`t2`.`strType` AS `strType`,`t2`.`strImage` AS `strImage`,`t2`.`iRating` AS `iRating`,`t2`.`bCompilation` AS `bCompilation`,(select min(`t1`.`iTimesPlayed`) from `t1` where (`t1`.`idAlbum` = `t2`.`idAlbum`)) AS `iTimesPlayed`,`t2`.`strReleaseType` AS `strReleaseType` from `t2`; -CREATE VIEW `v2` AS select `t3`.`idAlbum` AS `idAlbum`,`t3`.`idArtist` AS `idArtist`,`t4`.`strArtist` AS `strArtist`,`t4`.`strMusicBrainzArtistID` AS `strMusicBrainzArtistID`,`t3`.`boolFeatured` AS `boolFeatured`,`t3`.`strJoinPhrase` AS `strJoinPhrase`,`t3`.`iOrder` AS `iOrder` from (`t3` join `t4` on((`t3`.`idArtist` = `t4`.`idArtist`))); -SELECT v1.*,v2.* FROM v1 LEFT JOIN v2 ON v1.idAlbum = v2.idAlbum WHERE v1.idAlbum = 1 ORDER BY v2.iOrder; -idAlbum strAlbum strMusicBrainzAlbumID strArtists strGenres iYear strMoods strStyles strThemes strReview strLabel strType strImage iRating bCompilation iTimesPlayed strReleaseType idAlbum idArtist strArtist strMusicBrainzArtistID boolFeatured strJoinPhrase iOrder -1 strAlbum1 strMusicBrainzAlbumID1 strArtists1 strGenres1 2000 NULL NULL NULL NULL NULL NULL NULL NULL 0 0 album 1 1 strArtist1 strMusicBrainzArtistID 0 0 -drop view v1,v2; -drop table t1,t2,t3,t4; -# -# MDEV-8913: Derived queries with same column names as final -# projection causes issues when using Order By -# -create table t1 (field int); -insert into t1 values (10),(5),(3),(8),(20); -SELECT sq.f2 AS f1, sq.f1 AS f2 -FROM ( SELECT field AS f1, 1 AS f2 FROM t1) AS sq -ORDER BY sq.f1; -f1 f2 -1 3 -1 5 -1 8 -1 10 -1 20 -create view v1 as SELECT field AS f1, 1 AS f2 FROM t1; -SELECT sq.f2 AS f1, sq.f1 AS f2 -FROM v1 AS sq -ORDER BY sq.f1; -f1 f2 -1 3 -1 5 -1 8 -1 10 -1 20 -drop view v1; -create table t2 SELECT field AS f1, 1 AS f2 FROM t1; -SELECT -sq.f2 AS f1, -sq.f1 AS f2 -FROM t2 AS sq -ORDER BY sq.f1; -f1 f2 -1 3 -1 5 -1 8 -1 10 -1 20 -drop table t1, t2; -SELECT 1 FROM (SELECT 1 as a) AS b HAVING (SELECT `SOME_GARBAGE`.b.a)=1; -ERROR 42S22: Unknown column 'SOME_GARBAGE.b.a' in 'field list' -# -# MDEV-10035: DBUG_ASSERT on CREATE VIEW v1 AS SELECT * FROM t1 -# FOR UPDATE -# -CREATE TABLE t1 (a INT); -insert into t1 values (1),(2); -CREATE VIEW v1 AS SELECT * FROM t1 FOR UPDATE; -SHOW CREATE VIEW v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` for update latin1 latin1_swedish_ci -select * from v1; -a -1 -2 -DROP VIEW v1; -CREATE VIEW v1 AS SELECT * FROM t1 LOCK IN SHARE MODE; -SHOW CREATE VIEW v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` lock in share mode latin1 latin1_swedish_ci -select * from v1; -a -1 -2 -DROP VIEW v1; -DROP TABLE t1; -# -# MDEV-8642: WHERE Clause not applied on View - Empty result set returned -# -CREATE TABLE `t1` ( -`id` int(20) NOT NULL AUTO_INCREMENT, -`use_case` int(11) DEFAULT NULL, -`current_deadline` date DEFAULT NULL, -`ts_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, -PRIMARY KEY (`id`), -UNIQUE KEY `id_UNIQUE` (`id`) -) ENGINE=MyISAM AUTO_INCREMENT=13976 DEFAULT CHARSET=latin1; -INSERT INTO `t1` VALUES (1,10,'2015-12-18','2015-08-18 08:38:16'); -INSERT INTO `t1` VALUES (2,20,'2015-10-18','2015-08-18 08:43:30'); -CREATE VIEW v1 AS SELECT -use_case as use_case_id, -( -SELECT -deadline_sub.current_deadline -FROM -t1 deadline_sub -WHERE -deadline_sub.use_case = use_case_id -AND ts_create = (SELECT -MIN(ts_create) -FROM -t1 startdate_sub -WHERE -startdate_sub.use_case = use_case_id -) -) AS InitialDeadline -FROM -t1; -SELECT * FROM v1 where use_case_id = 10; -use_case_id InitialDeadline -10 2015-12-18 -drop view v1; -drop table t1; -# -# MDEV-12666: CURRENT_ROLE() and DATABASE() does not work in a view -# -# DATABASE() fails only when the initial view creation features a NULL -# default database. -# -# CREATE, USE and DROP database so that we have no "default" database. -# -CREATE DATABASE temporary; -USE temporary; -DROP DATABASE temporary; -SELECT DATABASE(); -DATABASE() -NULL -CREATE VIEW test.v_no_db AS SELECT DATABASE() = 'temporary_two'; -SHOW CREATE VIEW test.v_no_db; -View Create View character_set_client collation_connection -v_no_db CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `test`.`v_no_db` AS select database() = 'temporary_two' AS `DATABASE() = 'temporary_two'` latin1 latin1_swedish_ci -PREPARE prepared_no_database FROM "SELECT DATABASE() = 'temporary_two'"; -# -# All statements should return NULL -# -EXECUTE prepared_no_database; -DATABASE() = 'temporary_two' -NULL -SELECT DATABASE() = 'temporary_two'; -DATABASE() = 'temporary_two' -NULL -SELECT * FROM test.v_no_db; -DATABASE() = 'temporary_two' -NULL -CREATE DATABASE temporary_two; -USE temporary_two; -CREATE VIEW test.v_with_db AS SELECT DATABASE() = 'temporary_two'; -PREPARE prepared_with_database FROM "SELECT DATABASE() = 'temporary_two'"; -# -# All statements should return 1; -# -SELECT DATABASE() = 'temporary_two'; -DATABASE() = 'temporary_two' -1 -SELECT * FROM test.v_no_db; -DATABASE() = 'temporary_two' -1 -SELECT * FROM test.v_with_db; -DATABASE() = 'temporary_two' -1 -EXECUTE prepared_with_database; -DATABASE() = 'temporary_two' -1 -# -# Prepared statements maintain default database to be the same -# during on creation so this should return NULL still. -# See MySQL bug #25843 -# -EXECUTE prepared_no_database; -DATABASE() = 'temporary_two' -NULL -DROP DATABASE temporary_two; -DROP VIEW test.v_no_db; -DROP VIEW test.v_with_db; -USE test; -# ----------------------------------------------------------------- -# -- End of 10.0 tests. -# ----------------------------------------------------------------- -SET optimizer_switch=@save_optimizer_switch; -# -# Start of 10.1 tests -# -# -# MDEV-8747 Wrong result for SELECT..WHERE derived_table_column='a' AND derived_table_column<>_latin1'A' COLLATE latin1_bin -# -CREATE TABLE t1 (a varchar(10) character set cp1251 collate cp1251_ukrainian_ci, KEY (a)) ; -INSERT INTO t1 VALUES ('DD'), ('ZZ'), ('ZZ'), ('KK'), ('FF'), ('HH'),('MM'),('`1'); -CREATE VIEW v1 AS SELECT * FROM t1; -SELECT * FROM t1 WHERE a <> 0 AND a = ' 1'; -a -Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '`1' -SELECT * FROM v1 WHERE a <> 0 AND a = ' 1'; -a -Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '`1' -DROP VIEW v1; -DROP TABLE t1; -CREATE TABLE t1 (a ENUM('5','6')); -INSERT INTO t1 VALUES ('5'),('6'); -CREATE VIEW v1 AS SELECT * FROM t1; -SELECT * FROM t1 WHERE a='5' AND a<2; -a -5 -SELECT * FROM v1 WHERE a='5' AND a<2; -a -5 -DROP VIEW v1; -DROP TABLE t1; -# -# MDEV-8749 Wrong result for SELECT..WHERE derived_table_enum_column='number' AND derived_table_enum_column OP number2 -# -CREATE TABLE t1 (a varchar(10) character set cp1251 collate cp1251_ukrainian_ci, KEY (a)); -INSERT INTO t1 VALUES ('DD'), ('ZZ'), ('ZZ'), ('KK'), ('FF'), ('HH'),('MM'),('`1'); -CREATE VIEW v1 AS SELECT * FROM t1; -SELECT * FROM t1 WHERE a <> 0 AND a = ' 1'; -a -Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '`1' -SELECT * FROM v1 WHERE a <> 0 AND a = ' 1'; -a -Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '`1' -DROP VIEW v1; -DROP TABLE t1; -CREATE TABLE t1 (a ENUM('5','6')); -INSERT INTO t1 VALUES ('5'),('6'); -CREATE VIEW v1 AS SELECT * FROM t1; -SELECT * FROM t1 WHERE a='5' AND a<2; -a -5 -SELECT * FROM v1 WHERE a='5' AND a<2; -a -5 -DROP VIEW v1; -DROP TABLE t1; -# -# MDEV-8742 Wrong result for SELECT..WHERE view_latin1_swedish_ci_field='a' COLLATE latin1_bin -# -CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1); -INSERT INTO t1 VALUES ('a'),('A'); -CREATE VIEW v1 AS SELECT * FROM t1 WHERE a='a'; -SELECT * FROM v1 WHERE a=_latin1'a' COLLATE latin1_bin; -a -a -DROP VIEW v1; -DROP TABLE t1; -# -# MDEV-9701: CREATE VIEW with GROUP BY or ORDER BY and constant -# produces invalid definition -# -CREATE TABLE t1 ( i INT ); -INSERT INTO t1 VALUES (1),(2); -CREATE VIEW v1 AS -SELECT 3 AS three, COUNT(*) FROM t1 GROUP BY three; -show create view v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 3 AS `three`,count(0) AS `COUNT(*)` from `t1` group by '' latin1 latin1_swedish_ci -SELECT * FROM v1; -three COUNT(*) -3 2 -drop view v1; -drop table t1; -# -# MDEV-12819: order by ordering expression changed to empty string -# when creatin view with union -# -create table t1 (t1col1 int, t1col2 int,t1col3 int ); -create table t2 (t2col1 int, t2col2 int, t2col3 int); -create view v1 as -select t1col1,t1col2,t1col3 from t1 -union all -select t2col1,t2col2,t2col3 from t2 -order by 2,3; -show create view v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`t1col1` AS `t1col1`,`t1`.`t1col2` AS `t1col2`,`t1`.`t1col3` AS `t1col3` from `t1` union all select `t2`.`t2col1` AS `t2col1`,`t2`.`t2col2` AS `t2col2`,`t2`.`t2col3` AS `t2col3` from `t2` order by 2,3 latin1 latin1_swedish_ci -select * from v1; -t1col1 t1col2 t1col3 -drop view v1; -drop table t1,t2; -# -# End of 10.1 tests -# -# -# Start of 10.2 tests -# -# Checking that SHOW CREATE VIEW preserve parentheses -CREATE TABLE t1 (a INT); -INSERT INTO t1 VALUES (10),(20),(30); -CREATE VIEW v1 AS SELECT 1 AS a UNION SELECT a FROM t1; -SHOW CREATE VIEW v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `a` union select `t1`.`a` AS `a` from `t1` latin1 latin1_swedish_ci -SELECT * FROM v1; -a -1 -10 -20 -30 -DROP VIEW v1; -CREATE VIEW v1 AS SELECT 1 AS a UNION SELECT a FROM t1 LIMIT 1; -SHOW CREATE VIEW v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `a` union select `t1`.`a` AS `a` from `t1` limit 1 latin1 latin1_swedish_ci -SELECT * FROM v1; -a -1 -DROP VIEW v1; -CREATE VIEW v1 AS SELECT 1 AS a UNION (SELECT a FROM t1); -SHOW CREATE VIEW v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `a` union (select `t1`.`a` AS `a` from `t1`) latin1 latin1_swedish_ci -SELECT * FROM v1; -a -1 -10 -20 -30 -DROP VIEW v1; -CREATE VIEW v1 AS SELECT 1 AS a UNION (SELECT a FROM t1 LIMIT 1); -SHOW CREATE VIEW v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `a` union (select `t1`.`a` AS `a` from `t1` limit 1) latin1 latin1_swedish_ci -SELECT * FROM v1; -a -1 -10 -DROP VIEW v1; -CREATE VIEW v1 AS SELECT 1 AS a UNION (SELECT a FROM t1) LIMIT 1; -SHOW CREATE VIEW v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `a` union (select `t1`.`a` AS `a` from `t1`) limit 1 latin1 latin1_swedish_ci -SELECT * FROM v1; -a -1 -DROP VIEW v1; -DROP TABLE t1; -# -# MDEV-9408 CREATE TABLE SELECT MAX(int_column) creates different columns for table vs view -# -CREATE TABLE t1 ( -id int(11) NOT NULL PRIMARY KEY, -country varchar(32), -code int(11) default NULL -); -INSERT INTO t1 VALUES (1,'ITALY',100),(2,'ITALY',200),(3,'FRANCE',100), (4,'ITALY',100); -CREATE VIEW v1 AS SELECT * FROM t1; -CREATE TABLE t2 AS -SELECT code, COUNT(DISTINCT country), MAX(id) FROM t1 GROUP BY code ORDER BY MAX(id); -SHOW CREATE TABLE t2; -Table Create Table -t2 CREATE TABLE `t2` ( - `code` int(11) DEFAULT NULL, - `COUNT(DISTINCT country)` bigint(21) NOT NULL, - `MAX(id)` int(11) -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -CREATE TABLE t3 AS -SELECT code, COUNT(DISTINCT country), MAX(id) FROM v1 GROUP BY code ORDER BY MAX(id); -SHOW CREATE TABLE t3; -Table Create Table -t3 CREATE TABLE `t3` ( - `code` int(11) DEFAULT NULL, - `COUNT(DISTINCT country)` bigint(21) NOT NULL, - `MAX(id)` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -DROP VIEW v1; -DROP TABLE t1,t2,t3; -# -# MDEV-3944: Allow derived tables in VIEWS -# -create table t1 (s1 int); -insert into t1 values (1),(2),(3); -CREATE VIEW v1 AS SELECT * FROM (SELECT s1 FROM t1 WHERE s1>1) AS x; -CREATE VIEW v2 AS SELECT * FROM (SELECT s1 FROM t1 WHERE s1<3) AS x; -select * from v1; -s1 -2 -3 -select * from v2; -s1 -1 -2 -select * from v1 natural join v2; -s1 -2 -select * from v1 natural join t1; -s1 -2 -3 -select * from v1 natural join (SELECT s1 FROM t1 WHERE s1<3) as x; -s1 -2 -select * from v1 left join v2 on (v1.s1=v2.s1); -s1 s1 -2 2 -3 NULL -select * from v1 left join t1 on (v1.s1=t1.s1); -s1 s1 -2 2 -3 3 -select * from t1 left join v2 on (t1.s1=v2.s1); -s1 s1 -1 1 -2 2 -3 NULL -select * from v1 left join (SELECT s1 FROM t1 WHERE s1<3) as x on (v1.s1=x.s1); -s1 s1 -2 2 -3 NULL -select * from (SELECT s1 FROM t1 WHERE s1>1) AS x left join v2 on (x.s1=v2.s1); -s1 s1 -2 2 -3 NULL -drop view v1,v2; -CREATE VIEW v1 AS SELECT * FROM (SELECT s1 FROM (SELECT s1 FROM t1 WHERE s1 -< 100) as xx WHERE s1>1) AS x; -CREATE VIEW v2 AS SELECT * FROM (SELECT s1 FROM (SELECT s1 FROM t1 WHERE s1 -> -100) as xx WHERE s1<3) AS x; -insert into t1 values (200),(-200); -select * from t1; -s1 --200 -1 -2 -200 -3 -select * from v1; -s1 -2 -3 -select * from v2; -s1 -1 -2 -select * from v1 natural join v2; -s1 -2 -select * from v1 natural join t1; -s1 -2 -3 -select * from v1 natural join (SELECT s1 FROM t1 WHERE s1<3) as x; -s1 -2 -select * from v1 left join v2 on (v1.s1=v2.s1); -s1 s1 -2 2 -3 NULL -select * from v1 left join t1 on (v1.s1=t1.s1); -s1 s1 -2 2 -3 3 -select * from t1 left join v2 on (t1.s1=v2.s1); -s1 s1 --200 NULL -1 1 -2 2 -200 NULL -3 NULL -select * from v1 left join (SELECT s1 FROM t1 WHERE s1<3) as x on (v1.s1=x.s1); -s1 s1 -2 2 -3 NULL -select * from (SELECT s1 FROM t1 WHERE s1>1) AS x left join v2 on (x.s1=v2.s1); -s1 s1 -2 2 -200 NULL -3 NULL -drop view v1,v2; -CREATE algorithm=temptable VIEW v1 AS SELECT * FROM (SELECT s1 FROM (SELECT s1 FROM t1 WHERE s1 -< 100) as xx WHERE s1>1) AS x; -CREATE algorithm=temptable VIEW v2 AS SELECT * FROM (SELECT s1 FROM (SELECT s1 FROM t1 WHERE s1 -> -100) as xx WHERE s1<3) AS x; -select * from t1; -s1 --200 -1 -2 -200 -3 -select * from v1; -s1 -2 -3 -select * from v2; -s1 -1 -2 -select * from v1 natural join v2; -s1 -2 -select * from v1 natural join t1; -s1 -2 -3 -select * from v1 natural join (SELECT s1 FROM t1 WHERE s1<3) as x; -s1 -2 -select * from v1 left join v2 on (v1.s1=v2.s1); -s1 s1 -2 2 -3 NULL -select * from v1 left join t1 on (v1.s1=t1.s1); -s1 s1 -2 2 -3 3 -select * from t1 left join v2 on (t1.s1=v2.s1); -s1 s1 --200 NULL -1 1 -2 2 -200 NULL -3 NULL -select * from v1 left join (SELECT s1 FROM t1 WHERE s1<3) as x on (v1.s1=x.s1); -s1 s1 -2 2 -3 NULL -select * from (SELECT s1 FROM t1 WHERE s1>1) AS x left join v2 on (x.s1=v2.s1); -s1 s1 -2 2 -200 NULL -3 NULL -drop view v1,v2; -CREATE VIEW v1 AS SELECT * FROM (SELECT s1 FROM (SELECT s1 FROM t1 WHERE s1 -< 100) as xx WHERE s1>1) AS x; -insert into v1 values (-300); -ERROR HY000: The target table v1 of the INSERT is not insertable-into -update v1 set s1=s1+1; -ERROR HY000: The target table v1 of the UPDATE is not updatable -drop view v1; -CREATE VIEW v1 AS SELECT s1,s2 FROM (SELECT s1 as s2 FROM t1 WHERE s1 < -100) x, t1 WHERE t1.s1=x.s2; -select * from v1; -s1 s2 -1 1 -2 2 -3 3 --200 -200 -insert into v1 (s1) values (-300); -update v1 set s1=s1+1; -select * from v1; -s1 s2 -2 2 -3 3 -4 4 --199 -199 --299 -299 -select * from t1; -s1 -2 -3 -4 -200 --199 --299 -insert into v1(s2) values (-300); -ERROR HY000: The target table v1 of the INSERT is not insertable-into -update v1 set s2=s2+1; -ERROR HY000: The target table v1 of the UPDATE is not updatable -drop view v1; -CREATE VIEW v1 AS SELECT * FROM (SELECT s1 FROM t1 WHERE s1 -< 100) AS x; -insert into v1 values (-300); -ERROR HY000: The target table v1 of the INSERT is not insertable-into -update v1 set s1=s1+1; -ERROR HY000: The target table v1 of the UPDATE is not updatable -drop view v1; -CREATE VIEW v1 AS SELECT * FROM (SELECT s1 FROM (SELECT s1 FROM t1 WHERE s1 -< 100) as xx WHERE s1>1) AS x; -insert into v1 values (-300); -ERROR HY000: The target table v1 of the INSERT is not insertable-into -update v1 set s1=s1+1; -ERROR HY000: The target table v1 of the UPDATE is not updatable -create view v2 as select * from v1; -insert into v2 values (-300); -ERROR HY000: The target table v2 of the INSERT is not insertable-into -update v2 set s1=s1+1; -ERROR HY000: The target table v2 of the UPDATE is not updatable -drop view v1, v2; -drop table t1; -# -# MDEV-9671:Wrong result upon select from a view with a FROM subquery -# -CREATE TABLE t1 (i INT); -INSERT INTO t1 VALUES (3),(2); -CREATE TABLE t2 (j INT); -INSERT INTO t2 VALUES (8),(3),(3); -CREATE TABLE t3 (k INT); -INSERT INTO t3 VALUES (1),(8); -CREATE VIEW v1 AS SELECT * FROM t1 LEFT JOIN ( SELECT t2.* FROM t2 INNER JOIN t3 ON ( k = j ) ) AS alias1 ON ( i = j ); -show create view v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`t1`.`i` AS `i`,`alias1`.`j` AS `j` from (`test`.`t1` left join (select `test`.`t2`.`j` AS `j` from (`test`.`t2` join `test`.`t3` on(`test`.`t3`.`k` = `test`.`t2`.`j`))) `alias1` on(`test`.`t1`.`i` = `alias1`.`j`)) latin1 latin1_swedish_ci -SELECT * FROM t1 LEFT JOIN ( SELECT t2.* FROM t2 INNER JOIN t3 ON ( k = j ) ) AS alias1 ON ( i = j ); -i j -3 NULL -2 NULL -SELECT * FROM v1; -i j -3 NULL -2 NULL -DROP VIEW v1; -DROP TABLE t1, t2, t3; -# -# MDEV-10035: DBUG_ASSERT on CREATE VIEW v1 AS SELECT * FROM t1 -# FOR UPDATE -# -CREATE TABLE t1 (a INT); -insert into t1 values (1),(2); -CREATE VIEW v1 AS SELECT * FROM t1 FOR UPDATE; -SHOW CREATE VIEW v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` for update latin1 latin1_swedish_ci -select * from v1; -a -1 -2 -DROP VIEW v1; -CREATE VIEW v1 AS SELECT * FROM t1 LOCK IN SHARE MODE; -SHOW CREATE VIEW v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` lock in share mode latin1 latin1_swedish_ci -select * from v1; -a -1 -2 -DROP VIEW v1; -DROP TABLE t1; -# -# MDEV-10724:Assertion `vcol_table == 0 || vcol_table == table' -# failed in fill_record(THD*, TABLE*, List<Item>&, List<Item>&, -# bool, bool) -# -CREATE TABLE t1 (f1 INT); -CREATE TABLE t2 (f2 INT); -CREATE TABLE t3 (f3 INT); -CREATE ALGORITHM = MERGE VIEW v AS SELECT f1, f3 FROM t1, -( SELECT f3 FROM t2, t3 ) AS sq; -INSERT INTO v (f1, f3) VALUES (1,1), (2,2); -ERROR HY000: Can not modify more than one base table through a join view 'test.v' -drop view v; -drop tables t1,t2,t3; -create table t1 (i int, j int); -insert t1 values (1,1),(2,2); -create view v1 as select (2, 3) not in (select i, j from t1); -select * from v1; -(2, 3) not in (select i, j from t1) -1 -show create view v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select !((2,3) in (select `t1`.`i`,`t1`.`j` from `t1`)) AS `(2, 3) not in (select i, j from t1)` latin1 latin1_swedish_ci -drop view v1; -drop table t1; -# -# MDEV-10704: Assertion `field->field->table == table_arg' -# failed in fill_record(THD*, TABLE*, List<Item>&, List<Item>&, -# bool, bool) -# -CREATE TABLE t1 (i INT); -CREATE TABLE t2 (j INT); -CREATE TABLE t3 (k INT); -CREATE ALGORITHM = MERGE VIEW v AS SELECT j AS f1, k AS f2 FROM ( SELECT j FROM t1, t2 ) sq, t3; -REPLACE INTO v (f1,f2) VALUES (1,1); -ERROR HY000: Can not modify more than one base table through a join view 'test.v' -drop view v; -drop table t1,t2,t3; -# -# MDEV-12379: Server crashes in TABLE_LIST::is_with_table on -# SHOW CREATE VIEW -# -CREATE TABLE t (i INT); -CREATE VIEW v AS SELECT * FROM ( SELECT * FROM t ) sq; -DROP TABLE IF EXISTS t; -SHOW CREATE VIEW v; -View Create View character_set_client collation_connection -v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `sq`.`i` AS `i` from (select `test`.`t`.`i` AS `i` from `test`.`t`) `sq` latin1 latin1_swedish_ci -Warnings: -Warning 1356 View 'test.v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -DROP VIEW v; -# -# MDEV-13439: Database permissions are not enough to run a subquery -# with GROUP BY within a view -# -create database test_db; -use test_db; -create table t (i int); -create user foo@localhost; -grant all on test_db.* to foo@localhost; -connect con1,localhost,foo,,; -use test_db; -create view v as select * from (select i from t group by i) sq; -select * from v; -i -disconnect con1; -connection default; -use test; -drop database test_db; -drop user foo@localhost; -# -# MDEV-13523: Group By in a View, called within a Stored Routine -# causes Error Code 1356 when a non-root user runs the routine for -# a second time -# -CREATE DATABASE bugTest; -USE bugTest; -CREATE TABLE `procViewTable` (`id` int(10), `someText` varchar(50) NOT NULL); -insert into `procViewTable` values (1,'Test'), (2,'Test 2'); -CREATE USER 'procView'@'%'; -GRANT ALL PRIVILEGES ON `bugTest`.* TO 'procView'@'%'; -CREATE DEFINER=`procView`@`%` VIEW `procViewSimple` AS ( -select * from ( -select `id` from `bugTest`.`procViewTable` - ) `innerQuery` - group by `innerQuery`.`id` -); -connect con1,localhost,procView,,; -use bugTest; -prepare stmt from "SELECT * FROM procViewSimple"; -execute stmt; -id -1 -2 -execute stmt; -id -1 -2 -disconnect con1; -connection default; -drop user procView; -drop view procViewSimple; -drop table procViewTable; -use test; -drop database bugTest; -# -# MDEV-13436: PREPARE doesn't work as expected & throws errors but -# MySQL is working fine -# -create table t1 (a int); -insert into t1 values (1),(2); -SET @sql_query = " - CREATE VIEW v1 AS - SELECT * FROM ( - SELECT CASE WHEN 1 IN (SELECT a from t1 where a < 2) THEN TRUE END AS testcase - ) testalias -"; -PREPARE stmt FROM @sql_query; -EXECUTE stmt; -DEALLOCATE PREPARE stmt; -show create view v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `testalias`.`testcase` AS `testcase` from (select case when 1 in (select `test`.`t1`.`a` from `test`.`t1` where `test`.`t1`.`a` < 2) then 1 end AS `testcase`) `testalias` latin1 latin1_swedish_ci -SELECT * FROM v1; -testcase -1 -drop view v1; -drop table t1; -# -# End of 10.2 tests -# -# -# Start of 10.3 tests -# -# -# MDEV-13197 Parser refactoring for CREATE VIEW,TRIGGER,SP,UDF,EVENT -# -ALTER VIEW IF NOT EXISTS v1 AS SELECT 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'IF NOT EXISTS v1 AS SELECT 1' at line 1 -# -# End of 10.3 tests -# |