1drop table if exists t1,t2,t3,t4,t9,`t1a``b`,v1,v2,v3,v4,v5,v6; 2drop view if exists t1,t2,`t1a``b`,v1,v2,v3,v4,v5,v6; 3drop database if exists mysqltest; 4use test; 5create view v1 (c,d) as select a,b from t1; 6ERROR 42S02: Table 'test.t1' doesn't exist 7create temporary table t1 (a int, b int); 8create view v1 (c) as select b+1 from t1; 9ERROR HY000: View's SELECT refers to a temporary table 't1' 10drop table t1; 11create table t1 (a int, b int); 12insert into t1 values (1,2), (1,3), (2,4), (2,5), (3,10); 13create view v1 (c,d) as select a,b+@@global.max_user_connections from t1; 14ERROR HY000: View's SELECT contains a variable or parameter 15create view v1 (c,d) as select a,b from t1 16where a = @@global.max_user_connections; 17ERROR HY000: View's SELECT contains a variable or parameter 18create view v1 (c) as select b+1 from t1; 19select c from v1; 20c 213 224 235 246 2511 26select is_updatable from information_schema.views where table_name='v1'; 27is_updatable 28NO 29create temporary table t1 (a int, b int); 30select * from t1; 31a b 32select c from v1; 33c 343 354 365 376 3811 39show create table v1; 40View Create View character_set_client collation_connection 41v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select (`t1`.`b` + 1) AS `c` from `t1` latin1 latin1_swedish_ci 42show create view v1; 43View Create View character_set_client collation_connection 44v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select (`t1`.`b` + 1) AS `c` from `t1` latin1 latin1_swedish_ci 45show create view t1; 46ERROR HY000: 'test.t1' is not VIEW 47drop table t1; 48select a from v1; 49ERROR 42S22: Unknown column 'a' in 'field list' 50select v1.a from v1; 51ERROR 42S22: Unknown column 'v1.a' in 'field list' 52select b from v1; 53ERROR 42S22: Unknown column 'b' in 'field list' 54select v1.b from v1; 55ERROR 42S22: Unknown column 'v1.b' in 'field list' 56explain extended select c from v1; 57id select_type table type possible_keys key key_len ref rows filtered Extra 581 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 NULL 59Warnings: 60Note 1003 /* select#1 */ select (`test`.`t1`.`b` + 1) AS `c` from `test`.`t1` 61create algorithm=temptable view v2 (c) as select b+1 from t1; 62show create view v2; 63View Create View character_set_client collation_connection 64v2 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select (`t1`.`b` + 1) AS `c` from `t1` latin1 latin1_swedish_ci 65select c from v2; 66c 673 684 695 706 7111 72explain extended select c from v2; 73id select_type table type possible_keys key key_len ref rows filtered Extra 741 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 100.00 NULL 752 DERIVED t1 ALL NULL NULL NULL NULL 5 100.00 NULL 76Warnings: 77Note 1003 /* select#1 */ select `v2`.`c` AS `c` from `test`.`v2` 78create view v3 (c) as select a+1 from v1; 79ERROR 42S22: Unknown column 'a' in 'field list' 80create view v3 (c) as select b+1 from v1; 81ERROR 42S22: Unknown column 'b' in 'field list' 82create view v3 (c) as select c+1 from v1; 83select c from v3; 84c 854 865 876 887 8912 90explain extended select c from v3; 91id select_type table type possible_keys key key_len ref rows filtered Extra 921 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 NULL 93Warnings: 94Note 1003 /* select#1 */ select ((`test`.`t1`.`b` + 1) + 1) AS `c` from `test`.`t1` 95create algorithm=temptable view v4 (c) as select c+1 from v2; 96select c from v4; 97c 984 995 1006 1017 10212 103explain extended select c from v4; 104id select_type table type possible_keys key key_len ref rows filtered Extra 1051 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 100.00 NULL 1062 DERIVED <derived3> ALL NULL NULL NULL NULL 5 100.00 NULL 1073 DERIVED t1 ALL NULL NULL NULL NULL 5 100.00 NULL 108Warnings: 109Note 1003 /* select#1 */ select `v4`.`c` AS `c` from `test`.`v4` 110create view v5 (c) as select c+1 from v2; 111select c from v5; 112c 1134 1145 1156 1167 11712 118explain extended select c from v5; 119id select_type table type possible_keys key key_len ref rows filtered Extra 1201 PRIMARY <derived3> ALL NULL NULL NULL NULL 5 100.00 NULL 1213 DERIVED t1 ALL NULL NULL NULL NULL 5 100.00 NULL 122Warnings: 123Note 1003 /* select#1 */ select (`v2`.`c` + 1) AS `c` from `test`.`v2` 124create algorithm=temptable view v6 (c) as select c+1 from v1; 125select c from v6; 126c 1274 1285 1296 1307 13112 132explain extended select c from v6; 133id select_type table type possible_keys key key_len ref rows filtered Extra 1341 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 100.00 NULL 1352 DERIVED t1 ALL NULL NULL NULL NULL 5 100.00 NULL 136Warnings: 137Note 1003 /* select#1 */ select `v6`.`c` AS `c` from `test`.`v6` 138show tables; 139Tables_in_test 140t1 141v1 142v2 143v3 144v4 145v5 146v6 147show full tables; 148Tables_in_test Table_type 149t1 BASE TABLE 150v1 VIEW 151v2 VIEW 152v3 VIEW 153v4 VIEW 154v5 VIEW 155v6 VIEW 156show table status; 157Name 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 158t1 MyISAM 10 Fixed 5 9 45 # 1024 0 NULL # # NULL latin1_swedish_ci NULL 159v1 NULL NULL NULL NULL NULL NULL # NULL NULL NULL # # NULL NULL NULL NULL VIEW 160v2 NULL NULL NULL NULL NULL NULL # NULL NULL NULL # # NULL NULL NULL NULL VIEW 161v3 NULL NULL NULL NULL NULL NULL # NULL NULL NULL # # NULL NULL NULL NULL VIEW 162v4 NULL NULL NULL NULL NULL NULL # NULL NULL NULL # # NULL NULL NULL NULL VIEW 163v5 NULL NULL NULL NULL NULL NULL # NULL NULL NULL # # NULL NULL NULL NULL VIEW 164v6 NULL NULL NULL NULL NULL NULL # NULL NULL NULL # # NULL NULL NULL NULL VIEW 165drop view v1,v2,v3,v4,v5,v6; 166create view v1 (c,d,e,f) as select a,b, 167a in (select a+2 from t1), a = all (select a from t1) from t1; 168create view v2 as select c, d from v1; 169select * from v1; 170c d e f 1711 2 0 0 1721 3 0 0 1732 4 0 0 1742 5 0 0 1753 10 1 0 176select * from v2; 177c d 1781 2 1791 3 1802 4 1812 5 1823 10 183create 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; 184ERROR 42S01: Table 'v1' already exists 185create 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; 186drop view v2; 187alter view v2 as select c, d from v1; 188ERROR 42S02: Table 'test.v2' doesn't exist 189create or replace view v2 as select c, d from v1; 190alter view v1 (c,d) as select a,max(b) from t1 group by a; 191select * from v1; 192c d 1931 3 1942 5 1953 10 196select * from v2; 197c d 1981 3 1992 5 2003 10 201drop view v100; 202ERROR 42S02: Unknown table 'test.v100' 203drop view t1; 204ERROR HY000: 'test.t1' is not VIEW 205drop table v1; 206ERROR 42S02: Unknown table 'test.v1' 207drop view v1,v2; 208drop table t1; 209create table t1 (a int); 210insert into t1 values (1), (2), (3); 211create view v1 (a) as select a+1 from t1; 212create view v2 (a) as select a-1 from t1; 213select * from t1 natural left join v1; 214a 2151 2162 2173 218select * from v2 natural left join t1; 219a 2200 2211 2222 223select * from v2 natural left join v1; 224a 2250 2261 2272 228drop view v1, v2; 229drop table t1; 230create table t1 (a int); 231insert into t1 values (1), (2), (3), (1), (2), (3); 232create view v1 as select distinct a from t1; 233select * from v1; 234a 2351 2362 2373 238explain select * from v1; 239id select_type table type possible_keys key key_len ref rows Extra 2401 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 NULL 2412 DERIVED t1 ALL NULL NULL NULL NULL 6 Using temporary 242select * from t1; 243a 2441 2452 2463 2471 2482 2493 250drop view v1; 251drop table t1; 252create table t1 (a int); 253create view v1 as select distinct a from t1 WITH CHECK OPTION; 254ERROR HY000: CHECK OPTION on non-updatable view 'test.v1' 255create view v1 as select a from t1 WITH CHECK OPTION; 256create view v2 as select a from t1 WITH CASCADED CHECK OPTION; 257create view v3 as select a from t1 WITH LOCAL CHECK OPTION; 258drop view v3 RESTRICT; 259drop view v2 CASCADE; 260drop view v1; 261drop table t1; 262create table t1 (a int, b int); 263insert into t1 values (1,2), (1,3), (2,4), (2,5), (3,10); 264create view v1 (c) as select b+1 from t1; 265select test.c from v1 test; 266c 2673 2684 2695 2706 27111 272create algorithm=temptable view v2 (c) as select b+1 from t1; 273select test.c from v2 test; 274c 2753 2764 2775 2786 27911 280select test1.* from v1 test1, v2 test2 where test1.c=test2.c; 281c 2823 2834 2845 2856 28611 287select test2.* from v1 test1, v2 test2 where test1.c=test2.c; 288c 2893 2904 2915 2926 29311 294drop table t1; 295drop view v1,v2; 296create table t1 (a int); 297insert into t1 values (1), (2), (3), (4); 298create view v1 as select a+1 from t1 order by 1 desc limit 2; 299select * from v1; 300a+1 3015 3024 303explain select * from v1; 304id select_type table type possible_keys key key_len ref rows Extra 3051 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 NULL 3062 DERIVED t1 ALL NULL NULL NULL NULL 4 Using filesort 307drop view v1; 308drop table t1; 309create table t1 (a int); 310insert into t1 values (1), (2), (3), (4); 311create view v1 as select a+1 from t1; 312create table t2 select * from v1; 313show columns from t2; 314Field Type Null Key Default Extra 315a+1 bigint(12) YES NULL 316select * from t2; 317a+1 3182 3193 3204 3215 322drop view v1; 323drop table t1,t2; 324create table t1 (a int, b int, primary key(a)); 325insert into t1 values (10,2), (20,3), (30,4), (40,5), (50,10); 326create view v1 (a,c) as select a, b+1 from t1; 327create algorithm=temptable view v2 (a,c) as select a, b+1 from t1; 328select is_updatable from information_schema.views where table_name='v2'; 329is_updatable 330NO 331select is_updatable from information_schema.views where table_name='v1'; 332is_updatable 333YES 334update v1 set c=a+c; 335ERROR HY000: Column 'c' is not updatable 336update v2 set a=a+c; 337ERROR HY000: The target table v2 of the UPDATE is not updatable 338update v1 set a=a+c; 339select * from v1; 340a c 34113 3 34224 4 34335 5 34446 6 34561 11 346select * from t1; 347a b 34813 2 34924 3 35035 4 35146 5 35261 10 353drop table t1; 354drop view v1,v2; 355create table t1 (a int, b int, primary key(a)); 356insert into t1 values (10,2), (20,3), (30,4), (40,5), (50,10); 357create table t2 (x int); 358insert into t2 values (10), (20); 359create view v1 (a,c) as select a, b+1 from t1; 360create algorithm=temptable view v2 (a,c) as select a, b+1 from t1; 361update t2,v1 set v1.c=v1.a+v1.c where t2.x=v1.a; 362ERROR HY000: Column 'c' is not updatable 363update t2,v2 set v2.a=v2.v2.a+c where t2.x=v2.a; 364ERROR HY000: The target table v2 of the UPDATE is not updatable 365update t2,v1 set v1.a=v1.a+v1.c where t2.x=v1.a; 366select * from v1; 367a c 36813 3 36924 4 37030 5 37140 6 37250 11 373select * from t1; 374a b 37513 2 37624 3 37730 4 37840 5 37950 10 380drop table t1,t2; 381drop view v1,v2; 382create table t1 (a int, b int, primary key(b)); 383insert into t1 values (1,20), (2,30), (3,40), (4,50), (5,100); 384create view v1 (c) as select b from t1 where a<3; 385select * from v1; 386c 38720 38830 389explain extended select * from v1; 390id select_type table type possible_keys key key_len ref rows filtered Extra 3911 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where 392Warnings: 393Note 1003 /* select#1 */ select `test`.`t1`.`b` AS `c` from `test`.`t1` where (`test`.`t1`.`a` < 3) 394update v1 set c=c+1; 395select * from t1; 396a b 3971 21 3982 31 3993 40 4004 50 4015 100 402create view v2 (c) as select b from t1 where a>=3; 403select * from v1, v2; 404c c 40521 40 40631 40 40721 50 40831 50 40921 100 41031 100 411drop view v1, v2; 412drop table t1; 413create table t1 (a int, b int, primary key(a)); 414insert into t1 values (1,2), (2,3), (3,4), (4,5), (5,10); 415create view v1 (a,c) as select a, b+1 from t1; 416create algorithm=temptable view v2 (a,c) as select a, b+1 from t1; 417delete from v2 where c < 4; 418ERROR HY000: The target table v2 of the DELETE is not updatable 419delete from v1 where c < 4; 420select * from v1; 421a c 4222 4 4233 5 4244 6 4255 11 426select * from t1; 427a b 4282 3 4293 4 4304 5 4315 10 432drop table t1; 433drop view v1,v2; 434create table t1 (a int, b int, primary key(a)); 435insert into t1 values (1,2), (2,3), (3,4), (4,5), (5,10); 436create table t2 (x int); 437insert into t2 values (1), (2), (3), (4); 438create view v1 (a,c) as select a, b+1 from t1; 439create algorithm=temptable view v2 (a,c) as select a, b+1 from t1; 440delete v2 from t2,v2 where t2.x=v2.a; 441ERROR HY000: The target table v2 of the DELETE is not updatable 442delete v1 from t2,v1 where t2.x=v1.a; 443select * from v1; 444a c 4455 11 446select * from t1; 447a b 4485 10 449drop table t1,t2; 450drop view v1,v2; 451create table t1 (a int, b int, c int, primary key(a,b)); 452insert into t1 values (10,2,-1), (20,3,-2), (30,4,-3), (40,5,-4), (50,10,-5); 453create view v1 (x,y) as select a, b from t1; 454create view v2 (x,y) as select a, c from t1; 455set updatable_views_with_limit=NO; 456update v1 set x=x+1; 457update v2 set x=x+1; 458update v1 set x=x+1 limit 1; 459update v2 set x=x+1 limit 1; 460ERROR HY000: The target table v2 of the UPDATE is not updatable 461set updatable_views_with_limit=YES; 462update v1 set x=x+1 limit 1; 463update v2 set x=x+1 limit 1; 464Warnings: 465Note 1355 View being updated does not have complete key of underlying table in it 466set updatable_views_with_limit=DEFAULT; 467show variables like "updatable_views_with_limit"; 468Variable_name Value 469updatable_views_with_limit YES 470select * from t1; 471a b c 47215 2 -1 47322 3 -2 47432 4 -3 47542 5 -4 47652 10 -5 477drop table t1; 478drop view v1,v2; 479create table t1 (a int, b int, c int, primary key(a,b)); 480insert into t1 values (10,2,-1), (20,3,-2); 481create view v1 (x,y,z) as select c, b, a from t1; 482create view v2 (x,y) as select b, a from t1; 483create view v3 (x,y,z) as select b, a, b from t1; 484create view v4 (x,y,z) as select c+1, b, a from t1; 485create algorithm=temptable view v5 (x,y,z) as select c, b, a from t1; 486insert into v3 values (-60,4,30); 487ERROR HY000: The target table v3 of the INSERT is not insertable-into 488insert into v4 values (-60,4,30); 489ERROR HY000: The target table v4 of the INSERT is not insertable-into 490insert into v5 values (-60,4,30); 491ERROR HY000: The target table v5 of the INSERT is not insertable-into 492insert into v1 values (-60,4,30); 493insert into v1 (z,y,x) values (50,6,-100); 494insert into v2 values (5,40); 495select * from t1; 496a b c 49710 2 -1 49820 3 -2 49930 4 -60 50050 6 -100 50140 5 NULL 502drop table t1; 503drop view v1,v2,v3,v4,v5; 504create table t1 (a int, b int, c int, primary key(a,b)); 505insert into t1 values (10,2,-1), (20,3,-2); 506create table t2 (a int, b int, c int, primary key(a,b)); 507insert into t2 values (30,4,-60); 508create view v1 (x,y,z) as select c, b, a from t1; 509create view v2 (x,y) as select b, a from t1; 510create view v3 (x,y,z) as select b, a, b from t1; 511create view v4 (x,y,z) as select c+1, b, a from t1; 512create algorithm=temptable view v5 (x,y,z) as select c, b, a from t1; 513insert into v3 select c, b, a from t2; 514ERROR HY000: The target table v3 of the INSERT is not insertable-into 515insert into v4 select c, b, a from t2; 516ERROR HY000: The target table v4 of the INSERT is not insertable-into 517insert into v5 select c, b, a from t2; 518ERROR HY000: The target table v5 of the INSERT is not insertable-into 519insert into v1 select c, b, a from t2; 520insert into v1 (z,y,x) select a+20,b+2,-100 from t2; 521insert into v2 select b+1, a+10 from t2; 522select * from t1; 523a b c 52410 2 -1 52520 3 -2 52630 4 -60 52750 6 -100 52840 5 NULL 529drop table t1, t2; 530drop view v1,v2,v3,v4,v5; 531create table t1 (a int, primary key(a)); 532insert into t1 values (1), (2), (3); 533create view v1 (x) as select a from t1 where a > 1; 534select t1.a, v1.x from t1 left join v1 on (t1.a= v1.x); 535a x 5361 NULL 5372 2 5383 3 539drop table t1; 540drop view v1; 541create table t1 (a int, primary key(a)); 542insert into t1 values (1), (2), (3), (200); 543create view v1 (x) as select a from t1 where a > 1; 544create view v2 (y) as select x from v1 where x < 100; 545select * from v2; 546y 5472 5483 549drop table t1; 550drop view v1,v2; 551create table t1 (a int, primary key(a)); 552insert into t1 values (1), (2), (3), (200); 553create ALGORITHM=TEMPTABLE view v1 (x) as select a from t1; 554create view v2 (y) as select x from v1; 555update v2 set y=10 where y=2; 556ERROR HY000: The target table v2 of the UPDATE is not updatable 557drop table t1; 558drop view v1,v2; 559create table t1 (a int not null auto_increment, b int not null, primary key(a), unique(b)); 560create view v1 (x) as select b from t1; 561insert into v1 values (1); 562select last_insert_id(); 563last_insert_id() 5640 565insert into t1 (b) values (2); 566select last_insert_id(); 567last_insert_id() 5682 569select * from t1; 570a b 5711 1 5722 2 573drop view v1; 574drop table t1; 575set sql_mode='ansi'; 576create table t1 ("a*b" int); 577create view v1 as select "a*b" from t1; 578show create view v1; 579View Create View character_set_client collation_connection 580v1 CREATE VIEW "v1" AS select "t1"."a*b" AS "a*b" from "t1" latin1 latin1_swedish_ci 581drop view v1; 582drop table t1; 583set sql_mode=default; 584create table t1 (t_column int); 585create view v1 as select 'a'; 586select * from v1, t1; 587a t_column 588drop view v1; 589drop table t1; 590create table `t1a``b` (col1 char(2)); 591create view v1 as select * from `t1a``b`; 592select * from v1; 593col1 594describe v1; 595Field Type Null Key Default Extra 596col1 char(2) YES NULL 597drop view v1; 598drop table `t1a``b`; 599create table t1 (col1 char(5),col2 char(5)); 600create view v1 as select * from t1; 601drop table t1; 602create table t1 (col1 char(5),newcol2 char(5)); 603insert into v1 values('a','aa'); 604ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 605drop table t1; 606select * from v1; 607ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 608drop view v1; 609create view v1 (a,a) as select 'a','a'; 610ERROR 42S21: Duplicate column name 'a' 611create table t1 (col1 int,col2 char(22)); 612insert into t1 values(5,'Hello, world of views'); 613create view v1 as select * from t1; 614create view v2 as select * from v1; 615update v2 set col2='Hello, view world'; 616select is_updatable from information_schema.views; 617is_updatable 618YES 619YES 620select * from t1; 621col1 col2 6225 Hello, view world 623drop view v2, v1; 624drop table t1; 625create table t1 (a int, b int); 626create view v1 as select a, sum(b) from t1 group by a; 627select b from v1 use index (some_index) where b=1; 628ERROR 42000: Key 'some_index' doesn't exist in table 'v1' 629drop view v1; 630drop table t1; 631create table t1 (col1 char(5),col2 char(5)); 632create view v1 (col1,col2) as select col1,col2 from t1; 633insert into v1 values('s1','p1'),('s1','p2'),('s1','p3'),('s1','p4'),('s2','p1'),('s3','p2'),('s4','p4'); 634select distinct first.col2 from t1 first where first.col2 in (select second.col2 from t1 second where second.col1<>first.col1); 635col2 636p1 637p2 638p4 639select distinct first.col2 from v1 first where first.col2 in (select second.col2 from t1 second where second.col1<>first.col1); 640col2 641p1 642p2 643p4 644drop view v1; 645drop table t1; 646create table t1 (a int); 647create view v1 as select a from t1; 648insert into t1 values (1); 649SET @v0 = '2'; 650PREPARE stmt FROM 'UPDATE v1 SET a = ?'; 651EXECUTE stmt USING @v0; 652DEALLOCATE PREPARE stmt; 653SET @v0 = '3'; 654PREPARE stmt FROM 'insert into v1 values (?)'; 655EXECUTE stmt USING @v0; 656DEALLOCATE PREPARE stmt; 657SET @v0 = '4'; 658PREPARE stmt FROM 'insert into v1 (a) values (?)'; 659EXECUTE stmt USING @v0; 660DEALLOCATE PREPARE stmt; 661select * from t1; 662a 6632 6643 6654 666drop view v1; 667drop table t1; 668CREATE VIEW v02 AS SELECT * FROM DUAL; 669ERROR HY000: No tables used 670SHOW TABLES; 671Tables_in_test 672CREATE VIEW v1 AS SELECT EXISTS (SELECT 1 UNION SELECT 2); 673select * from v1; 674EXISTS (SELECT 1 UNION SELECT 2) 6751 676drop view v1; 677create table t1 (col1 int,col2 char(22)); 678create view v1 as select * from t1; 679create index i1 on v1 (col1); 680ERROR HY000: 'test.v1' is not BASE TABLE 681drop view v1; 682drop table t1; 683CREATE VIEW v1 (f1,f2,f3,f4) AS SELECT connection_id(), pi(), current_user(), version(); 684SHOW CREATE VIEW v1; 685View Create View character_set_client collation_connection 686v1 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 687drop view v1; 688create table t1 (s1 int); 689create table t2 (s2 int); 690insert into t1 values (1), (2); 691insert into t2 values (2), (3); 692create view v1 as select * from t1,t2 union all select * from t1,t2; 693select * from v1; 694s1 s2 6951 2 6962 2 6971 3 6982 3 6991 2 7002 2 7011 3 7022 3 703drop view v1; 704drop tables t1, t2; 705create table t1 (col1 int); 706insert into t1 values (1); 707create view v1 as select count(*) from t1; 708insert into t1 values (null); 709select * from v1; 710count(*) 7112 712drop view v1; 713drop table t1; 714create table t1 (a int); 715create table t2 (a int); 716create view v1 as select a from t1; 717create view v2 as select a from t2 where a in (select a from v1); 718show create view v2; 719View Create View character_set_client collation_connection 720v2 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 721drop view v2, v1; 722drop table t1, t2; 723CREATE VIEW `v 1` AS select 5 AS `5`; 724show create view `v 1`; 725View Create View character_set_client collation_connection 726v 1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v 1` AS select 5 AS `5` latin1 latin1_swedish_ci 727drop view `v 1`; 728create database mysqltest; 729create table mysqltest.t1 (a int, b int); 730create view mysqltest.v1 as select a from mysqltest.t1; 731alter view mysqltest.v1 as select b from mysqltest.t1; 732alter view mysqltest.v1 as select a from mysqltest.t1; 733drop database mysqltest; 734CREATE TABLE t1 (c1 int not null auto_increment primary key, c2 varchar(20), fulltext(c2)); 735insert into t1 (c2) VALUES ('real Beer'),('Water'),('Kossu'),('Coca-Cola'),('Vodka'),('Wine'),('almost real Beer'); 736select * from t1 WHERE match (c2) against ('Beer'); 737c1 c2 7381 real Beer 7397 almost real Beer 740CREATE VIEW v1 AS SELECT * from t1 WHERE match (c2) against ('Beer'); 741select * from v1; 742c1 c2 7431 real Beer 7447 almost real Beer 745drop view v1; 746drop table t1; 747create table t1 (a int); 748insert into t1 values (1),(1),(2),(2),(3),(3); 749create view v1 as select a from t1; 750select distinct a from v1; 751a 7521 7532 7543 755select distinct a from v1 limit 2; 756a 7571 7582 759select distinct a from t1 limit 2; 760a 7611 7622 763prepare stmt1 from "select distinct a from v1 limit 2"; 764execute stmt1; 765a 7661 7672 768execute stmt1; 769a 7701 7712 772deallocate prepare stmt1; 773drop view v1; 774drop table t1; 775create table t1 (tg_column bigint); 776create view v1 as select count(tg_column) as vg_column from t1; 777select avg(vg_column) from v1; 778avg(vg_column) 7790.0000 780drop view v1; 781drop table t1; 782create table t1 (col1 bigint not null, primary key (col1)); 783create table t2 (col1 bigint not null, key (col1)); 784create view v1 as select * from t1; 785create view v2 as select * from t2; 786insert into v1 values (1); 787insert into v2 values (1); 788create view v3 (a,b) as select v1.col1 as a, v2.col1 as b from v1, v2 where v1.col1 = v2.col1; 789select * from v3; 790a b 7911 1 792show create view v3; 793View Create View character_set_client collation_connection 794v3 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 795drop view v3, v2, v1; 796drop table t2, t1; 797create function `f``1` () returns int return 5; 798create view v1 as select test.`f``1` (); 799show create view v1; 800View Create View character_set_client collation_connection 801v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`f``1`() AS `test.``f````1`` ()` latin1 latin1_swedish_ci 802select * from v1; 803test.`f``1` () 8045 805drop view v1; 806drop function `f``1`; 807create function a() returns int return 5; 808create view v1 as select a(); 809select * from v1; 810a() 8115 812drop view v1; 813drop function a; 814create table t2 (col1 char collate latin1_german2_ci); 815create view v2 as select col1 collate latin1_german1_ci from t2; 816show create view v2; 817View Create View character_set_client collation_connection 818v2 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 819show create view v2; 820View Create View character_set_client collation_connection 821v2 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 822drop view v2; 823drop table t2; 824create table t1 (a int); 825insert into t1 values (1), (2); 826create view v1 as select 5 from t1 order by 1; 827select * from v1; 8285 8295 8305 831drop view v1; 832drop table t1; 833create function x1 () returns int return 5; 834create table t1 (s1 int); 835create view v1 as select x1() from t1; 836drop function x1; 837select * from v1; 838ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 839show table status; 840Name 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 841t1 MyISAM 10 Fixed 0 0 0 # 1024 0 NULL # # NULL latin1_swedish_ci NULL 842v1 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 843Warnings: 844Warning 1356 View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 845drop view v1; 846drop table t1; 847create view v1 as select 99999999999999999999999999999999999999999999999999999 as col1; 848show create view v1; 849View Create View character_set_client collation_connection 850v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 99999999999999999999999999999999999999999999999999999 AS `col1` latin1 latin1_swedish_ci 851drop view v1; 852SET @old_cs_client = @@character_set_client; 853SET @old_cs_results = @@character_set_results; 854SET @old_cs_connection = @@character_set_connection; 855set names utf8; 856create table tü (cü char); 857create view vü as select cü from tü; 858insert into vü values ('ü'); 859select * from vü; 860cü 861ü 862drop view vü; 863drop table tü; 864SET character_set_client = @old_cs_client; 865SET character_set_results = @old_cs_results; 866SET character_set_connection = @old_cs_connection; 867create table t1 (a int, b int); 868insert into t1 values (1,2), (1,3), (2,4), (2,5), (3,10); 869create view v1(c) as select a+1 from t1 where b >= 4; 870select c from v1 where exists (select * from t1 where a=2 and b=c); 871c 8724 873drop view v1; 874drop table t1; 875create view v1 as select cast(1 as char(3)); 876show create view v1; 877View Create View character_set_client collation_connection 878v1 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 879select * from v1; 880cast(1 as char(3)) 8811 882drop view v1; 883create table t1 (a int); 884create view v1 as select a from t1; 885create view v3 as select a from t1; 886create database mysqltest; 887rename table v1 to mysqltest.v1; 888ERROR HY000: Changing schema from 'test' to 'mysqltest' is not allowed. 889rename table v1 to v2; 890rename table v3 to v1, v2 to t1; 891ERROR 42S01: Table 't1' already exists 892drop table t1; 893drop view v2,v3; 894drop database mysqltest; 895create view v1 as select 'a',1; 896create view v2 as select * from v1 union all select * from v1; 897create view v3 as select * from v2 where 1 = (select `1` from v2); 898create view v4 as select * from v3; 899select * from v4; 900ERROR 21000: Subquery returns more than 1 row 901drop view v4, v3, v2, v1; 902create view v1 as select 5 into @w; 903ERROR HY000: View's SELECT contains a 'INTO' clause 904create view v1 as select 5 into outfile 'ttt'; 905ERROR HY000: View's SELECT contains a 'INTO' clause 906create table t1 (a int); 907create view v1 as select a from t1 procedure analyse(); 908ERROR HY000: View's SELECT contains a 'PROCEDURE' clause 909create view v1 as select 1 from (select 1) as d1; 910ERROR HY000: View's SELECT contains a subquery in the FROM clause 911drop table t1; 912create table t1 (s1 int, primary key (s1)); 913create view v1 as select * from t1; 914insert into v1 values (1) on duplicate key update s1 = 7; 915insert into v1 values (1) on duplicate key update s1 = 7; 916select * from t1; 917s1 9187 919drop view v1; 920drop table t1; 921create table t1 (col1 int); 922create table t2 (col1 int); 923create table t3 (col1 datetime not null); 924create view v1 as select * from t1; 925create view v2 as select * from v1; 926create view v3 as select v2.col1 from v2,t2 where v2.col1 = t2.col1; 927update v2 set col1 = (select max(col1) from v1); 928ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'v2'. 929update v2 set col1 = (select max(col1) from t1); 930ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 'v2'. 931update v2 set col1 = (select max(col1) from v2); 932ERROR HY000: You can't specify target table 'v2' for update in FROM clause 933update v2,t2 set v2.col1 = (select max(col1) from v1) where v2.col1 = t2.col1; 934ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'v2'. 935update t1,t2 set t1.col1 = (select max(col1) from v1) where t1.col1 = t2.col1; 936ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 't1'. 937update v1,t2 set v1.col1 = (select max(col1) from v1) where v1.col1 = t2.col1; 938ERROR HY000: You can't specify target table 'v1' for update in FROM clause 939update t2,v2 set v2.col1 = (select max(col1) from v1) where v2.col1 = t2.col1; 940ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 't2'. 941update t2,t1 set t1.col1 = (select max(col1) from v1) where t1.col1 = t2.col1; 942ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 't2'. 943update t2,v1 set v1.col1 = (select max(col1) from v1) where v1.col1 = t2.col1; 944ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 't2'. 945update v2,t2 set v2.col1 = (select max(col1) from t1) where v2.col1 = t2.col1; 946ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 'v2'. 947update t1,t2 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1; 948ERROR HY000: You can't specify target table 't1' for update in FROM clause 949update v1,t2 set v1.col1 = (select max(col1) from t1) where v1.col1 = t2.col1; 950ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'v1'. 951update t2,v2 set v2.col1 = (select max(col1) from t1) where v2.col1 = t2.col1; 952ERROR HY000: You can't specify target table 't2' for update in FROM clause 953update t2,t1 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1; 954ERROR HY000: You can't specify target table 't2' for update in FROM clause 955update t2,v1 set v1.col1 = (select max(col1) from t1) where v1.col1 = t2.col1; 956ERROR HY000: You can't specify target table 't2' for update in FROM clause 957update v2,t2 set v2.col1 = (select max(col1) from v2) where v2.col1 = t2.col1; 958ERROR HY000: You can't specify target table 'v2' for update in FROM clause 959update t1,t2 set t1.col1 = (select max(col1) from v2) where t1.col1 = t2.col1; 960ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 't1'. 961update v1,t2 set v1.col1 = (select max(col1) from v2) where v1.col1 = t2.col1; 962ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 'v1'. 963update t2,v2 set v2.col1 = (select max(col1) from v2) where v2.col1 = t2.col1; 964ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 't2'. 965update t2,t1 set t1.col1 = (select max(col1) from v2) where t1.col1 = t2.col1; 966ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 't2'. 967update t2,v1 set v1.col1 = (select max(col1) from v2) where v1.col1 = t2.col1; 968ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 't2'. 969update v3 set v3.col1 = (select max(col1) from v1); 970ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'v3'. 971update v3 set v3.col1 = (select max(col1) from t1); 972ERROR HY000: The definition of table 'v3' prevents operation UPDATE on table 'v3'. 973update v3 set v3.col1 = (select max(col1) from v2); 974ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 'v3'. 975update v3 set v3.col1 = (select max(col1) from v3); 976ERROR HY000: You can't specify target table 'v3' for update in FROM clause 977delete from v2 where col1 = (select max(col1) from v1); 978ERROR HY000: The definition of table 'v1' prevents operation DELETE on table 'v2'. 979delete from v2 where col1 = (select max(col1) from t1); 980ERROR HY000: The definition of table 'v2' prevents operation DELETE on table 'v2'. 981delete from v2 where col1 = (select max(col1) from v2); 982ERROR HY000: You can't specify target table 'v2' for update in FROM clause 983delete v2 from v2,t2 where (select max(col1) from v1) > 0 and v2.col1 = t2.col1; 984ERROR HY000: The definition of table 'v1' prevents operation DELETE on table 'v2'. 985delete t1 from t1,t2 where (select max(col1) from v1) > 0 and t1.col1 = t2.col1; 986ERROR HY000: The definition of table 'v1' prevents operation DELETE on table 't1'. 987delete v1 from v1,t2 where (select max(col1) from v1) > 0 and v1.col1 = t2.col1; 988ERROR HY000: You can't specify target table 'v1' for update in FROM clause 989delete v2 from v2,t2 where (select max(col1) from t1) > 0 and v2.col1 = t2.col1; 990ERROR HY000: The definition of table 'v2' prevents operation DELETE on table 'v2'. 991delete t1 from t1,t2 where (select max(col1) from t1) > 0 and t1.col1 = t2.col1; 992ERROR HY000: You can't specify target table 't1' for update in FROM clause 993delete v1 from v1,t2 where (select max(col1) from t1) > 0 and v1.col1 = t2.col1; 994ERROR HY000: The definition of table 'v1' prevents operation DELETE on table 'v1'. 995delete v2 from v2,t2 where (select max(col1) from v2) > 0 and v2.col1 = t2.col1; 996ERROR HY000: You can't specify target table 'v2' for update in FROM clause 997delete t1 from t1,t2 where (select max(col1) from v2) > 0 and t1.col1 = t2.col1; 998ERROR HY000: The definition of table 'v2' prevents operation DELETE on table 't1'. 999delete v1 from v1,t2 where (select max(col1) from v2) > 0 and v1.col1 = t2.col1; 1000ERROR HY000: The definition of table 'v2' prevents operation DELETE on table 'v1'. 1001insert into v2 values ((select max(col1) from v1)); 1002ERROR HY000: The definition of table 'v1' prevents operation INSERT on table 'v2'. 1003insert into t1 values ((select max(col1) from v1)); 1004ERROR HY000: The definition of table 'v1' prevents operation INSERT on table 't1'. 1005insert into v2 values ((select max(col1) from v1)); 1006ERROR HY000: The definition of table 'v1' prevents operation INSERT on table 'v2'. 1007insert into v2 values ((select max(col1) from t1)); 1008ERROR HY000: The definition of table 'v2' prevents operation INSERT on table 'v2'. 1009insert into t1 values ((select max(col1) from t1)); 1010ERROR HY000: You can't specify target table 't1' for update in FROM clause 1011insert into v2 values ((select max(col1) from t1)); 1012ERROR HY000: The definition of table 'v2' prevents operation INSERT on table 'v2'. 1013insert into v2 values ((select max(col1) from v2)); 1014ERROR HY000: You can't specify target table 'v2' for update in FROM clause 1015insert into t1 values ((select max(col1) from v2)); 1016ERROR HY000: The definition of table 'v2' prevents operation INSERT on table 't1'. 1017insert into v2 values ((select max(col1) from v2)); 1018ERROR HY000: You can't specify target table 'v2' for update in FROM clause 1019insert into v3 (col1) values ((select max(col1) from v1)); 1020ERROR HY000: The definition of table 'v1' prevents operation INSERT on table 'v3'. 1021insert into v3 (col1) values ((select max(col1) from t1)); 1022ERROR HY000: The definition of table 'v3' prevents operation INSERT on table 'v3'. 1023insert into v3 (col1) values ((select max(col1) from v2)); 1024ERROR HY000: The definition of table 'v2' prevents operation INSERT on table 'v3'. 1025insert into v3 (col1) values ((select CONVERT_TZ('20050101000000','UTC','MET') from v2)); 1026ERROR HY000: The definition of table 'v2' prevents operation INSERT on table 'v3'. 1027insert into v3 (col1) values ((select CONVERT_TZ('20050101000000','UTC','MET') from t2)); 1028insert into t3 values ((select CONVERT_TZ('20050101000000','UTC','MET') from t2)); 1029ERROR 23000: Column 'col1' cannot be null 1030create algorithm=temptable view v4 as select * from t1; 1031insert into t1 values (1),(2),(3); 1032insert into t1 (col1) values ((select max(col1) from v4)); 1033select * from t1; 1034col1 1035NULL 10361 10372 10383 10393 1040drop view v4,v3,v2,v1; 1041drop table t1,t2,t3; 1042create table t1 (s1 int); 1043create view v1 as select * from t1; 1044handler v1 open as xx; 1045ERROR HY000: 'test.v1' is not BASE TABLE 1046drop view v1; 1047drop table t1; 1048create table t1(a int); 1049insert into t1 values (0), (1), (2), (3); 1050create table t2 (a int); 1051insert into t2 select a from t1 where a > 1; 1052create view v1 as select a from t1 where a > 1; 1053select * from t1 left join (t2 as t, v1) on v1.a=t1.a; 1054a a a 10550 NULL NULL 10561 NULL NULL 10572 2 2 10582 3 2 10593 2 3 10603 3 3 1061select * from t1 left join (t2 as t, t2) on t2.a=t1.a; 1062a a a 10630 NULL NULL 10641 NULL NULL 10652 2 2 10662 3 2 10673 2 3 10683 3 3 1069drop view v1; 1070drop table t1, t2; 1071create table t1 (s1 char); 1072create view v1 as select s1 collate latin1_german1_ci as s1 from t1; 1073insert into v1 values ('a'); 1074select * from v1; 1075s1 1076a 1077update v1 set s1='b'; 1078select * from v1; 1079s1 1080b 1081update v1,t1 set v1.s1='c' where t1.s1=v1.s1; 1082select * from v1; 1083s1 1084c 1085prepare stmt1 from "update v1,t1 set v1.s1=? where t1.s1=v1.s1"; 1086set @arg='d'; 1087execute stmt1 using @arg; 1088select * from v1; 1089s1 1090d 1091set @arg='e'; 1092execute stmt1 using @arg; 1093select * from v1; 1094s1 1095e 1096deallocate prepare stmt1; 1097drop view v1; 1098drop table t1; 1099create table t1 (a int); 1100create table t2 (a int); 1101create view v1 as select * from t1; 1102lock tables t1 read, v1 read; 1103select * from v1; 1104a 1105select * from t2; 1106ERROR HY000: Table 't2' was not locked with LOCK TABLES 1107unlock tables; 1108drop view v1; 1109drop table t1, t2; 1110create table t1 (a int); 1111create view v1 as select * from t1 where a < 2 with check option; 1112insert into v1 values(1); 1113insert into v1 values(3); 1114ERROR HY000: CHECK OPTION failed 'test.v1' 1115insert ignore into v1 values (2),(3),(0); 1116Warnings: 1117Warning 1369 CHECK OPTION failed 'test.v1' 1118Warning 1369 CHECK OPTION failed 'test.v1' 1119select * from t1; 1120a 11211 11220 1123delete from t1; 1124insert into v1 SELECT 1; 1125insert into v1 SELECT 3; 1126ERROR HY000: CHECK OPTION failed 'test.v1' 1127create table t2 (a int); 1128insert into t2 values (2),(3),(0); 1129insert ignore into v1 SELECT a from t2; 1130Warnings: 1131Warning 1369 CHECK OPTION failed 'test.v1' 1132Warning 1369 CHECK OPTION failed 'test.v1' 1133select * from t1 order by a desc; 1134a 11351 11360 1137update v1 set a=-1 where a=0; 1138update v1 set a=2 where a=1; 1139ERROR HY000: CHECK OPTION failed 'test.v1' 1140select * from t1 order by a desc; 1141a 11421 1143-1 1144update v1 set a=0 where a=0; 1145insert into t2 values (1); 1146update v1,t2 set v1.a=v1.a-1 where v1.a=t2.a; 1147select * from t1 order by a desc; 1148a 11490 1150-1 1151update v1 set a=a+1; 1152update ignore v1,t2 set v1.a=v1.a+1 where v1.a=t2.a; 1153Warnings: 1154Warning 1369 CHECK OPTION failed 'test.v1' 1155select * from t1; 1156a 11571 11581 1159drop view v1; 1160drop table t1, t2; 1161create table t1 (a int); 1162create view v1 as select * from t1 where a < 2 with check option; 1163create view v2 as select * from v1 where a > 0 with local check option; 1164create view v3 as select * from v1 where a > 0 with cascaded check option; 1165insert into v2 values (1); 1166insert into v3 values (1); 1167insert into v2 values (0); 1168ERROR HY000: CHECK OPTION failed 'test.v2' 1169insert into v3 values (0); 1170ERROR HY000: CHECK OPTION failed 'test.v3' 1171insert into v2 values (2); 1172insert into v3 values (2); 1173ERROR HY000: CHECK OPTION failed 'test.v3' 1174select * from t1; 1175a 11761 11771 11782 1179drop view v3,v2,v1; 1180drop table t1; 1181create table t1 (a int, primary key (a)); 1182create view v1 as select * from t1 where a < 2 with check option; 1183insert into v1 values (1) on duplicate key update a=2; 1184insert into v1 values (1) on duplicate key update a=2; 1185ERROR HY000: CHECK OPTION failed 'test.v1' 1186insert ignore into v1 values (1) on duplicate key update a=2; 1187Warnings: 1188Warning 1369 CHECK OPTION failed 'test.v1' 1189select * from t1; 1190a 11911 1192drop view v1; 1193drop table t1; 1194create table t1 (s1 int); 1195create view v1 as select * from t1; 1196create view v2 as select * from v1; 1197alter view v1 as select * from v2; 1198ERROR 42S02: Table 'test.v1' doesn't exist 1199alter view v1 as select * from v1; 1200ERROR 42S02: Table 'test.v1' doesn't exist 1201create or replace view v1 as select * from v2; 1202ERROR 42S02: Table 'test.v1' doesn't exist 1203create or replace view v1 as select * from v1; 1204ERROR 42S02: Table 'test.v1' doesn't exist 1205drop view v2,v1; 1206drop table t1; 1207create table t1 (a int); 1208create view v1 as select * from t1; 1209show create view v1; 1210View Create View character_set_client collation_connection 1211v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` latin1 latin1_swedish_ci 1212alter algorithm=undefined view v1 as select * from t1 with check option; 1213show create view v1; 1214View Create View character_set_client collation_connection 1215v1 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 1216alter algorithm=merge view v1 as select * from t1 with cascaded check option; 1217show create view v1; 1218View Create View character_set_client collation_connection 1219v1 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 1220alter algorithm=temptable view v1 as select * from t1; 1221show create view v1; 1222View Create View character_set_client collation_connection 1223v1 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` latin1 latin1_swedish_ci 1224drop view v1; 1225drop table t1; 1226create table t1 (s1 int); 1227create table t2 (s1 int); 1228create view v2 as select * from t2 where s1 in (select s1 from t1); 1229insert into v2 values (5); 1230insert into t1 values (5); 1231select * from v2; 1232s1 12335 1234update v2 set s1 = 0; 1235select * from v2; 1236s1 1237select * from t2; 1238s1 12390 1240alter view v2 as select * from t2 where s1 in (select s1 from t1) with check option; 1241insert into v2 values (5); 1242update v2 set s1 = 1; 1243ERROR HY000: CHECK OPTION failed 'test.v2' 1244insert into t1 values (1); 1245update v2 set s1 = 1; 1246select * from v2; 1247s1 12481 1249select * from t2; 1250s1 12510 12521 1253prepare stmt1 from "select * from v2;"; 1254execute stmt1; 1255s1 12561 1257insert into t1 values (0); 1258execute stmt1; 1259s1 12601 12610 1262deallocate prepare stmt1; 1263drop view v2; 1264drop table t1, t2; 1265create table t1 (t time); 1266create view v1 as select substring_index(t,':',2) as t from t1; 1267insert into t1 (t) values ('12:24:10'); 1268select substring_index(t,':',2) from t1; 1269substring_index(t,':',2) 127012:24 1271select substring_index(t,':',2) from v1; 1272substring_index(t,':',2) 127312:24 1274drop view v1; 1275drop table t1; 1276create table t1 (s1 tinyint); 1277create view v1 as select * from t1 where s1 <> 0 with local check option; 1278create view v2 as select * from v1 with cascaded check option; 1279insert into v2 values (0); 1280ERROR HY000: CHECK OPTION failed 'test.v2' 1281drop view v2, v1; 1282drop table t1; 1283create table t1 (s1 int); 1284create view v1 as select * from t1 where s1 < 5 with check option; 1285insert ignore into v1 values (6); 1286ERROR HY000: CHECK OPTION failed 'test.v1' 1287insert ignore into v1 values (6),(3); 1288Warnings: 1289Warning 1369 CHECK OPTION failed 'test.v1' 1290select * from t1; 1291s1 12923 1293drop view v1; 1294drop table t1; 1295create table t1 (s1 tinyint); 1296create trigger t1_bi before insert on t1 for each row set new.s1 = 500; 1297create view v1 as select * from t1 where s1 <> 127 with check option; 1298insert into v1 values (0); 1299ERROR HY000: CHECK OPTION failed 'test.v1' 1300select * from v1; 1301s1 1302select * from t1; 1303s1 1304drop trigger t1_bi; 1305drop view v1; 1306drop table t1; 1307create table t1 (s1 tinyint); 1308create view v1 as select * from t1 where s1 <> 0; 1309create view v2 as select * from v1 where s1 <> 1 with cascaded check option; 1310insert into v2 values (0); 1311ERROR HY000: CHECK OPTION failed 'test.v2' 1312select * from v2; 1313s1 1314select * from t1; 1315s1 1316drop view v2, v1; 1317drop table t1; 1318create table t1 (a int, b char(10)); 1319create view v1 as select * from t1 where a != 0 with check option; 1320load data infile '../../std_data/loaddata3.dat' into table v1 fields terminated by '' enclosed by '' ignore 1 lines; 1321ERROR HY000: CHECK OPTION failed 'test.v1' 1322select * from t1; 1323a b 13241 row 1 13252 row 2 1326select * from v1; 1327a b 13281 row 1 13292 row 2 1330delete from t1; 1331load data infile '../../std_data/loaddata3.dat' ignore into table v1 fields terminated by '' enclosed by '' ignore 1 lines; 1332Warnings: 1333Warning 1366 Incorrect integer value: 'error ' for column 'a' at row 3 1334Warning 1369 CHECK OPTION failed 'test.v1' 1335Warning 1366 Incorrect integer value: 'wrong end ' for column 'a' at row 4 1336Warning 1369 CHECK OPTION failed 'test.v1' 1337select * from t1 order by a,b; 1338a b 13391 row 1 13402 row 2 13413 row 3 1342select * from v1 order by a,b; 1343a b 13441 row 1 13452 row 2 13463 row 3 1347drop view v1; 1348drop table t1; 1349create table t1 (a text, b text); 1350create view v1 as select * from t1 where a <> 'Field A' with check option; 1351load data infile '../../std_data/loaddata2.dat' into table v1 fields terminated by ',' enclosed by ''''; 1352ERROR HY000: CHECK OPTION failed 'test.v1' 1353select concat('|',a,'|'), concat('|',b,'|') from t1; 1354concat('|',a,'|') concat('|',b,'|') 1355select concat('|',a,'|'), concat('|',b,'|') from v1; 1356concat('|',a,'|') concat('|',b,'|') 1357delete from t1; 1358load data infile '../../std_data/loaddata2.dat' ignore into table v1 fields terminated by ',' enclosed by ''''; 1359Warnings: 1360Warning 1369 CHECK OPTION failed 'test.v1' 1361Warning 1261 Row 2 doesn't contain data for all columns 1362select concat('|',a,'|'), concat('|',b,'|') from t1; 1363concat('|',a,'|') concat('|',b,'|') 1364|Field 1| |Field 2' 1365Field 3,'Field 4| 1366|Field 5' ,'Field 6| NULL 1367|Field 6| | 'Field 7'| 1368select concat('|',a,'|'), concat('|',b,'|') from v1; 1369concat('|',a,'|') concat('|',b,'|') 1370|Field 1| |Field 2' 1371Field 3,'Field 4| 1372|Field 5' ,'Field 6| NULL 1373|Field 6| | 'Field 7'| 1374drop view v1; 1375drop table t1; 1376create table t1 (s1 smallint); 1377create view v1 as select * from t1 where 20 < (select (s1) from t1); 1378insert into v1 values (30); 1379ERROR HY000: The target table v1 of the INSERT is not insertable-into 1380create view v2 as select * from t1; 1381create view v3 as select * from t1 where 20 < (select (s1) from v2); 1382insert into v3 values (30); 1383ERROR HY000: The target table v3 of the INSERT is not insertable-into 1384create view v4 as select * from v2 where 20 < (select (s1) from t1); 1385insert into v4 values (30); 1386ERROR HY000: The target table v4 of the INSERT is not insertable-into 1387drop view v4, v3, v2, v1; 1388drop table t1; 1389create table t1 (a int); 1390create view v1 as select * from t1; 1391check table t1,v1; 1392Table Op Msg_type Msg_text 1393test.t1 check status OK 1394test.v1 check status OK 1395check table v1,t1; 1396Table Op Msg_type Msg_text 1397test.v1 check status OK 1398test.t1 check status OK 1399drop table t1; 1400check table v1; 1401Table Op Msg_type Msg_text 1402test.v1 check Error Table 'test.t1' doesn't exist 1403test.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 1404test.v1 check error Corrupt 1405drop view v1; 1406create table t1 (a int); 1407create table t2 (a int); 1408create table t3 (a int); 1409insert into t1 values (1), (2), (3); 1410insert into t2 values (1), (3); 1411insert into t3 values (1), (2), (4); 1412create view v3 (a,b) as select t1.a as a, t2.a as b from t1 left join t2 on (t1.a=t2.a); 1413select * from t3 left join v3 on (t3.a = v3.a); 1414a a b 14151 1 1 14162 2 NULL 14174 NULL NULL 1418explain extended select * from t3 left join v3 on (t3.a = v3.a); 1419id select_type table type possible_keys key key_len ref rows filtered Extra 14201 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 NULL 14211 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (Block Nested Loop) 14221 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop) 1423Warnings: 1424Note 1003 /* select#1 */ 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`.`t1`.`a` = `test`.`t2`.`a`))) on((`test`.`t3`.`a` = `test`.`t1`.`a`)) where 1 1425create view v1 (a) as select a from t1; 1426create view v2 (a) as select a from t2; 1427create view v4 (a,b) as select v1.a as a, v2.a as b from v1 left join v2 on (v1.a=v2.a); 1428select * from t3 left join v4 on (t3.a = v4.a); 1429a a b 14301 1 1 14312 2 NULL 14324 NULL NULL 1433explain extended select * from t3 left join v4 on (t3.a = v4.a); 1434id select_type table type possible_keys key key_len ref rows filtered Extra 14351 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 NULL 14361 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (Block Nested Loop) 14371 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop) 1438Warnings: 1439Note 1003 /* select#1 */ 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`.`t1`.`a` = `test`.`t2`.`a`))) on((`test`.`t3`.`a` = `test`.`t1`.`a`)) where 1 1440prepare stmt1 from "select * from t3 left join v4 on (t3.a = v4.a);"; 1441execute stmt1; 1442a a b 14431 1 1 14442 2 NULL 14454 NULL NULL 1446execute stmt1; 1447a a b 14481 1 1 14492 2 NULL 14504 NULL NULL 1451deallocate prepare stmt1; 1452drop view v4,v3,v2,v1; 1453drop tables t1,t2,t3; 1454create table t1 (a int, primary key (a), b int); 1455create table t2 (a int, primary key (a)); 1456insert into t1 values (1,100), (2,200); 1457insert into t2 values (1), (3); 1458create view v3 (a,b) as select t1.a as a, t2.a as b from t1, t2; 1459update v3 set a= 10 where a=1; 1460select * from t1; 1461a b 146210 100 14632 200 1464select * from t2; 1465a 14661 14673 1468create view v2 (a,b) as select t1.b as a, t2.a as b from t1, t2; 1469set updatable_views_with_limit=NO; 1470update v2 set a= 10 where a=200 limit 1; 1471ERROR HY000: The target table t1 of the UPDATE is not updatable 1472set updatable_views_with_limit=DEFAULT; 1473select * from v3; 1474a b 14752 1 147610 1 14772 3 147810 3 1479select * from v2; 1480a b 1481100 1 1482200 1 1483100 3 1484200 3 1485set @a= 10; 1486set @b= 100; 1487prepare stmt1 from "update v3 set a= ? where a=?"; 1488execute stmt1 using @a,@b; 1489select * from v3; 1490a b 14912 1 149210 1 14932 3 149410 3 1495set @a= 300; 1496set @b= 10; 1497execute stmt1 using @a,@b; 1498select * from v3; 1499a b 15002 1 1501300 1 15022 3 1503300 3 1504deallocate prepare stmt1; 1505drop view v3,v2; 1506drop tables t1,t2; 1507create table t1 (a int, primary key (a), b int); 1508create table t2 (a int, primary key (a), b int); 1509insert into t2 values (1000, 2000); 1510create view v3 (a,b) as select t1.a as a, t2.a as b from t1, t2; 1511insert into v3 values (1,2); 1512ERROR HY000: Can not insert into join view 'test.v3' without fields list 1513insert into v3 select * from t2; 1514ERROR HY000: Can not insert into join view 'test.v3' without fields list 1515insert into v3(a,b) values (1,2); 1516ERROR HY000: Can not modify more than one base table through a join view 'test.v3' 1517insert into v3(a,b) select * from t2; 1518ERROR HY000: Can not modify more than one base table through a join view 'test.v3' 1519insert into v3(a) values (1); 1520insert into v3(b) values (10); 1521insert into v3(a) select a from t2; 1522insert into v3(b) select b from t2; 1523Warnings: 1524Warning 1048 Column 'a' cannot be null 1525insert into v3(a) values (1) on duplicate key update a=a+10000+VALUES(a); 1526select * from t1; 1527a b 152810002 NULL 152910 NULL 15301000 NULL 1531select * from t2; 1532a b 15331000 2000 153410 NULL 15352000 NULL 15360 NULL 1537delete from v3; 1538ERROR HY000: Can not delete from join view 'test.v3' 1539delete v3,t1 from v3,t1; 1540ERROR HY000: Can not delete from join view 'test.v3' 1541delete t1,v3 from t1,v3; 1542ERROR HY000: Can not delete from join view 'test.v3' 1543delete from t1; 1544prepare stmt1 from "insert into v3(a) values (?);"; 1545set @a= 100; 1546execute stmt1 using @a; 1547set @a= 300; 1548execute stmt1 using @a; 1549deallocate prepare stmt1; 1550prepare stmt1 from "insert into v3(a) select ?;"; 1551set @a= 101; 1552execute stmt1 using @a; 1553set @a= 301; 1554execute stmt1 using @a; 1555deallocate prepare stmt1; 1556select * from v3; 1557a b 1558100 0 1559101 0 1560300 0 1561301 0 1562100 10 1563101 10 1564300 10 1565301 10 1566100 1000 1567101 1000 1568300 1000 1569301 1000 1570100 2000 1571101 2000 1572300 2000 1573301 2000 1574drop view v3; 1575drop tables t1,t2; 1576create table t1(f1 int); 1577create view v1 as select f1 from t1; 1578select * from v1 where F1 = 1; 1579f1 1580drop view v1; 1581drop table t1; 1582create table t1(c1 int); 1583create table t2(c2 int); 1584insert into t1 values (1),(2),(3); 1585insert into t2 values (1); 1586SELECT c1 FROM t1 WHERE c1 IN (SELECT c2 FROM t2); 1587c1 15881 1589SELECT c1 FROM t1 WHERE EXISTS (SELECT c2 FROM t2 WHERE c2 = c1); 1590c1 15911 1592create view v1 as SELECT c1 FROM t1 WHERE c1 IN (SELECT c2 FROM t2); 1593create view v2 as SELECT c1 FROM t1 WHERE EXISTS (SELECT c2 FROM t2 WHERE c2 = c1); 1594select * from v1; 1595c1 15961 1597select * from v2; 1598c1 15991 1600select * from (select c1 from v2) X; 1601c1 16021 1603drop view v2, v1; 1604drop table t1, t2; 1605CREATE TABLE t1 (C1 INT, C2 INT); 1606CREATE TABLE t2 (C2 INT); 1607CREATE VIEW v1 AS SELECT C2 FROM t2; 1608CREATE VIEW v2 AS SELECT C1 FROM t1 LEFT OUTER JOIN v1 USING (C2); 1609SELECT * FROM v2; 1610C1 1611drop view v2, v1; 1612drop table t1, t2; 1613create table t1 (col1 char(5),col2 int,col3 int); 1614insert into t1 values ('one',10,25), ('two',10,50), ('two',10,50), ('one',20,25), ('one',30,25); 1615create view v1 as select * from t1; 1616select col1,group_concat(col2,col3) from t1 group by col1; 1617col1 group_concat(col2,col3) 1618one 1025,2025,3025 1619two 1050,1050 1620select col1,group_concat(col2,col3) from v1 group by col1; 1621col1 group_concat(col2,col3) 1622one 1025,2025,3025 1623two 1050,1050 1624drop view v1; 1625drop table t1; 1626create table t1 (s1 int, s2 char); 1627create view v1 as select s1, s2 from t1; 1628select s2 from v1 vq1 where 2 = (select count(*) from v1 vq2 having vq1.s2 = vq2.s2); 1629ERROR 42S22: Unknown column 'vq2.s2' in 'having clause' 1630select s2 from v1 vq1 where 2 = (select count(*) aa from v1 vq2 having vq1.s2 = aa); 1631s2 1632drop view v1; 1633drop table t1; 1634CREATE TABLE t1 (a1 int); 1635CREATE TABLE t2 (a2 int); 1636INSERT INTO t1 VALUES (1), (2), (3), (4); 1637INSERT INTO t2 VALUES (1), (2), (3); 1638CREATE VIEW v1(a,b) AS SELECT a1,a2 FROM t1 JOIN t2 ON a1=a2 WHERE a1>1; 1639SELECT * FROM v1; 1640a b 16412 2 16423 3 1643CREATE TABLE t3 SELECT * FROM v1; 1644SELECT * FROM t3; 1645a b 16462 2 16473 3 1648DROP VIEW v1; 1649DROP TABLE t1,t2,t3; 1650create table t1 (a int); 1651create table t2 like t1; 1652create table t3 like t1; 1653create view v1 as select t1.a x, t2.a y from t1 join t2 where t1.a=t2.a; 1654insert into t3 select x from v1; 1655insert into t2 select x from v1; 1656drop view v1; 1657drop table t1,t2,t3; 1658CREATE TABLE t1 (col1 int PRIMARY KEY, col2 varchar(10)); 1659INSERT INTO t1 VALUES(1,'trudy'); 1660INSERT INTO t1 VALUES(2,'peter'); 1661INSERT INTO t1 VALUES(3,'sanja'); 1662INSERT INTO t1 VALUES(4,'monty'); 1663INSERT INTO t1 VALUES(5,'david'); 1664INSERT INTO t1 VALUES(6,'kent'); 1665INSERT INTO t1 VALUES(7,'carsten'); 1666INSERT INTO t1 VALUES(8,'ranger'); 1667INSERT INTO t1 VALUES(10,'matt'); 1668CREATE TABLE t2 (col1 int, col2 int, col3 char(1)); 1669INSERT INTO t2 VALUES (1,1,'y'); 1670INSERT INTO t2 VALUES (1,2,'y'); 1671INSERT INTO t2 VALUES (2,1,'n'); 1672INSERT INTO t2 VALUES (3,1,'n'); 1673INSERT INTO t2 VALUES (4,1,'y'); 1674INSERT INTO t2 VALUES (4,2,'n'); 1675INSERT INTO t2 VALUES (4,3,'n'); 1676INSERT INTO t2 VALUES (6,1,'n'); 1677INSERT INTO t2 VALUES (8,1,'y'); 1678CREATE VIEW v1 AS SELECT * FROM t1; 1679SELECT a.col1,a.col2,b.col2,b.col3 1680FROM t1 a LEFT JOIN t2 b ON a.col1=b.col1 1681WHERE b.col2 IS NULL OR 1682b.col2=(SELECT MAX(col2) FROM t2 b WHERE b.col1=a.col1); 1683col1 col2 col2 col3 16841 trudy 2 y 168510 matt NULL NULL 16862 peter 1 n 16873 sanja 1 n 16884 monty 3 n 16895 david NULL NULL 16906 kent 1 n 16917 carsten NULL NULL 16928 ranger 1 y 1693SELECT a.col1,a.col2,b.col2,b.col3 1694FROM v1 a LEFT JOIN t2 b ON a.col1=b.col1 1695WHERE b.col2 IS NULL OR 1696b.col2=(SELECT MAX(col2) FROM t2 b WHERE b.col1=a.col1); 1697col1 col2 col2 col3 16981 trudy 2 y 169910 matt NULL NULL 17002 peter 1 n 17013 sanja 1 n 17024 monty 3 n 17035 david NULL NULL 17046 kent 1 n 17057 carsten NULL NULL 17068 ranger 1 y 1707CREATE VIEW v2 AS SELECT * FROM t2; 1708SELECT a.col1,a.col2,b.col2,b.col3 1709FROM v2 b RIGHT JOIN v1 a ON a.col1=b.col1 1710WHERE b.col2 IS NULL OR 1711b.col2=(SELECT MAX(col2) FROM v2 b WHERE b.col1=a.col1); 1712col1 col2 col2 col3 17131 trudy 2 y 171410 matt NULL NULL 17152 peter 1 n 17163 sanja 1 n 17174 monty 3 n 17185 david NULL NULL 17196 kent 1 n 17207 carsten NULL NULL 17218 ranger 1 y 1722SELECT a.col1,a.col2,b.col2,b.col3 1723FROM v2 b RIGHT JOIN v1 a ON a.col1=b.col1 1724WHERE a.col1 IN (1,5,9) AND 1725(b.col2 IS NULL OR 1726b.col2=(SELECT MAX(col2) FROM v2 b WHERE b.col1=a.col1)); 1727col1 col2 col2 col3 17281 trudy 2 y 17295 david NULL NULL 1730CREATE VIEW v3 AS SELECT * FROM t1 WHERE col1 IN (1,5,9); 1731SELECT a.col1,a.col2,b.col2,b.col3 1732FROM v2 b RIGHT JOIN v3 a ON a.col1=b.col1 1733WHERE b.col2 IS NULL OR 1734b.col2=(SELECT MAX(col2) FROM v2 b WHERE b.col1=a.col1); 1735col1 col2 col2 col3 17361 trudy 2 y 17375 david NULL NULL 1738DROP VIEW v1,v2,v3; 1739DROP TABLE t1,t2; 1740create table t1 as select 1 A union select 2 union select 3; 1741create table t2 as select * from t1; 1742create view v1 as select * from t1 where a in (select * from t2); 1743select * from v1 A, v1 B where A.a = B.a; 1744A A 17451 1 17462 2 17473 3 1748create table t3 as select a a,a b from t2; 1749create view v2 as select * from t3 where 1750a in (select * from t1) or b in (select * from t2); 1751select * from v2 A, v2 B where A.a = B.b; 1752a b a b 17531 1 1 1 17542 2 2 2 17553 3 3 3 1756drop view v1, v2; 1757drop table t1, t2, t3; 1758CREATE TABLE t1 (a int); 1759CREATE TABLE t2 (b int); 1760INSERT INTO t1 VALUES (1), (2), (3), (4); 1761INSERT INTO t2 VALUES (4), (2); 1762CREATE VIEW v1 AS SELECT * FROM t1,t2 WHERE t1.a=t2.b; 1763SELECT * FROM v1; 1764a b 17652 2 17664 4 1767CREATE VIEW v2 AS SELECT * FROM v1; 1768SELECT * FROM v2; 1769a b 17702 2 17714 4 1772DROP VIEW v2,v1; 1773DROP TABLE t1, t2; 1774create table t1 (a int); 1775create view v1 as select sum(a) from t1 group by a; 1776create procedure p1() 1777begin 1778select * from v1; 1779end// 1780call p1(); 1781sum(a) 1782call p1(); 1783sum(a) 1784drop procedure p1; 1785drop view v1; 1786drop table t1; 1787CREATE TABLE t1(a char(2) primary key, b char(2)); 1788CREATE TABLE t2(a char(2), b char(2), index i(a)); 1789INSERT INTO t1 VALUES ('a','1'), ('b','2'); 1790INSERT INTO t2 VALUES ('a','5'), ('a','6'), ('b','5'), ('b','6'); 1791CREATE VIEW v1 AS 1792SELECT t1.b as c, t2.b as d FROM t1,t2 WHERE t1.a=t2.a; 1793SELECT d, c FROM v1 ORDER BY d,c; 1794d c 17955 1 17965 2 17976 1 17986 2 1799DROP VIEW v1; 1800DROP TABLE t1, t2; 1801create table t1 (s1 int); 1802create view v1 as select sum(distinct s1) from t1; 1803select * from v1; 1804sum(distinct s1) 1805NULL 1806drop view v1; 1807create view v1 as select avg(distinct s1) from t1; 1808select * from v1; 1809avg(distinct s1) 1810NULL 1811drop view v1; 1812drop table t1; 1813create view v1 as select cast(1 as decimal); 1814select * from v1; 1815cast(1 as decimal) 18161 1817drop view v1; 1818create table t1(f1 int); 1819create table t2(f2 int); 1820insert into t1 values(1),(2),(3); 1821insert into t2 values(1),(2),(3); 1822create view v1 as select * from t1,t2 where f1=f2; 1823create table t3 (f1 int, f2 int); 1824insert into t3 select * from v1 order by 1; 1825select * from t3; 1826f1 f2 18271 1 18282 2 18293 3 1830drop view v1; 1831drop table t1,t2,t3; 1832create view v1 as select '\\','\\shazam'; 1833select * from v1; 1834\ \shazam 1835\ \shazam 1836drop view v1; 1837create view v1 as select '\'','\shazam'; 1838select * from v1; 1839' shazam 1840' shazam 1841drop view v1; 1842create view v1 as select 'k','K'; 1843select * from v1; 1844k My_exp_K 1845k K 1846drop view v1; 1847create table t1 (s1 int); 1848create view v1 as select s1, 's1' from t1; 1849select * from v1; 1850s1 My_exp_s1 1851drop view v1; 1852create view v1 as select 's1', s1 from t1; 1853select * from v1; 1854My_exp_s1 s1 1855drop view v1; 1856create view v1 as select 's1', s1, 1 as My_exp_s1 from t1; 1857select * from v1; 1858My_exp_1_s1 s1 My_exp_s1 1859drop view v1; 1860create view v1 as select 1 as My_exp_s1, 's1', s1 from t1; 1861select * from v1; 1862My_exp_s1 My_exp_1_s1 s1 1863drop view v1; 1864create view v1 as select 1 as s1, 's1', 's1' from t1; 1865select * from v1; 1866s1 My_exp_s1 My_exp_1_s1 1867drop view v1; 1868create view v1 as select 's1', 's1', 1 as s1 from t1; 1869select * from v1; 1870My_exp_1_s1 My_exp_s1 s1 1871drop view v1; 1872create view v1 as select s1, 's1', 's1' from t1; 1873select * from v1; 1874s1 My_exp_s1 My_exp_1_s1 1875drop view v1; 1876create view v1 as select 's1', 's1', s1 from t1; 1877select * from v1; 1878My_exp_1_s1 My_exp_s1 s1 1879drop view v1; 1880create view v1 as select 1 as s1, 's1', s1 from t1; 1881ERROR 42S21: Duplicate column name 's1' 1882create view v1 as select 's1', s1, 1 as s1 from t1; 1883ERROR 42S21: Duplicate column name 's1' 1884drop table t1; 1885create view v1(k, K) as select 1,2; 1886ERROR 42S21: Duplicate column name 'K' 1887create view v1 as SELECT TIME_FORMAT(SEC_TO_TIME(3600),'%H:%i') as t; 1888select * from v1; 1889t 189001:00 1891drop view v1; 1892create table t1 (a timestamp default now()); 1893create table t2 (b timestamp default now()); 1894create view v1 as select a,b,t1.a < now() from t1,t2 where t1.a < now(); 1895SHOW CREATE VIEW v1; 1896View Create View character_set_client collation_connection 1897v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a`,`t2`.`b` AS `b`,(`t1`.`a` < now()) AS `t1.a < now()` from (`t1` join `t2`) where (`t1`.`a` < now()) latin1 latin1_swedish_ci 1898drop view v1; 1899drop table t1, t2; 1900CREATE TABLE t1 ( a varchar(50) ); 1901CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = CURRENT_USER(); 1902SHOW CREATE VIEW v1; 1903View Create View character_set_client collation_connection 1904v1 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 1905DROP VIEW v1; 1906CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = VERSION(); 1907SHOW CREATE VIEW v1; 1908View Create View character_set_client collation_connection 1909v1 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 1910DROP VIEW v1; 1911CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = DATABASE(); 1912SHOW CREATE VIEW v1; 1913View Create View character_set_client collation_connection 1914v1 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 1915DROP VIEW v1; 1916DROP TABLE t1; 1917CREATE TABLE t1 (col1 time); 1918CREATE TABLE t2 (col1 time); 1919CREATE VIEW v1 AS SELECT CONVERT_TZ(col1,'GMT','MET') FROM t1; 1920CREATE VIEW v2 AS SELECT CONVERT_TZ(col1,'GMT','MET') FROM t2; 1921CREATE VIEW v3 AS SELECT CONVERT_TZ(col1,'GMT','MET') FROM t1; 1922CREATE VIEW v4 AS SELECT CONVERT_TZ(col1,'GMT','MET') FROM t2; 1923CREATE VIEW v5 AS SELECT CONVERT_TZ(col1,'GMT','MET') FROM t1; 1924CREATE VIEW v6 AS SELECT CONVERT_TZ(col1,'GMT','MET') FROM t2; 1925DROP TABLE t1; 1926CHECK TABLE v1, v2, v3, v4, v5, v6; 1927Table Op Msg_type Msg_text 1928test.v1 check Error Table 'test.t1' doesn't exist 1929test.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 1930test.v1 check error Corrupt 1931test.v2 check status OK 1932test.v3 check Error Table 'test.t1' doesn't exist 1933test.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 1934test.v3 check error Corrupt 1935test.v4 check status OK 1936test.v5 check Error Table 'test.t1' doesn't exist 1937test.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 1938test.v5 check error Corrupt 1939test.v6 check status OK 1940drop view v1, v2, v3, v4, v5, v6; 1941drop table t2; 1942drop function if exists f1; 1943drop function if exists f2; 1944CREATE TABLE t1 (col1 time); 1945CREATE TABLE t2 (col1 time); 1946CREATE TABLE t3 (col1 time); 1947create function f1 () returns int return (select max(col1) from t1); 1948create function f2 () returns int return (select max(col1) from t2); 1949CREATE VIEW v1 AS SELECT f1() FROM t3; 1950CREATE VIEW v2 AS SELECT f2() FROM t3; 1951CREATE VIEW v3 AS SELECT f1() FROM t3; 1952CREATE VIEW v4 AS SELECT f2() FROM t3; 1953CREATE VIEW v5 AS SELECT f1() FROM t3; 1954CREATE VIEW v6 AS SELECT f2() FROM t3; 1955drop function f1; 1956CHECK TABLE v1, v2, v3, v4, v5, v6; 1957Table Op Msg_type Msg_text 1958test.v1 check Error FUNCTION test.f1 does not exist 1959test.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 1960test.v1 check error Corrupt 1961test.v2 check status OK 1962test.v3 check Error FUNCTION test.f1 does not exist 1963test.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 1964test.v3 check error Corrupt 1965test.v4 check status OK 1966test.v5 check Error FUNCTION test.f1 does not exist 1967test.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 1968test.v5 check error Corrupt 1969test.v6 check status OK 1970create function f1 () returns int return (select max(col1) from t1); 1971DROP TABLE t1; 1972CHECK TABLE v1, v2, v3, v4, v5, v6; 1973Table Op Msg_type Msg_text 1974test.v1 check status OK 1975test.v2 check status OK 1976test.v3 check status OK 1977test.v4 check status OK 1978test.v5 check status OK 1979test.v6 check status OK 1980drop function f1; 1981drop function f2; 1982drop view v1, v2, v3, v4, v5, v6; 1983drop table t2,t3; 1984create table t1 (f1 date); 1985insert into t1 values ('2005-01-01'),('2005-02-02'); 1986create view v1 as select * from t1; 1987select * from v1 where f1='2005.02.02'; 1988f1 19892005-02-02 1990select * from v1 where '2005.02.02'=f1; 1991f1 19922005-02-02 1993drop view v1; 1994drop table t1; 1995CREATE VIEW v1 AS SELECT ENCRYPT("dhgdhgd"); 1996SELECT * FROM v1; 1997drop view v1; 1998CREATE VIEW v1 AS SELECT SUBSTRING_INDEX("dkjhgd:kjhdjh", ":", 1); 1999SELECT * FROM v1; 2000SUBSTRING_INDEX("dkjhgd:kjhdjh", ":", 1) 2001dkjhgd 2002drop view v1; 2003create table t1 (f59 int, f60 int, f61 int); 2004insert into t1 values (19,41,32); 2005create view v1 as select f59, f60 from t1 where f59 in 2006(select f59 from t1); 2007update v1 set f60=2345; 2008ERROR HY000: The target table v1 of the UPDATE is not updatable 2009update t1 set f60=(select max(f60) from v1); 2010ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 't1'. 2011drop view v1; 2012drop table t1; 2013create table t1 (s1 int); 2014create view v1 as select var_samp(s1) from t1; 2015show create view v1; 2016View Create View character_set_client collation_connection 2017v1 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 2018drop view v1; 2019drop table t1; 2020set sql_mode='strict_all_tables'; 2021CREATE TABLE t1 (col1 INT NOT NULL, col2 INT NOT NULL); 2022CREATE VIEW v1 (vcol1) AS SELECT col1 FROM t1; 2023CREATE VIEW v2 (vcol1) AS SELECT col1 FROM t1 WHERE col2 > 2; 2024INSERT INTO t1 (col1) VALUES(12); 2025ERROR HY000: Field 'col2' doesn't have a default value 2026INSERT INTO v1 (vcol1) VALUES(12); 2027ERROR HY000: Field of view 'test.v1' underlying table doesn't have a default value 2028INSERT INTO v2 (vcol1) VALUES(12); 2029ERROR HY000: Field of view 'test.v2' underlying table doesn't have a default value 2030set sql_mode=default; 2031drop view v2,v1; 2032drop table t1; 2033create table t1 (f1 int); 2034insert into t1 values (1); 2035create view v1 as select f1 from t1; 2036select f1 as alias from v1; 2037alias 20381 2039drop view v1; 2040drop table t1; 2041CREATE TABLE t1 (s1 int, s2 int); 2042INSERT INTO t1 VALUES (1,2); 2043CREATE VIEW v1 AS SELECT s2 AS s1, s1 AS s2 FROM t1; 2044SELECT * FROM v1; 2045s1 s2 20462 1 2047CREATE PROCEDURE p1 () SELECT * FROM v1; 2048CALL p1(); 2049s1 s2 20502 1 2051ALTER VIEW v1 AS SELECT s1 AS s1, s2 AS s2 FROM t1; 2052CALL p1(); 2053s1 s2 20541 2 2055DROP VIEW v1; 2056CREATE VIEW v1 AS SELECT s2 AS s1, s1 AS s2 FROM t1; 2057CALL p1(); 2058s1 s2 20592 1 2060DROP PROCEDURE p1; 2061DROP VIEW v1; 2062DROP TABLE t1; 2063create table t1 (f1 int, f2 int); 2064create view v1 as select f1 as f3, f2 as f1 from t1; 2065insert into t1 values (1,3),(2,1),(3,2); 2066select * from v1 order by f1; 2067f3 f1 20682 1 20693 2 20701 3 2071drop view v1; 2072drop table t1; 2073CREATE TABLE t1 (f1 char); 2074INSERT INTO t1 VALUES ('A'); 2075CREATE VIEW v1 AS SELECT * FROM t1; 2076INSERT INTO t1 VALUES('B'); 2077SELECT * FROM v1; 2078f1 2079A 2080B 2081SELECT * FROM t1; 2082f1 2083A 2084B 2085DROP VIEW v1; 2086DROP TABLE t1; 2087CREATE TABLE t1 ( bug_table_seq INTEGER NOT NULL); 2088CREATE OR REPLACE VIEW v1 AS SELECT * from t1; 2089DROP PROCEDURE IF EXISTS p1; 2090Warnings: 2091Note 1305 PROCEDURE test.p1 does not exist 2092CREATE PROCEDURE p1 ( ) 2093BEGIN 2094DO (SELECT @next := IFNULL(max(bug_table_seq),0) + 1 FROM v1); 2095INSERT INTO t1 VALUES (1); 2096END // 2097CALL p1(); 2098DROP PROCEDURE p1; 2099DROP VIEW v1; 2100DROP TABLE t1; 2101create table t1(f1 datetime); 2102insert into t1 values('2005.01.01 12:0:0'); 2103create view v1 as select f1, subtime(f1, '1:1:1') as sb from t1; 2104select * from v1; 2105f1 sb 21062005-01-01 12:00:00 2005-01-01 10:58:59 2107drop view v1; 2108drop table t1; 2109CREATE TABLE t1 ( 2110aid int PRIMARY KEY, 2111fn varchar(20) NOT NULL, 2112ln varchar(20) NOT NULL 2113); 2114CREATE TABLE t2 ( 2115aid int NOT NULL, 2116pid int NOT NULL 2117); 2118INSERT INTO t1 VALUES(1,'a','b'), (2,'c','d'); 2119INSERT INTO t2 values (1,1), (2,1), (2,2); 2120CREATE VIEW v1 AS SELECT t1.*,t2.pid FROM t1,t2 WHERE t1.aid = t2.aid; 2121SELECT pid,GROUP_CONCAT(CONCAT(fn,' ',ln) ORDER BY 1) FROM t1,t2 2122WHERE t1.aid = t2.aid GROUP BY pid; 2123pid GROUP_CONCAT(CONCAT(fn,' ',ln) ORDER BY 1) 21241 a b,c d 21252 c d 2126SELECT pid,GROUP_CONCAT(CONCAT(fn,' ',ln) ORDER BY 1) FROM v1 GROUP BY pid; 2127pid GROUP_CONCAT(CONCAT(fn,' ',ln) ORDER BY 1) 21281 a b,c d 21292 c d 2130DROP VIEW v1; 2131DROP TABLE t1,t2; 2132CREATE TABLE t1 (id int PRIMARY KEY, f varchar(255)); 2133CREATE VIEW v1 AS SELECT id, f FROM t1 WHERE id <= 2; 2134INSERT INTO t1 VALUES (2, 'foo2'); 2135INSERT INTO t1 VALUES (1, 'foo1'); 2136SELECT * FROM v1; 2137id f 21381 foo1 21392 foo2 2140SELECT * FROM v1; 2141id f 21421 foo1 21432 foo2 2144DROP VIEW v1; 2145DROP TABLE t1; 2146CREATE TABLE t1 (pk int PRIMARY KEY, b int); 2147CREATE TABLE t2 (pk int PRIMARY KEY, fk int, INDEX idx(fk)); 2148CREATE TABLE t3 (pk int PRIMARY KEY, fk int, INDEX idx(fk)); 2149CREATE TABLE t4 (pk int PRIMARY KEY, fk int, INDEX idx(fk)); 2150CREATE TABLE t5 (pk int PRIMARY KEY, fk int, INDEX idx(fk)); 2151CREATE VIEW v1 AS 2152SELECT t1.pk as a FROM t1,t2,t3,t4,t5 2153WHERE t1.b IS NULL AND 2154t1.pk=t2.fk AND t2.pk=t3.fk AND t3.pk=t4.fk AND t4.pk=t5.fk; 2155SELECT a FROM v1; 2156a 2157DROP VIEW v1; 2158DROP TABLE t1,t2,t3,t4,t5; 2159create view v1 as select timestampdiff(day,'1997-01-01 00:00:00','1997-01-02 00:00:00') as f1; 2160select * from v1; 2161f1 21621 2163drop view v1; 2164create table t1(a int); 2165create procedure p1() create view v1 as select * from t1; 2166drop table t1; 2167call p1(); 2168ERROR 42S02: Table 'test.t1' doesn't exist 2169call p1(); 2170ERROR 42S02: Table 'test.t1' doesn't exist 2171drop procedure p1; 2172create table t1 (f1 int); 2173create table t2 (f1 int); 2174insert into t1 values (1); 2175insert into t2 values (2); 2176create view v1 as select * from t1 union select * from t2 union all select * from t2; 2177select * from v1; 2178f1 21791 21802 21812 2182drop view v1; 2183drop table t1,t2; 2184CREATE TEMPORARY TABLE t1 (a int); 2185CREATE FUNCTION f1 () RETURNS int RETURN (SELECT COUNT(*) FROM t1); 2186CREATE VIEW v1 AS SELECT f1(); 2187ERROR HY000: View's SELECT refers to a temporary table 't1' 2188DROP FUNCTION f1; 2189DROP TABLE t1; 2190DROP TABLE IF EXISTS t1; 2191DROP VIEW IF EXISTS v1; 2192CREATE TABLE t1 (f4 CHAR(5)); 2193CREATE VIEW v1 AS SELECT * FROM t1; 2194DESCRIBE v1; 2195Field Type Null Key Default Extra 2196f4 char(5) YES NULL 2197ALTER TABLE t1 CHANGE COLUMN f4 f4x CHAR(5); 2198DESCRIBE v1; 2199ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 2200DROP TABLE t1; 2201DROP VIEW v1; 2202create table t1 (f1 char); 2203create view v1 as select strcmp(f1,'a') from t1; 2204select * from v1; 2205strcmp(f1,'a') 2206drop view v1; 2207drop table t1; 2208create table t1 (f1 int, f2 int,f3 int); 2209insert into t1 values (1,10,20),(2,0,0); 2210create view v1 as select * from t1; 2211select if(sum(f1)>1,f2,f3) from v1 group by f1; 2212if(sum(f1)>1,f2,f3) 221320 22140 2215drop view v1; 2216drop table t1; 2217create table t1 ( 2218r_object_id char(16) NOT NULL, 2219group_name varchar(32) NOT NULL 2220) engine = InnoDB; 2221create table t2 ( 2222r_object_id char(16) NOT NULL, 2223i_position int(11) NOT NULL, 2224users_names varchar(32) default NULL 2225) Engine = InnoDB; 2226create view v1 as select r_object_id, group_name from t1; 2227create view v2 as select r_object_id, i_position, users_names from t2; 2228create unique index r_object_id on t1(r_object_id); 2229create index group_name on t1(group_name); 2230create unique index r_object_id_i_position on t2(r_object_id,i_position); 2231create index users_names on t2(users_names); 2232insert into t1 values('120001a080000542','tstgroup1'); 2233insert into t2 values('120001a080000542',-1, 'guser01'); 2234insert into t2 values('120001a080000542',-2, 'guser02'); 2235select v1.r_object_id, v2.users_names from v1, v2 2236where (v1.group_name='tstgroup1') and v2.r_object_id=v1.r_object_id 2237order by users_names; 2238r_object_id users_names 2239120001a080000542 guser01 2240120001a080000542 guser02 2241drop view v1, v2; 2242drop table t1, t2; 2243create table t1 (s1 int); 2244create view abc as select * from t1 as abc; 2245drop table t1; 2246drop view abc; 2247create table t1(f1 char(1)); 2248create view v1 as select * from t1; 2249select * from (select f1 as f2 from v1) v where v.f2='a'; 2250f2 2251drop view v1; 2252drop table t1; 2253create view v1 as SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET'); 2254select * from v1; 2255CONVERT_TZ('2004-01-01 12:00:00','GMT','MET') 2256NULL 2257drop view v1; 2258CREATE TABLE t1 (date DATE NOT NULL); 2259INSERT INTO t1 VALUES ('2005-09-06'); 2260CREATE VIEW v1 AS SELECT DAYNAME(date) FROM t1; 2261SHOW CREATE VIEW v1; 2262View Create View character_set_client collation_connection 2263v1 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 2264CREATE VIEW v2 AS SELECT DAYOFWEEK(date) FROM t1; 2265SHOW CREATE VIEW v2; 2266View Create View character_set_client collation_connection 2267v2 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 2268CREATE VIEW v3 AS SELECT WEEKDAY(date) FROM t1; 2269SHOW CREATE VIEW v3; 2270View Create View character_set_client collation_connection 2271v3 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 2272SELECT DAYNAME('2005-09-06'); 2273DAYNAME('2005-09-06') 2274Tuesday 2275SELECT DAYNAME(date) FROM t1; 2276DAYNAME(date) 2277Tuesday 2278SELECT * FROM v1; 2279DAYNAME(date) 2280Tuesday 2281SELECT DAYOFWEEK('2005-09-06'); 2282DAYOFWEEK('2005-09-06') 22833 2284SELECT DAYOFWEEK(date) FROM t1; 2285DAYOFWEEK(date) 22863 2287SELECT * FROM v2; 2288DAYOFWEEK(date) 22893 2290SELECT WEEKDAY('2005-09-06'); 2291WEEKDAY('2005-09-06') 22921 2293SELECT WEEKDAY(date) FROM t1; 2294WEEKDAY(date) 22951 2296SELECT * FROM v3; 2297WEEKDAY(date) 22981 2299DROP TABLE t1; 2300DROP VIEW v1, v2, v3; 2301CREATE TABLE t1 ( a int, b int ); 2302INSERT INTO t1 VALUES (1,1),(2,2),(3,3); 2303CREATE VIEW v1 AS SELECT a,b FROM t1; 2304SELECT t1.a FROM t1 GROUP BY t1.a HAVING a > 1; 2305a 23062 23073 2308SELECT v1.a FROM v1 GROUP BY v1.a HAVING a > 1; 2309a 23102 23113 2312DROP VIEW v1; 2313DROP TABLE t1; 2314CREATE TABLE t1 ( a int, b int ); 2315INSERT INTO t1 VALUES (1,1),(2,2),(3,3); 2316CREATE VIEW v1 AS SELECT a,b FROM t1; 2317SELECT t1.a FROM t1 GROUP BY t1.a HAVING t1.a > 1; 2318a 23192 23203 2321SELECT v1.a FROM v1 GROUP BY v1.a HAVING v1.a > 1; 2322a 23232 23243 2325SELECT t_1.a FROM t1 AS t_1 GROUP BY t_1.a HAVING t_1.a IN (1,2,3); 2326a 23271 23282 23293 2330SELECT v_1.a FROM v1 AS v_1 GROUP BY v_1.a HAVING v_1.a IN (1,2,3); 2331a 23321 23332 23343 2335DROP VIEW v1; 2336DROP TABLE t1; 2337CREATE TABLE t1 (a INT, b INT, INDEX(a,b)); 2338CREATE TABLE t2 LIKE t1; 2339CREATE TABLE t3 (a INT); 2340INSERT INTO t1 VALUES (1,1),(2,2),(3,3); 2341INSERT INTO t2 VALUES (1,1),(2,2),(3,3); 2342INSERT INTO t3 VALUES (1),(2),(3); 2343CREATE VIEW v1 AS SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a AND t1.b=t2.b; 2344CREATE VIEW v2 AS SELECT t3.* FROM t1,t3 WHERE t1.a=t3.a; 2345EXPLAIN SELECT t1.* FROM t1 JOIN t2 WHERE t1.a=t2.a AND t1.b=t2.b AND t1.a=1; 2346id select_type table type possible_keys key key_len ref rows Extra 23471 SIMPLE t1 ref a a 5 const 1 Using where; Using index 23481 SIMPLE t2 ref a a 10 const,test.t1.b 1 Using index 2349EXPLAIN SELECT * FROM v1 WHERE a=1; 2350id select_type table type possible_keys key key_len ref rows Extra 23511 SIMPLE t1 ref a a 5 const 1 Using where; Using index 23521 SIMPLE t2 ref a a 10 const,test.t1.b 1 Using index 2353EXPLAIN SELECT * FROM v2 WHERE a=1; 2354id select_type table type possible_keys key key_len ref rows Extra 23551 SIMPLE t1 ref a a 5 const 1 Using index 23561 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (Block Nested Loop) 2357DROP VIEW v1,v2; 2358DROP TABLE t1,t2,t3; 2359create table t1 (f1 int); 2360create view v1 as select t1.f1 as '123 2361456' from t1; 2362select * from v1; 2363123 2364456 2365drop view v1; 2366drop table t1; 2367create table t1 (f1 int, f2 int); 2368insert into t1 values(1,1),(1,2),(1,3); 2369create view v1 as select f1 ,group_concat(f2 order by f2 asc) from t1 group by f1; 2370create view v2 as select f1 ,group_concat(f2 order by f2 desc) from t1 group by f1; 2371select * from v1; 2372f1 group_concat(f2 order by f2 asc) 23731 1,2,3 2374select * from v2; 2375f1 group_concat(f2 order by f2 desc) 23761 3,2,1 2377drop view v1,v2; 2378drop table t1; 2379create table t1 (x int, y int); 2380create table t2 (x int, y int, z int); 2381create table t3 (x int, y int, z int); 2382create table t4 (x int, y int, z int); 2383create view v1 as 2384select t1.x 2385from ( 2386(t1 join t2 on ((t1.y = t2.y))) 2387join 2388(t3 left join t4 on (t3.y = t4.y) and (t3.z = t4.z)) 2389); 2390prepare stmt1 from "select count(*) from v1 where x = ?"; 2391set @parm1=1; 2392execute stmt1 using @parm1; 2393count(*) 23940 2395execute stmt1 using @parm1; 2396count(*) 23970 2398drop view v1; 2399drop table t1,t2,t3,t4; 2400CREATE TABLE t1(id INT); 2401CREATE VIEW v1 AS SELECT id FROM t1; 2402OPTIMIZE TABLE v1; 2403Table Op Msg_type Msg_text 2404test.v1 optimize Error 'test.v1' is not BASE TABLE 2405test.v1 optimize status Operation failed 2406ANALYZE TABLE v1; 2407Table Op Msg_type Msg_text 2408test.v1 analyze Error 'test.v1' is not BASE TABLE 2409test.v1 analyze status Operation failed 2410REPAIR TABLE v1; 2411Table Op Msg_type Msg_text 2412test.v1 repair Error 'test.v1' is not BASE TABLE 2413test.v1 repair status Operation failed 2414DROP TABLE t1; 2415OPTIMIZE TABLE v1; 2416Table Op Msg_type Msg_text 2417test.v1 optimize Error 'test.v1' is not BASE TABLE 2418test.v1 optimize status Operation failed 2419ANALYZE TABLE v1; 2420Table Op Msg_type Msg_text 2421test.v1 analyze Error 'test.v1' is not BASE TABLE 2422test.v1 analyze status Operation failed 2423REPAIR TABLE v1; 2424Table Op Msg_type Msg_text 2425test.v1 repair Error 'test.v1' is not BASE TABLE 2426test.v1 repair status Operation failed 2427DROP VIEW v1; 2428create definer = current_user() sql security invoker view v1 as select 1; 2429show create view v1; 2430View Create View character_set_client collation_connection 2431v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `v1` AS select 1 AS `1` latin1 latin1_swedish_ci 2432drop view v1; 2433create definer = current_user sql security invoker view v1 as select 1; 2434show create view v1; 2435View Create View character_set_client collation_connection 2436v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `v1` AS select 1 AS `1` latin1 latin1_swedish_ci 2437drop view v1; 2438create table t1 (id INT, primary key(id)); 2439insert into t1 values (1),(2); 2440create view v1 as select * from t1; 2441explain select id from v1 order by id; 2442id select_type table type possible_keys key key_len ref rows Extra 24431 SIMPLE t1 index NULL PRIMARY 4 NULL 2 Using index 2444drop view v1; 2445drop table t1; 2446create table t1(f1 int, f2 int); 2447insert into t1 values (null, 10), (null,2); 2448select f1, sum(f2) from t1 group by f1; 2449f1 sum(f2) 2450NULL 12 2451create view v1 as select * from t1; 2452select f1, sum(f2) from v1 group by f1; 2453f1 sum(f2) 2454NULL 12 2455drop view v1; 2456drop table t1; 2457drop procedure if exists p1; 2458create procedure p1 () deterministic 2459begin 2460create view v1 as select 1; 2461end; 2462// 2463call p1(); 2464show create view v1; 2465View Create View character_set_client collation_connection 2466v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `1` latin1 latin1_swedish_ci 2467drop view v1; 2468drop procedure p1; 2469CREATE VIEW v1 AS SELECT 42 AS Meaning; 2470DROP FUNCTION IF EXISTS f1; 2471CREATE FUNCTION f1() RETURNS INTEGER 2472BEGIN 2473DECLARE retn INTEGER; 2474SELECT Meaning FROM v1 INTO retn; 2475RETURN retn; 2476END 2477// 2478CREATE VIEW v2 AS SELECT f1(); 2479select * from v2; 2480f1() 248142 2482drop view v2,v1; 2483drop function f1; 2484create table t1 (id numeric, warehouse_id numeric); 2485create view v1 as select id from t1; 2486create view v2 as 2487select t1.warehouse_id, v1.id as receipt_id 2488from t1, v1 where t1.id = v1.id; 2489insert into t1 (id, warehouse_id) values(3, 2); 2490insert into t1 (id, warehouse_id) values(4, 2); 2491insert into t1 (id, warehouse_id) values(5, 1); 2492select v2.receipt_id as alias1, v2.receipt_id as alias2 from v2 2493order by v2.receipt_id; 2494alias1 alias2 24953 3 24964 4 24975 5 2498drop view v2, v1; 2499drop table t1; 2500CREATE TABLE t1 (a int PRIMARY KEY, b int); 2501INSERT INTO t1 VALUES (2,20), (3,10), (1,10), (0,30), (5,10); 2502CREATE VIEW v1 AS SELECT * FROM t1; 2503SELECT MAX(a) FROM t1; 2504MAX(a) 25055 2506SELECT MAX(a) FROM v1; 2507MAX(a) 25085 2509EXPLAIN SELECT MAX(a) FROM t1; 2510id select_type table type possible_keys key key_len ref rows Extra 25111 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2512EXPLAIN SELECT MAX(a) FROM v1; 2513id select_type table type possible_keys key key_len ref rows Extra 25141 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2515SELECT MIN(a) FROM t1; 2516MIN(a) 25170 2518SELECT MIN(a) FROM v1; 2519MIN(a) 25200 2521EXPLAIN SELECT MIN(a) FROM t1; 2522id select_type table type possible_keys key key_len ref rows Extra 25231 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2524EXPLAIN SELECT MIN(a) FROM v1; 2525id select_type table type possible_keys key key_len ref rows Extra 25261 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2527DROP VIEW v1; 2528DROP TABLE t1; 2529CREATE TABLE t1 (x varchar(10)); 2530INSERT INTO t1 VALUES (null), ('foo'), ('bar'), (null); 2531CREATE VIEW v1 AS SELECT * FROM t1; 2532SELECT IF(x IS NULL, 'blank', 'not blank') FROM v1 GROUP BY x; 2533IF(x IS NULL, 'blank', 'not blank') 2534blank 2535not blank 2536not blank 2537SELECT IF(x IS NULL, 'blank', 'not blank') AS x FROM t1 GROUP BY x; 2538x 2539blank 2540not blank 2541not blank 2542Warnings: 2543Warning 1052 Column 'x' in group statement is ambiguous 2544SELECT IF(x IS NULL, 'blank', 'not blank') AS x FROM v1; 2545x 2546blank 2547not blank 2548not blank 2549blank 2550SELECT IF(x IS NULL, 'blank', 'not blank') AS y FROM v1 GROUP BY y; 2551y 2552blank 2553not blank 2554SELECT IF(x IS NULL, 'blank', 'not blank') AS x FROM v1 GROUP BY x; 2555x 2556blank 2557not blank 2558not blank 2559Warnings: 2560Warning 1052 Column 'x' in group statement is ambiguous 2561DROP VIEW v1; 2562DROP TABLE t1; 2563drop table if exists t1; 2564drop view if exists v1; 2565create table t1 (id int); 2566create view v1 as select * from t1; 2567drop table t1; 2568show create view v1; 2569drop view v1; 2570// 2571View Create View character_set_client collation_connection 2572v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`t1`.`id` AS `id` from `t1` latin1 latin1_swedish_ci 2573create table t1(f1 int, f2 int); 2574create view v1 as select ta.f1 as a, tb.f1 as b from t1 ta, t1 tb where ta.f1=tb 2575.f1 and ta.f2=tb.f2; 2576insert into t1 values(1,1),(2,2); 2577create view v2 as select * from v1 where a > 1 with local check option; 2578select * from v2; 2579a b 25802 2 2581update v2 set b=3 where a=2; 2582select * from v2; 2583a b 25843 3 2585drop view v2, v1; 2586drop table t1; 2587CREATE TABLE t1 (a int); 2588INSERT INTO t1 VALUES (1), (2); 2589CREATE VIEW v1 AS SELECT SQRT(a) my_sqrt FROM t1; 2590SELECT my_sqrt FROM v1 ORDER BY my_sqrt; 2591my_sqrt 25921 25931.4142135623730951 2594DROP VIEW v1; 2595DROP TABLE t1; 2596CREATE TABLE t1 (id int PRIMARY KEY); 2597CREATE TABLE t2 (id int PRIMARY KEY); 2598INSERT INTO t1 VALUES (1), (3); 2599INSERT INTO t2 VALUES (1), (2), (3); 2600CREATE VIEW v2 AS SELECT * FROM t2; 2601SELECT COUNT(*) FROM t1 LEFT JOIN t2 ON t1.id=t2.id; 2602COUNT(*) 26032 2604SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id; 2605id id 26061 1 26073 3 2608SELECT COUNT(*) FROM t1 LEFT JOIN v2 ON t1.id=v2.id; 2609COUNT(*) 26102 2611DROP VIEW v2; 2612DROP TABLE t1, t2; 2613CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, 2614td date DEFAULT NULL, KEY idx(td)); 2615INSERT INTO t1 VALUES 2616(1, '2005-01-01'), (2, '2005-01-02'), (3, '2005-01-02'), 2617(4, '2005-01-03'), (5, '2005-01-04'), (6, '2005-01-05'), 2618(7, '2005-01-05'), (8, '2005-01-05'), (9, '2005-01-06'); 2619CREATE VIEW v1 AS SELECT * FROM t1; 2620SELECT * FROM t1 WHERE td BETWEEN CAST('2005.01.02' AS DATE) AND CAST('2005.01.04' AS DATE); 2621id td 26222 2005-01-02 26233 2005-01-02 26244 2005-01-03 26255 2005-01-04 2626SELECT * FROM v1 WHERE td BETWEEN CAST('2005.01.02' AS DATE) AND CAST('2005.01.04' AS DATE); 2627id td 26282 2005-01-02 26293 2005-01-02 26304 2005-01-03 26315 2005-01-04 2632DROP VIEW v1; 2633DROP TABLE t1; 2634create table t1 (a int); 2635create view v1 as select * from t1; 2636create view v2 as select * from v1; 2637drop table t1; 2638rename table v2 to t1; 2639select * from v1; 2640ERROR HY000: `test`.`v1` contains view recursion 2641drop view t1, v1; 2642create table t1 (a int); 2643create function f1() returns int 2644begin 2645declare mx int; 2646select max(a) from t1 into mx; 2647return mx; 2648end// 2649create view v1 as select f1() as a; 2650create view v2 as select * from v1; 2651drop table t1; 2652rename table v2 to t1; 2653select * from v1; 2654ERROR HY000: Recursive stored functions and triggers are not allowed. 2655drop function f1; 2656drop view t1, v1; 2657create table t1 (dt datetime); 2658insert into t1 values (20040101000000), (20050101000000), (20060101000000); 2659create view v1 as select convert_tz(dt, 'UTC', 'Europe/Moscow') as ldt from t1; 2660select * from v1; 2661ldt 26622004-01-01 03:00:00 26632005-01-01 03:00:00 26642006-01-01 03:00:00 2665drop view v1; 2666create view v1 as select * from t1 where convert_tz(dt, 'UTC', 'Europe/Moscow') >= 20050101000000; 2667select * from v1; 2668dt 26692005-01-01 00:00:00 26702006-01-01 00:00:00 2671create view v2 as select * from v1 where dt < 20060101000000; 2672select * from v2; 2673dt 26742005-01-01 00:00:00 2675drop view v2; 2676create view v2 as select convert_tz(dt, 'UTC', 'Europe/Moscow') as ldt from v1; 2677select * from v2; 2678ldt 26792005-01-01 03:00:00 26802006-01-01 03:00:00 2681drop view v1, v2; 2682drop table t1; 2683CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, d datetime); 2684CREATE VIEW v1 AS 2685SELECT id, date(d) + INTERVAL TIME_TO_SEC(d) SECOND AS t, COUNT(*) 2686FROM t1 GROUP BY id, t; 2687SHOW CREATE VIEW v1; 2688View Create View character_set_client collation_connection 2689v1 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`,`t` latin1 latin1_swedish_ci 2690SELECT * FROM v1; 2691id t COUNT(*) 2692DROP VIEW v1; 2693DROP TABLE t1; 2694CREATE TABLE t1 (i INT, j BIGINT); 2695INSERT INTO t1 VALUES (1, 2), (2, 2), (3, 2); 2696CREATE VIEW v1 AS SELECT MIN(j) AS j FROM t1; 2697CREATE VIEW v2 AS SELECT MIN(i) FROM t1 WHERE j = ( SELECT * FROM v1 ); 2698SELECT * FROM v2; 2699MIN(i) 27001 2701DROP VIEW v2, v1; 2702DROP TABLE t1; 2703CREATE TABLE t1( 2704fName varchar(25) NOT NULL, 2705lName varchar(25) NOT NULL, 2706DOB date NOT NULL, 2707test_date date NOT NULL, 2708uID int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY); 2709INSERT INTO t1(fName, lName, DOB, test_date) VALUES 2710('Hank', 'Hill', '1964-09-29', '2007-01-01'), 2711('Tom', 'Adams', '1908-02-14', '2007-01-01'), 2712('Homer', 'Simpson', '1968-03-05', '2007-01-01'); 2713CREATE VIEW v1 AS 2714SELECT (year(test_date)-year(DOB)) AS Age 2715FROM t1 HAVING Age < 75; 2716SHOW CREATE VIEW v1; 2717View Create View character_set_client collation_connection 2718v1 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 2719SELECT (year(test_date)-year(DOB)) AS Age FROM t1 HAVING Age < 75; 2720Age 272143 272239 2723SELECT * FROM v1; 2724Age 272543 272639 2727DROP VIEW v1; 2728DROP TABLE t1; 2729CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, a char(6) DEFAULT 'xxx'); 2730INSERT INTO t1(id) VALUES (1), (2), (3), (4); 2731INSERT INTO t1 VALUES (5,'yyy'), (6,'yyy'); 2732SELECT * FROM t1; 2733id a 27341 xxx 27352 xxx 27363 xxx 27374 xxx 27385 yyy 27396 yyy 2740CREATE VIEW v1(a, m) AS SELECT a, MIN(id) FROM t1 GROUP BY a; 2741SELECT * FROM v1; 2742a m 2743xxx 1 2744yyy 5 2745CREATE TABLE t2 SELECT * FROM v1; 2746INSERT INTO t2(m) VALUES (0); 2747SELECT * FROM t2; 2748a m 2749xxx 1 2750yyy 5 2751xxx 0 2752DROP VIEW v1; 2753DROP TABLE t1,t2; 2754CREATE TABLE t1 (id int PRIMARY KEY, e ENUM('a','b') NOT NULL DEFAULT 'b'); 2755INSERT INTO t1(id) VALUES (1), (2), (3); 2756INSERT INTO t1 VALUES (4,'a'); 2757SELECT * FROM t1; 2758id e 27591 b 27602 b 27613 b 27624 a 2763CREATE VIEW v1(m, e) AS SELECT MIN(id), e FROM t1 GROUP BY e; 2764CREATE TABLE t2 SELECT * FROM v1; 2765SELECT * FROM t2; 2766m e 27674 a 27681 b 2769DROP VIEW v1; 2770DROP TABLE t1,t2; 2771CREATE TABLE t1 (a INT NOT NULL, b INT NULL DEFAULT NULL); 2772CREATE VIEW v1 AS SELECT a, b FROM t1; 2773INSERT INTO v1 (b) VALUES (2); 2774Warnings: 2775Warning 1423 Field of view 'test.v1' underlying table doesn't have a default value 2776SET SQL_MODE = STRICT_ALL_TABLES; 2777INSERT INTO v1 (b) VALUES (4); 2778ERROR HY000: Field of view 'test.v1' underlying table doesn't have a default value 2779SET SQL_MODE = ''; 2780SELECT * FROM t1; 2781a b 27820 2 2783DROP VIEW v1; 2784DROP TABLE t1; 2785CREATE TABLE t1 (firstname text, surname text); 2786INSERT INTO t1 VALUES 2787("Bart","Simpson"),("Milhouse","van Houten"),("Montgomery","Burns"); 2788CREATE VIEW v1 AS SELECT CONCAT(firstname," ",surname) AS name FROM t1; 2789SELECT CONCAT(LEFT(name,LENGTH(name)-INSTR(REVERSE(name)," ")), 2790LEFT(name,LENGTH(name)-INSTR(REVERSE(name)," "))) AS f1 2791FROM v1; 2792f1 2793BartBart 2794Milhouse vanMilhouse van 2795MontgomeryMontgomery 2796DROP VIEW v1; 2797DROP TABLE t1; 2798CREATE TABLE t1 (i int, j int); 2799CREATE VIEW v1 AS SELECT COALESCE(i,j) FROM t1; 2800DESCRIBE v1; 2801Field Type Null Key Default Extra 2802COALESCE(i,j) bigint(11) YES NULL 2803CREATE TABLE t2 SELECT COALESCE(i,j) FROM t1; 2804DESCRIBE t2; 2805Field Type Null Key Default Extra 2806COALESCE(i,j) int(11) YES NULL 2807DROP VIEW v1; 2808DROP TABLE t1,t2; 2809CREATE TABLE t1 (s varchar(10)); 2810INSERT INTO t1 VALUES ('yadda'), ('yady'); 2811SELECT TRIM(BOTH 'y' FROM s) FROM t1; 2812TRIM(BOTH 'y' FROM s) 2813adda 2814ad 2815CREATE VIEW v1 AS SELECT TRIM(BOTH 'y' FROM s) FROM t1; 2816SELECT * FROM v1; 2817TRIM(BOTH 'y' FROM s) 2818adda 2819ad 2820DROP VIEW v1; 2821SELECT TRIM(LEADING 'y' FROM s) FROM t1; 2822TRIM(LEADING 'y' FROM s) 2823adda 2824ady 2825CREATE VIEW v1 AS SELECT TRIM(LEADING 'y' FROM s) FROM t1; 2826SELECT * FROM v1; 2827TRIM(LEADING 'y' FROM s) 2828adda 2829ady 2830DROP VIEW v1; 2831SELECT TRIM(TRAILING 'y' FROM s) FROM t1; 2832TRIM(TRAILING 'y' FROM s) 2833yadda 2834yad 2835CREATE VIEW v1 AS SELECT TRIM(TRAILING 'y' FROM s) FROM t1; 2836SELECT * FROM v1; 2837TRIM(TRAILING 'y' FROM s) 2838yadda 2839yad 2840DROP VIEW v1; 2841DROP TABLE t1; 2842CREATE TABLE t1 (x INT, y INT); 2843CREATE ALGORITHM=TEMPTABLE SQL SECURITY INVOKER VIEW v1 AS SELECT x FROM t1; 2844SHOW CREATE VIEW v1; 2845View Create View character_set_client collation_connection 2846v1 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `v1` AS select `t1`.`x` AS `x` from `t1` latin1 latin1_swedish_ci 2847ALTER VIEW v1 AS SELECT x, y FROM t1; 2848SHOW CREATE VIEW v1; 2849View Create View character_set_client collation_connection 2850v1 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 2851DROP VIEW v1; 2852DROP TABLE t1; 2853CREATE TABLE t1 (s1 char); 2854INSERT INTO t1 VALUES ('Z'); 2855CREATE VIEW v1 AS SELECT s1 collate latin1_german1_ci AS col FROM t1; 2856CREATE VIEW v2 (col) AS SELECT s1 collate latin1_german1_ci FROM t1; 2857INSERT INTO v1 (col) VALUES ('b'); 2858INSERT INTO v2 (col) VALUES ('c'); 2859SELECT s1 FROM t1; 2860s1 2861Z 2862b 2863c 2864DROP VIEW v1, v2; 2865DROP TABLE t1; 2866CREATE TABLE t1 (id INT); 2867CREATE VIEW v1 AS SELECT id FROM t1; 2868SHOW TABLES; 2869Tables_in_test 2870t1 2871v1 2872DROP VIEW v2,v1; 2873ERROR 42S02: Unknown table 'test.v2' 2874SHOW TABLES; 2875Tables_in_test 2876t1 2877CREATE VIEW v1 AS SELECT id FROM t1; 2878DROP VIEW t1,v1; 2879ERROR HY000: 'test.t1' is not VIEW 2880SHOW TABLES; 2881Tables_in_test 2882t1 2883DROP TABLE t1; 2884DROP VIEW IF EXISTS v1; 2885CREATE DATABASE bug21261DB; 2886USE bug21261DB; 2887CREATE TABLE t1 (x INT); 2888CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT x FROM t1; 2889GRANT INSERT, UPDATE ON v1 TO 'user21261'@'localhost'; 2890GRANT INSERT, UPDATE ON t1 TO 'user21261'@'localhost'; 2891CREATE TABLE t2 (y INT); 2892GRANT SELECT ON t2 TO 'user21261'@'localhost'; 2893INSERT INTO v1 (x) VALUES (5); 2894UPDATE v1 SET x=1; 2895GRANT SELECT ON v1 TO 'user21261'@'localhost'; 2896GRANT SELECT ON t1 TO 'user21261'@'localhost'; 2897UPDATE v1,t2 SET x=1 WHERE x=y; 2898SELECT * FROM t1; 2899x 29001 2901REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user21261'@'localhost'; 2902DROP USER 'user21261'@'localhost'; 2903DROP VIEW v1; 2904DROP TABLE t1; 2905DROP DATABASE bug21261DB; 2906USE test; 2907create table t1 (f1 datetime); 2908create view v1 as select * from t1 where f1 between now() and now() + interval 1 minute; 2909show create view v1; 2910View Create View character_set_client collation_connection 2911v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f1` AS `f1` from `t1` where (`t1`.`f1` between now() and (now() + interval 1 minute)) latin1 latin1_swedish_ci 2912drop view v1; 2913drop table t1; 2914DROP TABLE IF EXISTS t1; 2915DROP VIEW IF EXISTS v1; 2916DROP VIEW IF EXISTS v2; 2917CREATE TABLE t1(a INT, b INT); 2918CREATE DEFINER=1234567890abcdefGHIKL@localhost 2919VIEW v1 AS SELECT a FROM t1; 2920ERROR HY000: String '1234567890abcdefGHIKL' is too long for user name (should be no longer than 16) 2921CREATE DEFINER=some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY 2922VIEW v2 AS SELECT b FROM t1; 2923ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY' is too long for host name (should be no longer than 60) 2924DROP TABLE t1; 2925DROP FUNCTION IF EXISTS f1; 2926DROP FUNCTION IF EXISTS f2; 2927DROP VIEW IF EXISTS v1, v2; 2928DROP TABLE IF EXISTS t1; 2929CREATE TABLE t1 (i INT); 2930CREATE VIEW v1 AS SELECT * FROM t1; 2931CREATE FUNCTION f1() RETURNS INT 2932BEGIN 2933INSERT INTO v1 VALUES (0); 2934RETURN 0; 2935END | 2936SELECT f1(); 2937f1() 29380 2939CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t1; 2940CREATE FUNCTION f2() RETURNS INT 2941BEGIN 2942INSERT INTO v2 VALUES (0); 2943RETURN 0; 2944END | 2945SELECT f2(); 2946ERROR HY000: The target table v2 of the INSERT is not insertable-into 2947DROP FUNCTION f1; 2948DROP FUNCTION f2; 2949DROP VIEW v1, v2; 2950DROP TABLE t1; 2951CREATE TABLE t1 (s1 int); 2952CREATE VIEW v1 AS SELECT * FROM t1; 2953EXPLAIN SELECT * FROM t1; 2954id select_type table type possible_keys key key_len ref rows Extra 29551 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found 2956EXPLAIN SELECT * FROM v1; 2957id select_type table type possible_keys key key_len ref rows Extra 29581 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found 2959INSERT INTO t1 VALUES (1), (3), (2); 2960EXPLAIN SELECT * FROM t1 t WHERE t.s1+1 < (SELECT MAX(t1.s1) FROM t1); 2961id select_type table type possible_keys key key_len ref rows Extra 29621 PRIMARY t ALL NULL NULL NULL NULL 3 Using where 29632 SUBQUERY t1 ALL NULL NULL NULL NULL 3 NULL 2964EXPLAIN SELECT * FROM v1 t WHERE t.s1+1 < (SELECT MAX(t1.s1) FROM t1); 2965id select_type table type possible_keys key key_len ref rows Extra 29661 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where 29672 SUBQUERY t1 ALL NULL NULL NULL NULL 3 NULL 2968DROP VIEW v1; 2969DROP TABLE t1; 2970create table t1 (s1 int); 2971create view v1 as select s1 as a, s1 as b from t1; 2972insert into v1 values (1,1); 2973ERROR HY000: The target table v1 of the INSERT is not insertable-into 2974update v1 set a = 5; 2975drop view v1; 2976drop table t1; 2977CREATE TABLE t1(pk int PRIMARY KEY); 2978CREATE TABLE t2(pk int PRIMARY KEY, fk int, ver int, org int); 2979CREATE ALGORITHM=MERGE VIEW v1 AS 2980SELECT t1.* 2981FROM t1 JOIN t2 2982ON t2.fk = t1.pk AND 2983t2.ver = (SELECT MAX(t.ver) FROM t2 t WHERE t.org = t2.org); 2984SHOW WARNINGS; 2985Level Code Message 2986SHOW CREATE VIEW v1; 2987View Create View character_set_client collation_connection 2988v1 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 2989DROP VIEW v1; 2990DROP TABLE t1, t2; 2991DROP FUNCTION IF EXISTS f1; 2992DROP VIEW IF EXISTS v1; 2993DROP TABLE IF EXISTS t1; 2994CREATE TABLE t1 (i INT); 2995INSERT INTO t1 VALUES (1); 2996CREATE VIEW v1 AS SELECT MAX(i) FROM t1; 2997CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW 2998SET NEW.i = (SELECT * FROM v1) + 1; 2999INSERT INTO t1 VALUES (1); 3000CREATE FUNCTION f1() RETURNS INT RETURN (SELECT * FROM v1); 3001UPDATE t1 SET i= f1(); 3002DROP FUNCTION f1; 3003DROP VIEW v1; 3004DROP TABLE t1; 3005CREATE TABLE t1(id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, val INT UNSIGNED NOT NULL); 3006CREATE VIEW v1 AS SELECT id, val FROM t1 WHERE val >= 1 AND val <= 5 WITH CHECK OPTION; 3007INSERT INTO v1 (val) VALUES (2); 3008INSERT INTO v1 (val) VALUES (4); 3009INSERT INTO v1 (val) VALUES (6); 3010ERROR HY000: CHECK OPTION failed 'test.v1' 3011UPDATE v1 SET val=6 WHERE id=2; 3012ERROR HY000: CHECK OPTION failed 'test.v1' 3013DROP VIEW v1; 3014DROP TABLE t1; 3015DROP VIEW IF EXISTS v1, v2; 3016DROP TABLE IF EXISTS t1; 3017CREATE TABLE t1 (i INT AUTO_INCREMENT PRIMARY KEY, j INT); 3018CREATE VIEW v1 AS SELECT j FROM t1; 3019CREATE VIEW v2 AS SELECT * FROM t1; 3020INSERT INTO t1 (j) VALUES (1); 3021SELECT LAST_INSERT_ID(); 3022LAST_INSERT_ID() 30231 3024INSERT INTO v1 (j) VALUES (2); 3025# LAST_INSERT_ID() should not change. 3026SELECT LAST_INSERT_ID(); 3027LAST_INSERT_ID() 30281 3029INSERT INTO v2 (j) VALUES (3); 3030# LAST_INSERT_ID() should be updated. 3031SELECT LAST_INSERT_ID(); 3032LAST_INSERT_ID() 30333 3034INSERT INTO v1 (j) SELECT j FROM t1; 3035# LAST_INSERT_ID() should not change. 3036SELECT LAST_INSERT_ID(); 3037LAST_INSERT_ID() 30383 3039SELECT * FROM t1; 3040i j 30411 1 30422 2 30433 3 30444 1 30455 2 30466 3 3047DROP VIEW v1, v2; 3048DROP TABLE t1; 3049CREATE VIEW v AS SELECT !0 * 5 AS x FROM DUAL; 3050SHOW CREATE VIEW v; 3051View Create View character_set_client collation_connection 3052v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select ((not(0)) * 5) AS `x` latin1 latin1_swedish_ci 3053SELECT !0 * 5 AS x FROM DUAL; 3054x 30555 3056SELECT * FROM v; 3057x 30585 3059DROP VIEW v; 3060DROP VIEW IF EXISTS v1; 3061CREATE VIEW v1 AS SELECT 'The\ZEnd'; 3062SELECT * FROM v1; 3063TheEnd 3064TheEnd 3065SHOW CREATE VIEW v1; 3066View Create View character_set_client collation_connection 3067v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 'The\ZEnd' AS `TheEnd` latin1 latin1_swedish_ci 3068DROP VIEW v1; 3069CREATE TABLE t1 (mydate DATETIME); 3070INSERT INTO t1 VALUES 3071('2007-01-01'), ('2007-01-02'), ('2007-01-30'), ('2007-01-31'); 3072CREATE VIEW v1 AS SELECT mydate from t1; 3073SELECT * FROM t1 WHERE mydate BETWEEN '2007-01-01' AND '2007-01-31'; 3074mydate 30752007-01-01 00:00:00 30762007-01-02 00:00:00 30772007-01-30 00:00:00 30782007-01-31 00:00:00 3079SELECT * FROM v1 WHERE mydate BETWEEN '2007-01-01' AND '2007-01-31'; 3080mydate 30812007-01-01 00:00:00 30822007-01-02 00:00:00 30832007-01-30 00:00:00 30842007-01-31 00:00:00 3085DROP VIEW v1; 3086DROP TABLE t1; 3087CREATE TABLE t1 (a int); 3088CREATE TABLE t2 (b int); 3089INSERT INTO t1 VALUES (1), (2); 3090INSERT INTO t2 VALUES (1), (2); 3091CREATE VIEW v1 AS 3092SELECT t2.b FROM t1,t2 WHERE t1.a = t2.b WITH CHECK OPTION; 3093SELECT * FROM v1; 3094b 30951 30962 3097UPDATE v1 SET b=3; 3098ERROR HY000: CHECK OPTION failed 'test.v1' 3099SELECT * FROM v1; 3100b 31011 31022 3103SELECT * FROM t1; 3104a 31051 31062 3107SELECT * FROM t2; 3108b 31091 31102 3111DROP VIEW v1; 3112DROP TABLE t1,t2; 3113create table t1(f1 int, f2 int); 3114insert into t1 values(1,2),(1,3),(1,1),(2,3),(2,1),(2,2); 3115select * from t1; 3116f1 f2 31171 2 31181 3 31191 1 31202 3 31212 1 31222 2 3123create view v1 as select * from t1 order by f2; 3124select * from v1; 3125f1 f2 31261 1 31272 1 31281 2 31292 2 31301 3 31312 3 3132explain extended select * from v1; 3133id select_type table type possible_keys key key_len ref rows filtered Extra 31341 SIMPLE t1 ALL NULL NULL NULL NULL 6 100.00 Using filesort 3135Warnings: 3136Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` order by `test`.`t1`.`f2` 3137select * from v1 order by f1; 3138f1 f2 31391 1 31401 2 31411 3 31422 1 31432 2 31442 3 3145explain extended select * from v1 order by f1; 3146id select_type table type possible_keys key key_len ref rows filtered Extra 31471 SIMPLE t1 ALL NULL NULL NULL NULL 6 100.00 Using filesort 3148Warnings: 3149Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` order by `test`.`t1`.`f1`,`test`.`t1`.`f2` 3150drop view v1; 3151drop table t1; 3152CREATE TABLE t1 ( 3153id int(11) NOT NULL PRIMARY KEY, 3154country varchar(32), 3155code int(11) default NULL 3156); 3157INSERT INTO t1 VALUES 3158(1,'ITALY',100),(2,'ITALY',200),(3,'FRANCE',100), (4,'ITALY',100); 3159CREATE VIEW v1 AS SELECT * FROM t1; 3160SELECT code, COUNT(DISTINCT country) FROM t1 GROUP BY code ORDER BY MAX(id); 3161code COUNT(DISTINCT country) 3162200 1 3163100 2 3164SELECT code, COUNT(DISTINCT country) FROM v1 GROUP BY code ORDER BY MAX(id); 3165code COUNT(DISTINCT country) 3166200 1 3167100 2 3168DROP VIEW v1; 3169DROP TABLE t1; 3170DROP VIEW IF EXISTS v1; 3171SELECT * FROM (SELECT 1) AS t; 31721 31731 3174CREATE VIEW v1 AS SELECT * FROM (SELECT 1) AS t; 3175ERROR HY000: View's SELECT contains a subquery in the FROM clause 3176# Previously the following would fail. 3177SELECT * FROM (SELECT 1) AS t; 31781 31791 3180drop view if exists view_24532_a; 3181drop view if exists view_24532_b; 3182drop table if exists table_24532; 3183create table table_24532 ( 3184a int, 3185b bigint, 3186c int(4), 3187d bigint(48) 3188); 3189create view view_24532_a as 3190select 3191a IS TRUE, 3192a IS NOT TRUE, 3193a IS FALSE, 3194a IS NOT FALSE, 3195a IS UNKNOWN, 3196a IS NOT UNKNOWN, 3197a is NULL, 3198a IS NOT NULL, 3199ISNULL(a), 3200b IS TRUE, 3201b IS NOT TRUE, 3202b IS FALSE, 3203b IS NOT FALSE, 3204b IS UNKNOWN, 3205b IS NOT UNKNOWN, 3206b is NULL, 3207b IS NOT NULL, 3208ISNULL(b), 3209c IS TRUE, 3210c IS NOT TRUE, 3211c IS FALSE, 3212c IS NOT FALSE, 3213c IS UNKNOWN, 3214c IS NOT UNKNOWN, 3215c is NULL, 3216c IS NOT NULL, 3217ISNULL(c), 3218d IS TRUE, 3219d IS NOT TRUE, 3220d IS FALSE, 3221d IS NOT FALSE, 3222d IS UNKNOWN, 3223d IS NOT UNKNOWN, 3224d is NULL, 3225d IS NOT NULL, 3226ISNULL(d) 3227from table_24532; 3228describe view_24532_a; 3229Field Type Null Key Default Extra 3230a IS TRUE int(1) NO 0 3231a IS NOT TRUE int(1) NO 0 3232a IS FALSE int(1) NO 0 3233a IS NOT FALSE int(1) NO 0 3234a IS UNKNOWN int(1) NO 0 3235a IS NOT UNKNOWN int(1) NO 0 3236a is NULL int(1) NO 0 3237a IS NOT NULL int(1) NO 0 3238ISNULL(a) int(1) NO 0 3239b IS TRUE int(1) NO 0 3240b IS NOT TRUE int(1) NO 0 3241b IS FALSE int(1) NO 0 3242b IS NOT FALSE int(1) NO 0 3243b IS UNKNOWN int(1) NO 0 3244b IS NOT UNKNOWN int(1) NO 0 3245b is NULL int(1) NO 0 3246b IS NOT NULL int(1) NO 0 3247ISNULL(b) int(1) NO 0 3248c IS TRUE int(1) NO 0 3249c IS NOT TRUE int(1) NO 0 3250c IS FALSE int(1) NO 0 3251c IS NOT FALSE int(1) NO 0 3252c IS UNKNOWN int(1) NO 0 3253c IS NOT UNKNOWN int(1) NO 0 3254c is NULL int(1) NO 0 3255c IS NOT NULL int(1) NO 0 3256ISNULL(c) int(1) NO 0 3257d IS TRUE int(1) NO 0 3258d IS NOT TRUE int(1) NO 0 3259d IS FALSE int(1) NO 0 3260d IS NOT FALSE int(1) NO 0 3261d IS UNKNOWN int(1) NO 0 3262d IS NOT UNKNOWN int(1) NO 0 3263d is NULL int(1) NO 0 3264d IS NOT NULL int(1) NO 0 3265ISNULL(d) int(1) NO 0 3266create view view_24532_b as 3267select 3268a IS TRUE, 3269if(ifnull(a, 0), 1, 0) as old_istrue, 3270a IS NOT TRUE, 3271if(ifnull(a, 0), 0, 1) as old_isnottrue, 3272a IS FALSE, 3273if(ifnull(a, 1), 0, 1) as old_isfalse, 3274a IS NOT FALSE, 3275if(ifnull(a, 1), 1, 0) as old_isnotfalse 3276from table_24532; 3277describe view_24532_b; 3278Field Type Null Key Default Extra 3279a IS TRUE int(1) NO 0 3280old_istrue int(1) NO 0 3281a IS NOT TRUE int(1) NO 0 3282old_isnottrue int(1) NO 0 3283a IS FALSE int(1) NO 0 3284old_isfalse int(1) NO 0 3285a IS NOT FALSE int(1) NO 0 3286old_isnotfalse int(1) NO 0 3287show create view view_24532_b; 3288View Create View character_set_client collation_connection 3289view_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 3290insert into table_24532 values (0, 0, 0, 0); 3291select * from view_24532_b; 3292a IS TRUE old_istrue a IS NOT TRUE old_isnottrue a IS FALSE old_isfalse a IS NOT FALSE old_isnotfalse 32930 0 1 1 1 1 0 0 3294update table_24532 set a=1; 3295select * from view_24532_b; 3296a IS TRUE old_istrue a IS NOT TRUE old_isnottrue a IS FALSE old_isfalse a IS NOT FALSE old_isnotfalse 32971 1 0 0 0 0 1 1 3298update table_24532 set a=NULL; 3299select * from view_24532_b; 3300a IS TRUE old_istrue a IS NOT TRUE old_isnottrue a IS FALSE old_isfalse a IS NOT FALSE old_isnotfalse 33010 0 1 1 0 0 1 1 3302drop view view_24532_a; 3303drop view view_24532_b; 3304drop table table_24532; 3305CREATE TABLE t1 ( 3306lid int NOT NULL PRIMARY KEY, 3307name char(10) NOT NULL 3308); 3309INSERT INTO t1 (lid, name) VALUES 3310(1, 'YES'), (2, 'NO'); 3311CREATE TABLE t2 ( 3312id int NOT NULL PRIMARY KEY, 3313gid int NOT NULL, 3314lid int NOT NULL, 3315dt date 3316); 3317INSERT INTO t2 (id, gid, lid, dt) VALUES 3318(1, 1, 1, '2007-01-01'),(2, 1, 2, '2007-01-02'), 3319(3, 2, 2, '2007-02-01'),(4, 2, 1, '2007-02-02'); 3320SELECT DISTINCT t2.gid AS lgid, 3321(SELECT t1.name FROM t1, t2 3322WHERE t1.lid = t2.lid AND t2.gid = lgid 3323ORDER BY t2.dt DESC LIMIT 1 3324) as clid 3325FROM t2; 3326lgid clid 33271 NO 33282 YES 3329CREATE VIEW v1 AS 3330SELECT DISTINCT t2.gid AS lgid, 3331(SELECT t1.name FROM t1, t2 3332WHERE t1.lid = t2.lid AND t2.gid = lgid 3333ORDER BY t2.dt DESC LIMIT 1 3334) as clid 3335FROM t2; 3336SELECT * FROM v1; 3337lgid clid 33381 NO 33392 YES 3340DROP VIEW v1; 3341DROP table t1,t2; 3342CREATE TABLE t1 (a INT); 3343INSERT INTO t1 VALUES (1),(2),(3); 3344CREATE VIEW v1 AS SELECT a FROM t1 ORDER BY a; 3345SELECT * FROM t1 UNION SELECT * FROM v1; 3346a 33471 33482 33493 3350EXPLAIN SELECT * FROM t1 UNION SELECT * FROM v1; 3351id select_type table type possible_keys key key_len ref rows Extra 33521 PRIMARY t1 ALL NULL NULL NULL NULL 3 NULL 33532 UNION t1 ALL NULL NULL NULL NULL 3 NULL 3354NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL Using temporary 3355SELECT * FROM v1 UNION SELECT * FROM t1; 3356a 33571 33582 33593 3360EXPLAIN SELECT * FROM v1 UNION SELECT * FROM t1; 3361id select_type table type possible_keys key key_len ref rows Extra 33621 PRIMARY t1 ALL NULL NULL NULL NULL 3 NULL 33632 UNION t1 ALL NULL NULL NULL NULL 3 NULL 3364NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL Using temporary 3365SELECT * FROM t1 UNION SELECT * FROM v1 ORDER BY a; 3366a 33671 33682 33693 3370EXPLAIN SELECT * FROM t1 UNION SELECT * FROM v1 ORDER BY a; 3371id select_type table type possible_keys key key_len ref rows Extra 33721 PRIMARY t1 ALL NULL NULL NULL NULL 3 NULL 33732 UNION t1 ALL NULL NULL NULL NULL 3 NULL 3374NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL Using temporary; Using filesort 3375DROP VIEW v1; 3376DROP TABLE t1; 3377CREATE VIEW v1 AS SELECT CAST( 1.23456789 AS DECIMAL( 7,5 ) ) AS col; 3378SELECT * FROM v1; 3379col 33801.23457 3381DESCRIBE v1; 3382Field Type Null Key Default Extra 3383col decimal(7,5) NO 0.00000 3384DROP VIEW v1; 3385CREATE VIEW v1 AS SELECT CAST(1.23456789 AS DECIMAL(8,0)) AS col; 3386SHOW CREATE VIEW v1; 3387View Create View character_set_client collation_connection 3388v1 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 3389DROP VIEW v1; 3390CREATE TABLE t1 (a INT); 3391CREATE TABLE t2 (b INT, c INT DEFAULT 0); 3392INSERT INTO t1 (a) VALUES (1), (2); 3393INSERT INTO t2 (b) VALUES (1), (2); 3394CREATE VIEW v1 AS SELECT t2.b,t2.c FROM t1, t2 3395WHERE t1.a=t2.b AND t2.b < 3 WITH CHECK OPTION; 3396SELECT * FROM v1; 3397b c 33981 0 33992 0 3400UPDATE v1 SET c=1 WHERE b=1; 3401SELECT * FROM v1; 3402b c 34031 1 34042 0 3405DROP VIEW v1; 3406DROP TABLE t1,t2; 3407CREATE TABLE t1 (id int); 3408CREATE TABLE t2 (id int, c int DEFAULT 0); 3409INSERT INTO t1 (id) VALUES (1); 3410INSERT INTO t2 (id) VALUES (1); 3411CREATE VIEW v1 AS 3412SELECT t2.c FROM t1, t2 3413WHERE t1.id=t2.id AND 1 IN (SELECT id FROM t1) WITH CHECK OPTION; 3414UPDATE v1 SET c=1; 3415DROP VIEW v1; 3416DROP TABLE t1,t2; 3417CREATE TABLE t1 (a1 INT, c INT DEFAULT 0); 3418CREATE TABLE t2 (a2 INT); 3419CREATE TABLE t3 (a3 INT); 3420CREATE TABLE t4 (a4 INT); 3421INSERT INTO t1 (a1) VALUES (1),(2); 3422INSERT INTO t2 (a2) VALUES (1),(2); 3423INSERT INTO t3 (a3) VALUES (1),(2); 3424INSERT INTO t4 (a4) VALUES (1),(2); 3425CREATE VIEW v1 AS 3426SELECT t1.a1, t1.c FROM t1 JOIN t2 ON t1.a1=t2.a2 AND t1.c < 3 3427WITH CHECK OPTION; 3428SELECT * FROM v1; 3429a1 c 34301 0 34312 0 3432UPDATE v1 SET c=3; 3433ERROR HY000: CHECK OPTION failed 'test.v1' 3434PREPARE t FROM 'UPDATE v1 SET c=3'; 3435EXECUTE t; 3436ERROR HY000: CHECK OPTION failed 'test.v1' 3437EXECUTE t; 3438ERROR HY000: CHECK OPTION failed 'test.v1' 3439INSERT INTO v1(a1, c) VALUES (3, 3); 3440ERROR HY000: CHECK OPTION failed 'test.v1' 3441UPDATE v1 SET c=1 WHERE a1=1; 3442SELECT * FROM v1; 3443a1 c 34441 1 34452 0 3446SELECT * FROM t1; 3447a1 c 34481 1 34492 0 3450CREATE VIEW v2 AS SELECT t1.a1, t1.c 3451FROM (t1 JOIN t2 ON t1.a1=t2.a2 AND t1.c < 3) 3452JOIN (t3 JOIN t4 ON t3.a3=t4.a4) 3453ON t2.a2=t3.a3 WITH CHECK OPTION; 3454SELECT * FROM v2; 3455a1 c 34561 1 34572 0 3458UPDATE v2 SET c=3; 3459ERROR HY000: CHECK OPTION failed 'test.v2' 3460PREPARE t FROM 'UPDATE v2 SET c=3'; 3461EXECUTE t; 3462ERROR HY000: CHECK OPTION failed 'test.v2' 3463EXECUTE t; 3464ERROR HY000: CHECK OPTION failed 'test.v2' 3465INSERT INTO v2(a1, c) VALUES (3, 3); 3466ERROR HY000: CHECK OPTION failed 'test.v2' 3467UPDATE v2 SET c=2 WHERE a1=1; 3468SELECT * FROM v2; 3469a1 c 34701 2 34712 0 3472SELECT * FROM t1; 3473a1 c 34741 2 34752 0 3476DROP VIEW v1,v2; 3477DROP TABLE t1,t2,t3,t4; 3478CREATE TABLE t1 (a int, b int); 3479INSERT INTO t1 VALUES (1,2), (2,2), (1,3), (1,2); 3480CREATE VIEW v1 AS SELECT a, b+1 as b FROM t1; 3481SELECT b, SUM(a) FROM v1 WHERE b=3 GROUP BY b; 3482b SUM(a) 34833 4 3484EXPLAIN SELECT b, SUM(a) FROM v1 WHERE b=3 GROUP BY b; 3485id select_type table type possible_keys key key_len ref rows Extra 34861 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where 3487SELECT a, SUM(b) FROM v1 WHERE b=3 GROUP BY a; 3488a SUM(b) 34891 6 34902 3 3491EXPLAIN SELECT a, SUM(b) FROM v1 WHERE b=3 GROUP BY a; 3492id select_type table type possible_keys key key_len ref rows Extra 34931 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where; Using temporary; Using filesort 3494SELECT a, SUM(b) FROM v1 WHERE a=1 GROUP BY a; 3495a SUM(b) 34961 10 3497EXPLAIN SELECT a, SUM(b) FROM v1 WHERE a=1 GROUP BY a; 3498id select_type table type possible_keys key key_len ref rows Extra 34991 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where 3500DROP VIEW v1; 3501DROP TABLE t1; 3502CREATE TABLE t1 ( 3503person_id int NOT NULL PRIMARY KEY, 3504username varchar(40) default NULL, 3505status_flg char(1) NOT NULL default 'A' 3506); 3507CREATE TABLE t2 ( 3508person_role_id int NOT NULL auto_increment PRIMARY KEY, 3509role_id int NOT NULL, 3510person_id int NOT NULL, 3511INDEX idx_person_id (person_id), 3512INDEX idx_role_id (role_id) 3513); 3514CREATE TABLE t3 ( 3515role_id int NOT NULL auto_increment PRIMARY KEY, 3516role_name varchar(100) default NULL, 3517app_name varchar(40) NOT NULL, 3518INDEX idx_app_name(app_name) 3519); 3520CREATE VIEW v1 AS 3521SELECT profile.person_id AS person_id 3522FROM t1 profile, t2 userrole, t3 role 3523WHERE userrole.person_id = profile.person_id AND 3524role.role_id = userrole.role_id AND 3525profile.status_flg = 'A' 3526 ORDER BY profile.person_id,role.app_name,role.role_name; 3527INSERT INTO t1 VALUES 3528(6,'Sw','A'), (-1136332546,'ols','e'), (0,' *\n','0'), 3529(-717462680,'ENTS Ta','0'), (-904346964,'ndard SQL\n','0'); 3530INSERT INTO t2 VALUES 3531(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); 3532INSERT INTO t3 VALUES 3533(1,'NUCANS_APP_USER','NUCANSAPP'),(2,'NUCANS_TRGAPP_USER','NUCANSAPP'), 3534(3,'IA_INTAKE_COORDINATOR','IACANS'),(4,'IA_SCREENER','IACANS'), 3535(5,'IA_SUPERVISOR','IACANS'),(6,'IA_READONLY','IACANS'), 3536(7,'SOC_USER','SOCCANS'),(8,'CAYIT_USER','CAYITCANS'), 3537(9,'RTOS_DCFSPOS_SUPERVISOR','RTOS'); 3538EXPLAIN SELECT t.person_id AS a, t.person_id AS b FROM v1 t WHERE t.person_id=6; 3539id select_type table type possible_keys key key_len ref rows Extra 35401 SIMPLE profile const PRIMARY PRIMARY 4 const 1 Using temporary; Using filesort 35411 SIMPLE userrole ref idx_person_id,idx_role_id idx_person_id 4 const 2 NULL 35421 SIMPLE role eq_ref PRIMARY PRIMARY 4 test.userrole.role_id 1 NULL 3543SELECT t.person_id AS a, t.person_id AS b FROM v1 t WHERE t.person_id=6; 3544a b 35456 6 35466 6 3547DROP VIEW v1; 3548DROP TABLE t1,t2,t3; 3549create table t1 (i int); 3550insert into t1 values (1), (2), (1), (3), (2), (4); 3551create view v1 as select distinct i from t1; 3552select * from v1; 3553i 35541 35552 35563 35574 3558select table_name, is_updatable from information_schema.views 3559where table_name = 'v1'; 3560table_name is_updatable 3561v1 NO 3562drop view v1; 3563drop table t1; 3564CREATE TABLE t1 (a INT); 3565INSERT INTO t1 VALUES (1),(2); 3566CREATE VIEW v1 AS SELECT * FROM t1; 3567SELECT * FROM v1 USE KEY(non_existant); 3568ERROR 42000: Key 'non_existant' doesn't exist in table 'v1' 3569SELECT * FROM v1 FORCE KEY(non_existant); 3570ERROR 42000: Key 'non_existant' doesn't exist in table 'v1' 3571SELECT * FROM v1 IGNORE KEY(non_existant); 3572ERROR 42000: Key 'non_existant' doesn't exist in table 'v1' 3573DROP VIEW v1; 3574DROP TABLE t1; 3575CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT, b INT NOT NULL DEFAULT 0, 3576PRIMARY KEY(a), KEY (b)); 3577INSERT INTO t1 VALUES (),(),(),(),(),(),(),(),(),(),(),(),(),(),(); 3578CREATE VIEW v1 AS SELECT * FROM t1 FORCE KEY (PRIMARY,b) ORDER BY a; 3579SHOW CREATE VIEW v1; 3580View Create View character_set_client collation_connection 3581v1 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 3582EXPLAIN SELECT * FROM v1; 3583id select_type table type possible_keys key key_len ref rows Extra 35841 SIMPLE t1 index NULL PRIMARY 4 NULL 15 NULL 3585CREATE VIEW v2 AS SELECT * FROM t1 USE KEY () ORDER BY a; 3586SHOW CREATE VIEW v2; 3587View Create View character_set_client collation_connection 3588v2 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 3589EXPLAIN SELECT * FROM v2; 3590id select_type table type possible_keys key key_len ref rows Extra 35911 SIMPLE t1 ALL NULL NULL NULL NULL 15 Using filesort 3592CREATE VIEW v3 AS SELECT * FROM t1 IGNORE KEY (b) ORDER BY a; 3593SHOW CREATE VIEW v3; 3594View Create View character_set_client collation_connection 3595v3 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 3596EXPLAIN SELECT * FROM v3; 3597id select_type table type possible_keys key key_len ref rows Extra 35981 SIMPLE t1 ALL NULL NULL NULL NULL 15 Using filesort 3599DROP VIEW v1; 3600DROP VIEW v2; 3601DROP VIEW v3; 3602DROP TABLE t1; 3603# 3604# Bug#29477 Not all fields of the target table were checked to have 3605# a default value when inserting into a view. 3606# 3607create table t1(f1 int, f2 int not null); 3608create view v1 as select f1 from t1; 3609insert into v1 values(1); 3610Warnings: 3611Warning 1423 Field of view 'test.v1' underlying table doesn't have a default value 3612set @old_mode=@@sql_mode; 3613set @@sql_mode=traditional; 3614insert into v1 values(1); 3615ERROR HY000: Field of view 'test.v1' underlying table doesn't have a default value 3616set @@sql_mode=@old_mode; 3617drop view v1; 3618drop table t1; 3619create table t1 (a int, key(a)); 3620create table t2 (c int); 3621create view v1 as select a b from t1; 3622create view v2 as select 1 a from t2, v1 where c in 3623(select 1 from t1 where b = a); 3624insert into t1 values (1), (1); 3625insert into t2 values (1), (1); 3626prepare stmt from "select * from v2 where a = 1"; 3627execute stmt; 3628a 36291 36301 36311 36321 3633drop view v1, v2; 3634drop table t1, t2; 3635CREATE TABLE t1 (a INT); 3636CREATE VIEW v1 AS SELECT p.a AS a FROM t1 p, t1 q; 3637INSERT INTO t1 VALUES (1), (1); 3638SELECT MAX(a), COUNT(DISTINCT a) FROM v1 GROUP BY a; 3639MAX(a) COUNT(DISTINCT a) 36401 1 3641DROP VIEW v1; 3642DROP TABLE t1; 3643# ----------------------------------------------------------------- 3644# -- Bug#34337 Server crash when Altering a view using a table name. 3645# ----------------------------------------------------------------- 3646 3647DROP TABLE IF EXISTS t1; 3648 3649CREATE TABLE t1(c1 INT); 3650 3651SELECT * FROM t1; 3652c1 3653ALTER ALGORITHM=TEMPTABLE SQL SECURITY INVOKER VIEW t1 (c2) AS SELECT (1); 3654ERROR HY000: 'test.t1' is not VIEW 3655 3656DROP TABLE t1; 3657 3658# -- End of test case for Bug#34337. 3659 3660# ----------------------------------------------------------------- 3661# -- Bug#35193 VIEW query is rewritten without "FROM DUAL", 3662# -- causing syntax error 3663# ----------------------------------------------------------------- 3664 3665CREATE VIEW v1 AS SELECT 1 FROM DUAL WHERE 1; 3666 3667SELECT * FROM v1; 36681 36691 3670SHOW CREATE TABLE v1; 3671View Create View character_set_client collation_connection 3672v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `1` from DUAL where 1 latin1 latin1_swedish_ci 3673 3674DROP VIEW v1; 3675 3676# -- End of test case for Bug#35193. 3677 3678CREATE VIEW v1 AS SELECT 1; 3679DROP VIEW v1; 3680CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT, INDEX (c2)); 3681INSERT INTO t1 VALUES (1,1), (2,2), (3,3); 3682SELECT * FROM t1 USE INDEX (PRIMARY) WHERE c1=2; 3683c1 c2 36842 2 3685SELECT * FROM t1 USE INDEX (c2) WHERE c2=2; 3686c1 c2 36872 2 3688CREATE VIEW v1 AS SELECT c1, c2 FROM t1; 3689SHOW INDEX FROM v1; 3690Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 3691SELECT * FROM v1 USE INDEX (PRIMARY) WHERE c1=2; 3692ERROR 42000: Key 'PRIMARY' doesn't exist in table 'v1' 3693SELECT * FROM v1 FORCE INDEX (PRIMARY) WHERE c1=2; 3694ERROR 42000: Key 'PRIMARY' doesn't exist in table 'v1' 3695SELECT * FROM v1 IGNORE INDEX (PRIMARY) WHERE c1=2; 3696ERROR 42000: Key 'PRIMARY' doesn't exist in table 'v1' 3697SELECT * FROM v1 USE INDEX (c2) WHERE c2=2; 3698ERROR 42000: Key 'c2' doesn't exist in table 'v1' 3699SELECT * FROM v1 FORCE INDEX (c2) WHERE c2=2; 3700ERROR 42000: Key 'c2' doesn't exist in table 'v1' 3701SELECT * FROM v1 IGNORE INDEX (c2) WHERE c2=2; 3702ERROR 42000: Key 'c2' doesn't exist in table 'v1' 3703DROP VIEW v1; 3704DROP TABLE t1; 3705# 3706# Bug #45806 crash when replacing into a view with a join! 3707# 3708CREATE TABLE t1(a INT UNIQUE); 3709CREATE VIEW v1 AS SELECT t1.a FROM t1, t1 AS a; 3710INSERT INTO t1 VALUES (1), (2); 3711REPLACE INTO v1(a) SELECT 1 FROM t1,t1 AS c; 3712SELECT * FROM v1; 3713a 37141 37152 37161 37172 3718REPLACE INTO v1(a) SELECT 3 FROM t1,t1 AS c; 3719SELECT * FROM v1; 3720a 37211 37222 37233 37241 37252 37263 37271 37282 37293 3730DELETE FROM t1 WHERE a=3; 3731INSERT INTO v1(a) SELECT 1 FROM t1,t1 AS c 3732ON DUPLICATE KEY UPDATE `v1`.`a`= 1; 3733SELECT * FROM v1; 3734a 37351 37362 37371 37382 3739CREATE VIEW v2 AS SELECT t1.a FROM t1, v1 AS a; 3740REPLACE INTO v2(a) SELECT 1 FROM t1,t1 AS c; 3741SELECT * FROM v2; 3742a 37431 37442 37451 37462 37471 37482 37491 37502 3751REPLACE INTO v2(a) SELECT 3 FROM t1,t1 AS c; 3752SELECT * FROM v2; 3753a 37541 37552 37563 37571 37582 37593 37601 37612 37623 37631 37642 37653 37661 37672 37683 37691 37702 37713 37721 37732 37743 37751 37762 37773 37781 37792 37803 3781INSERT INTO v2(a) SELECT 1 FROM t1,t1 AS c 3782ON DUPLICATE KEY UPDATE `v2`.`a`= 1; 3783SELECT * FROM v2; 3784a 37851 37862 37873 37881 37892 37903 37911 37922 37933 37941 37952 37963 37971 37982 37993 38001 38012 38023 38031 38042 38053 38061 38072 38083 38091 38102 38113 3812DROP VIEW v1; 3813DROP VIEW v2; 3814DROP TABLE t1; 3815# -- End of test case for Bug#45806 3816# ----------------------------------------------------------------- 3817# -- Bug#40825: Error 1356 while selecting from a view 3818# -- with a "HAVING" clause though query works 3819# ----------------------------------------------------------------- 3820 3821CREATE TABLE t1 (c INT); 3822 3823CREATE VIEW v1 (view_column) AS SELECT c AS alias FROM t1 HAVING alias; 3824SHOW CREATE VIEW v1; 3825View Create View character_set_client collation_connection 3826v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`c` AS `view_column` from `t1` having `view_column` latin1 latin1_swedish_ci 3827SELECT * FROM v1; 3828view_column 3829 3830DROP VIEW v1; 3831DROP TABLE t1; 3832 3833# -- End of test case for Bug#40825 3834 3835# ----------------------------------------------------------------- 3836# -- End of 5.0 tests. 3837# ----------------------------------------------------------------- 3838DROP DATABASE IF EXISTS `d-1`; 3839CREATE DATABASE `d-1`; 3840USE `d-1`; 3841CREATE TABLE `t-1` (c1 INT); 3842CREATE VIEW `v-1` AS SELECT c1 FROM `t-1`; 3843SHOW TABLES; 3844Tables_in_d-1 3845t-1 3846v-1 3847RENAME TABLE `t-1` TO `t-2`; 3848RENAME TABLE `v-1` TO `v-2`; 3849SHOW TABLES; 3850Tables_in_d-1 3851t-2 3852v-2 3853DROP TABLE `t-2`; 3854DROP VIEW `v-2`; 3855DROP DATABASE `d-1`; 3856USE test; 3857 3858# 3859# Bug#26676 VIEW using old table schema in a session. 3860# 3861 3862DROP VIEW IF EXISTS v1; 3863DROP TABLE IF EXISTS t1; 3864CREATE TABLE t1(c1 INT, c2 INT); 3865INSERT INTO t1 VALUES (1, 2), (3, 4); 3866 3867SELECT * FROM t1; 3868c1 c2 38691 2 38703 4 3871 3872CREATE VIEW v1 AS SELECT * FROM t1; 3873 3874SELECT * FROM v1; 3875c1 c2 38761 2 38773 4 3878 3879ALTER TABLE t1 ADD COLUMN c3 INT AFTER c2; 3880 3881SELECT * FROM t1; 3882c1 c2 c3 38831 2 NULL 38843 4 NULL 3885 3886SELECT * FROM v1; 3887c1 c2 38881 2 38893 4 3890 3891SHOW CREATE VIEW v1; 3892View Create View character_set_client collation_connection 3893v1 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 3894 3895DROP VIEW v1; 3896DROP TABLE t1; 3897 3898# End of test case for Bug#26676. 3899 3900# ----------------------------------------------------------------- 3901# -- Bug#32538 View definition picks up character set, but not collation 3902# ----------------------------------------------------------------- 3903 3904DROP VIEW IF EXISTS v1; 3905 3906SET collation_connection = latin1_general_ci; 3907CREATE VIEW v1 AS SELECT _latin1 'text1' AS c1, 'text2' AS c2; 3908 3909SELECT COLLATION(c1), COLLATION(c2) FROM v1; 3910COLLATION(c1) COLLATION(c2) 3911latin1_swedish_ci latin1_general_ci 3912 3913SHOW CREATE VIEW v1; 3914View Create View character_set_client collation_connection 3915v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select _latin1'text1' AS `c1`,'text2' AS `c2` latin1 latin1_general_ci 3916 3917SELECT * FROM v1 WHERE c1 = 'text1'; 3918ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin1_general_ci,COERCIBLE) for operation '=' 3919 3920SELECT * FROM v1 WHERE c2 = 'text2'; 3921c1 c2 3922text1 text2 3923 3924use test; 3925SET names latin1; 3926 3927SELECT COLLATION(c1), COLLATION(c2) FROM v1; 3928COLLATION(c1) COLLATION(c2) 3929latin1_swedish_ci latin1_general_ci 3930 3931SELECT * FROM v1 WHERE c1 = 'text1'; 3932c1 c2 3933text1 text2 3934 3935SELECT * FROM v1 WHERE c2 = 'text2'; 3936ERROR HY000: Illegal mix of collations (latin1_general_ci,COERCIBLE) and (latin1_swedish_ci,COERCIBLE) for operation '=' 3937 3938DROP VIEW v1; 3939 3940# -- End of test case for Bug#32538. 3941 3942drop view if exists a; 3943drop procedure if exists p; 3944create procedure p() 3945begin 3946declare continue handler for sqlexception begin end; 3947create view a as select 1; 3948end| 3949call p(); 3950call p(); 3951drop view a; 3952drop procedure p; 3953# 3954# Bug #44860: ALTER TABLE on view crashes server 3955# 3956CREATE TABLE t1 (a INT); 3957CREATE VIEW v1 AS SELECT a FROM t1; 3958ALTER TABLE v1; 3959ERROR HY000: 'test.v1' is not BASE TABLE 3960DROP VIEW v1; 3961DROP TABLE t1; 3962# 3963# Bug#48449: hang on show create view after upgrading when 3964# view contains function of view 3965# 3966DROP VIEW IF EXISTS v1,v2; 3967DROP TABLE IF EXISTS t1,t2; 3968DROP FUNCTION IF EXISTS f1; 3969CREATE TABLE t1 (a INT); 3970CREATE TABLE t2 (a INT); 3971CREATE FUNCTION f1() RETURNS INT 3972BEGIN 3973SELECT a FROM v2 INTO @a; 3974RETURN @a; 3975END// 3976# Trigger pre-locking when opening v2. 3977CREATE VIEW v1 AS SELECT f1() FROM t1; 3978SHOW CREATE VIEW v1; 3979View Create View character_set_client collation_connection 3980v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `f1`() AS `f1()` from `t1` latin1 latin1_swedish_ci 3981Warnings: 3982Note 1599 View `test`.`v2` has no creation context 3983DROP VIEW v1,v2; 3984DROP TABLE t1,t2; 3985DROP FUNCTION f1; 3986CREATE TABLE t1(f1 INT); 3987INSERT INTO t1 VALUES (); 3988CREATE VIEW v1 AS SELECT 1 FROM t1 WHERE 3989ROW(1,1) >= ROW(1, (SELECT 1 FROM t1 WHERE f1 >= ANY ( SELECT '1' ))); 3990DROP VIEW v1; 3991DROP TABLE t1; 3992# 3993# Bug#52120 create view cause Assertion failed: 0, file .\item_subselect.cc, line 817 3994# 3995CREATE TABLE t1 (a CHAR(1) CHARSET latin1, b CHAR(1) CHARSET utf8); 3996CREATE VIEW v1 AS SELECT 1 from t1 3997WHERE t1.b <=> (SELECT a FROM t1 WHERE a < SOME(SELECT '1')); 3998DROP VIEW v1; 3999DROP TABLE t1; 4000# 4001# Bug#57703 create view cause Assertion failed: 0, file .\item_subselect.cc, line 846 4002# 4003CREATE TABLE t1(a int); 4004CREATE VIEW v1 AS SELECT 1 FROM t1 GROUP BY 4005SUBSTRING(1 FROM (SELECT 3 FROM t1 WHERE a >= ANY(SELECT 1))); 4006DROP VIEW v1; 4007DROP TABLE t1; 4008# 4009# Bug#57352 valgrind warnings when creating view 4010# 4011CREATE VIEW v1 AS SELECT 1 IN (1 LIKE 2,0) AS f; 4012DROP VIEW v1; 4013# 4014# Bug 11829681 - 60295: ERROR 1356 ON VIEW THAT EXECUTES FINE AS A QUERY 4015# 4016CREATE TABLE t1 (a INT); 4017CREATE VIEW v1 AS SELECT s.* FROM t1 s, t1 b HAVING a; 4018SELECT * FROM v1; 4019a 4020DROP VIEW v1; 4021DROP TABLE t1; 4022# ----------------------------------------------------------------- 4023# -- End of 5.1 tests. 4024# ----------------------------------------------------------------- 4025drop table if exists t_9801; 4026drop view if exists v_9801; 4027create table t_9801 (s1 int); 4028create view v_9801 as 4029select sum(s1) from t_9801 with check option; 4030ERROR HY000: CHECK OPTION on non-updatable view 'test.v_9801' 4031create view v_9801 as 4032select sum(s1) from t_9801 group by s1 with check option; 4033ERROR HY000: CHECK OPTION on non-updatable view 'test.v_9801' 4034create view v_9801 as 4035select sum(s1) from t_9801 group by s1 with rollup with check option; 4036ERROR HY000: CHECK OPTION on non-updatable view 'test.v_9801' 4037drop table t_9801; 4038# 4039# Bug #47335 assert in get_table_share 4040# 4041DROP TABLE IF EXISTS t1; 4042DROP VIEW IF EXISTS v1; 4043CREATE TEMPORARY TABLE t1 (id INT); 4044ALTER VIEW t1 AS SELECT 1 AS f1; 4045ERROR 42S02: Table 'test.t1' doesn't exist 4046DROP TABLE t1; 4047CREATE VIEW v1 AS SELECT 1 AS f1; 4048CREATE TEMPORARY TABLE v1 (id INT); 4049ALTER VIEW v1 AS SELECT 2 AS f1; 4050DROP TABLE v1; 4051SELECT * FROM v1; 4052f1 40532 4054DROP VIEW v1; 4055# 4056# Bug #47635 assert in start_waiting_global_read_lock 4057# during CREATE VIEW 4058# 4059DROP TABLE IF EXISTS t1, t2; 4060DROP VIEW IF EXISTS t2; 4061CREATE TABLE t1 (f1 integer); 4062CREATE TEMPORARY TABLE IF NOT EXISTS t1 (f1 integer); 4063CREATE TEMPORARY TABLE t2 (f1 integer); 4064DROP TABLE t1; 4065FLUSH TABLES WITH READ LOCK; 4066CREATE VIEW t2 AS SELECT * FROM t1; 4067ERROR HY000: Can't execute the query because you have a conflicting read lock 4068UNLOCK TABLES; 4069DROP TABLE t1, t2; 4070# 4071# Bug#48315 Metadata lock is not taken for merged views that 4072# use an INFORMATION_SCHEMA table 4073# 4074DROP TABLE IF EXISTS t1; 4075DROP VIEW IF EXISTS v1; 4076DROP PROCEDURE IF EXISTS p1; 4077# Connection default 4078CREATE VIEW v1 AS SELECT schema_name FROM information_schema.schemata; 4079CREATE TABLE t1 (str VARCHAR(50)); 4080CREATE PROCEDURE p1() INSERT INTO t1 SELECT * FROM v1; 4081# CALL p1() so the view is merged. 4082CALL p1(); 4083# Connection 3 4084LOCK TABLE t1 READ; 4085# Connection default 4086# Try to CALL p1() again, this time it should block for t1. 4087# Sending: 4088CALL p1(); 4089# Connection 2 4090# ... then try to drop the view. This should block. 4091# Sending: 4092DROP VIEW v1; 4093# Connection 3 4094# Now allow CALL p1() to complete 4095UNLOCK TABLES; 4096# Connection default 4097# Reaping: CALL p1() 4098# Connection 2 4099# Reaping: DROP VIEW v1 4100# Connection default 4101DROP PROCEDURE p1; 4102DROP TABLE t1; 4103# 4104# Bug#11757397 49437: CANNOT DO SHOW FIELDS FOR BIG VIEW 4105# 4106DROP TABLE IF EXISTS t1, t2, t3, table_broken; 4107DROP VIEW IF EXISTS view_broken; 4108CREATE TABLE t1 (a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int, a10 int, a11 int, a12 int, a13 int, a14 int, a15 int, a16 int, a17 int, a18 int, a19 int, a20 int, a21 int, a22 int, a23 int, a24 int, a25 int, a26 int, a27 int, a28 int, a29 int, a30 int, a31 int, a32 int, a33 int, a34 int, a35 int, a36 int, a37 int, a38 int, a39 int, a40 int, a41 int, a42 int, a43 int, a44 int, a45 int, a46 int, a47 int, a48 int, a49 int, a50 int, a51 int, a52 int, a53 int, a54 int, a55 int, a56 int, a57 int, a58 int, a59 int, a60 int, a61 int, a62 int, a63 int, a64 int, a65 int, a66 int, a67 int, a68 int, a69 int, a70 int, a71 int, a72 int, a73 int, a74 int, a75 int, a76 int, a77 int, a78 int, a79 int, a80 int, a81 int, a82 int, a83 int, a84 int, a85 int, a86 int, a87 int, a88 int, a89 int, a90 int, a91 int, a92 int, a93 int, a94 int, a95 int, a96 int, a97 int, a98 int, a99 int, a100 int, a101 int, a102 int, a103 int, a104 int, a105 int, a106 int, a107 int, a108 int, a109 int, a110 int, a111 int, a112 int, a113 int, a114 int, a115 int, a116 int, a117 int, a118 int, a119 int, a120 int, a121 int, a122 int, a123 int, a124 int, a125 int, a126 int, a127 int, a128 int, a129 int, a130 int, a131 int, a132 int, a133 int, a134 int, a135 int, a136 int, a137 int, a138 int, a139 int, a140 int, a141 int, a142 int, a143 int, a144 int, a145 int, a146 int, a147 int, a148 int, a149 int, a150 int, a151 int, a152 int, a153 int, a154 int, a155 int, a156 int, a157 int, a158 int, a159 int, a160 int, a161 int, a162 int, a163 int, a164 int, a165 int, a166 int, a167 int, a168 int, a169 int, a170 int, a171 int, a172 int, a173 int, a174 int, a175 int, a176 int, a177 int, a178 int, a179 int, a180 int, a181 int, a182 int, a183 int, a184 int, a185 int, a186 int, a187 int, a188 int, a189 int, a190 int, a191 int, a192 int, a193 int, a194 int, a195 int, a196 int, a197 int, a198 int, a199 int, a200 int, a201 int, a202 int, a203 int, a204 int, a205 int, a206 int, a207 int, a208 int, a209 int, a210 int, a211 int, a212 int, a213 int, a214 int, a215 int, a216 int, a217 int, a218 int, a219 int, a220 int, a221 int, a222 int, a223 int, a224 int, a225 int, a226 int, a227 int, a228 int, a229 int, a230 int, a231 int, a232 int, a233 int, a234 int, a235 int, a236 int, a237 int, a238 int, a239 int, a240 int, a241 int, a242 int, a243 int, a244 int, a245 int, a246 int, a247 int, a248 int, a249 int, a250 int, a251 int, a252 int, a253 int, a254 int, a255 int, a256 int, a257 int, a258 int, a259 int, a260 int, a261 int, a262 int, a263 int, a264 int, a265 int, a266 int, a267 int, a268 int, a269 int, a270 int, a271 int, a272 int, a273 int, a274 int, a275 int, a276 int, a277 int, a278 int, a279 int, a280 int, a281 int, a282 int, a283 int, a284 int, a285 int, a286 int, a287 int, a288 int, a289 int, a290 int, a291 int, a292 int, a293 int, a294 int, a295 int, a296 int, a297 int, a298 int, a299 int, a300 int, a301 int, a302 int, a303 int, a304 int, a305 int, a306 int, a307 int, a308 int, a309 int, a310 int, a311 int, a312 int, a313 int, a314 int, a315 int, a316 int, a317 int, a318 int, a319 int, a320 int, a321 int, a322 int, a323 int, a324 int, a325 int, a326 int, a327 int, a328 int, a329 int, a330 int, a331 int, a332 int, a333 int, a334 int, a335 int, a336 int, a337 int, a338 int, a339 int, a340 int, a341 int, a342 int, a343 int, a344 int, a345 int, a346 int, a347 int, a348 int, a349 int, a350 int, a351 int, a352 int, a353 int, a354 int, a355 int, a356 int, a357 int, a358 int, a359 int, a360 int, a361 int, a362 int, a363 int, a364 int, a365 int, a366 int, a367 int, a368 int, a369 int, a370 int, a371 int, a372 int, a373 int, a374 int, a375 int, a376 int, a377 int, a378 int, a379 int, a380 int, a381 int, a382 int, a383 int, a384 int, a385 int, a386 int, a387 int, a388 int, a389 int, a390 int, a391 int, a392 int, a393 int, a394 int, a395 int, a396 int, a397 int, a398 int, a399 int, a400 int, a401 int, a402 int, a403 int, a404 int, a405 int, a406 int, a407 int, a408 int, a409 int, a410 int, a411 int, a412 int, a413 int, a414 int, a415 int, a416 int, a417 int, a418 int, a419 int, a420 int, a421 int, a422 int, a423 int, a424 int, a425 int, a426 int, a427 int, a428 int, a429 int, a430 int, a431 int, a432 int, a433 int, a434 int, a435 int, a436 int, a437 int, a438 int, a439 int, a440 int, a441 int, a442 int, a443 int, a444 int, a445 int, a446 int, a447 int, a448 int, a449 int, a450 int, a451 int, a452 int, a453 int, a454 int, a455 int, a456 int, a457 int, a458 int, a459 int, a460 int, a461 int, a462 int, a463 int, a464 int, a465 int, a466 int, a467 int, a468 int, a469 int, a470 int, a471 int, a472 int, a473 int, a474 int, a475 int, a476 int, a477 int, a478 int, a479 int, a480 int, a481 int, a482 int, a483 int, a484 int, a485 int, a486 int, a487 int, a488 int, a489 int, a490 int, a491 int, a492 int, a493 int, a494 int, a495 int, a496 int, a497 int, a498 int, a499 int, a500 int, a501 int, a502 int, a503 int, a504 int, a505 int, a506 int, a507 int, a508 int, a509 int, a510 int, a511 int, a512 int, a513 int, a514 int, a515 int, a516 int, a517 int, a518 int, a519 int, a520 int, a521 int, a522 int, a523 int, a524 int, a525 int, a526 int, a527 int, a528 int, a529 int, a530 int, a531 int, a532 int, a533 int, a534 int, a535 int, a536 int, a537 int, a538 int, a539 int, a540 int, a541 int, a542 int, a543 int, a544 int, a545 int, a546 int, a547 int, a548 int, a549 int, a550 int, a551 int, a552 int, a553 int, a554 int, a555 int, a556 int, a557 int, a558 int, a559 int, a560 int, a561 int, a562 int, a563 int, a564 int, a565 int, a566 int, a567 int, a568 int, a569 int, a570 int, a571 int, a572 int, a573 int, a574 int, a575 int, a576 int, a577 int, a578 int, a579 int, a580 int, a581 int, a582 int, a583 int, a584 int, a585 int, a586 int, a587 int, a588 int, a589 int, a590 int, a591 int, a592 int, a593 int, a594 int, a595 int, a596 int, a597 int, a598 int, a599 int, a600 int, a601 int, a602 int, a603 int, a604 int, a605 int, a606 int, a607 int, a608 int, a609 int, a610 int, a611 int, a612 int, a613 int, a614 int, a615 int, a616 int, a617 int, a618 int, a619 int, a620 int, a621 int, a622 int, a623 int, a624 int, a625 int, a626 int, a627 int, a628 int, a629 int, a630 int, a631 int, a632 int, a633 int, a634 int, a635 int, a636 int, a637 int, a638 int, a639 int, a640 int, a641 int, a642 int, a643 int, a644 int, a645 int, a646 int, a647 int, a648 int, a649 int, a650 int, a651 int, a652 int, a653 int, a654 int, a655 int, a656 int, a657 int, a658 int, a659 int, a660 int, a661 int, a662 int, a663 int, a664 int, a665 int, a666 int, a667 int, a668 int, a669 int, a670 int, a671 int, a672 int, a673 int, a674 int, a675 int, a676 int, a677 int, a678 int, a679 int, a680 int, a681 int, a682 int, a683 int, a684 int, a685 int, a686 int, a687 int, a688 int, a689 int, a690 int, a691 int, a692 int, a693 int, a694 int, a695 int, a696 int, a697 int, a698 int, a699 int, a700 int, a701 int, a702 int, a703 int, a704 int, a705 int, a706 int, a707 int, a708 int, a709 int, a710 int, a711 int, a712 int, a713 int, a714 int, a715 int, a716 int, a717 int, a718 int, a719 int, a720 int, a721 int, a722 int, a723 int, a724 int, a725 int, a726 int, a727 int, a728 int, a729 int, a730 int, a731 int, a732 int, a733 int, a734 int, a735 int, a736 int, a737 int, a738 int, a739 int, a740 int, a741 int, a742 int, a743 int, a744 int, a745 int, a746 int, a747 int, a748 int, a749 int, a750 int, a751 int, a752 int, a753 int, a754 int, a755 int, a756 int, a757 int, a758 int, a759 int, a760 int, a761 int, a762 int, a763 int, a764 int, a765 int, a766 int, a767 int, a768 int, a769 int, a770 int, a771 int, a772 int, a773 int, a774 int, a775 int, a776 int, a777 int, a778 int, a779 int, a780 int, a781 int, a782 int, a783 int, a784 int, a785 int, a786 int, a787 int, a788 int, a789 int, a790 int, a791 int, a792 int, a793 int, a794 int, a795 int, a796 int, a797 int, a798 int, a799 int, a800 int, a801 int, a802 int, a803 int, a804 int, a805 int, a806 int, a807 int, a808 int, a809 int, a810 int, a811 int, a812 int, a813 int, a814 int, a815 int, a816 int, a817 int, a818 int, a819 int, a820 int, a821 int, a822 int, a823 int, a824 int, a825 int, a826 int, a827 int, a828 int, a829 int, a830 int, a831 int, a832 int, a833 int, a834 int, a835 int, a836 int, a837 int, a838 int, a839 int, a840 int, a841 int, a842 int, a843 int, a844 int, a845 int, a846 int, a847 int, a848 int, a849 int, a850 int, a851 int, a852 int, a853 int, a854 int, a855 int, a856 int, a857 int, a858 int, a859 int, a860 int, a861 int, a862 int, a863 int, a864 int, a865 int, a866 int, a867 int, a868 int, a869 int, a870 int, a871 int, a872 int, a873 int, a874 int, a875 int, a876 int, a877 int, a878 int, a879 int, a880 int, a881 int, a882 int, a883 int, a884 int, a885 int, a886 int, a887 int, a888 int, a889 int, a890 int, a891 int, a892 int, a893 int, a894 int, a895 int, a896 int, a897 int, a898 int, a899 int, a900 int, a901 int, a902 int, a903 int, a904 int, a905 int, a906 int, a907 int, a908 int, a909 int, a910 int, a911 int, a912 int, a913 int, a914 int, a915 int, a916 int, a917 int, a918 int, a919 int, a920 int, a921 int, a922 int, a923 int, a924 int, a925 int, a926 int, a927 int, a928 int, a929 int, a930 int, a931 int, a932 int, a933 int, a934 int, a935 int, a936 int, a937 int, a938 int, a939 int, a940 int, a941 int, a942 int, a943 int, a944 int, a945 int, a946 int, a947 int, a948 int, a949 int, a950 int, a951 int, a952 int, a953 int, a954 int, a955 int, a956 int, a957 int, a958 int, a959 int, a960 int, a961 int, a962 int, a963 int, a964 int, a965 int, a966 int, a967 int, a968 int, a969 int, a970 int, a971 int, a972 int, a973 int, a974 int, a975 int, a976 int, a977 int, a978 int, a979 int, a980 int, a981 int, a982 int, a983 int, a984 int, a985 int, a986 int, a987 int, a988 int, a989 int, a990 int, a991 int, a992 int, a993 int, a994 int, a995 int, a996 int, a997 int, a998 int, a999 int, a1000 int, a1001 int, a1002 int, a1003 int, a1004 int, a1005 int, a1006 int, a1007 int, a1008 int, a1009 int, a1010 int, a1011 int, a1012 int, a1013 int, a1014 int, a1015 int, a1016 int, a1017 int, a1018 int, a1019 int, a1020 int, a1021 int, a1022 int, a1023 int, a1024 int, a1025 int, a1026 int, a1027 int, a1028 int, a1029 int, a1030 int, a1031 int, a1032 int, a1033 int, a1034 int, a1035 int, a1036 int, a1037 int, a1038 int, a1039 int, a1040 int, a1041 int, a1042 int, a1043 int, a1044 int, a1045 int, a1046 int, a1047 int, a1048 int, a1049 int, a1050 int, a1051 int, a1052 int, a1053 int, a1054 int, a1055 int, a1056 int, a1057 int, a1058 int, a1059 int, a1060 int, a1061 int, a1062 int, a1063 int, a1064 int, a1065 int, a1066 int, a1067 int, a1068 int, a1069 int, a1070 int, a1071 int, a1072 int, a1073 int, a1074 int, a1075 int, a1076 int, a1077 int, a1078 int, a1079 int, a1080 int, a1081 int, a1082 int, a1083 int, a1084 int, a1085 int, a1086 int, a1087 int, a1088 int, a1089 int, a1090 int, a1091 int, a1092 int, a1093 int, a1094 int, a1095 int, a1096 int, a1097 int, a1098 int, a1099 int, a1100 int, a1101 int, a1102 int, a1103 int, a1104 int, a1105 int, a1106 int, a1107 int, a1108 int, a1109 int, a1110 int, a1111 int, a1112 int, a1113 int, a1114 int, a1115 int, a1116 int, a1117 int, a1118 int, a1119 int, a1120 int, a1121 int, a1122 int, a1123 int, a1124 int, a1125 int, a1126 int, a1127 int, a1128 int, a1129 int, a1130 int, a1131 int, a1132 int, a1133 int, a1134 int, a1135 int, a1136 int, a1137 int, a1138 int, a1139 int, a1140 int, a1141 int, a1142 int, a1143 int, a1144 int, a1145 int, a1146 int, a1147 int, a1148 int, a1149 int, a1150 int, a1151 int, a1152 int, a1153 int, a1154 int, a1155 int, a1156 int, a1157 int, a1158 int, a1159 int, a1160 int, a1161 int, a1162 int, a1163 int, a1164 int, a1165 int, a1166 int, a1167 int, a1168 int, a1169 int, a1170 int, a1171 int, a1172 int, a1173 int, a1174 int, a1175 int, a1176 int, a1177 int, a1178 int, a1179 int, a1180 int, a1181 int, a1182 int, a1183 int, a1184 int, a1185 int, a1186 int, a1187 int, a1188 int, a1189 int, a1190 int, a1191 int, a1192 int, a1193 int, a1194 int, a1195 int, a1196 int, a1197 int, a1198 int, a1199 int, a1200 int, a1201 int, a1202 int, a1203 int, a1204 int, a1205 int, a1206 int, a1207 int, a1208 int, a1209 int, a1210 int, a1211 int, a1212 int, a1213 int, a1214 int, a1215 int, a1216 int, a1217 int, a1218 int, a1219 int, a1220 int, a1221 int, a1222 int, a1223 int, a1224 int, a1225 int, a1226 int, a1227 int, a1228 int, a1229 int, a1230 int, a1231 int, a1232 int, a1233 int, a1234 int, a1235 int, a1236 int, a1237 int, a1238 int, a1239 int, a1240 int, a1241 int, a1242 int, a1243 int, a1244 int, a1245 int, a1246 int, a1247 int, a1248 int, a1249 int, a1250 int, a1251 int, a1252 int, a1253 int, a1254 int, a1255 int, a1256 int, a1257 int, a1258 int, a1259 int, a1260 int, a1261 int, a1262 int, a1263 int, a1264 int, a1265 int, a1266 int, a1267 int, a1268 int, a1269 int, a1270 int, a1271 int, a1272 int, a1273 int, a1274 int, a1275 int, a1276 int, a1277 int, a1278 int, a1279 int, a1280 int, a1281 int, a1282 int, a1283 int, a1284 int, a1285 int, a1286 int, a1287 int, a1288 int, a1289 int, a1290 int, a1291 int, a1292 int, a1293 int, a1294 int, a1295 int, a1296 int, a1297 int, a1298 int, a1299 int, a1300 int, a1301 int, a1302 int, a1303 int, a1304 int, a1305 int, a1306 int, a1307 int, a1308 int, a1309 int, a1310 int, a1311 int, a1312 int, a1313 int, a1314 int, a1315 int, a1316 int, a1317 int, a1318 int, a1319 int, a1320 int, a1321 int, a1322 int, a1323 int, a1324 int, a1325 int, a1326 int, a1327 int, a1328 int, a1329 int, a1330 int, a1331 int, a1332 int, a1333 int, a1334 int, a1335 int, a1336 int, a1337 int, a1338 int, a1339 int, a1340 int, a1341 int, a1342 int, a1343 int, a1344 int, a1345 int, a1346 int, a1347 int, a1348 int, a1349 int, a1350 int, a1351 int, a1352 int, a1353 int, a1354 int, a1355 int, a1356 int, a1357 int, a1358 int, a1359 int, a1360 int, a1361 int, a1362 int, a1363 int, a1364 int, a1365 int, a1366 int, a1367 int, a1368 int, a1369 int, a1370 int, a1371 int, a1372 int, a1373 int, a1374 int, a1375 int, a1376 int, a1377 int, a1378 int, a1379 int, a1380 int, a1381 int, a1382 int, a1383 int, a1384 int, a1385 int, a1386 int, a1387 int, a1388 int, a1389 int, a1390 int, a1391 int, a1392 int, a1393 int, a1394 int, a1395 int, a1396 int, a1397 int, a1398 int, a1399 int, a1400 int, a1401 int, a1402 int, a1403 int, a1404 int, a1405 int, a1406 int, a1407 int, a1408 int, a1409 int, a1410 int, a1411 int, a1412 int, a1413 int, a1414 int, a1415 int, a1416 int, a1417 int, a1418 int, a1419 int, a1420 int, a1421 int, a1422 int, a1423 int, a1424 int, a1425 int, a1426 int, a1427 int, a1428 int, a1429 int, a1430 int, a1431 int, a1432 int, a1433 int, a1434 int, a1435 int, a1436 int, a1437 int, a1438 int, a1439 int, a1440 int, a1441 int, a1442 int, a1443 int, a1444 int, a1445 int, a1446 int, a1447 int, a1448 int, a1449 int, a1450 int, a1451 int, a1452 int, a1453 int, a1454 int, a1455 int, a1456 int, a1457 int, a1458 int, a1459 int, a1460 int, a1461 int, a1462 int, a1463 int, a1464 int, a1465 int, a1466 int, a1467 int, a1468 int, a1469 int, a1470 int, a1471 int, a1472 int, a1473 int, a1474 int, a1475 int, a1476 int, a1477 int, a1478 int, a1479 int, a1480 int, a1481 int, a1482 int, a1483 int, a1484 int, a1485 int, a1486 int, a1487 int, a1488 int, a1489 int, a1490 int, a1491 int, a1492 int, a1493 int, a1494 int, a1495 int, a1496 int, a1497 int, a1498 int, a1499 int, a1500 int, a text); 4109CREATE TABLE t2 (b1 int, b2 int, b3 int, b4 int, b5 int, b6 int, b7 int, b8 int, b9 int, b10 int, b11 int, b12 int, b13 int, b14 int, b15 int, b16 int, b17 int, b18 int, b19 int, b20 int, b21 int, b22 int, b23 int, b24 int, b25 int, b26 int, b27 int, b28 int, b29 int, b30 int, b31 int, b32 int, b33 int, b34 int, b35 int, b36 int, b37 int, b38 int, b39 int, b40 int, b41 int, b42 int, b43 int, b44 int, b45 int, b46 int, b47 int, b48 int, b49 int, b50 int, b51 int, b52 int, b53 int, b54 int, b55 int, b56 int, b57 int, b58 int, b59 int, b60 int, b61 int, b62 int, b63 int, b64 int, b65 int, b66 int, b67 int, b68 int, b69 int, b70 int, b71 int, b72 int, b73 int, b74 int, b75 int, b76 int, b77 int, b78 int, b79 int, b80 int, b81 int, b82 int, b83 int, b84 int, b85 int, b86 int, b87 int, b88 int, b89 int, b90 int, b91 int, b92 int, b93 int, b94 int, b95 int, b96 int, b97 int, b98 int, b99 int, b100 int, b101 int, b102 int, b103 int, b104 int, b105 int, b106 int, b107 int, b108 int, b109 int, b110 int, b111 int, b112 int, b113 int, b114 int, b115 int, b116 int, b117 int, b118 int, b119 int, b120 int, b121 int, b122 int, b123 int, b124 int, b125 int, b126 int, b127 int, b128 int, b129 int, b130 int, b131 int, b132 int, b133 int, b134 int, b135 int, b136 int, b137 int, b138 int, b139 int, b140 int, b141 int, b142 int, b143 int, b144 int, b145 int, b146 int, b147 int, b148 int, b149 int, b150 int, b151 int, b152 int, b153 int, b154 int, b155 int, b156 int, b157 int, b158 int, b159 int, b160 int, b161 int, b162 int, b163 int, b164 int, b165 int, b166 int, b167 int, b168 int, b169 int, b170 int, b171 int, b172 int, b173 int, b174 int, b175 int, b176 int, b177 int, b178 int, b179 int, b180 int, b181 int, b182 int, b183 int, b184 int, b185 int, b186 int, b187 int, b188 int, b189 int, b190 int, b191 int, b192 int, b193 int, b194 int, b195 int, b196 int, b197 int, b198 int, b199 int, b200 int, b201 int, b202 int, b203 int, b204 int, b205 int, b206 int, b207 int, b208 int, b209 int, b210 int, b211 int, b212 int, b213 int, b214 int, b215 int, b216 int, b217 int, b218 int, b219 int, b220 int, b221 int, b222 int, b223 int, b224 int, b225 int, b226 int, b227 int, b228 int, b229 int, b230 int, b231 int, b232 int, b233 int, b234 int, b235 int, b236 int, b237 int, b238 int, b239 int, b240 int, b241 int, b242 int, b243 int, b244 int, b245 int, b246 int, b247 int, b248 int, b249 int, b250 int, b251 int, b252 int, b253 int, b254 int, b255 int, b256 int, b257 int, b258 int, b259 int, b260 int, b261 int, b262 int, b263 int, b264 int, b265 int, b266 int, b267 int, b268 int, b269 int, b270 int, b271 int, b272 int, b273 int, b274 int, b275 int, b276 int, b277 int, b278 int, b279 int, b280 int, b281 int, b282 int, b283 int, b284 int, b285 int, b286 int, b287 int, b288 int, b289 int, b290 int, b291 int, b292 int, b293 int, b294 int, b295 int, b296 int, b297 int, b298 int, b299 int, b300 int, b301 int, b302 int, b303 int, b304 int, b305 int, b306 int, b307 int, b308 int, b309 int, b310 int, b311 int, b312 int, b313 int, b314 int, b315 int, b316 int, b317 int, b318 int, b319 int, b320 int, b321 int, b322 int, b323 int, b324 int, b325 int, b326 int, b327 int, b328 int, b329 int, b330 int, b331 int, b332 int, b333 int, b334 int, b335 int, b336 int, b337 int, b338 int, b339 int, b340 int, b341 int, b342 int, b343 int, b344 int, b345 int, b346 int, b347 int, b348 int, b349 int, b350 int, b351 int, b352 int, b353 int, b354 int, b355 int, b356 int, b357 int, b358 int, b359 int, b360 int, b361 int, b362 int, b363 int, b364 int, b365 int, b366 int, b367 int, b368 int, b369 int, b370 int, b371 int, b372 int, b373 int, b374 int, b375 int, b376 int, b377 int, b378 int, b379 int, b380 int, b381 int, b382 int, b383 int, b384 int, b385 int, b386 int, b387 int, b388 int, b389 int, b390 int, b391 int, b392 int, b393 int, b394 int, b395 int, b396 int, b397 int, b398 int, b399 int, b400 int, b401 int, b402 int, b403 int, b404 int, b405 int, b406 int, b407 int, b408 int, b409 int, b410 int, b411 int, b412 int, b413 int, b414 int, b415 int, b416 int, b417 int, b418 int, b419 int, b420 int, b421 int, b422 int, b423 int, b424 int, b425 int, b426 int, b427 int, b428 int, b429 int, b430 int, b431 int, b432 int, b433 int, b434 int, b435 int, b436 int, b437 int, b438 int, b439 int, b440 int, b441 int, b442 int, b443 int, b444 int, b445 int, b446 int, b447 int, b448 int, b449 int, b450 int, b451 int, b452 int, b453 int, b454 int, b455 int, b456 int, b457 int, b458 int, b459 int, b460 int, b461 int, b462 int, b463 int, b464 int, b465 int, b466 int, b467 int, b468 int, b469 int, b470 int, b471 int, b472 int, b473 int, b474 int, b475 int, b476 int, b477 int, b478 int, b479 int, b480 int, b481 int, b482 int, b483 int, b484 int, b485 int, b486 int, b487 int, b488 int, b489 int, b490 int, b491 int, b492 int, b493 int, b494 int, b495 int, b496 int, b497 int, b498 int, b499 int, b500 int, b501 int, b502 int, b503 int, b504 int, b505 int, b506 int, b507 int, b508 int, b509 int, b510 int, b511 int, b512 int, b513 int, b514 int, b515 int, b516 int, b517 int, b518 int, b519 int, b520 int, b521 int, b522 int, b523 int, b524 int, b525 int, b526 int, b527 int, b528 int, b529 int, b530 int, b531 int, b532 int, b533 int, b534 int, b535 int, b536 int, b537 int, b538 int, b539 int, b540 int, b541 int, b542 int, b543 int, b544 int, b545 int, b546 int, b547 int, b548 int, b549 int, b550 int, b551 int, b552 int, b553 int, b554 int, b555 int, b556 int, b557 int, b558 int, b559 int, b560 int, b561 int, b562 int, b563 int, b564 int, b565 int, b566 int, b567 int, b568 int, b569 int, b570 int, b571 int, b572 int, b573 int, b574 int, b575 int, b576 int, b577 int, b578 int, b579 int, b580 int, b581 int, b582 int, b583 int, b584 int, b585 int, b586 int, b587 int, b588 int, b589 int, b590 int, b591 int, b592 int, b593 int, b594 int, b595 int, b596 int, b597 int, b598 int, b599 int, b600 int, b601 int, b602 int, b603 int, b604 int, b605 int, b606 int, b607 int, b608 int, b609 int, b610 int, b611 int, b612 int, b613 int, b614 int, b615 int, b616 int, b617 int, b618 int, b619 int, b620 int, b621 int, b622 int, b623 int, b624 int, b625 int, b626 int, b627 int, b628 int, b629 int, b630 int, b631 int, b632 int, b633 int, b634 int, b635 int, b636 int, b637 int, b638 int, b639 int, b640 int, b641 int, b642 int, b643 int, b644 int, b645 int, b646 int, b647 int, b648 int, b649 int, b650 int, b651 int, b652 int, b653 int, b654 int, b655 int, b656 int, b657 int, b658 int, b659 int, b660 int, b661 int, b662 int, b663 int, b664 int, b665 int, b666 int, b667 int, b668 int, b669 int, b670 int, b671 int, b672 int, b673 int, b674 int, b675 int, b676 int, b677 int, b678 int, b679 int, b680 int, b681 int, b682 int, b683 int, b684 int, b685 int, b686 int, b687 int, b688 int, b689 int, b690 int, b691 int, b692 int, b693 int, b694 int, b695 int, b696 int, b697 int, b698 int, b699 int, b700 int, b701 int, b702 int, b703 int, b704 int, b705 int, b706 int, b707 int, b708 int, b709 int, b710 int, b711 int, b712 int, b713 int, b714 int, b715 int, b716 int, b717 int, b718 int, b719 int, b720 int, b721 int, b722 int, b723 int, b724 int, b725 int, b726 int, b727 int, b728 int, b729 int, b730 int, b731 int, b732 int, b733 int, b734 int, b735 int, b736 int, b737 int, b738 int, b739 int, b740 int, b741 int, b742 int, b743 int, b744 int, b745 int, b746 int, b747 int, b748 int, b749 int, b750 int, b751 int, b752 int, b753 int, b754 int, b755 int, b756 int, b757 int, b758 int, b759 int, b760 int, b761 int, b762 int, b763 int, b764 int, b765 int, b766 int, b767 int, b768 int, b769 int, b770 int, b771 int, b772 int, b773 int, b774 int, b775 int, b776 int, b777 int, b778 int, b779 int, b780 int, b781 int, b782 int, b783 int, b784 int, b785 int, b786 int, b787 int, b788 int, b789 int, b790 int, b791 int, b792 int, b793 int, b794 int, b795 int, b796 int, b797 int, b798 int, b799 int, b800 int, b801 int, b802 int, b803 int, b804 int, b805 int, b806 int, b807 int, b808 int, b809 int, b810 int, b811 int, b812 int, b813 int, b814 int, b815 int, b816 int, b817 int, b818 int, b819 int, b820 int, b821 int, b822 int, b823 int, b824 int, b825 int, b826 int, b827 int, b828 int, b829 int, b830 int, b831 int, b832 int, b833 int, b834 int, b835 int, b836 int, b837 int, b838 int, b839 int, b840 int, b841 int, b842 int, b843 int, b844 int, b845 int, b846 int, b847 int, b848 int, b849 int, b850 int, b851 int, b852 int, b853 int, b854 int, b855 int, b856 int, b857 int, b858 int, b859 int, b860 int, b861 int, b862 int, b863 int, b864 int, b865 int, b866 int, b867 int, b868 int, b869 int, b870 int, b871 int, b872 int, b873 int, b874 int, b875 int, b876 int, b877 int, b878 int, b879 int, b880 int, b881 int, b882 int, b883 int, b884 int, b885 int, b886 int, b887 int, b888 int, b889 int, b890 int, b891 int, b892 int, b893 int, b894 int, b895 int, b896 int, b897 int, b898 int, b899 int, b900 int, b901 int, b902 int, b903 int, b904 int, b905 int, b906 int, b907 int, b908 int, b909 int, b910 int, b911 int, b912 int, b913 int, b914 int, b915 int, b916 int, b917 int, b918 int, b919 int, b920 int, b921 int, b922 int, b923 int, b924 int, b925 int, b926 int, b927 int, b928 int, b929 int, b930 int, b931 int, b932 int, b933 int, b934 int, b935 int, b936 int, b937 int, b938 int, b939 int, b940 int, b941 int, b942 int, b943 int, b944 int, b945 int, b946 int, b947 int, b948 int, b949 int, b950 int, b951 int, b952 int, b953 int, b954 int, b955 int, b956 int, b957 int, b958 int, b959 int, b960 int, b961 int, b962 int, b963 int, b964 int, b965 int, b966 int, b967 int, b968 int, b969 int, b970 int, b971 int, b972 int, b973 int, b974 int, b975 int, b976 int, b977 int, b978 int, b979 int, b980 int, b981 int, b982 int, b983 int, b984 int, b985 int, b986 int, b987 int, b988 int, b989 int, b990 int, b991 int, b992 int, b993 int, b994 int, b995 int, b996 int, b997 int, b998 int, b999 int, b1000 int, b1001 int, b1002 int, b1003 int, b1004 int, b1005 int, b1006 int, b1007 int, b1008 int, b1009 int, b1010 int, b1011 int, b1012 int, b1013 int, b1014 int, b1015 int, b1016 int, b1017 int, b1018 int, b1019 int, b1020 int, b1021 int, b1022 int, b1023 int, b1024 int, b1025 int, b1026 int, b1027 int, b1028 int, b1029 int, b1030 int, b1031 int, b1032 int, b1033 int, b1034 int, b1035 int, b1036 int, b1037 int, b1038 int, b1039 int, b1040 int, b1041 int, b1042 int, b1043 int, b1044 int, b1045 int, b1046 int, b1047 int, b1048 int, b1049 int, b1050 int, b1051 int, b1052 int, b1053 int, b1054 int, b1055 int, b1056 int, b1057 int, b1058 int, b1059 int, b1060 int, b1061 int, b1062 int, b1063 int, b1064 int, b1065 int, b1066 int, b1067 int, b1068 int, b1069 int, b1070 int, b1071 int, b1072 int, b1073 int, b1074 int, b1075 int, b1076 int, b1077 int, b1078 int, b1079 int, b1080 int, b1081 int, b1082 int, b1083 int, b1084 int, b1085 int, b1086 int, b1087 int, b1088 int, b1089 int, b1090 int, b1091 int, b1092 int, b1093 int, b1094 int, b1095 int, b1096 int, b1097 int, b1098 int, b1099 int, b1100 int, b1101 int, b1102 int, b1103 int, b1104 int, b1105 int, b1106 int, b1107 int, b1108 int, b1109 int, b1110 int, b1111 int, b1112 int, b1113 int, b1114 int, b1115 int, b1116 int, b1117 int, b1118 int, b1119 int, b1120 int, b1121 int, b1122 int, b1123 int, b1124 int, b1125 int, b1126 int, b1127 int, b1128 int, b1129 int, b1130 int, b1131 int, b1132 int, b1133 int, b1134 int, b1135 int, b1136 int, b1137 int, b1138 int, b1139 int, b1140 int, b1141 int, b1142 int, b1143 int, b1144 int, b1145 int, b1146 int, b1147 int, b1148 int, b1149 int, b1150 int, b1151 int, b1152 int, b1153 int, b1154 int, b1155 int, b1156 int, b1157 int, b1158 int, b1159 int, b1160 int, b1161 int, b1162 int, b1163 int, b1164 int, b1165 int, b1166 int, b1167 int, b1168 int, b1169 int, b1170 int, b1171 int, b1172 int, b1173 int, b1174 int, b1175 int, b1176 int, b1177 int, b1178 int, b1179 int, b1180 int, b1181 int, b1182 int, b1183 int, b1184 int, b1185 int, b1186 int, b1187 int, b1188 int, b1189 int, b1190 int, b1191 int, b1192 int, b1193 int, b1194 int, b1195 int, b1196 int, b1197 int, b1198 int, b1199 int, b1200 int, b1201 int, b1202 int, b1203 int, b1204 int, b1205 int, b1206 int, b1207 int, b1208 int, b1209 int, b1210 int, b1211 int, b1212 int, b1213 int, b1214 int, b1215 int, b1216 int, b1217 int, b1218 int, b1219 int, b1220 int, b1221 int, b1222 int, b1223 int, b1224 int, b1225 int, b1226 int, b1227 int, b1228 int, b1229 int, b1230 int, b1231 int, b1232 int, b1233 int, b1234 int, b1235 int, b1236 int, b1237 int, b1238 int, b1239 int, b1240 int, b1241 int, b1242 int, b1243 int, b1244 int, b1245 int, b1246 int, b1247 int, b1248 int, b1249 int, b1250 int, b1251 int, b1252 int, b1253 int, b1254 int, b1255 int, b1256 int, b1257 int, b1258 int, b1259 int, b1260 int, b1261 int, b1262 int, b1263 int, b1264 int, b1265 int, b1266 int, b1267 int, b1268 int, b1269 int, b1270 int, b1271 int, b1272 int, b1273 int, b1274 int, b1275 int, b1276 int, b1277 int, b1278 int, b1279 int, b1280 int, b1281 int, b1282 int, b1283 int, b1284 int, b1285 int, b1286 int, b1287 int, b1288 int, b1289 int, b1290 int, b1291 int, b1292 int, b1293 int, b1294 int, b1295 int, b1296 int, b1297 int, b1298 int, b1299 int, b1300 int, b1301 int, b1302 int, b1303 int, b1304 int, b1305 int, b1306 int, b1307 int, b1308 int, b1309 int, b1310 int, b1311 int, b1312 int, b1313 int, b1314 int, b1315 int, b1316 int, b1317 int, b1318 int, b1319 int, b1320 int, b1321 int, b1322 int, b1323 int, b1324 int, b1325 int, b1326 int, b1327 int, b1328 int, b1329 int, b1330 int, b1331 int, b1332 int, b1333 int, b1334 int, b1335 int, b1336 int, b1337 int, b1338 int, b1339 int, b1340 int, b1341 int, b1342 int, b1343 int, b1344 int, b1345 int, b1346 int, b1347 int, b1348 int, b1349 int, b1350 int, b1351 int, b1352 int, b1353 int, b1354 int, b1355 int, b1356 int, b1357 int, b1358 int, b1359 int, b1360 int, b1361 int, b1362 int, b1363 int, b1364 int, b1365 int, b1366 int, b1367 int, b1368 int, b1369 int, b1370 int, b1371 int, b1372 int, b1373 int, b1374 int, b1375 int, b1376 int, b1377 int, b1378 int, b1379 int, b1380 int, b1381 int, b1382 int, b1383 int, b1384 int, b1385 int, b1386 int, b1387 int, b1388 int, b1389 int, b1390 int, b1391 int, b1392 int, b1393 int, b1394 int, b1395 int, b1396 int, b1397 int, b1398 int, b1399 int, b1400 int, b1401 int, b1402 int, b1403 int, b1404 int, b1405 int, b1406 int, b1407 int, b1408 int, b1409 int, b1410 int, b1411 int, b1412 int, b1413 int, b1414 int, b1415 int, b1416 int, b1417 int, b1418 int, b1419 int, b1420 int, b1421 int, b1422 int, b1423 int, b1424 int, b1425 int, b1426 int, b1427 int, b1428 int, b1429 int, b1430 int, b1431 int, b1432 int, b1433 int, b1434 int, b1435 int, b1436 int, b1437 int, b1438 int, b1439 int, b1440 int, b1441 int, b1442 int, b1443 int, b1444 int, b1445 int, b1446 int, b1447 int, b1448 int, b1449 int, b1450 int, b1451 int, b1452 int, b1453 int, b1454 int, b1455 int, b1456 int, b1457 int, b1458 int, b1459 int, b1460 int, b1461 int, b1462 int, b1463 int, b1464 int, b1465 int, b1466 int, b1467 int, b1468 int, b1469 int, b1470 int, b1471 int, b1472 int, b1473 int, b1474 int, b1475 int, b1476 int, b1477 int, b1478 int, b1479 int, b1480 int, b1481 int, b1482 int, b1483 int, b1484 int, b1485 int, b1486 int, b1487 int, b1488 int, b1489 int, b1490 int, b1491 int, b1492 int, b1493 int, b1494 int, b1495 int, b1496 int, b1497 int, b1498 int, b1499 int, b1500 int, b text); 4110CREATE TABLE t3 (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int, c9 int, c10 int, c11 int, c12 int, c13 int, c14 int, c15 int, c16 int, c17 int, c18 int, c19 int, c20 int, c21 int, c22 int, c23 int, c24 int, c25 int, c26 int, c27 int, c28 int, c29 int, c30 int, c31 int, c32 int, c33 int, c34 int, c35 int, c36 int, c37 int, c38 int, c39 int, c40 int, c41 int, c42 int, c43 int, c44 int, c45 int, c46 int, c47 int, c48 int, c49 int, c50 int, c51 int, c52 int, c53 int, c54 int, c55 int, c56 int, c57 int, c58 int, c59 int, c60 int, c61 int, c62 int, c63 int, c64 int, c65 int, c66 int, c67 int, c68 int, c69 int, c70 int, c71 int, c72 int, c73 int, c74 int, c75 int, c76 int, c77 int, c78 int, c79 int, c80 int, c81 int, c82 int, c83 int, c84 int, c85 int, c86 int, c87 int, c88 int, c89 int, c90 int, c91 int, c92 int, c93 int, c94 int, c95 int, c96 int, c97 int, c98 int, c99 int, c100 int, c101 int, c102 int, c103 int, c104 int, c105 int, c106 int, c107 int, c108 int, c109 int, c110 int, c111 int, c112 int, c113 int, c114 int, c115 int, c116 int, c117 int, c118 int, c119 int, c120 int, c121 int, c122 int, c123 int, c124 int, c125 int, c126 int, c127 int, c128 int, c129 int, c130 int, c131 int, c132 int, c133 int, c134 int, c135 int, c136 int, c137 int, c138 int, c139 int, c140 int, c141 int, c142 int, c143 int, c144 int, c145 int, c146 int, c147 int, c148 int, c149 int, c150 int, c151 int, c152 int, c153 int, c154 int, c155 int, c156 int, c157 int, c158 int, c159 int, c160 int, c161 int, c162 int, c163 int, c164 int, c165 int, c166 int, c167 int, c168 int, c169 int, c170 int, c171 int, c172 int, c173 int, c174 int, c175 int, c176 int, c177 int, c178 int, c179 int, c180 int, c181 int, c182 int, c183 int, c184 int, c185 int, c186 int, c187 int, c188 int, c189 int, c190 int, c191 int, c192 int, c193 int, c194 int, c195 int, c196 int, c197 int, c198 int, c199 int, c200 int, c201 int, c202 int, c203 int, c204 int, c205 int, c206 int, c207 int, c208 int, c209 int, c210 int, c211 int, c212 int, c213 int, c214 int, c215 int, c216 int, c217 int, c218 int, c219 int, c220 int, c221 int, c222 int, c223 int, c224 int, c225 int, c226 int, c227 int, c228 int, c229 int, c230 int, c231 int, c232 int, c233 int, c234 int, c235 int, c236 int, c237 int, c238 int, c239 int, c240 int, c241 int, c242 int, c243 int, c244 int, c245 int, c246 int, c247 int, c248 int, c249 int, c250 int, c251 int, c252 int, c253 int, c254 int, c255 int, c256 int, c257 int, c258 int, c259 int, c260 int, c261 int, c262 int, c263 int, c264 int, c265 int, c266 int, c267 int, c268 int, c269 int, c270 int, c271 int, c272 int, c273 int, c274 int, c275 int, c276 int, c277 int, c278 int, c279 int, c280 int, c281 int, c282 int, c283 int, c284 int, c285 int, c286 int, c287 int, c288 int, c289 int, c290 int, c291 int, c292 int, c293 int, c294 int, c295 int, c296 int, c297 int, c298 int, c299 int, c300 int, c301 int, c302 int, c303 int, c304 int, c305 int, c306 int, c307 int, c308 int, c309 int, c310 int, c311 int, c312 int, c313 int, c314 int, c315 int, c316 int, c317 int, c318 int, c319 int, c320 int, c321 int, c322 int, c323 int, c324 int, c325 int, c326 int, c327 int, c328 int, c329 int, c330 int, c331 int, c332 int, c333 int, c334 int, c335 int, c336 int, c337 int, c338 int, c339 int, c340 int, c341 int, c342 int, c343 int, c344 int, c345 int, c346 int, c347 int, c348 int, c349 int, c350 int, c351 int, c352 int, c353 int, c354 int, c355 int, c356 int, c357 int, c358 int, c359 int, c360 int, c361 int, c362 int, c363 int, c364 int, c365 int, c366 int, c367 int, c368 int, c369 int, c370 int, c371 int, c372 int, c373 int, c374 int, c375 int, c376 int, c377 int, c378 int, c379 int, c380 int, c381 int, c382 int, c383 int, c384 int, c385 int, c386 int, c387 int, c388 int, c389 int, c390 int, c391 int, c392 int, c393 int, c394 int, c395 int, c396 int, c397 int, c398 int, c399 int, c400 int, c401 int, c402 int, c403 int, c404 int, c405 int, c406 int, c407 int, c408 int, c409 int, c410 int, c411 int, c412 int, c413 int, c414 int, c415 int, c416 int, c417 int, c418 int, c419 int, c420 int, c421 int, c422 int, c423 int, c424 int, c425 int, c426 int, c427 int, c428 int, c429 int, c430 int, c431 int, c432 int, c433 int, c434 int, c435 int, c436 int, c437 int, c438 int, c439 int, c440 int, c441 int, c442 int, c443 int, c444 int, c445 int, c446 int, c447 int, c448 int, c449 int, c450 int, c451 int, c452 int, c453 int, c454 int, c455 int, c456 int, c457 int, c458 int, c459 int, c460 int, c461 int, c462 int, c463 int, c464 int, c465 int, c466 int, c467 int, c468 int, c469 int, c470 int, c471 int, c472 int, c473 int, c474 int, c475 int, c476 int, c477 int, c478 int, c479 int, c480 int, c481 int, c482 int, c483 int, c484 int, c485 int, c486 int, c487 int, c488 int, c489 int, c490 int, c491 int, c492 int, c493 int, c494 int, c495 int, c496 int, c497 int, c498 int, c499 int, c500 int, c501 int, c502 int, c503 int, c504 int, c505 int, c506 int, c507 int, c508 int, c509 int, c510 int, c511 int, c512 int, c513 int, c514 int, c515 int, c516 int, c517 int, c518 int, c519 int, c520 int, c521 int, c522 int, c523 int, c524 int, c525 int, c526 int, c527 int, c528 int, c529 int, c530 int, c531 int, c532 int, c533 int, c534 int, c535 int, c536 int, c537 int, c538 int, c539 int, c540 int, c541 int, c542 int, c543 int, c544 int, c545 int, c546 int, c547 int, c548 int, c549 int, c550 int, c551 int, c552 int, c553 int, c554 int, c555 int, c556 int, c557 int, c558 int, c559 int, c560 int, c561 int, c562 int, c563 int, c564 int, c565 int, c566 int, c567 int, c568 int, c569 int, c570 int, c571 int, c572 int, c573 int, c574 int, c575 int, c576 int, c577 int, c578 int, c579 int, c580 int, c581 int, c582 int, c583 int, c584 int, c585 int, c586 int, c587 int, c588 int, c589 int, c590 int, c591 int, c592 int, c593 int, c594 int, c595 int, c596 int, c597 int, c598 int, c599 int, c600 int, c601 int, c602 int, c603 int, c604 int, c605 int, c606 int, c607 int, c608 int, c609 int, c610 int, c611 int, c612 int, c613 int, c614 int, c615 int, c616 int, c617 int, c618 int, c619 int, c620 int, c621 int, c622 int, c623 int, c624 int, c625 int, c626 int, c627 int, c628 int, c629 int, c630 int, c631 int, c632 int, c633 int, c634 int, c635 int, c636 int, c637 int, c638 int, c639 int, c640 int, c641 int, c642 int, c643 int, c644 int, c645 int, c646 int, c647 int, c648 int, c649 int, c650 int, c651 int, c652 int, c653 int, c654 int, c655 int, c656 int, c657 int, c658 int, c659 int, c660 int, c661 int, c662 int, c663 int, c664 int, c665 int, c666 int, c667 int, c668 int, c669 int, c670 int, c671 int, c672 int, c673 int, c674 int, c675 int, c676 int, c677 int, c678 int, c679 int, c680 int, c681 int, c682 int, c683 int, c684 int, c685 int, c686 int, c687 int, c688 int, c689 int, c690 int, c691 int, c692 int, c693 int, c694 int, c695 int, c696 int, c697 int, c698 int, c699 int, c700 int, c701 int, c702 int, c703 int, c704 int, c705 int, c706 int, c707 int, c708 int, c709 int, c710 int, c711 int, c712 int, c713 int, c714 int, c715 int, c716 int, c717 int, c718 int, c719 int, c720 int, c721 int, c722 int, c723 int, c724 int, c725 int, c726 int, c727 int, c728 int, c729 int, c730 int, c731 int, c732 int, c733 int, c734 int, c735 int, c736 int, c737 int, c738 int, c739 int, c740 int, c741 int, c742 int, c743 int, c744 int, c745 int, c746 int, c747 int, c748 int, c749 int, c750 int, c751 int, c752 int, c753 int, c754 int, c755 int, c756 int, c757 int, c758 int, c759 int, c760 int, c761 int, c762 int, c763 int, c764 int, c765 int, c766 int, c767 int, c768 int, c769 int, c770 int, c771 int, c772 int, c773 int, c774 int, c775 int, c776 int, c777 int, c778 int, c779 int, c780 int, c781 int, c782 int, c783 int, c784 int, c785 int, c786 int, c787 int, c788 int, c789 int, c790 int, c791 int, c792 int, c793 int, c794 int, c795 int, c796 int, c797 int, c798 int, c799 int, c800 int, c801 int, c802 int, c803 int, c804 int, c805 int, c806 int, c807 int, c808 int, c809 int, c810 int, c811 int, c812 int, c813 int, c814 int, c815 int, c816 int, c817 int, c818 int, c819 int, c820 int, c821 int, c822 int, c823 int, c824 int, c825 int, c826 int, c827 int, c828 int, c829 int, c830 int, c831 int, c832 int, c833 int, c834 int, c835 int, c836 int, c837 int, c838 int, c839 int, c840 int, c841 int, c842 int, c843 int, c844 int, c845 int, c846 int, c847 int, c848 int, c849 int, c850 int, c851 int, c852 int, c853 int, c854 int, c855 int, c856 int, c857 int, c858 int, c859 int, c860 int, c861 int, c862 int, c863 int, c864 int, c865 int, c866 int, c867 int, c868 int, c869 int, c870 int, c871 int, c872 int, c873 int, c874 int, c875 int, c876 int, c877 int, c878 int, c879 int, c880 int, c881 int, c882 int, c883 int, c884 int, c885 int, c886 int, c887 int, c888 int, c889 int, c890 int, c891 int, c892 int, c893 int, c894 int, c895 int, c896 int, c897 int, c898 int, c899 int, c900 int, c901 int, c902 int, c903 int, c904 int, c905 int, c906 int, c907 int, c908 int, c909 int, c910 int, c911 int, c912 int, c913 int, c914 int, c915 int, c916 int, c917 int, c918 int, c919 int, c920 int, c921 int, c922 int, c923 int, c924 int, c925 int, c926 int, c927 int, c928 int, c929 int, c930 int, c931 int, c932 int, c933 int, c934 int, c935 int, c936 int, c937 int, c938 int, c939 int, c940 int, c941 int, c942 int, c943 int, c944 int, c945 int, c946 int, c947 int, c948 int, c949 int, c950 int, c951 int, c952 int, c953 int, c954 int, c955 int, c956 int, c957 int, c958 int, c959 int, c960 int, c961 int, c962 int, c963 int, c964 int, c965 int, c966 int, c967 int, c968 int, c969 int, c970 int, c971 int, c972 int, c973 int, c974 int, c975 int, c976 int, c977 int, c978 int, c979 int, c980 int, c981 int, c982 int, c983 int, c984 int, c985 int, c986 int, c987 int, c988 int, c989 int, c990 int, c991 int, c992 int, c993 int, c994 int, c995 int, c996 int, c997 int, c998 int, c999 int, c1000 int, c1001 int, c1002 int, c1003 int, c1004 int, c1005 int, c1006 int, c1007 int, c1008 int, c1009 int, c1010 int, c1011 int, c1012 int, c1013 int, c1014 int, c1015 int, c1016 int, c1017 int, c1018 int, c1019 int, c1020 int, c1021 int, c1022 int, c1023 int, c1024 int, c1025 int, c1026 int, c1027 int, c1028 int, c1029 int, c1030 int, c1031 int, c1032 int, c1033 int, c1034 int, c1035 int, c1036 int, c1037 int, c1038 int, c1039 int, c1040 int, c1041 int, c1042 int, c1043 int, c1044 int, c1045 int, c1046 int, c1047 int, c1048 int, c1049 int, c1050 int, c1051 int, c1052 int, c1053 int, c1054 int, c1055 int, c1056 int, c1057 int, c1058 int, c1059 int, c1060 int, c1061 int, c1062 int, c1063 int, c1064 int, c1065 int, c1066 int, c1067 int, c1068 int, c1069 int, c1070 int, c1071 int, c1072 int, c1073 int, c1074 int, c1075 int, c1076 int, c1077 int, c1078 int, c1079 int, c1080 int, c1081 int, c1082 int, c1083 int, c1084 int, c1085 int, c1086 int, c1087 int, c1088 int, c1089 int, c1090 int, c1091 int, c1092 int, c1093 int, c1094 int, c1095 int, c1096 int, c1097 int, c1098 int, c1099 int, c1100 int, c1101 int, c1102 int, c1103 int, c1104 int, c1105 int, c1106 int, c1107 int, c1108 int, c1109 int, c1110 int, c1111 int, c1112 int, c1113 int, c1114 int, c1115 int, c1116 int, c1117 int, c1118 int, c1119 int, c1120 int, c1121 int, c1122 int, c1123 int, c1124 int, c1125 int, c1126 int, c1127 int, c1128 int, c1129 int, c1130 int, c1131 int, c1132 int, c1133 int, c1134 int, c1135 int, c1136 int, c1137 int, c1138 int, c1139 int, c1140 int, c1141 int, c1142 int, c1143 int, c1144 int, c1145 int, c1146 int, c1147 int, c1148 int, c1149 int, c1150 int, c1151 int, c1152 int, c1153 int, c1154 int, c1155 int, c1156 int, c1157 int, c1158 int, c1159 int, c1160 int, c1161 int, c1162 int, c1163 int, c1164 int, c1165 int, c1166 int, c1167 int, c1168 int, c1169 int, c1170 int, c1171 int, c1172 int, c1173 int, c1174 int, c1175 int, c1176 int, c1177 int, c1178 int, c1179 int, c1180 int, c1181 int, c1182 int, c1183 int, c1184 int, c1185 int, c1186 int, c1187 int, c1188 int, c1189 int, c1190 int, c1191 int, c1192 int, c1193 int, c1194 int, c1195 int, c1196 int, c1197 int, c1198 int, c1199 int, c1200 int, c1201 int, c1202 int, c1203 int, c1204 int, c1205 int, c1206 int, c1207 int, c1208 int, c1209 int, c1210 int, c1211 int, c1212 int, c1213 int, c1214 int, c1215 int, c1216 int, c1217 int, c1218 int, c1219 int, c1220 int, c1221 int, c1222 int, c1223 int, c1224 int, c1225 int, c1226 int, c1227 int, c1228 int, c1229 int, c1230 int, c1231 int, c1232 int, c1233 int, c1234 int, c1235 int, c1236 int, c1237 int, c1238 int, c1239 int, c1240 int, c1241 int, c1242 int, c1243 int, c1244 int, c1245 int, c1246 int, c1247 int, c1248 int, c1249 int, c1250 int, c1251 int, c1252 int, c1253 int, c1254 int, c1255 int, c1256 int, c1257 int, c1258 int, c1259 int, c1260 int, c1261 int, c1262 int, c1263 int, c1264 int, c1265 int, c1266 int, c1267 int, c1268 int, c1269 int, c1270 int, c1271 int, c1272 int, c1273 int, c1274 int, c1275 int, c1276 int, c1277 int, c1278 int, c1279 int, c1280 int, c1281 int, c1282 int, c1283 int, c1284 int, c1285 int, c1286 int, c1287 int, c1288 int, c1289 int, c1290 int, c1291 int, c1292 int, c1293 int, c1294 int, c1295 int, c1296 int, c1297 int, c1298 int, c1299 int, c1300 int, c1301 int, c1302 int, c1303 int, c1304 int, c1305 int, c1306 int, c1307 int, c1308 int, c1309 int, c1310 int, c1311 int, c1312 int, c1313 int, c1314 int, c1315 int, c1316 int, c1317 int, c1318 int, c1319 int, c1320 int, c1321 int, c1322 int, c1323 int, c1324 int, c1325 int, c1326 int, c1327 int, c1328 int, c1329 int, c1330 int, c1331 int, c1332 int, c1333 int, c1334 int, c1335 int, c1336 int, c1337 int, c1338 int, c1339 int, c1340 int, c1341 int, c1342 int, c1343 int, c1344 int, c1345 int, c1346 int, c1347 int, c1348 int, c1349 int, c1350 int, c1351 int, c1352 int, c1353 int, c1354 int, c1355 int, c1356 int, c1357 int, c1358 int, c1359 int, c1360 int, c1361 int, c1362 int, c1363 int, c1364 int, c1365 int, c1366 int, c1367 int, c1368 int, c1369 int, c1370 int, c1371 int, c1372 int, c1373 int, c1374 int, c1375 int, c1376 int, c1377 int, c1378 int, c1379 int, c1380 int, c1381 int, c1382 int, c1383 int, c1384 int, c1385 int, c1386 int, c1387 int, c1388 int, c1389 int, c1390 int, c1391 int, c1392 int, c1393 int, c1394 int, c1395 int, c1396 int, c1397 int, c1398 int, c1399 int, c1400 int, c1401 int, c1402 int, c1403 int, c1404 int, c1405 int, c1406 int, c1407 int, c1408 int, c1409 int, c1410 int, c1411 int, c1412 int, c1413 int, c1414 int, c1415 int, c1416 int, c1417 int, c1418 int, c1419 int, c1420 int, c1421 int, c1422 int, c1423 int, c1424 int, c1425 int, c1426 int, c1427 int, c1428 int, c1429 int, c1430 int, c1431 int, c1432 int, c1433 int, c1434 int, c1435 int, c1436 int, c1437 int, c1438 int, c1439 int, c1440 int, c1441 int, c1442 int, c1443 int, c1444 int, c1445 int, c1446 int, c1447 int, c1448 int, c1449 int, c1450 int, c1451 int, c1452 int, c1453 int, c1454 int, c1455 int, c1456 int, c1457 int, c1458 int, c1459 int, c1460 int, c1461 int, c1462 int, c1463 int, c1464 int, c1465 int, c1466 int, c1467 int, c1468 int, c1469 int, c1470 int, c1471 int, c1472 int, c1473 int, c1474 int, c1475 int, c1476 int, c1477 int, c1478 int, c1479 int, c1480 int, c1481 int, c1482 int, c1483 int, c1484 int, c1485 int, c1486 int, c1487 int, c1488 int, c1489 int, c1490 int, c1491 int, c1492 int, c1493 int, c1494 int, c1495 int, c1496 int, c1497 int, c1498 int, c1499 int, c1500 int, c text); 4111CREATE VIEW view_broken AS SELECT * FROM t1, t2, t3; 4112ERROR HY000: Too many columns 4113CREATE TABLE table_broken AS SELECT * FROM t1, t2, t3; 4114ERROR HY000: Too many columns 4115DROP TABLE t1, t2, t3; 4116# 4117# Bug#11766440 59546: Assertion m_sp == __null fails in 4118# Item_func_sp::init_result_field with functions 4119# 4120CREATE TABLE t1 (a INT); 4121CREATE FUNCTION f1 () RETURNS INTEGER RETURN 1; 4122CREATE FUNCTION f2 (i INTEGER) RETURNS INTEGER RETURN 1; 4123CREATE VIEW v1 AS SELECT f1() AS a FROM t1; 4124CREATE VIEW v2 AS SELECT f2(a) AS a FROM v1; 4125DROP FUNCTION f1; 4126SELECT f2(a) FROM v2; 4127ERROR HY000: View 'test.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 4128DROP VIEW v2; 4129DROP VIEW v1; 4130DROP FUNCTION f2; 4131DROP TABLE t1; 4132# 4133# Bug#13418197: ASSERTION `(*TABLES)->REGINFO.LOCK_TYPE >= 4134# TL_READ' FAILED | MYSQL_LOCK_TABLES 4135# 4136DROP TABLE IF EXISTS t1; 4137CREATE TEMPORARY TABLE t1 (a INT) engine=InnoDB; 4138CREATE VIEW t1 AS SELECT 1; 4139DROP VIEW t1; 4140DROP TEMPORARY TABLE t1; 4141# 4142# Bug#13601606: FAILED VIEW CREATION ERROR MESSAGE (FOR DB NOT PRESENT) 4143# NEEDS BIG IMPROVEMENT 4144# 4145DROP DATABASE IF EXISTS nodb; 4146CREATE VIEW nodb.a AS SELECT 1; 4147ERROR 42000: Unknown database 'nodb' 4148# 4149# Bug#13633549 HANDLE_FATAL_SIGNAL IN 4150# TEST_IF_SKIP_SORT_ORDER/CREATE_SORT_INDEX 4151# 4152CREATE TABLE t1 ( 4153pk INT AUTO_INCREMENT, 4154c_int_key INT, 4155PRIMARY KEY (pk), 4156KEY (c_int_key) 4157) 4158ENGINE=innodb; 4159CREATE VIEW v_t1 AS SELECT * FROM t1; 4160CREATE TABLE t2 ( 4161pk INT auto_increment, 4162c_varchar_600_x VARCHAR(600), 4163c_int_key INT, 4164c_varchar_600_y VARCHAR(600), 4165c_varchar_600_z VARCHAR(600), 4166PRIMARY KEY (pk), 4167KEY (c_int_key) 4168) 4169ENGINE=innodb; 4170CREATE VIEW v_t2 AS SELECT * FROM t2; 4171INSERT INTO t2 VALUES 4172( 4173NULL, 4174repeat('x', 600), 41753, 4176repeat('y', 600), 4177repeat('z', 600) 4178); 4179SELECT a1.pk AS f1 4180FROM v_t1 AS a1 LEFT JOIN v_t2 AS a2 ON a1.pk=a2.c_int_key 4181WHERE 4182a1.pk > 8 4183OR ((a1.pk BETWEEN 9 AND 13) AND a1.pk = 90) 4184ORDER BY f1 ; 4185f1 4186DROP TABLE t1, t2; 4187DROP VIEW v_t1, v_t2; 4188# 4189# Bug#13783777 CONSTANT PROPAGATION IS WRONG FOR 4190# DISJUNCTIVE PREDICATES IN VIEWS 4191# 4192CREATE TABLE t1 ( 4193pk INTEGER, 4194PRIMARY KEY (pk) 4195); 4196INSERT INTO t1 VALUES (1), (2); 4197CREATE VIEW v_t1 AS SELECT * FROM t1; 4198SELECT pk 4199FROM t1 4200WHERE 4201pk > 8 4202OR ((pk BETWEEN 9 AND 13) AND pk = 90) 4203; 4204pk 4205SELECT pk 4206FROM v_t1 4207WHERE 4208pk > 8 4209OR ((pk BETWEEN 9 AND 13) AND pk = 90) 4210; 4211pk 4212DROP VIEW v_t1; 4213DROP TABLE t1; 4214# 4215# BUG#14117018 - MYSQL SERVER CREATES INVALID VIEW DEFINITION 4216# BUG#18405221 - SHOW CREATE VIEW OUTPUT INCORRECT 4217# 4218CREATE VIEW v1 AS (SELECT '' FROM DUAL); 4219CREATE VIEW v2 AS (SELECT 'BUG#14117018' AS col1 FROM DUAL) UNION ALL 4220(SELECT '' FROM DUAL); 4221CREATE VIEW v3 AS (SELECT 'BUG#14117018' AS col1 FROM DUAL) UNION ALL 4222(SELECT '' FROM DUAL) UNION ALL 4223(SELECT '' FROM DUAL); 4224CREATE VIEW v4 AS (SELECT 'BUG#14117018' AS col1 FROM DUAL) UNION ALL 4225(SELECT '' AS col2 FROM DUAL) UNION ALL 4226(SELECT '' FROM DUAL); 4227# Name for the column in select1 is set properly with or 4228# without this fix. 4229SHOW CREATE VIEW v1; 4230View Create View character_set_client collation_connection 4231v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select '' AS `Name_exp_1`) latin1 latin1_swedish_ci 4232# Name for the column in select2 is set with this fix. 4233# Without this fix, name would not have set for the 4234# columns in select2. 4235SHOW CREATE VIEW v2; 4236View Create View character_set_client collation_connection 4237v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS (select 'BUG#14117018' AS `col1`) union all (select '' AS `Name_exp_2`) latin1 latin1_swedish_ci 4238# Name for the field item in select2 & select3 is set with this fix. 4239# Without this fix, name would not have set for the 4240# columns in select2 & select3. 4241SHOW CREATE VIEW v3; 4242View Create View character_set_client collation_connection 4243v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS (select 'BUG#14117018' AS `col1`) union all (select '' AS `Name_exp_2`) union all (select '' AS `Name_exp_3`) latin1 latin1_swedish_ci 4244# Name for the field item in select3 is set with this fix. 4245# Without this fix, name would not have set for the 4246# columns in select3. 4247SHOW CREATE VIEW v4; 4248View Create View character_set_client collation_connection 4249v4 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_3`) latin1 latin1_swedish_ci 4250DROP VIEW v1, v2, v3, v4; 4251CREATE TABLE t0(x INTEGER); 4252INSERT INTO t0 VALUES(0); 4253CREATE TABLE t1(a1 INTEGER PRIMARY KEY, b1 INTEGER); 4254CREATE TABLE t2(a2 INTEGER PRIMARY KEY, b2 INTEGER); 4255CREATE VIEW v0 AS SELECT DISTINCT x FROM t0; 4256CREATE VIEW vmat1 AS SELECT DISTINCT * FROM t1; 4257CREATE VIEW vmat2 AS SELECT DISTINCT * FROM t2; 4258CREATE VIEW vtt AS 4259SELECT * FROM t1 JOIN t2 ON t1.a1=t2.a2; 4260CREATE VIEW vtr AS 4261SELECT * FROM t1 JOIN vmat2 AS dt2 ON t1.a1=dt2.a2; 4262CREATE VIEW vtrd AS 4263SELECT * FROM t1 JOIN (SELECT DISTINCT * FROM t2) AS dt2 ON t1.a1=dt2.a2; 4264ERROR HY000: View's SELECT contains a subquery in the FROM clause 4265CREATE VIEW vrt AS 4266SELECT * FROM vmat1 AS dt1 JOIN t2 ON dt1.a1=t2.a2; 4267CREATE VIEW vrtd AS 4268SELECT * FROM (SELECT DISTINCT * FROM t1) AS dt1 JOIN t2 ON dt1.a1=t2.a2; 4269ERROR HY000: View's SELECT contains a subquery in the FROM clause 4270CREATE VIEW vrr AS 4271SELECT * FROM vmat1 AS dt1 JOIN vmat2 AS dt2 ON dt1.a1=dt2.a2; 4272CREATE VIEW vrrd AS 4273SELECT * FROM (SELECT DISTINCT * FROM t1) AS dt1 JOIN 4274(SELECT DISTINCT * FROM t2) AS dt2 ON dt1.a1=dt2.a2; 4275ERROR HY000: View's SELECT contains a subquery in the FROM clause 4276INSERT INTO vtt(a1,b1) VALUES (1,100); 4277INSERT INTO vtt(a2,b2) VALUES (1,100); 4278INSERT INTO vtr(a1,b1) VALUES (2,100); 4279ERROR HY000: The target table vtr of the INSERT is not insertable-into 4280INSERT INTO vtrd(a1,b1) VALUES (3,100); 4281ERROR 42S02: Table 'test.vtrd' doesn't exist 4282INSERT INTO vtr(a2,b2) VALUES (2,100); 4283ERROR HY000: The target table vtr of the INSERT is not insertable-into 4284INSERT INTO vtrd(a2,b2) VALUES (3,100); 4285ERROR 42S02: Table 'test.vtrd' doesn't exist 4286INSERT INTO vrt(a1,b1) VALUES (4,100); 4287ERROR HY000: The target table vrt of the INSERT is not insertable-into 4288INSERT INTO vrtd(a1,b1) VALUES (5,100); 4289ERROR 42S02: Table 'test.vrtd' doesn't exist 4290INSERT INTO vrt(a2,b2) VALUES (4,100); 4291ERROR HY000: The target table vrt of the INSERT is not insertable-into 4292INSERT INTO vrtd(a2,b2) VALUES (5,100); 4293ERROR 42S02: Table 'test.vrtd' doesn't exist 4294INSERT INTO vrr(a1,b1) VALUES (6,100); 4295ERROR HY000: The target table vrr of the INSERT is not insertable-into 4296INSERT INTO vrrd(a1,b1) VALUES (7,100); 4297ERROR 42S02: Table 'test.vrrd' doesn't exist 4298INSERT INTO vrr(a2,b2) VALUES (6,100); 4299ERROR HY000: The target table vrr of the INSERT is not insertable-into 4300INSERT INTO vrrd(a2,b2) VALUES (7,100); 4301ERROR 42S02: Table 'test.vrrd' doesn't exist 4302SELECT * FROM vtt; 4303a1 b1 a2 b2 43041 100 1 100 4305DELETE FROM t1; 4306DELETE FROM t2; 4307INSERT INTO vtt(a1,b1) SELECT 1,100; 4308INSERT INTO vtt(a2,b2) SELECT 1,100; 4309INSERT INTO vtr(a1,b1) SELECT 2,100; 4310ERROR HY000: The target table vtr of the INSERT is not insertable-into 4311INSERT INTO vtrd(a1,b1) SELECT 3,100; 4312ERROR 42S02: Table 'test.vtrd' doesn't exist 4313INSERT INTO vtr(a2,b2) SELECT 2,100; 4314ERROR HY000: The target table vtr of the INSERT is not insertable-into 4315INSERT INTO vtrd(a2,b2) SELECT 3,100; 4316ERROR 42S02: Table 'test.vtrd' doesn't exist 4317INSERT INTO vrt(a1,b1) SELECT 4,100; 4318ERROR HY000: The target table vrt of the INSERT is not insertable-into 4319INSERT INTO vrtd(a1,b1) SELECT 5,100; 4320ERROR 42S02: Table 'test.vrtd' doesn't exist 4321INSERT INTO vrt(a2,b2) SELECT 4,100; 4322ERROR HY000: The target table vrt of the INSERT is not insertable-into 4323INSERT INTO vrtd(a2,b2) SELECT 5,100; 4324ERROR 42S02: Table 'test.vrtd' doesn't exist 4325INSERT INTO vrr(a1,b1) SELECT 6,100; 4326ERROR HY000: The target table vrr of the INSERT is not insertable-into 4327INSERT INTO vrrd(a1,b1) SELECT 7,100; 4328ERROR 42S02: Table 'test.vrrd' doesn't exist 4329INSERT INTO vrr(a2,b2) SELECT 6,100; 4330ERROR HY000: The target table vrr of the INSERT is not insertable-into 4331INSERT INTO vrrd(a2,b2) SELECT 7,100; 4332ERROR 42S02: Table 'test.vrrd' doesn't exist 4333SELECT * FROM vtt; 4334a1 b1 a2 b2 43351 100 1 100 4336DELETE FROM t1; 4337DELETE FROM t2; 4338INSERT INTO t1 VALUES 4339(1,100), (2,100), (3,100), (4,100), (5,100), 4340(6,100), (7,100), (8,100), (9,100), (10,100), 4341(11,100), (12,100), (13,100), (14,100); 4342INSERT INTO t2 VALUES 4343(1,100), (2,100), (3,100), (4,100), (5,100), 4344(6,100), (7,100), (8,100), (9,100), (10,100), 4345(11,100), (12,100), (13,100), (14,100); 4346DELETE FROM vtt WHERE a1=1; 4347ERROR HY000: Can not delete from join view 'test.vtt' 4348DELETE FROM vtr WHERE a1=2; 4349ERROR HY000: Can not delete from join view 'test.vtr' 4350DELETE FROM vtrd WHERE a1=3; 4351ERROR 42S02: Table 'test.vtrd' doesn't exist 4352DELETE FROM vrt WHERE a1=4; 4353ERROR HY000: Can not delete from join view 'test.vrt' 4354DELETE FROM vrtd WHERE a1=5; 4355ERROR 42S02: Table 'test.vrtd' doesn't exist 4356DELETE FROM vrr WHERE a1=6; 4357ERROR HY000: Can not delete from join view 'test.vrr' 4358DELETE FROM vrrd WHERE a1=7; 4359ERROR 42S02: Table 'test.vrrd' doesn't exist 4360DELETE vtt FROM vtt WHERE a1=8; 4361ERROR HY000: Can not delete from join view 'test.vtt' 4362DELETE vtr FROM vtr WHERE a1=9; 4363ERROR HY000: Can not delete from join view 'test.vtr' 4364DELETE vtrd FROM vtrd WHERE a1=10; 4365ERROR 42S02: Table 'test.vtrd' doesn't exist 4366DELETE vrt FROM vrt WHERE a1=11; 4367ERROR HY000: Can not delete from join view 'test.vrt' 4368DELETE vrtd FROM vrtd WHERE a1=12; 4369ERROR 42S02: Table 'test.vrtd' doesn't exist 4370DELETE vrr FROM vrr WHERE a1=13; 4371ERROR HY000: Can not delete from join view 'test.vrr' 4372DELETE vrrd FROM vrrd WHERE a1=14; 4373ERROR 42S02: Table 'test.vrrd' doesn't exist 4374SELECT * FROM vtt; 4375a1 b1 a2 b2 43761 100 1 100 43772 100 2 100 43783 100 3 100 43794 100 4 100 43805 100 5 100 43816 100 6 100 43827 100 7 100 43838 100 8 100 43849 100 9 100 438510 100 10 100 438611 100 11 100 438712 100 12 100 438813 100 13 100 438914 100 14 100 4390DELETE FROM t1; 4391DELETE FROM t2; 4392INSERT INTO t1 VALUES (1,100); 4393INSERT INTO t2 VALUES (1,100); 4394UPDATE vtt SET b1=b1+1 WHERE a1=1; 4395UPDATE vtt SET b2=b2+1 WHERE a2=1; 4396UPDATE vtr SET b1=b1+1 WHERE a1=1; 4397UPDATE vtrd SET b1=b1+1 WHERE a1=1; 4398ERROR 42S02: Table 'test.vtrd' doesn't exist 4399UPDATE vtr SET b2=b2+1 WHERE a2=1; 4400ERROR HY000: The target table dt2 of the UPDATE is not updatable 4401UPDATE vtrd SET b2=b2+1 WHERE a2=1; 4402ERROR 42S02: Table 'test.vtrd' doesn't exist 4403UPDATE vrt SET b1=b1+1 WHERE a1=1; 4404ERROR HY000: The target table dt1 of the UPDATE is not updatable 4405UPDATE vrtd SET b1=b1+1 WHERE a1=1; 4406ERROR 42S02: Table 'test.vrtd' doesn't exist 4407UPDATE vrt SET b2=b2+1 WHERE a2=1; 4408UPDATE vrtd SET b2=b2+1 WHERE a2=1; 4409ERROR 42S02: Table 'test.vrtd' doesn't exist 4410UPDATE vrr SET b1=b1+1 WHERE a1=1; 4411ERROR HY000: The target table dt1 of the UPDATE is not updatable 4412UPDATE vrrd SET b1=b1+1 WHERE a1=1; 4413ERROR 42S02: Table 'test.vrrd' doesn't exist 4414UPDATE vrr SET b2=b2+1 WHERE a2=1; 4415ERROR HY000: The target table dt2 of the UPDATE is not updatable 4416UPDATE vrrd SET b2=b2+1 WHERE a2=1; 4417ERROR 42S02: Table 'test.vrrd' doesn't exist 4418UPDATE vtt, v0 AS dt SET b1=b1+1 WHERE a1=1; 4419UPDATE vtt, v0 SET b2=b2+1 WHERE a2=1; 4420UPDATE vtr, v0 SET b1=b1+1 WHERE a1=1; 4421UPDATE vtrd, v0 SET b1=b1+1 WHERE a1=1; 4422ERROR 42S02: Table 'test.vtrd' doesn't exist 4423UPDATE vtr, v0 SET b2=b2+1 WHERE a2=1; 4424ERROR HY000: The target table dt2 of the UPDATE is not updatable 4425UPDATE vtrd, v0 SET b2=b2+1 WHERE a2=1; 4426ERROR 42S02: Table 'test.vtrd' doesn't exist 4427UPDATE vrt, v0 SET b1=b1+1 WHERE a1=1; 4428ERROR HY000: The target table dt1 of the UPDATE is not updatable 4429UPDATE vrtd, v0 SET b1=b1+1 WHERE a1=1; 4430ERROR 42S02: Table 'test.vrtd' doesn't exist 4431UPDATE vrt, v0 SET b2=b2+1 WHERE a2=1; 4432UPDATE vrtd, v0 SET b2=b2+1 WHERE a2=1; 4433ERROR 42S02: Table 'test.vrtd' doesn't exist 4434UPDATE vrr, v0 SET b1=b1+1 WHERE a1=1; 4435ERROR HY000: The target table dt1 of the UPDATE is not updatable 4436UPDATE vrrd, v0 SET b1=b1+1 WHERE a1=1; 4437ERROR 42S02: Table 'test.vrrd' doesn't exist 4438UPDATE vrr, v0 SET b2=b2+1 WHERE a2=1; 4439ERROR HY000: The target table dt2 of the UPDATE is not updatable 4440UPDATE vrrd, v0 SET b2=b2+1 WHERE a2=1; 4441ERROR 42S02: Table 'test.vrrd' doesn't exist 4442SELECT * FROM vtt; 4443a1 b1 a2 b2 44441 104 1 104 4445DROP VIEW v0, vtt, vtr, vrt, vrr, vmat1, vmat2; 4446DROP TABLE t0, t1, t2; 4447# 4448# BUG#19886430: VIEW CREATION WITH NAMED COLUMNS, OVER UNION, 4449# IS REJECTED 4450# Without the patch, reports an error. 4451CREATE VIEW v1 (fld1, fld2) AS 4452SELECT 1 AS a, 2 AS b 4453UNION ALL 4454SELECT 1 AS a, 1 AS a; 4455# The column names are explicitly specified and not duplicates, hence 4456# succeeds. 4457CREATE VIEW v2 (fld1, fld2) AS 4458SELECT 1 AS a, 2 AS a 4459UNION ALL 4460SELECT 1 AS a, 1 AS a; 4461# The column name in the first SELECT are not duplicates, hence succeeds. 4462CREATE VIEW v3 AS 4463SELECT 1 AS a, 2 AS b 4464UNION ALL 4465SELECT 1 AS a, 1 AS a; 4466# Should report an error, since the explicitly specified column names are 4467# duplicates. 4468CREATE VIEW v4 (fld1, fld1) AS 4469SELECT 1 AS a, 2 AS b 4470UNION ALL 4471SELECT 1 AS a, 1 AS a; 4472ERROR 42S21: Duplicate column name 'fld1' 4473# Should report an error, since duplicate column name is specified in the 4474# First SELECT. 4475CREATE VIEW v4 AS 4476SELECT 1 AS a, 2 AS a 4477UNION ALL 4478SELECT 1 AS a, 1 AS a; 4479ERROR 42S21: Duplicate column name 'a' 4480# Cleanup 4481DROP VIEW v1, v2, v3; 4482