diff options
Diffstat (limited to 'mysql-test/main/view.result')
| -rw-r--r-- | mysql-test/main/view.result | 6512 |
1 files changed, 6512 insertions, 0 deletions
diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result new file mode 100644 index 00000000000..e61e2d2663d --- /dev/null +++ b/mysql-test/main/view.result @@ -0,0 +1,6512 @@ +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 +# |