From 3a54e5930dc70da39cea42464448cdda14178603 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 7 Feb 2007 00:18:36 -0800 Subject: Fixed bug #26124: SELECT from a view wrapper over a table with a column of the DATETIME type could return a wrong result set if the WHERE clause included a BETWEEN condition on the column. Fixed the method Item_func_between::fix_length_and_dec where the aggregation type for BETWEEN predicates calculated incorrectly if the first argument was a view column of the DATETIME type. mysql-test/r/view.result: Added a test case for bug #26124. mysql-test/t/view.test: Added a test case for bug #26124. --- mysql-test/t/view.test | 16 ++++++++++++++++ 1 file changed, 16 insertions(+) (limited to 'mysql-test/t/view.test') diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 47a5a54007b..bd0329ddf4f 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -2986,4 +2986,20 @@ SHOW CREATE VIEW v1; DROP VIEW v1; +# +# Bug #26124: BETWEEN over a view column of the DATETIME type +# + +CREATE TABLE t1 (mydate DATETIME); +INSERT INTO t1 VALUES + ('2007-01-01'), ('2007-01-02'), ('2007-01-30'), ('2007-01-31'); + +CREATE VIEW v1 AS SELECT mydate from t1; + +SELECT * FROM t1 WHERE mydate BETWEEN '2007-01-01' AND '2007-01-31'; +SELECT * FROM v1 WHERE mydate BETWEEN '2007-01-01' AND '2007-01-31'; + +DROP VIEW v1; +DROP TABLE t1; + --echo End of 5.0 tests. -- cgit v1.2.1 From 3609c3a4a3aaa42525d22d9de4f92c0ca7ffeeac Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 7 Feb 2007 14:41:57 -0800 Subject: Fixed bug #25931. View check option clauses were ignored for updates of multi-table views when the updates could not be performed on fly and the rows to update had to be put into temporary tables first. mysql-test/r/view.result: Added a test case for bug #25931. mysql-test/t/view.test: Added a test case for bug #25931. Adjusted another existed test case to have the correct result. sql/sql_update.cc: Fixed bug #25931. View check option clauses were ignored for updates of multi-table views when the updates could not be performed on fly and the rows to update had to be put into temporary tables first. Added the required check to multi_update::do_updates to fix the problem. --- mysql-test/t/view.test | 24 +++++++++++++++++++++++- 1 file changed, 23 insertions(+), 1 deletion(-) (limited to 'mysql-test/t/view.test') diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index bd0329ddf4f..0fa5765bb64 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -2385,7 +2385,7 @@ create table t1(f1 int, f2 int); create view v1 as select ta.f1 as a, tb.f1 as b from t1 ta, t1 tb where ta.f1=tb .f1 and ta.f2=tb.f2; insert into t1 values(1,1),(2,2); -create view v2 as select * from v1 where a > 1 with check option; +create view v2 as select * from v1 where a > 1 with local check option; select * from v2; update v2 set b=3 where a=2; select * from v2; @@ -3002,4 +3002,26 @@ SELECT * FROM v1 WHERE mydate BETWEEN '2007-01-01' AND '2007-01-31'; DROP VIEW v1; DROP TABLE t1; +# +# Bug #25931: update of a multi-table view with check option +# + +CREATE TABLE t1 (a int); +CREATE TABLE t2 (b int); +INSERT INTO t1 VALUES (1), (2); +INSERT INTO t2 VALUES (1), (2); + +CREATE VIEW v1 AS + SELECT t2.b FROM t1,t2 WHERE t1.a = t2.b WITH CHECK OPTION; + +SELECT * FROM v1; +--error 1369 +UPDATE v1 SET b=3; +SELECT * FROM v1; +SELECT * FROM t1; +SELECT * FROM t2; + +DROP VIEW v1; +DROP TABLE t1,t2; + --echo End of 5.0 tests. -- cgit v1.2.1 From 308da65162429ae0d505a3e04736e57acfea11de Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 10 Feb 2007 00:00:07 +0300 Subject: Bug#12122: The MERGE algorithm isn't applicable if the ORDER BY clause is present. A view created with CREATE VIEW ... ORDER BY ... cannot be resolved with the MERGE algorithm, even when no other part of the CREATE VIEW statement would require the view to be resolved using the TEMPTABLE algorithm. The check for presence of the ORDER BY clause in the underlying select is removed from the st_lex::can_be_merged() function. The ORDER BY list of the underlying select is appended to the ORDER BY list mysql-test/t/view.test: Added a test case for bug#12122: Views with ORDER BY can't be resolved using MERGE algorithm. mysql-test/r/view.result: Added a test case for bug#12122: Views with ORDER BY can't be resolved using MERGE algorithm. sql/sql_lex.cc: Bug#12122: Views with ORDER BY can't be resolved using MERGE algorithm. The st_lex::can_be_merged() function now allows views with the ORDER BY clause to be resolved using MERGE algorithm. The ORDER BY list of the view is appended to the ORDER BY list of the embedding select. --- mysql-test/t/view.test | 14 ++++++++++++++ 1 file changed, 14 insertions(+) (limited to 'mysql-test/t/view.test') diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 0fa5765bb64..7fdca1ff7e0 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -3024,4 +3024,18 @@ SELECT * FROM t2; DROP VIEW v1; DROP TABLE t1,t2; +# +# Bug#12122: Views with ORDER BY can't be resolved using MERGE algorithm. +# +create table t1(f1 int, f2 int); +insert into t1 values(1,2),(1,3),(1,1),(2,3),(2,1),(2,2); +select * from t1; +create view v1 as select * from t1 order by f2; +select * from v1; +explain extended select * from v1; +select * from v1 order by f1; +explain extended select * from v1 order by f1; +drop view v1; +drop table t1; + --echo End of 5.0 tests. -- cgit v1.2.1