2 drop table if exists t1,t2,t3,t4,t9,`t1a``b`,v1,v2,v3,v4,v5,v6;
3 drop view if exists t1,t2,`t1a``b`,v1,v2,v3,v4,v5,v6;
4 drop database if exists mysqltest;
9 # some basic test of views and its functionality
12 # create view on nonexistent table
13 -- error ER_NO_SUCH_TABLE
14 create view v1 (c,d) as select a,b from t1;
16 create temporary table t1 (a int, b int);
17 # view on temporary table
18 -- error ER_VIEW_SELECT_TMPTABLE
19 create view v1 (c) as select b+1 from t1;
22 create table t1 (a int, b int);
23 insert into t1 values (1,2), (1,3), (2,4), (2,5), (3,10);
26 -- error ER_VIEW_SELECT_VARIABLE
27 create view v1 (c,d) as select a,b+@@global.max_user_connections from t1;
28 -- error ER_VIEW_SELECT_VARIABLE
29 create view v1 (c,d) as select a,b from t1
30 where a = @@global.max_user_connections;
33 create view v1 (c) as select b+1 from t1;
35 select is_updatable from information_schema.views where table_name='v1';
37 # temporary table should not hide table of view
38 create temporary table t1 (a int, b int);
41 # but this based on normal t1
45 -- error ER_WRONG_OBJECT
49 # try to use fields from underlying table
50 -- error ER_BAD_FIELD_ERROR
52 -- error ER_BAD_FIELD_ERROR
54 -- error ER_BAD_FIELD_ERROR
56 -- error ER_BAD_FIELD_ERROR
59 # view with different algorithms (explain output differs)
60 explain extended select c from v1;
61 create algorithm=temptable view v2 (c) as select b+1 from t1;
64 explain extended select c from v2;
66 # try to use underlying table fields in VIEW creation process
67 -- error ER_BAD_FIELD_ERROR
68 create view v3 (c) as select a+1 from v1;
69 -- error ER_BAD_FIELD_ERROR
70 create view v3 (c) as select b+1 from v1;
73 # VIEW on VIEW test with mixing different algorithms on different order
74 create view v3 (c) as select c+1 from v1;
76 explain extended select c from v3;
77 create algorithm=temptable view v4 (c) as select c+1 from v2;
79 explain extended select c from v4;
80 create view v5 (c) as select c+1 from v2;
82 explain extended select c from v5;
83 create algorithm=temptable view v6 (c) as select c+1 from v1;
85 explain extended select c from v6;
87 # show table/table status test
90 --replace_column 8 # 12 # 13 #
93 drop view v1,v2,v3,v4,v5,v6;
96 # alter/create view test
99 # view with subqueries of different types
100 create view v1 (c,d,e,f) as select a,b,
101 a in (select a+2 from t1), a = all (select a from t1) from t1;
102 create view v2 as select c, d from v1;
106 # try to create VIEW with name of existing VIEW
107 -- error ER_TABLE_EXISTS_ERROR
108 create view v1 (c,d,e,f) as select a,b, a in (select a+2 from t1), a = all (select a from t1) from t1;
110 # 'or replace' should work in this case
111 create or replace view v1 (c,d,e,f) as select a,b, a in (select a+2 from t1), a = all (select a from t1) from t1;
113 # try to ALTER unexisting VIEW
115 -- error ER_NO_SUCH_TABLE
116 alter view v2 as select c, d from v1;
118 # 'or replace' on unexisting view
119 create or replace view v2 as select c, d from v1;
121 # alter view on existing view
122 alter view v1 (c,d) as select a,max(b) from t1 group by a;
124 # check that created view works
128 # try to drop nonexistent VIEW
129 -- error ER_BAD_TABLE_ERROR
132 # try to drop table with DROP VIEW
133 -- error ER_WRONG_OBJECT
136 # try to drop VIEW with DROP TABLE
137 -- error ER_BAD_TABLE_ERROR
140 # try to drop table with DROP VIEW
146 # outer left join with merged views
148 create table t1 (a int);
149 insert into t1 values (1), (2), (3);
151 create view v1 (a) as select a+1 from t1;
152 create view v2 (a) as select a-1 from t1;
154 select * from t1 natural left join v1;
155 select * from v2 natural left join t1;
156 select * from v2 natural left join v1;
163 # DISTINCT option for VIEW
165 create table t1 (a int);
166 insert into t1 values (1), (2), (3), (1), (2), (3);
167 create view v1 as select distinct a from t1;
169 explain select * from v1;
175 # syntax compatibility
177 create table t1 (a int);
178 -- error ER_VIEW_NONUPD_CHECK
179 create view v1 as select distinct a from t1 WITH CHECK OPTION;
180 create view v1 as select a from t1 WITH CHECK OPTION;
181 create view v2 as select a from t1 WITH CASCADED CHECK OPTION;
182 create view v3 as select a from t1 WITH LOCAL CHECK OPTION;
183 drop view v3 RESTRICT;
184 drop view v2 CASCADE;
191 create table t1 (a int, b int);
192 insert into t1 values (1,2), (1,3), (2,4), (2,5), (3,10);
193 create view v1 (c) as select b+1 from t1;
194 select test.c from v1 test;
195 create algorithm=temptable view v2 (c) as select b+1 from t1;
196 select test.c from v2 test;
197 select test1.* from v1 test1, v2 test2 where test1.c=test2.c;
198 select test2.* from v1 test1, v2 test2 where test1.c=test2.c;
205 create table t1 (a int);
206 insert into t1 values (1), (2), (3), (4);
207 create view v1 as select a+1 from t1 order by 1 desc limit 2;
209 explain select * from v1;
214 # CREATE ... SELECT view test
216 create table t1 (a int);
217 insert into t1 values (1), (2), (3), (4);
218 create view v1 as select a+1 from t1;
219 create table t2 select * from v1;
220 show columns from t2;
226 # simple view + simple update
228 create table t1 (a int, b int, primary key(a));
229 insert into t1 values (10,2), (20,3), (30,4), (40,5), (50,10);
230 create view v1 (a,c) as select a, b+1 from t1;
231 create algorithm=temptable view v2 (a,c) as select a, b+1 from t1;
232 select is_updatable from information_schema.views where table_name='v2';
233 select is_updatable from information_schema.views where table_name='v1';
234 # try to update expression
235 -- error ER_NONUPDATEABLE_COLUMN
237 # try to update VIEW with forced TEMPORARY TABLE algorithm
238 -- error ER_NON_UPDATABLE_TABLE
240 # updatable field of updateable view
248 # simple view + simple multi-update
250 create table t1 (a int, b int, primary key(a));
251 insert into t1 values (10,2), (20,3), (30,4), (40,5), (50,10);
252 create table t2 (x int);
253 insert into t2 values (10), (20);
254 create view v1 (a,c) as select a, b+1 from t1;
255 create algorithm=temptable view v2 (a,c) as select a, b+1 from t1;
256 # try to update expression
257 -- error ER_NONUPDATEABLE_COLUMN
258 update 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
261 update t2,v2 set v2.a=v2.v2.a+c where t2.x=v2.a;
262 # updatable field of updateable view
263 update t2,v1 set v1.a=v1.a+v1.c where t2.x=v1.a;
270 # MERGE VIEW with WHERE clause
272 create table t1 (a int, b int, primary key(b));
273 insert into t1 values (1,20), (2,30), (3,40), (4,50), (5,100);
274 create view v1 (c) as select b from t1 where a<3;
275 # simple select and explaint to be sure that it is MERGE
277 explain extended select * from v1;
281 # join of such VIEWs test
282 create view v2 (c) as select b from t1 where a>=3;
283 select * from v1, v2;
288 # simple view + simple delete
290 create table t1 (a int, b int, primary key(a));
291 insert into t1 values (1,2), (2,3), (3,4), (4,5), (5,10);
292 create view v1 (a,c) as select a, b+1 from t1;
293 create 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
296 delete from v2 where c < 4;
297 # updatable field of updateable view
298 delete from v1 where c < 4;
305 # simple view + simple multi-delete
307 create table t1 (a int, b int, primary key(a));
308 insert into t1 values (1,2), (2,3), (3,4), (4,5), (5,10);
309 create table t2 (x int);
310 insert into t2 values (1), (2), (3), (4);
311 create view v1 (a,c) as select a, b+1 from t1;
312 create 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
315 delete v2 from t2,v2 where t2.x=v2.a;
316 # updatable field of updateable view
317 delete v1 from t2,v1 where t2.x=v1.a;
326 create table t1 (a int, b int, c int, primary key(a,b));
327 insert into t1 values (10,2,-1), (20,3,-2), (30,4,-3), (40,5,-4), (50,10,-5);
328 create view v1 (x,y) as select a, b from t1;
329 create view v2 (x,y) as select a, c from t1;
330 set updatable_views_with_limit=NO;
333 update v1 set x=x+1 limit 1;
334 -- error ER_NON_UPDATABLE_TABLE
335 update v2 set x=x+1 limit 1;
336 set updatable_views_with_limit=YES;
337 update v1 set x=x+1 limit 1;
338 update v2 set x=x+1 limit 1;
339 set updatable_views_with_limit=DEFAULT;
340 show variables like "updatable_views_with_limit";
348 create table t1 (a int, b int, c int, primary key(a,b));
349 insert into t1 values (10,2,-1), (20,3,-2);
350 create view v1 (x,y,z) as select c, b, a from t1;
351 create view v2 (x,y) as select b, a from t1;
352 create view v3 (x,y,z) as select b, a, b from t1;
353 create view v4 (x,y,z) as select c+1, b, a from t1;
354 create 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
357 insert into v3 values (-60,4,30);
358 # try insert to VIEW with expression in SELECT list
359 -- error ER_NON_INSERTABLE_TABLE
360 insert into v4 values (-60,4,30);
361 # try insert to VIEW using temporary table algorithm
362 -- error ER_NON_INSERTABLE_TABLE
363 insert into v5 values (-60,4,30);
364 insert into v1 values (-60,4,30);
365 insert into v1 (z,y,x) values (50,6,-100);
366 insert into v2 values (5,40);
369 drop view v1,v2,v3,v4,v5;
374 create table t1 (a int, b int, c int, primary key(a,b));
375 insert into t1 values (10,2,-1), (20,3,-2);
376 create table t2 (a int, b int, c int, primary key(a,b));
377 insert into t2 values (30,4,-60);
378 create view v1 (x,y,z) as select c, b, a from t1;
379 create view v2 (x,y) as select b, a from t1;
380 create view v3 (x,y,z) as select b, a, b from t1;
381 create view v4 (x,y,z) as select c+1, b, a from t1;
382 create 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
385 insert into v3 select c, b, a from t2;
386 # try insert to VIEW with expression in SELECT list
387 -- error ER_NON_INSERTABLE_TABLE
388 insert into v4 select c, b, a from t2;
389 # try insert to VIEW using temporary table algorithm
390 -- error ER_NON_INSERTABLE_TABLE
391 insert into v5 select c, b, a from t2;
392 insert into v1 select c, b, a from t2;
393 insert into v1 (z,y,x) select a+20,b+2,-100 from t2;
394 insert into v2 select b+1, a+10 from t2;
397 drop view v1,v2,v3,v4,v5;
400 # outer join based on VIEW with WHERE clause
402 create table t1 (a int, primary key(a));
403 insert into t1 values (1), (2), (3);
404 create view v1 (x) as select a from t1 where a > 1;
405 select t1.a, v1.x from t1 left join v1 on (t1.a= v1.x);
410 # merging WHERE condition on VIEW on VIEW
412 create table t1 (a int, primary key(a));
413 insert into t1 values (1), (2), (3), (200);
414 create view v1 (x) as select a from t1 where a > 1;
415 create view v2 (y) as select x from v1 where x < 100;
421 # VIEW on non-updatable view
423 create table t1 (a int, primary key(a));
424 insert into t1 values (1), (2), (3), (200);
425 create ALGORITHM=TEMPTABLE view v1 (x) as select a from t1;
426 create view v2 (y) as select x from v1;
427 -- error ER_NON_UPDATABLE_TABLE
428 update v2 set y=10 where y=2;
433 # auto_increment field out of VIEW
435 create table t1 (a int not null auto_increment, b int not null, primary key(a), unique(b));
436 create view v1 (x) as select b from t1;
437 insert into v1 values (1);
438 select last_insert_id();
439 insert into t1 (b) values (2);
440 select last_insert_id();
446 # VIEW fields quoting
449 create table t1 ("a*b" int);
450 create view v1 as select "a*b" from t1;
454 set sql_mode=default;
457 # VIEW without tables
459 create table t1 (t_column int);
460 create view v1 as select 'a';
461 select * from v1, t1;
466 # quote mark inside table name
468 create table `t1a``b` (col1 char(2));
469 create view v1 as select * from `t1a``b`;
476 # Changing of underlying table
478 create table t1 (col1 char(5),col2 char(5));
479 create view v1 as select * from t1;
481 create table t1 (col1 char(5),newcol2 char(5));
482 -- error ER_VIEW_INVALID
483 insert into v1 values('a','aa');
485 -- error ER_VIEW_INVALID
490 # check of duplication of column names
492 -- error ER_DUP_FIELDNAME
493 create view v1 (a,a) as select 'a','a';
496 # updatablity should be transitive
498 create table t1 (col1 int,col2 char(22));
499 insert into t1 values(5,'Hello, world of views');
500 create view v1 as select * from t1;
501 create view v2 as select * from v1;
502 update v2 set col2='Hello, view world';
503 select is_updatable from information_schema.views;
509 # check 'use index' on view with temporary table
511 create table t1 (a int, b int);
512 create view v1 as select a, sum(b) from t1 group by a;
513 --error ER_KEY_DOES_NOT_EXITS
514 select b from v1 use index (some_index) where b=1;
519 # using VIEW fields several times in query resolved via temporary tables
521 create table t1 (col1 char(5),col2 char(5));
522 create view v1 (col1,col2) as select col1,col2 from t1;
523 insert into v1 values('s1','p1'),('s1','p2'),('s1','p3'),('s1','p4'),('s2','p1'),('s3','p2'),('s4','p4');
524 select distinct first.col2 from t1 first where first.col2 in (select second.col2 from t1 second where second.col1<>first.col1);
525 select distinct first.col2 from v1 first where first.col2 in (select second.col2 from t1 second where second.col1<>first.col1);
530 # Test of view updatability in prepared statement
532 create table t1 (a int);
533 create view v1 as select a from t1;
534 insert into t1 values (1);
538 PREPARE stmt FROM 'UPDATE v1 SET a = ?';
539 EXECUTE stmt USING @v0;
540 DEALLOCATE PREPARE stmt;
542 #insert without field list
544 PREPARE stmt FROM 'insert into v1 values (?)';
545 EXECUTE stmt USING @v0;
546 DEALLOCATE PREPARE stmt;
548 #insert with field list
550 PREPARE stmt FROM 'insert into v1 (a) values (?)';
551 EXECUTE stmt USING @v0;
552 DEALLOCATE PREPARE stmt;
560 # error on preparation
562 -- error ER_NO_TABLES_USED
563 CREATE VIEW v02 AS SELECT * FROM DUAL;
567 # EXISTS with UNION VIEW
569 CREATE VIEW v1 AS SELECT EXISTS (SELECT 1 UNION SELECT 2);
574 # using VIEW where table is required
576 create table t1 (col1 int,col2 char(22));
577 create view v1 as select * from t1;
578 -- error ER_WRONG_OBJECT
579 create index i1 on v1 (col1);
584 # connection_id(), pi(), current_user(), version() representation test
586 CREATE VIEW v1 (f1,f2,f3,f4) AS SELECT connection_id(), pi(), current_user(), version();
591 # VIEW built over UNION
593 create table t1 (s1 int);
594 create table t2 (s2 int);
595 insert into t1 values (1), (2);
596 insert into t2 values (2), (3);
597 create view v1 as select * from t1,t2 union all select * from t1,t2;
603 # Aggregate functions in view list
605 create table t1 (col1 int);
606 insert into t1 values (1);
607 create view v1 as select count(*) from t1;
608 insert into t1 values (null);
614 # Showing VIEW with VIEWs in subquery
616 create table t1 (a int);
617 create table t2 (a int);
618 create view v1 as select a from t1;
619 create view v2 as select a from t2 where a in (select a from v1);
625 # SHOW VIEW view with name with spaces
627 CREATE VIEW `v 1` AS select 5 AS `5`;
628 show create view `v 1`;
632 # Removing database with .frm archives
634 create database mysqltest;
635 create table mysqltest.t1 (a int, b int);
636 create view mysqltest.v1 as select a from mysqltest.t1;
637 alter view mysqltest.v1 as select b from mysqltest.t1;
638 alter view mysqltest.v1 as select a from mysqltest.t1;
639 drop database mysqltest;
642 # VIEW with full text
644 CREATE TABLE t1 (c1 int not null auto_increment primary key, c2 varchar(20), fulltext(c2));
645 insert into t1 (c2) VALUES ('real Beer'),('Water'),('Kossu'),('Coca-Cola'),('Vodka'),('Wine'),('almost real Beer');
646 select * from t1 WHERE match (c2) against ('Beer');
647 CREATE VIEW v1 AS SELECT * from t1 WHERE match (c2) against ('Beer');
653 # distinct in temporary table with a VIEW
655 create table t1 (a int);
656 insert into t1 values (1),(1),(2),(2),(3),(3);
657 create view v1 as select a from t1;
658 select distinct a from v1;
659 select distinct a from v1 limit 2;
660 select distinct a from t1 limit 2;
661 prepare stmt1 from "select distinct a from v1 limit 2";
664 deallocate prepare stmt1;
669 # aggregate function of aggregate function
671 create table t1 (tg_column bigint);
672 create view v1 as select count(tg_column) as vg_column from t1;
673 select avg(vg_column) from v1;
678 # VIEW of VIEW with column renaming
680 create table t1 (col1 bigint not null, primary key (col1));
681 create table t2 (col1 bigint not null, key (col1));
682 create view v1 as select * from t1;
683 create view v2 as select * from t2;
684 insert into v1 values (1);
685 insert into v2 values (1);
686 create view v3 (a,b) as select v1.col1 as a, v2.col1 as b from v1, v2 where v1.col1 = v2.col1;
689 drop view v3, v2, v1;
693 # VIEW based on functions with complex names
695 create function `f``1` () returns int return 5;
696 create view v1 as select test.`f``1` ();
700 drop function `f``1`;
703 # tested problem when function name length close to ALIGN_SIZE
705 create function a() returns int return 5;
706 create view v1 as select a();
712 # VIEW with collation
714 create table t2 (col1 char collate latin1_german2_ci);
715 create view v2 as select col1 collate latin1_german1_ci from t2;
722 # order by refers on integer field
724 create table t1 (a int);
725 insert into t1 values (1), (2);
726 create view v1 as select 5 from t1 order by 1;
732 # VIEW over dropped function
734 create function x1 () returns int return 5;
735 create table t1 (s1 int);
736 create view v1 as select x1() from t1;
738 -- error ER_VIEW_INVALID
740 --replace_column 8 # 12 # 13 #
746 # VIEW with floating point (long number) as column
748 create view v1 as select 99999999999999999999999999999999999999999999999999999 as col1;
753 # VIEWs with national characters
756 SET @old_cs_client = @@character_set_client;
757 SET @old_cs_results = @@character_set_results;
758 SET @old_cs_connection = @@character_set_connection;
761 create table tü (cü char);
762 create view vü as select cü from tü;
763 insert into vü values ('ü');
768 SET character_set_client = @old_cs_client;
769 SET character_set_results = @old_cs_results;
770 SET character_set_connection = @old_cs_connection;
773 # problem with used_tables() of outer reference resolved in VIEW
775 create table t1 (a int, b int);
776 insert into t1 values (1,2), (1,3), (2,4), (2,5), (3,10);
777 create view v1(c) as select a+1 from t1 where b >= 4;
778 select c from v1 where exists (select * from t1 where a=2 and b=c);
783 # view with cast operation
785 create view v1 as select cast(1 as char(3));
793 create table t1 (a int);
794 create view v1 as select a from t1;
795 create view v3 as select a from t1;
796 create database mysqltest;
797 -- error ER_FORBID_SCHEMA_CHANGE
798 rename table v1 to mysqltest.v1;
799 rename table v1 to v2;
800 --error ER_TABLE_EXISTS_ERROR
801 rename table v3 to v1, v2 to t1;
804 drop database mysqltest;
807 # bug handling from VIEWs
809 create view v1 as select 'a',1;
810 create view v2 as select * from v1 union all select * from v1;
811 create view v3 as select * from v2 where 1 = (select `1` from v2);
812 create view v4 as select * from v3;
813 -- error ER_SUBQUERY_NO_1_ROW
815 drop view v4, v3, v2, v1;
818 # VIEW over SELECT with prohibited clauses
820 -- error ER_VIEW_SELECT_CLAUSE
821 create view v1 as select 5 into @w;
822 -- error ER_VIEW_SELECT_CLAUSE
823 create view v1 as select 5 into outfile 'ttt';
824 create table t1 (a int);
825 -- error ER_VIEW_SELECT_CLAUSE
826 create view v1 as select a from t1 procedure analyse();
827 -- error ER_VIEW_SELECT_DERIVED
828 create view v1 as select 1 from (select 1) as d1;
832 # INSERT into VIEW with ON DUPLICATE
834 create table t1 (s1 int, primary key (s1));
835 create view v1 as select * from t1;
836 insert into v1 values (1) on duplicate key update s1 = 7;
837 insert into v1 values (1) on duplicate key update s1 = 7;
843 # test of updating and fetching from the same table check
845 create table t1 (col1 int);
846 create table t2 (col1 int);
847 create table t3 (col1 datetime not null);
848 create view v1 as select * from t1;
849 create view v2 as select * from v1;
850 create view v3 as select v2.col1 from v2,t2 where v2.col1 = t2.col1;
851 -- error ER_VIEW_PREVENT_UPDATE
852 update v2 set col1 = (select max(col1) from v1);
853 -- error ER_VIEW_PREVENT_UPDATE
854 update v2 set col1 = (select max(col1) from t1);
855 -- error ER_UPDATE_TABLE_USED
856 update v2 set col1 = (select max(col1) from v2);
857 -- error ER_VIEW_PREVENT_UPDATE
858 update v2,t2 set v2.col1 = (select max(col1) from v1) where v2.col1 = t2.col1;
859 -- error ER_VIEW_PREVENT_UPDATE
860 update t1,t2 set t1.col1 = (select max(col1) from v1) where t1.col1 = t2.col1;
861 -- error ER_UPDATE_TABLE_USED
862 update v1,t2 set v1.col1 = (select max(col1) from v1) where v1.col1 = t2.col1;
863 -- error ER_VIEW_PREVENT_UPDATE
864 update t2,v2 set v2.col1 = (select max(col1) from v1) where v2.col1 = t2.col1;
865 -- error ER_VIEW_PREVENT_UPDATE
866 update t2,t1 set t1.col1 = (select max(col1) from v1) where t1.col1 = t2.col1;
867 -- error ER_VIEW_PREVENT_UPDATE
868 update t2,v1 set v1.col1 = (select max(col1) from v1) where v1.col1 = t2.col1;
869 -- error ER_VIEW_PREVENT_UPDATE
870 update v2,t2 set v2.col1 = (select max(col1) from t1) where v2.col1 = t2.col1;
871 -- error ER_UPDATE_TABLE_USED
872 update t1,t2 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1;
873 -- error ER_VIEW_PREVENT_UPDATE
874 update v1,t2 set v1.col1 = (select max(col1) from t1) where v1.col1 = t2.col1;
875 -- error ER_UPDATE_TABLE_USED
876 update t2,v2 set v2.col1 = (select max(col1) from t1) where v2.col1 = t2.col1;
877 -- error ER_UPDATE_TABLE_USED
878 update t2,t1 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1;
879 -- error ER_UPDATE_TABLE_USED
880 update t2,v1 set v1.col1 = (select max(col1) from t1) where v1.col1 = t2.col1;
881 -- error ER_UPDATE_TABLE_USED
882 update v2,t2 set v2.col1 = (select max(col1) from v2) where v2.col1 = t2.col1;
883 -- error ER_VIEW_PREVENT_UPDATE
884 update t1,t2 set t1.col1 = (select max(col1) from v2) where t1.col1 = t2.col1;
885 -- error ER_VIEW_PREVENT_UPDATE
886 update v1,t2 set v1.col1 = (select max(col1) from v2) where v1.col1 = t2.col1;
887 -- error ER_VIEW_PREVENT_UPDATE
888 update t2,v2 set v2.col1 = (select max(col1) from v2) where v2.col1 = t2.col1;
889 -- error ER_VIEW_PREVENT_UPDATE
890 update t2,t1 set t1.col1 = (select max(col1) from v2) where t1.col1 = t2.col1;
891 -- error ER_VIEW_PREVENT_UPDATE
892 update t2,v1 set v1.col1 = (select max(col1) from v2) where v1.col1 = t2.col1;
893 -- error ER_VIEW_PREVENT_UPDATE
894 update v3 set v3.col1 = (select max(col1) from v1);
895 -- error ER_VIEW_PREVENT_UPDATE
896 update v3 set v3.col1 = (select max(col1) from t1);
897 -- error ER_VIEW_PREVENT_UPDATE
898 update v3 set v3.col1 = (select max(col1) from v2);
899 -- error ER_UPDATE_TABLE_USED
900 update v3 set v3.col1 = (select max(col1) from v3);
901 -- error ER_VIEW_PREVENT_UPDATE
902 delete from v2 where col1 = (select max(col1) from v1);
903 -- error ER_VIEW_PREVENT_UPDATE
904 delete from v2 where col1 = (select max(col1) from t1);
905 -- error ER_UPDATE_TABLE_USED
906 delete from v2 where col1 = (select max(col1) from v2);
907 -- error ER_VIEW_PREVENT_UPDATE
908 delete v2 from v2,t2 where (select max(col1) from v1) > 0 and v2.col1 = t2.col1;
909 -- error ER_VIEW_PREVENT_UPDATE
910 delete t1 from t1,t2 where (select max(col1) from v1) > 0 and t1.col1 = t2.col1;
911 -- error ER_UPDATE_TABLE_USED
912 delete v1 from v1,t2 where (select max(col1) from v1) > 0 and v1.col1 = t2.col1;
913 -- error ER_VIEW_PREVENT_UPDATE
914 delete v2 from v2,t2 where (select max(col1) from t1) > 0 and v2.col1 = t2.col1;
915 -- error ER_UPDATE_TABLE_USED
916 delete t1 from t1,t2 where (select max(col1) from t1) > 0 and t1.col1 = t2.col1;
917 -- error ER_VIEW_PREVENT_UPDATE
918 delete v1 from v1,t2 where (select max(col1) from t1) > 0 and v1.col1 = t2.col1;
919 -- error ER_UPDATE_TABLE_USED
920 delete v2 from v2,t2 where (select max(col1) from v2) > 0 and v2.col1 = t2.col1;
921 -- error ER_VIEW_PREVENT_UPDATE
922 delete t1 from t1,t2 where (select max(col1) from v2) > 0 and t1.col1 = t2.col1;
923 -- error ER_VIEW_PREVENT_UPDATE
924 delete v1 from v1,t2 where (select max(col1) from v2) > 0 and v1.col1 = t2.col1;
925 -- error ER_VIEW_PREVENT_UPDATE
926 insert into v2 values ((select max(col1) from v1));
927 -- error ER_VIEW_PREVENT_UPDATE
928 insert into t1 values ((select max(col1) from v1));
929 -- error ER_VIEW_PREVENT_UPDATE
930 insert into v2 values ((select max(col1) from v1));
931 -- error ER_VIEW_PREVENT_UPDATE
932 insert into v2 values ((select max(col1) from t1));
933 -- error ER_UPDATE_TABLE_USED
934 insert into t1 values ((select max(col1) from t1));
935 -- error ER_VIEW_PREVENT_UPDATE
936 insert into v2 values ((select max(col1) from t1));
937 -- error ER_UPDATE_TABLE_USED
938 insert into v2 values ((select max(col1) from v2));
939 -- error ER_VIEW_PREVENT_UPDATE
940 insert into t1 values ((select max(col1) from v2));
941 -- error ER_UPDATE_TABLE_USED
942 insert into v2 values ((select max(col1) from v2));
943 -- error ER_VIEW_PREVENT_UPDATE
944 insert into v3 (col1) values ((select max(col1) from v1));
945 -- error ER_VIEW_PREVENT_UPDATE
946 insert into v3 (col1) values ((select max(col1) from t1));
947 -- error ER_VIEW_PREVENT_UPDATE
948 insert into v3 (col1) values ((select max(col1) from v2));
949 # check with TZ tables in list
950 -- error ER_VIEW_PREVENT_UPDATE
951 insert into v3 (col1) values ((select CONVERT_TZ('20050101000000','UTC','MET') from v2));
952 insert into v3 (col1) values ((select CONVERT_TZ('20050101000000','UTC','MET') from t2));
953 -- error ER_BAD_NULL_ERROR
954 insert into t3 values ((select CONVERT_TZ('20050101000000','UTC','MET') from t2));
955 # temporary table algorithm view should be equal to subquery in the from clause
956 create algorithm=temptable view v4 as select * from t1;
957 insert into t1 values (1),(2),(3);
958 insert into t1 (col1) values ((select max(col1) from v4));
961 drop view v4,v3,v2,v1;
967 create table t1 (s1 int);
968 create view v1 as select * from t1;
969 -- error ER_WRONG_OBJECT
970 handler v1 open as xx;
975 # view with WHERE in nested join
977 create table t1(a int);
978 insert into t1 values (0), (1), (2), (3);
979 create table t2 (a int);
980 insert into t2 select a from t1 where a > 1;
981 create view v1 as select a from t1 where a > 1;
982 select * from t1 left join (t2 as t, v1) on v1.a=t1.a;
983 select * from t1 left join (t2 as t, t2) on t2.a=t1.a;
988 # Collation with view update
990 create table t1 (s1 char);
991 create view v1 as select s1 collate latin1_german1_ci as s1 from t1;
992 insert into v1 values ('a');
994 update v1 set s1='b';
996 update v1,t1 set v1.s1='c' where t1.s1=v1.s1;
998 prepare stmt1 from "update v1,t1 set v1.s1=? where t1.s1=v1.s1";
1000 execute stmt1 using @arg;
1003 execute stmt1 using @arg;
1005 deallocate prepare stmt1;
1010 # test view with LOCK TABLES (work around)
1012 create table t1 (a int);
1013 create table t2 (a int);
1014 create view v1 as select * from t1;
1015 lock tables t1 read, v1 read;
1017 -- error ER_TABLE_NOT_LOCKED
1023 # WITH CHECK OPTION insert/update test
1025 create table t1 (a int);
1026 create view v1 as select * from t1 where a < 2 with check option;
1028 insert into v1 values(1);
1029 -- error ER_VIEW_CHECK_FAILED
1030 insert into v1 values(3);
1031 # simple insert with ignore
1032 insert ignore into v1 values (2),(3),(0);
1034 # prepare data for next check
1036 # INSERT SELECT test
1037 insert into v1 SELECT 1;
1038 -- error ER_VIEW_CHECK_FAILED
1039 insert into v1 SELECT 3;
1040 # prepare data for next check
1041 create table t2 (a int);
1042 insert into t2 values (2),(3),(0);
1043 # INSERT SELECT with ignore test
1044 insert ignore into v1 SELECT a from t2;
1045 select * from t1 order by a desc;
1046 # simple UPDATE test
1047 update v1 set a=-1 where a=0;
1048 -- error ER_VIEW_CHECK_FAILED
1049 update v1 set a=2 where a=1;
1050 select * from t1 order by a desc;
1051 # prepare data for next check
1052 update v1 set a=0 where a=0;
1053 insert into t2 values (1);
1055 update v1,t2 set v1.a=v1.a-1 where v1.a=t2.a;
1056 select * from t1 order by a desc;
1057 # prepare data for next check
1058 update v1 set a=a+1;
1059 # multiupdate with ignore test
1060 update ignore v1,t2 set v1.a=v1.a+1 where v1.a=t2.a;
1067 # CASCADED/LOCAL CHECK OPTION test
1069 create table t1 (a int);
1070 create view v1 as select * from t1 where a < 2 with check option;
1071 create view v2 as select * from v1 where a > 0 with local check option;
1072 create view v3 as select * from v1 where a > 0 with cascaded check option;
1073 insert into v2 values (1);
1074 insert into v3 values (1);
1075 -- error ER_VIEW_CHECK_FAILED
1076 insert into v2 values (0);
1077 -- error ER_VIEW_CHECK_FAILED
1078 insert into v3 values (0);
1079 insert into v2 values (2);
1080 -- error ER_VIEW_CHECK_FAILED
1081 insert into v3 values (2);
1087 # CHECK OPTION with INSERT ... ON DUPLICATE KEY UPDATE
1089 create table t1 (a int, primary key (a));
1090 create view v1 as select * from t1 where a < 2 with check option;
1091 insert into v1 values (1) on duplicate key update a=2;
1092 -- error ER_VIEW_CHECK_FAILED
1093 insert into v1 values (1) on duplicate key update a=2;
1094 insert ignore into v1 values (1) on duplicate key update a=2;
1100 # check cyclic referencing protection on altering view
1102 create table t1 (s1 int);
1103 create view v1 as select * from t1;
1104 create view v2 as select * from v1;
1105 -- error ER_NO_SUCH_TABLE
1106 alter view v1 as select * from v2;
1107 -- error ER_NO_SUCH_TABLE
1108 alter view v1 as select * from v1;
1109 -- error ER_NO_SUCH_TABLE
1110 create or replace view v1 as select * from v2;
1111 -- error ER_NO_SUCH_TABLE
1112 create or replace view v1 as select * from v1;
1117 # check altering differ options
1119 create table t1 (a int);
1120 create view v1 as select * from t1;
1121 show create view v1;
1122 alter algorithm=undefined view v1 as select * from t1 with check option;
1123 show create view v1;
1124 alter algorithm=merge view v1 as select * from t1 with cascaded check option;
1125 show create view v1;
1126 alter algorithm=temptable view v1 as select * from t1;
1127 show create view v1;
1132 # updating view with subquery in the WHERE clause
1134 create table t1 (s1 int);
1135 create table t2 (s1 int);
1136 create view v2 as select * from t2 where s1 in (select s1 from t1);
1137 insert into v2 values (5);
1138 insert into t1 values (5);
1140 update v2 set s1 = 0;
1143 # check it with check option
1144 alter view v2 as select * from t2 where s1 in (select s1 from t1) with check option;
1145 insert into v2 values (5);
1146 -- error ER_VIEW_CHECK_FAILED
1147 update v2 set s1 = 1;
1148 insert into t1 values (1);
1149 update v2 set s1 = 1;
1152 # scheck how VIEWs with subqueries work with prepared statements
1153 prepare stmt1 from "select * from v2;";
1155 insert into t1 values (0);
1157 deallocate prepare stmt1;
1162 # test of substring_index with view
1164 create table t1 (t time);
1165 create view v1 as select substring_index(t,':',2) as t from t1;
1166 insert into t1 (t) values ('12:24:10');
1167 select substring_index(t,':',2) from t1;
1168 select substring_index(t,':',2) from v1;
1173 # test of cascaded check option for whiew without WHERE clause
1175 create table t1 (s1 tinyint);
1176 create view v1 as select * from t1 where s1 <> 0 with local check option;
1177 create view v2 as select * from v1 with cascaded check option;
1178 -- error ER_VIEW_CHECK_FAILED
1179 insert into v2 values (0);
1184 # inserting single value with check option failed always get error
1186 create table t1 (s1 int);
1187 create view v1 as select * from t1 where s1 < 5 with check option;
1189 -- error ER_VIEW_CHECK_FAILED
1190 insert ignore into v1 values (6);
1192 insert ignore into v1 values (6),(3);
1198 # changing value by trigger and CHECK OPTION
1200 create table t1 (s1 tinyint);
1201 create trigger t1_bi before insert on t1 for each row set new.s1 = 500;
1202 create view v1 as select * from t1 where s1 <> 127 with check option;
1203 -- error ER_VIEW_CHECK_FAILED
1204 insert into v1 values (0);
1212 # CASCADED should be used for all underlaying VIEWs
1214 create table t1 (s1 tinyint);
1215 create view v1 as select * from t1 where s1 <> 0;
1216 create view v2 as select * from v1 where s1 <> 1 with cascaded check option;
1217 -- error ER_VIEW_CHECK_FAILED
1218 insert into v2 values (0);
1225 # LOAD DATA with view and CHECK OPTION
1227 # fixed length fields
1228 create table t1 (a int, b char(10));
1229 create view v1 as select * from t1 where a != 0 with check option;
1230 -- error ER_VIEW_CHECK_FAILED
1231 load data infile '../../std_data/loaddata3.dat' into table v1 fields terminated by '' enclosed by '' ignore 1 lines;
1235 load data infile '../../std_data/loaddata3.dat' ignore into table v1 fields terminated by '' enclosed by '' ignore 1 lines;
1236 select * from t1 order by a,b;
1237 select * from v1 order by a,b;
1240 # variable length fields
1241 create table t1 (a text, b text);
1242 create view v1 as select * from t1 where a <> 'Field A' with check option;
1243 -- error ER_VIEW_CHECK_FAILED
1244 load data infile '../../std_data/loaddata2.dat' into table v1 fields terminated by ',' enclosed by '''';
1245 select concat('|',a,'|'), concat('|',b,'|') from t1;
1246 select concat('|',a,'|'), concat('|',b,'|') from v1;
1248 load data infile '../../std_data/loaddata2.dat' ignore into table v1 fields terminated by ',' enclosed by '''';
1249 select concat('|',a,'|'), concat('|',b,'|') from t1;
1250 select concat('|',a,'|'), concat('|',b,'|') from v1;
1255 # Trys update table from which we select using views and subqueries
1257 create table t1 (s1 smallint);
1258 create view v1 as select * from t1 where 20 < (select (s1) from t1);
1259 -- error ER_NON_INSERTABLE_TABLE
1260 insert into v1 values (30);
1261 create view v2 as select * from t1;
1262 create view v3 as select * from t1 where 20 < (select (s1) from v2);
1263 -- error ER_NON_INSERTABLE_TABLE
1264 insert into v3 values (30);
1265 create view v4 as select * from v2 where 20 < (select (s1) from t1);
1266 -- error ER_NON_INSERTABLE_TABLE
1267 insert into v4 values (30);
1268 drop view v4, v3, v2, v1;
1272 # CHECK TABLE with VIEW
1274 create table t1 (a int);
1275 create view v1 as select * from t1;
1283 # merge of VIEW with several tables
1285 create table t1 (a int);
1286 create table t2 (a int);
1287 create table t3 (a int);
1288 insert into t1 values (1), (2), (3);
1289 insert into t2 values (1), (3);
1290 insert into t3 values (1), (2), (4);
1292 create view v3 (a,b) as select t1.a as a, t2.a as b from t1 left join t2 on (t1.a=t2.a);
1293 select * from t3 left join v3 on (t3.a = v3.a);
1294 explain extended select * from t3 left join v3 on (t3.a = v3.a);
1296 create view v1 (a) as select a from t1;
1297 create view v2 (a) as select a from t2;
1298 create view v4 (a,b) as select v1.a as a, v2.a as b from v1 left join v2 on (v1.a=v2.a);
1299 select * from t3 left join v4 on (t3.a = v4.a);
1300 explain extended select * from t3 left join v4 on (t3.a = v4.a);
1301 # PS with view over views
1302 prepare stmt1 from "select * from t3 left join v4 on (t3.a = v4.a);";
1305 deallocate prepare stmt1;
1306 drop view v4,v3,v2,v1;
1307 drop tables t1,t2,t3;
1310 # updating of join view
1312 create table t1 (a int, primary key (a), b int);
1313 create table t2 (a int, primary key (a));
1314 insert into t1 values (1,100), (2,200);
1315 insert into t2 values (1), (3);
1316 # legal view for update
1317 create view v3 (a,b) as select t1.a as a, t2.a as b from t1, t2;
1318 update v3 set a= 10 where a=1;
1321 # view without primary key
1322 create view v2 (a,b) as select t1.b as a, t2.a as b from t1, t2;
1323 set updatable_views_with_limit=NO;
1324 -- error ER_NON_UPDATABLE_TABLE
1325 update v2 set a= 10 where a=200 limit 1;
1326 set updatable_views_with_limit=DEFAULT;
1330 # prepare statement with updating join view
1333 prepare stmt1 from "update v3 set a= ? where a=?";
1334 execute stmt1 using @a,@b;
1338 execute stmt1 using @a,@b;
1340 deallocate prepare stmt1;
1345 # inserting/deleting join view
1347 create table t1 (a int, primary key (a), b int);
1348 create table t2 (a int, primary key (a), b int);
1349 insert into t2 values (1000, 2000);
1350 create view v3 (a,b) as select t1.a as a, t2.a as b from t1, t2;
1351 # inserting into join view without field list
1352 -- error ER_VIEW_NO_INSERT_FIELD_LIST
1353 insert into v3 values (1,2);
1354 -- error ER_VIEW_NO_INSERT_FIELD_LIST
1355 insert into v3 select * from t2;
1356 # inserting in several tables of join view
1357 -- error ER_VIEW_MULTIUPDATE
1358 insert into v3(a,b) values (1,2);
1359 -- error ER_VIEW_MULTIUPDATE
1360 insert into v3(a,b) select * from t2;
1361 # correct inserts into join view
1362 insert into v3(a) values (1);
1363 insert into v3(b) values (10);
1364 insert into v3(a) select a from t2;
1365 insert into v3(b) select b from t2;
1366 insert into v3(a) values (1) on duplicate key update a=a+10000+VALUES(a);
1369 # try delete from join view
1370 -- error ER_VIEW_DELETE_MERGE_VIEW
1372 -- error ER_VIEW_DELETE_MERGE_VIEW
1373 delete v3,t1 from v3,t1;
1374 -- error ER_VIEW_DELETE_MERGE_VIEW
1375 delete t1,v3 from t1,v3;
1376 # delete from t1 just to reduce result set size
1378 # prepare statement with insert join view
1379 prepare stmt1 from "insert into v3(a) values (?);";
1381 execute stmt1 using @a;
1383 execute stmt1 using @a;
1384 deallocate prepare stmt1;
1385 prepare stmt1 from "insert into v3(a) select ?;";
1387 execute stmt1 using @a;
1389 execute stmt1 using @a;
1390 deallocate prepare stmt1;
1397 # View field names should be case insensitive
1399 create table t1(f1 int);
1400 create view v1 as select f1 from t1;
1401 select * from v1 where F1 = 1;
1406 # Resolving view fields in subqueries in VIEW (Bug#6394)
1408 create table t1(c1 int);
1409 create table t2(c2 int);
1410 insert into t1 values (1),(2),(3);
1411 insert into t2 values (1);
1412 SELECT c1 FROM t1 WHERE c1 IN (SELECT c2 FROM t2);
1413 SELECT c1 FROM t1 WHERE EXISTS (SELECT c2 FROM t2 WHERE c2 = c1);
1414 create view v1 as SELECT c1 FROM t1 WHERE c1 IN (SELECT c2 FROM t2);
1415 create view v2 as SELECT c1 FROM t1 WHERE EXISTS (SELECT c2 FROM t2 WHERE c2 = c1);
1418 select * from (select c1 from v2) X;
1423 # view over other view setup (Bug#7433)
1425 CREATE TABLE t1 (C1 INT, C2 INT);
1426 CREATE TABLE t2 (C2 INT);
1427 CREATE VIEW v1 AS SELECT C2 FROM t2;
1428 CREATE VIEW v2 AS SELECT C1 FROM t1 LEFT OUTER JOIN v1 USING (C2);
1434 # view and group_concat() (Bug#7116)
1436 create table t1 (col1 char(5),col2 int,col3 int);
1437 insert into t1 values ('one',10,25), ('two',10,50), ('two',10,50), ('one',20,25), ('one',30,25);
1438 create view v1 as select * from t1;
1439 select col1,group_concat(col2,col3) from t1 group by col1;
1440 select col1,group_concat(col2,col3) from v1 group by col1;
1445 # Item_ref resolved as view field (Bug#6894)
1447 create table t1 (s1 int, s2 char);
1448 create view v1 as select s1, s2 from t1;
1449 -- error ER_BAD_FIELD_ERROR
1450 select s2 from v1 vq1 where 2 = (select count(*) from v1 vq2 having vq1.s2 = vq2.s2);
1451 select s2 from v1 vq1 where 2 = (select count(*) aa from v1 vq2 having vq1.s2 = aa);
1456 # Test case for Bug#9398 CREATE TABLE with SELECT from a multi-table view
1458 CREATE TABLE t1 (a1 int);
1459 CREATE TABLE t2 (a2 int);
1460 INSERT INTO t1 VALUES (1), (2), (3), (4);
1461 INSERT INTO t2 VALUES (1), (2), (3);
1462 CREATE VIEW v1(a,b) AS SELECT a1,a2 FROM t1 JOIN t2 ON a1=a2 WHERE a1>1;
1465 CREATE TABLE t3 SELECT * FROM v1;
1469 DROP TABLE t1,t2,t3;
1472 # Test for Bug#8703 insert into table select from view crashes
1474 create table t1 (a int);
1475 create table t2 like t1;
1476 create table t3 like t1;
1477 create view v1 as select t1.a x, t2.a y from t1 join t2 where t1.a=t2.a;
1478 insert into t3 select x from v1;
1479 insert into t2 select x from v1;
1481 drop table t1,t2,t3;
1484 # Test for Bug#6106 query over a view using subquery for the underlying table
1487 CREATE TABLE t1 (col1 int PRIMARY KEY, col2 varchar(10));
1488 INSERT INTO t1 VALUES(1,'trudy');
1489 INSERT INTO t1 VALUES(2,'peter');
1490 INSERT INTO t1 VALUES(3,'sanja');
1491 INSERT INTO t1 VALUES(4,'monty');
1492 INSERT INTO t1 VALUES(5,'david');
1493 INSERT INTO t1 VALUES(6,'kent');
1494 INSERT INTO t1 VALUES(7,'carsten');
1495 INSERT INTO t1 VALUES(8,'ranger');
1496 INSERT INTO t1 VALUES(10,'matt');
1497 CREATE TABLE t2 (col1 int, col2 int, col3 char(1));
1498 INSERT INTO t2 VALUES (1,1,'y');
1499 INSERT INTO t2 VALUES (1,2,'y');
1500 INSERT INTO t2 VALUES (2,1,'n');
1501 INSERT INTO t2 VALUES (3,1,'n');
1502 INSERT INTO t2 VALUES (4,1,'y');
1503 INSERT INTO t2 VALUES (4,2,'n');
1504 INSERT INTO t2 VALUES (4,3,'n');
1505 INSERT INTO t2 VALUES (6,1,'n');
1506 INSERT INTO t2 VALUES (8,1,'y');
1508 CREATE VIEW v1 AS SELECT * FROM t1;
1510 SELECT a.col1,a.col2,b.col2,b.col3
1511 FROM t1 a LEFT JOIN t2 b ON a.col1=b.col1
1512 WHERE b.col2 IS NULL OR
1513 b.col2=(SELECT MAX(col2) FROM t2 b WHERE b.col1=a.col1);
1515 SELECT a.col1,a.col2,b.col2,b.col3
1516 FROM v1 a LEFT JOIN t2 b ON a.col1=b.col1
1517 WHERE b.col2 IS NULL OR
1518 b.col2=(SELECT MAX(col2) FROM t2 b WHERE b.col1=a.col1);
1520 CREATE VIEW v2 AS SELECT * FROM t2;
1522 SELECT a.col1,a.col2,b.col2,b.col3
1523 FROM v2 b RIGHT JOIN v1 a ON a.col1=b.col1
1524 WHERE b.col2 IS NULL OR
1525 b.col2=(SELECT MAX(col2) FROM v2 b WHERE b.col1=a.col1);
1527 # Tests from the report for Bug#6107
1529 SELECT a.col1,a.col2,b.col2,b.col3
1530 FROM v2 b RIGHT JOIN v1 a ON a.col1=b.col1
1531 WHERE a.col1 IN (1,5,9) AND
1533 b.col2=(SELECT MAX(col2) FROM v2 b WHERE b.col1=a.col1));
1535 CREATE VIEW v3 AS SELECT * FROM t1 WHERE col1 IN (1,5,9);
1537 SELECT a.col1,a.col2,b.col2,b.col3
1538 FROM v2 b RIGHT JOIN v3 a ON a.col1=b.col1
1539 WHERE b.col2 IS NULL OR
1540 b.col2=(SELECT MAX(col2) FROM v2 b WHERE b.col1=a.col1);
1546 # Bug#8490 Select from views containing subqueries causes server to hang
1549 create table t1 as select 1 A union select 2 union select 3;
1550 create table t2 as select * from t1;
1551 create view v1 as select * from t1 where a in (select * from t2);
1552 select * from v1 A, v1 B where A.a = B.a;
1553 create table t3 as select a a,a b from t2;
1554 create view v2 as select * from t3 where
1555 a in (select * from t1) or b in (select * from t2);
1556 select * from v2 A, v2 B where A.a = B.b;
1558 drop table t1, t2, t3;
1561 # Test case for Bug#8528 select from view over multi-table view
1563 CREATE TABLE t1 (a int);
1564 CREATE TABLE t2 (b int);
1565 INSERT INTO t1 VALUES (1), (2), (3), (4);
1566 INSERT INTO t2 VALUES (4), (2);
1568 CREATE VIEW v1 AS SELECT * FROM t1,t2 WHERE t1.a=t2.b;
1570 CREATE VIEW v2 AS SELECT * FROM v1;
1577 # Correct restoring view name in SP table locking Bug#9758
1579 create table t1 (a int);
1580 create view v1 as select sum(a) from t1 group by a;
1582 create procedure p1()
1594 # Bug#7422 "order by" doesn't work
1596 CREATE TABLE t1(a char(2) primary key, b char(2));
1597 CREATE TABLE t2(a char(2), b char(2), index i(a));
1598 INSERT INTO t1 VALUES ('a','1'), ('b','2');
1599 INSERT INTO t2 VALUES ('a','5'), ('a','6'), ('b','5'), ('b','6');
1601 SELECT t1.b as c, t2.b as d FROM t1,t2 WHERE t1.a=t2.a;
1602 SELECT d, c FROM v1 ORDER BY d,c;
1606 # using sum(distinct ) & avg(distinct ) in views (Bug#7015)
1608 create table t1 (s1 int);
1609 create view v1 as select sum(distinct s1) from t1;
1612 create view v1 as select avg(distinct s1) from t1;
1618 # using cast(... as decimal) in views (Bug#11387);
1620 create view v1 as select cast(1 as decimal);
1625 # Bug#11298 insert into select from VIEW produces incorrect result when
1627 create table t1(f1 int);
1628 create table t2(f2 int);
1629 insert into t1 values(1),(2),(3);
1630 insert into t2 values(1),(2),(3);
1631 create view v1 as select * from t1,t2 where f1=f2;
1632 create table t3 (f1 int, f2 int);
1633 insert into t3 select * from v1 order by 1;
1636 drop table t1,t2,t3;
1639 # Generation unique names for columns, and correct names check (Bug#7448)
1641 # names with ' and \
1642 create view v1 as select '\\','\\shazam';
1645 create view v1 as select '\'','\shazam';
1648 # autogenerated names differ by case only
1649 create view v1 as select 'k','K';
1652 create table t1 (s1 int);
1653 # same autogenerated names
1654 create view v1 as select s1, 's1' from t1;
1657 create view v1 as select 's1', s1 from t1;
1660 # set name as one of expected autogenerated
1661 create view v1 as select 's1', s1, 1 as My_exp_s1 from t1;
1664 create view v1 as select 1 as My_exp_s1, 's1', s1 from t1;
1667 # set name conflict with autogenerated names
1668 create view v1 as select 1 as s1, 's1', 's1' from t1;
1671 create view v1 as select 's1', 's1', 1 as s1 from t1;
1674 # underlying field name conflict with autogenerated names
1675 create view v1 as select s1, 's1', 's1' from t1;
1678 create view v1 as select 's1', 's1', s1 from t1;
1681 # underlying field name conflict with set name
1682 -- error ER_DUP_FIELDNAME
1683 create view v1 as select 1 as s1, 's1', s1 from t1;
1684 -- error ER_DUP_FIELDNAME
1685 create view v1 as select 's1', s1, 1 as s1 from t1;
1687 # set names differ by case only
1688 -- error ER_DUP_FIELDNAME
1689 create view v1(k, K) as select 1,2;
1692 # using time_format in view (Bug#7521)
1694 create view v1 as SELECT TIME_FORMAT(SEC_TO_TIME(3600),'%H:%i') as t;
1699 # evaluation constant functions in WHERE (Bug#4663)
1701 create table t1 (a timestamp default now());
1702 create table t2 (b timestamp default now());
1703 create view v1 as select a,b,t1.a < now() from t1,t2 where t1.a < now();
1704 SHOW CREATE VIEW v1;
1707 CREATE TABLE t1 ( a varchar(50) );
1708 CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = CURRENT_USER();
1709 SHOW CREATE VIEW v1;
1711 CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = VERSION();
1712 SHOW CREATE VIEW v1;
1714 CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = DATABASE();
1715 SHOW CREATE VIEW v1;
1720 # checking views after some view with error (Bug#11337)
1722 CREATE TABLE t1 (col1 time);
1723 CREATE TABLE t2 (col1 time);
1724 CREATE VIEW v1 AS SELECT CONVERT_TZ(col1,'GMT','MET') FROM t1;
1725 CREATE VIEW v2 AS SELECT CONVERT_TZ(col1,'GMT','MET') FROM t2;
1726 CREATE VIEW v3 AS SELECT CONVERT_TZ(col1,'GMT','MET') FROM t1;
1727 CREATE VIEW v4 AS SELECT CONVERT_TZ(col1,'GMT','MET') FROM t2;
1728 CREATE VIEW v5 AS SELECT CONVERT_TZ(col1,'GMT','MET') FROM t1;
1729 CREATE VIEW v6 AS SELECT CONVERT_TZ(col1,'GMT','MET') FROM t2;
1731 CHECK TABLE v1, v2, v3, v4, v5, v6;
1732 drop view v1, v2, v3, v4, v5, v6;
1736 drop function if exists f1;
1737 drop function if exists f2;
1739 CREATE TABLE t1 (col1 time);
1740 CREATE TABLE t2 (col1 time);
1741 CREATE TABLE t3 (col1 time);
1742 create function f1 () returns int return (select max(col1) from t1);
1743 create function f2 () returns int return (select max(col1) from t2);
1744 CREATE VIEW v1 AS SELECT f1() FROM t3;
1745 CREATE VIEW v2 AS SELECT f2() FROM t3;
1746 CREATE VIEW v3 AS SELECT f1() FROM t3;
1747 CREATE VIEW v4 AS SELECT f2() FROM t3;
1748 CREATE VIEW v5 AS SELECT f1() FROM t3;
1749 CREATE VIEW v6 AS SELECT f2() FROM t3;
1751 CHECK TABLE v1, v2, v3, v4, v5, v6;
1752 create function f1 () returns int return (select max(col1) from t1);
1754 CHECK TABLE v1, v2, v3, v4, v5, v6;
1757 drop view v1, v2, v3, v4, v5, v6;
1761 # Bug#11325 Wrong date comparison in views
1763 create table t1 (f1 date);
1764 insert into t1 values ('2005-01-01'),('2005-02-02');
1765 create view v1 as select * from t1;
1766 select * from v1 where f1='2005.02.02';
1767 select * from v1 where '2005.02.02'=f1;
1772 # using encrypt & substring_index in view (Bug#7024)
1774 CREATE VIEW v1 AS SELECT ENCRYPT("dhgdhgd");
1779 CREATE VIEW v1 AS SELECT SUBSTRING_INDEX("dkjhgd:kjhdjh", ":", 1);
1784 # hide underlying tables names in case of imposibility to update (Bug#10773)
1786 create table t1 (f59 int, f60 int, f61 int);
1787 insert into t1 values (19,41,32);
1788 create view v1 as select f59, f60 from t1 where f59 in
1789 (select f59 from t1);
1790 -- error ER_NON_UPDATABLE_TABLE
1791 update v1 set f60=2345;
1792 -- error ER_VIEW_PREVENT_UPDATE
1793 update t1 set f60=(select max(f60) from v1);
1798 # Using var_samp with view (Bug#10651)
1800 create table t1 (s1 int);
1801 create view v1 as select var_samp(s1) from t1;
1802 show create view v1;
1808 # Correct inserting data check (absence of default value) for view
1809 # underlying tables (Bug#6443)
1811 set sql_mode='strict_all_tables';
1812 CREATE TABLE t1 (col1 INT NOT NULL, col2 INT NOT NULL);
1813 CREATE VIEW v1 (vcol1) AS SELECT col1 FROM t1;
1814 CREATE VIEW v2 (vcol1) AS SELECT col1 FROM t1 WHERE col2 > 2;
1815 -- error ER_NO_DEFAULT_FOR_FIELD
1816 INSERT INTO t1 (col1) VALUES(12);
1817 -- error ER_NO_DEFAULT_FOR_VIEW_FIELD
1818 INSERT INTO v1 (vcol1) VALUES(12);
1819 -- error ER_NO_DEFAULT_FOR_VIEW_FIELD
1820 INSERT INTO v2 (vcol1) VALUES(12);
1821 set sql_mode=default;
1827 # Bug#11399 Use an alias in a select statement on a view
1829 create table t1 (f1 int);
1830 insert into t1 values (1);
1831 create view v1 as select f1 from t1;
1832 select f1 as alias from v1;
1838 # Test for Bug#6120 SP cache to be invalidated when altering a view
1841 CREATE TABLE t1 (s1 int, s2 int);
1842 INSERT INTO t1 VALUES (1,2);
1843 CREATE VIEW v1 AS SELECT s2 AS s1, s1 AS s2 FROM t1;
1845 CREATE PROCEDURE p1 () SELECT * FROM v1;
1847 ALTER VIEW v1 AS SELECT s1 AS s1, s2 AS s2 FROM t1;
1850 CREATE VIEW v1 AS SELECT s2 AS s1, s1 AS s2 FROM t1;
1859 # Test for Bug#11709 View was ordered by wrong column
1861 create table t1 (f1 int, f2 int);
1862 create view v1 as select f1 as f3, f2 as f1 from t1;
1863 insert into t1 values (1,3),(2,1),(3,2);
1864 select * from v1 order by f1;
1870 # Test for Bug#11771 wrong query_id in SELECT * FROM <view>
1872 CREATE TABLE t1 (f1 char);
1873 INSERT INTO t1 VALUES ('A');
1874 CREATE VIEW v1 AS SELECT * FROM t1;
1876 INSERT INTO t1 VALUES('B');
1885 # opening table in correct locking mode (Bug#9597)
1887 CREATE TABLE t1 ( bug_table_seq INTEGER NOT NULL);
1888 CREATE OR REPLACE VIEW v1 AS SELECT * from t1;
1889 DROP PROCEDURE IF EXISTS p1;
1891 CREATE PROCEDURE p1 ( )
1893 DO (SELECT @next := IFNULL(max(bug_table_seq),0) + 1 FROM v1);
1894 INSERT INTO t1 VALUES (1);
1904 # Bug#11760 Typo in Item_func_add_time::print() results in NULLs returned
1906 create table t1(f1 datetime);
1907 insert into t1 values('2005.01.01 12:0:0');
1908 create view v1 as select f1, subtime(f1, '1:1:1') as sb from t1;
1915 # Test for Bug#11412 query over a multitable view with GROUP_CONCAT
1918 aid int PRIMARY KEY,
1919 fn varchar(20) NOT NULL,
1920 ln varchar(20) NOT NULL
1926 INSERT INTO t1 VALUES(1,'a','b'), (2,'c','d');
1927 INSERT INTO t2 values (1,1), (2,1), (2,2);
1929 CREATE VIEW v1 AS SELECT t1.*,t2.pid FROM t1,t2 WHERE t1.aid = t2.aid;
1931 SELECT pid,GROUP_CONCAT(CONCAT(fn,' ',ln) ORDER BY 1) FROM t1,t2
1932 WHERE t1.aid = t2.aid GROUP BY pid;
1933 SELECT pid,GROUP_CONCAT(CONCAT(fn,' ',ln) ORDER BY 1) FROM v1 GROUP BY pid;
1940 # Test for Bug#12382 SELECT * FROM view after INSERT command
1943 CREATE TABLE t1 (id int PRIMARY KEY, f varchar(255));
1944 CREATE VIEW v1 AS SELECT id, f FROM t1 WHERE id <= 2;
1945 INSERT INTO t1 VALUES (2, 'foo2');
1946 INSERT INTO t1 VALUES (1, 'foo1');
1956 # Test for Bug#12470 crash for a simple select from a view defined
1957 # as a join over 5 tables
1959 CREATE TABLE t1 (pk int PRIMARY KEY, b int);
1960 CREATE TABLE t2 (pk int PRIMARY KEY, fk int, INDEX idx(fk));
1961 CREATE TABLE t3 (pk int PRIMARY KEY, fk int, INDEX idx(fk));
1962 CREATE TABLE t4 (pk int PRIMARY KEY, fk int, INDEX idx(fk));
1963 CREATE TABLE t5 (pk int PRIMARY KEY, fk int, INDEX idx(fk));
1965 SELECT t1.pk as a FROM t1,t2,t3,t4,t5
1966 WHERE t1.b IS NULL AND
1967 t1.pk=t2.fk AND t2.pk=t3.fk AND t3.pk=t4.fk AND t4.pk=t5.fk;
1972 DROP TABLE t1,t2,t3,t4,t5;
1976 # Bug#12298 Typo in function name results in erroneous view being created.
1978 create view v1 as select timestampdiff(day,'1997-01-01 00:00:00','1997-01-02 00:00:00') as f1;
1983 # repeatable CREATE VIEW statement Bug#12468
1985 create table t1(a int);
1986 create procedure p1() create view v1 as select * from t1;
1988 -- error ER_NO_SUCH_TABLE
1990 -- error ER_NO_SUCH_TABLE
1996 # Bug#10624 Views with multiple UNION and UNION ALL produce incorrect results
1998 create table t1 (f1 int);
1999 create table t2 (f1 int);
2000 insert into t1 values (1);
2001 insert into t2 values (2);
2002 create view v1 as select * from t1 union select * from t2 union all select * from t2;
2009 # Test for Bug#10970 view referring a temporary table indirectly
2012 CREATE TEMPORARY TABLE t1 (a int);
2013 CREATE FUNCTION f1 () RETURNS int RETURN (SELECT COUNT(*) FROM t1);
2014 -- error ER_VIEW_SELECT_TMPTABLE
2015 CREATE VIEW v1 AS SELECT f1();
2022 # Bug#12533 (crash on DESCRIBE <view> after renaming base table column)
2025 DROP TABLE IF EXISTS t1;
2026 DROP VIEW IF EXISTS v1;
2029 CREATE TABLE t1 (f4 CHAR(5));
2030 CREATE VIEW v1 AS SELECT * FROM t1;
2033 ALTER TABLE t1 CHANGE COLUMN f4 f4x CHAR(5);
2034 --error ER_VIEW_INVALID
2041 # Bug#12489 wrongly printed strcmp() function results in creation of broken
2043 create table t1 (f1 char);
2044 create view v1 as select strcmp(f1,'a') from t1;
2051 # Bug#12922 if(sum(),...) with group from view returns wrong results
2053 create table t1 (f1 int, f2 int,f3 int);
2054 insert into t1 values (1,10,20),(2,0,0);
2055 create view v1 as select * from t1;
2056 select if(sum(f1)>1,f2,f3) from v1 group by f1;
2065 r_object_id char(16) NOT NULL,
2066 group_name varchar(32) NOT NULL
2070 r_object_id char(16) NOT NULL,
2071 i_position int(11) NOT NULL,
2072 users_names varchar(32) default NULL
2076 create view v1 as select r_object_id, group_name from t1;
2077 create view v2 as select r_object_id, i_position, users_names from t2;
2079 create unique index r_object_id on t1(r_object_id);
2080 create index group_name on t1(group_name);
2081 create unique index r_object_id_i_position on t2(r_object_id,i_position);
2082 create index users_names on t2(users_names);
2084 insert into t1 values('120001a080000542','tstgroup1');
2085 insert into t2 values('120001a080000542',-1, 'guser01');
2086 insert into t2 values('120001a080000542',-2, 'guser02');
2088 select v1.r_object_id, v2.users_names from v1, v2
2089 where (v1.group_name='tstgroup1') and v2.r_object_id=v1.r_object_id
2090 order by users_names;
2097 # Bug#6808 Views: CREATE VIEW v ... FROM t AS v fails
2100 create table t1 (s1 int);
2101 create view abc as select * from t1 as abc;
2107 # Bug#12993 View column rename broken in subselect
2109 create table t1(f1 char(1));
2110 create view v1 as select * from t1;
2111 select * from (select f1 as f2 from v1) v where v.f2='a';
2117 # Bug#11416 Server crash if using a view that uses function convert_tz
2119 create view v1 as SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');
2125 # Bugs#12963, #13000 wrong creation of VIEW with DAYNAME, DAYOFWEEK, and WEEKDAY
2128 CREATE TABLE t1 (date DATE NOT NULL);
2129 INSERT INTO t1 VALUES ('2005-09-06');
2131 CREATE VIEW v1 AS SELECT DAYNAME(date) FROM t1;
2132 SHOW CREATE VIEW v1;
2134 CREATE VIEW v2 AS SELECT DAYOFWEEK(date) FROM t1;
2135 SHOW CREATE VIEW v2;
2137 CREATE VIEW v3 AS SELECT WEEKDAY(date) FROM t1;
2138 SHOW CREATE VIEW v3;
2140 SELECT DAYNAME('2005-09-06');
2141 SELECT DAYNAME(date) FROM t1;
2144 SELECT DAYOFWEEK('2005-09-06');
2145 SELECT DAYOFWEEK(date) FROM t1;
2148 SELECT WEEKDAY('2005-09-06');
2149 SELECT WEEKDAY(date) FROM t1;
2153 DROP VIEW v1, v2, v3;
2157 # Bug#13411 crash when using non-qualified view column in HAVING clause
2160 CREATE TABLE t1 ( a int, b int );
2161 INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
2162 CREATE VIEW v1 AS SELECT a,b FROM t1;
2163 SELECT t1.a FROM t1 GROUP BY t1.a HAVING a > 1;
2164 SELECT v1.a FROM v1 GROUP BY v1.a HAVING a > 1;
2171 # Bug#13410 failed name resolution for qualified view column in HAVING
2174 CREATE TABLE t1 ( a int, b int );
2175 INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
2176 CREATE VIEW v1 AS SELECT a,b FROM t1;
2177 SELECT t1.a FROM t1 GROUP BY t1.a HAVING t1.a > 1;
2178 SELECT v1.a FROM v1 GROUP BY v1.a HAVING v1.a > 1;
2179 SELECT t_1.a FROM t1 AS t_1 GROUP BY t_1.a HAVING t_1.a IN (1,2,3);
2180 SELECT v_1.a FROM v1 AS v_1 GROUP BY v_1.a HAVING v_1.a IN (1,2,3);
2187 # Bug#13327 view wasn't using index for const condition
2190 CREATE TABLE t1 (a INT, b INT, INDEX(a,b));
2191 CREATE TABLE t2 LIKE t1;
2192 CREATE TABLE t3 (a INT);
2193 INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
2194 INSERT INTO t2 VALUES (1,1),(2,2),(3,3);
2195 INSERT INTO t3 VALUES (1),(2),(3);
2196 CREATE VIEW v1 AS SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a AND t1.b=t2.b;
2197 CREATE VIEW v2 AS SELECT t3.* FROM t1,t3 WHERE t1.a=t3.a;
2198 EXPLAIN SELECT t1.* FROM t1 JOIN t2 WHERE t1.a=t2.a AND t1.b=t2.b AND t1.a=1;
2199 EXPLAIN SELECT * FROM v1 WHERE a=1;
2200 EXPLAIN SELECT * FROM v2 WHERE a=1;
2202 DROP TABLE t1,t2,t3;
2206 # Bug#13622 Wrong view .frm created if some field's alias contain \n
2208 create table t1 (f1 int);
2209 create view v1 as select t1.f1 as '123
2216 # Bug#14466 lost sort order in GROUP_CONCAT() in a view
2218 create table t1 (f1 int, f2 int);
2219 insert into t1 values(1,1),(1,2),(1,3);
2220 create view v1 as select f1 ,group_concat(f2 order by f2 asc) from t1 group by f1;
2221 create view v2 as select f1 ,group_concat(f2 order by f2 desc) from t1 group by f1;
2229 # Bug#14026 Crash on second PS execution when using views
2231 create table t1 (x int, y int);
2232 create table t2 (x int, y int, z int);
2233 create table t3 (x int, y int, z int);
2234 create table t4 (x int, y int, z int);
2239 (t1 join t2 on ((t1.y = t2.y)))
2241 (t3 left join t4 on (t3.y = t4.y) and (t3.z = t4.z))
2244 prepare stmt1 from "select count(*) from v1 where x = ?";
2247 execute stmt1 using @parm1;
2248 execute stmt1 using @parm1;
2250 drop table t1,t2,t3,t4;
2254 # Bug#14540 OPTIMIZE, ANALYZE, REPAIR applied to not a view
2257 CREATE TABLE t1(id INT);
2258 CREATE VIEW v1 AS SELECT id FROM t1;
2273 # Bug#14719 Views DEFINER grammar is incorrect
2276 create definer = current_user() sql security invoker view v1 as select 1;
2277 show create view v1;
2280 create definer = current_user sql security invoker view v1 as select 1;
2281 show create view v1;
2286 # Bug#14816 test_if_order_by_key() expected only Item_fields.
2288 create table t1 (id INT, primary key(id));
2289 insert into t1 values (1),(2);
2290 create view v1 as select * from t1;
2291 explain select id from v1 order by id;
2297 # Bug#14850 Item_ref's values wasn't updated
2299 create table t1(f1 int, f2 int);
2300 insert into t1 values (null, 10), (null,2);
2301 select f1, sum(f2) from t1 group by f1;
2302 create view v1 as select * from t1;
2303 select f1, sum(f2) from v1 group by f1;
2309 # Bug#14885 incorrect SOURCE in view created in a procedure
2310 # TODO: here SOURCE string must be shown when it will be possible
2313 drop procedure if exists p1;
2316 create procedure p1 () deterministic
2318 create view v1 as select 1;
2323 show create view v1;
2329 # Bug#15096 using function with view for view creation
2331 CREATE VIEW v1 AS SELECT 42 AS Meaning;
2333 DROP FUNCTION IF EXISTS f1;
2336 CREATE FUNCTION f1() RETURNS INTEGER
2338 DECLARE retn INTEGER;
2339 SELECT Meaning FROM v1 INTO retn;
2344 CREATE VIEW v2 AS SELECT f1();
2351 # Bug#14861 aliased column names are not preserved.
2353 create table t1 (id numeric, warehouse_id numeric);
2354 create view v1 as select id from t1;
2356 select t1.warehouse_id, v1.id as receipt_id
2357 from t1, v1 where t1.id = v1.id;
2359 insert into t1 (id, warehouse_id) values(3, 2);
2360 insert into t1 (id, warehouse_id) values(4, 2);
2361 insert into t1 (id, warehouse_id) values(5, 1);
2363 select v2.receipt_id as alias1, v2.receipt_id as alias2 from v2
2364 order by v2.receipt_id;
2371 # Bug#16016 MIN/MAX optimization for views
2374 CREATE TABLE t1 (a int PRIMARY KEY, b int);
2375 INSERT INTO t1 VALUES (2,20), (3,10), (1,10), (0,30), (5,10);
2377 CREATE VIEW v1 AS SELECT * FROM t1;
2379 SELECT MAX(a) FROM t1;
2380 SELECT MAX(a) FROM v1;
2382 EXPLAIN SELECT MAX(a) FROM t1;
2383 EXPLAIN SELECT MAX(a) FROM v1;
2385 SELECT MIN(a) FROM t1;
2386 SELECT MIN(a) FROM v1;
2388 EXPLAIN SELECT MIN(a) FROM t1;
2389 EXPLAIN SELECT MIN(a) FROM v1;
2396 # Bug#16382 grouping name is resolved against a view column name
2397 # which coincides with a select column name
2399 CREATE TABLE t1 (x varchar(10));
2400 INSERT INTO t1 VALUES (null), ('foo'), ('bar'), (null);
2401 CREATE VIEW v1 AS SELECT * FROM t1;
2403 SELECT IF(x IS NULL, 'blank', 'not blank') FROM v1 GROUP BY x;
2404 SELECT IF(x IS NULL, 'blank', 'not blank') AS x FROM t1 GROUP BY x;
2405 SELECT IF(x IS NULL, 'blank', 'not blank') AS x FROM v1;
2406 SELECT IF(x IS NULL, 'blank', 'not blank') AS y FROM v1 GROUP BY y;
2407 SELECT IF(x IS NULL, 'blank', 'not blank') AS x FROM v1 GROUP BY x;
2414 # Bug#15943 mysql_next_result hangs on invalid SHOW CREATE VIEW
2418 drop table if exists t1;
2419 drop view if exists v1;
2420 create table t1 (id int);
2421 create view v1 as select * from t1;
2423 show create view v1;
2430 # Bug#17726 Not checked empty list caused endless loop
2432 create table t1(f1 int, f2 int);
2433 create view v1 as select ta.f1 as a, tb.f1 as b from t1 ta, t1 tb where ta.f1=tb
2434 .f1 and ta.f2=tb.f2;
2435 insert into t1 values(1,1),(2,2);
2436 create view v2 as select * from v1 where a > 1 with local check option;
2438 update v2 set b=3 where a=2;
2445 # Bug#18386 select from view over a table with ORDER BY view_col clause
2446 # given view_col is not an image of any column from the base table
2448 CREATE TABLE t1 (a int);
2449 INSERT INTO t1 VALUES (1), (2);
2451 CREATE VIEW v1 AS SELECT SQRT(a) my_sqrt FROM t1;
2453 SELECT my_sqrt FROM v1 ORDER BY my_sqrt;
2460 # Bug#18237 invalid count optimization applied to an outer join with a view
2463 CREATE TABLE t1 (id int PRIMARY KEY);
2464 CREATE TABLE t2 (id int PRIMARY KEY);
2466 INSERT INTO t1 VALUES (1), (3);
2467 INSERT INTO t2 VALUES (1), (2), (3);
2469 CREATE VIEW v2 AS SELECT * FROM t2;
2471 SELECT COUNT(*) FROM t1 LEFT JOIN t2 ON t1.id=t2.id;
2472 SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id;
2474 SELECT COUNT(*) FROM t1 LEFT JOIN v2 ON t1.id=v2.id;
2482 # Bug#16069 VIEW does return the same results as underlying SELECT
2483 # with WHERE condition containing BETWEEN over dates
2484 # Dates as strings should be casted to date type
2486 CREATE TABLE t1 (id int NOT NULL PRIMARY KEY,
2487 td date DEFAULT NULL, KEY idx(td));
2489 INSERT INTO t1 VALUES
2490 (1, '2005-01-01'), (2, '2005-01-02'), (3, '2005-01-02'),
2491 (4, '2005-01-03'), (5, '2005-01-04'), (6, '2005-01-05'),
2492 (7, '2005-01-05'), (8, '2005-01-05'), (9, '2005-01-06');
2494 CREATE VIEW v1 AS SELECT * FROM t1;
2496 SELECT * FROM t1 WHERE td BETWEEN CAST('2005.01.02' AS DATE) AND CAST('2005.01.04' AS DATE);
2497 SELECT * FROM v1 WHERE td BETWEEN CAST('2005.01.02' AS DATE) AND CAST('2005.01.04' AS DATE);
2504 # Bug#14308 Recursive view definitions
2507 create table t1 (a int);
2508 create view v1 as select * from t1;
2509 create view v2 as select * from v1;
2511 rename table v2 to t1;
2512 -- error ER_VIEW_RECURSIVE
2516 create table t1 (a int);
2518 create function f1() returns int
2521 select max(a) from t1 into mx;
2525 create view v1 as select f1() as a;
2526 create view v2 as select * from v1;
2528 rename table v2 to t1;
2529 -- error ER_SP_NO_RECURSION
2536 # Bug#15153 CONVERT_TZ() is not allowed in all places in VIEWs
2538 # Error was reported when one tried to use CONVERT_TZ() function
2539 # select list of view which was processed using MERGE algorithm.
2540 # (Also see additional test in timezone_grant.test)
2541 create table t1 (dt datetime);
2542 insert into t1 values (20040101000000), (20050101000000), (20060101000000);
2543 # Let us test that convert_tz() can be used in view's select list
2544 create view v1 as select convert_tz(dt, 'UTC', 'Europe/Moscow') as ldt from t1;
2547 # And in its where part
2548 create view v1 as select * from t1 where convert_tz(dt, 'UTC', 'Europe/Moscow') >= 20050101000000;
2550 # Other interesting case - a view which uses convert_tz() function
2551 # through other view.
2552 create view v2 as select * from v1 where dt < 20060101000000;
2555 # And even more interesting case when view uses convert_tz() both
2556 # directly and indirectly
2557 create view v2 as select convert_tz(dt, 'UTC', 'Europe/Moscow') as ldt from v1;
2564 # Bug#19490 usage of view specified by a query with GROUP BY
2565 # an expression containing non-constant interval
2567 CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, d datetime);
2570 SELECT id, date(d) + INTERVAL TIME_TO_SEC(d) SECOND AS t, COUNT(*)
2571 FROM t1 GROUP BY id, t;
2573 SHOW CREATE VIEW v1;
2581 # Bug#19077 A nested materialized view is used before being populated.
2583 CREATE TABLE t1 (i INT, j BIGINT);
2584 INSERT INTO t1 VALUES (1, 2), (2, 2), (3, 2);
2585 CREATE VIEW v1 AS SELECT MIN(j) AS j FROM t1;
2586 CREATE VIEW v2 AS SELECT MIN(i) FROM t1 WHERE j = ( SELECT * FROM v1 );
2593 # Bug#19573 VIEW with HAVING that refers an alias name
2597 fName varchar(25) NOT NULL,
2598 lName varchar(25) NOT NULL,
2600 test_date date NOT NULL,
2601 uID int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY);
2603 INSERT INTO t1(fName, lName, DOB, test_date) VALUES
2604 ('Hank', 'Hill', '1964-09-29', '2007-01-01'),
2605 ('Tom', 'Adams', '1908-02-14', '2007-01-01'),
2606 ('Homer', 'Simpson', '1968-03-05', '2007-01-01');
2609 SELECT (year(test_date)-year(DOB)) AS Age
2610 FROM t1 HAVING Age < 75;
2611 SHOW CREATE VIEW v1;
2613 SELECT (year(test_date)-year(DOB)) AS Age FROM t1 HAVING Age < 75;
2621 # Bug#19089 wrong inherited dafault values in temp table views
2624 CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, a char(6) DEFAULT 'xxx');
2625 INSERT INTO t1(id) VALUES (1), (2), (3), (4);
2626 INSERT INTO t1 VALUES (5,'yyy'), (6,'yyy');
2629 CREATE VIEW v1(a, m) AS SELECT a, MIN(id) FROM t1 GROUP BY a;
2632 CREATE TABLE t2 SELECT * FROM v1;
2633 INSERT INTO t2(m) VALUES (0);
2639 CREATE TABLE t1 (id int PRIMARY KEY, e ENUM('a','b') NOT NULL DEFAULT 'b');
2640 INSERT INTO t1(id) VALUES (1), (2), (3);
2641 INSERT INTO t1 VALUES (4,'a');
2644 CREATE VIEW v1(m, e) AS SELECT MIN(id), e FROM t1 GROUP BY e;
2645 CREATE TABLE t2 SELECT * FROM v1;
2653 # Bug#16110 insert permitted into view col w/o default value
2655 CREATE TABLE t1 (a INT NOT NULL, b INT NULL DEFAULT NULL);
2656 CREATE VIEW v1 AS SELECT a, b FROM t1;
2658 INSERT INTO v1 (b) VALUES (2);
2660 SET SQL_MODE = STRICT_ALL_TABLES;
2661 --error ER_NO_DEFAULT_FOR_VIEW_FIELD
2662 INSERT INTO v1 (b) VALUES (4);
2672 # Bug#18243 expression over a view column that with the REVERSE function
2675 CREATE TABLE t1 (firstname text, surname text);
2676 INSERT INTO t1 VALUES
2677 ("Bart","Simpson"),("Milhouse","van Houten"),("Montgomery","Burns");
2679 CREATE VIEW v1 AS SELECT CONCAT(firstname," ",surname) AS name FROM t1;
2680 SELECT CONCAT(LEFT(name,LENGTH(name)-INSTR(REVERSE(name)," ")),
2681 LEFT(name,LENGTH(name)-INSTR(REVERSE(name)," "))) AS f1
2689 # Bug#19714 wrong type of a view column specified by an expressions over ints
2692 CREATE TABLE t1 (i int, j int);
2693 CREATE VIEW v1 AS SELECT COALESCE(i,j) FROM t1;
2695 CREATE TABLE t2 SELECT COALESCE(i,j) FROM t1;
2703 # Bug#17526 views with TRIM functions
2706 CREATE TABLE t1 (s varchar(10));
2707 INSERT INTO t1 VALUES ('yadda'), ('yady');
2709 SELECT TRIM(BOTH 'y' FROM s) FROM t1;
2710 CREATE VIEW v1 AS SELECT TRIM(BOTH 'y' FROM s) FROM t1;
2714 SELECT TRIM(LEADING 'y' FROM s) FROM t1;
2715 CREATE VIEW v1 AS SELECT TRIM(LEADING 'y' FROM s) FROM t1;
2719 SELECT TRIM(TRAILING 'y' FROM s) FROM t1;
2720 CREATE VIEW v1 AS SELECT TRIM(TRAILING 'y' FROM s) FROM t1;
2728 # Bug#21080 ALTER VIEW makes user restate SQL SECURITY mode, and ALGORITHM
2730 CREATE TABLE t1 (x INT, y INT);
2731 CREATE ALGORITHM=TEMPTABLE SQL SECURITY INVOKER VIEW v1 AS SELECT x FROM t1;
2732 SHOW CREATE VIEW v1;
2734 ALTER VIEW v1 AS SELECT x, y FROM t1;
2735 SHOW CREATE VIEW v1;
2741 # Bug#21086 server crashes when VIEW defined with a SELECT with COLLATE
2744 CREATE TABLE t1 (s1 char);
2745 INSERT INTO t1 VALUES ('Z');
2747 CREATE VIEW v1 AS SELECT s1 collate latin1_german1_ci AS col FROM t1;
2749 CREATE VIEW v2 (col) AS SELECT s1 collate latin1_german1_ci FROM t1;
2751 # either of these statements will cause crash
2752 INSERT INTO v1 (col) VALUES ('b');
2753 INSERT INTO v2 (col) VALUES ('c');
2761 # Bug#11551 Asymmetric + undocumented behaviour of DROP VIEW and DROP TABLE
2763 CREATE TABLE t1 (id INT);
2764 CREATE VIEW v1 AS SELECT id FROM t1;
2767 --error ER_BAD_TABLE_ERROR
2771 CREATE VIEW v1 AS SELECT id FROM t1;
2772 --error ER_WRONG_OBJECT
2778 DROP VIEW IF EXISTS v1;
2783 # Bug#21261 Wrong access rights was required for an insert to a view
2785 CREATE DATABASE bug21261DB;
2787 connect (root,localhost,root,,bug21261DB);
2790 CREATE TABLE t1 (x INT);
2791 CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT x FROM t1;
2792 GRANT INSERT, UPDATE ON v1 TO 'user21261'@'localhost';
2793 GRANT INSERT, UPDATE ON t1 TO 'user21261'@'localhost';
2794 CREATE TABLE t2 (y INT);
2795 GRANT SELECT ON t2 TO 'user21261'@'localhost';
2797 connect (user21261, localhost, user21261,, bug21261DB);
2798 connection user21261;
2799 INSERT INTO v1 (x) VALUES (5);
2802 GRANT SELECT ON v1 TO 'user21261'@'localhost';
2803 GRANT SELECT ON t1 TO 'user21261'@'localhost';
2804 connection user21261;
2805 UPDATE v1,t2 SET x=1 WHERE x=y;
2808 REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user21261'@'localhost';
2809 DROP USER 'user21261'@'localhost';
2812 DROP DATABASE bug21261DB;
2817 disconnect user21261;
2821 # Bug#15950 NOW() optimized away in VIEWs
2823 create table t1 (f1 datetime);
2824 create view v1 as select * from t1 where f1 between now() and now() + interval 1 minute;
2825 show create view v1;
2831 # Test for Bug#16899 Possible buffer overflow in handling of DEFINER-clause.
2837 DROP TABLE IF EXISTS t1;
2838 DROP VIEW IF EXISTS v1;
2839 DROP VIEW IF EXISTS v2;
2842 CREATE TABLE t1(a INT, b INT);
2844 --error ER_WRONG_STRING_LENGTH
2845 CREATE DEFINER=1234567890abcdefGHIKL@localhost
2846 VIEW v1 AS SELECT a FROM t1;
2848 --error ER_WRONG_STRING_LENGTH
2849 CREATE DEFINER=some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY
2850 VIEW v2 AS SELECT b FROM t1;
2858 # Bug#17591 Updatable view not possible with trigger or stored function
2860 # During prelocking phase we didn't update lock type of view tables,
2861 # hence READ lock was always requested.
2864 DROP FUNCTION IF EXISTS f1;
2865 DROP FUNCTION IF EXISTS f2;
2866 DROP VIEW IF EXISTS v1, v2;
2867 DROP TABLE IF EXISTS t1;
2870 CREATE TABLE t1 (i INT);
2872 CREATE VIEW v1 AS SELECT * FROM t1;
2875 CREATE FUNCTION f1() RETURNS INT
2877 INSERT INTO v1 VALUES (0);
2884 CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t1;
2887 CREATE FUNCTION f2() RETURNS INT
2889 INSERT INTO v2 VALUES (0);
2894 --error ER_NON_INSERTABLE_TABLE
2904 # Bug#5500 wrong select_type in EXPLAIN output for queries over views
2907 CREATE TABLE t1 (s1 int);
2908 CREATE VIEW v1 AS SELECT * FROM t1;
2910 EXPLAIN SELECT * FROM t1;
2911 EXPLAIN SELECT * FROM v1;
2913 INSERT INTO t1 VALUES (1), (3), (2);
2915 EXPLAIN SELECT * FROM t1 t WHERE t.s1+1 < (SELECT MAX(t1.s1) FROM t1);
2916 EXPLAIN SELECT * FROM v1 t WHERE t.s1+1 < (SELECT MAX(t1.s1) FROM t1);
2923 # Bug#5505 Wrong error message on INSERT into a view
2925 create table t1 (s1 int);
2926 create view v1 as select s1 as a, s1 as b from t1;
2927 --error ER_NON_INSERTABLE_TABLE
2928 insert into v1 values (1,1);
2929 update v1 set a = 5;
2935 # Bug#21646 view qith a subquery in ON expression
2938 CREATE TABLE t1(pk int PRIMARY KEY);
2939 CREATE TABLE t2(pk int PRIMARY KEY, fk int, ver int, org int);
2941 CREATE ALGORITHM=MERGE VIEW v1 AS
2944 ON t2.fk = t1.pk AND
2945 t2.ver = (SELECT MAX(t.ver) FROM t2 t WHERE t.org = t2.org);
2947 SHOW CREATE VIEW v1;
2954 # Bug#19111 TRIGGERs selecting from a VIEW on the firing base table fail
2956 # Allow to select from a view on a table being modified in a trigger
2957 # and stored function, since plain select is allowed there.
2960 DROP FUNCTION IF EXISTS f1;
2961 DROP VIEW IF EXISTS v1;
2962 DROP TABLE IF EXISTS t1;
2965 CREATE TABLE t1 (i INT);
2966 INSERT INTO t1 VALUES (1);
2968 CREATE VIEW v1 AS SELECT MAX(i) FROM t1;
2970 # Plain 'SET NEW.i = (SELECT MAX(i) FROM t1) + 1' works, so select
2971 # from a view should work too.
2972 CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
2973 SET NEW.i = (SELECT * FROM v1) + 1;
2974 INSERT INTO t1 VALUES (1);
2976 # Plain 'RETURN (SELECT MAX(i) FROM t1)' works in INSERT, so select
2977 # from a view should work too.
2978 CREATE FUNCTION f1() RETURNS INT RETURN (SELECT * FROM v1);
2979 UPDATE t1 SET i= f1();
2987 # Bug#16813 (WITH CHECK OPTION doesn't work with UPDATE)
2989 CREATE TABLE t1(id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, val INT UNSIGNED NOT NULL);
2990 CREATE VIEW v1 AS SELECT id, val FROM t1 WHERE val >= 1 AND val <= 5 WITH CHECK OPTION;
2991 INSERT INTO v1 (val) VALUES (2);
2992 INSERT INTO v1 (val) VALUES (4);
2993 -- error ER_VIEW_CHECK_FAILED
2994 INSERT INTO v1 (val) VALUES (6);
2995 -- error ER_VIEW_CHECK_FAILED
2996 UPDATE v1 SET val=6 WHERE id=2;
3002 # Bug#22584 last_insert_id not updated after inserting a record
3003 # through a updatable view
3005 # We still do not update LAST_INSERT_ID if AUTO_INCREMENT column is
3006 # not accessible through a view. However, we do not reset the value
3007 # of LAST_INSERT_ID, but keep it unchanged.
3010 DROP VIEW IF EXISTS v1, v2;
3011 DROP TABLE IF EXISTS t1;
3014 CREATE TABLE t1 (i INT AUTO_INCREMENT PRIMARY KEY, j INT);
3015 CREATE VIEW v1 AS SELECT j FROM t1;
3016 CREATE VIEW v2 AS SELECT * FROM t1;
3018 INSERT INTO t1 (j) VALUES (1);
3019 SELECT LAST_INSERT_ID();
3021 INSERT INTO v1 (j) VALUES (2);
3022 --echo # LAST_INSERT_ID() should not change.
3023 SELECT LAST_INSERT_ID();
3025 INSERT INTO v2 (j) VALUES (3);
3026 --echo # LAST_INSERT_ID() should be updated.
3027 SELECT LAST_INSERT_ID();
3029 INSERT INTO v1 (j) SELECT j FROM t1;
3030 --echo # LAST_INSERT_ID() should not change.
3031 SELECT LAST_INSERT_ID();
3040 # Bug#25580 !0 as an operand in a select expression of a view
3043 CREATE VIEW v AS SELECT !0 * 5 AS x FROM DUAL;
3046 SELECT !0 * 5 AS x FROM DUAL;
3053 # Bug#24293 '\Z' token is not handled correctly in views
3057 DROP VIEW IF EXISTS v1;
3060 CREATE VIEW v1 AS SELECT 'The\ZEnd';
3063 SHOW CREATE VIEW v1;
3069 # Bug#26124 BETWEEN over a view column of the DATETIME type
3072 CREATE TABLE t1 (mydate DATETIME);
3073 INSERT INTO t1 VALUES
3074 ('2007-01-01'), ('2007-01-02'), ('2007-01-30'), ('2007-01-31');
3076 CREATE VIEW v1 AS SELECT mydate from t1;
3078 SELECT * FROM t1 WHERE mydate BETWEEN '2007-01-01' AND '2007-01-31';
3079 SELECT * FROM v1 WHERE mydate BETWEEN '2007-01-01' AND '2007-01-31';
3086 # Bug#25931 update of a multi-table view with check option
3089 CREATE TABLE t1 (a int);
3090 CREATE TABLE t2 (b int);
3091 INSERT INTO t1 VALUES (1), (2);
3092 INSERT INTO t2 VALUES (1), (2);
3095 SELECT t2.b FROM t1,t2 WHERE t1.a = t2.b WITH CHECK OPTION;
3098 --error ER_VIEW_CHECK_FAILED
3109 # Bug#12122 Views with ORDER BY can't be resolved using MERGE algorithm.
3111 create table t1(f1 int, f2 int);
3112 insert into t1 values(1,2),(1,3),(1,1),(2,3),(2,1),(2,2);
3114 create view v1 as select * from t1 order by f2;
3116 explain extended select * from v1;
3117 select * from v1 order by f1;
3118 explain extended select * from v1 order by f1;
3123 # Bug#26209 queries with GROUP BY and ORDER BY using views
3127 id int(11) NOT NULL PRIMARY KEY,
3128 country varchar(32),
3129 code int(11) default NULL
3131 INSERT INTO t1 VALUES
3132 (1,'ITALY',100),(2,'ITALY',200),(3,'FRANCE',100), (4,'ITALY',100);
3134 CREATE VIEW v1 AS SELECT * FROM t1;
3136 SELECT code, COUNT(DISTINCT country) FROM t1 GROUP BY code ORDER BY MAX(id);
3137 SELECT code, COUNT(DISTINCT country) FROM v1 GROUP BY code ORDER BY MAX(id);
3144 # Bug#25897 Some queries are no longer possible after a CREATE VIEW fails
3147 DROP VIEW IF EXISTS v1;
3150 let $query = SELECT * FROM (SELECT 1) AS t;
3153 --error ER_VIEW_SELECT_DERIVED
3154 eval CREATE VIEW v1 AS $query;
3155 --echo # Previously the following would fail.
3160 # Bug#24532 The return data type of IS TRUE is different from similar operations
3164 drop view if exists view_24532_a;
3165 drop view if exists view_24532_b;
3166 drop table if exists table_24532;
3169 create table table_24532 (
3176 create view view_24532_a as
3216 describe view_24532_a;
3218 create view view_24532_b as
3221 if(ifnull(a, 0), 1, 0) as old_istrue,
3223 if(ifnull(a, 0), 0, 1) as old_isnottrue,
3225 if(ifnull(a, 1), 0, 1) as old_isfalse,
3227 if(ifnull(a, 1), 1, 0) as old_isnotfalse
3230 describe view_24532_b;
3232 show create view view_24532_b;
3234 insert into table_24532 values (0, 0, 0, 0);
3235 select * from view_24532_b;
3236 update table_24532 set a=1;
3237 select * from view_24532_b;
3238 update table_24532 set a=NULL;
3239 select * from view_24532_b;
3241 drop view view_24532_a;
3242 drop view view_24532_b;
3243 drop table table_24532;
3247 # Bug#26560 view using subquery with a reference to an outer alias
3251 lid int NOT NULL PRIMARY KEY,
3252 name char(10) NOT NULL
3254 INSERT INTO t1 (lid, name) VALUES
3255 (1, 'YES'), (2, 'NO');
3258 id int NOT NULL PRIMARY KEY,
3263 INSERT INTO t2 (id, gid, lid, dt) VALUES
3264 (1, 1, 1, '2007-01-01'),(2, 1, 2, '2007-01-02'),
3265 (3, 2, 2, '2007-02-01'),(4, 2, 1, '2007-02-02');
3267 SELECT DISTINCT t2.gid AS lgid,
3268 (SELECT t1.name FROM t1, t2
3269 WHERE t1.lid = t2.lid AND t2.gid = lgid
3270 ORDER BY t2.dt DESC LIMIT 1
3275 SELECT DISTINCT t2.gid AS lgid,
3276 (SELECT t1.name FROM t1, t2
3277 WHERE t1.lid = t2.lid AND t2.gid = lgid
3278 ORDER BY t2.dt DESC LIMIT 1
3288 # Bug#27786 Inconsistent Operation Performing UNION On View With ORDER BY
3290 CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2),(3);
3291 CREATE VIEW v1 AS SELECT a FROM t1 ORDER BY a;
3293 SELECT * FROM t1 UNION SELECT * FROM v1;
3294 EXPLAIN SELECT * FROM t1 UNION SELECT * FROM v1;
3295 SELECT * FROM v1 UNION SELECT * FROM t1;
3296 EXPLAIN SELECT * FROM v1 UNION SELECT * FROM t1;
3297 SELECT * FROM t1 UNION SELECT * FROM v1 ORDER BY a;
3298 EXPLAIN SELECT * FROM t1 UNION SELECT * FROM v1 ORDER BY a;
3305 # Bug#27921 View ignores precision for CAST()
3307 CREATE VIEW v1 AS SELECT CAST( 1.23456789 AS DECIMAL( 7,5 ) ) AS col;
3312 CREATE VIEW v1 AS SELECT CAST(1.23456789 AS DECIMAL(8,0)) AS col;
3313 SHOW CREATE VIEW v1;
3318 # Bug#28716 CHECK OPTION expression is evaluated over expired record buffers
3319 # when VIEW is updated via temporary tables
3321 CREATE TABLE t1 (a INT);
3322 CREATE TABLE t2 (b INT, c INT DEFAULT 0);
3323 INSERT INTO t1 (a) VALUES (1), (2);
3324 INSERT INTO t2 (b) VALUES (1), (2);
3325 CREATE VIEW v1 AS SELECT t2.b,t2.c FROM t1, t2
3326 WHERE t1.a=t2.b AND t2.b < 3 WITH CHECK OPTION;
3328 UPDATE v1 SET c=1 WHERE b=1;
3335 # Bug#28561 update on multi-table view with CHECK OPTION and a subquery
3336 # in WHERE condition
3339 CREATE TABLE t1 (id int);
3340 CREATE TABLE t2 (id int, c int DEFAULT 0);
3341 INSERT INTO t1 (id) VALUES (1);
3342 INSERT INTO t2 (id) VALUES (1);
3345 SELECT t2.c FROM t1, t2
3346 WHERE t1.id=t2.id AND 1 IN (SELECT id FROM t1) WITH CHECK OPTION;
3355 # Bug#27827 CHECK OPTION ignores ON conditions when updating
3356 # a multi-table view with CHECK OPTION.
3359 CREATE TABLE t1 (a1 INT, c INT DEFAULT 0);
3360 CREATE TABLE t2 (a2 INT);
3361 CREATE TABLE t3 (a3 INT);
3362 CREATE TABLE t4 (a4 INT);
3363 INSERT INTO t1 (a1) VALUES (1),(2);
3364 INSERT INTO t2 (a2) VALUES (1),(2);
3365 INSERT INTO t3 (a3) VALUES (1),(2);
3366 INSERT INTO t4 (a4) VALUES (1),(2);
3369 SELECT t1.a1, t1.c FROM t1 JOIN t2 ON t1.a1=t2.a2 AND t1.c < 3
3372 --error ER_VIEW_CHECK_FAILED
3374 PREPARE t FROM 'UPDATE v1 SET c=3';
3375 --error ER_VIEW_CHECK_FAILED
3377 --error ER_VIEW_CHECK_FAILED
3379 --error ER_VIEW_CHECK_FAILED
3380 INSERT INTO v1(a1, c) VALUES (3, 3);
3381 UPDATE v1 SET c=1 WHERE a1=1;
3385 CREATE VIEW v2 AS SELECT t1.a1, t1.c
3386 FROM (t1 JOIN t2 ON t1.a1=t2.a2 AND t1.c < 3)
3387 JOIN (t3 JOIN t4 ON t3.a3=t4.a4)
3388 ON t2.a2=t3.a3 WITH CHECK OPTION;
3390 --error ER_VIEW_CHECK_FAILED
3392 PREPARE t FROM 'UPDATE v2 SET c=3';
3393 --error ER_VIEW_CHECK_FAILED
3395 --error ER_VIEW_CHECK_FAILED
3397 --error ER_VIEW_CHECK_FAILED
3398 INSERT INTO v2(a1, c) VALUES (3, 3);
3399 UPDATE v2 SET c=2 WHERE a1=1;
3404 DROP TABLE t1,t2,t3,t4;
3408 # Bug#29104 assertion abort for a query with a view column reference
3409 # in the GROUP BY list and a condition requiring the value
3410 # of another view column to be equal to a constant
3413 CREATE TABLE t1 (a int, b int);
3414 INSERT INTO t1 VALUES (1,2), (2,2), (1,3), (1,2);
3416 CREATE VIEW v1 AS SELECT a, b+1 as b FROM t1;
3419 SELECT b, SUM(a) FROM v1 WHERE b=3 GROUP BY b;
3420 EXPLAIN SELECT b, SUM(a) FROM v1 WHERE b=3 GROUP BY b;
3422 SELECT a, SUM(b) FROM v1 WHERE b=3 GROUP BY a;
3423 EXPLAIN SELECT a, SUM(b) FROM v1 WHERE b=3 GROUP BY a;
3425 SELECT a, SUM(b) FROM v1 WHERE a=1 GROUP BY a;
3426 EXPLAIN SELECT a, SUM(b) FROM v1 WHERE a=1 GROUP BY a;
3433 # Bug#29392 SELECT over a multi-table view with ORDER BY
3434 # selecting the same view column with two different aliases
3438 person_id int NOT NULL PRIMARY KEY,
3439 username varchar(40) default NULL,
3440 status_flg char(1) NOT NULL default 'A'
3444 person_role_id int NOT NULL auto_increment PRIMARY KEY,
3445 role_id int NOT NULL,
3446 person_id int NOT NULL,
3447 INDEX idx_person_id (person_id),
3448 INDEX idx_role_id (role_id)
3452 role_id int NOT NULL auto_increment PRIMARY KEY,
3453 role_name varchar(100) default NULL,
3454 app_name varchar(40) NOT NULL,
3455 INDEX idx_app_name(app_name)
3459 SELECT profile.person_id AS person_id
3460 FROM t1 profile, t2 userrole, t3 role
3461 WHERE userrole.person_id = profile.person_id AND
3462 role.role_id = userrole.role_id AND
3463 profile.status_flg = 'A'
3464 ORDER BY profile.person_id,role.app_name,role.role_name;
3466 INSERT INTO t1 VALUES
3467 (6,'Sw','A'), (-1136332546,'ols','e'), (0,' *\n','0'),
3468 (-717462680,'ENTS Ta','0'), (-904346964,'ndard SQL\n','0');
3469 INSERT INTO t2 VALUES
3470 (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);
3472 INSERT INTO t3 VALUES
3473 (1,'NUCANS_APP_USER','NUCANSAPP'),(2,'NUCANS_TRGAPP_USER','NUCANSAPP'),
3474 (3,'IA_INTAKE_COORDINATOR','IACANS'),(4,'IA_SCREENER','IACANS'),
3475 (5,'IA_SUPERVISOR','IACANS'),(6,'IA_READONLY','IACANS'),
3476 (7,'SOC_USER','SOCCANS'),(8,'CAYIT_USER','CAYITCANS'),
3477 (9,'RTOS_DCFSPOS_SUPERVISOR','RTOS');
3479 EXPLAIN SELECT t.person_id AS a, t.person_id AS b FROM v1 t WHERE t.person_id=6;
3480 SELECT t.person_id AS a, t.person_id AS b FROM v1 t WHERE t.person_id=6;
3483 DROP TABLE t1,t2,t3;
3487 # Bug#30020 Insufficient check led to a wrong info provided by the
3488 # information schema table.
3490 create table t1 (i int);
3491 insert into t1 values (1), (2), (1), (3), (2), (4);
3492 create view v1 as select distinct i from t1;
3494 select table_name, is_updatable from information_schema.views
3495 where table_name = 'v1';
3501 # Bug#28701 SELECTs from VIEWs completely ignore USE/FORCE KEY, allowing
3502 # invalid statements
3505 CREATE TABLE t1 (a INT);
3506 INSERT INTO t1 VALUES (1),(2);
3507 CREATE VIEW v1 AS SELECT * FROM t1;
3508 --error ER_KEY_DOES_NOT_EXITS
3509 SELECT * FROM v1 USE KEY(non_existant);
3510 --error ER_KEY_DOES_NOT_EXITS
3511 SELECT * FROM v1 FORCE KEY(non_existant);
3512 --error ER_KEY_DOES_NOT_EXITS
3513 SELECT * FROM v1 IGNORE KEY(non_existant);
3520 # Bug#28702 VIEWs defined with USE/FORCE KEY ignore that request
3522 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT, b INT NOT NULL DEFAULT 0,
3523 PRIMARY KEY(a), KEY (b));
3524 INSERT INTO t1 VALUES (),(),(),(),(),(),(),(),(),(),(),(),(),(),();
3525 CREATE VIEW v1 AS SELECT * FROM t1 FORCE KEY (PRIMARY,b) ORDER BY a;
3526 SHOW CREATE VIEW v1;
3527 EXPLAIN SELECT * FROM v1;
3528 CREATE VIEW v2 AS SELECT * FROM t1 USE KEY () ORDER BY a;
3529 SHOW CREATE VIEW v2;
3530 EXPLAIN SELECT * FROM v2;
3531 CREATE VIEW v3 AS SELECT * FROM t1 IGNORE KEY (b) ORDER BY a;
3532 SHOW CREATE VIEW v3;
3533 EXPLAIN SELECT * FROM v3;
3542 --echo # Bug#29477 Not all fields of the target table were checked to have
3543 --echo # a default value when inserting into a view.
3545 create table t1(f1 int, f2 int not null);
3546 create view v1 as select f1 from t1;
3547 insert into v1 values(1);
3548 set @old_mode=@@sql_mode;
3549 set @@sql_mode=traditional;
3550 --error ER_NO_DEFAULT_FOR_VIEW_FIELD
3551 insert into v1 values(1);
3552 set @@sql_mode=@old_mode;
3558 # Bug#33389 Selecting from a view into a table from within SP or trigger
3562 create table t1 (a int, key(a));
3563 create table t2 (c int);
3565 create view v1 as select a b from t1;
3566 create view v2 as select 1 a from t2, v1 where c in
3567 (select 1 from t1 where b = a);
3569 insert into t1 values (1), (1);
3570 insert into t2 values (1), (1);
3572 prepare stmt from "select * from v2 where a = 1";
3580 # Bug#33049 Assert while running test-as3ap test(mysql-bench suite)
3583 CREATE TABLE t1 (a INT);
3584 CREATE VIEW v1 AS SELECT p.a AS a FROM t1 p, t1 q;
3586 INSERT INTO t1 VALUES (1), (1);
3587 SELECT MAX(a), COUNT(DISTINCT a) FROM v1 GROUP BY a;
3592 ###########################################################################
3594 --echo # -----------------------------------------------------------------
3595 --echo # -- Bug#34337 Server crash when Altering a view using a table name.
3596 --echo # -----------------------------------------------------------------
3600 DROP TABLE IF EXISTS t1;
3605 CREATE TABLE t1(c1 INT);
3611 --error ER_WRONG_OBJECT
3612 ALTER ALGORITHM=TEMPTABLE SQL SECURITY INVOKER VIEW t1 (c2) AS SELECT (1);
3619 --echo # -- End of test case for Bug#34337.
3622 ###########################################################################
3624 --echo # -----------------------------------------------------------------
3625 --echo # -- Bug#35193 VIEW query is rewritten without "FROM DUAL",
3626 --echo # -- causing syntax error
3627 --echo # -----------------------------------------------------------------
3630 CREATE VIEW v1 AS SELECT 1 FROM DUAL WHERE 1;
3635 SHOW CREATE TABLE v1;
3642 --echo # -- End of test case for Bug#35193.
3645 ###########################################################################
3648 # Bug#39040 valgrind errors/crash when creating views with binlog logging
3651 # Bug is visible only when running in valgrind with binary logging.
3652 CREATE VIEW v1 AS SELECT 1;
3657 # Bug#33461 SELECT ... FROM <view> USE INDEX (...) throws an error
3660 CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT, INDEX (c2));
3661 INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
3662 SELECT * FROM t1 USE INDEX (PRIMARY) WHERE c1=2;
3663 SELECT * FROM t1 USE INDEX (c2) WHERE c2=2;
3665 CREATE VIEW v1 AS SELECT c1, c2 FROM t1;
3667 --error ER_KEY_DOES_NOT_EXITS
3668 SELECT * FROM v1 USE INDEX (PRIMARY) WHERE c1=2;
3669 --error ER_KEY_DOES_NOT_EXITS
3670 SELECT * FROM v1 FORCE INDEX (PRIMARY) WHERE c1=2;
3671 --error ER_KEY_DOES_NOT_EXITS
3672 SELECT * FROM v1 IGNORE INDEX (PRIMARY) WHERE c1=2;
3673 --error ER_KEY_DOES_NOT_EXITS
3674 SELECT * FROM v1 USE INDEX (c2) WHERE c2=2;
3675 --error ER_KEY_DOES_NOT_EXITS
3676 SELECT * FROM v1 FORCE INDEX (c2) WHERE c2=2;
3677 --error ER_KEY_DOES_NOT_EXITS
3678 SELECT * FROM v1 IGNORE INDEX (c2) WHERE c2=2;
3683 --echo # -----------------------------------------------------------------
3684 --echo # -- Bug#40825: Error 1356 while selecting from a view
3685 --echo # -- with a "HAVING" clause though query works
3686 --echo # -----------------------------------------------------------------
3689 CREATE TABLE t1 (c INT);
3693 CREATE VIEW v1 (view_column) AS SELECT c AS alias FROM t1 HAVING alias;
3694 SHOW CREATE VIEW v1;
3703 --echo # -- End of test case for Bug#40825
3706 --echo # -----------------------------------------------------------------
3707 --echo # -- End of 5.0 tests.
3708 --echo # -----------------------------------------------------------------
3711 # Bug#21370 View renaming lacks tablename_to_filename encoding
3714 DROP DATABASE IF EXISTS `d-1`;
3716 CREATE DATABASE `d-1`;
3718 CREATE TABLE `t-1` (c1 INT);
3719 CREATE VIEW `v-1` AS SELECT c1 FROM `t-1`;
3721 RENAME TABLE `t-1` TO `t-2`;
3722 RENAME TABLE `v-1` TO `v-2`;
3726 DROP DATABASE `d-1`;
3731 --echo # Bug#26676 VIEW using old table schema in a session.
3736 DROP VIEW IF EXISTS v1;
3737 DROP TABLE IF EXISTS t1;
3740 CREATE TABLE t1(c1 INT, c2 INT);
3741 INSERT INTO t1 VALUES (1, 2), (3, 4);
3749 CREATE VIEW v1 AS SELECT * FROM t1;
3757 ALTER TABLE t1 ADD COLUMN c3 INT AFTER c2;
3769 SHOW CREATE VIEW v1;
3777 --echo # End of test case for Bug#26676.
3780 ###########################################################################
3782 --echo # -----------------------------------------------------------------
3783 --echo # -- Bug#32538 View definition picks up character set, but not collation
3784 --echo # -----------------------------------------------------------------
3788 DROP VIEW IF EXISTS v1;
3793 SET collation_connection = latin1_general_ci;
3794 CREATE VIEW v1 AS SELECT _latin1 'text1' AS c1, 'text2' AS c2;
3798 SELECT COLLATION(c1), COLLATION(c2) FROM v1;
3802 SHOW CREATE VIEW v1;
3806 --error ER_CANT_AGGREGATE_2COLLATIONS
3807 SELECT * FROM v1 WHERE c1 = 'text1';
3811 SELECT * FROM v1 WHERE c2 = 'text2';
3820 SELECT COLLATION(c1), COLLATION(c2) FROM v1;
3824 SELECT * FROM v1 WHERE c1 = 'text1';
3828 --error ER_CANT_AGGREGATE_2COLLATIONS
3829 SELECT * FROM v1 WHERE c2 = 'text2';
3836 --echo # -- End of test case for Bug#32538.
3840 # Bug#34587 Creating a view inside a stored procedure leads to a server crash
3844 drop view if exists a;
3845 drop procedure if exists p;
3849 create procedure p()
3851 declare continue handler for sqlexception begin end;
3852 create view a as select 1;
3860 ###########################################################################
3864 --echo # Bug #44860: ALTER TABLE on view crashes server
3866 CREATE TABLE t1 (a INT);
3867 CREATE VIEW v1 AS SELECT a FROM t1;
3873 --echo # Bug#48449: hang on show create view after upgrading when
3874 --echo # view contains function of view
3878 DROP VIEW IF EXISTS v1,v2;
3879 DROP TABLE IF EXISTS t1,t2;
3880 DROP FUNCTION IF EXISTS f1;
3883 CREATE TABLE t1 (a INT);
3884 CREATE TABLE t2 (a INT);
3887 CREATE FUNCTION f1() RETURNS INT
3889 SELECT a FROM v2 INTO @a;
3894 --echo # Trigger pre-locking when opening v2.
3895 CREATE VIEW v1 AS SELECT f1() FROM t1;
3897 let $MYSQLD_DATADIR= `SELECT @@datadir`;
3898 copy_file std_data/bug48449.frm $MYSQLD_DATADIR/test/v2.frm;
3900 SHOW CREATE VIEW v1;
3908 # Bug#48294 assertion when creating a view based on some row() construct in select query
3910 CREATE TABLE t1(f1 INT);
3911 INSERT INTO t1 VALUES ();
3913 CREATE VIEW v1 AS SELECT 1 FROM t1 WHERE
3914 ROW(1,1) >= ROW(1, (SELECT 1 FROM t1 WHERE f1 >= ANY ( SELECT '1' )));
3920 --echo # Bug#52120 create view cause Assertion failed: 0, file .\item_subselect.cc, line 817
3922 CREATE TABLE t1 (a CHAR(1) CHARSET latin1, b CHAR(1) CHARSET utf8);
3923 CREATE VIEW v1 AS SELECT 1 from t1
3924 WHERE t1.b <=> (SELECT a FROM t1 WHERE a < SOME(SELECT '1'));
3929 --echo # Bug#57703 create view cause Assertion failed: 0, file .\item_subselect.cc, line 846
3932 CREATE TABLE t1(a int);
3933 CREATE VIEW v1 AS SELECT 1 FROM t1 GROUP BY
3934 SUBSTRING(1 FROM (SELECT 3 FROM t1 WHERE a >= ANY(SELECT 1)));
3939 --echo # Bug#57352 valgrind warnings when creating view
3941 CREATE VIEW v1 AS SELECT 1 IN (1 LIKE 2,0) AS f;
3945 --echo # Bug 11829681 - 60295: ERROR 1356 ON VIEW THAT EXECUTES FINE AS A QUERY
3948 CREATE TABLE t1 (a INT);
3949 CREATE VIEW v1 AS SELECT s.* FROM t1 s, t1 b HAVING a;
3956 --echo # -----------------------------------------------------------------
3957 --echo # -- End of 5.1 tests.
3958 --echo # -----------------------------------------------------------------