diff options
Diffstat (limited to 'mysql-test/r/view.result')
| -rw-r--r-- | mysql-test/r/view.result | 56 |
1 files changed, 48 insertions, 8 deletions
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index c5c6b675146..0e3d650c571 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -625,7 +625,7 @@ drop table t1; create table t1 (a int, b int); create view v1 as select a, sum(b) from t1 group by a; select b from v1 use index (some_index) where b=1; -ERROR HY000: Key 'some_index' doesn't exist in table 'v1' +ERROR HY000: Incorrect usage of USE INDEX and VIEW drop view v1; drop table t1; create table t1 (col1 char(5),col2 char(5)); @@ -2706,18 +2706,19 @@ CREATE TABLE t1( fName varchar(25) NOT NULL, lName varchar(25) NOT NULL, DOB date NOT NULL, +test_date date NOT NULL, uID int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY); -INSERT INTO t1(fName, lName, DOB) VALUES -('Hank', 'Hill', '1964-09-29'), -('Tom', 'Adams', '1908-02-14'), -('Homer', 'Simpson', '1968-03-05'); +INSERT INTO t1(fName, lName, DOB, test_date) VALUES +('Hank', 'Hill', '1964-09-29', '2007-01-01'), +('Tom', 'Adams', '1908-02-14', '2007-01-01'), +('Homer', 'Simpson', '1968-03-05', '2007-01-01'); CREATE VIEW v1 AS -SELECT (year(now())-year(DOB)) AS Age +SELECT (year(test_date)-year(DOB)) AS Age FROM t1 HAVING Age < 75; SHOW CREATE VIEW v1; View Create View -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select (year(now()) - year(`t1`.`DOB`)) AS `Age` from `t1` having (`Age` < 75) -SELECT (year(now())-year(DOB)) AS Age FROM t1 HAVING Age < 75; +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select (year(`t1`.`test_date`) - year(`t1`.`DOB`)) AS `Age` from `t1` having (`Age` < 75) +SELECT (year(test_date)-year(DOB)) AS Age FROM t1 HAVING Age < 75; Age 43 39 @@ -3562,4 +3563,43 @@ table_name is_updatable v1 NO drop view v1; drop table t1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +CREATE VIEW v1 AS SELECT * FROM t1; +SELECT * FROM v1 USE KEY(non_existant); +ERROR HY000: Incorrect usage of USE INDEX and VIEW +SELECT * FROM v1 FORCE KEY(non_existant); +ERROR HY000: Incorrect usage of FORCE INDEX and VIEW +SELECT * FROM v1 IGNORE KEY(non_existant); +ERROR HY000: Incorrect usage of IGNORE INDEX and VIEW +DROP VIEW v1; +DROP TABLE t1; +CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT, b INT NOT NULL DEFAULT 0, +PRIMARY KEY(a), KEY (b)); +INSERT INTO t1 VALUES (),(),(),(),(),(),(),(),(),(),(),(),(),(),(); +CREATE VIEW v1 AS SELECT * FROM t1 FORCE KEY (PRIMARY,b) ORDER BY a; +SHOW CREATE VIEW v1; +View Create View +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` FORCE INDEX (PRIMARY,`b`) order by `t1`.`a` +EXPLAIN SELECT * FROM v1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL PRIMARY 4 NULL 15 +CREATE VIEW v2 AS SELECT * FROM t1 USE KEY () ORDER BY a; +SHOW CREATE VIEW v2; +View Create View +v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` USE INDEX () order by `t1`.`a` +EXPLAIN SELECT * FROM v2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 15 Using filesort +CREATE VIEW v3 AS SELECT * FROM t1 IGNORE KEY (b) ORDER BY a; +SHOW CREATE VIEW v3; +View Create View +v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` IGNORE INDEX (`b`) order by `t1`.`a` +EXPLAIN SELECT * FROM v3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 15 Using filesort +DROP VIEW v1; +DROP VIEW v2; +DROP VIEW v3; +DROP TABLE t1; End of 5.0 tests. |