summaryrefslogtreecommitdiff
path: root/mysql-test/t/view.test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/t/view.test')
-rw-r--r--mysql-test/t/view.test330
1 files changed, 330 insertions, 0 deletions
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 0fc64e26217..f464239b81e 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -2787,6 +2787,7 @@ DROP VIEW IF EXISTS v1;
#
# Bug#21261 Wrong access rights was required for an insert to a view
#
+
CREATE DATABASE bug21261DB;
USE bug21261DB;
connect (root,localhost,root,,bug21261DB);
@@ -3991,10 +3992,339 @@ SELECT * FROM v1;
DROP VIEW v1;
DROP TABLE t1;
+--echo #
+--echo # LP BUG#777809 (a retrograded condition for view ON)
+--echo #
+
+CREATE TABLE t1 ( f1 int NOT NULL , f6 int NOT NULL ) ;
+INSERT IGNORE INTO t1 VALUES (20, 2);
+
+CREATE TABLE t2 ( f3 int NOT NULL ) ;
+INSERT IGNORE INTO t2 VALUES (7);
+
+CREATE OR REPLACE VIEW v2 AS SELECT * FROM t2;
+
+PREPARE prep_stmt FROM 'SELECT t1.f6 FROM t1 RIGHT JOIN v2 ON v2.f3 WHERE t1.f1 != 0';
+
+EXECUTE prep_stmt;
+EXECUTE prep_stmt;
+
+drop view v2;
+drop table t1,t2;
+
--echo # -----------------------------------------------------------------
--echo # -- End of 5.1 tests.
--echo # -----------------------------------------------------------------
+--echo #
+--echo # Bug #59696 Optimizer does not use equalities for conditions over view
+--echo #
+
+CREATE TABLE t1 (a int NOT NULL);
+INSERT INTO t1 VALUES
+ (9), (2), (8), (1), (3), (4), (2), (5),
+ (9), (2), (8), (1), (3), (4), (2), (5);
+
+CREATE TABLE t2 (pk int PRIMARY KEY, c int NOT NULL);
+INSERT INTO t2 VALUES
+ (9,90), (16, 160), (11,110), (1,10), (18,180), (2,20),
+ (14,140), (15, 150), (12,120), (3,30), (17,170), (19,190);
+
+EXPLAIN EXTENDED
+SELECT t1.a,t2.c FROM t1,t2 WHERE t2.pk = t1.a AND t2.pk > 8;
+FLUSH STATUS;
+SELECT t1.a,t2.c FROM t1,t2 WHERE t2.pk = t1.a AND t2.pk > 8;
+SHOW STATUS LIKE 'Handler_read_%';
+
+CREATE VIEW v AS SELECT * FROM t2;
+EXPLAIN EXTENDED
+SELECT t1.a,v.c FROM t1,v WHERE v.pk = t1.a AND v.pk > 8;
+FLUSH STATUS;
+SELECT t1.a,v.c FROM t1,v WHERE v.pk = t1.a AND v.pk > 8;
+SHOW STATUS LIKE 'Handler_read_%';
+DROP VIEW v;
+
+DROP TABLE t1, t2;
+
+--echo #
+--echo # Bug#702403: crash with multiple equalities and a view
+--echo #
+
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (10);
+
+CREATE TABLE t2 (pk int PRIMARY KEY, b int, INDEX idx (b));
+INSERT INTO t2 VALUES (1,2), (3,4);
+CREATE TABLE t3 (pk int PRIMARY KEY, b int, INDEX idx (b));
+INSERT INTO t3 VALUES (1,2), (3,4);
+
+CREATE VIEW v1 AS SELECT * FROM t1;
+
+EXPLAIN
+SELECT * FROM v1, t2, t3
+ WHERE t3.pk = v1.a AND t2.b = 1 AND t2.b = t3.pk AND v1.a BETWEEN 2 AND 5;
+
+SELECT * FROM v1, t2, t3
+ WHERE t3.pk = v1.a AND t2.b = 1 AND t2.b = t3.pk AND v1.a BETWEEN 2 AND 5;
+
+DROP VIEW v1;
+DROP TABLE t1, t2, t3;
+
+--echo #
+--echo # Bug#717577: substitution for best field in a query over a view and
+--echo # with OR in the WHERE condition
+--echo #
+
+create table t1 (a int, b int);
+insert into t1 values (2,4), (1,3);
+create table t2 (c int);
+insert into t2 values (6), (4), (1), (3), (8), (3), (4), (2);
+
+select * from t1,t2 where t2.c=t1.a and t2.c < 3 or t2.c=t1.b and t2.c >=4;
+explain extended
+select * from t1,t2 where t2.c=t1.a and t2.c < 3 or t2.c=t1.b and t2.c >=4;
+
+create view v1 as select * from t2;
+select * from t1,v1 where v1.c=t1.a and v1.c < 3 or v1.c=t1.b and v1.c >=4;
+explain extended
+select * from t1,v1 where v1.c=t1.a and v1.c < 3 or v1.c=t1.b and v1.c >=4;
+
+create view v2 as select * from v1;
+select * from t1,v2 where v2.c=t1.a and v2.c < 3 or v2.c=t1.b and v2.c >=4;
+explain extended
+select * from t1,v2 where v2.c=t1.a and v2.c < 3 or v2.c=t1.b and v2.c >=4;
+
+create view v3 as select * from t1;
+select * from v3,v2 where v2.c=v3.a and v2.c < 3 or v2.c=v3.b and v2.c >=4;
+explain extended
+select * from v3,v2 where v2.c=v3.a and v2.c < 3 or v2.c=v3.b and v2.c >=4;
+
+drop view v1,v2,v3;
+drop table t1,t2;
+
+--echo #
+--echo # Bug#724942: substitution of the constant into a view field
+--echo #
+
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (2), (9), (9), (6), (5), (4), (7);
+
+CREATE VIEW v1 AS SELECT * FROM t1;
+
+SELECT * FROM v1 WHERE a > -1 OR a > 6 AND a = 3;
+EXPLAIN EXTENDED
+SELECT * FROM v1 WHERE a > -1 OR a > 6 AND a = 3;
+
+SELECT * FROM v1 WHERE a > -1 OR a AND a = 0;
+EXPLAIN EXTENDED
+SELECT * FROM v1 WHERE a > -1 OR a AND a = 0;
+
+CREATE VIEW v2 AS SELECT * FROM v1;
+
+SELECT * FROM v2 WHERE a > -1 OR a AND a = 0;
+EXPLAIN EXTENDED
+SELECT * FROM v2 WHERE a > -1 OR a AND a = 0;
+
+DROP VIEW v1,v2;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a varchar(10), KEY (a)) ;
+INSERT INTO t1 VALUES
+ ('DD'), ('ZZ'), ('ZZ'), ('KK'), ('FF'), ('HH'),('MM');
+
+CREATE VIEW v1 AS SELECT * FROM t1;
+
+SELECT * FROM v1 WHERE a > 'JJ' OR a <> 0 AND a = 'VV';
+EXPLAIN EXTENDED
+SELECT * FROM v1 WHERE a > 'JJ' OR a <> 0 AND a = 'VV';
+
+SELECT * FROM v1 WHERE a > 'JJ' OR a AND a = 'VV';
+EXPLAIN EXTENDED
+SELECT * FROM v1 WHERE a > 'JJ' OR a AND a = 'VV';
+
+DROP VIEW v1;
+DROP TABLE t1;
+
+--echo #
+--echo # Bug#777745: crash with equality propagation
+--echo # over view fields
+--echo #
+
+CREATE TABLE t1 (a int NOT NULL ) ;
+INSERT INTO t1 VALUES (2), (1);
+
+CREATE TABLE t2 (a int NOT NULL , b int NOT NULL) ;
+INSERT INTO t2 VALUES (2,20),(2,30);
+
+CREATE VIEW v2 AS SELECT * FROM t2;
+
+EXPLAIN
+SELECT * FROM t1,v2
+ WHERE v2.a = t1.a AND v2.a = 2 AND v2.a IS NULL AND t1.a != 0;
+SELECT * FROM t1,v2
+ WHERE v2.a = t1.a AND v2.a = 2 AND v2.a IS NULL AND t1.a != 0;
+
+EXPLAIN
+SELECT * FROM t1,v2
+ WHERE v2.a = t1.a AND v2.a = 2 AND v2.a+1 > 2 AND t1.a != 0;
+SELECT * FROM t1,v2
+ WHERE v2.a = t1.a AND v2.a = 2 AND v2.a+1 > 2 AND t1.a != 0;
+
+DROP VIEW v2;
+DROP TABLE t1,t2;
+
+--echo #
+--echo # Bug#794038: crash with INSERT/UPDATE/DELETE
+--echo # over a non-updatable view
+--echo #
+
+CREATE TABLE t1 (a int);
+CREATE ALGORITHM = TEMPTABLE VIEW v1 AS SELECT * FROM t1;
+CREATE ALGORITHM = MERGE VIEW v2 AS SELECT * FROM v1;
+CREATE ALGORITHM = TEMPTABLE VIEW v3 AS SELECT * FROM v2;
+
+-- error ER_NON_INSERTABLE_TABLE
+INSERT INTO v3 VALUES (1);
+-- error ER_NON_UPDATABLE_TABLE
+UPDATE v3 SET a=0;
+-- error ER_NON_UPDATABLE_TABLE
+DELETE FROM v3;
+
+DROP VIEW v1,v2,v3;
+DROP TABLE t1;
+
+--echo #
+--echo # Bug#798621: crash with a view string field equal
+--echo # to a constant
+--echo #
+
+CREATE TABLE t1 (a varchar(32), b int) ;
+INSERT INTO t1 VALUES ('j', NULL), ('c', 8), ('c', 1);
+CREATE VIEW v1 AS SELECT * FROM t1;
+
+CREATE TABLE t2 (a varchar(32)) ;
+INSERT INTO t2 VALUES ('j'), ('c');
+
+SELECT * FROM v1 LEFT JOIN t2 ON t2.a = v1.a
+ WHERE v1.b = 1 OR v1.a = 'a' AND LENGTH(v1.a) >= v1.b;
+EXPLAIN EXTENDED
+SELECT * FROM v1 LEFT JOIN t2 ON t2.a = v1.a
+ WHERE v1.b = 1 OR v1.a = 'a' AND LENGTH(v1.a) >= v1.b;
+
+DROP VIEW v1;
+DROP TABLE t1,t2;
+
+--echo # Bug#798625: duplicate of the previous one, but without crash
+
+CREATE TABLE t1 (f1 int NOT NULL, f2 int, f3 int, f4 varchar(32), f5 int) ;
+INSERT INTO t1 VALUES (20,5,2,'r', 0);
+
+CREATE VIEW v1 AS SELECT * FROM t1;
+
+SELECT v1.f4 FROM v1
+ WHERE f1<>0 OR f2<>0 AND f4='v' AND (f2<>0 OR f3<>0 AND f5<>0 OR f4 LIKE '%b%');
+EXPLAIN EXTENDED
+SELECT v1.f4 FROM v1
+ WHERE f1<>0 OR f2<>0 AND f4='v' AND (f2<>0 OR f3<>0 AND f5<>0 OR f4 LIKE '%b%');
+
+DROP VIEW v1;
+DROP TABLE t1;
+
+--echo #
+--echo # Bug#798576: abort on a GROUP BY query over a view with left join
+--echo # that can be converted to inner join
+--echo #
+
+CREATE TABLE t1 (a int NOT NULL , b int NOT NULL) ;
+INSERT INTO t1 VALUES (214,0), (6,6), (6,0), (7,0);
+
+CREATE TABLE t2 (b int) ;
+INSERT INTO t2 VALUES (88), (78), (6);
+
+CREATE ALGORITHM=MERGE VIEW v1 AS
+ SELECT t1.a, t2.b FROM (t2 LEFT JOIN t1 ON t2.b > t1.a) WHERE t1.b <= 0;
+
+SELECT * FROM v1;
+SELECT a, MIN(b) FROM v1 GROUP BY a;
+
+DROP VIEW v1;
+DROP TABLE t1,t2;
+
+--echo #
+--echo # LP bug #793386: unexpected 'Duplicate column name ''' error
+--echo # at the second execution of a PS using a view
+--echo #
+
+CREATE TABLE t1 (f1 int, f2 int, f3 int, f4 int);
+
+CREATE VIEW v1 AS
+ SELECT t.f1, t.f2, s.f3, s.f4 FROM t1 t, t1 s
+ WHERE t.f4 >= s.f2 AND s.f3 < 0;
+
+PREPARE stmt1 FROM
+ "SELECT s.f1 AS f1, s.f2 AS f2, s.f3 AS f3, t.f4 AS f4
+ FROM v1 AS t LEFT JOIN v1 AS s ON t.f4=s.f4 WHERE t.f2 <> 1225";
+EXECUTE stmt1;
+EXECUTE stmt1;
+
+DEALLOCATE PREPARE stmt1;
+
+DROP VIEW v1;
+DROP TABLE t1;
+
+--echo #
+--echo # LP BUG#806071 (2 views with ORDER BY)
+--echo #
+
+CREATE TABLE t1 (f1 int);
+INSERT INTO t1 VALUES (1),(1);
+
+CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT f1 FROM t1;
+CREATE ALGORITHM=MERGE VIEW v2 AS SELECT f1 FROM v1 ORDER BY f1;
+
+SELECT * FROM v2 AS a1, v2 AS a2;
+EXPLAIN EXTENDED SELECT * FROM v2 AS a1, v2 AS a2;
+
+DROP VIEW v1, v2;
+DROP TABLE t1;
+
+--echo #
+--echo # LP bug #823189: dependent subquery with RIGHT JOIN
+--echo # referencing view in WHERE
+--echo #
+
+CREATE TABLE t1 (a varchar(32));
+INSERT INTO t1 VALUES ('y'), ('w');
+
+CREATE TABLE t2 (a int);
+INSERT INTO t2 VALUES (10);
+
+CREATE TABLE t3 (a varchar(32), b int);
+
+CREATE TABLE t4 (a varchar(32));
+INSERT INTO t4 VALUES ('y'), ('w');
+
+CREATE VIEW v1 AS SELECT * FROM t1;
+
+EXPLAIN EXTENDED
+SELECT * FROM t1, t2
+ WHERE t2.a NOT IN (SELECT t3.b FROM t3 RIGHT JOIN t4 ON (t4.a = t3.a)
+ WHERE t4.a >= t1.a);
+SELECT * FROM t1, t2
+ WHERE t2.a NOT IN (SELECT t3.b FROM t3 RIGHT JOIN t4 ON (t4.a = t3.a)
+ WHERE t4.a >= t1.a);
+
+EXPLAIN EXTENDED
+SELECT * FROM v1, t2
+ WHERE t2.a NOT IN (SELECT t3.b FROM t3 RIGHT JOIN t4 ON (t4.a = t3.a)
+ WHERE t4.a >= v1.a);
+SELECT * FROM v1, t2
+ WHERE t2.a NOT IN (SELECT t3.b FROM t3 RIGHT JOIN t4 ON (t4.a = t3.a)
+ WHERE t4.a >= v1.a);
+
+DROP VIEW v1;
+DROP TABLE t1,t2,t3,t4;
+
#
# Bug#9801 (Views: imperfect error message)
#
View Lorry Controller Status