848
848
create view v1 as select * from t1;
849
849
create view v2 as select * from v1;
850
850
create view v3 as select v2.col1 from v2,t2 where v2.col1 = t2.col1;
851
-- error ER_VIEW_PREVENT_UPDATE
851
852
update v2 set col1 = (select max(col1) from v1);
853
-- error ER_VIEW_PREVENT_UPDATE
852
854
update v2 set col1 = (select max(col1) from t1);
855
-- error ER_UPDATE_TABLE_USED
853
856
update v2 set col1 = (select max(col1) from v2);
857
-- error ER_VIEW_PREVENT_UPDATE
854
858
update v2,t2 set v2.col1 = (select max(col1) from v1) where v2.col1 = t2.col1;
859
-- error ER_VIEW_PREVENT_UPDATE
855
860
update t1,t2 set t1.col1 = (select max(col1) from v1) where t1.col1 = t2.col1;
861
-- error ER_UPDATE_TABLE_USED
856
862
update v1,t2 set v1.col1 = (select max(col1) from v1) where v1.col1 = t2.col1;
863
-- error ER_VIEW_PREVENT_UPDATE
857
864
update t2,v2 set v2.col1 = (select max(col1) from v1) where v2.col1 = t2.col1;
865
-- error ER_VIEW_PREVENT_UPDATE
858
866
update t2,t1 set t1.col1 = (select max(col1) from v1) where t1.col1 = t2.col1;
867
-- error ER_VIEW_PREVENT_UPDATE
859
868
update t2,v1 set v1.col1 = (select max(col1) from v1) where v1.col1 = t2.col1;
869
-- error ER_VIEW_PREVENT_UPDATE
860
870
update v2,t2 set v2.col1 = (select max(col1) from t1) where v2.col1 = t2.col1;
871
-- error ER_UPDATE_TABLE_USED
861
872
update t1,t2 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1;
873
-- error ER_VIEW_PREVENT_UPDATE
862
874
update v1,t2 set v1.col1 = (select max(col1) from t1) where v1.col1 = t2.col1;
875
-- error ER_UPDATE_TABLE_USED
863
876
update t2,v2 set v2.col1 = (select max(col1) from t1) where v2.col1 = t2.col1;
877
-- error ER_UPDATE_TABLE_USED
864
878
update t2,t1 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1;
879
-- error ER_UPDATE_TABLE_USED
865
880
update t2,v1 set v1.col1 = (select max(col1) from t1) where v1.col1 = t2.col1;
881
-- error ER_UPDATE_TABLE_USED
866
882
update v2,t2 set v2.col1 = (select max(col1) from v2) where v2.col1 = t2.col1;
883
-- error ER_VIEW_PREVENT_UPDATE
867
884
update t1,t2 set t1.col1 = (select max(col1) from v2) where t1.col1 = t2.col1;
885
-- error ER_VIEW_PREVENT_UPDATE
868
886
update v1,t2 set v1.col1 = (select max(col1) from v2) where v1.col1 = t2.col1;
887
-- error ER_VIEW_PREVENT_UPDATE
869
888
update t2,v2 set v2.col1 = (select max(col1) from v2) where v2.col1 = t2.col1;
889
-- error ER_VIEW_PREVENT_UPDATE
870
890
update t2,t1 set t1.col1 = (select max(col1) from v2) where t1.col1 = t2.col1;
891
-- error ER_VIEW_PREVENT_UPDATE
871
892
update t2,v1 set v1.col1 = (select max(col1) from v2) where v1.col1 = t2.col1;
893
-- error ER_VIEW_PREVENT_UPDATE
872
894
update v3 set v3.col1 = (select max(col1) from v1);
895
-- error ER_VIEW_PREVENT_UPDATE
873
896
update v3 set v3.col1 = (select max(col1) from t1);
897
-- error ER_VIEW_PREVENT_UPDATE
874
898
update v3 set v3.col1 = (select max(col1) from v2);
899
-- error ER_UPDATE_TABLE_USED
875
900
update v3 set v3.col1 = (select max(col1) from v3);
901
-- error ER_VIEW_PREVENT_UPDATE
876
902
delete from v2 where col1 = (select max(col1) from v1);
903
-- error ER_VIEW_PREVENT_UPDATE
877
904
delete from v2 where col1 = (select max(col1) from t1);
905
-- error ER_UPDATE_TABLE_USED
878
906
delete from v2 where col1 = (select max(col1) from v2);
907
-- error ER_VIEW_PREVENT_UPDATE
879
908
delete v2 from v2,t2 where (select max(col1) from v1) > 0 and v2.col1 = t2.col1;
909
-- error ER_VIEW_PREVENT_UPDATE
880
910
delete t1 from t1,t2 where (select max(col1) from v1) > 0 and t1.col1 = t2.col1;
911
-- error ER_UPDATE_TABLE_USED
881
912
delete v1 from v1,t2 where (select max(col1) from v1) > 0 and v1.col1 = t2.col1;
913
-- error ER_VIEW_PREVENT_UPDATE
882
914
delete v2 from v2,t2 where (select max(col1) from t1) > 0 and v2.col1 = t2.col1;
915
-- error ER_UPDATE_TABLE_USED
883
916
delete t1 from t1,t2 where (select max(col1) from t1) > 0 and t1.col1 = t2.col1;
917
-- error ER_VIEW_PREVENT_UPDATE
884
918
delete v1 from v1,t2 where (select max(col1) from t1) > 0 and v1.col1 = t2.col1;
919
-- error ER_UPDATE_TABLE_USED
885
920
delete v2 from v2,t2 where (select max(col1) from v2) > 0 and v2.col1 = t2.col1;
921
-- error ER_VIEW_PREVENT_UPDATE
886
922
delete t1 from t1,t2 where (select max(col1) from v2) > 0 and t1.col1 = t2.col1;
923
-- error ER_VIEW_PREVENT_UPDATE
887
924
delete v1 from v1,t2 where (select max(col1) from v2) > 0 and v1.col1 = t2.col1;
925
-- error ER_VIEW_PREVENT_UPDATE
888
926
insert into v2 values ((select max(col1) from v1));
927
-- error ER_VIEW_PREVENT_UPDATE
889
928
insert into t1 values ((select max(col1) from v1));
929
-- error ER_VIEW_PREVENT_UPDATE
890
930
insert into v2 values ((select max(col1) from v1));
931
-- error ER_VIEW_PREVENT_UPDATE
891
932
insert into v2 values ((select max(col1) from t1));
933
-- error ER_UPDATE_TABLE_USED
892
934
insert into t1 values ((select max(col1) from t1));
935
-- error ER_VIEW_PREVENT_UPDATE
893
936
insert into v2 values ((select max(col1) from t1));
937
-- error ER_UPDATE_TABLE_USED
894
938
insert into v2 values ((select max(col1) from v2));
939
-- error ER_VIEW_PREVENT_UPDATE
895
940
insert into t1 values ((select max(col1) from v2));
941
-- error ER_UPDATE_TABLE_USED
896
942
insert into v2 values ((select max(col1) from v2));
943
-- error ER_VIEW_PREVENT_UPDATE
897
944
insert into v3 (col1) values ((select max(col1) from v1));
945
-- error ER_VIEW_PREVENT_UPDATE
898
946
insert into v3 (col1) values ((select max(col1) from t1));
947
-- error ER_VIEW_PREVENT_UPDATE
899
948
insert into v3 (col1) values ((select max(col1) from v2));
900
#check with TZ tables in list
949
# check with TZ tables in list
950
-- error ER_VIEW_PREVENT_UPDATE
901
951
insert into v3 (col1) values ((select CONVERT_TZ('20050101000000','UTC','MET') from v2));
902
952
insert into v3 (col1) values ((select CONVERT_TZ('20050101000000','UTC','MET') from t2));
953
-- error ER_BAD_NULL_ERROR
903
954
insert into t3 values ((select CONVERT_TZ('20050101000000','UTC','MET') from t2));
904
955
# temporary table algorithm view should be equal to subquery in the from clause
905
956
create algorithm=temptable view v4 as select * from t1;
1240
1240
# variable length fields
1241
1241
create table t1 (a text, b text);
1242
1242
create view v1 as select * from t1 where a <> 'Field A' with check option;
1243
load data infile '../std_data_ln/loaddata2.dat' into table v1 fields terminated by ',' enclosed by '''';
1243
-- error ER_VIEW_CHECK_FAILED
1244
load data infile '../../std_data/loaddata2.dat' into table v1 fields terminated by ',' enclosed by '''';
1244
1245
select concat('|',a,'|'), concat('|',b,'|') from t1;
1245
1246
select concat('|',a,'|'), concat('|',b,'|') from v1;
1246
1247
delete from t1;
1247
load data infile '../std_data_ln/loaddata2.dat' ignore into table v1 fields terminated by ',' enclosed by '''';
1248
load data infile '../../std_data/loaddata2.dat' ignore into table v1 fields terminated by ',' enclosed by '''';
1248
1249
select concat('|',a,'|'), concat('|',b,'|') from t1;
1249
1250
select concat('|',a,'|'), concat('|',b,'|') from v1;
1431
1431
drop table t1, t2;
1434
# view and group_concat() (BUG#7116)
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);
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
1438
create view v1 as select * from t1;
1439
1439
select col1,group_concat(col2,col3) from t1 group by col1;
1440
1440
select col1,group_concat(col2,col3) from v1 group by col1;
1481
1481
drop table t1,t2,t3;
1484
# Test for BUG #6106: query over a view using subquery for the underlying table
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');
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
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
1508
CREATE VIEW v1 AS SELECT * FROM t1;
1510
SELECT a.col1,a.col2,b.col2,b.col3
1511
1511
FROM t1 a LEFT JOIN t2 b ON a.col1=b.col1
1512
WHERE b.col2 IS NULL OR
1512
WHERE b.col2 IS NULL OR
1513
1513
b.col2=(SELECT MAX(col2) FROM t2 b WHERE b.col1=a.col1);
1515
SELECT a.col1,a.col2,b.col2,b.col3
1515
SELECT a.col1,a.col2,b.col2,b.col3
1516
1516
FROM v1 a LEFT JOIN t2 b ON a.col1=b.col1
1517
WHERE b.col2 IS NULL OR
1517
WHERE b.col2 IS NULL OR
1518
1518
b.col2=(SELECT MAX(col2) FROM t2 b WHERE b.col1=a.col1);
1520
CREATE VIEW v2 AS SELECT * FROM t2;
1520
CREATE VIEW v2 AS SELECT * FROM t2;
1522
1522
SELECT a.col1,a.col2,b.col2,b.col3
1523
1523
FROM v2 b RIGHT JOIN v1 a ON a.col1=b.col1
1524
1524
WHERE b.col2 IS NULL OR
1525
b.col2=(SELECT MAX(col2) FROM v2 b WHERE b.col1=a.col1);
1525
b.col2=(SELECT MAX(col2) FROM v2 b WHERE b.col1=a.col1);
1527
# Tests from the report for bug #6107
1527
# Tests from the report for Bug#6107
1529
1529
SELECT a.col1,a.col2,b.col2,b.col3
1530
1530
FROM v2 b RIGHT JOIN v1 a ON a.col1=b.col1
1531
1531
WHERE a.col1 IN (1,5,9) AND
1532
1532
(b.col2 IS NULL OR
1533
b.col2=(SELECT MAX(col2) FROM v2 b WHERE b.col1=a.col1));
1533
b.col2=(SELECT MAX(col2) FROM v2 b WHERE b.col1=a.col1));
1535
1535
CREATE VIEW v3 AS SELECT * FROM t1 WHERE col1 IN (1,5,9);
1537
1537
SELECT a.col1,a.col2,b.col2,b.col3
1538
1538
FROM v2 b RIGHT JOIN v3 a ON a.col1=b.col1
1539
1539
WHERE b.col2 IS NULL OR
1540
b.col2=(SELECT MAX(col2) FROM v2 b WHERE b.col1=a.col1);
1540
b.col2=(SELECT MAX(col2) FROM v2 b WHERE b.col1=a.col1);
1542
1542
DROP VIEW v1,v2,v3;
1543
1543
DROP TABLE t1,t2;
1546
# BUG#8490 Select from views containing subqueries causes server to hang
1546
# Bug#8490 Select from views containing subqueries causes server to hang
1549
1549
create table t1 as select 1 A union select 2 union select 3;
1550
1550
create table t2 as select * from t1;
1551
1551
create view v1 as select * from t1 where a in (select * from t2);
1552
1552
select * from v1 A, v1 B where A.a = B.a;
1553
1553
create table t3 as select a a,a b from t2;
1554
create view v2 as select * from t3 where
1554
create view v2 as select * from t3 where
1555
1555
a in (select * from t1) or b in (select * from t2);
1556
1556
select * from v2 A, v2 B where A.a = B.b;
1557
1557
drop view v1, v2;
1558
1558
drop table t1, t2, t3;
1561
# Test case for bug #8528: select from view over multi-table view
1561
# Test case for Bug#8528 select from view over multi-table view
1563
1563
CREATE TABLE t1 (a int);
1564
1564
CREATE TABLE t2 (b int);
2848
2913
INSERT INTO t1 VALUES (1), (3), (2);
2850
EXPLAIN SELECT * FROM t1 t WHERE t.s1+1 < (SELECT MAX(t1.s1) FROM t1);
2851
EXPLAIN SELECT * FROM v1 t WHERE t.s1+1 < (SELECT MAX(t1.s1) FROM t1);
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);
2857
# Bug #5505: Wrong error message on INSERT into a view
2923
# Bug#5505 Wrong error message on INSERT into a view
2859
2925
create table t1 (s1 int);
2860
2926
create view v1 as select s1 as a, s1 as b from t1;
2861
2927
--error ER_NON_INSERTABLE_TABLE
2862
insert into v1 values (1,1);
2928
insert into v1 values (1,1);
2863
2929
update v1 set a = 5;
2868
# Bug #21646: view qith a subquery in ON expression
2871
CREATE TABLE t1(pk int PRIMARY KEY);
2935
# Bug#21646 view qith a subquery in ON expression
2938
CREATE TABLE t1(pk int PRIMARY KEY);
2872
2939
CREATE TABLE t2(pk int PRIMARY KEY, fk int, ver int, org int);
2874
CREATE ALGORITHM=MERGE VIEW v1 AS
2941
CREATE ALGORITHM=MERGE VIEW v1 AS
2877
ON t2.fk = t1.pk AND
2944
ON t2.fk = t1.pk AND
2878
2945
t2.ver = (SELECT MAX(t.ver) FROM t2 t WHERE t.org = t2.org);
2880
2947
SHOW CREATE VIEW v1;
3387
3469
INSERT INTO t2 VALUES
3388
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);
3390
INSERT INTO t3 VALUES
3472
INSERT INTO t3 VALUES
3391
3473
(1,'NUCANS_APP_USER','NUCANSAPP'),(2,'NUCANS_TRGAPP_USER','NUCANSAPP'),
3392
3474
(3,'IA_INTAKE_COORDINATOR','IACANS'),(4,'IA_SCREENER','IACANS'),
3393
3475
(5,'IA_SUPERVISOR','IACANS'),(6,'IA_READONLY','IACANS'),
3394
3476
(7,'SOC_USER','SOCCANS'),(8,'CAYIT_USER','CAYITCANS'),
3395
3477
(9,'RTOS_DCFSPOS_SUPERVISOR','RTOS');
3397
3479
EXPLAIN SELECT t.person_id AS a, t.person_id AS b FROM v1 t WHERE t.person_id=6;
3398
3480
SELECT t.person_id AS a, t.person_id AS b FROM v1 t WHERE t.person_id=6;
3401
3483
DROP TABLE t1,t2,t3;
3404
# Bug#30020: Insufficient check led to a wrong info provided by the
3405
# information schema table.
3487
# Bug#30020 Insufficient check led to a wrong info provided by the
3488
# information schema table.
3407
3490
create table t1 (i int);
3408
3491
insert into t1 values (1), (2), (1), (3), (2), (4);
3409
3492
create view v1 as select distinct i from t1;
3410
3493
select * from v1;
3411
select table_name, is_updatable from information_schema.views
3494
select table_name, is_updatable from information_schema.views
3412
3495
where table_name = 'v1';
3417
# Bug #28701: SELECTs from VIEWs completely ignore USE/FORCE KEY, allowing
3418
# invalid statements
3501
# Bug#28701 SELECTs from VIEWs completely ignore USE/FORCE KEY, allowing
3502
# invalid statements
3421
3505
CREATE TABLE t1 (a INT);