1--source include/have_partition.inc 2# Save the initial number of concurrent sessions. 3--source include/count_sessions.inc 4--source include/default_optimizer_switch.inc 5 6SET optimizer_switch='outer_join_with_cache=off'; 7 8# 9# some basic test of views and its functionality 10# 11 12# create view on nonexistent table 13-- error ER_NO_SUCH_TABLE 14create view v1 (c,d) as select a,b from t1; 15 16create temporary table t1 (a int, b int); 17# view on temporary table 18-- error ER_VIEW_SELECT_TMPTABLE 19create view v1 (c) as select b+1 from t1; 20drop table t1; 21 22create table t1 (a int, b int); 23insert into t1 values (1,2), (1,3), (2,4), (2,5), (3,10); 24 25# view with variable 26-- error ER_VIEW_SELECT_VARIABLE 27create view v1 (c,d) as select a,b+@@global.max_user_connections from t1; 28-- error ER_VIEW_SELECT_VARIABLE 29create view v1 (c,d) as select a,b from t1 30 where a = @@global.max_user_connections; 31 32# simple view 33create view v1 (c) as select b+1 from t1; 34select c from v1; 35select is_updatable from information_schema.views where table_name='v1'; 36 37# temporary table should not hide table of view 38create temporary table t1 (a int, b int); 39# this is empty 40select * from t1; 41# but this based on normal t1 42select c from v1; 43show create table v1; 44show create view v1; 45-- error ER_WRONG_OBJECT 46show create view t1; 47drop table t1; 48 49# try to use fields from underlying table 50-- error ER_BAD_FIELD_ERROR 51select a from v1; 52-- error ER_BAD_FIELD_ERROR 53select v1.a from v1; 54-- error ER_BAD_FIELD_ERROR 55select b from v1; 56-- error ER_BAD_FIELD_ERROR 57select v1.b from v1; 58 59# view with different algorithms (explain output differs) 60explain extended select c from v1; 61create algorithm=temptable view v2 (c) as select b+1 from t1; 62show create view v2; 63select c from v2; 64explain extended select c from v2; 65 66# try to use underlying table fields in VIEW creation process 67-- error ER_BAD_FIELD_ERROR 68create view v3 (c) as select a+1 from v1; 69-- error ER_BAD_FIELD_ERROR 70create view v3 (c) as select b+1 from v1; 71 72 73# VIEW on VIEW test with mixing different algorithms on different order 74create view v3 (c) as select c+1 from v1; 75select c from v3; 76explain extended select c from v3; 77create algorithm=temptable view v4 (c) as select c+1 from v2; 78select c from v4; 79explain extended select c from v4; 80create view v5 (c) as select c+1 from v2; 81select c from v5; 82explain extended select c from v5; 83create algorithm=temptable view v6 (c) as select c+1 from v1; 84select c from v6; 85explain extended select c from v6; 86 87# show table/table status test 88show tables; 89show full tables; 90--replace_column 8 # 12 # 13 # 14 # 19 # 91show table status; 92 93drop view v1,v2,v3,v4,v5,v6; 94 95# 96# alter/create view test 97# 98 99# view with subqueries of different types 100create view v1 (c,d,e,f) as select a,b, 101a in (select a+2 from t1), a = all (select a from t1) from t1; 102create view v2 as select c, d from v1; 103select * from v1; 104select * from v2; 105 106# try to create VIEW with name of existing VIEW 107-- error ER_TABLE_EXISTS_ERROR 108create 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; 109 110# 'or replace' should work in this case 111create 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; 112 113# try to ALTER unexisting VIEW 114drop view v2; 115-- error ER_NO_SUCH_TABLE 116alter view v2 as select c, d from v1; 117 118# 'or replace' on unexisting view 119create or replace view v2 as select c, d from v1; 120 121# alter view on existing view 122alter view v1 (c,d) as select a,max(b) from t1 group by a; 123 124# check that created view works 125select * from v1; 126select * from v2; 127 128# try to drop nonexistent VIEW 129--error ER_UNKNOWN_VIEW 130drop view v100; 131 132# try to drop table with DROP VIEW 133-- error ER_UNKNOWN_VIEW 134drop view t1; 135 136# try to drop VIEW with DROP TABLE 137-- error ER_IT_IS_A_VIEW 138drop table v1; 139 140# try to drop table with DROP VIEW 141 142drop view v1,v2; 143drop table t1; 144 145# 146# outer left join with merged views 147# 148create table t1 (a int); 149insert into t1 values (1), (2), (3); 150 151create view v1 (a) as select a+1 from t1; 152create view v2 (a) as select a-1 from t1; 153 154select * from t1 natural left join v1; 155select * from v2 natural left join t1; 156select * from v2 natural left join v1; 157 158drop view v1, v2; 159drop table t1; 160 161 162# 163# DISTINCT option for VIEW 164# 165create table t1 (a int); 166insert into t1 values (1), (2), (3), (1), (2), (3); 167create view v1 as select distinct a from t1; 168select * from v1; 169explain select * from v1; 170select * from t1; 171drop view v1; 172drop table t1; 173 174# 175# syntax compatibility 176# 177create table t1 (a int); 178-- error ER_VIEW_NONUPD_CHECK 179create view v1 as select distinct a from t1 WITH CHECK OPTION; 180create view v1 as select a from t1 WITH CHECK OPTION; 181create view v2 as select a from t1 WITH CASCADED CHECK OPTION; 182create view v3 as select a from t1 WITH LOCAL CHECK OPTION; 183drop view v3 RESTRICT; 184drop view v2 CASCADE; 185drop view v1; 186drop table t1; 187 188# 189# aliases 190# 191create table t1 (a int, b int); 192insert into t1 values (1,2), (1,3), (2,4), (2,5), (3,10); 193create view v1 (c) as select b+1 from t1; 194select test.c from v1 test; 195create algorithm=temptable view v2 (c) as select b+1 from t1; 196select test.c from v2 test; 197select test1.* from v1 test1, v2 test2 where test1.c=test2.c; 198select test2.* from v1 test1, v2 test2 where test1.c=test2.c; 199drop table t1; 200drop view v1,v2; 201 202# 203# LIMIT clause test 204# 205create table t1 (a int); 206insert into t1 values (1), (2), (3), (4); 207create view v1 as select a+1 from t1 order by 1 desc limit 2; 208select * from v1; 209explain select * from v1; 210drop view v1; 211drop table t1; 212 213# 214# CREATE ... SELECT view test 215# 216create table t1 (a int); 217insert into t1 values (1), (2), (3), (4); 218create view v1 as select a+1 from t1; 219create table t2 select * from v1; 220show columns from t2; 221select * from t2; 222drop view v1; 223drop table t1,t2; 224 225# 226# simple view + simple update 227# 228create table t1 (a int, b int, primary key(a)); 229insert into t1 values (10,2), (20,3), (30,4), (40,5), (50,10); 230create view v1 (a,c) as select a, b+1 from t1; 231create algorithm=temptable view v2 (a,c) as select a, b+1 from t1; 232select is_updatable from information_schema.views where table_name='v2'; 233select is_updatable from information_schema.views where table_name='v1'; 234# try to update expression 235-- error ER_NONUPDATEABLE_COLUMN 236update v1 set c=a+c; 237# try to update VIEW with forced TEMPORARY TABLE algorithm 238-- error ER_NON_UPDATABLE_TABLE 239update v2 set a=a+c; 240# updatable field of updateable view 241update v1 set a=a+c; 242select * from v1; 243select * from t1; 244drop table t1; 245drop view v1,v2; 246 247# 248# simple view + simple multi-update 249# 250create table t1 (a int, b int, primary key(a)); 251insert into t1 values (10,2), (20,3), (30,4), (40,5), (50,10); 252create table t2 (x int); 253insert into t2 values (10), (20); 254create view v1 (a,c) as select a, b+1 from t1; 255create algorithm=temptable view v2 (a,c) as select a, b+1 from t1; 256# try to update expression 257-- error ER_NONUPDATEABLE_COLUMN 258update t2,v1 set v1.c=v1.a+v1.c where t2.x=v1.a; 259# try to update VIEW with forced TEMPORARY TABLE algorithm 260-- error ER_NON_UPDATABLE_TABLE 261update t2,v2 set v2.a=v2.v2.a+c where t2.x=v2.a; 262# updatable field of updateable view 263update t2,v1 set v1.a=v1.a+v1.c where t2.x=v1.a; 264select * from v1; 265select * from t1; 266drop table t1,t2; 267drop view v1,v2; 268 269# 270# MERGE VIEW with WHERE clause 271# 272create table t1 (a int, b int, primary key(b)); 273insert into t1 values (1,20), (2,30), (3,40), (4,50), (5,100); 274create view v1 (c) as select b from t1 where a<3; 275# simple select and explaint to be sure that it is MERGE 276select * from v1; 277explain extended select * from v1; 278# update test 279update v1 set c=c+1; 280select * from t1; 281# join of such VIEWs test 282create view v2 (c) as select b from t1 where a>=3; 283select * from v1, v2; 284drop view v1, v2; 285drop table t1; 286 287# 288# simple view + simple delete 289# 290create table t1 (a int, b int, primary key(a)); 291insert into t1 values (1,2), (2,3), (3,4), (4,5), (5,10); 292create view v1 (a,c) as select a, b+1 from t1; 293create algorithm=temptable view v2 (a,c) as select a, b+1 from t1; 294# try to update VIEW with forced TEMPORARY TABLE algorithm 295-- error ER_NON_UPDATABLE_TABLE 296delete from v2 where c < 4; 297# updatable field of updateable view 298delete from v1 where c < 4; 299select * from v1; 300select * from t1; 301drop table t1; 302drop view v1,v2; 303 304# 305# simple view + simple multi-delete 306# 307create table t1 (a int, b int, primary key(a)); 308insert into t1 values (1,2), (2,3), (3,4), (4,5), (5,10); 309create table t2 (x int); 310insert into t2 values (1), (2), (3), (4); 311create view v1 (a,c) as select a, b+1 from t1; 312create algorithm=temptable view v2 (a,c) as select a, b+1 from t1; 313# try to update VIEW with forced TEMPORARY TABLE algorithm 314-- error ER_NON_UPDATABLE_TABLE 315delete v2 from t2,v2 where t2.x=v2.a; 316# updatable field of updateable view 317delete v1 from t2,v1 where t2.x=v1.a; 318select * from v1; 319select * from t1; 320drop table t1,t2; 321drop view v1,v2; 322 323# 324# key presence check 325# 326create table t1 (a int, b int, c int, primary key(a,b)); 327insert into t1 values (10,2,-1), (20,3,-2), (30,4,-3), (40,5,-4), (50,10,-5); 328create view v1 (x,y) as select a, b from t1; 329create view v2 (x,y) as select a, c from t1; 330set updatable_views_with_limit=NO; 331update v1 set x=x+1; 332update v2 set x=x+1; 333update v1 set x=x+1 limit 1; 334-- error ER_NON_UPDATABLE_TABLE 335update v2 set x=x+1 limit 1; 336set updatable_views_with_limit=YES; 337update v1 set x=x+1 limit 1; 338update v2 set x=x+1 limit 1; 339set updatable_views_with_limit=DEFAULT; 340show variables like "updatable_views_with_limit"; 341select * from t1; 342drop table t1; 343drop view v1,v2; 344 345# 346# simple insert 347# 348create table t1 (a int, b int, c int, primary key(a,b)); 349insert into t1 values (10,2,-1), (20,3,-2); 350create view v1 (x,y,z) as select c, b, a from t1; 351create view v2 (x,y) as select b, a from t1; 352create view v3 (x,y,z) as select b, a, b from t1; 353create view v4 (x,y,z) as select c+1, b, a from t1; 354create algorithm=temptable view v5 (x,y,z) as select c, b, a from t1; 355# try insert to VIEW with fields duplicate 356-- error ER_NON_INSERTABLE_TABLE 357insert into v3 values (-60,4,30); 358# try insert to VIEW with expression in SELECT list 359-- error ER_NON_INSERTABLE_TABLE 360insert into v4 values (-60,4,30); 361# try insert to VIEW using temporary table algorithm 362-- error ER_NON_INSERTABLE_TABLE 363insert into v5 values (-60,4,30); 364insert into v1 values (-60,4,30); 365insert into v1 (z,y,x) values (50,6,-100); 366insert into v2 values (5,40); 367select * from t1; 368drop table t1; 369drop view v1,v2,v3,v4,v5; 370 371# 372# insert ... select 373# 374create table t1 (a int, b int, c int, primary key(a,b)); 375insert into t1 values (10,2,-1), (20,3,-2); 376create table t2 (a int, b int, c int, primary key(a,b)); 377insert into t2 values (30,4,-60); 378create view v1 (x,y,z) as select c, b, a from t1; 379create view v2 (x,y) as select b, a from t1; 380create view v3 (x,y,z) as select b, a, b from t1; 381create view v4 (x,y,z) as select c+1, b, a from t1; 382create algorithm=temptable view v5 (x,y,z) as select c, b, a from t1; 383# try insert to VIEW with fields duplicate 384-- error ER_NON_INSERTABLE_TABLE 385insert into v3 select c, b, a from t2; 386# try insert to VIEW with expression in SELECT list 387-- error ER_NON_INSERTABLE_TABLE 388insert into v4 select c, b, a from t2; 389# try insert to VIEW using temporary table algorithm 390-- error ER_NON_INSERTABLE_TABLE 391insert into v5 select c, b, a from t2; 392insert into v1 select c, b, a from t2; 393insert into v1 (z,y,x) select a+20,b+2,-100 from t2; 394insert into v2 select b+1, a+10 from t2; 395select * from t1; 396drop table t1, t2; 397drop view v1,v2,v3,v4,v5; 398 399# 400# outer join based on VIEW with WHERE clause 401# 402create table t1 (a int, primary key(a)); 403insert into t1 values (1), (2), (3); 404create view v1 (x) as select a from t1 where a > 1; 405select t1.a, v1.x from t1 left join v1 on (t1.a= v1.x); 406drop table t1; 407drop view v1; 408 409# 410# merging WHERE condition on VIEW on VIEW 411# 412create table t1 (a int, primary key(a)); 413insert into t1 values (1), (2), (3), (200); 414create view v1 (x) as select a from t1 where a > 1; 415create view v2 (y) as select x from v1 where x < 100; 416select * from v2; 417drop table t1; 418drop view v1,v2; 419 420# 421# VIEW on non-updatable view 422# 423create table t1 (a int, primary key(a)); 424insert into t1 values (1), (2), (3), (200); 425create ALGORITHM=TEMPTABLE view v1 (x) as select a from t1; 426create view v2 (y) as select x from v1; 427-- error ER_NON_UPDATABLE_TABLE 428update v2 set y=10 where y=2; 429drop table t1; 430drop view v1,v2; 431 432# 433# auto_increment field out of VIEW 434# 435create table t1 (a int not null auto_increment, b int not null, primary key(a), unique(b)); 436create view v1 (x) as select b from t1; 437insert into v1 values (1); 438select last_insert_id(); 439insert into t1 (b) values (2); 440select last_insert_id(); 441select * from t1; 442drop view v1; 443drop table t1; 444 445# 446# VIEW fields quoting 447# 448set sql_mode='ansi'; 449create table t1 ("a*b" int); 450create view v1 as select "a*b" from t1; 451show create view v1; 452drop view v1; 453drop table t1; 454set sql_mode=default; 455 456# 457# VIEW without tables 458# 459create table t1 (t_column int); 460create view v1 as select 'a'; 461select * from v1, t1; 462drop view v1; 463drop table t1; 464 465# 466# quote mark inside table name 467# 468create table `t1a``b` (col1 char(2)); 469create view v1 as select * from `t1a``b`; 470select * from v1; 471describe v1; 472drop view v1; 473drop table `t1a``b`; 474 475# 476# Changing of underlying table 477# 478create table t1 (col1 char(5),col2 char(5)); 479create view v1 as select * from t1; 480drop table t1; 481create table t1 (col1 char(5),newcol2 char(5)); 482-- error ER_VIEW_INVALID 483insert into v1 values('a','aa'); 484drop table t1; 485-- error ER_VIEW_INVALID 486select * from v1; 487drop view v1; 488 489# 490# check of duplication of column names 491# 492-- error ER_DUP_FIELDNAME 493create view v1 (a,a) as select 'a','a'; 494 495# 496# updatablity should be transitive 497# 498create table t1 (col1 int,col2 char(22)); 499insert into t1 values(5,'Hello, world of views'); 500create view v1 as select * from t1; 501create view v2 as select * from v1; 502update v2 set col2='Hello, view world'; 503select is_updatable from information_schema.views; 504select * from t1; 505drop view v2, v1; 506drop table t1; 507 508# 509# check 'use index' on view with temporary table 510# 511create table t1 (a int, b int); 512create view v1 as select a, sum(b) from t1 group by a; 513--error ER_KEY_DOES_NOT_EXISTS 514select b from v1 use index (some_index) where b=1; 515drop view v1; 516drop table t1; 517 518# 519# using VIEW fields several times in query resolved via temporary tables 520# 521create table t1 (col1 char(5),col2 char(5)); 522create view v1 (col1,col2) as select col1,col2 from t1; 523insert into v1 values('s1','p1'),('s1','p2'),('s1','p3'),('s1','p4'),('s2','p1'),('s3','p2'),('s4','p4'); 524select distinct first.col2 from t1 first where first.col2 in (select second.col2 from t1 second where second.col1<>first.col1); 525select distinct first.col2 from v1 first where first.col2 in (select second.col2 from t1 second where second.col1<>first.col1); 526drop view v1; 527drop table t1; 528 529# 530# Test of view updatability in prepared statement 531# 532create table t1 (a int); 533create view v1 as select a from t1; 534insert into t1 values (1); 535 536#update 537SET @v0 = '2'; 538PREPARE stmt FROM 'UPDATE v1 SET a = ?'; 539EXECUTE stmt USING @v0; 540DEALLOCATE PREPARE stmt; 541 542#insert without field list 543SET @v0 = '3'; 544PREPARE stmt FROM 'insert into v1 values (?)'; 545EXECUTE stmt USING @v0; 546DEALLOCATE PREPARE stmt; 547 548#insert with field list 549SET @v0 = '4'; 550PREPARE stmt FROM 'insert into v1 (a) values (?)'; 551EXECUTE stmt USING @v0; 552DEALLOCATE PREPARE stmt; 553 554select * from t1; 555 556drop view v1; 557drop table t1; 558 559# 560# error on preparation 561# 562-- error ER_NO_TABLES_USED 563CREATE VIEW v02 AS SELECT * FROM DUAL; 564SHOW TABLES; 565 566# 567# EXISTS with UNION VIEW 568# 569CREATE VIEW v1 AS SELECT EXISTS (SELECT 1 UNION SELECT 2); 570select * from v1; 571drop view v1; 572 573# 574# using VIEW where table is required 575# 576create table t1 (col1 int,col2 char(22)); 577create view v1 as select * from t1; 578-- error ER_WRONG_OBJECT 579create index i1 on v1 (col1); 580drop view v1; 581drop table t1; 582 583# 584# connection_id(), pi(), current_user(), version() representation test 585# 586CREATE VIEW v1 (f1,f2,f3,f4) AS SELECT connection_id(), pi(), current_user(), version(); 587SHOW CREATE VIEW v1; 588drop view v1; 589 590# 591# VIEW built over UNION 592# 593create table t1 (s1 int); 594create table t2 (s2 int); 595insert into t1 values (1), (2); 596insert into t2 values (2), (3); 597create view v1 as select * from t1,t2 union all select * from t1,t2; 598select * from v1; 599drop view v1; 600drop tables t1, t2; 601 602# 603# Aggregate functions in view list 604# 605create table t1 (col1 int); 606insert into t1 values (1); 607create view v1 as select count(*) from t1; 608insert into t1 values (null); 609select * from v1; 610drop view v1; 611drop table t1; 612 613# 614# Showing VIEW with VIEWs in subquery 615# 616create table t1 (a int); 617create table t2 (a int); 618create view v1 as select a from t1; 619create view v2 as select a from t2 where a in (select a from v1); 620show create view v2; 621drop view v2, v1; 622drop table t1, t2; 623 624# 625# SHOW VIEW view with name with spaces 626# 627CREATE VIEW `v 1` AS select 5 AS `5`; 628show create view `v 1`; 629drop view `v 1`; 630 631# 632# Removing database with .frm archives 633# 634create database mysqltest; 635create table mysqltest.t1 (a int, b int); 636create view mysqltest.v1 as select a from mysqltest.t1; 637alter view mysqltest.v1 as select b from mysqltest.t1; 638alter view mysqltest.v1 as select a from mysqltest.t1; 639drop database mysqltest; 640 641# 642# VIEW with full text 643# 644CREATE TABLE t1 (c1 int not null auto_increment primary key, c2 varchar(20), fulltext(c2)); 645insert into t1 (c2) VALUES ('real Beer'),('Water'),('Kossu'),('Coca-Cola'),('Vodka'),('Wine'),('almost real Beer'); 646select * from t1 WHERE match (c2) against ('Beer'); 647CREATE VIEW v1 AS SELECT * from t1 WHERE match (c2) against ('Beer'); 648select * from v1; 649drop view v1; 650drop table t1; 651 652# 653# distinct in temporary table with a VIEW 654# 655create table t1 (a int); 656insert into t1 values (1),(1),(2),(2),(3),(3); 657create view v1 as select a from t1; 658select distinct a from v1; 659select distinct a from v1 limit 2; 660select distinct a from t1 limit 2; 661prepare stmt1 from "select distinct a from v1 limit 2"; 662execute stmt1; 663execute stmt1; 664deallocate prepare stmt1; 665drop view v1; 666drop table t1; 667 668# 669# aggregate function of aggregate function 670# 671create table t1 (tg_column bigint); 672create view v1 as select count(tg_column) as vg_column from t1; 673select avg(vg_column) from v1; 674drop view v1; 675drop table t1; 676 677# 678# VIEW of VIEW with column renaming 679# 680create table t1 (col1 bigint not null, primary key (col1)); 681create table t2 (col1 bigint not null, key (col1)); 682create view v1 as select * from t1; 683create view v2 as select * from t2; 684insert into v1 values (1); 685insert into v2 values (1); 686create view v3 (a,b) as select v1.col1 as a, v2.col1 as b from v1, v2 where v1.col1 = v2.col1; 687select * from v3; 688show create view v3; 689drop view v3, v2, v1; 690drop table t2, t1; 691 692# 693# VIEW based on functions with complex names 694# 695create function `f``1` () returns int return 5; 696create view v1 as select test.`f``1` (); 697show create view v1; 698select * from v1; 699drop view v1; 700drop function `f``1`; 701 702# 703# tested problem when function name length close to ALIGN_SIZE 704# 705create function a() returns int return 5; 706create view v1 as select a(); 707select * from v1; 708drop view v1; 709drop function a; 710 711# 712# VIEW with collation 713# 714create table t2 (col1 char collate latin1_german2_ci); 715create view v2 as select col1 collate latin1_german1_ci from t2; 716show create view v2; 717show create view v2; 718drop view v2; 719drop table t2; 720 721# 722# order by refers on integer field 723# 724create table t1 (a int); 725insert into t1 values (1), (2); 726create view v1 as select 5 from t1 order by 1; 727show create view v1; 728select * from v1; 729drop view v1; 730drop table t1; 731 732# 733# VIEW over dropped function 734# 735create function x1 () returns int return 5; 736create table t1 (s1 int); 737create view v1 as select x1() from t1; 738drop function x1; 739-- error ER_VIEW_INVALID 740select * from v1; 741--replace_column 8 # 12 # 13 # 19 # 742show table status; 743drop view v1; 744drop table t1; 745 746# 747# VIEW over non-existing column 748# 749create table t1 (a varchar(20)); 750create view v1 as select a from t1; 751alter table t1 change a aa int; 752--error ER_VIEW_INVALID 753select * from v1; 754--replace_column 8 # 12 # 13 # 19 # 755show table status; 756show create view v1; 757drop view v1; 758drop table t1; 759 760 761# 762# VIEW with floating point (long number) as column 763# 764create view v1 as select 99999999999999999999999999999999999999999999999999999 as col1; 765show create view v1; 766drop view v1; 767 768# 769# VIEWs with national characters 770# 771 772SET @old_cs_client = @@character_set_client; 773SET @old_cs_results = @@character_set_results; 774SET @old_cs_connection = @@character_set_connection; 775 776set names utf8; 777create table tü (cü char); 778create view vü as select cü from tü; 779insert into vü values ('ü'); 780select * from vü; 781drop view vü; 782drop table tü; 783 784SET character_set_client = @old_cs_client; 785SET character_set_results = @old_cs_results; 786SET character_set_connection = @old_cs_connection; 787 788# 789# problem with used_tables() of outer reference resolved in VIEW 790# 791create table t1 (a int, b int); 792insert into t1 values (1,2), (1,3), (2,4), (2,5), (3,10); 793create view v1(c) as select a+1 from t1 where b >= 4; 794select c from v1 where exists (select * from t1 where a=2 and b=c); 795drop view v1; 796drop table t1; 797 798# 799# view with cast operation 800# 801create view v1 as select cast(1 as char(3)); 802show create view v1; 803select * from v1; 804drop view v1; 805 806# 807# renaming views 808# 809create table t1 (a int); 810create view v1 as select a from t1; 811create view v3 as select a from t1; 812create database mysqltest; 813-- error ER_FORBID_SCHEMA_CHANGE 814rename table v1 to mysqltest.v1; 815rename table v1 to v2; 816--error ER_TABLE_EXISTS_ERROR 817rename table v3 to v1, v2 to t1; 818drop table t1; 819drop view v2,v3; 820drop database mysqltest; 821 822# 823# bug handling from VIEWs 824# 825create view v1 as select 'a',1; 826create view v2 as select * from v1 union all select * from v1; 827create view v3 as select * from v2 where 1 = (select `1` from v2); 828create view v4 as select * from v3; 829-- error ER_SUBQUERY_NO_1_ROW 830select * from v4; 831drop view v4, v3, v2, v1; 832 833# 834# VIEW over SELECT with prohibited clauses 835# 836-- error ER_PARSE_ERROR 837create view v1 as select 5 into @w; 838-- error ER_PARSE_ERROR 839create view v1 as select 5 into outfile 'ttt'; 840create table t1 (a int); 841-- error ER_PARSE_ERROR 842create view v1 as select a from t1 procedure analyse(); 843# now derived tables are allowed 844create view v1 as select 1 from (select 1) as d1; 845drop view v1; 846drop table t1; 847 848# 849# INSERT into VIEW with ON DUPLICATE 850# 851create table t1 (s1 int, primary key (s1)); 852create view v1 as select * from t1; 853insert into v1 values (1) on duplicate key update s1 = 7; 854insert into v1 values (1) on duplicate key update s1 = 7; 855select * from t1; 856drop view v1; 857drop table t1; 858 859# 860# test of updating and fetching from the same table check 861# 862create table t1 (col1 int); 863create table t2 (col1 int); 864create table t3 (col1 datetime not null); 865create view v1 as select * from t1; 866create view v2 as select * from v1; 867create view v3 as select v2.col1 from v2,t2 where v2.col1 = t2.col1; 868-- error ER_VIEW_PREVENT_UPDATE 869insert into v2 values ((select max(col1) from v1)); 870-- error ER_VIEW_PREVENT_UPDATE 871insert into t1 values ((select max(col1) from v1)); 872-- error ER_VIEW_PREVENT_UPDATE 873insert into v2 values ((select max(col1) from v1)); 874-- error ER_VIEW_PREVENT_UPDATE 875insert into v2 values ((select max(col1) from t1)); 876-- error ER_UPDATE_TABLE_USED 877insert into t1 values ((select max(col1) from t1)); 878-- error ER_VIEW_PREVENT_UPDATE 879insert into v2 values ((select max(col1) from t1)); 880-- error ER_UPDATE_TABLE_USED 881insert into v2 values ((select max(col1) from v2)); 882-- error ER_VIEW_PREVENT_UPDATE 883insert into t1 values ((select max(col1) from v2)); 884-- error ER_UPDATE_TABLE_USED 885insert into v2 values ((select max(col1) from v2)); 886-- error ER_VIEW_PREVENT_UPDATE 887insert into v3 (col1) values ((select max(col1) from v1)); 888-- error ER_VIEW_PREVENT_UPDATE 889insert into v3 (col1) values ((select max(col1) from t1)); 890-- error ER_VIEW_PREVENT_UPDATE 891insert into v3 (col1) values ((select max(col1) from v2)); 892# check with TZ tables in list 893-- error ER_VIEW_PREVENT_UPDATE 894insert into v3 (col1) values ((select CONVERT_TZ('20050101000000','UTC','MET') from v2)); 895insert into v3 (col1) values ((select CONVERT_TZ('20050101000000','UTC','MET') from t2)); 896-- error ER_BAD_NULL_ERROR 897insert into t3 values ((select CONVERT_TZ('20050101000000','UTC','MET') from t2)); 898# temporary table algorithm view should be equal to subquery in the from clause 899create algorithm=temptable view v4 as select * from t1; 900insert into t1 values (1),(2),(3); 901insert into t1 (col1) values ((select max(col1) from v4)); 902select * from t1; 903 904drop view v4,v3,v2,v1; 905drop table t1,t2,t3; 906 907# 908# HANDLER with VIEW 909# 910create table t1 (s1 int); 911create view v1 as select * from t1; 912-- error ER_WRONG_OBJECT 913handler v1 open as xx; 914drop view v1; 915drop table t1; 916 917# 918# view with WHERE in nested join 919# 920create table t1(a int); 921insert into t1 values (0), (1), (2), (3); 922create table t2 (a int); 923insert into t2 select a from t1 where a > 1; 924create view v1 as select a from t1 where a > 1; 925select * from t1 left join (t2 as t, v1) on v1.a=t1.a; 926select * from t1 left join (t2 as t, t2) on t2.a=t1.a; 927drop view v1; 928drop table t1, t2; 929 930# 931# Collation with view update 932# 933create table t1 (s1 char); 934create view v1 as select s1 collate latin1_german1_ci as s1 from t1; 935insert into v1 values ('a'); 936select * from v1; 937update v1 set s1='b'; 938select * from v1; 939update v1,t1 set v1.s1='c' where t1.s1=v1.s1; 940select * from v1; 941prepare stmt1 from "update v1,t1 set v1.s1=? where t1.s1=v1.s1"; 942set @arg='d'; 943execute stmt1 using @arg; 944select * from v1; 945set @arg='e'; 946execute stmt1 using @arg; 947select * from v1; 948deallocate prepare stmt1; 949drop view v1; 950drop table t1; 951 952# 953# test view with LOCK TABLES (work around) 954# 955create table t1 (a int); 956create table t2 (a int); 957create view v1 as select * from t1; 958lock tables t1 read, v1 read; 959select * from v1; 960-- error ER_TABLE_NOT_LOCKED 961select * from t2; 962unlock tables; 963drop view v1; 964drop table t1, t2; 965 966# 967# WITH CHECK OPTION insert/update test 968# 969create table t1 (a int); 970create view v1 as select * from t1 where a < 2 with check option; 971# simple insert 972insert into v1 values(1); 973-- error ER_VIEW_CHECK_FAILED 974insert into v1 values(3); 975# simple insert with ignore 976insert ignore into v1 values (2),(3),(0); 977select * from t1; 978# prepare data for next check 979delete from t1; 980# INSERT SELECT test 981insert into v1 SELECT 1; 982-- error ER_VIEW_CHECK_FAILED 983insert into v1 SELECT 3; 984# prepare data for next check 985create table t2 (a int); 986insert into t2 values (2),(3),(0); 987# INSERT SELECT with ignore test 988insert ignore into v1 SELECT a from t2; 989select * from t1 order by a desc; 990# simple UPDATE test 991update v1 set a=-1 where a=0; 992-- error ER_VIEW_CHECK_FAILED 993update v1 set a=2 where a=1; 994select * from t1 order by a desc; 995# prepare data for next check 996update v1 set a=0 where a=0; 997insert into t2 values (1); 998# multiupdate test 999update v1,t2 set v1.a=v1.a-1 where v1.a=t2.a; 1000select * from t1 order by a desc; 1001# prepare data for next check 1002update v1 set a=a+1; 1003# multiupdate with ignore test 1004update ignore v1,t2 set v1.a=v1.a+1 where v1.a=t2.a; 1005select * from t1; 1006 1007drop view v1; 1008drop table t1, t2; 1009 1010# 1011# CASCADED/LOCAL CHECK OPTION test 1012# 1013create table t1 (a int); 1014create view v1 as select * from t1 where a < 2 with check option; 1015create view v2 as select * from v1 where a > 0 with local check option; 1016create view v3 as select * from v1 where a > 0 with cascaded check option; 1017insert into v2 values (1); 1018insert into v3 values (1); 1019-- error ER_VIEW_CHECK_FAILED 1020insert into v2 values (0); 1021-- error ER_VIEW_CHECK_FAILED 1022insert into v3 values (0); 1023insert into v2 values (2); 1024-- error ER_VIEW_CHECK_FAILED 1025insert into v3 values (2); 1026select * from t1; 1027drop view v3,v2,v1; 1028drop table t1; 1029 1030# 1031# CHECK OPTION with INSERT ... ON DUPLICATE KEY UPDATE 1032# 1033create table t1 (a int, primary key (a)); 1034create view v1 as select * from t1 where a < 2 with check option; 1035insert into v1 values (1) on duplicate key update a=2; 1036-- error ER_VIEW_CHECK_FAILED 1037insert into v1 values (1) on duplicate key update a=2; 1038insert ignore into v1 values (1) on duplicate key update a=2; 1039select * from t1; 1040drop view v1; 1041drop table t1; 1042 1043# 1044# check cyclic referencing protection on altering view 1045# 1046create table t1 (s1 int); 1047create view v1 as select * from t1; 1048create view v2 as select * from v1; 1049-- error ER_NO_SUCH_TABLE 1050alter view v1 as select * from v2; 1051-- error ER_NO_SUCH_TABLE 1052alter view v1 as select * from v1; 1053-- error ER_NO_SUCH_TABLE 1054create or replace view v1 as select * from v2; 1055-- error ER_NO_SUCH_TABLE 1056create or replace view v1 as select * from v1; 1057drop view v2,v1; 1058drop table t1; 1059 1060# 1061# check altering differ options 1062# 1063create table t1 (a int); 1064create view v1 as select * from t1; 1065show create view v1; 1066alter algorithm=undefined view v1 as select * from t1 with check option; 1067show create view v1; 1068alter algorithm=merge view v1 as select * from t1 with cascaded check option; 1069show create view v1; 1070alter algorithm=temptable view v1 as select * from t1; 1071show create view v1; 1072drop view v1; 1073drop table t1; 1074 1075# 1076# updating view with subquery in the WHERE clause 1077# 1078create table t1 (s1 int); 1079create table t2 (s1 int); 1080create view v2 as select * from t2 where s1 in (select s1 from t1); 1081insert into v2 values (5); 1082insert into t1 values (5); 1083select * from v2; 1084update v2 set s1 = 0; 1085select * from v2; 1086select * from t2; 1087# check it with check option 1088alter view v2 as select * from t2 where s1 in (select s1 from t1) with check option; 1089insert into v2 values (5); 1090-- error ER_VIEW_CHECK_FAILED 1091update v2 set s1 = 1; 1092insert into t1 values (1); 1093update v2 set s1 = 1; 1094select * from v2; 1095select * from t2; 1096# scheck how VIEWs with subqueries work with prepared statements 1097prepare stmt1 from "select * from v2;"; 1098execute stmt1; 1099insert into t1 values (0); 1100execute stmt1; 1101deallocate prepare stmt1; 1102drop view v2; 1103drop table t1, t2; 1104 1105# 1106# test of substring_index with view 1107# 1108create table t1 (t time); 1109create view v1 as select substring_index(t,':',2) as t from t1; 1110insert into t1 (t) values ('12:24:10'); 1111select substring_index(t,':',2) from t1; 1112select substring_index(t,':',2) from v1; 1113drop view v1; 1114drop table t1; 1115 1116# 1117# test of cascaded check option for whiew without WHERE clause 1118# 1119create table t1 (s1 tinyint); 1120create view v1 as select * from t1 where s1 <> 0 with local check option; 1121create view v2 as select * from v1 with cascaded check option; 1122-- error ER_VIEW_CHECK_FAILED 1123insert into v2 values (0); 1124drop view v2, v1; 1125drop table t1; 1126 1127# 1128# inserting single value with check option failed always get error 1129# 1130create table t1 (s1 int); 1131create view v1 as select * from t1 where s1 < 5 with check option; 1132#single value 1133-- error ER_VIEW_CHECK_FAILED 1134insert ignore into v1 values (6); 1135#several values 1136insert ignore into v1 values (6),(3); 1137select * from t1; 1138drop view v1; 1139drop table t1; 1140 1141# 1142# changing value by trigger and CHECK OPTION 1143# 1144SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; 1145create table t1 (s1 tinyint); 1146create trigger t1_bi before insert on t1 for each row set new.s1 = 500; 1147create view v1 as select * from t1 where s1 <> 127 with check option; 1148-- error ER_VIEW_CHECK_FAILED 1149insert into v1 values (0); 1150select * from v1; 1151select * from t1; 1152drop trigger t1_bi; 1153drop view v1; 1154drop table t1; 1155SET sql_mode = default; 1156 1157# 1158# CASCADED should be used for all underlaying VIEWs 1159# 1160create table t1 (s1 tinyint); 1161create view v1 as select * from t1 where s1 <> 0; 1162create view v2 as select * from v1 where s1 <> 1 with cascaded check option; 1163-- error ER_VIEW_CHECK_FAILED 1164insert into v2 values (0); 1165select * from v2; 1166select * from t1; 1167drop view v2, v1; 1168drop table t1; 1169 1170# 1171# LOAD DATA with view and CHECK OPTION 1172# 1173# fixed length fields 1174create table t1 (a int, b char(10)); 1175create view v1 as select * from t1 where a != 0 with check option; 1176-- error ER_VIEW_CHECK_FAILED 1177load data infile '../../std_data/loaddata3.dat' into table v1 fields terminated by '' enclosed by '' ignore 1 lines; 1178select * from t1; 1179select * from v1; 1180delete from t1; 1181load data infile '../../std_data/loaddata3.dat' ignore into table v1 fields terminated by '' enclosed by '' ignore 1 lines; 1182select * from t1 order by a,b; 1183select * from v1 order by a,b; 1184drop view v1; 1185drop table t1; 1186# variable length fields 1187create table t1 (a text, b text); 1188create view v1 as select * from t1 where a <> 'Field A' with check option; 1189-- error ER_VIEW_CHECK_FAILED 1190load data infile '../../std_data/loaddata2.dat' into table v1 fields terminated by ',' enclosed by ''''; 1191select concat('|',a,'|'), concat('|',b,'|') from t1; 1192select concat('|',a,'|'), concat('|',b,'|') from v1; 1193delete from t1; 1194load data infile '../../std_data/loaddata2.dat' ignore into table v1 fields terminated by ',' enclosed by ''''; 1195select concat('|',a,'|'), concat('|',b,'|') from t1; 1196select concat('|',a,'|'), concat('|',b,'|') from v1; 1197drop view v1; 1198drop table t1; 1199 1200# 1201# Trys update table from which we select using views and subqueries 1202# 1203create table t1 (s1 smallint); 1204create view v1 as select * from t1 where 20 < (select (s1) from t1); 1205-- error ER_NON_INSERTABLE_TABLE 1206insert into v1 values (30); 1207create view v2 as select * from t1; 1208create view v3 as select * from t1 where 20 < (select (s1) from v2); 1209-- error ER_NON_INSERTABLE_TABLE 1210insert into v3 values (30); 1211create view v4 as select * from v2 where 20 < (select (s1) from t1); 1212-- error ER_NON_INSERTABLE_TABLE 1213insert into v4 values (30); 1214drop view v4, v3, v2, v1; 1215drop table t1; 1216 1217# 1218# CHECK TABLE with VIEW 1219# 1220create table t1 (a int); 1221create view v1 as select * from t1; 1222check table t1,v1; 1223check table v1,t1; 1224drop table t1; 1225check table v1; 1226drop view v1; 1227 1228# 1229# merge of VIEW with several tables 1230# 1231create table t1 (a int); 1232create table t2 (a int); 1233create table t3 (a int); 1234insert into t1 values (1), (2), (3); 1235insert into t2 values (1), (3); 1236insert into t3 values (1), (2), (4); 1237# view over tables 1238create view v3 (a,b) as select t1.a as a, t2.a as b from t1 left join t2 on (t1.a=t2.a); 1239select * from t3 left join v3 on (t3.a = v3.a); 1240explain extended select * from t3 left join v3 on (t3.a = v3.a); 1241# view over views 1242create view v1 (a) as select a from t1; 1243create view v2 (a) as select a from t2; 1244create view v4 (a,b) as select v1.a as a, v2.a as b from v1 left join v2 on (v1.a=v2.a); 1245select * from t3 left join v4 on (t3.a = v4.a); 1246explain extended select * from t3 left join v4 on (t3.a = v4.a); 1247# PS with view over views 1248prepare stmt1 from "select * from t3 left join v4 on (t3.a = v4.a);"; 1249execute stmt1; 1250execute stmt1; 1251deallocate prepare stmt1; 1252drop view v4,v3,v2,v1; 1253drop tables t1,t2,t3; 1254 1255# 1256# updating of join view 1257# 1258create table t1 (a int, primary key (a), b int); 1259create table t2 (a int, primary key (a)); 1260insert into t1 values (1,100), (2,200); 1261insert into t2 values (1), (3); 1262# legal view for update 1263create view v3 (a,b) as select t1.a as a, t2.a as b from t1, t2; 1264update v3 set a= 10 where a=1; 1265select * from t1; 1266select * from t2; 1267# view without primary key 1268create view v2 (a,b) as select t1.b as a, t2.a as b from t1, t2; 1269set updatable_views_with_limit=NO; 1270-- error ER_NON_UPDATABLE_TABLE 1271update v2 set a= 10 where a=200 limit 1; 1272set updatable_views_with_limit=DEFAULT; 1273# just view selects 1274select * from v3; 1275select * from v2; 1276# prepare statement with updating join view 1277set @a= 10; 1278set @b= 100; 1279prepare stmt1 from "update v3 set a= ? where a=?"; 1280execute stmt1 using @a,@b; 1281select * from v3; 1282set @a= 300; 1283set @b= 10; 1284execute stmt1 using @a,@b; 1285select * from v3; 1286deallocate prepare stmt1; 1287drop view v3,v2; 1288drop tables t1,t2; 1289 1290# 1291# inserting/deleting join view 1292# 1293create table t1 (a int, primary key (a), b int); 1294create table t2 (a int, primary key (a), b int); 1295insert into t2 values (1000, 2000); 1296create view v3 (a,b) as select t1.a as a, t2.a as b from t1, t2; 1297# inserting into join view without field list 1298-- error ER_VIEW_NO_INSERT_FIELD_LIST 1299insert into v3 values (1,2); 1300-- error ER_VIEW_NO_INSERT_FIELD_LIST 1301insert into v3 select * from t2; 1302# inserting in several tables of join view 1303-- error ER_VIEW_MULTIUPDATE 1304insert into v3(a,b) values (1,2); 1305-- error ER_VIEW_MULTIUPDATE 1306insert into v3(a,b) select * from t2; 1307# correct inserts into join view 1308insert into v3(a) values (1); 1309insert into v3(b) values (10); 1310insert into v3(a) select a from t2; 1311insert into v3(b) select b from t2; 1312insert into v3(a) values (1) on duplicate key update a=a+10000+VALUES(a); 1313select * from t1; 1314select * from t2; 1315# try delete from join view 1316-- error ER_VIEW_DELETE_MERGE_VIEW 1317delete from v3; 1318-- error ER_VIEW_DELETE_MERGE_VIEW 1319delete v3,t1 from v3,t1; 1320-- error ER_VIEW_DELETE_MERGE_VIEW 1321delete t1,v3 from t1,v3; 1322# delete from t1 just to reduce result set size 1323delete from t1; 1324# prepare statement with insert join view 1325prepare stmt1 from "insert into v3(a) values (?);"; 1326set @a= 100; 1327execute stmt1 using @a; 1328set @a= 300; 1329execute stmt1 using @a; 1330deallocate prepare stmt1; 1331prepare stmt1 from "insert into v3(a) select ?;"; 1332set @a= 101; 1333execute stmt1 using @a; 1334set @a= 301; 1335execute stmt1 using @a; 1336deallocate prepare stmt1; 1337--sorted_result 1338select * from v3; 1339 1340drop view v3; 1341drop tables t1,t2; 1342 1343# 1344# View field names should be case insensitive 1345# 1346create table t1(f1 int); 1347create view v1 as select f1 from t1; 1348select * from v1 where F1 = 1; 1349drop view v1; 1350drop table t1; 1351 1352# 1353# Resolving view fields in subqueries in VIEW (Bug#6394) 1354# 1355create table t1(c1 int); 1356create table t2(c2 int); 1357insert into t1 values (1),(2),(3); 1358insert into t2 values (1); 1359SELECT c1 FROM t1 WHERE c1 IN (SELECT c2 FROM t2); 1360SELECT c1 FROM t1 WHERE EXISTS (SELECT c2 FROM t2 WHERE c2 = c1); 1361create view v1 as SELECT c1 FROM t1 WHERE c1 IN (SELECT c2 FROM t2); 1362create view v2 as SELECT c1 FROM t1 WHERE EXISTS (SELECT c2 FROM t2 WHERE c2 = c1); 1363select * from v1; 1364select * from v2; 1365select * from (select c1 from v2) X; 1366drop view v2, v1; 1367drop table t1, t2; 1368 1369# 1370# view over other view setup (Bug#7433) 1371# 1372CREATE TABLE t1 (C1 INT, C2 INT); 1373CREATE TABLE t2 (C2 INT); 1374CREATE VIEW v1 AS SELECT C2 FROM t2; 1375CREATE VIEW v2 AS SELECT C1 FROM t1 LEFT OUTER JOIN v1 USING (C2); 1376SELECT * FROM v2; 1377drop view v2, v1; 1378drop table t1, t2; 1379 1380# 1381# view and group_concat() (Bug#7116) 1382# 1383create table t1 (col1 char(5),col2 int,col3 int); 1384insert into t1 values ('one',10,25), ('two',10,50), ('two',10,50), ('one',20,25), ('one',30,25); 1385create view v1 as select * from t1; 1386select col1,group_concat(col2,col3) from t1 group by col1; 1387select col1,group_concat(col2,col3) from v1 group by col1; 1388drop view v1; 1389drop table t1; 1390 1391# 1392# Item_ref resolved as view field (Bug#6894) 1393# 1394create table t1 (s1 int, s2 char); 1395create view v1 as select s1, s2 from t1; 1396-- error ER_BAD_FIELD_ERROR 1397select s2 from v1 vq1 where 2 = (select count(*) from v1 vq2 having vq1.s2 = vq2.s2); 1398select s2 from v1 vq1 where 2 = (select count(*) aa from v1 vq2 having vq1.s2 = aa); 1399drop view v1; 1400drop table t1; 1401 1402# 1403# Test case for Bug#9398 CREATE TABLE with SELECT from a multi-table view 1404# 1405CREATE TABLE t1 (a1 int); 1406CREATE TABLE t2 (a2 int); 1407INSERT INTO t1 VALUES (1), (2), (3), (4); 1408INSERT INTO t2 VALUES (1), (2), (3); 1409CREATE VIEW v1(a,b) AS SELECT a1,a2 FROM t1 JOIN t2 ON a1=a2 WHERE a1>1; 1410 1411SELECT * FROM v1; 1412CREATE TABLE t3 SELECT * FROM v1; 1413SELECT * FROM t3; 1414 1415DROP VIEW v1; 1416DROP TABLE t1,t2,t3; 1417 1418# 1419# Test for Bug#8703 insert into table select from view crashes 1420# 1421create table t1 (a int); 1422create table t2 like t1; 1423create table t3 like t1; 1424create view v1 as select t1.a x, t2.a y from t1 join t2 where t1.a=t2.a; 1425insert into t3 select x from v1; 1426insert into t2 select x from v1; 1427drop view v1; 1428drop table t1,t2,t3; 1429 1430# 1431# Test for Bug#6106 query over a view using subquery for the underlying table 1432# 1433 1434CREATE TABLE t1 (col1 int PRIMARY KEY, col2 varchar(10)); 1435INSERT INTO t1 VALUES(1,'trudy'); 1436INSERT INTO t1 VALUES(2,'peter'); 1437INSERT INTO t1 VALUES(3,'sanja'); 1438INSERT INTO t1 VALUES(4,'monty'); 1439INSERT INTO t1 VALUES(5,'david'); 1440INSERT INTO t1 VALUES(6,'kent'); 1441INSERT INTO t1 VALUES(7,'carsten'); 1442INSERT INTO t1 VALUES(8,'ranger'); 1443INSERT INTO t1 VALUES(10,'matt'); 1444CREATE TABLE t2 (col1 int, col2 int, col3 char(1)); 1445INSERT INTO t2 VALUES (1,1,'y'); 1446INSERT INTO t2 VALUES (1,2,'y'); 1447INSERT INTO t2 VALUES (2,1,'n'); 1448INSERT INTO t2 VALUES (3,1,'n'); 1449INSERT INTO t2 VALUES (4,1,'y'); 1450INSERT INTO t2 VALUES (4,2,'n'); 1451INSERT INTO t2 VALUES (4,3,'n'); 1452INSERT INTO t2 VALUES (6,1,'n'); 1453INSERT INTO t2 VALUES (8,1,'y'); 1454 1455CREATE VIEW v1 AS SELECT * FROM t1; 1456 1457SELECT a.col1,a.col2,b.col2,b.col3 1458 FROM t1 a LEFT JOIN t2 b ON a.col1=b.col1 1459 WHERE b.col2 IS NULL OR 1460 b.col2=(SELECT MAX(col2) FROM t2 b WHERE b.col1=a.col1); 1461 1462SELECT a.col1,a.col2,b.col2,b.col3 1463 FROM v1 a LEFT JOIN t2 b ON a.col1=b.col1 1464 WHERE b.col2 IS NULL OR 1465 b.col2=(SELECT MAX(col2) FROM t2 b WHERE b.col1=a.col1); 1466 1467CREATE VIEW v2 AS SELECT * FROM t2; 1468 1469SELECT a.col1,a.col2,b.col2,b.col3 1470 FROM v2 b RIGHT JOIN v1 a ON a.col1=b.col1 1471 WHERE b.col2 IS NULL OR 1472 b.col2=(SELECT MAX(col2) FROM v2 b WHERE b.col1=a.col1); 1473 1474# Tests from the report for Bug#6107 1475 1476SELECT a.col1,a.col2,b.col2,b.col3 1477 FROM v2 b RIGHT JOIN v1 a ON a.col1=b.col1 1478 WHERE a.col1 IN (1,5,9) AND 1479 (b.col2 IS NULL OR 1480 b.col2=(SELECT MAX(col2) FROM v2 b WHERE b.col1=a.col1)); 1481 1482CREATE VIEW v3 AS SELECT * FROM t1 WHERE col1 IN (1,5,9); 1483 1484SELECT a.col1,a.col2,b.col2,b.col3 1485 FROM v2 b RIGHT JOIN v3 a ON a.col1=b.col1 1486 WHERE b.col2 IS NULL OR 1487 b.col2=(SELECT MAX(col2) FROM v2 b WHERE b.col1=a.col1); 1488 1489DROP VIEW v1,v2,v3; 1490DROP TABLE t1,t2; 1491 1492# 1493# Bug#8490 Select from views containing subqueries causes server to hang 1494# forever. 1495# 1496create table t1 as select 1 A union select 2 union select 3; 1497create table t2 as select * from t1; 1498create view v1 as select * from t1 where a in (select * from t2); 1499select * from v1 A, v1 B where A.a = B.a; 1500create table t3 as select a a,a b from t2; 1501create view v2 as select * from t3 where 1502 a in (select * from t1) or b in (select * from t2); 1503select * from v2 A, v2 B where A.a = B.b; 1504drop view v1, v2; 1505drop table t1, t2, t3; 1506 1507# 1508# Test case for Bug#8528 select from view over multi-table view 1509# 1510CREATE TABLE t1 (a int); 1511CREATE TABLE t2 (b int); 1512INSERT INTO t1 VALUES (1), (2), (3), (4); 1513INSERT INTO t2 VALUES (4), (2); 1514 1515CREATE VIEW v1 AS SELECT * FROM t1,t2 WHERE t1.a=t2.b; 1516SELECT * FROM v1; 1517CREATE VIEW v2 AS SELECT * FROM v1; 1518SELECT * FROM v2; 1519 1520DROP VIEW v2,v1; 1521 1522DROP TABLE t1, t2; 1523# 1524# Correct restoring view name in SP table locking Bug#9758 1525# 1526create table t1 (a int); 1527create view v1 as select sum(a) from t1 group by a; 1528delimiter //; 1529create procedure p1() 1530begin 1531select * from v1; 1532end// 1533delimiter ;// 1534call p1(); 1535call p1(); 1536drop procedure p1; 1537drop view v1; 1538drop table t1; 1539 1540# 1541# Bug#7422 "order by" doesn't work 1542# 1543CREATE TABLE t1(a char(2) primary key, b char(2)); 1544CREATE TABLE t2(a char(2), b char(2), index i(a)); 1545INSERT INTO t1 VALUES ('a','1'), ('b','2'); 1546INSERT INTO t2 VALUES ('a','5'), ('a','6'), ('b','5'), ('b','6'); 1547CREATE VIEW v1 AS 1548 SELECT t1.b as c, t2.b as d FROM t1,t2 WHERE t1.a=t2.a; 1549SELECT d, c FROM v1 ORDER BY d,c; 1550DROP VIEW v1; 1551DROP TABLE t1, t2; 1552# 1553# using sum(distinct ) & avg(distinct ) in views (Bug#7015) 1554# 1555create table t1 (s1 int); 1556create view v1 as select sum(distinct s1) from t1; 1557select * from v1; 1558drop view v1; 1559create view v1 as select avg(distinct s1) from t1; 1560select * from v1; 1561drop view v1; 1562drop table t1; 1563 1564# 1565# using cast(... as decimal) in views (Bug#11387); 1566# 1567create view v1 as select cast(1 as decimal); 1568select * from v1; 1569drop view v1; 1570 1571# 1572# Bug#11298 insert into select from VIEW produces incorrect result when 1573# using ORDER BY 1574create table t1(f1 int); 1575create table t2(f2 int); 1576insert into t1 values(1),(2),(3); 1577insert into t2 values(1),(2),(3); 1578create view v1 as select * from t1,t2 where f1=f2; 1579create table t3 (f1 int, f2 int); 1580insert into t3 select * from v1 order by 1; 1581select * from t3; 1582drop view v1; 1583drop table t1,t2,t3; 1584 1585# 1586# Generation unique names for columns, and correct names check (Bug#7448) 1587# 1588# names with ' and \ 1589create view v1 as select '\\','\\shazam'; 1590select * from v1; 1591drop view v1; 1592create view v1 as select '\'','\shazam'; 1593select * from v1; 1594drop view v1; 1595# autogenerated names differ by case only 1596create view v1 as select 'k','K'; 1597select * from v1; 1598drop view v1; 1599create table t1 (s1 int); 1600# same autogenerated names 1601create view v1 as select s1, 's1' from t1; 1602select * from v1; 1603drop view v1; 1604create view v1 as select 's1', s1 from t1; 1605select * from v1; 1606drop view v1; 1607# set name as one of expected autogenerated 1608create view v1 as select 's1', s1, 1 as My_exp_s1 from t1; 1609select * from v1; 1610drop view v1; 1611create view v1 as select 1 as My_exp_s1, 's1', s1 from t1; 1612select * from v1; 1613drop view v1; 1614# set name conflict with autogenerated names 1615create view v1 as select 1 as s1, 's1', 's1' from t1; 1616select * from v1; 1617drop view v1; 1618create view v1 as select 's1', 's1', 1 as s1 from t1; 1619select * from v1; 1620drop view v1; 1621# underlying field name conflict with autogenerated names 1622create view v1 as select s1, 's1', 's1' from t1; 1623select * from v1; 1624drop view v1; 1625create view v1 as select 's1', 's1', s1 from t1; 1626select * from v1; 1627drop view v1; 1628# underlying field name conflict with set name 1629-- error ER_DUP_FIELDNAME 1630create view v1 as select 1 as s1, 's1', s1 from t1; 1631-- error ER_DUP_FIELDNAME 1632create view v1 as select 's1', s1, 1 as s1 from t1; 1633drop table t1; 1634# set names differ by case only 1635-- error ER_DUP_FIELDNAME 1636create view v1(k, K) as select 1,2; 1637 1638# 1639# using time_format in view (Bug#7521) 1640# 1641create view v1 as SELECT TIME_FORMAT(SEC_TO_TIME(3600),'%H:%i') as t; 1642select * from v1; 1643drop view v1; 1644 1645# 1646# evaluation constant functions in WHERE (Bug#4663) 1647# 1648create table t1 (a timestamp default now()); 1649create table t2 (b timestamp default now()); 1650create view v1 as select a,b,t1.a < now() from t1,t2 where t1.a < now(); 1651SHOW CREATE VIEW v1; 1652drop view v1; 1653drop table t1, t2; 1654CREATE TABLE t1 ( a varchar(50) ); 1655CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = CURRENT_USER(); 1656SHOW CREATE VIEW v1; 1657DROP VIEW v1; 1658CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = VERSION(); 1659SHOW CREATE VIEW v1; 1660DROP VIEW v1; 1661CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = DATABASE(); 1662SHOW CREATE VIEW v1; 1663DROP VIEW v1; 1664DROP TABLE t1; 1665 1666# 1667# checking views after some view with error (Bug#11337) 1668# 1669CREATE TABLE t1 (col1 time); 1670CREATE TABLE t2 (col1 time); 1671CREATE VIEW v1 AS SELECT CONVERT_TZ(col1,'GMT','MET') FROM t1; 1672CREATE VIEW v2 AS SELECT CONVERT_TZ(col1,'GMT','MET') FROM t2; 1673CREATE VIEW v3 AS SELECT CONVERT_TZ(col1,'GMT','MET') FROM t1; 1674CREATE VIEW v4 AS SELECT CONVERT_TZ(col1,'GMT','MET') FROM t2; 1675CREATE VIEW v5 AS SELECT CONVERT_TZ(col1,'GMT','MET') FROM t1; 1676CREATE VIEW v6 AS SELECT CONVERT_TZ(col1,'GMT','MET') FROM t2; 1677DROP TABLE t1; 1678CHECK TABLE v1, v2, v3, v4, v5, v6; 1679drop view v1, v2, v3, v4, v5, v6; 1680drop table t2; 1681 1682--disable_warnings 1683drop function if exists f1; 1684drop function if exists f2; 1685--enable_warnings 1686CREATE TABLE t1 (col1 time); 1687CREATE TABLE t2 (col1 time); 1688CREATE TABLE t3 (col1 time); 1689create function f1 () returns int return (select max(col1) from t1); 1690create function f2 () returns int return (select max(col1) from t2); 1691CREATE VIEW v1 AS SELECT f1() FROM t3; 1692CREATE VIEW v2 AS SELECT f2() FROM t3; 1693CREATE VIEW v3 AS SELECT f1() FROM t3; 1694CREATE VIEW v4 AS SELECT f2() FROM t3; 1695CREATE VIEW v5 AS SELECT f1() FROM t3; 1696CREATE VIEW v6 AS SELECT f2() FROM t3; 1697drop function f1; 1698CHECK TABLE v1, v2, v3, v4, v5, v6; 1699create function f1 () returns int return (select max(col1) from t1); 1700DROP TABLE t1; 1701CHECK TABLE v1, v2, v3, v4, v5, v6; 1702drop function f1; 1703drop function f2; 1704drop view v1, v2, v3, v4, v5, v6; 1705drop table t2,t3; 1706 1707# 1708# Bug#11325 Wrong date comparison in views 1709# 1710create table t1 (f1 date); 1711insert into t1 values ('2005-01-01'),('2005-02-02'); 1712create view v1 as select * from t1; 1713select * from v1 where f1='2005.02.02'; 1714select * from v1 where '2005.02.02'=f1; 1715drop view v1; 1716drop table t1; 1717 1718# 1719# using encrypt & substring_index in view (Bug#7024) 1720# 1721CREATE VIEW v1 AS SELECT ENCRYPT("dhgdhgd"); 1722disable_result_log; 1723SELECT * FROM v1; 1724enable_result_log; 1725drop view v1; 1726CREATE VIEW v1 AS SELECT SUBSTRING_INDEX("dkjhgd:kjhdjh", ":", 1); 1727SELECT * FROM v1; 1728drop view v1; 1729 1730# 1731# hide underlying tables names in case of imposibility to update (Bug#10773) 1732# 1733create table t1 (f59 int, f60 int, f61 int); 1734insert into t1 values (19,41,32); 1735create view v1 as select f59, f60 from t1 where f59 in 1736 (select f59 from t1); 1737-- error ER_NON_UPDATABLE_TABLE 1738update v1 set f60=2345; 1739drop view v1; 1740drop table t1; 1741 1742# 1743# Using var_samp with view (Bug#10651) 1744# 1745create table t1 (s1 int); 1746create view v1 as select var_samp(s1) from t1; 1747show create view v1; 1748drop view v1; 1749drop table t1; 1750 1751 1752# 1753# Correct inserting data check (absence of default value) for view 1754# underlying tables (Bug#6443) 1755# 1756set sql_mode='strict_all_tables'; 1757CREATE TABLE t1 (col1 INT NOT NULL, col2 INT NOT NULL); 1758CREATE VIEW v1 (vcol1) AS SELECT col1 FROM t1; 1759CREATE VIEW v2 (vcol1) AS SELECT col1 FROM t1 WHERE col2 > 2; 1760-- error ER_NO_DEFAULT_FOR_FIELD 1761INSERT INTO t1 (col1) VALUES(12); 1762-- error ER_NO_DEFAULT_FOR_VIEW_FIELD 1763INSERT INTO v1 (vcol1) VALUES(12); 1764-- error ER_NO_DEFAULT_FOR_VIEW_FIELD 1765INSERT INTO v2 (vcol1) VALUES(12); 1766set sql_mode=default; 1767drop view v2,v1; 1768drop table t1; 1769 1770 1771# 1772# Bug#11399 Use an alias in a select statement on a view 1773# 1774create table t1 (f1 int); 1775insert into t1 values (1); 1776create view v1 as select f1 from t1; 1777select f1 as alias from v1; 1778drop view v1; 1779drop table t1; 1780 1781 1782# 1783# Test for Bug#6120 SP cache to be invalidated when altering a view 1784# 1785 1786CREATE TABLE t1 (s1 int, s2 int); 1787INSERT INTO t1 VALUES (1,2); 1788CREATE VIEW v1 AS SELECT s2 AS s1, s1 AS s2 FROM t1; 1789SELECT * FROM v1; 1790CREATE PROCEDURE p1 () SELECT * FROM v1; 1791CALL p1(); 1792ALTER VIEW v1 AS SELECT s1 AS s1, s2 AS s2 FROM t1; 1793CALL p1(); 1794DROP VIEW v1; 1795CREATE VIEW v1 AS SELECT s2 AS s1, s1 AS s2 FROM t1; 1796CALL p1(); 1797 1798DROP PROCEDURE p1; 1799DROP VIEW v1; 1800DROP TABLE t1; 1801 1802 1803# 1804# Test for Bug#11709 View was ordered by wrong column 1805# 1806create table t1 (f1 int, f2 int); 1807create view v1 as select f1 as f3, f2 as f1 from t1; 1808insert into t1 values (1,3),(2,1),(3,2); 1809select * from v1 order by f1; 1810drop view v1; 1811drop table t1; 1812 1813 1814# 1815# Test for Bug#11771 wrong query_id in SELECT * FROM <view> 1816# 1817CREATE TABLE t1 (f1 char); 1818INSERT INTO t1 VALUES ('A'); 1819CREATE VIEW v1 AS SELECT * FROM t1; 1820 1821INSERT INTO t1 VALUES('B'); 1822SELECT * FROM v1; 1823SELECT * FROM t1; 1824 1825DROP VIEW v1; 1826DROP TABLE t1; 1827 1828 1829# 1830# opening table in correct locking mode (Bug#9597) 1831# 1832CREATE TABLE t1 ( bug_table_seq INTEGER NOT NULL); 1833CREATE OR REPLACE VIEW v1 AS SELECT * from t1; 1834DROP PROCEDURE IF EXISTS p1; 1835delimiter //; 1836CREATE PROCEDURE p1 ( ) 1837BEGIN 1838 DO (SELECT @next := IFNULL(max(bug_table_seq),0) + 1 FROM v1); 1839 INSERT INTO t1 VALUES (1); 1840END // 1841delimiter ;// 1842CALL p1(); 1843DROP PROCEDURE p1; 1844DROP VIEW v1; 1845DROP TABLE t1; 1846 1847 1848# 1849# Bug#11760 Typo in Item_func_add_time::print() results in NULLs returned 1850# subtime() in view 1851create table t1(f1 datetime); 1852insert into t1 values('2005.01.01 12:0:0'); 1853create view v1 as select f1, subtime(f1, '1:1:1') as sb from t1; 1854select * from v1; 1855drop view v1; 1856drop table t1; 1857 1858 1859# 1860# Test for Bug#11412 query over a multitable view with GROUP_CONCAT 1861# 1862CREATE TABLE t1 ( 1863 aid int PRIMARY KEY, 1864 fn varchar(20) NOT NULL, 1865 ln varchar(20) NOT NULL 1866); 1867CREATE TABLE t2 ( 1868 aid int NOT NULL, 1869 pid int NOT NULL 1870); 1871INSERT INTO t1 VALUES(1,'a','b'), (2,'c','d'); 1872INSERT INTO t2 values (1,1), (2,1), (2,2); 1873 1874CREATE VIEW v1 AS SELECT t1.*,t2.pid FROM t1,t2 WHERE t1.aid = t2.aid; 1875 1876SELECT pid,GROUP_CONCAT(CONCAT(fn,' ',ln) ORDER BY 1) FROM t1,t2 1877 WHERE t1.aid = t2.aid GROUP BY pid; 1878SELECT pid,GROUP_CONCAT(CONCAT(fn,' ',ln) ORDER BY 1) FROM v1 GROUP BY pid; 1879 1880DROP VIEW v1; 1881DROP TABLE t1,t2; 1882 1883 1884# 1885# Test for Bug#12382 SELECT * FROM view after INSERT command 1886# 1887 1888CREATE TABLE t1 (id int PRIMARY KEY, f varchar(255)); 1889CREATE VIEW v1 AS SELECT id, f FROM t1 WHERE id <= 2; 1890INSERT INTO t1 VALUES (2, 'foo2'); 1891INSERT INTO t1 VALUES (1, 'foo1'); 1892 1893SELECT * FROM v1; 1894SELECT * FROM v1; 1895 1896DROP VIEW v1; 1897DROP TABLE t1; 1898 1899 1900# 1901# Test for Bug#12470 crash for a simple select from a view defined 1902# as a join over 5 tables 1903 1904CREATE TABLE t1 (pk int PRIMARY KEY, b int); 1905CREATE TABLE t2 (pk int PRIMARY KEY, fk int, INDEX idx(fk)); 1906CREATE TABLE t3 (pk int PRIMARY KEY, fk int, INDEX idx(fk)); 1907CREATE TABLE t4 (pk int PRIMARY KEY, fk int, INDEX idx(fk)); 1908CREATE TABLE t5 (pk int PRIMARY KEY, fk int, INDEX idx(fk)); 1909CREATE VIEW v1 AS 1910 SELECT t1.pk as a FROM t1,t2,t3,t4,t5 1911 WHERE t1.b IS NULL AND 1912 t1.pk=t2.fk AND t2.pk=t3.fk AND t3.pk=t4.fk AND t4.pk=t5.fk; 1913 1914SELECT a FROM v1; 1915 1916DROP VIEW v1; 1917DROP TABLE t1,t2,t3,t4,t5; 1918 1919 1920# 1921# Bug#12298 Typo in function name results in erroneous view being created. 1922# 1923create view v1 as select timestampdiff(day,'1997-01-01 00:00:00','1997-01-02 00:00:00') as f1; 1924select * from v1; 1925drop view v1; 1926 1927# 1928# repeatable CREATE VIEW statement Bug#12468 1929# 1930create table t1(a int); 1931create procedure p1() create view v1 as select * from t1; 1932drop table t1; 1933-- error ER_NO_SUCH_TABLE 1934call p1(); 1935-- error ER_NO_SUCH_TABLE 1936call p1(); 1937drop procedure p1; 1938 1939 1940# 1941# Bug#10624 Views with multiple UNION and UNION ALL produce incorrect results 1942# 1943create table t1 (f1 int); 1944create table t2 (f1 int); 1945insert into t1 values (1); 1946insert into t2 values (2); 1947create view v1 as select * from t1 union select * from t2 union all select * from t2; 1948select * from v1; 1949drop view v1; 1950drop table t1,t2; 1951 1952 1953# 1954# Test for Bug#10970 view referring a temporary table indirectly 1955# 1956 1957CREATE TEMPORARY TABLE t1 (a int); 1958CREATE FUNCTION f1 () RETURNS int RETURN (SELECT COUNT(*) FROM t1); 1959-- error ER_VIEW_SELECT_TMPTABLE 1960CREATE VIEW v1 AS SELECT f1(); 1961 1962DROP FUNCTION f1; 1963DROP TABLE t1; 1964 1965 1966# 1967# Bug#12533 (crash on DESCRIBE <view> after renaming base table column) 1968# 1969--disable_warnings 1970DROP TABLE IF EXISTS t1; 1971DROP VIEW IF EXISTS v1; 1972--enable_warnings 1973 1974CREATE TABLE t1 (f4 CHAR(5)); 1975CREATE VIEW v1 AS SELECT * FROM t1; 1976DESCRIBE v1; 1977 1978ALTER TABLE t1 CHANGE COLUMN f4 f4x CHAR(5); 1979--error ER_VIEW_INVALID 1980DESCRIBE v1; 1981DROP TABLE t1; 1982DROP VIEW v1; 1983 1984 1985# 1986# Bug#12489 wrongly printed strcmp() function results in creation of broken 1987# view 1988create table t1 (f1 char); 1989create view v1 as select strcmp(f1,'a') from t1; 1990select * from v1; 1991drop view v1; 1992drop table t1; 1993 1994 1995# 1996# Bug#12922 if(sum(),...) with group from view returns wrong results 1997# 1998create table t1 (f1 int, f2 int,f3 int); 1999insert into t1 values (1,10,20),(2,0,0); 2000create view v1 as select * from t1; 2001select if(sum(f1)>1,f2,f3) from v1 group by f1; 2002drop view v1; 2003drop table t1; 2004 2005 2006# Bug#12941 2007# 2008create table t1 ( 2009 r_object_id char(16) NOT NULL, 2010 group_name varchar(32) NOT NULL 2011); 2012 2013create table t2 ( 2014 r_object_id char(16) NOT NULL, 2015 i_position int(11) NOT NULL, 2016 users_names varchar(32) default NULL 2017); 2018 2019create view v1 as select r_object_id, group_name from t1; 2020create view v2 as select r_object_id, i_position, users_names from t2; 2021 2022create unique index r_object_id on t1(r_object_id); 2023create index group_name on t1(group_name); 2024create unique index r_object_id_i_position on t2(r_object_id,i_position); 2025create index users_names on t2(users_names); 2026 2027insert into t1 values('120001a080000542','tstgroup1'); 2028insert into t2 values('120001a080000542',-1, 'guser01'); 2029insert into t2 values('120001a080000542',-2, 'guser02'); 2030 2031select v1.r_object_id, v2.users_names from v1, v2 2032where (v1.group_name='tstgroup1') and v2.r_object_id=v1.r_object_id 2033order by users_names; 2034 2035drop view v1, v2; 2036drop table t1, t2; 2037 2038 2039# 2040# Bug#6808 Views: CREATE VIEW v ... FROM t AS v fails 2041# 2042 2043create table t1 (s1 int); 2044create view abc as select * from t1 as abc; 2045drop table t1; 2046drop view abc; 2047 2048 2049# 2050# Bug#12993 View column rename broken in subselect 2051# 2052 2053flush status; 2054create table t1(f1 char(1)); 2055create view v1 as select * from t1; 2056select * from (select f1 as f2, f1 as f3 from v1) v where v.f2='a'; 2057--disable_ps_protocol 2058show status like "Created_tmp%"; 2059--enable_ps_protocol 2060drop view v1; 2061drop table t1; 2062 2063set @tmp=@@optimizer_switch; 2064set @@optimizer_switch='derived_merge=OFF'; 2065create table t1(f1 char(1)); 2066create view v1 as select * from t1; 2067select * from (select f1 as f2, f1 as f3 from v1) v where v.f2='a'; 2068--disable_ps_protocol 2069show status like "Created_tmp%"; 2070--enable_ps_protocol 2071drop view v1; 2072drop table t1; 2073set @@optimizer_switch=@tmp; 2074 2075# 2076# Bug#11416 Server crash if using a view that uses function convert_tz 2077# 2078create view v1 as SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET'); 2079select * from v1; 2080drop view v1; 2081 2082 2083# 2084# Bugs#12963, #13000 wrong creation of VIEW with DAYNAME, DAYOFWEEK, and WEEKDAY 2085# 2086 2087CREATE TABLE t1 (date DATE NOT NULL); 2088INSERT INTO t1 VALUES ('2005-09-06'); 2089 2090CREATE VIEW v1 AS SELECT DAYNAME(date) FROM t1; 2091SHOW CREATE VIEW v1; 2092 2093CREATE VIEW v2 AS SELECT DAYOFWEEK(date) FROM t1; 2094SHOW CREATE VIEW v2; 2095 2096CREATE VIEW v3 AS SELECT WEEKDAY(date) FROM t1; 2097SHOW CREATE VIEW v3; 2098 2099SELECT DAYNAME('2005-09-06'); 2100SELECT DAYNAME(date) FROM t1; 2101SELECT * FROM v1; 2102 2103SELECT DAYOFWEEK('2005-09-06'); 2104SELECT DAYOFWEEK(date) FROM t1; 2105SELECT * FROM v2; 2106 2107SELECT WEEKDAY('2005-09-06'); 2108SELECT WEEKDAY(date) FROM t1; 2109SELECT * FROM v3; 2110 2111DROP TABLE t1; 2112DROP VIEW v1, v2, v3; 2113 2114 2115# 2116# Bug#13411 crash when using non-qualified view column in HAVING clause 2117# 2118 2119CREATE TABLE t1 ( a int, b int ); 2120INSERT INTO t1 VALUES (1,1),(2,2),(3,3); 2121CREATE VIEW v1 AS SELECT a,b FROM t1; 2122SELECT t1.a FROM t1 GROUP BY t1.a HAVING a > 1; 2123SELECT v1.a FROM v1 GROUP BY v1.a HAVING a > 1; 2124 2125DROP VIEW v1; 2126DROP TABLE t1; 2127 2128 2129# 2130# Bug#13410 failed name resolution for qualified view column in HAVING 2131# 2132 2133CREATE TABLE t1 ( a int, b int ); 2134INSERT INTO t1 VALUES (1,1),(2,2),(3,3); 2135CREATE VIEW v1 AS SELECT a,b FROM t1; 2136SELECT t1.a FROM t1 GROUP BY t1.a HAVING t1.a > 1; 2137SELECT v1.a FROM v1 GROUP BY v1.a HAVING v1.a > 1; 2138SELECT t_1.a FROM t1 AS t_1 GROUP BY t_1.a HAVING t_1.a IN (1,2,3); 2139SELECT v_1.a FROM v1 AS v_1 GROUP BY v_1.a HAVING v_1.a IN (1,2,3); 2140 2141DROP VIEW v1; 2142DROP TABLE t1; 2143 2144 2145# 2146# Bug#13327 view wasn't using index for const condition 2147# 2148 2149CREATE TABLE t1 (a INT, b INT, INDEX(a,b)); 2150CREATE TABLE t2 LIKE t1; 2151CREATE TABLE t3 (a INT); 2152INSERT INTO t1 VALUES (1,1),(2,2),(3,3); 2153INSERT INTO t2 VALUES (1,1),(2,2),(3,3); 2154INSERT INTO t3 VALUES (1),(2),(3); 2155CREATE VIEW v1 AS SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a AND t1.b=t2.b; 2156CREATE VIEW v2 AS SELECT t3.* FROM t1,t3 WHERE t1.a=t3.a; 2157EXPLAIN SELECT t1.* FROM t1 JOIN t2 WHERE t1.a=t2.a AND t1.b=t2.b AND t1.a=1; 2158EXPLAIN SELECT * FROM v1 WHERE a=1; 2159EXPLAIN SELECT * FROM v2 WHERE a=1; 2160DROP VIEW v1,v2; 2161DROP TABLE t1,t2,t3; 2162 2163 2164# 2165# Bug#13622 Wrong view .frm created if some field's alias contain \n 2166# 2167create table t1 (f1 int); 2168create view v1 as select t1.f1 as '123 2169456' from t1; 2170select * from v1; 2171drop view v1; 2172drop table t1; 2173 2174 2175# Bug#14466 lost sort order in GROUP_CONCAT() in a view 2176# 2177create table t1 (f1 int, f2 int); 2178insert into t1 values(1,1),(1,2),(1,3); 2179create view v1 as select f1 ,group_concat(f2 order by f2 asc) from t1 group by f1; 2180create view v2 as select f1 ,group_concat(f2 order by f2 desc) from t1 group by f1; 2181select * from v1; 2182select * from v2; 2183drop view v1,v2; 2184drop table t1; 2185 2186 2187# 2188# Bug#14026 Crash on second PS execution when using views 2189# 2190create table t1 (x int, y int); 2191create table t2 (x int, y int, z int); 2192create table t3 (x int, y int, z int); 2193create table t4 (x int, y int, z int); 2194 2195create view v1 as 2196select t1.x 2197from ( 2198 (t1 join t2 on ((t1.y = t2.y))) 2199 join 2200 (t3 left join t4 on (t3.y = t4.y) and (t3.z = t4.z)) 2201); 2202 2203prepare stmt1 from "select count(*) from v1 where x = ?"; 2204set @parm1=1; 2205 2206execute stmt1 using @parm1; 2207execute stmt1 using @parm1; 2208drop view v1; 2209drop table t1,t2,t3,t4; 2210 2211 2212# 2213# Bug#14540 OPTIMIZE, ANALYZE, REPAIR applied to not a view 2214# 2215 2216CREATE TABLE t1(id INT); 2217CREATE VIEW v1 AS SELECT id FROM t1; 2218 2219OPTIMIZE TABLE v1; 2220ANALYZE TABLE v1; 2221REPAIR TABLE v1; 2222 2223DROP TABLE t1; 2224OPTIMIZE TABLE v1; 2225ANALYZE TABLE v1; 2226REPAIR TABLE v1; 2227 2228DROP VIEW v1; 2229 2230 2231# 2232# Bug#14719 Views DEFINER grammar is incorrect 2233# 2234 2235create definer = current_user() sql security invoker view v1 as select 1; 2236show create view v1; 2237drop view v1; 2238 2239create definer = current_user sql security invoker view v1 as select 1; 2240show create view v1; 2241drop view v1; 2242 2243 2244# 2245# Bug#14816 test_if_order_by_key() expected only Item_fields. 2246# 2247create table t1 (id INT, primary key(id)); 2248insert into t1 values (1),(2); 2249create view v1 as select * from t1; 2250explain select id from v1 order by id; 2251drop view v1; 2252drop table t1; 2253 2254 2255# 2256# Bug#14850 Item_ref's values wasn't updated 2257# 2258create table t1(f1 int, f2 int); 2259insert into t1 values (null, 10), (null,2); 2260select f1, sum(f2) from t1 group by f1; 2261create view v1 as select * from t1; 2262select f1, sum(f2) from v1 group by f1; 2263drop view v1; 2264drop table t1; 2265 2266 2267# 2268# Bug#14885 incorrect SOURCE in view created in a procedure 2269# TODO: here SOURCE string must be shown when it will be possible 2270# 2271--disable_warnings 2272drop procedure if exists p1; 2273--enable_warnings 2274delimiter //; 2275create procedure p1 () deterministic 2276begin 2277create view v1 as select 1; 2278end; 2279// 2280delimiter ;// 2281call p1(); 2282show create view v1; 2283drop view v1; 2284drop procedure p1; 2285 2286 2287# 2288# Bug#15096 using function with view for view creation 2289# 2290CREATE VIEW v1 AS SELECT 42 AS Meaning; 2291--disable_warnings 2292DROP FUNCTION IF EXISTS f1; 2293--enable_warnings 2294DELIMITER //; 2295CREATE FUNCTION f1() RETURNS INTEGER 2296BEGIN 2297 DECLARE retn INTEGER; 2298 SELECT Meaning FROM v1 INTO retn; 2299 RETURN retn; 2300END 2301// 2302DELIMITER ;// 2303CREATE VIEW v2 AS SELECT f1(); 2304select * from v2; 2305drop view v2,v1; 2306drop function f1; 2307 2308 2309# 2310# Bug#14861 aliased column names are not preserved. 2311# 2312create table t1 (id numeric, warehouse_id numeric); 2313create view v1 as select id from t1; 2314create view v2 as 2315select t1.warehouse_id, v1.id as receipt_id 2316from t1, v1 where t1.id = v1.id; 2317 2318insert into t1 (id, warehouse_id) values(3, 2); 2319insert into t1 (id, warehouse_id) values(4, 2); 2320insert into t1 (id, warehouse_id) values(5, 1); 2321 2322select v2.receipt_id as alias1, v2.receipt_id as alias2 from v2 2323order by v2.receipt_id; 2324 2325drop view v2, v1; 2326drop table t1; 2327 2328 2329# 2330# Bug#16016 MIN/MAX optimization for views 2331# 2332 2333CREATE TABLE t1 (a int PRIMARY KEY, b int); 2334INSERT INTO t1 VALUES (2,20), (3,10), (1,10), (0,30), (5,10); 2335 2336CREATE VIEW v1 AS SELECT * FROM t1; 2337 2338SELECT MAX(a) FROM t1; 2339SELECT MAX(a) FROM v1; 2340 2341EXPLAIN SELECT MAX(a) FROM t1; 2342EXPLAIN SELECT MAX(a) FROM v1; 2343 2344SELECT MIN(a) FROM t1; 2345SELECT MIN(a) FROM v1; 2346 2347EXPLAIN SELECT MIN(a) FROM t1; 2348EXPLAIN SELECT MIN(a) FROM v1; 2349 2350DROP VIEW v1; 2351DROP TABLE t1; 2352 2353 2354# 2355# Bug#16382 grouping name is resolved against a view column name 2356# which coincides with a select column name 2357 2358CREATE TABLE t1 (x varchar(10)); 2359INSERT INTO t1 VALUES (null), ('foo'), ('bar'), (null); 2360CREATE VIEW v1 AS SELECT * FROM t1; 2361 2362SELECT IF(x IS NULL, 'blank', 'not blank') FROM v1 GROUP BY x; 2363SELECT IF(x IS NULL, 'blank', 'not blank') AS x FROM t1 GROUP BY x; 2364SELECT IF(x IS NULL, 'blank', 'not blank') AS x FROM v1; 2365SELECT IF(x IS NULL, 'blank', 'not blank') AS y FROM v1 GROUP BY y; 2366SELECT IF(x IS NULL, 'blank', 'not blank') AS x FROM v1 GROUP BY x; 2367 2368DROP VIEW v1; 2369DROP TABLE t1; 2370 2371 2372# 2373# Bug#15943 mysql_next_result hangs on invalid SHOW CREATE VIEW 2374# 2375 2376--disable_ps_protocol 2377delimiter //; 2378drop table if exists t1; 2379drop view if exists v1; 2380create table t1 (id int); 2381create view v1 as select * from t1; 2382drop table t1; 2383show create view v1; 2384drop view v1; 2385// 2386delimiter ;// 2387--enable_ps_protocol 2388 2389 2390# 2391# Bug#17726 Not checked empty list caused endless loop 2392# 2393create table t1(f1 int, f2 int); 2394create view v1 as select ta.f1 as a, tb.f1 as b from t1 ta, t1 tb where ta.f1=tb 2395.f1 and ta.f2=tb.f2; 2396insert into t1 values(1,1),(2,2); 2397create view v2 as select * from v1 where a > 1 with local check option; 2398select * from v2; 2399update v2 set b=3 where a=2; 2400select * from v2; 2401drop view v2, v1; 2402drop table t1; 2403 2404 2405# 2406# Bug#18386 select from view over a table with ORDER BY view_col clause 2407# given view_col is not an image of any column from the base table 2408 2409CREATE TABLE t1 (a int); 2410INSERT INTO t1 VALUES (1), (2); 2411 2412CREATE VIEW v1 AS SELECT SQRT(a) my_sqrt FROM t1; 2413 2414SELECT my_sqrt FROM v1 ORDER BY my_sqrt; 2415 2416DROP VIEW v1; 2417DROP TABLE t1; 2418 2419 2420# 2421# Bug#18237 invalid count optimization applied to an outer join with a view 2422# 2423 2424CREATE TABLE t1 (id int PRIMARY KEY); 2425CREATE TABLE t2 (id int PRIMARY KEY); 2426 2427INSERT INTO t1 VALUES (1), (3); 2428INSERT INTO t2 VALUES (1), (2), (3); 2429 2430CREATE VIEW v2 AS SELECT * FROM t2; 2431 2432SELECT COUNT(*) FROM t1 LEFT JOIN t2 ON t1.id=t2.id; 2433SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id; 2434 2435SELECT COUNT(*) FROM t1 LEFT JOIN v2 ON t1.id=v2.id; 2436 2437DROP VIEW v2; 2438 2439DROP TABLE t1, t2; 2440 2441 2442# 2443# Bug#16069 VIEW does return the same results as underlying SELECT 2444# with WHERE condition containing BETWEEN over dates 2445# Dates as strings should be casted to date type 2446 2447CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, 2448 td date DEFAULT NULL, KEY idx(td)); 2449 2450INSERT INTO t1 VALUES 2451 (1, '2005-01-01'), (2, '2005-01-02'), (3, '2005-01-02'), 2452 (4, '2005-01-03'), (5, '2005-01-04'), (6, '2005-01-05'), 2453 (7, '2005-01-05'), (8, '2005-01-05'), (9, '2005-01-06'); 2454 2455CREATE VIEW v1 AS SELECT * FROM t1; 2456 2457SELECT * FROM t1 WHERE td BETWEEN CAST('2005.01.02' AS DATE) AND CAST('2005.01.04' AS DATE); 2458SELECT * FROM v1 WHERE td BETWEEN CAST('2005.01.02' AS DATE) AND CAST('2005.01.04' AS DATE); 2459 2460DROP VIEW v1; 2461DROP TABLE t1; 2462 2463 2464# 2465# Bug#14308 Recursive view definitions 2466# 2467# using view only 2468create table t1 (a int); 2469create view v1 as select * from t1; 2470create view v2 as select * from v1; 2471drop table t1; 2472rename table v2 to t1; 2473-- error ER_VIEW_RECURSIVE 2474select * from v1; 2475drop view t1, v1; 2476# using SP function 2477create table t1 (a int); 2478delimiter //; 2479create function f1() returns int 2480begin 2481 declare mx int; 2482 select max(a) from t1 into mx; 2483 return mx; 2484end// 2485delimiter ;// 2486create view v1 as select f1() as a; 2487create view v2 as select * from v1; 2488drop table t1; 2489rename table v2 to t1; 2490-- error ER_SP_NO_RECURSION 2491select * from v1; 2492drop function f1; 2493drop view t1, v1; 2494 2495 2496# 2497# Bug#15153 CONVERT_TZ() is not allowed in all places in VIEWs 2498# 2499# Error was reported when one tried to use CONVERT_TZ() function 2500# select list of view which was processed using MERGE algorithm. 2501# (Also see additional test in timezone_grant.test) 2502create table t1 (dt datetime); 2503insert into t1 values (20040101000000), (20050101000000), (20060101000000); 2504# Let us test that convert_tz() can be used in view's select list 2505create view v1 as select convert_tz(dt, 'UTC', 'Europe/Moscow') as ldt from t1; 2506select * from v1; 2507drop view v1; 2508# And in its where part 2509create view v1 as select * from t1 where convert_tz(dt, 'UTC', 'Europe/Moscow') >= 20050101000000; 2510select * from v1; 2511# Other interesting case - a view which uses convert_tz() function 2512# through other view. 2513create view v2 as select * from v1 where dt < 20060101000000; 2514select * from v2; 2515drop view v2; 2516# And even more interesting case when view uses convert_tz() both 2517# directly and indirectly 2518create view v2 as select convert_tz(dt, 'UTC', 'Europe/Moscow') as ldt from v1; 2519select * from v2; 2520drop view v1, v2; 2521drop table t1; 2522 2523 2524# 2525# Bug#19490 usage of view specified by a query with GROUP BY 2526# an expression containing non-constant interval 2527 2528CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, d datetime); 2529 2530CREATE VIEW v1 AS 2531SELECT id, date(d) + INTERVAL TIME_TO_SEC(d) SECOND AS t, COUNT(*) 2532 FROM t1 GROUP BY id, t; 2533 2534SHOW CREATE VIEW v1; 2535SELECT * FROM v1; 2536 2537DROP VIEW v1; 2538DROP TABLE t1; 2539 2540 2541# 2542# Bug#19077 A nested materialized view is used before being populated. 2543# 2544CREATE TABLE t1 (i INT, j BIGINT); 2545INSERT INTO t1 VALUES (1, 2), (2, 2), (3, 2); 2546CREATE VIEW v1 AS SELECT MIN(j) AS j FROM t1; 2547CREATE VIEW v2 AS SELECT MIN(i) FROM t1 WHERE j = ( SELECT * FROM v1 ); 2548SELECT * FROM v2; 2549DROP VIEW v2, v1; 2550DROP TABLE t1; 2551 2552 2553# 2554# Bug#19573 VIEW with HAVING that refers an alias name 2555# 2556 2557CREATE TABLE t1( 2558 fName varchar(25) NOT NULL, 2559 lName varchar(25) NOT NULL, 2560 DOB date NOT NULL, 2561 test_date date NOT NULL, 2562 uID int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY); 2563 2564INSERT INTO t1(fName, lName, DOB, test_date) VALUES 2565 ('Hank', 'Hill', '1964-09-29', '2007-01-01'), 2566 ('Tom', 'Adams', '1908-02-14', '2007-01-01'), 2567 ('Homer', 'Simpson', '1968-03-05', '2007-01-01'); 2568 2569CREATE VIEW v1 AS 2570 SELECT (year(test_date)-year(DOB)) AS Age 2571 FROM t1 HAVING Age < 75; 2572SHOW CREATE VIEW v1; 2573 2574SELECT (year(test_date)-year(DOB)) AS Age FROM t1 HAVING Age < 75; 2575SELECT * FROM v1; 2576 2577DROP VIEW v1; 2578DROP TABLE t1; 2579 2580 2581# 2582# Bug#19089 wrong inherited dafault values in temp table views 2583# 2584 2585CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, a char(6) DEFAULT 'xxx'); 2586INSERT INTO t1(id) VALUES (1), (2), (3), (4); 2587INSERT INTO t1 VALUES (5,'yyy'), (6,'yyy'); 2588SELECT * FROM t1; 2589 2590CREATE VIEW v1(a, m) AS SELECT a, MIN(id) FROM t1 GROUP BY a; 2591SELECT * FROM v1; 2592 2593CREATE TABLE t2 SELECT * FROM v1; 2594INSERT INTO t2(m) VALUES (0); 2595SELECT * FROM t2; 2596 2597DROP VIEW v1; 2598DROP TABLE t1,t2; 2599 2600CREATE TABLE t1 (id int PRIMARY KEY, e ENUM('a','b') NOT NULL DEFAULT 'b'); 2601INSERT INTO t1(id) VALUES (1), (2), (3); 2602INSERT INTO t1 VALUES (4,'a'); 2603SELECT * FROM t1; 2604 2605CREATE VIEW v1(m, e) AS SELECT MIN(id), e FROM t1 GROUP BY e; 2606CREATE TABLE t2 SELECT * FROM v1; 2607SELECT * FROM t2; 2608 2609DROP VIEW v1; 2610DROP TABLE t1,t2; 2611 2612 2613# 2614# Bug#16110 insert permitted into view col w/o default value 2615# 2616CREATE TABLE t1 (a INT NOT NULL, b INT NULL DEFAULT NULL); 2617CREATE VIEW v1 AS SELECT a, b FROM t1; 2618 2619INSERT IGNORE INTO v1 (b) VALUES (2); 2620 2621SET SQL_MODE = STRICT_ALL_TABLES; 2622--error ER_NO_DEFAULT_FOR_VIEW_FIELD 2623INSERT INTO v1 (b) VALUES (4); 2624SET SQL_MODE = ''; 2625 2626SELECT * FROM t1; 2627 2628DROP VIEW v1; 2629DROP TABLE t1; 2630 2631 2632# 2633# Bug#18243 expression over a view column that with the REVERSE function 2634# 2635 2636CREATE TABLE t1 (firstname text, surname text); 2637INSERT INTO t1 VALUES 2638 ("Bart","Simpson"),("Milhouse","van Houten"),("Montgomery","Burns"); 2639 2640CREATE VIEW v1 AS SELECT CONCAT(firstname," ",surname) AS name FROM t1; 2641SELECT CONCAT(LEFT(name,LENGTH(name)-INSTR(REVERSE(name)," ")), 2642 LEFT(name,LENGTH(name)-INSTR(REVERSE(name)," "))) AS f1 2643 FROM v1; 2644 2645DROP VIEW v1; 2646DROP TABLE t1; 2647 2648 2649# 2650# Bug#19714 wrong type of a view column specified by an expressions over ints 2651# 2652 2653CREATE TABLE t1 (i int, j int); 2654CREATE VIEW v1 AS SELECT COALESCE(i,j) FROM t1; 2655DESCRIBE v1; 2656CREATE TABLE t2 SELECT COALESCE(i,j) FROM t1; 2657DESCRIBE t2; 2658 2659DROP VIEW v1; 2660DROP TABLE t1,t2; 2661 2662 2663# 2664# Bug#17526 views with TRIM functions 2665# 2666 2667CREATE TABLE t1 (s varchar(10)); 2668INSERT INTO t1 VALUES ('yadda'), ('yady'); 2669 2670SELECT TRIM(BOTH 'y' FROM s) FROM t1; 2671CREATE VIEW v1 AS SELECT TRIM(BOTH 'y' FROM s) FROM t1; 2672SELECT * FROM v1; 2673DROP VIEW v1; 2674 2675SELECT TRIM(LEADING 'y' FROM s) FROM t1; 2676CREATE VIEW v1 AS SELECT TRIM(LEADING 'y' FROM s) FROM t1; 2677SELECT * FROM v1; 2678DROP VIEW v1; 2679 2680SELECT TRIM(TRAILING 'y' FROM s) FROM t1; 2681CREATE VIEW v1 AS SELECT TRIM(TRAILING 'y' FROM s) FROM t1; 2682SELECT * FROM v1; 2683DROP VIEW v1; 2684 2685DROP TABLE t1; 2686 2687 2688# 2689# Bug#21080 ALTER VIEW makes user restate SQL SECURITY mode, and ALGORITHM 2690# 2691CREATE TABLE t1 (x INT, y INT); 2692CREATE ALGORITHM=TEMPTABLE SQL SECURITY INVOKER VIEW v1 AS SELECT x FROM t1; 2693SHOW CREATE VIEW v1; 2694 2695ALTER VIEW v1 AS SELECT x, y FROM t1; 2696SHOW CREATE VIEW v1; 2697 2698DROP VIEW v1; 2699DROP TABLE t1; 2700 2701 2702# Bug#21086 server crashes when VIEW defined with a SELECT with COLLATE 2703# clause is called 2704# 2705CREATE TABLE t1 (s1 char); 2706INSERT INTO t1 VALUES ('Z'); 2707 2708CREATE VIEW v1 AS SELECT s1 collate latin1_german1_ci AS col FROM t1; 2709 2710CREATE VIEW v2 (col) AS SELECT s1 collate latin1_german1_ci FROM t1; 2711 2712# either of these statements will cause crash 2713INSERT INTO v1 (col) VALUES ('b'); 2714INSERT INTO v2 (col) VALUES ('c'); 2715 2716SELECT s1 FROM t1; 2717DROP VIEW v1, v2; 2718DROP TABLE t1; 2719 2720 2721# 2722# Bug#11551 Asymmetric + undocumented behaviour of DROP VIEW and DROP TABLE 2723# 2724CREATE TABLE t1 (id INT); 2725CREATE VIEW v1 AS SELECT id FROM t1; 2726SHOW TABLES; 2727 2728--error ER_UNKNOWN_VIEW 2729DROP VIEW v2,v1; 2730SHOW TABLES; 2731 2732CREATE VIEW v1 AS SELECT id FROM t1; 2733--error ER_UNKNOWN_VIEW 2734DROP VIEW t1,v1; 2735show warnings; 2736SHOW TABLES; 2737 2738DROP TABLE t1; 2739--disable_warnings 2740DROP VIEW IF EXISTS v1; 2741--enable_warnings 2742 2743 2744# 2745# Bug#21261 Wrong access rights was required for an insert to a view 2746# 2747 2748set GLOBAL sql_mode=""; 2749set LOCAL sql_mode=""; 2750CREATE DATABASE bug21261DB; 2751USE bug21261DB; 2752connect (root,localhost,root,,bug21261DB); 2753connection root; 2754CREATE TABLE t1 (x INT); 2755CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT x FROM t1; 2756GRANT INSERT, UPDATE ON v1 TO 'user21261'@'localhost'; 2757GRANT INSERT, UPDATE ON t1 TO 'user21261'@'localhost'; 2758CREATE TABLE t2 (y INT); 2759GRANT SELECT ON t2 TO 'user21261'@'localhost'; 2760 2761connect (user21261, localhost, user21261,, bug21261DB); 2762connection user21261; 2763INSERT INTO v1 (x) VALUES (5); 2764UPDATE v1 SET x=1; 2765connection root; 2766GRANT SELECT ON v1 TO 'user21261'@'localhost'; 2767GRANT SELECT ON t1 TO 'user21261'@'localhost'; 2768connection user21261; 2769UPDATE v1,t2 SET x=1 WHERE x=y; 2770connection root; 2771SELECT * FROM t1; 2772REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user21261'@'localhost'; 2773DROP USER 'user21261'@'localhost'; 2774DROP VIEW v1; 2775DROP TABLE t1; 2776DROP DATABASE bug21261DB; 2777 2778connection default; 2779USE test; 2780disconnect root; 2781disconnect user21261; 2782 2783set GLOBAL sql_mode=default; 2784set LOCAL sql_mode=default; 2785 2786# 2787# Bug#15950 NOW() optimized away in VIEWs 2788# 2789create table t1 (f1 datetime); 2790create view v1 as select * from t1 where f1 between now() and now() + interval 1 minute; 2791show create view v1; 2792drop view v1; 2793drop table t1; 2794 2795 2796# 2797# Test for Bug#16899 Possible buffer overflow in handling of DEFINER-clause. 2798# 2799 2800# Prepare. 2801 2802--disable_warnings 2803DROP TABLE IF EXISTS t1; 2804DROP VIEW IF EXISTS v1; 2805DROP VIEW IF EXISTS v2; 2806--enable_warnings 2807 2808CREATE TABLE t1(a INT, b INT); 2809 2810--error ER_WRONG_STRING_LENGTH 2811CREATE DEFINER=longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost 2812 VIEW v1 AS SELECT a FROM t1; 2813 2814--error ER_WRONG_STRING_LENGTH 2815CREATE DEFINER=some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY 2816 VIEW v2 AS SELECT b FROM t1; 2817 2818# Cleanup. 2819 2820DROP TABLE t1; 2821 2822 2823# 2824# Bug#17591 Updatable view not possible with trigger or stored function 2825# 2826# During prelocking phase we didn't update lock type of view tables, 2827# hence READ lock was always requested. 2828# 2829--disable_warnings 2830DROP FUNCTION IF EXISTS f1; 2831DROP FUNCTION IF EXISTS f2; 2832DROP VIEW IF EXISTS v1, v2; 2833DROP TABLE IF EXISTS t1; 2834--enable_warnings 2835 2836CREATE TABLE t1 (i INT); 2837 2838CREATE VIEW v1 AS SELECT * FROM t1; 2839 2840delimiter |; 2841CREATE FUNCTION f1() RETURNS INT 2842BEGIN 2843 INSERT INTO v1 VALUES (0); 2844 RETURN 0; 2845END | 2846delimiter ;| 2847 2848SELECT f1(); 2849 2850CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t1; 2851 2852delimiter |; 2853CREATE FUNCTION f2() RETURNS INT 2854BEGIN 2855 INSERT INTO v2 VALUES (0); 2856 RETURN 0; 2857END | 2858delimiter ;| 2859 2860--error ER_NON_INSERTABLE_TABLE 2861SELECT f2(); 2862 2863DROP FUNCTION f1; 2864DROP FUNCTION f2; 2865DROP VIEW v1, v2; 2866DROP TABLE t1; 2867 2868 2869# 2870# Bug#5500 wrong select_type in EXPLAIN output for queries over views 2871# 2872 2873CREATE TABLE t1 (s1 int); 2874CREATE VIEW v1 AS SELECT * FROM t1; 2875 2876EXPLAIN SELECT * FROM t1; 2877EXPLAIN SELECT * FROM v1; 2878 2879INSERT INTO t1 VALUES (1), (3), (2); 2880 2881EXPLAIN SELECT * FROM t1 t WHERE t.s1+1 < (SELECT MAX(t1.s1) FROM t1); 2882EXPLAIN SELECT * FROM v1 t WHERE t.s1+1 < (SELECT MAX(t1.s1) FROM t1); 2883 2884DROP VIEW v1; 2885DROP TABLE t1; 2886 2887 2888# 2889# Bug#5505 Wrong error message on INSERT into a view 2890# 2891create table t1 (s1 int); 2892create view v1 as select s1 as a, s1 as b from t1; 2893--error ER_NON_INSERTABLE_TABLE 2894insert into v1 values (1,1); 2895update v1 set a = 5; 2896drop view v1; 2897drop table t1; 2898 2899 2900# 2901# Bug#21646 view qith a subquery in ON expression 2902# 2903 2904CREATE TABLE t1(pk int PRIMARY KEY); 2905CREATE TABLE t2(pk int PRIMARY KEY, fk int, ver int, org int); 2906 2907CREATE ALGORITHM=MERGE VIEW v1 AS 2908SELECT t1.* 2909 FROM t1 JOIN t2 2910 ON t2.fk = t1.pk AND 2911 t2.ver = (SELECT MAX(t.ver) FROM t2 t WHERE t.org = t2.org); 2912SHOW WARNINGS; 2913SHOW CREATE VIEW v1; 2914 2915DROP VIEW v1; 2916DROP TABLE t1, t2; 2917 2918 2919# 2920# Bug#19111 TRIGGERs selecting from a VIEW on the firing base table fail 2921# 2922# Allow to select from a view on a table being modified in a trigger 2923# and stored function, since plain select is allowed there. 2924# 2925--disable_warnings 2926DROP FUNCTION IF EXISTS f1; 2927DROP VIEW IF EXISTS v1; 2928DROP TABLE IF EXISTS t1; 2929--enable_warnings 2930 2931CREATE TABLE t1 (i INT); 2932INSERT INTO t1 VALUES (1); 2933 2934CREATE VIEW v1 AS SELECT MAX(i) FROM t1; 2935 2936# Plain 'SET NEW.i = (SELECT MAX(i) FROM t1) + 1' works, so select 2937# from a view should work too. 2938CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW 2939 SET NEW.i = (SELECT * FROM v1) + 1; 2940INSERT INTO t1 VALUES (1); 2941 2942# Plain 'RETURN (SELECT MAX(i) FROM t1)' works in INSERT, so select 2943# from a view should work too. 2944CREATE FUNCTION f1() RETURNS INT RETURN (SELECT * FROM v1); 2945UPDATE t1 SET i= f1(); 2946 2947DROP FUNCTION f1; 2948DROP VIEW v1; 2949DROP TABLE t1; 2950 2951 2952# 2953# Bug#16813 (WITH CHECK OPTION doesn't work with UPDATE) 2954# 2955CREATE TABLE t1(id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, val INT UNSIGNED NOT NULL); 2956CREATE VIEW v1 AS SELECT id, val FROM t1 WHERE val >= 1 AND val <= 5 WITH CHECK OPTION; 2957INSERT INTO v1 (val) VALUES (2); 2958INSERT INTO v1 (val) VALUES (4); 2959-- error ER_VIEW_CHECK_FAILED 2960INSERT INTO v1 (val) VALUES (6); 2961-- error ER_VIEW_CHECK_FAILED 2962UPDATE v1 SET val=6 WHERE id=2; 2963DROP VIEW v1; 2964DROP TABLE t1; 2965 2966 2967# 2968# Bug#22584 last_insert_id not updated after inserting a record 2969# through a updatable view 2970# 2971# We still do not update LAST_INSERT_ID if AUTO_INCREMENT column is 2972# not accessible through a view. However, we do not reset the value 2973# of LAST_INSERT_ID, but keep it unchanged. 2974# 2975--disable_warnings 2976DROP VIEW IF EXISTS v1, v2; 2977DROP TABLE IF EXISTS t1; 2978--enable_warnings 2979 2980CREATE TABLE t1 (i INT AUTO_INCREMENT PRIMARY KEY, j INT); 2981CREATE VIEW v1 AS SELECT j FROM t1; 2982CREATE VIEW v2 AS SELECT * FROM t1; 2983 2984INSERT INTO t1 (j) VALUES (1); 2985SELECT LAST_INSERT_ID(); 2986 2987INSERT INTO v1 (j) VALUES (2); 2988--echo # LAST_INSERT_ID() should not change. 2989SELECT LAST_INSERT_ID(); 2990 2991INSERT INTO v2 (j) VALUES (3); 2992--echo # LAST_INSERT_ID() should be updated. 2993SELECT LAST_INSERT_ID(); 2994 2995INSERT INTO v1 (j) SELECT j FROM t1; 2996--echo # LAST_INSERT_ID() should not change. 2997SELECT LAST_INSERT_ID(); 2998 2999SELECT * FROM t1; 3000 3001DROP VIEW v1, v2; 3002DROP TABLE t1; 3003 3004 3005# 3006# Bug#25580 !0 as an operand in a select expression of a view 3007# 3008 3009CREATE VIEW v AS SELECT !0 * 5 AS x FROM DUAL; 3010SHOW CREATE VIEW v; 3011 3012SELECT !0 * 5 AS x FROM DUAL; 3013SELECT * FROM v; 3014 3015DROP VIEW v; 3016 3017 3018# 3019# Bug#24293 '\Z' token is not handled correctly in views 3020# 3021 3022--disable_warnings 3023DROP VIEW IF EXISTS v1; 3024--enable_warnings 3025 3026CREATE VIEW v1 AS SELECT 'The\ZEnd'; 3027SELECT * FROM v1; 3028 3029SHOW CREATE VIEW v1; 3030 3031DROP VIEW v1; 3032 3033 3034# 3035# Bug#26124 BETWEEN over a view column of the DATETIME type 3036# 3037 3038CREATE TABLE t1 (mydate DATETIME); 3039INSERT INTO t1 VALUES 3040 ('2007-01-01'), ('2007-01-02'), ('2007-01-30'), ('2007-01-31'); 3041 3042CREATE VIEW v1 AS SELECT mydate from t1; 3043 3044SELECT * FROM t1 WHERE mydate BETWEEN '2007-01-01' AND '2007-01-31'; 3045SELECT * FROM v1 WHERE mydate BETWEEN '2007-01-01' AND '2007-01-31'; 3046 3047DROP VIEW v1; 3048DROP TABLE t1; 3049 3050 3051# 3052# Bug#25931 update of a multi-table view with check option 3053# 3054 3055CREATE TABLE t1 (a int); 3056CREATE TABLE t2 (b int); 3057INSERT INTO t1 VALUES (1), (2); 3058INSERT INTO t2 VALUES (1), (2); 3059 3060CREATE VIEW v1 AS 3061 SELECT t2.b FROM t1,t2 WHERE t1.a = t2.b WITH CHECK OPTION; 3062 3063SELECT * FROM v1; 3064--error ER_VIEW_CHECK_FAILED 3065UPDATE v1 SET b=3; 3066SELECT * FROM v1; 3067SELECT * FROM t1; 3068SELECT * FROM t2; 3069 3070DROP VIEW v1; 3071DROP TABLE t1,t2; 3072 3073 3074# 3075# Bug#12122 Views with ORDER BY can't be resolved using MERGE algorithm. 3076# 3077create table t1(f1 int, f2 int); 3078insert into t1 values(1,2),(1,3),(1,1),(2,3),(2,1),(2,2); 3079select * from t1; 3080create view v1 as select * from t1 order by f2; 3081select * from v1; 3082explain extended select * from v1; 3083select * from v1 order by f1; 3084explain extended select * from v1 order by f1; 3085drop view v1; 3086drop table t1; 3087 3088# 3089# Bug#26209 queries with GROUP BY and ORDER BY using views 3090# 3091 3092CREATE TABLE t1 ( 3093 id int(11) NOT NULL PRIMARY KEY, 3094 country varchar(32), 3095 code int(11) default NULL 3096); 3097INSERT INTO t1 VALUES 3098 (1,'ITALY',100),(2,'ITALY',200),(3,'FRANCE',100), (4,'ITALY',100); 3099 3100CREATE VIEW v1 AS SELECT * FROM t1; 3101 3102SELECT code, COUNT(DISTINCT country) FROM t1 GROUP BY code ORDER BY MAX(id); 3103SELECT code, COUNT(DISTINCT country) FROM v1 GROUP BY code ORDER BY MAX(id); 3104 3105DROP VIEW v1; 3106DROP TABLE t1; 3107 3108 3109# 3110# Bug#25897 Some queries are no longer possible after a CREATE VIEW fails 3111# 3112--disable_warnings 3113DROP VIEW IF EXISTS v1; 3114--enable_warnings 3115 3116let $query = SELECT * FROM (SELECT 1) AS t into @w; 3117 3118eval $query; 3119--error ER_PARSE_ERROR 3120eval CREATE VIEW v1 AS $query; 3121--echo # Previously the following would fail. 3122eval $query; 3123 3124 3125# 3126# Bug#24532 The return data type of IS TRUE is different from similar operations 3127# 3128 3129--disable_warnings 3130drop view if exists view_24532_a; 3131drop view if exists view_24532_b; 3132drop table if exists table_24532; 3133--enable_warnings 3134 3135create table table_24532 ( 3136 a int, 3137 b bigint, 3138 c int(4), 3139 d bigint(48) 3140); 3141 3142create view view_24532_a as 3143select 3144 a IS TRUE, 3145 a IS NOT TRUE, 3146 a IS FALSE, 3147 a IS NOT FALSE, 3148 a IS UNKNOWN, 3149 a IS NOT UNKNOWN, 3150 a is NULL, 3151 a IS NOT NULL, 3152 ISNULL(a), 3153 b IS TRUE, 3154 b IS NOT TRUE, 3155 b IS FALSE, 3156 b IS NOT FALSE, 3157 b IS UNKNOWN, 3158 b IS NOT UNKNOWN, 3159 b is NULL, 3160 b IS NOT NULL, 3161 ISNULL(b), 3162 c IS TRUE, 3163 c IS NOT TRUE, 3164 c IS FALSE, 3165 c IS NOT FALSE, 3166 c IS UNKNOWN, 3167 c IS NOT UNKNOWN, 3168 c is NULL, 3169 c IS NOT NULL, 3170 ISNULL(c), 3171 d IS TRUE, 3172 d IS NOT TRUE, 3173 d IS FALSE, 3174 d IS NOT FALSE, 3175 d IS UNKNOWN, 3176 d IS NOT UNKNOWN, 3177 d is NULL, 3178 d IS NOT NULL, 3179 ISNULL(d) 3180from table_24532; 3181 3182describe view_24532_a; 3183 3184create view view_24532_b as 3185select 3186 a IS TRUE, 3187 if(ifnull(a, 0), 1, 0) as old_istrue, 3188 a IS NOT TRUE, 3189 if(ifnull(a, 0), 0, 1) as old_isnottrue, 3190 a IS FALSE, 3191 if(ifnull(a, 1), 0, 1) as old_isfalse, 3192 a IS NOT FALSE, 3193 if(ifnull(a, 1), 1, 0) as old_isnotfalse 3194from table_24532; 3195 3196describe view_24532_b; 3197 3198show create view view_24532_b; 3199 3200insert into table_24532 values (0, 0, 0, 0); 3201select * from view_24532_b; 3202update table_24532 set a=1; 3203select * from view_24532_b; 3204update table_24532 set a=NULL; 3205select * from view_24532_b; 3206 3207drop view view_24532_a; 3208drop view view_24532_b; 3209drop table table_24532; 3210 3211 3212# 3213# Bug#26560 view using subquery with a reference to an outer alias 3214# 3215 3216CREATE TABLE t1 ( 3217 lid int NOT NULL PRIMARY KEY, 3218 name char(10) NOT NULL 3219); 3220INSERT INTO t1 (lid, name) VALUES 3221 (1, 'YES'), (2, 'NO'); 3222 3223CREATE TABLE t2 ( 3224 id int NOT NULL PRIMARY KEY, 3225 gid int NOT NULL, 3226 lid int NOT NULL, 3227 dt date 3228); 3229INSERT INTO t2 (id, gid, lid, dt) VALUES 3230 (1, 1, 1, '2007-01-01'),(2, 1, 2, '2007-01-02'), 3231 (3, 2, 2, '2007-02-01'),(4, 2, 1, '2007-02-02'); 3232 3233SELECT DISTINCT t2.gid AS lgid, 3234 (SELECT t1.name FROM t1, t2 3235 WHERE t1.lid = t2.lid AND t2.gid = lgid 3236 ORDER BY t2.dt DESC LIMIT 1 3237 ) as clid 3238 FROM t2; 3239 3240CREATE VIEW v1 AS 3241SELECT DISTINCT t2.gid AS lgid, 3242 (SELECT t1.name FROM t1, t2 3243 WHERE t1.lid = t2.lid AND t2.gid = lgid 3244 ORDER BY t2.dt DESC LIMIT 1 3245 ) as clid 3246 FROM t2; 3247SELECT * FROM v1; 3248 3249DROP VIEW v1; 3250DROP table t1,t2; 3251 3252 3253# 3254# Bug#27786 Inconsistent Operation Performing UNION On View With ORDER BY 3255# 3256CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2),(3); 3257CREATE VIEW v1 AS SELECT a FROM t1 ORDER BY a; 3258 3259SELECT * FROM t1 UNION SELECT * FROM v1; 3260EXPLAIN SELECT * FROM t1 UNION SELECT * FROM v1; 3261SELECT * FROM v1 UNION SELECT * FROM t1; 3262EXPLAIN SELECT * FROM v1 UNION SELECT * FROM t1; 3263SELECT * FROM t1 UNION SELECT * FROM v1 ORDER BY a; 3264EXPLAIN SELECT * FROM t1 UNION SELECT * FROM v1 ORDER BY a; 3265 3266DROP VIEW v1; 3267DROP TABLE t1; 3268 3269 3270# 3271# Bug#27921 View ignores precision for CAST() 3272# 3273CREATE VIEW v1 AS SELECT CAST( 1.23456789 AS DECIMAL( 7,5 ) ) AS col; 3274SELECT * FROM v1; 3275DESCRIBE v1; 3276DROP VIEW v1; 3277 3278CREATE VIEW v1 AS SELECT CAST(1.23456789 AS DECIMAL(8,0)) AS col; 3279SHOW CREATE VIEW v1; 3280DROP VIEW v1; 3281 3282 3283# 3284# Bug#28716 CHECK OPTION expression is evaluated over expired record buffers 3285# when VIEW is updated via temporary tables 3286# 3287CREATE TABLE t1 (a INT); 3288CREATE TABLE t2 (b INT, c INT DEFAULT 0); 3289INSERT INTO t1 (a) VALUES (1), (2); 3290INSERT INTO t2 (b) VALUES (1), (2); 3291CREATE VIEW v1 AS SELECT t2.b,t2.c FROM t1, t2 3292 WHERE t1.a=t2.b AND t2.b < 3 WITH CHECK OPTION; 3293SELECT * FROM v1; 3294UPDATE v1 SET c=1 WHERE b=1; 3295SELECT * FROM v1; 3296DROP VIEW v1; 3297DROP TABLE t1,t2; 3298 3299 3300# 3301# Bug#28561 update on multi-table view with CHECK OPTION and a subquery 3302# in WHERE condition 3303# 3304 3305CREATE TABLE t1 (id int); 3306CREATE TABLE t2 (id int, c int DEFAULT 0); 3307INSERT INTO t1 (id) VALUES (1); 3308INSERT INTO t2 (id) VALUES (1); 3309 3310CREATE VIEW v1 AS 3311 SELECT t2.c FROM t1, t2 3312 WHERE t1.id=t2.id AND 1 IN (SELECT id FROM t1) WITH CHECK OPTION; 3313 3314UPDATE v1 SET c=1; 3315 3316DROP VIEW v1; 3317DROP TABLE t1,t2; 3318 3319 3320# 3321# Bug#27827 CHECK OPTION ignores ON conditions when updating 3322# a multi-table view with CHECK OPTION. 3323# 3324 3325CREATE TABLE t1 (a1 INT, c INT DEFAULT 0); 3326CREATE TABLE t2 (a2 INT); 3327CREATE TABLE t3 (a3 INT); 3328CREATE TABLE t4 (a4 INT); 3329INSERT INTO t1 (a1) VALUES (1),(2); 3330INSERT INTO t2 (a2) VALUES (1),(2); 3331INSERT INTO t3 (a3) VALUES (1),(2); 3332INSERT INTO t4 (a4) VALUES (1),(2); 3333 3334CREATE VIEW v1 AS 3335 SELECT t1.a1, t1.c FROM t1 JOIN t2 ON t1.a1=t2.a2 AND t1.c < 3 3336 WITH CHECK OPTION; 3337SELECT * FROM v1; 3338--error ER_VIEW_CHECK_FAILED 3339UPDATE v1 SET c=3; 3340PREPARE t FROM 'UPDATE v1 SET c=3'; 3341--error ER_VIEW_CHECK_FAILED 3342EXECUTE t; 3343--error ER_VIEW_CHECK_FAILED 3344EXECUTE t; 3345--error ER_VIEW_CHECK_FAILED 3346INSERT INTO v1(a1, c) VALUES (3, 3); 3347UPDATE v1 SET c=1 WHERE a1=1; 3348SELECT * FROM v1; 3349SELECT * FROM t1; 3350 3351CREATE VIEW v2 AS SELECT t1.a1, t1.c 3352 FROM (t1 JOIN t2 ON t1.a1=t2.a2 AND t1.c < 3) 3353 JOIN (t3 JOIN t4 ON t3.a3=t4.a4) 3354 ON t2.a2=t3.a3 WITH CHECK OPTION; 3355SELECT * FROM v2; 3356--error ER_VIEW_CHECK_FAILED 3357UPDATE v2 SET c=3; 3358PREPARE t FROM 'UPDATE v2 SET c=3'; 3359--error ER_VIEW_CHECK_FAILED 3360EXECUTE t; 3361--error ER_VIEW_CHECK_FAILED 3362EXECUTE t; 3363--error ER_VIEW_CHECK_FAILED 3364INSERT INTO v2(a1, c) VALUES (3, 3); 3365UPDATE v2 SET c=2 WHERE a1=1; 3366SELECT * FROM v2; 3367SELECT * FROM t1; 3368 3369DROP VIEW v1,v2; 3370DROP TABLE t1,t2,t3,t4; 3371 3372 3373# 3374# Bug#29104 assertion abort for a query with a view column reference 3375# in the GROUP BY list and a condition requiring the value 3376# of another view column to be equal to a constant 3377# 3378 3379CREATE TABLE t1 (a int, b int); 3380INSERT INTO t1 VALUES (1,2), (2,2), (1,3), (1,2); 3381 3382CREATE VIEW v1 AS SELECT a, b+1 as b FROM t1; 3383 3384 3385SELECT b, SUM(a) FROM v1 WHERE b=3 GROUP BY b; 3386EXPLAIN SELECT b, SUM(a) FROM v1 WHERE b=3 GROUP BY b; 3387 3388SELECT a, SUM(b) FROM v1 WHERE b=3 GROUP BY a; 3389EXPLAIN SELECT a, SUM(b) FROM v1 WHERE b=3 GROUP BY a; 3390 3391SELECT a, SUM(b) FROM v1 WHERE a=1 GROUP BY a; 3392EXPLAIN SELECT a, SUM(b) FROM v1 WHERE a=1 GROUP BY a; 3393 3394DROP VIEW v1; 3395DROP TABLE t1; 3396 3397 3398# 3399# Bug#29392 SELECT over a multi-table view with ORDER BY 3400# selecting the same view column with two different aliases 3401# 3402 3403CREATE TABLE t1 ( 3404 person_id int NOT NULL PRIMARY KEY, 3405 username varchar(40) default NULL, 3406 status_flg char(1) NOT NULL default 'A' 3407); 3408 3409CREATE TABLE t2 ( 3410 person_role_id int NOT NULL auto_increment PRIMARY KEY, 3411 role_id int NOT NULL, 3412 person_id int NOT NULL, 3413 INDEX idx_person_id (person_id), 3414 INDEX idx_role_id (role_id) 3415); 3416 3417CREATE TABLE t3 ( 3418 role_id int NOT NULL auto_increment PRIMARY KEY, 3419 role_name varchar(100) default NULL, 3420 app_name varchar(40) NOT NULL, 3421 INDEX idx_app_name(app_name) 3422); 3423 3424CREATE VIEW v1 AS 3425SELECT profile.person_id AS person_id 3426 FROM t1 profile, t2 userrole, t3 role 3427 WHERE userrole.person_id = profile.person_id AND 3428 role.role_id = userrole.role_id AND 3429 profile.status_flg = 'A' 3430 ORDER BY profile.person_id,role.app_name,role.role_name; 3431 3432INSERT INTO t1 VALUES 3433 (6,'Sw','A'), (-1136332546,'ols','e'), (0,' *\n','0'), 3434 (-717462680,'ENTS Ta','0'), (-904346964,'ndard SQL\n','0'); 3435INSERT INTO t2 VALUES 3436 (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); 3437 3438INSERT INTO t3 VALUES 3439 (1,'NUCANS_APP_USER','NUCANSAPP'),(2,'NUCANS_TRGAPP_USER','NUCANSAPP'), 3440 (3,'IA_INTAKE_COORDINATOR','IACANS'),(4,'IA_SCREENER','IACANS'), 3441 (5,'IA_SUPERVISOR','IACANS'),(6,'IA_READONLY','IACANS'), 3442 (7,'SOC_USER','SOCCANS'),(8,'CAYIT_USER','CAYITCANS'), 3443 (9,'RTOS_DCFSPOS_SUPERVISOR','RTOS'); 3444 3445EXPLAIN SELECT t.person_id AS a, t.person_id AS b FROM v1 t WHERE t.person_id=6; 3446SELECT t.person_id AS a, t.person_id AS b FROM v1 t WHERE t.person_id=6; 3447 3448DROP VIEW v1; 3449DROP TABLE t1,t2,t3; 3450 3451 3452# 3453# Bug#30020 Insufficient check led to a wrong info provided by the 3454# information schema table. 3455# 3456create table t1 (i int); 3457insert into t1 values (1), (2), (1), (3), (2), (4); 3458create view v1 as select distinct i from t1; 3459select * from v1; 3460select table_name, is_updatable from information_schema.views 3461 where table_name = 'v1'; 3462drop view v1; 3463drop table t1; 3464 3465 3466# 3467# Bug#28701 SELECTs from VIEWs completely ignore USE/FORCE KEY, allowing 3468# invalid statements 3469# 3470 3471CREATE TABLE t1 (a INT); 3472INSERT INTO t1 VALUES (1),(2); 3473CREATE VIEW v1 AS SELECT * FROM t1; 3474--error ER_KEY_DOES_NOT_EXISTS 3475SELECT * FROM v1 USE KEY(non_existant); 3476--error ER_KEY_DOES_NOT_EXISTS 3477SELECT * FROM v1 FORCE KEY(non_existant); 3478--error ER_KEY_DOES_NOT_EXISTS 3479SELECT * FROM v1 IGNORE KEY(non_existant); 3480 3481DROP VIEW v1; 3482DROP TABLE t1; 3483 3484 3485# 3486# Bug#28702 VIEWs defined with USE/FORCE KEY ignore that request 3487# 3488CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT, b INT NOT NULL DEFAULT 0, 3489 PRIMARY KEY(a), KEY (b)); 3490INSERT INTO t1 VALUES (),(),(),(),(),(),(),(),(),(),(),(),(),(),(); 3491CREATE VIEW v1 AS SELECT * FROM t1 FORCE KEY (PRIMARY,b) ORDER BY a; 3492SHOW CREATE VIEW v1; 3493EXPLAIN SELECT * FROM v1; 3494CREATE VIEW v2 AS SELECT * FROM t1 USE KEY () ORDER BY a; 3495SHOW CREATE VIEW v2; 3496EXPLAIN SELECT * FROM v2; 3497CREATE VIEW v3 AS SELECT * FROM t1 IGNORE KEY (b) ORDER BY a; 3498SHOW CREATE VIEW v3; 3499EXPLAIN SELECT * FROM v3; 3500 3501DROP VIEW v1; 3502DROP VIEW v2; 3503DROP VIEW v3; 3504DROP TABLE t1; 3505 3506 3507--echo # 3508--echo # Bug#29477 Not all fields of the target table were checked to have 3509--echo # a default value when inserting into a view. 3510--echo # 3511create table t1(f1 int, f2 int not null); 3512create view v1 as select f1 from t1; 3513insert ignore into v1 values(1); 3514set @old_mode=@@sql_mode; 3515set @@sql_mode=traditional; 3516--error ER_NO_DEFAULT_FOR_VIEW_FIELD 3517insert into v1 values(1); 3518set @@sql_mode=@old_mode; 3519drop view v1; 3520drop table t1; 3521 3522 3523# 3524# Bug#33389 Selecting from a view into a table from within SP or trigger 3525# crashes server 3526# 3527 3528create table t1 (a int, key(a)); 3529create table t2 (c int); 3530 3531create view v1 as select a b from t1; 3532create view v2 as select 1 a from t2, v1 where c in 3533 (select 1 from t1 where b = a); 3534 3535insert into t1 values (1), (1); 3536insert into t2 values (1), (1); 3537 3538prepare stmt from "select * from v2 where a = 1"; 3539execute stmt; 3540 3541drop view v1, v2; 3542drop table t1, t2; 3543 3544 3545# 3546# Bug#33049 Assert while running test-as3ap test(mysql-bench suite) 3547# 3548 3549CREATE TABLE t1 (a INT); 3550CREATE VIEW v1 AS SELECT p.a AS a FROM t1 p, t1 q; 3551 3552INSERT INTO t1 VALUES (1), (1); 3553SELECT MAX(a), COUNT(DISTINCT a) FROM v1 GROUP BY a; 3554 3555DROP VIEW v1; 3556DROP TABLE t1; 3557 3558########################################################################### 3559 3560--echo # ----------------------------------------------------------------- 3561--echo # -- Bug#34337 Server crash when Altering a view using a table name. 3562--echo # ----------------------------------------------------------------- 3563--echo 3564 3565--disable_warnings 3566DROP TABLE IF EXISTS t1; 3567--enable_warnings 3568 3569--echo 3570 3571CREATE TABLE t1(c1 INT); 3572 3573--echo 3574 3575SELECT * FROM t1; 3576 3577--error ER_WRONG_OBJECT 3578ALTER ALGORITHM=TEMPTABLE SQL SECURITY INVOKER VIEW t1 (c2) AS SELECT (1); 3579 3580--echo 3581 3582DROP TABLE t1; 3583 3584--echo 3585--echo # -- End of test case for Bug#34337. 3586--echo 3587 3588########################################################################### 3589 3590--echo # ----------------------------------------------------------------- 3591--echo # -- Bug#35193 VIEW query is rewritten without "FROM DUAL", 3592--echo # -- causing syntax error 3593--echo # ----------------------------------------------------------------- 3594--echo 3595 3596CREATE VIEW v1 AS SELECT 1 FROM DUAL WHERE 1; 3597 3598--echo 3599 3600SELECT * FROM v1; 3601SHOW CREATE TABLE v1; 3602 3603--echo 3604 3605DROP VIEW v1; 3606 3607--echo 3608--echo # -- End of test case for Bug#35193. 3609--echo 3610 3611########################################################################### 3612 3613# 3614# Bug#39040 valgrind errors/crash when creating views with binlog logging 3615# enabled 3616# 3617# Bug is visible only when running in valgrind with binary logging. 3618CREATE VIEW v1 AS SELECT 1; 3619DROP VIEW v1; 3620 3621 3622# 3623# Bug#33461 SELECT ... FROM <view> USE INDEX (...) throws an error 3624# 3625 3626CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT, INDEX (c2)); 3627INSERT INTO t1 VALUES (1,1), (2,2), (3,3); 3628SELECT * FROM t1 USE INDEX (PRIMARY) WHERE c1=2; 3629SELECT * FROM t1 USE INDEX (c2) WHERE c2=2; 3630 3631CREATE VIEW v1 AS SELECT c1, c2 FROM t1; 3632SHOW INDEX FROM v1; 3633--error ER_KEY_DOES_NOT_EXISTS 3634SELECT * FROM v1 USE INDEX (PRIMARY) WHERE c1=2; 3635--error ER_KEY_DOES_NOT_EXISTS 3636SELECT * FROM v1 FORCE INDEX (PRIMARY) WHERE c1=2; 3637--error ER_KEY_DOES_NOT_EXISTS 3638SELECT * FROM v1 IGNORE INDEX (PRIMARY) WHERE c1=2; 3639--error ER_KEY_DOES_NOT_EXISTS 3640SELECT * FROM v1 USE INDEX (c2) WHERE c2=2; 3641--error ER_KEY_DOES_NOT_EXISTS 3642SELECT * FROM v1 FORCE INDEX (c2) WHERE c2=2; 3643--error ER_KEY_DOES_NOT_EXISTS 3644SELECT * FROM v1 IGNORE INDEX (c2) WHERE c2=2; 3645 3646DROP VIEW v1; 3647DROP TABLE t1; 3648 3649--echo # 3650--echo # Bug #45806 crash when replacing into a view with a join! 3651--echo # 3652CREATE TABLE t1(a INT UNIQUE); 3653CREATE VIEW v1 AS SELECT t1.a FROM t1, t1 AS a; 3654INSERT INTO t1 VALUES (1), (2); 3655 3656REPLACE INTO v1(a) SELECT 1 FROM t1,t1 AS c; 3657SELECT * FROM v1; 3658REPLACE INTO v1(a) SELECT 3 FROM t1,t1 AS c; 3659SELECT * FROM v1; 3660DELETE FROM t1 WHERE a=3; 3661INSERT INTO v1(a) SELECT 1 FROM t1,t1 AS c 3662ON DUPLICATE KEY UPDATE `v1`.`a`= 1; 3663SELECT * FROM v1; 3664 3665CREATE VIEW v2 AS SELECT t1.a FROM t1, v1 AS a; 3666 3667REPLACE INTO v2(a) SELECT 1 FROM t1,t1 AS c; 3668SELECT * FROM v2 order by 1; 3669REPLACE INTO v2(a) SELECT 3 FROM t1,t1 AS c; 3670SELECT * FROM v2 order by 1; 3671INSERT INTO v2(a) SELECT 1 FROM t1,t1 AS c 3672ON DUPLICATE KEY UPDATE `v2`.`a`= 1; 3673SELECT * FROM v2 order by 1; 3674 3675DROP VIEW v1; 3676DROP VIEW v2; 3677DROP TABLE t1; 3678 3679--echo # -- End of test case for Bug#45806 3680 3681--echo # ----------------------------------------------------------------- 3682--echo # -- Bug#40825: Error 1356 while selecting from a view 3683--echo # -- with a "HAVING" clause though query works 3684--echo # ----------------------------------------------------------------- 3685--echo 3686 3687CREATE TABLE t1 (c INT); 3688 3689--echo 3690 3691CREATE VIEW v1 (view_column) AS SELECT c AS alias FROM t1 HAVING alias; 3692SHOW CREATE VIEW v1; 3693SELECT * FROM v1; 3694 3695--echo 3696 3697DROP VIEW v1; 3698DROP TABLE t1; 3699 3700--echo 3701--echo # -- End of test case for Bug#40825 3702--echo 3703 3704--echo # ----------------------------------------------------------------- 3705--echo # -- End of 5.0 tests. 3706--echo # ----------------------------------------------------------------- 3707 3708# 3709# Bug#21370 View renaming lacks tablename_to_filename encoding 3710# 3711--disable_warnings 3712DROP DATABASE IF EXISTS `d-1`; 3713--enable_warnings 3714CREATE DATABASE `d-1`; 3715USE `d-1`; 3716CREATE TABLE `t-1` (c1 INT); 3717CREATE VIEW `v-1` AS SELECT c1 FROM `t-1`; 3718SHOW TABLES; 3719RENAME TABLE `t-1` TO `t-2`; 3720RENAME TABLE `v-1` TO `v-2`; 3721SHOW TABLES; 3722DROP TABLE `t-2`; 3723DROP VIEW `v-2`; 3724DROP DATABASE `d-1`; 3725USE test; 3726 3727--echo 3728--echo # 3729--echo # Bug#26676 VIEW using old table schema in a session. 3730--echo # 3731--echo 3732 3733--disable_warnings 3734DROP VIEW IF EXISTS v1; 3735DROP TABLE IF EXISTS t1; 3736--enable_warnings 3737 3738CREATE TABLE t1(c1 INT, c2 INT); 3739INSERT INTO t1 VALUES (1, 2), (3, 4); 3740 3741--echo 3742 3743SELECT * FROM t1; 3744 3745--echo 3746 3747CREATE VIEW v1 AS SELECT * FROM t1; 3748 3749--echo 3750 3751SELECT * FROM v1; 3752 3753--echo 3754 3755ALTER TABLE t1 ADD COLUMN c3 INT AFTER c2; 3756 3757--echo 3758 3759SELECT * FROM t1; 3760 3761--echo 3762 3763SELECT * FROM v1; 3764 3765--echo 3766 3767SHOW CREATE VIEW v1; 3768 3769--echo 3770 3771DROP VIEW v1; 3772DROP TABLE t1; 3773 3774--echo 3775--echo # End of test case for Bug#26676. 3776--echo 3777 3778########################################################################### 3779 3780--echo # ----------------------------------------------------------------- 3781--echo # -- Bug#32538 View definition picks up character set, but not collation 3782--echo # ----------------------------------------------------------------- 3783--echo 3784 3785--disable_warnings 3786DROP VIEW IF EXISTS v1; 3787--enable_warnings 3788 3789--echo 3790 3791SET collation_connection = latin1_general_ci; 3792CREATE VIEW v1 AS SELECT _latin1 'text1' AS c1, 'text2' AS c2; 3793 3794--echo 3795 3796SELECT COLLATION(c1), COLLATION(c2) FROM v1; 3797 3798--echo 3799 3800SHOW CREATE VIEW v1; 3801 3802--echo 3803 3804--error ER_CANT_AGGREGATE_2COLLATIONS 3805SELECT * FROM v1 WHERE c1 = 'text1'; 3806 3807--echo 3808 3809SELECT * FROM v1 WHERE c2 = 'text2'; 3810 3811--echo 3812 3813use test; 3814SET names latin1; 3815 3816--echo 3817 3818SELECT COLLATION(c1), COLLATION(c2) FROM v1; 3819 3820--echo 3821 3822SELECT * FROM v1 WHERE c1 = 'text1'; 3823 3824--echo 3825 3826--error ER_CANT_AGGREGATE_2COLLATIONS 3827SELECT * FROM v1 WHERE c2 = 'text2'; 3828 3829--echo 3830 3831DROP VIEW v1; 3832 3833--echo 3834--echo # -- End of test case for Bug#32538. 3835--echo 3836 3837# 3838# Bug#34587 Creating a view inside a stored procedure leads to a server crash 3839# 3840 3841--disable_warnings 3842drop view if exists a; 3843drop procedure if exists p; 3844--enable_warnings 3845 3846delimiter |; 3847create procedure p() 3848begin 3849 declare continue handler for sqlexception begin end; 3850 create view a as select 1; 3851end| 3852delimiter ;| 3853call p(); 3854call p(); 3855drop view a; 3856drop procedure p; 3857 3858########################################################################### 3859 3860 3861--echo # 3862--echo # Bug #44860: ALTER TABLE on view crashes server 3863--echo # 3864CREATE TABLE t1 (a INT); 3865CREATE VIEW v1 AS SELECT a FROM t1; 3866--error ER_WRONG_OBJECT 3867ALTER TABLE v1; 3868DROP VIEW v1; 3869DROP TABLE t1; 3870 3871--echo # 3872--echo # Bug#48449: hang on show create view after upgrading when 3873--echo # view contains function of view 3874--echo # 3875 3876--disable_warnings 3877DROP VIEW IF EXISTS v1,v2; 3878DROP TABLE IF EXISTS t1,t2; 3879DROP FUNCTION IF EXISTS f1; 3880--enable_warnings 3881 3882CREATE TABLE t1 (a INT); 3883CREATE TABLE t2 (a INT); 3884 3885delimiter //; 3886CREATE FUNCTION f1() RETURNS INT 3887BEGIN 3888 SELECT a FROM v2 INTO @a; 3889 RETURN @a; 3890END// 3891delimiter ;// 3892 3893--echo # Trigger pre-locking when opening v2. 3894CREATE VIEW v1 AS SELECT f1() FROM t1; 3895 3896let $MYSQLD_DATADIR= `SELECT @@datadir`; 3897copy_file std_data/bug48449.frm $MYSQLD_DATADIR/test/v2.frm; 3898 3899enable_prepare_warnings; 3900SHOW CREATE VIEW v1; 3901disable_prepare_warnings; 3902 3903DROP VIEW v1,v2; 3904DROP TABLE t1,t2; 3905DROP FUNCTION f1; 3906 3907 3908# 3909# Bug#48294 assertion when creating a view based on some row() construct in select query 3910# 3911CREATE TABLE t1(f1 INT); 3912INSERT INTO t1 VALUES (); 3913 3914CREATE VIEW v1 AS SELECT 1 FROM t1 WHERE 3915ROW(1,1) >= ROW(1, (SELECT 1 FROM t1 WHERE f1 >= ANY ( SELECT '1' ))); 3916 3917DROP VIEW v1; 3918DROP TABLE t1; 3919 3920--echo # 3921--echo # Bug#52120 create view cause Assertion failed: 0, file .\item_subselect.cc, line 817 3922--echo # 3923CREATE TABLE t1 (a CHAR(1) CHARSET latin1, b CHAR(1) CHARSET utf8); 3924CREATE VIEW v1 AS SELECT 1 from t1 3925WHERE t1.b <=> (SELECT a FROM t1 WHERE a < SOME(SELECT '1')); 3926DROP VIEW v1; 3927DROP TABLE t1; 3928 3929--echo # 3930--echo # Bug#57703 create view cause Assertion failed: 0, file .\item_subselect.cc, line 846 3931--echo # 3932 3933CREATE TABLE t1(a int); 3934CREATE VIEW v1 AS SELECT 1 FROM t1 GROUP BY 3935SUBSTRING(1 FROM (SELECT 3 FROM t1 WHERE a >= ANY(SELECT 1))); 3936DROP VIEW v1; 3937DROP TABLE t1; 3938 3939--echo # 3940--echo # Bug#57352 valgrind warnings when creating view 3941--echo # 3942CREATE VIEW v1 AS SELECT 1 IN (1 LIKE 2,0) AS f; 3943DROP VIEW v1; 3944 3945--echo # 3946--echo # Bug 11829681 - 60295: ERROR 1356 ON VIEW THAT EXECUTES FINE AS A QUERY 3947--echo # 3948 3949CREATE TABLE t1 (a INT); 3950CREATE VIEW v1 AS SELECT s.* FROM t1 s, t1 b HAVING a; 3951 3952SELECT * FROM v1; 3953 3954DROP VIEW v1; 3955DROP TABLE t1; 3956 3957--echo # 3958--echo # LP BUG#777809 (a retrograded condition for view ON) 3959--echo # 3960 3961CREATE TABLE t1 ( f1 int NOT NULL , f6 int NOT NULL ) ; 3962INSERT IGNORE INTO t1 VALUES (20, 2); 3963 3964CREATE TABLE t2 ( f3 int NOT NULL ) ; 3965INSERT IGNORE INTO t2 VALUES (7); 3966 3967CREATE OR REPLACE VIEW v2 AS SELECT * FROM t2; 3968 3969PREPARE prep_stmt FROM 'SELECT t1.f6 FROM t1 RIGHT JOIN v2 ON v2.f3 WHERE t1.f1 != 0'; 3970 3971EXECUTE prep_stmt; 3972EXECUTE prep_stmt; 3973 3974drop view v2; 3975drop table t1,t2; 3976 3977--echo # ----------------------------------------------------------------- 3978--echo # -- End of 5.1 tests. 3979--echo # ----------------------------------------------------------------- 3980--echo # 3981--echo # Bug #794005: crash in st_table::mark_virtual_columns_for_write 3982--echo # 3983 3984CREATE TABLE t1 (a int); 3985insert into t1 values (1); 3986CREATE TABLE t2 (a int); 3987insert into t2 values (1); 3988 3989CREATE VIEW v2 AS SELECT * FROM t2; 3990CREATE VIEW v1 AS SELECT * FROM v2; 3991CREATE VIEW v3 AS SELECT t2.a,v1.a as b FROM t2,v1 where t2.a=v1.a; 3992CREATE OR REPLACE ALGORITHM = TEMPTABLE VIEW v2 AS SELECT * FROM t1; 3993 3994--error ER_NON_UPDATABLE_TABLE 3995UPDATE v1 SET a = 10; 3996--error ER_NON_INSERTABLE_TABLE 3997REPLACE v1 SET a = 10; 3998--error ER_NON_INSERTABLE_TABLE 3999INSERT into v1 values (20); 4000--error ER_NON_UPDATABLE_TABLE 4001DELETE from v1; 4002--error ER_NON_UPDATABLE_TABLE 4003UPDATE v3 SET b= 10; 4004--error ER_NON_INSERTABLE_TABLE 4005REPLACE v3 SET b= 10; 4006--error ER_NON_INSERTABLE_TABLE 4007INSERT into v3(b) values (20); 4008--error ER_VIEW_DELETE_MERGE_VIEW 4009DELETE from v3 where b=20; 4010--error ER_VIEW_DELETE_MERGE_VIEW 4011DELETE from v3 where a=20; 4012--error ER_NON_UPDATABLE_TABLE 4013DELETE v1 from v1,t1 where v1.a=t1.a; 4014UPDATE v3 SET a = 10; 4015REPLACE v3 SET a = 11; 4016INSERT INTO v3(a) values (20); 4017 4018select * from t1; 4019select * from t2; 4020 4021CREATE OR REPLACE ALGORITHM = MERGE VIEW v2 AS SELECT * FROM t2; 4022DELETE from v1 where a=11; 4023DELETE v1 from v1,t1 where v1.a=t1.a; 4024select * from t1; 4025select * from t2; 4026 4027DROP VIEW v1,v2,v3; 4028DROP TABLE t1,t2; 4029 4030--echo # 4031--echo # MDEV-6251: SIGSEGV in query optimizer (in set_check_materialized 4032--echo # with MERGE view) 4033--echo # 4034 4035CREATE TABLE t1 (a1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); 4036CREATE TABLE t2 (b1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); 4037CREATE TABLE t3 (c1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); 4038CREATE TABLE t4 (d1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); 4039CREATE TABLE t5 (e1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); 4040CREATE TABLE t6 (f1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); 4041 4042CREATE OR REPLACE view v1 AS 4043 SELECT 1 4044 FROM t1 a_alias_1 4045 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 4046 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 4047 LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 4048 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 4049 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 4050 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 4051; 4052 4053SELECT 1 4054FROM (( SELECT 1 4055 FROM t1 a_alias_1 4056 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 4057 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 4058 LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 4059 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 4060 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 4061 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 4062) t1) 4063LEFT OUTER JOIN (( SELECT 1 4064 FROM t1 a_alias_1 4065 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 4066 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 4067 LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 4068 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 4069 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 4070 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 4071) t2) ON 1=1 4072LEFT OUTER JOIN (( SELECT 1 4073 FROM t1 a_alias_1 4074 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 4075 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 4076 LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 4077 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 4078 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 4079 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 4080) t3) ON 1=1 4081LEFT OUTER JOIN (( SELECT 1 4082 FROM t1 a_alias_1 4083 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 4084 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 4085 LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 4086 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 4087 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 4088 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 4089) t4) ON 1=1 4090LEFT OUTER JOIN (( SELECT 1 4091 FROM t1 a_alias_1 4092 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 4093 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 4094 LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 4095 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 4096 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 4097 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 4098) t5) ON 1=1 4099LEFT OUTER JOIN (( SELECT 1 4100 FROM t1 a_alias_1 4101 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 4102 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 4103 LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 4104 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 4105 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 4106 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 4107) t6) ON 1=1 4108LEFT OUTER JOIN (( SELECT 1 4109 FROM t1 a_alias_1 4110 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 4111 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 4112 LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 4113 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 4114 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 4115 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 4116) t7) ON 1=1 4117LEFT OUTER JOIN (( SELECT 1 4118 FROM t1 a_alias_1 4119 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 4120 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 4121 LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 4122 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 4123 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 4124 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 4125) t8) ON 1=1 4126; 4127 4128SELECT 1 4129FROM (v1 t1) 4130LEFT OUTER JOIN (v1 t2) ON 1=1 4131LEFT OUTER JOIN (v1 t3) ON 1=1 4132LEFT OUTER JOIN (v1 t4) ON 1=1 4133LEFT OUTER JOIN (v1 t5) ON 1=1 4134LEFT OUTER JOIN (v1 t6) ON 1=1 4135LEFT OUTER JOIN (v1 t7) ON 1=1 4136LEFT OUTER JOIN (v1 t8) ON 1=1 4137; 4138 4139drop view v1; 4140drop table t1,t2,t3,t4,t5,t6; 4141 4142--echo # ----------------------------------------------------------------- 4143--echo # -- End of 5.2 tests. 4144--echo # ----------------------------------------------------------------- 4145 4146--echo # 4147--echo # Bug #59696 Optimizer does not use equalities for conditions over view 4148--echo # 4149 4150CREATE TABLE t1 (a int NOT NULL); 4151INSERT INTO t1 VALUES 4152 (9), (2), (8), (1), (3), (4), (2), (5), 4153 (9), (2), (8), (1), (3), (4), (2), (5); 4154 4155CREATE TABLE t2 (pk int PRIMARY KEY, c int NOT NULL); 4156INSERT INTO t2 VALUES 4157 (9,90), (16, 160), (11,110), (1,10), (18,180), (2,20), 4158 (14,140), (15, 150), (12,120), (3,30), (17,170), (19,190); 4159 4160EXPLAIN EXTENDED 4161SELECT t1.a,t2.c FROM t1,t2 WHERE t2.pk = t1.a AND t2.pk > 8; 4162FLUSH STATUS; 4163SELECT t1.a,t2.c FROM t1,t2 WHERE t2.pk = t1.a AND t2.pk > 8; 4164SHOW STATUS LIKE 'Handler_read_%'; 4165 4166CREATE VIEW v AS SELECT * FROM t2; 4167EXPLAIN EXTENDED 4168SELECT t1.a,v.c FROM t1,v WHERE v.pk = t1.a AND v.pk > 8; 4169FLUSH STATUS; 4170SELECT t1.a,v.c FROM t1,v WHERE v.pk = t1.a AND v.pk > 8; 4171SHOW STATUS LIKE 'Handler_read_%'; 4172DROP VIEW v; 4173 4174DROP TABLE t1, t2; 4175 4176--echo # 4177--echo # Bug#702403: crash with multiple equalities and a view 4178--echo # 4179 4180CREATE TABLE t1 (a int); 4181INSERT INTO t1 VALUES (10); 4182 4183CREATE TABLE t2 (pk int PRIMARY KEY, b int, INDEX idx (b)); 4184INSERT INTO t2 VALUES (1,2), (3,4); 4185CREATE TABLE t3 (pk int PRIMARY KEY, b int, INDEX idx (b)); 4186INSERT INTO t3 VALUES (1,2), (3,4); 4187 4188CREATE VIEW v1 AS SELECT * FROM t1; 4189 4190EXPLAIN 4191SELECT * FROM v1, t2, t3 4192 WHERE t3.pk = v1.a AND t2.b = 1 AND t2.b = t3.pk AND v1.a BETWEEN 2 AND 5; 4193 4194SELECT * FROM v1, t2, t3 4195 WHERE t3.pk = v1.a AND t2.b = 1 AND t2.b = t3.pk AND v1.a BETWEEN 2 AND 5; 4196 4197DROP VIEW v1; 4198DROP TABLE t1, t2, t3; 4199 4200--echo # 4201--echo # Bug#717577: substitution for best field in a query over a view and 4202--echo # with OR in the WHERE condition 4203--echo # 4204 4205create table t1 (a int, b int); 4206insert into t1 values (2,4), (1,3); 4207create table t2 (c int); 4208insert into t2 values (6), (4), (1), (3), (8), (3), (4), (2); 4209 4210select * from t1,t2 where t2.c=t1.a and t2.c < 3 or t2.c=t1.b and t2.c >=4; 4211explain extended 4212select * from t1,t2 where t2.c=t1.a and t2.c < 3 or t2.c=t1.b and t2.c >=4; 4213 4214create view v1 as select * from t2; 4215select * from t1,v1 where v1.c=t1.a and v1.c < 3 or v1.c=t1.b and v1.c >=4; 4216explain extended 4217select * from t1,v1 where v1.c=t1.a and v1.c < 3 or v1.c=t1.b and v1.c >=4; 4218 4219create view v2 as select * from v1; 4220select * from t1,v2 where v2.c=t1.a and v2.c < 3 or v2.c=t1.b and v2.c >=4; 4221explain extended 4222select * from t1,v2 where v2.c=t1.a and v2.c < 3 or v2.c=t1.b and v2.c >=4; 4223 4224create view v3 as select * from t1; 4225select * from v3,v2 where v2.c=v3.a and v2.c < 3 or v2.c=v3.b and v2.c >=4; 4226explain extended 4227select * from v3,v2 where v2.c=v3.a and v2.c < 3 or v2.c=v3.b and v2.c >=4; 4228 4229drop view v1,v2,v3; 4230drop table t1,t2; 4231 4232--echo # 4233--echo # Bug#724942: substitution of the constant into a view field 4234--echo # 4235 4236CREATE TABLE t1 (a int); 4237INSERT INTO t1 VALUES (2), (9), (9), (6), (5), (4), (7); 4238 4239CREATE VIEW v1 AS SELECT * FROM t1; 4240 4241SELECT * FROM v1 WHERE a > -1 OR a > 6 AND a = 3; 4242EXPLAIN EXTENDED 4243SELECT * FROM v1 WHERE a > -1 OR a > 6 AND a = 3; 4244 4245SELECT * FROM v1 WHERE a > -1 OR a AND a = 0; 4246EXPLAIN EXTENDED 4247SELECT * FROM v1 WHERE a > -1 OR a AND a = 0; 4248 4249CREATE VIEW v2 AS SELECT * FROM v1; 4250 4251SELECT * FROM v2 WHERE a > -1 OR a AND a = 0; 4252EXPLAIN EXTENDED 4253SELECT * FROM v2 WHERE a > -1 OR a AND a = 0; 4254 4255DROP VIEW v1,v2; 4256DROP TABLE t1; 4257 4258CREATE TABLE t1 (a varchar(10), KEY (a)) ; 4259INSERT INTO t1 VALUES 4260 ('DD'), ('ZZ'), ('ZZ'), ('KK'), ('FF'), ('HH'), ('MM'), 4261 ('AA'), ('DD'), ('CC'), ('GG'); 4262 4263CREATE VIEW v1 AS SELECT * FROM t1; 4264 4265--echo # t1 and v1 should return the same result set 4266SELECT * FROM v1 WHERE a > 'JJ' OR a <> 0 AND a = 'VV'; 4267SELECT * FROM t1 WHERE a > 'JJ' OR a <> 0 AND a = 'VV'; 4268--echo # t1 and v1 should propagate constants in the same way 4269EXPLAIN EXTENDED 4270SELECT * FROM v1 WHERE a > 'JJ' OR a <> 0 AND a = 'VV'; 4271EXPLAIN EXTENDED 4272SELECT * FROM t1 WHERE a > 'JJ' OR a <> 0 AND a = 'VV'; 4273 4274--echo # t1 and v1 should return the same result set 4275SELECT * FROM v1 WHERE a > 'JJ' OR a AND a = 'VV'; 4276SELECT * FROM t1 WHERE a > 'JJ' OR a AND a = 'VV'; 4277--echo # t1 and v1 should propagate constants in the same way 4278EXPLAIN EXTENDED 4279SELECT * FROM v1 WHERE a > 'JJ' OR a AND a = 'VV'; 4280EXPLAIN EXTENDED 4281SELECT * FROM t1 WHERE a > 'JJ' OR a AND a = 'VV'; 4282 4283DROP VIEW v1; 4284DROP TABLE t1; 4285 4286--echo # 4287--echo # Bug#777745: crash with equality propagation 4288--echo # over view fields 4289--echo # 4290 4291CREATE TABLE t1 (a int NOT NULL ) ; 4292INSERT INTO t1 VALUES (2), (1); 4293 4294CREATE TABLE t2 (a int NOT NULL , b int NOT NULL) ; 4295INSERT INTO t2 VALUES (2,20),(2,30); 4296 4297CREATE VIEW v2 AS SELECT * FROM t2; 4298 4299EXPLAIN 4300SELECT * FROM t1,v2 4301 WHERE v2.a = t1.a AND v2.a = 2 AND v2.a IS NULL AND t1.a != 0; 4302SELECT * FROM t1,v2 4303 WHERE v2.a = t1.a AND v2.a = 2 AND v2.a IS NULL AND t1.a != 0; 4304 4305EXPLAIN 4306SELECT * FROM t1,v2 4307 WHERE v2.a = t1.a AND v2.a = 2 AND v2.a+1 > 2 AND t1.a != 0; 4308SELECT * FROM t1,v2 4309 WHERE v2.a = t1.a AND v2.a = 2 AND v2.a+1 > 2 AND t1.a != 0; 4310 4311DROP VIEW v2; 4312DROP TABLE t1,t2; 4313 4314--echo # 4315--echo # Bug#794038: crash with INSERT/UPDATE/DELETE 4316--echo # over a non-updatable view 4317--echo # 4318 4319CREATE TABLE t1 (a int); 4320CREATE ALGORITHM = TEMPTABLE VIEW v1 AS SELECT * FROM t1; 4321CREATE ALGORITHM = MERGE VIEW v2 AS SELECT * FROM v1; 4322CREATE ALGORITHM = TEMPTABLE VIEW v3 AS SELECT * FROM v2; 4323 4324-- error ER_NON_INSERTABLE_TABLE 4325INSERT INTO v3 VALUES (1); 4326-- error ER_NON_UPDATABLE_TABLE 4327UPDATE v3 SET a=0; 4328-- error ER_NON_UPDATABLE_TABLE 4329DELETE FROM v3; 4330 4331DROP VIEW v1,v2,v3; 4332DROP TABLE t1; 4333 4334--echo # 4335--echo # Bug#798621: crash with a view string field equal 4336--echo # to a constant 4337--echo # 4338 4339CREATE TABLE t1 (a varchar(32), b int) ; 4340INSERT INTO t1 VALUES ('j', NULL), ('c', 8), ('c', 1); 4341CREATE VIEW v1 AS SELECT * FROM t1; 4342 4343CREATE TABLE t2 (a varchar(32)) ; 4344INSERT INTO t2 VALUES ('j'), ('c'); 4345 4346SELECT * FROM v1 LEFT JOIN t2 ON t2.a = v1.a 4347 WHERE v1.b = 1 OR v1.a = 'a' AND LENGTH(v1.a) >= v1.b; 4348EXPLAIN EXTENDED 4349SELECT * FROM v1 LEFT JOIN t2 ON t2.a = v1.a 4350 WHERE v1.b = 1 OR v1.a = 'a' AND LENGTH(v1.a) >= v1.b; 4351 4352DROP VIEW v1; 4353DROP TABLE t1,t2; 4354 4355--echo # Bug#798625: duplicate of the previous one, but without crash 4356 4357CREATE TABLE t1 (f1 int NOT NULL, f2 int, f3 int, f4 varchar(32), f5 int) ; 4358INSERT INTO t1 VALUES (20,5,2,'r', 0); 4359 4360CREATE VIEW v1 AS SELECT * FROM t1; 4361 4362SELECT v1.f4 FROM v1 4363 WHERE f1<>0 OR f2<>0 AND f4='v' AND (f2<>0 OR f3<>0 AND f5<>0 OR f4 LIKE '%b%'); 4364EXPLAIN EXTENDED 4365SELECT v1.f4 FROM v1 4366 WHERE f1<>0 OR f2<>0 AND f4='v' AND (f2<>0 OR f3<>0 AND f5<>0 OR f4 LIKE '%b%'); 4367 4368DROP VIEW v1; 4369DROP TABLE t1; 4370 4371--echo # 4372--echo # Bug#798576: abort on a GROUP BY query over a view with left join 4373--echo # that can be converted to inner join 4374--echo # 4375 4376CREATE TABLE t1 (a int NOT NULL , b int NOT NULL) ; 4377INSERT INTO t1 VALUES (214,0), (6,6), (6,0), (7,0); 4378 4379CREATE TABLE t2 (b int) ; 4380INSERT INTO t2 VALUES (88), (78), (6); 4381 4382CREATE ALGORITHM=MERGE VIEW v1 AS 4383 SELECT t1.a, t2.b FROM (t2 LEFT JOIN t1 ON t2.b > t1.a) WHERE t1.b <= 0; 4384 4385SELECT * FROM v1; 4386SELECT a, MIN(b) FROM v1 GROUP BY a; 4387 4388DROP VIEW v1; 4389DROP TABLE t1,t2; 4390 4391--echo # 4392--echo # LP bug #793386: unexpected 'Duplicate column name ''' error 4393--echo # at the second execution of a PS using a view 4394--echo # 4395 4396CREATE TABLE t1 (f1 int, f2 int, f3 int, f4 int); 4397 4398CREATE VIEW v1 AS 4399 SELECT t.f1, t.f2, s.f3, s.f4 FROM t1 t, t1 s 4400 WHERE t.f4 >= s.f2 AND s.f3 < 0; 4401 4402PREPARE stmt1 FROM 4403 "SELECT s.f1 AS f1, s.f2 AS f2, s.f3 AS f3, t.f4 AS f4 4404 FROM v1 AS t LEFT JOIN v1 AS s ON t.f4=s.f4 WHERE t.f2 <> 1225"; 4405EXECUTE stmt1; 4406EXECUTE stmt1; 4407 4408DEALLOCATE PREPARE stmt1; 4409 4410DROP VIEW v1; 4411DROP TABLE t1; 4412 4413--echo # 4414--echo # LP BUG#806071 (2 views with ORDER BY) 4415--echo # 4416 4417CREATE TABLE t1 (f1 int); 4418INSERT INTO t1 VALUES (1),(1); 4419 4420CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT f1 FROM t1; 4421CREATE ALGORITHM=MERGE VIEW v2 AS SELECT f1 FROM v1 ORDER BY f1; 4422 4423SELECT * FROM v2 AS a1, v2 AS a2; 4424EXPLAIN EXTENDED SELECT * FROM v2 AS a1, v2 AS a2; 4425 4426DROP VIEW v1, v2; 4427DROP TABLE t1; 4428 4429--echo # 4430--echo # LP bug #823189: dependent subquery with RIGHT JOIN 4431--echo # referencing view in WHERE 4432--echo # 4433 4434CREATE TABLE t1 (a varchar(32)); 4435INSERT INTO t1 VALUES ('y'), ('w'); 4436 4437CREATE TABLE t2 (a int); 4438INSERT INTO t2 VALUES (10); 4439 4440CREATE TABLE t3 (a varchar(32), b int); 4441 4442CREATE TABLE t4 (a varchar(32)); 4443INSERT INTO t4 VALUES ('y'), ('w'); 4444 4445CREATE VIEW v1 AS SELECT * FROM t1; 4446 4447EXPLAIN EXTENDED 4448SELECT * FROM t1, t2 4449 WHERE t2.a NOT IN (SELECT t3.b FROM t3 RIGHT JOIN t4 ON (t4.a = t3.a) 4450 WHERE t4.a >= t1.a); 4451SELECT * FROM t1, t2 4452 WHERE t2.a NOT IN (SELECT t3.b FROM t3 RIGHT JOIN t4 ON (t4.a = t3.a) 4453 WHERE t4.a >= t1.a); 4454 4455EXPLAIN EXTENDED 4456SELECT * FROM v1, t2 4457 WHERE t2.a NOT IN (SELECT t3.b FROM t3 RIGHT JOIN t4 ON (t4.a = t3.a) 4458 WHERE t4.a >= v1.a); 4459SELECT * FROM v1, t2 4460 WHERE t2.a NOT IN (SELECT t3.b FROM t3 RIGHT JOIN t4 ON (t4.a = t3.a) 4461 WHERE t4.a >= v1.a); 4462 4463DROP VIEW v1; 4464DROP TABLE t1,t2,t3,t4; 4465 4466--echo # 4467--echo # LP bug #823237: dependent subquery with LEFT JOIN 4468--echo # referencing view in WHERE 4469--echo # (duplicate of LP bug #823189) 4470--echo # 4471 4472CREATE TABLE t1 (a int); 4473 4474CREATE TABLE t2 ( b int, d int, e int); 4475INSERT INTO t2 VALUES (7,8,0); 4476 4477CREATE TABLE t3 ( c int); 4478INSERT INTO t3 VALUES (0); 4479 4480CREATE TABLE t4 (a int, b int, c int); 4481INSERT INTO t4 VALUES (93,1,0), (95,NULL,0); 4482 4483CREATE VIEW v4 AS SELECT * FROM t4; 4484 4485EXPLAIN EXTENDED 4486SELECT * FROM t3 , t4 4487 WHERE t4.c <= (SELECT t2.e FROM t2 LEFT JOIN t1 ON ( t1.a = t2.d ) 4488 WHERE t2.b > t4.b); 4489SELECT * FROM t3 , t4 4490 WHERE t4.c <= (SELECT t2.e FROM t2 LEFT JOIN t1 ON ( t1.a = t2.d ) 4491 WHERE t2.b > t4.b); 4492 4493 4494EXPLAIN EXTENDED 4495SELECT * FROM t3, v4 4496 WHERE v4.c <= (SELECT t2.e FROM t2 LEFT JOIN t1 ON ( t1.a = t2.d ) 4497 WHERE t2.b > v4.b); 4498 4499SELECT * FROM t3, v4 4500 WHERE v4.c <= (SELECT t2.e FROM t2 LEFT JOIN t1 ON ( t1.a = t2.d ) 4501 WHERE t2.b > v4.b); 4502 4503DROP VIEW v4; 4504DROP TABLE t1,t2,t3,t4; 4505 4506# 4507# Bug#9801 (Views: imperfect error message) 4508# 4509 4510--disable_warnings 4511drop table if exists t_9801; 4512drop view if exists v_9801; 4513--enable_warnings 4514 4515create table t_9801 (s1 int); 4516 4517--error ER_VIEW_NONUPD_CHECK 4518create view v_9801 as 4519 select sum(s1) from t_9801 with check option; 4520 4521--error ER_VIEW_NONUPD_CHECK 4522create view v_9801 as 4523 select sum(s1) from t_9801 group by s1 with check option; 4524 4525 --error ER_VIEW_NONUPD_CHECK 4526create view v_9801 as 4527 select sum(s1) from t_9801 group by s1 with rollup with check option; 4528 4529drop table t_9801; 4530 4531--echo # 4532--echo # Bug #47335 assert in get_table_share 4533--echo # 4534 4535--disable_warnings 4536DROP TABLE IF EXISTS t1; 4537DROP VIEW IF EXISTS v1; 4538--enable_warnings 4539 4540CREATE TEMPORARY TABLE t1 (id INT); 4541--error ER_NO_SUCH_TABLE 4542ALTER VIEW t1 AS SELECT 1 AS f1; 4543DROP TABLE t1; 4544 4545CREATE VIEW v1 AS SELECT 1 AS f1; 4546CREATE TEMPORARY TABLE v1 (id INT); 4547ALTER VIEW v1 AS SELECT 2 AS f1; 4548DROP TABLE v1; 4549SELECT * FROM v1; 4550DROP VIEW v1; 4551 4552 4553--echo # 4554--echo # Bug #47635 assert in start_waiting_global_read_lock 4555--echo # during CREATE VIEW 4556--echo # 4557 4558--disable_warnings 4559DROP TABLE IF EXISTS t1, t2; 4560DROP VIEW IF EXISTS t2; 4561--enable_warnings 4562 4563CREATE TABLE t1 (f1 integer); 4564CREATE TEMPORARY TABLE IF NOT EXISTS t1 (f1 integer); 4565CREATE TEMPORARY TABLE t2 (f1 integer); 4566DROP TABLE t1; 4567FLUSH TABLES WITH READ LOCK; 4568--error ER_CANT_UPDATE_WITH_READLOCK 4569CREATE VIEW t2 AS SELECT * FROM t1; 4570 4571UNLOCK TABLES; 4572DROP TABLE t1, t2; 4573 4574 4575--echo # 4576--echo # Bug#48315 Metadata lock is not taken for merged views that 4577--echo # use an INFORMATION_SCHEMA table 4578--echo # 4579 4580--disable_warnings 4581DROP VIEW IF EXISTS v1; 4582DROP PROCEDURE IF EXISTS p1; 4583--enable_warnings 4584 4585connect (con2, localhost, root); 4586connect (con3, localhost, root); 4587 4588connection default; 4589 4590CREATE VIEW v1 AS SELECT schema_name FROM information_schema.schemata; 4591CREATE PROCEDURE p1() SELECT COUNT(*), GET_LOCK('blocker', 100) FROM v1; 4592 4593--echo # CALL p1() so the view is merged. 4594--disable_result_log 4595CALL p1(); 4596--enable_result_log 4597SELECT RELEASE_LOCK('blocker'); 4598 4599connection con3; 4600SELECT GET_LOCK('blocker', 100); 4601 4602connection default; 4603--echo # Try to CALL p1() again, this time it should block on "blocker". 4604--echo # Sending: 4605--send CALL p1() 4606 4607connection con2; 4608let $wait_condition= 4609 SELECT COUNT(*) = 1 from information_schema.processlist 4610 WHERE state = "User lock" AND 4611 info = "SELECT COUNT(*), GET_LOCK('blocker', 100) FROM v1"; 4612--source include/wait_condition.inc 4613--echo # ... then try to drop the view. This should block. 4614--echo # Sending: 4615--send DROP VIEW v1 4616 4617connection con3; 4618let $wait_condition= 4619 SELECT COUNT(*) = 1 from information_schema.processlist 4620 WHERE state = "Waiting for table metadata lock" AND info = "DROP VIEW v1"; 4621--source include/wait_condition.inc 4622--echo # Now allow CALL p1() to complete 4623SELECT RELEASE_LOCK('blocker'); 4624 4625connection default; 4626--echo # Reaping: CALL p1() 4627--disable_result_log 4628--reap 4629--enable_result_log 4630SELECT RELEASE_LOCK('blocker'); 4631 4632connection con2; 4633--echo # Reaping: DROP VIEW v1 4634--reap 4635 4636connection default; 4637DROP PROCEDURE p1; 4638disconnect con2; 4639disconnect con3; 4640 4641 4642--echo # 4643--echo # Bug#12626844: WRONG ERROR MESSAGE WHILE CREATING A VIEW ON A 4644--echo # NON EXISTING DATABASE 4645--echo # 4646 4647--disable_warnings 4648DROP DATABASE IF EXISTS nodb; 4649--enable_warnings 4650--error ER_BAD_DB_ERROR 4651CREATE VIEW nodb.a AS SELECT 1; 4652 4653 4654--echo # 4655--echo # BUG#14117018 - MYSQL SERVER CREATES INVALID VIEW DEFINITION 4656--echo # BUG#18405221 - SHOW CREATE VIEW OUTPUT INCORRECT 4657--echo # 4658 4659CREATE VIEW v1 AS (SELECT '' FROM DUAL); 4660CREATE VIEW v2 AS (SELECT 'BUG#14117018' AS col1 FROM DUAL) UNION ALL 4661 (SELECT '' FROM DUAL); 4662CREATE VIEW v3 AS (SELECT 'BUG#14117018' AS col1 FROM DUAL) UNION ALL 4663 (SELECT '' FROM DUAL) UNION ALL 4664 (SELECT '' FROM DUAL); 4665CREATE VIEW v4 AS (SELECT 'BUG#14117018' AS col1 FROM DUAL) UNION ALL 4666 (SELECT '' AS col2 FROM DUAL) UNION ALL 4667 (SELECT '' FROM DUAL); 4668 4669# In the second (and later) UNIONed queries, duplicate column names are allowed 4670CREATE VIEW v5 AS (SELECT 'buggy' AS col1, 'fix' as col2 FROM DUAL) UNION ALL 4671 (SELECT 'buggy' as a, 'fix' as a FROM DUAL); 4672 4673--echo # Name for the column in select1 is set properly with or 4674--echo # without this fix. 4675SHOW CREATE VIEW v1; 4676 4677--echo # Name for the column in select2 is set with this fix. 4678--echo # Without this fix, name would not have set for the 4679--echo # columns in select2. 4680SHOW CREATE VIEW v2; 4681 4682--echo # Name for the field item in select2 & select3 is set with this fix. 4683--echo # Without this fix, name would not have set for the 4684--echo # columns in select2 & select3. 4685SHOW CREATE VIEW v3; 4686 4687--echo # Name for the field item in select3 is set with this fix. 4688--echo # Without this fix, name would not have set for the 4689--echo # columns in select3. 4690SHOW CREATE VIEW v4; 4691 4692DROP VIEW v1, v2, v3, v4, v5; 4693 4694 4695--echo # 4696--echo # BUG#19886430: VIEW CREATION WITH NAMED COLUMNS, OVER UNION, 4697--echo # IS REJECTED 4698 4699--echo # Without the patch, reports an error. 4700CREATE VIEW v1 (fld1, fld2) AS 4701 SELECT 1 AS a, 2 AS b 4702 UNION ALL 4703 SELECT 1 AS a, 1 AS a; 4704 4705--echo # The column names are explicitly specified and not duplicates, hence 4706--echo # succeeds. 4707CREATE VIEW v2 (fld1, fld2) AS 4708 SELECT 1 AS a, 2 AS a 4709 UNION ALL 4710 SELECT 1 AS a, 1 AS a; 4711 4712--echo # The column name in the first SELECT are not duplicates, hence succeeds. 4713CREATE VIEW v3 AS 4714 SELECT 1 AS a, 2 AS b 4715 UNION ALL 4716 SELECT 1 AS a, 1 AS a; 4717 4718--echo # Should report an error, since the explicitly specified column names are 4719--echo # duplicates. 4720--error ER_DUP_FIELDNAME 4721CREATE VIEW v4 (fld1, fld1) AS 4722 SELECT 1 AS a, 2 AS b 4723 UNION ALL 4724 SELECT 1 AS a, 1 AS a; 4725 4726--echo # Should report an error, since duplicate column name is specified in the 4727--echo # First SELECT. 4728--error ER_DUP_FIELDNAME 4729CREATE VIEW v4 AS 4730 SELECT 1 AS a, 2 AS a 4731 UNION ALL 4732 SELECT 1 AS a, 1 AS a; 4733 4734--echo # Cleanup 4735DROP VIEW v1, v2, v3; 4736 4737 4738# Check that all connections opened by test cases in this file are really 4739# gone so execution of other tests won't be affected by their presence. 4740--source include/wait_until_count_sessions.inc 4741 4742--echo # 4743--echo # lp:833600 Wrong result with view + outer join + uncorrelated subquery (non-semijoin) 4744--echo # 4745 4746CREATE TABLE t1 ( a int, b int ); 4747INSERT INTO t1 VALUES (0,0),(0,0); 4748 4749CREATE TABLE t2 ( a int, b int ); 4750INSERT IGNORE INTO t2 VALUES (1,0),(1,0); 4751 4752CREATE TABLE t3 ( b int ); 4753INSERT IGNORE INTO t3 VALUES (0),(0); 4754 4755CREATE OR REPLACE VIEW v2 AS SELECT * FROM t2; 4756SELECT * FROM t1 RIGHT JOIN v2 ON ( v2.a = t1.a ) WHERE v2.b IN ( SELECT b FROM t3 ) AND t1.b IS NULL ; 4757 4758SELECT * FROM t1 RIGHT JOIN v2 ON ( v2.a = t1.a ) WHERE v2.b IN ( SELECT b FROM t3 ) AND t1.b IS NULL ; 4759 4760DROP VIEW v2; 4761DROP TABLE t1, t2, t3; 4762 4763--echo # 4764--echo # BUG#915222: Valgrind complains or crashes with INSERT SELECT 4765--echo # within a trigger that uses a view 4766--echo # 4767 4768CREATE TABLE t1 (a char(1)); 4769 4770CREATE TABLE t2 (d int, e char(1)); 4771 4772INSERT INTO t2 VALUES (13,'z'); 4773 4774CREATE TRIGGER tr AFTER UPDATE ON t2 4775 FOR EACH ROW 4776 REPLACE INTO t3 4777 SELECT f, a AS alias FROM t3, v; 4778 4779CREATE TABLE t3 (f int, g char(8)); 4780 4781CREATE VIEW v AS SELECT a, e FROM t2, t1; 4782 4783UPDATE t2 SET d=7; 4784UPDATE t2 SET d=7; 4785UPDATE t2 SET d=7; 4786UPDATE t2 SET d=7; 4787 4788DROP TRIGGER tr; 4789DROP VIEW v; 4790DROP TABLE t1,t2,t3; 4791 4792--echo # 4793--echo # BUG#972943: Assertion failure with INSERT SELECT within a trigger 4794--echo # that uses derived table and materialized view 4795--echo # 4796 4797CREATE TABLE t1 (a int, b int); 4798INSERT INTO t1 VALUES (1,0), (2,8); 4799 4800CREATE ALGORITHM=TEMPTABLE VIEW v1 4801 AS SELECT * FROM t1; 4802 4803CREATE TABLE t2 (c int); 4804CREATE TABLE t3 (d int, e int); 4805 4806CREATE TRIGGER tr BEFORE INSERT ON t2 FOR EACH ROW 4807 INSERT INTO t3 4808 SELECT t1.* 4809 FROM (SELECT * FROM t1 WHERE b IN (SELECT b FROM v1)) AS alias1, t1 4810 WHERE t1.a = 3 OR t1.a > 5; 4811 4812INSERT INTO t2 VALUES (1); 4813 4814DROP TRIGGER tr; 4815DROP VIEW v1; 4816DROP TABLE t1,t2,t3; 4817 4818--echo # 4819--echo # LP bug#1007622 Server crashes in handler::increment_statistics on 4820--echo # inserting into a view over a view 4821--echo # 4822 4823flush status; 4824--disable_ps_protocol 4825CREATE TABLE t1 (a INT); 4826CREATE ALGORITHM=MERGE VIEW v1 AS SELECT a1.* FROM t1 AS a1, t1 AS a2; 4827CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM v1; 4828INSERT INTO v2 (a) VALUES (1) ; 4829select * from t1; 4830drop view v2,v1; 4831drop table t1; 4832show status like '%view%'; 4833show status like 'Opened_table%'; 4834--enable_ps_protocol 4835 4836--echo # 4837--echo # MDEV-486 LP BUG#1010116 Incorrect query results in 4838--echo # view and derived tables 4839--echo # 4840 4841SELECT 4842`Derived1`.`id`, 4843`Derived2`.`Val1` 4844FROM (select 30631 as `id`) AS `Derived1` LEFT OUTER JOIN (SELECT 48452 as `id`, 48461 AS `Val1` 4847FROM (select 30631 as `id`) AS `Derived3`) AS `Derived2` ON `Derived1`.`id` = `Derived2`.`id`; 4848 4849create table t1 ( id int ); 4850insert into t1 values (30631); 4851create table t2 ( id int ); 4852insert into t2 values (30631); 4853create algorithm=MERGE view v2 as select 2 as id, 1 as val1 from t2; 4854select t1.*, v2.* from t1 left join v2 on t1.id = v2.id; 4855drop view v2; 4856drop table t1,t2; 4857 4858create table t1 ( id int ); 4859insert into t1 values (30631); 4860create table t2 ( id int ); 4861insert into t2 values (30631); 4862create algorithm=MERGE view v2 as select 2 as id, id is null as bbb, id as iddqd, 1 as val1 from t2; 4863select t1.*, v2.* from t1 left join v2 on t1.id = v2.id; 4864drop view v2; 4865drop table t1,t2; 4866 4867--echo # 4868--echo # MDEV-3914: Wrong result (NULLs instead of real values) 4869--echo # with INNER and RIGHT JOIN in a FROM subquery, derived_merge=on 4870--echo # (fix of above MDEV-486 fix) 4871--echo # 4872SET @save_optimizer_switch_MDEV_3914=@@optimizer_switch; 4873SET optimizer_switch = 'derived_merge=on'; 4874 4875CREATE TABLE t1 (a INT) ENGINE=MyISAM; 4876INSERT INTO t1 VALUES (1),(2); 4877 4878CREATE TABLE t2 (b INT) ENGINE=MyISAM; 4879INSERT INTO t2 VALUES (3),(4); 4880 4881CREATE TABLE t3 (c INT) ENGINE=MyISAM; 4882INSERT INTO t3 VALUES (5),(6); 4883 4884SELECT * FROM ( SELECT c FROM ( t1 INNER JOIN t2 ) RIGHT JOIN t3 ON a = c ) AS alias; 4885 4886SET optimizer_switch = 'derived_merge=off'; 4887 4888SELECT * FROM ( SELECT c FROM ( t1 INNER JOIN t2 ) RIGHT JOIN t3 ON a = c ) AS alias; 4889 4890SET optimizer_switch=@save_optimizer_switch_MDEV_3914; 4891drop table t1,t2,t3; 4892 4893--echo # 4894--echo # MDEV-589 (LP BUG#1007647) : 4895--echo # Assertion `vcol_table == 0 || vcol_table == table' failed in 4896--echo # fill_record(THD*, List<Item>&, List<Item>&, bool) 4897--echo # 4898CREATE TABLE t1 (f1 INT, f2 INT); 4899CREATE TABLE t2 (f1 INT, f2 INT); 4900CREATE ALGORITHM=MERGE VIEW v1 AS SELECT a1.f1, a2.f2 FROM t1 AS a1, t1 AS a2; 4901CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM v1; 4902CREATE ALGORITHM=MERGE VIEW v3 AS SELECT a1.f1, a2.f2 FROM t1 AS a1, t2 AS a2; 4903CREATE ALGORITHM=MERGE VIEW v4 AS SELECT * FROM v3; 4904--error ER_VIEW_MULTIUPDATE 4905INSERT INTO v3 (f1, f2) VALUES (1, 2); 4906--error ER_VIEW_MULTIUPDATE 4907INSERT INTO v1 (f1, f2) VALUES (1, 2); 4908--error ER_VIEW_MULTIUPDATE 4909INSERT INTO v4 (f1, f2) VALUES (1, 2); 4910--error ER_VIEW_MULTIUPDATE 4911INSERT INTO v2 (f1, f2) VALUES (1, 2); 4912drop view v4,v3,v2,v1; 4913drop table t1,t2; 4914 4915--echo # 4916--echo # MDEV-3799 fix of above bugfix (MDEV-589) 4917--echo # Wrong result (NULLs instead of real values) with RIGHT JOIN 4918--echo # in a FROM subquery and derived_merge=on 4919--echo # 4920 4921CREATE TABLE t1 (f1 INT) ENGINE=MyISAM; 4922INSERT INTO t1 VALUES (4),(6); 4923 4924CREATE TABLE t2 (f2 INT) ENGINE=MyISAM; 4925INSERT INTO t2 VALUES (7),(8); 4926 4927SELECT * FROM ( 4928 SELECT * FROM t1 RIGHT JOIN t2 ON f1 = f2 4929) AS alias; 4930 4931SELECT * FROM ( 4932 SELECT * FROM t2 LEFT JOIN t1 ON f1 = f2 4933) AS alias; 4934 4935drop tables t1,t2; 4936 4937 4938--echo # 4939--echo # MDEV-3876 Wrong result (extra rows) with ALL subquery 4940--echo # from a MERGE view (duplicate of MDEV-3873) 4941--echo # 4942 4943CREATE TABLE t1 (a INT NOT NULL) ENGINE=MyISAM; 4944INSERT INTO t1 VALUES (1),(2); 4945 4946CREATE TABLE t2 (b INT NOT NULL) ENGINE=MyISAM; 4947INSERT INTO t2 VALUES (1),(3); 4948 4949CREATE OR REPLACE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t2; 4950 4951SELECT a FROM t1 AS alias 4952WHERE a >= ALL ( 4953SELECT b FROM t1 LEFT JOIN v1 ON (a = b) 4954WHERE a = alias.a ); 4955 4956drop view v1; 4957drop table t1,t2; 4958 4959--echo # 4960--echo # MDEV-4593: p_s: crash in simplify_joins with delete using subselect 4961--echo # from view 4962--echo # 4963 4964create table `t1`(`a` int); 4965create table `t2`(`a` int); 4966create or replace view `v1` as select `a` from `t1`; 4967prepare s from "delete from `t2` order by (select 1 from `v1`)"; 4968execute s; 4969deallocate prepare s; 4970drop view v1; 4971drop tables t1,t2; 4972 4973--echo # 4974--echo # MDEV-5034 (duplicate of MDEV-5107): 4975--echo # Left Join Yields All Nulls Instead of Appropriate Matches 4976--echo # 4977 4978 --echo # test #1 4979 4980CREATE TABLE t1 (state VARCHAR(32), INDEX(state)); 4981INSERT INTO t1 VALUES ('Indiana'),('Vermont'); 4982 4983CREATE TABLE t2 (state VARCHAR(32)); 4984INSERT INTO t2 VALUES ('Hawaii'),('Oregon'),('Vermont'); 4985 4986CREATE ALGORITHM=MERGE VIEW v1 AS SELECT t1.* FROM t2, t1; 4987SELECT * FROM t1 AS outer_t1 LEFT JOIN v1 AS joined_t1 4988ON (joined_t1.state = outer_t1.state AND joined_t1.state IN ( SELECT 'Vermont' UNION SELECT 'Florida' ) ); 4989 4990SELECT * FROM t1 AS outer_t1 LEFT JOIN (SELECT t1.* FROM t2, t1) AS joined_t1 ON (joined_t1.state = outer_t1.state AND joined_t1.state IN ( SELECT 'Vermont' UNION SELECT 'Florida' ) ); 4991 4992drop view v1; 4993drop table t1, t2; 4994 4995--echo # test #1 4996 4997CREATE TABLE t1 (a INT, b VARCHAR(1), INDEX(b,a)); 4998INSERT INTO t1 VALUES (4,'p'),(1,'q'),(9,'w'); 4999 5000CREATE TABLE t2 (c VARCHAR(1), INDEX(c)); 5001INSERT INTO t2 VALUES ('q'),('a'); 5002CREATE ALGORITHM=MERGE VIEW v1 AS SELECT t1a.* FROM t1, t1 AS t1a; 5003SELECT * FROM t2 LEFT JOIN v1 ON ( c=b AND a IN ( 1,6 ) ); 5004CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT t1a.* FROM t1, t1 AS t1a; 5005SELECT * FROM t2 LEFT JOIN v1 ON ( c=b AND a IN ( 1,6 ) ); 5006 5007drop view v1; 5008drop table t1,t2; 5009 5010--echo # 5011--echo # MDEV-5153: Server crashes in Item_ref::fix_fields on 2nd execution 5012--echo # of PS with LEFT JOIN and MERGE view or SELECT SQ 5013--echo # 5014 5015CREATE TABLE t1 (i1 INT, c1 VARCHAR(6)) ENGINE=MyISAM; 5016INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); 5017 5018CREATE TABLE t2 (c2 VARCHAR(6)) ENGINE=MyISAM; 5019INSERT INTO t2 VALUES ('foobar'),('qux'); 5020 5021CREATE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1 WHERE ( c1 ) IN ( SELECT c2 FROM t2 ) AND i1 <= 2 ; 5022 5023PREPARE stmt FROM 'SELECT * FROM t1 LEFT JOIN v1 ON (v1.i1 = t1.i1)'; 5024 5025EXECUTE stmt; 5026EXECUTE stmt; 5027 5028drop view v1; 5029CREATE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1 WHERE ( c1, c1 ) IN ( SELECT c2, c2 FROM t2 ) AND i1 <= 2 ; 5030 5031EXECUTE stmt; 5032EXECUTE stmt; 5033 5034deallocate prepare stmt; 5035drop view v1; 5036drop table t1,t2; 5037 5038# 5039# MDEV-5414: RAND() in a subselect : different behavior in MariaDB and MySQL 5040# 5041create table t1 (a int); 5042insert into t1 values (1),(2); 5043 5044create view v1 (a,r) as select a,rand() from t1; 5045 5046 5047create table t2 select a, r as r1, r as r2, r as r3 from v1; 5048 5049select a, r1 = r2, r2 = r3 from t2; 5050 5051drop view v1; 5052drop table t1,t2; 5053 5054--echo # 5055--echo # MDEV-5515: 2nd execution of a prepared statement returns wrong results 5056--echo # 5057CREATE TABLE t1 (i1 INT, j1 INT NOT NULL, PRIMARY KEY (i1)); 5058 5059INSERT INTO t1 VALUES (30,300),(40,400); 5060 5061CREATE TABLE t2 (i2 INT); 5062INSERT INTO t2 VALUES (50),(60); 5063 5064CREATE TABLE t3 (c3 VARCHAR(20), i3 INT); 5065INSERT INTO t3 VALUES ('a',10),('b',2); 5066 5067CREATE TABLE t4 (i4 INT); 5068INSERT INTO t4 VALUES (1),(2); 5069 5070DROP VIEW IF EXISTS v1; 5071CREATE VIEW v1 AS select coalesce(j1,i3) AS v1_field1 from t2 join t3 left join t1 on ( i1 = i2 ); 5072 5073CREATE VIEW v2 AS select v1_field1 from t4 join v1; 5074 5075prepare my_stmt from "select v1_field1 from v2"; 5076execute my_stmt; 5077execute my_stmt; 5078deallocate prepare my_stmt; 5079 5080DROP VIEW v1,v2; 5081DROP TABLE t1,t2,t3,t4; 5082 5083--echo # 5084--echo #MDEV-5717: Server crash with insert statement containing DEFAULT into 5085--echo #view 5086--echo # 5087CREATE TABLE t1 ( 5088 `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 5089 `test` tinyint(3) unsigned NOT NULL DEFAULT '0', 5090 PRIMARY KEY (`id`) 5091); 5092 5093CREATE VIEW v1 AS (select t1.id AS id, t1.test AS test from t1); 5094 5095INSERT INTO v1 SET test = DEFAULT; 5096 5097select * from v1; 5098 5099drop view v1; 5100drop table t1; 5101 5102--echo # 5103--echo # MDEV-5981: name resolution issues with views and multi-update 5104--echo # in ps-protocol 5105--echo # 5106 5107create table t1 (id1 int primary key, val1 varchar(20)); 5108insert into t1 values (1, 'test1'); 5109create table t2 (id2 int primary key, val2 varchar(20)); 5110insert into t2 values (1, 'test2'); 5111create algorithm=merge view v1 as select id1 as id1v1, val1 as val1v1 from t1; 5112create algorithm=merge view v2 as 5113select t2.id2 as id2v2, t2.val2 as val2v2 5114from t2, v1 5115where t2.id2 = v1.id1v1; 5116prepare stmt1 from "update v2 set val2v2 = 'test19' where 1 = id2v2"; 5117execute stmt1; 5118deallocate prepare stmt1; 5119drop view v1,v2; 5120drop table t1,t2; 5121 5122--echo # ----------------------------------------------------------------- 5123--echo # -- End of 5.3 tests. 5124--echo # ----------------------------------------------------------------- 5125 5126--echo # 5127--echo # MDEV-3874: Server crashes in Item_field::print on a SELECT 5128--echo # from a MERGE view with materialization+semijoin, subquery, ORDER BY 5129--echo # 5130SET @save_optimizer_switch_MDEV_3874=@@optimizer_switch; 5131 5132SET optimizer_switch = 'materialization=on,semijoin=on'; 5133 5134CREATE TABLE t1 (a INT) ENGINE=MyISAM; 5135INSERT INTO t1 VALUES (1),(7); 5136 5137CREATE TABLE t2 (b INT) ENGINE=MyISAM; 5138INSERT INTO t2 VALUES (4),(6); 5139 5140CREATE TABLE t3 (c INT) ENGINE=MyISAM; 5141INSERT INTO t3 VALUES (1),(2); 5142 5143 5144CREATE ALGORITHM=MERGE VIEW v1 AS SELECT 5145( SELECT a FROM t1 WHERE ( 1, 1 ) IN ( 5146SELECT b, c FROM t2, t3 HAVING c > 2 ) ) AS field1, 5147b + c AS field2 5148FROM t2, t3 AS table1 5149GROUP BY field1, field2 ORDER BY field1; 5150 5151SELECT * FROM v1; 5152 5153drop view v1; 5154drop table t1,t2,t3; 5155SET optimizer_switch=@save_optimizer_switch_MDEV_3874; 5156 5157# 5158# MDEV-5515: sub-bug test of 3rd execution crash 5159# 5160 5161CREATE TABLE `t1` ( 5162 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 5163 `f0` int(11) unsigned NOT NULL DEFAULT '0', 5164 `f1` int(11) unsigned NOT NULL DEFAULT '0', 5165 PRIMARY KEY (`id`), 5166 UNIQUE KEY `id` (`id`) 5167); 5168 5169CREATE TABLE `t2` ( 5170 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 5171 `f02` bigint(20) unsigned NOT NULL DEFAULT '0', 5172 `f03` int(11) unsigned NOT NULL DEFAULT '0', 5173 PRIMARY KEY (`id`), 5174 UNIQUE KEY `id` (`id`) 5175); 5176 5177CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `v1` AS 5178 SELECT 5179 `t1`.`f0` AS `f0`, 5180 `t1`.`f1` AS `f1`, 5181 `t2`.`f02` AS `f02`, 5182 `t2`.`f03` AS `f03` 5183 FROM 5184 (`t1` LEFT JOIN `t2` ON((`t1`.`id` = `t2`.`f02`))); 5185 5186--delimiter | 5187CREATE FUNCTION `f1`( 5188 p0 BIGINT(20) UNSIGNED 5189 ) 5190 RETURNS bigint(20) unsigned 5191 DETERMINISTIC 5192 CONTAINS SQL 5193 SQL SECURITY DEFINER 5194 COMMENT '' 5195BEGIN 5196 5197DECLARE k0 INTEGER UNSIGNED DEFAULT 0; 5198DECLARE lResult INTEGER UNSIGNED DEFAULT 0; 5199 5200 SET k0 = 0; 5201 WHILE k0 < 1 DO 5202 SELECT COUNT(*) as `f00` INTO lResult FROM `v1` WHERE `v1`.`f0` = p0; -- BUG 5203 SET k0 = k0 + 1; 5204 END WHILE; 5205 5206 RETURN(k0); 5207END| 5208--delimiter ; 5209 5210 5211SELECT `f1`(1); 5212SELECT `f1`(1); 5213SELECT `f1`(1); 5214SELECT `f1`(1); 5215 5216DROP FUNCTION f1; 5217DROP VIEW v1; 5218DROP TABLE t1, t2; 5219 5220 5221create view v1 as select 1; 5222 5223--let $MYSQLD_DATADIR= `select @@datadir` 5224--let SEARCH_FILE= $MYSQLD_DATADIR/test/v1.frm 5225--let SEARCH_RANGE= 50000 5226--let SEARCH_PATTERN=mariadb-version 5227--source include/search_pattern_in_file.inc 5228 5229drop view v1; 5230 5231--echo # 5232--echo # MDEV-7260: Crash in get_best_combination when executing multi-table 5233--echo # UPDATE with nested views 5234--echo # 5235 5236CREATE TABLE `t1` (`id` bigint(20)); 5237 5238INSERT INTO `t1` VALUES (1),(2); 5239 5240CREATE TABLE `t2` (`id` bigint(20)); 5241 5242CREATE TABLE `t3` (`id` bigint(20), `flag` tinyint(4)); 5243 5244create view v1 as select id from t1; 5245 5246create view v2 as select t2.* from (t2 left join v1 using (id)); 5247 5248update t3 left join v2 using (id) set flag=flag+1; 5249 5250drop view v2, v1; 5251drop table t1, t2, t3; 5252 5253--echo # 5254--echo # MDEV-7207 - ALTER VIEW does not change ALGORITM 5255--echo # 5256create table t1 (a int, b int); 5257create algorithm=temptable view v2 (c) as select b+1 from t1; 5258show create view v2; 5259alter algorithm=undefined view v2 (c) as select b+1 from t1; 5260show create view v2; 5261alter algorithm=merge view v2 (c) as select b+1 from t1; 5262show create view v2; 5263drop view v2; 5264drop table t1; 5265 5266--echo # 5267--echo # MDEV-8554: Server crashes in base_list_iterator::next_fast on 1st execution of PS with a multi-table update 5268--echo # 5269CREATE TABLE t1 (a INT) ENGINE=MyISAM; 5270INSERT INTO t1 VALUES (1),(2); # Not necessary, the table can be empty 5271 5272CREATE TABLE t2 (b INT) ENGINE=MyISAM; 5273INSERT INTO t2 VALUES (3),(4); # Not necessary, the table can be empty 5274 5275CREATE TABLE t3 (c INT) ENGINE=MyISAM; 5276INSERT INTO t3 VALUES (5),(6); # Not necessary, the table can be empty 5277 5278CREATE OR REPLACE ALGORITHM=MERGE VIEW v3 AS SELECT * FROM t3; 5279 5280PREPARE stmt FROM 'UPDATE t1, t2 SET a = 1 WHERE a IN ( SELECT 0 FROM t3 )'; 5281UPDATE t1, t2 SET a = 1 WHERE a IN ( SELECT 0 FROM v3 ); 5282EXECUTE stmt; 5283 5284DROP TABLE t1, t2, t3; 5285DROP VIEW v3; 5286 5287--echo # 5288--echo # MDEV-8632: Segmentation fault on INSERT 5289--echo # 5290CREATE TABLE `t1` ( 5291 `id` int(10) unsigned NOT NULL, 5292 `r` float NOT NULL, 5293 PRIMARY KEY (`id`) 5294) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 5295create view v1 as select id, if(r=r,1,2) as d from t1; 5296create view v2 as 5297 select id, 5298 d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d as p 5299 from v1; 5300insert into t1 (id, r) 5301select id,p from 5302( 5303 select id, 5304 d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d+d as p 5305 from ( 5306 select id, if(r=r,1,2) as d 5307 from t1 5308 ) a 5309) b 5310on duplicate key update r=p; 5311insert into t1 (id, r) 5312select id,p from v2 5313on duplicate key update r=p; 5314 5315prepare stmt from "insert into t1 (id, r) select id,p from v2 on duplicate key update r=p"; 5316execute stmt; 5317execute stmt; 5318deallocate prepare stmt; 5319 5320drop view v1,v2; 5321drop table `t1`; 5322 5323# 5324# Bug#19817021 5325# 5326create table t1 (a int, b int); 5327create view v1 as select a+b from t1; 5328alter table v1 check partition p1; 5329drop view v1; 5330drop table t1; 5331 5332 5333--echo # 5334--echo # MDEV-10419: crash in mariadb 10.1.16-MariaDB-1~trusty 5335--echo # 5336CREATE TABLE t1 (c1 CHAR(13)); 5337CREATE TABLE t2 (c2 CHAR(13)); 5338 5339CREATE FUNCTION f() RETURNS INT RETURN 0; 5340CREATE OR REPLACE VIEW v1 AS select f() from t1 where c1 in (select c2 from t2); 5341DROP FUNCTION f; 5342 5343SHOW CREATE VIEW v1; 5344 5345drop view v1; 5346drop table t1,t2; 5347 5348--echo # 5349--echo # MDEV-12099: usage of mergeable view with LEFT JOIN 5350--echo # that can be converted to INNER JOIN 5351--echo # 5352 5353create table t1 (a int, b int, key(a)) engine=myisam; 5354insert into t1 values 5355 (3,20), (7,10), (2,10), (4,30), (8,70), 5356 (7,70), (9,100), (9,60), (8,80), (7,60); 5357 5358create table t2 (c int, d int, key (c)) engine=myisam; 5359insert into t2 values 5360 (50,100), (20, 200), (10,300), 5361 (150,100), (120, 200), (110,300), 5362 (250,100), (220, 200), (210,300); 5363 5364create table t3(e int, f int not null, key(e), unique (f)) engine=myisam; 5365insert into t3 values 5366 (100, 3), (300, 5), (400, 4), (300,7), 5367 (300,2), (600, 13), (800, 15), (700, 14), 5368 (600, 23), (800, 25), (700, 24); 5369 5370create view v1 as 5371 select * from t2 left join t3 on t3.e=t2.d where t3.f is not null; 5372 5373select * 5374 from t1 left join v1 on v1.c=t1.b 5375 where t1.a < 5; 5376 5377select * 5378 from t1 left join ( t2 left join t3 on t3.e=t2.d ) 5379 on t2.c=t1.b and t3.f is not null 5380 where t1.a < 5; 5381 5382explain extended 5383select * 5384 from t1 left join v1 on v1.c=t1.b 5385 where t1.a < 5; 5386 5387explain extended 5388select * 5389 from t1 left join ( t2 left join t3 on t3.e=t2.d ) 5390 on t2.c=t1.b and t3.f is not null 5391 where t1.a < 5; 5392 5393explain extended 5394select * 5395 from t1 left join v1 on v1.c=t1.b and v1.f=t1.a 5396 where t1.a < 5; 5397 5398explain extended 5399select * 5400 from t1 left join ( t2 left join t3 on t3.e=t2.d ) 5401 on t2.c=t1.b and t3.f=t1.a and t3.f is not null 5402 where t1.a < 5; 5403 5404drop view v1; 5405drop table t1,t2,t3; 5406 5407--echo # 5408--echo # MDEV-11240: Server crashes in check_view_single_update or 5409--echo # Assertion `derived->table' failed in mysql_derived_merge_for_insert 5410--echo # 5411 5412CREATE TABLE t3 (a INT); 5413CREATE ALGORITHM = MERGE VIEW v1 AS SELECT t2.a FROM t3 AS t1, t3 AS t2; 5414CREATE ALGORITHM = MERGE VIEW v2 AS SELECT * FROM v1; 5415--error ER_VIEW_NO_INSERT_FIELD_LIST 5416PREPARE stmt FROM 'REPLACE INTO v2 SELECT a FROM t3'; 5417drop view v1,v2; 5418drop table t3; 5419 5420--echo # 5421--echo # MDEV-14619: VIEW and GROUP_CONCAT 5422--echo # 5423 5424CREATE TABLE t1 (str text); 5425INSERT INTO t1 VALUES ("My"),("SQL"); 5426CREATE VIEW v1 AS SELECT GROUP_CONCAT(str SEPARATOR '\\') FROM t1; 5427SELECT * FROM v1; 5428SHOW CREATE VIEW v1; 5429drop view v1; 5430drop table t1; 5431 5432CREATE TABLE IF NOT EXISTS t0 (f0 INT); 5433CREATE TABLE IF NOT EXISTS t1 (f1 INT); 5434CREATE TABLE IF NOT EXISTS t2 (f2 INT); 5435CREATE TABLE IF NOT EXISTS t3 (f3 INT); 5436CREATE TABLE IF NOT EXISTS t4 (f4 INT); 5437CREATE TABLE IF NOT EXISTS t5 (f5 INT); 5438CREATE TABLE IF NOT EXISTS t6 (f6 INT); 5439CREATE TABLE IF NOT EXISTS t7 (f7 INT); 5440CREATE TABLE IF NOT EXISTS t8 (f8 INT); 5441CREATE TABLE IF NOT EXISTS t9 (f9 INT); 5442CREATE TABLE IF NOT EXISTS t10 (f10 INT); 5443CREATE TABLE IF NOT EXISTS t11 (f11 INT); 5444CREATE TABLE IF NOT EXISTS t12 (f12 INT); 5445CREATE TABLE IF NOT EXISTS t13 (f13 INT); 5446CREATE TABLE IF NOT EXISTS t14 (f14 INT); 5447CREATE TABLE IF NOT EXISTS t15 (f15 INT); 5448CREATE TABLE IF NOT EXISTS t16 (f16 INT); 5449CREATE TABLE IF NOT EXISTS t17 (f17 INT); 5450CREATE TABLE IF NOT EXISTS t18 (f18 INT); 5451CREATE TABLE IF NOT EXISTS t19 (f19 INT); 5452CREATE TABLE IF NOT EXISTS t20 (f20 INT); 5453CREATE TABLE IF NOT EXISTS t21 (f21 INT); 5454CREATE TABLE IF NOT EXISTS t22 (f22 INT); 5455CREATE TABLE IF NOT EXISTS t23 (f23 INT); 5456CREATE TABLE IF NOT EXISTS t24 (f24 INT); 5457CREATE TABLE IF NOT EXISTS t25 (f25 INT); 5458CREATE TABLE IF NOT EXISTS t26 (f26 INT); 5459CREATE TABLE IF NOT EXISTS t27 (f27 INT); 5460CREATE TABLE IF NOT EXISTS t28 (f28 INT); 5461CREATE TABLE IF NOT EXISTS t29 (f29 INT); 5462CREATE TABLE IF NOT EXISTS t30 (f30 INT); 5463CREATE TABLE IF NOT EXISTS t31 (f31 INT); 5464CREATE TABLE IF NOT EXISTS t32 (f32 INT); 5465CREATE TABLE IF NOT EXISTS t33 (f33 INT); 5466CREATE TABLE IF NOT EXISTS t34 (f34 INT); 5467CREATE TABLE IF NOT EXISTS t35 (f35 INT); 5468CREATE TABLE IF NOT EXISTS t36 (f36 INT); 5469CREATE TABLE IF NOT EXISTS t37 (f37 INT); 5470CREATE TABLE IF NOT EXISTS t38 (f38 INT); 5471CREATE TABLE IF NOT EXISTS t39 (f39 INT); 5472CREATE TABLE IF NOT EXISTS t40 (f40 INT); 5473CREATE TABLE IF NOT EXISTS t41 (f41 INT); 5474CREATE TABLE IF NOT EXISTS t42 (f42 INT); 5475CREATE TABLE IF NOT EXISTS t43 (f43 INT); 5476CREATE TABLE IF NOT EXISTS t44 (f44 INT); 5477CREATE TABLE IF NOT EXISTS t45 (f45 INT); 5478CREATE TABLE IF NOT EXISTS t46 (f46 INT); 5479CREATE TABLE IF NOT EXISTS t47 (f47 INT); 5480CREATE TABLE IF NOT EXISTS t48 (f48 INT); 5481CREATE TABLE IF NOT EXISTS t49 (f49 INT); 5482CREATE TABLE IF NOT EXISTS t50 (f50 INT); 5483CREATE TABLE IF NOT EXISTS t51 (f51 INT); 5484CREATE TABLE IF NOT EXISTS t52 (f52 INT); 5485CREATE TABLE IF NOT EXISTS t53 (f53 INT); 5486CREATE TABLE IF NOT EXISTS t54 (f54 INT); 5487CREATE TABLE IF NOT EXISTS t55 (f55 INT); 5488CREATE TABLE IF NOT EXISTS t56 (f56 INT); 5489CREATE TABLE IF NOT EXISTS t57 (f57 INT); 5490CREATE TABLE IF NOT EXISTS t58 (f58 INT); 5491CREATE TABLE IF NOT EXISTS t59 (f59 INT); 5492CREATE TABLE IF NOT EXISTS t60 (f60 INT); 5493CREATE OR REPLACE VIEW v60 AS SELECT * FROM t60; 5494 5495EXPLAIN 5496 SELECT t0.* 5497FROM t0 5498JOIN t1 5499 ON t1.f1 = t0.f0 5500LEFT JOIN t2 5501 ON t0.f0 = t2.f2 5502LEFT JOIN t3 5503 ON t0.f0 = t3.f3 5504LEFT JOIN t4 5505 ON t0.f0 = t4.f4 5506LEFT JOIN t5 5507 ON t4.f4 = t5.f5 5508LEFT JOIN t6 5509 ON t0.f0 = t6.f6 5510LEFT JOIN t7 5511 ON t0.f0 = t7.f7 5512LEFT JOIN t8 5513 ON t0.f0 = t8.f8 5514LEFT JOIN t9 5515 ON t0.f0 = t9.f9 5516LEFT JOIN t10 5517 ON t0.f0 = t10.f10 5518LEFT JOIN t11 5519 ON t0.f0 = t11.f11 5520LEFT JOIN t12 5521 ON t0.f0 = t12.f12 5522LEFT JOIN t13 5523 ON t0.f0 = t13.f13 5524LEFT JOIN t14 5525 ON t0.f0 = t14.f14 5526LEFT JOIN t15 5527 ON t0.f0 = t15.f15 5528LEFT JOIN t16 5529 ON t0.f0 = t16.f16 5530LEFT JOIN t17 5531 ON t0.f0 = t17.f17 5532LEFT JOIN t18 5533 ON t0.f0 = t18.f18 5534LEFT JOIN t19 5535 ON t18.f18 = t19.f19 5536LEFT JOIN t20 5537 ON t20.f20 = t19.f19 5538LEFT JOIN t21 5539 ON t20.f20 = t21.f21 5540LEFT JOIN t22 5541 ON t19.f19 = t22.f22 5542LEFT JOIN t23 5543 ON t23.f23 = t0.f0 5544LEFT JOIN t24 5545 ON t24.f24 = t23.f23 5546LEFT JOIN t25 5547 ON t0.f0 = t25.f25 5548LEFT JOIN t26 5549 ON t26.f26 = t0.f0 5550LEFT JOIN t27 5551 ON t27.f27 = t0.f0 5552LEFT JOIN t28 5553 ON t0.f0 = t28.f28 5554LEFT JOIN t29 5555 ON t0.f0 = t29.f29 5556LEFT JOIN t30 5557 ON t30.f30 = t0.f0 5558LEFT JOIN t31 5559 ON t0.f0 = t31.f31 5560LEFT JOIN t32 5561 ON t32.f32 = t31.f31 5562LEFT JOIN t33 5563 ON t33.f33 = t0.f0 5564LEFT JOIN t34 5565 ON t33.f33 = t34.f34 5566LEFT JOIN t35 5567 ON t33.f33 = t35.f35 5568LEFT JOIN t36 5569 ON t36.f36 = t0.f0 5570LEFT JOIN t37 5571 ON t32.f32 = t37.f37 5572LEFT JOIN t38 5573 ON t31.f31 = t38.f38 5574LEFT JOIN t39 5575 ON t39.f39 = t0.f0 5576LEFT JOIN t40 5577 ON t40.f40 = t39.f39 5578LEFT JOIN t41 5579 ON t41.f41 = t0.f0 5580LEFT JOIN t42 5581 ON t42.f42 = t41.f41 5582LEFT JOIN t43 5583 ON t43.f43 = t41.f41 5584LEFT JOIN t44 5585 ON t44.f44 = t0.f0 5586LEFT JOIN t45 5587 ON t45.f45 = t0.f0 5588LEFT JOIN t46 5589 ON t46.f46 = t0.f0 5590LEFT JOIN t47 5591 ON t47.f47 = t0.f0 5592LEFT JOIN t48 5593 ON t48.f48 = t0.f0 5594LEFT JOIN t49 5595 ON t0.f0 = t49.f49 5596LEFT JOIN t50 5597 ON t0.f0 = t50.f50 5598LEFT JOIN t51 5599 ON t0.f0 = t51.f51 5600LEFT JOIN t52 5601 ON t52.f52 = t0.f0 5602LEFT JOIN t53 5603 ON t53.f53 = t0.f0 5604LEFT JOIN t54 5605 ON t54.f54 = t0.f0 5606LEFT JOIN t55 5607 ON t55.f55 = t0.f0 5608LEFT JOIN t56 5609 ON t56.f56 = t0.f0 5610LEFT JOIN t57 5611 ON t57.f57 = t0.f0 5612LEFT JOIN t58 5613 ON t58.f58 = t57.f57 5614LEFT JOIN t59 5615 ON t36.f36 = t59.f59 5616LEFT JOIN v60 5617 ON t36.f36 = v60.f60 5618; 5619drop table t0, t1, t2, t3, t4, t5, t6, t7, t8, t9, 5620t10, t11, t12, t13, t14, t15, t16, t17, t18, 5621t19, t20, t21, t22, t23, t24, t25, t26, t27, 5622t28, t29, t30, t31, t32, t33, t34, t35, t36, 5623t37, t38, t39, t40, t41, t42, t43, t44, t45, 5624t46, t47, t48, t49, t50, t51, t52, t53, t54, 5625t55, t56, t57, t58, t59,t60; 5626drop view v60; 5627 5628--echo # 5629--echo # MDEV-15572: view.test, server crash with --big-tables=1 5630--echo # 5631 5632set tmp_memory_table_size=0; # force on-disk tmp table 5633CREATE TABLE t1 ( f1 int , f2 int , f3 int , f4 int); 5634CREATE TABLE t2 ( f1 int , f2 int , f3 int , f4 int); 5635 5636CREATE VIEW v1 AS 5637 SELECT t2.f1, t1.f2, t2.f3, t2.f4 FROM (t1 JOIN t2); 5638 5639--error ER_VIEW_MULTIUPDATE 5640REPLACE INTO v1 (f1, f2, f3, f4) 5641 SELECT f1, f2, f3, f4 FROM t1; 5642 5643drop view v1; 5644drop table t1, t2; 5645set tmp_memory_table_size=default; 5646 5647--echo # ----------------------------------------------------------------- 5648--echo # -- End of 5.5 tests. 5649--echo # ----------------------------------------------------------------- 5650 5651--echo # some subqueries in SELECT list test 5652create table t1 (a int, b int); 5653create table t2 (a int, b int); 5654insert into t1 values (1,2), (3,4), (3,3), (5,6), (7,8), (9,10); 5655insert into t2 values (1,2), (3,4), (3,3), (5,6), (7,8), (9,10); 5656create algorithm=merge view v1 as select t1.a as a, (select max(b) from t2 where t1.a=t2.a) as c from t1; 5657explain extended 5658select * from v1; 5659select * from v1; 5660explain extended 5661select * from t2, v1 where t2.a=v1.a; 5662select * from t2, v1 where t2.a=v1.a; 5663explain extended 5664select * from t1, v1 where t1.a=v1.a; 5665select * from t1, v1 where t1.a=v1.a; 5666explain extended 5667select * from t1, v1 where t1.b=v1.c; 5668select * from t1, v1 where t1.b=v1.c; 5669explain extended 5670select * from t2, t1, v1 where t1.a=t2.a and t1.a=v1.a; 5671select * from t2, t1, v1 where t1.a=t2.a and t1.a=v1.a; 5672 5673drop view v1; 5674drop table t1,t2; 5675 5676# 5677# MDEV-6785 Wrong result on 2nd execution of PS with aggregate function, FROM SQ or MERGE view 5678# 5679 5680create table t1 (i int not null); 5681insert into t1 values (1),(2); 5682create table t2 (j int not null); 5683insert into t2 values (11),(12); 5684create algorithm=merge view v3 as select t1.* from t2 left join t1 on (t2.j = t1.i); 5685prepare stmt from 'select count(v3.i) from t1, v3'; 5686execute stmt; 5687execute stmt; 5688drop table t1, t2; 5689drop view v3; 5690 5691--echo # 5692--echo # MDEV-8525: mariadb 10.0.20 crashing when data is read by Kodi 5693--echo # media center (http://kodi.tv). 5694--echo # 5695 5696CREATE TABLE `t1` ( 5697 `idSong` int(11) NOT NULL AUTO_INCREMENT, 5698 `idAlbum` int(11) DEFAULT NULL, 5699 `idPath` int(11) DEFAULT NULL, 5700 `strArtists` text, 5701 `strGenres` text, 5702 `strTitle` varchar(512) DEFAULT NULL, 5703 `iTrack` int(11) DEFAULT NULL, 5704 `iDuration` int(11) DEFAULT NULL, 5705 `iYear` int(11) DEFAULT NULL, 5706 `dwFileNameCRC` text, 5707 `strFileName` text, 5708 `strMusicBrainzTrackID` text, 5709 `iTimesPlayed` int(11) DEFAULT NULL, 5710 `iStartOffset` int(11) DEFAULT NULL, 5711 `iEndOffset` int(11) DEFAULT NULL, 5712 `idThumb` int(11) DEFAULT NULL, 5713 `lastplayed` varchar(20) DEFAULT NULL, 5714 `rating` char(1) DEFAULT '0', 5715 `comment` text, 5716 `mood` text, 5717 PRIMARY KEY (`idSong`), 5718 UNIQUE KEY `idxSong7` (`idAlbum`,`strMusicBrainzTrackID`(36)), 5719 KEY `idxSong` (`strTitle`(255)), 5720 KEY `idxSong1` (`iTimesPlayed`), 5721 KEY `idxSong2` (`lastplayed`), 5722 KEY `idxSong3` (`idAlbum`), 5723 KEY `idxSong6` (`idPath`,`strFileName`(255)) 5724) DEFAULT CHARSET=utf8; 5725 5726INSERT INTO `t1` VALUES (1,1,1,'strArtists1','strGenres1','strTitle1',1,100,2000,NULL,'strFileName1','strMusicBrainzTrackID1',0,0,0,NULL,NULL,'0','',''),(2,2,2,'strArtists2','strGenres2','strTitle2',2,200,2001,NULL,'strFileName2','strMusicBrainzTrackID2',0,0,0,NULL,NULL,'0','',''); 5727 5728CREATE TABLE `t2` ( 5729 `idAlbum` int(11) NOT NULL AUTO_INCREMENT, 5730 `strAlbum` varchar(256) DEFAULT NULL, 5731 `strMusicBrainzAlbumID` text, 5732 `strArtists` text, 5733 `strGenres` text, 5734 `iYear` int(11) DEFAULT NULL, 5735 `idThumb` int(11) DEFAULT NULL, 5736 `bCompilation` int(11) NOT NULL DEFAULT '0', 5737 `strMoods` text, 5738 `strStyles` text, 5739 `strThemes` text, 5740 `strReview` text, 5741 `strImage` text, 5742 `strLabel` text, 5743 `strType` text, 5744 `iRating` int(11) DEFAULT NULL, 5745 `lastScraped` varchar(20) DEFAULT NULL, 5746 `dateAdded` varchar(20) DEFAULT NULL, 5747 `strReleaseType` text, 5748 PRIMARY KEY (`idAlbum`), 5749 UNIQUE KEY `idxAlbum_2` (`strMusicBrainzAlbumID`(36)), 5750 KEY `idxAlbum` (`strAlbum`(255)), 5751 KEY `idxAlbum_1` (`bCompilation`) 5752) DEFAULT CHARSET=utf8; 5753 5754INSERT INTO `t2` VALUES (1,'strAlbum1','strMusicBrainzAlbumID1','strArtists1','strGenres1',2000,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'album'); 5755 5756CREATE TABLE `t3` ( 5757 `idArtist` int(11) DEFAULT NULL, 5758 `idAlbum` int(11) DEFAULT NULL, 5759 `strJoinPhrase` text, 5760 `boolFeatured` int(11) DEFAULT NULL, 5761 `iOrder` int(11) DEFAULT NULL, 5762 `strArtist` text, 5763 UNIQUE KEY `idxAlbumArtist_1` (`idAlbum`,`idArtist`), 5764 UNIQUE KEY `idxAlbumArtist_2` (`idArtist`,`idAlbum`), 5765 KEY `idxAlbumArtist_3` (`boolFeatured`) 5766) DEFAULT CHARSET=utf8; 5767 5768INSERT INTO `t3` VALUES (1,1,'',0,0,'strArtist1'); 5769 5770CREATE TABLE `t4` ( 5771 `idArtist` int(11) NOT NULL AUTO_INCREMENT, 5772 `strArtist` varchar(256) DEFAULT NULL, 5773 `strMusicBrainzArtistID` text, 5774 `strBorn` text, 5775 `strFormed` text, 5776 `strGenres` text, 5777 `strMoods` text, 5778 `strStyles` text, 5779 `strInstruments` text, 5780 `strBiography` text, 5781 `strDied` text, 5782 `strDisbanded` text, 5783 `strYearsActive` text, 5784 `strImage` text, 5785 `strFanart` text, 5786 `lastScraped` varchar(20) DEFAULT NULL, 5787 `dateAdded` varchar(20) DEFAULT NULL, 5788 PRIMARY KEY (`idArtist`), 5789 UNIQUE KEY `idxArtist1` (`strMusicBrainzArtistID`(36)), 5790 KEY `idxArtist` (`strArtist`(255)) 5791) DEFAULT CHARSET=utf8; 5792 5793INSERT INTO `t4` VALUES (1,'strArtist1','strMusicBrainzArtistID',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); 5794 5795CREATE VIEW `v1` AS select `t2`.`idAlbum` AS `idAlbum`,`t2`.`strAlbum` AS `strAlbum`,`t2`.`strMusicBrainzAlbumID` AS `strMusicBrainzAlbumID`,`t2`.`strArtists` AS `strArtists`,`t2`.`strGenres` AS `strGenres`,`t2`.`iYear` AS `iYear`,`t2`.`strMoods` AS `strMoods`,`t2`.`strStyles` AS `strStyles`,`t2`.`strThemes` AS `strThemes`,`t2`.`strReview` AS `strReview`,`t2`.`strLabel` AS `strLabel`,`t2`.`strType` AS `strType`,`t2`.`strImage` AS `strImage`,`t2`.`iRating` AS `iRating`,`t2`.`bCompilation` AS `bCompilation`,(select min(`t1`.`iTimesPlayed`) from `t1` where (`t1`.`idAlbum` = `t2`.`idAlbum`)) AS `iTimesPlayed`,`t2`.`strReleaseType` AS `strReleaseType` from `t2`; 5796 5797CREATE VIEW `v2` AS select `t3`.`idAlbum` AS `idAlbum`,`t3`.`idArtist` AS `idArtist`,`t4`.`strArtist` AS `strArtist`,`t4`.`strMusicBrainzArtistID` AS `strMusicBrainzArtistID`,`t3`.`boolFeatured` AS `boolFeatured`,`t3`.`strJoinPhrase` AS `strJoinPhrase`,`t3`.`iOrder` AS `iOrder` from (`t3` join `t4` on((`t3`.`idArtist` = `t4`.`idArtist`))); 5798 5799SELECT v1.*,v2.* FROM v1 LEFT JOIN v2 ON v1.idAlbum = v2.idAlbum WHERE v1.idAlbum = 1 ORDER BY v2.iOrder; 5800 5801drop view v1,v2; 5802drop table t1,t2,t3,t4; 5803 5804--echo # 5805--echo # MDEV-8913: Derived queries with same column names as final 5806--echo # projection causes issues when using Order By 5807--echo # 5808create table t1 (field int); 5809insert into t1 values (10),(5),(3),(8),(20); 5810 5811SELECT sq.f2 AS f1, sq.f1 AS f2 5812FROM ( SELECT field AS f1, 1 AS f2 FROM t1) AS sq 5813ORDER BY sq.f1; 5814 5815create view v1 as SELECT field AS f1, 1 AS f2 FROM t1; 5816 5817SELECT sq.f2 AS f1, sq.f1 AS f2 5818FROM v1 AS sq 5819ORDER BY sq.f1; 5820 5821drop view v1; 5822 5823create table t2 SELECT field AS f1, 1 AS f2 FROM t1; 5824 5825SELECT 5826 sq.f2 AS f1, 5827 sq.f1 AS f2 5828FROM t2 AS sq 5829ORDER BY sq.f1; 5830 5831drop table t1, t2; 5832 5833--error ER_BAD_FIELD_ERROR 5834SELECT 1 FROM (SELECT 1 as a) AS b HAVING (SELECT `SOME_GARBAGE`.b.a)=1; 5835 5836 5837--echo # 5838--echo # MDEV-10035: DBUG_ASSERT on CREATE VIEW v1 AS SELECT * FROM t1 5839--echo # FOR UPDATE 5840--echo # 5841 5842CREATE TABLE t1 (a INT); 5843insert into t1 values (1),(2); 5844 5845CREATE VIEW v1 AS SELECT * FROM t1 FOR UPDATE; 5846SHOW CREATE VIEW v1; 5847select * from v1; 5848DROP VIEW v1; 5849 5850CREATE VIEW v1 AS SELECT * FROM t1 LOCK IN SHARE MODE; 5851SHOW CREATE VIEW v1; 5852select * from v1; 5853DROP VIEW v1; 5854 5855DROP TABLE t1; 5856 5857--echo # 5858--echo # MDEV-8642: WHERE Clause not applied on View - Empty result set returned 5859--echo # 5860 5861CREATE TABLE `t1` ( 5862 `id` int(20) NOT NULL AUTO_INCREMENT, 5863 `use_case` int(11) DEFAULT NULL, 5864 `current_deadline` date DEFAULT NULL, 5865 `ts_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 5866 PRIMARY KEY (`id`), 5867 UNIQUE KEY `id_UNIQUE` (`id`) 5868) ENGINE=MyISAM AUTO_INCREMENT=13976 DEFAULT CHARSET=latin1; 5869INSERT INTO `t1` VALUES (1,10,'2015-12-18','2015-08-18 08:38:16'); 5870INSERT INTO `t1` VALUES (2,20,'2015-10-18','2015-08-18 08:43:30'); 5871CREATE VIEW v1 AS SELECT 5872 use_case as use_case_id, 5873 ( 5874 SELECT 5875 deadline_sub.current_deadline 5876 FROM 5877 t1 deadline_sub 5878 WHERE 5879 deadline_sub.use_case = use_case_id 5880 AND ts_create = (SELECT 5881 MIN(ts_create) 5882 FROM 5883 t1 startdate_sub 5884 WHERE 5885 startdate_sub.use_case = use_case_id 5886 ) 5887 ) AS InitialDeadline 5888FROM 5889 t1; 5890 5891SELECT * FROM v1 where use_case_id = 10; 5892 5893drop view v1; 5894drop table t1; 5895 5896--echo # 5897--echo # MDEV-12666: CURRENT_ROLE() and DATABASE() does not work in a view 5898--echo # 5899--echo # DATABASE() fails only when the initial view creation features a NULL 5900--echo # default database. 5901--echo # 5902--echo # CREATE, USE and DROP database so that we have no "default" database. 5903--echo # 5904CREATE DATABASE temporary; 5905USE temporary; 5906DROP DATABASE temporary; 5907SELECT DATABASE(); 5908 5909CREATE VIEW test.v_no_db AS SELECT DATABASE() = 'temporary_two'; 5910SHOW CREATE VIEW test.v_no_db; 5911PREPARE prepared_no_database FROM "SELECT DATABASE() = 'temporary_two'"; 5912 5913--echo # 5914--echo # All statements should return NULL 5915--echo # 5916EXECUTE prepared_no_database; 5917SELECT DATABASE() = 'temporary_two'; 5918SELECT * FROM test.v_no_db; 5919 5920CREATE DATABASE temporary_two; 5921USE temporary_two; 5922CREATE VIEW test.v_with_db AS SELECT DATABASE() = 'temporary_two'; 5923PREPARE prepared_with_database FROM "SELECT DATABASE() = 'temporary_two'"; 5924 5925--echo # 5926--echo # All statements should return 1; 5927--echo # 5928SELECT DATABASE() = 'temporary_two'; 5929SELECT * FROM test.v_no_db; 5930SELECT * FROM test.v_with_db; 5931EXECUTE prepared_with_database; 5932 5933--echo # 5934--echo # Prepared statements maintain default database to be the same 5935--echo # during on creation so this should return NULL still. 5936--echo # See MySQL bug #25843 5937--echo # 5938EXECUTE prepared_no_database; 5939 5940DROP DATABASE temporary_two; 5941DROP VIEW test.v_no_db; 5942DROP VIEW test.v_with_db; 5943USE test; 5944 5945--echo # ----------------------------------------------------------------- 5946--echo # -- End of 10.0 tests. 5947--echo # ----------------------------------------------------------------- 5948SET optimizer_switch=@save_optimizer_switch; 5949 5950--echo # 5951--echo # Start of 10.1 tests 5952--echo # 5953 5954--echo # 5955--echo # MDEV-8747 Wrong result for SELECT..WHERE derived_table_column='a' AND derived_table_column<>_latin1'A' COLLATE latin1_bin 5956--echo # 5957CREATE TABLE t1 (a varchar(10) character set cp1251 collate cp1251_ukrainian_ci, KEY (a)) ; 5958INSERT INTO t1 VALUES ('DD'), ('ZZ'), ('ZZ'), ('KK'), ('FF'), ('HH'),('MM'),('`1'); 5959CREATE VIEW v1 AS SELECT * FROM t1; 5960SELECT * FROM t1 WHERE a <> 0 AND a = ' 1'; 5961SELECT * FROM v1 WHERE a <> 0 AND a = ' 1'; 5962DROP VIEW v1; 5963DROP TABLE t1; 5964 5965CREATE TABLE t1 (a ENUM('5','6')); 5966INSERT INTO t1 VALUES ('5'),('6'); 5967CREATE VIEW v1 AS SELECT * FROM t1; 5968SELECT * FROM t1 WHERE a='5' AND a<2; 5969SELECT * FROM v1 WHERE a='5' AND a<2; 5970DROP VIEW v1; 5971DROP TABLE t1; 5972 5973--echo # 5974--echo # MDEV-8749 Wrong result for SELECT..WHERE derived_table_enum_column='number' AND derived_table_enum_column OP number2 5975--echo # 5976CREATE TABLE t1 (a varchar(10) character set cp1251 collate cp1251_ukrainian_ci, KEY (a)); 5977INSERT INTO t1 VALUES ('DD'), ('ZZ'), ('ZZ'), ('KK'), ('FF'), ('HH'),('MM'),('`1'); 5978CREATE VIEW v1 AS SELECT * FROM t1; 5979SELECT * FROM t1 WHERE a <> 0 AND a = ' 1'; 5980SELECT * FROM v1 WHERE a <> 0 AND a = ' 1'; 5981DROP VIEW v1; 5982DROP TABLE t1; 5983 5984CREATE TABLE t1 (a ENUM('5','6')); 5985INSERT INTO t1 VALUES ('5'),('6'); 5986CREATE VIEW v1 AS SELECT * FROM t1; 5987SELECT * FROM t1 WHERE a='5' AND a<2; 5988SELECT * FROM v1 WHERE a='5' AND a<2; 5989DROP VIEW v1; 5990DROP TABLE t1; 5991 5992--echo # 5993--echo # MDEV-8742 Wrong result for SELECT..WHERE view_latin1_swedish_ci_field='a' COLLATE latin1_bin 5994--echo # 5995CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1); 5996INSERT INTO t1 VALUES ('a'),('A'); 5997CREATE VIEW v1 AS SELECT * FROM t1 WHERE a='a'; 5998SELECT * FROM v1 WHERE a=_latin1'a' COLLATE latin1_bin; 5999DROP VIEW v1; 6000DROP TABLE t1; 6001 6002--echo # 6003--echo # MDEV-9701: CREATE VIEW with GROUP BY or ORDER BY and constant 6004--echo # produces invalid definition 6005--echo # 6006CREATE TABLE t1 ( i INT ); 6007INSERT INTO t1 VALUES (1),(2); 6008 6009CREATE VIEW v1 AS 6010SELECT 3 AS three, COUNT(*) FROM t1 GROUP BY three; 6011 6012show create view v1; 6013 6014SELECT * FROM v1; 6015 6016drop view v1; 6017drop table t1; 6018 6019--echo # 6020--echo # MDEV-12819: order by ordering expression changed to empty string 6021--echo # when creatin view with union 6022--echo # 6023 6024create table t1 (t1col1 int, t1col2 int,t1col3 int ); 6025create table t2 (t2col1 int, t2col2 int, t2col3 int); 6026 6027create view v1 as 6028select t1col1,t1col2,t1col3 from t1 6029union all 6030select t2col1,t2col2,t2col3 from t2 6031order by 2,3; 6032 6033show create view v1; 6034 6035select * from v1; 6036 6037drop view v1; 6038drop table t1,t2; 6039 6040--echo # 6041--echo # End of 10.1 tests 6042--echo # 6043 6044--echo # 6045--echo # Start of 10.2 tests 6046--echo # 6047 6048--echo # Checking that SHOW CREATE VIEW preserve parentheses 6049 6050CREATE TABLE t1 (a INT); 6051INSERT INTO t1 VALUES (10),(20),(30); 6052 6053CREATE VIEW v1 AS SELECT 1 AS a UNION SELECT a FROM t1; 6054SHOW CREATE VIEW v1; 6055SELECT * FROM v1; 6056DROP VIEW v1; 6057 6058CREATE VIEW v1 AS SELECT 1 AS a UNION SELECT a FROM t1 LIMIT 1; 6059SHOW CREATE VIEW v1; 6060SELECT * FROM v1; 6061DROP VIEW v1; 6062 6063CREATE VIEW v1 AS SELECT 1 AS a UNION (SELECT a FROM t1); 6064SHOW CREATE VIEW v1; 6065SELECT * FROM v1; 6066DROP VIEW v1; 6067 6068CREATE VIEW v1 AS SELECT 1 AS a UNION (SELECT a FROM t1 LIMIT 1); 6069SHOW CREATE VIEW v1; 6070SELECT * FROM v1; 6071DROP VIEW v1; 6072 6073CREATE VIEW v1 AS SELECT 1 AS a UNION (SELECT a FROM t1) LIMIT 1; 6074SHOW CREATE VIEW v1; 6075SELECT * FROM v1; 6076DROP VIEW v1; 6077 6078DROP TABLE t1; 6079 6080 6081--echo # 6082--echo # MDEV-9408 CREATE TABLE SELECT MAX(int_column) creates different columns for table vs view 6083--echo # 6084CREATE TABLE t1 ( 6085 id int(11) NOT NULL PRIMARY KEY, 6086 country varchar(32), 6087 code int(11) default NULL 6088); 6089INSERT INTO t1 VALUES (1,'ITALY',100),(2,'ITALY',200),(3,'FRANCE',100), (4,'ITALY',100); 6090CREATE VIEW v1 AS SELECT * FROM t1; 6091CREATE TABLE t2 AS 6092SELECT code, COUNT(DISTINCT country), MAX(id) FROM t1 GROUP BY code ORDER BY MAX(id); 6093SHOW CREATE TABLE t2; 6094CREATE TABLE t3 AS 6095SELECT code, COUNT(DISTINCT country), MAX(id) FROM v1 GROUP BY code ORDER BY MAX(id); 6096SHOW CREATE TABLE t3; 6097DROP VIEW v1; 6098DROP TABLE t1,t2,t3; 6099 6100--echo # 6101--echo # MDEV-3944: Allow derived tables in VIEWS 6102--echo # 6103create table t1 (s1 int); 6104insert into t1 values (1),(2),(3); 6105 6106CREATE VIEW v1 AS SELECT * FROM (SELECT s1 FROM t1 WHERE s1>1) AS x; 6107CREATE VIEW v2 AS SELECT * FROM (SELECT s1 FROM t1 WHERE s1<3) AS x; 6108 6109--sorted_result 6110select * from v1; 6111--sorted_result 6112select * from v2; 6113--sorted_result 6114select * from v1 natural join v2; 6115--sorted_result 6116select * from v1 natural join t1; 6117--sorted_result 6118select * from v1 natural join (SELECT s1 FROM t1 WHERE s1<3) as x; 6119--sorted_result 6120select * from v1 left join v2 on (v1.s1=v2.s1); 6121--sorted_result 6122select * from v1 left join t1 on (v1.s1=t1.s1); 6123--sorted_result 6124select * from t1 left join v2 on (t1.s1=v2.s1); 6125--sorted_result 6126select * from v1 left join (SELECT s1 FROM t1 WHERE s1<3) as x on (v1.s1=x.s1); 6127--sorted_result 6128select * from (SELECT s1 FROM t1 WHERE s1>1) AS x left join v2 on (x.s1=v2.s1); 6129 6130drop view v1,v2; 6131 6132CREATE VIEW v1 AS SELECT * FROM (SELECT s1 FROM (SELECT s1 FROM t1 WHERE s1 6133< 100) as xx WHERE s1>1) AS x; 6134CREATE VIEW v2 AS SELECT * FROM (SELECT s1 FROM (SELECT s1 FROM t1 WHERE s1 6135> -100) as xx WHERE s1<3) AS x; 6136insert into t1 values (200),(-200); 6137--sorted_result 6138select * from t1; 6139--sorted_result 6140select * from v1; 6141--sorted_result 6142select * from v2; 6143--sorted_result 6144select * from v1 natural join v2; 6145--sorted_result 6146select * from v1 natural join t1; 6147--sorted_result 6148select * from v1 natural join (SELECT s1 FROM t1 WHERE s1<3) as x; 6149--sorted_result 6150select * from v1 left join v2 on (v1.s1=v2.s1); 6151--sorted_result 6152select * from v1 left join t1 on (v1.s1=t1.s1); 6153--sorted_result 6154select * from t1 left join v2 on (t1.s1=v2.s1); 6155--sorted_result 6156select * from v1 left join (SELECT s1 FROM t1 WHERE s1<3) as x on (v1.s1=x.s1); 6157--sorted_result 6158select * from (SELECT s1 FROM t1 WHERE s1>1) AS x left join v2 on (x.s1=v2.s1); 6159 6160drop view v1,v2; 6161 6162CREATE algorithm=temptable VIEW v1 AS SELECT * FROM (SELECT s1 FROM (SELECT s1 FROM t1 WHERE s1 6163< 100) as xx WHERE s1>1) AS x; 6164CREATE algorithm=temptable VIEW v2 AS SELECT * FROM (SELECT s1 FROM (SELECT s1 FROM t1 WHERE s1 6165> -100) as xx WHERE s1<3) AS x; 6166--sorted_result 6167select * from t1; 6168--sorted_result 6169select * from v1; 6170--sorted_result 6171select * from v2; 6172--sorted_result 6173select * from v1 natural join v2; 6174--sorted_result 6175select * from v1 natural join t1; 6176--sorted_result 6177select * from v1 natural join (SELECT s1 FROM t1 WHERE s1<3) as x; 6178--sorted_result 6179select * from v1 left join v2 on (v1.s1=v2.s1); 6180--sorted_result 6181select * from v1 left join t1 on (v1.s1=t1.s1); 6182--sorted_result 6183select * from t1 left join v2 on (t1.s1=v2.s1); 6184--sorted_result 6185select * from v1 left join (SELECT s1 FROM t1 WHERE s1<3) as x on (v1.s1=x.s1); 6186--sorted_result 6187select * from (SELECT s1 FROM t1 WHERE s1>1) AS x left join v2 on (x.s1=v2.s1); 6188 6189drop view v1,v2; 6190 6191CREATE VIEW v1 AS SELECT * FROM (SELECT s1 FROM (SELECT s1 FROM t1 WHERE s1 6192< 100) as xx WHERE s1>1) AS x; 6193 6194--error ER_NON_INSERTABLE_TABLE 6195insert into v1 values (-300); 6196--error ER_NON_UPDATABLE_TABLE 6197update v1 set s1=s1+1; 6198 6199drop view v1; 6200 6201CREATE VIEW v1 AS SELECT s1,s2 FROM (SELECT s1 as s2 FROM t1 WHERE s1 < 6202100) x, t1 WHERE t1.s1=x.s2; 6203select * from v1; 6204 6205insert into v1 (s1) values (-300); 6206update v1 set s1=s1+1; 6207select * from v1; 6208select * from t1; 6209--error ER_NON_INSERTABLE_TABLE 6210insert into v1(s2) values (-300); 6211--error ER_NON_UPDATABLE_TABLE 6212update v1 set s2=s2+1; 6213 6214drop view v1; 6215 6216CREATE VIEW v1 AS SELECT * FROM (SELECT s1 FROM t1 WHERE s1 6217< 100) AS x; 6218 6219--error ER_NON_INSERTABLE_TABLE 6220insert into v1 values (-300); 6221--error ER_NON_UPDATABLE_TABLE 6222update v1 set s1=s1+1; 6223 6224drop view v1; 6225 6226CREATE VIEW v1 AS SELECT * FROM (SELECT s1 FROM (SELECT s1 FROM t1 WHERE s1 6227< 100) as xx WHERE s1>1) AS x; 6228 6229--error ER_NON_INSERTABLE_TABLE 6230insert into v1 values (-300); 6231--error ER_NON_UPDATABLE_TABLE 6232update v1 set s1=s1+1; 6233 6234create view v2 as select * from v1; 6235 6236--error ER_NON_INSERTABLE_TABLE 6237insert into v2 values (-300); 6238--error ER_NON_UPDATABLE_TABLE 6239update v2 set s1=s1+1; 6240 6241drop view v1, v2; 6242drop table t1; 6243 6244--echo # 6245--echo # MDEV-9671:Wrong result upon select from a view with a FROM subquery 6246--echo # 6247CREATE TABLE t1 (i INT); 6248INSERT INTO t1 VALUES (3),(2); 6249 6250CREATE TABLE t2 (j INT); 6251INSERT INTO t2 VALUES (8),(3),(3); 6252 6253CREATE TABLE t3 (k INT); 6254INSERT INTO t3 VALUES (1),(8); 6255 6256CREATE VIEW v1 AS SELECT * FROM t1 LEFT JOIN ( SELECT t2.* FROM t2 INNER JOIN t3 ON ( k = j ) ) AS alias1 ON ( i = j ); 6257 6258show create view v1; 6259 6260SELECT * FROM t1 LEFT JOIN ( SELECT t2.* FROM t2 INNER JOIN t3 ON ( k = j ) ) AS alias1 ON ( i = j ); 6261 6262SELECT * FROM v1; 6263 6264DROP VIEW v1; 6265DROP TABLE t1, t2, t3; 6266 6267--echo # 6268--echo # MDEV-10035: DBUG_ASSERT on CREATE VIEW v1 AS SELECT * FROM t1 6269--echo # FOR UPDATE 6270--echo # 6271 6272CREATE TABLE t1 (a INT); 6273insert into t1 values (1),(2); 6274 6275CREATE VIEW v1 AS SELECT * FROM t1 FOR UPDATE; 6276SHOW CREATE VIEW v1; 6277select * from v1; 6278DROP VIEW v1; 6279 6280CREATE VIEW v1 AS SELECT * FROM t1 LOCK IN SHARE MODE; 6281SHOW CREATE VIEW v1; 6282select * from v1; 6283DROP VIEW v1; 6284 6285DROP TABLE t1; 6286 6287--echo # 6288--echo # MDEV-10724:Assertion `vcol_table == 0 || vcol_table == table' 6289--echo # failed in fill_record(THD*, TABLE*, List<Item>&, List<Item>&, 6290--echo # bool, bool) 6291--echo # 6292 6293CREATE TABLE t1 (f1 INT); 6294CREATE TABLE t2 (f2 INT); 6295CREATE TABLE t3 (f3 INT); 6296 6297CREATE ALGORITHM = MERGE VIEW v AS SELECT f1, f3 FROM t1, 6298( SELECT f3 FROM t2, t3 ) AS sq; 6299 6300--error ER_VIEW_MULTIUPDATE 6301INSERT INTO v (f1, f3) VALUES (1,1), (2,2); 6302 6303drop view v; 6304drop tables t1,t2,t3; 6305 6306--echo # 6307--echo # MDEV-10704: Assertion `field->field->table == table_arg' 6308--echo # failed in fill_record(THD*, TABLE*, List<Item>&, List<Item>&, 6309--echo # bool, bool) 6310--echo # 6311 6312CREATE TABLE t1 (i INT); 6313CREATE TABLE t2 (j INT); 6314CREATE TABLE t3 (k INT); 6315 6316CREATE ALGORITHM = MERGE VIEW v AS SELECT j AS f1, k AS f2 FROM ( SELECT j FROM t1, t2 ) sq, t3; 6317--error ER_VIEW_MULTIUPDATE 6318REPLACE INTO v (f1,f2) VALUES (1,1); 6319 6320drop view v; 6321drop table t1,t2,t3; 6322 6323 6324--echo # 6325--echo # MDEV-12379: Server crashes in TABLE_LIST::is_with_table on 6326--echo # SHOW CREATE VIEW 6327--echo # 6328 6329CREATE TABLE t (i INT); 6330CREATE VIEW v AS SELECT * FROM ( SELECT * FROM t ) sq; 6331DROP TABLE IF EXISTS t; 6332SHOW CREATE VIEW v; 6333DROP VIEW v; 6334 6335--echo # 6336--echo # MDEV-13439: Database permissions are not enough to run a subquery 6337--echo # with GROUP BY within a view 6338--echo # 6339 6340create database test_db; 6341use test_db; 6342create table t (i int); 6343 6344create user foo@localhost; 6345grant all on test_db.* to foo@localhost; 6346 6347--connect (con1,localhost,foo,,) 6348 6349use test_db; 6350create view v as select * from (select i from t group by i) sq; 6351select * from v; 6352 6353# Cleanup 6354--disconnect con1 6355--connection default 6356use test; 6357drop database test_db; 6358drop user foo@localhost; 6359 6360--echo # 6361--echo # MDEV-13523: Group By in a View, called within a Stored Routine 6362--echo # causes Error Code 1356 when a non-root user runs the routine for 6363--echo # a second time 6364--echo # 6365 6366CREATE DATABASE bugTest; 6367USE bugTest; 6368 6369CREATE TABLE `procViewTable` (`id` int(10), `someText` varchar(50) NOT NULL); 6370insert into `procViewTable` values (1,'Test'), (2,'Test 2'); 6371 6372CREATE USER 'procView'@'%'; 6373GRANT ALL PRIVILEGES ON `bugTest`.* TO 'procView'@'%'; 6374 6375CREATE DEFINER=`procView`@`%` VIEW `procViewSimple` AS ( 6376 select * from ( 6377 select `id` from `bugTest`.`procViewTable` 6378 ) `innerQuery` 6379 group by `innerQuery`.`id` 6380); 6381 6382--connect (con1,localhost,procView,,) 6383use bugTest; 6384 6385prepare stmt from "SELECT * FROM procViewSimple"; 6386execute stmt; 6387execute stmt; 6388 6389# Cleanup 6390--disconnect con1 6391--connection default 6392drop user procView; 6393drop view procViewSimple; 6394drop table procViewTable; 6395use test; 6396drop database bugTest; 6397 6398--echo # 6399--echo # MDEV-13436: PREPARE doesn't work as expected & throws errors but 6400--echo # MySQL is working fine 6401--echo # 6402 6403create table t1 (a int); 6404insert into t1 values (1),(2); 6405SET @sql_query = " 6406 CREATE VIEW v1 AS 6407 SELECT * FROM ( 6408 SELECT CASE WHEN 1 IN (SELECT a from t1 where a < 2) THEN TRUE END AS testcase 6409 ) testalias 6410"; 6411PREPARE stmt FROM @sql_query; 6412EXECUTE stmt; 6413DEALLOCATE PREPARE stmt; 6414show create view v1; 6415SELECT * FROM v1; 6416drop view v1; 6417drop table t1; 6418 6419--echo # 6420--echo # MDEV-18502: Server crash in find_field_in_tables upon 2nd execution of SP which causes ER_WRONG_GROUP_FIELD 6421--echo # 6422 6423CREATE TABLE t1 (id INT, f VARCHAR(1)); 6424CREATE VIEW v1 AS SELECT * FROM t1; 6425INSERT INTO t1 VALUES (1,'a'),(2,'b'); 6426CREATE PROCEDURE sp() SELECT f AS f1, MAX(id) AS f2 FROM v1 GROUP BY f1, f2 ORDER BY f1; 6427--error ER_WRONG_GROUP_FIELD 6428CALL sp; 6429--error ER_WRONG_GROUP_FIELD 6430CALL sp; 6431DROP PROCEDURE sp; 6432DROP VIEW v1; 6433DROP TABLE t1; 6434 6435--echo # 6436--echo # MDEV-24314: create view with derived table without default database 6437--echo # 6438 6439drop database test; 6440 6441create database db1; 6442create table db1.t1 (a int); 6443insert into db1.t1 values (3),(7),(1); 6444 6445create view db1.v1 as select * from (select * from db1.t1) t; 6446show create view db1.v1; 6447select * from db1.v1; 6448drop view db1.v1; 6449 6450prepare stmt from " 6451create view db1.v1 as select * from (select * from db1.t1) t; 6452"; 6453execute stmt; 6454deallocate prepare stmt; 6455show create view db1.v1; 6456select * from db1.v1; 6457drop view db1.v1; 6458 6459drop table db1.t1; 6460drop database db1; 6461 6462create database test; 6463use test; 6464 6465--echo # 6466--echo # MDEV-16940: update of multi-table view returning error used in SP 6467--echo # 6468 6469CREATE TABLE t1 (a INT) ENGINE=MyISAM; 6470INSERT INTO t1 VALUES (1), (2); 6471CREATE TABLE t2 (b INT) ENGINE=MyISAM; 6472INSERT INTO t2 VALUES (2), (3); 6473 6474CREATE VIEW v1 AS SELECT a, b FROM t1,t2; 6475 6476CREATE PROCEDURE sp1() UPDATE v1 SET a = 8, b = 9; 6477 6478--error ER_VIEW_MULTIUPDATE 6479CALL sp1; 6480--error ER_VIEW_MULTIUPDATE 6481CALL sp1; 6482 6483DROP PROCEDURE sp1; 6484DROP VIEW v1; 6485DROP TABLE t1, t2; 6486 6487--echo # 6488--echo # MDEV-23291: SUM column from a derived table returns invalid values 6489--echo # 6490 6491CREATE TABLE t1(a INT, b INT); 6492INSERT INTO t1 VALUES (1,1), (2,2); 6493 6494CREATE view v1 AS 6495SELECT a as x, (select x) as y, (select y) as z FROM t1; 6496 6497SELECT sum(z) FROM (SELECT a as x, (select x) as y, (select y) as z FROM t1) q; 6498SELECT sum(z) FROM v1; 6499 6500DROP TABLE t1; 6501DROP VIEW v1; 6502 6503--echo # 6504--echo # MDEV-26299: Some views force server (and mysqldump) to generate 6505--echo # invalid SQL for their definitions 6506--echo # 6507 6508create view v1 as 6509 select * from 6510 (select 6511 "12345678901234567890123456789012345678901234567890123456789012345") as t1; 6512 6513let $definition=`select VIEW_DEFINITION from information_schema.views where TABLE_NAME="v1"`; 6514 6515drop view v1; 6516 6517eval CREATE VIEW v1 AS $definition; 6518 6519 6520drop view v1; 6521 6522--echo # 6523--echo # MDEV-25631: view with outer reference in select used 6524--echo # as argument of set function 6525--echo # 6526 6527create table t1 (c int); 6528insert into t1 values (1); 6529create view v1 as select c from t1 where (select t1.c from t1 t) = 1; 6530 6531select * from (select sum((select * from v1)) as r) dt; 6532 6533with cte as (select c from t1 where (select t1.c from t1 t) = 1) 6534select * from (select sum((select * from cte)) as r) dt1 6535union 6536select * from (select sum((select * from cte)) as r) dt2; 6537 6538drop view v1; 6539drop table t1; 6540 6541--echo # 6542--echo # End of 10.2 tests 6543--echo # 6544 6545 6546--echo # 6547--echo # Start of 10.3 tests 6548--echo # 6549 6550--echo # 6551--echo # MDEV-13197 Parser refactoring for CREATE VIEW,TRIGGER,SP,UDF,EVENT 6552--echo # 6553 6554--error ER_PARSE_ERROR 6555ALTER VIEW IF NOT EXISTS v1 AS SELECT 1; 6556 6557--echo # 6558--echo # MDEV-18605: Loss of column aliases by using view and group 6559--echo # 6560 6561CREATE TABLE t1 (id int, foo int); 6562CREATE VIEW v1 AS SELECT id, IFNULL(foo,'') AS foo FROM t1; 6563 6564INSERT INTO t1 (id, foo) VALUES (1,1),(2,2); 6565 6566SELECT v.id, v.foo AS bar FROM v1 v 6567 WHERE id = 2; 6568 6569SELECT v.id, v.foo AS bar FROM v1 v 6570 GROUP BY v.id; 6571 6572SELECT v.id, v.foo AS bar FROM v1 v 6573 WHERE id = 2 6574 GROUP BY v.id; 6575 6576#Cleanup 6577Drop View v1; 6578Drop table t1; 6579 6580--echo # 6581--echo # End of 10.3 tests 6582--echo # 6583 6584--echo # 6585--echo # MDEV-25206: view specification contains unknown column reference 6586--echo # 6587 6588CREATE TABLE t1 (a int); 6589INSERT INTO t1 VALUES (1),(2); 6590CREATE TABLE t2 (b int); 6591INSERT INTO t2 VALUES (2),(3); 6592CREATE TABLE t3 (c int); 6593 6594--error ER_BAD_FIELD_ERROR 6595CREATE VIEW v1 AS SELECT * FROM t1 JOIN t2 ON t1.x > t2.b; 6596--error ER_BAD_FIELD_ERROR 6597INSERT INTO t3 SELECT * FROM t1 JOIN t2 ON t1.x > t2.b; 6598--error ER_BAD_FIELD_ERROR 6599CREATE TABLE t4 AS SELECT * FROM t1 JOIN t2 ON t1.x > t2.b; 6600 6601DROP TABLE t1,t2,t3; 6602 6603--echo # End of 10.4 tests 6604