From c90493749aa8736c76b5765cda0ca925864bbe37 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 2 Aug 2007 12:45:56 -0700 Subject: Fixed bug#28404. This patch adds cost estimation for the queries with ORDER BY / GROUP BY and LIMIT. If there was a ref/range access to the table whose rows were required to be ordered in the result set the optimizer always employed this access though a scan by a different index that was compatible with the required order could be cheaper to produce the first L rows of the result set. Now for such queries the optimizer makes a choice between the cheapest ref/range accesses not compatible with the given order and index scans compatible with it. mysql-test/r/distinct.result: Adjusted results for test cases affected fy the fix for bug #28404. mysql-test/r/endspace.result: Adjusted results for test cases affected fy the fix for bug #28404. mysql-test/r/group_by.result: Adjusted results for test cases affected fy the fix for bug #28404. mysql-test/r/group_min_max.result: Adjusted results for test cases affected fy the fix for bug #28404. mysql-test/r/innodb.result: Adjusted results for test cases affected fy the fix for bug #28404. mysql-test/r/innodb_mysql.result: Adjusted results for test cases affected fy the fix for bug #28404. mysql-test/r/merge.result: Adjusted results for test cases affected fy the fix for bug #28404. mysql-test/r/order_by.result: Added a test case for bug #28404. mysql-test/r/select_found.result: Adjusted results for test cases affected fy the fix for bug #28404. mysql-test/r/subselect.result: Adjusted results for test cases affected fy the fix for bug #28404. mysql-test/t/distinct.test: Changed a test case after adding the fix for bug #28404. mysql-test/t/order_by.test: Added a test case for bug #28404. sql/sql_select.cc: Fixed bug#28404. This patch adds cost estimation for the queries with ORDER BY / GROUP BY and LIMIT. Now for such queries the optimizer makes a choice between the cheapest ref/range accesses not compatible with the given order and index scans compatible with it. Modified the function test_if_skip_sort_order to make the above mentioned choice cost based. sql/sql_select.h: Fixed bug#28404. This patch adds cost estimation for the queries with ORDER BY / GROUP BY and LIMIT. Added a new field fot the JOIN_TAB structure. --- mysql-test/r/distinct.result | 8 +++--- mysql-test/r/endspace.result | 2 +- mysql-test/r/group_by.result | 2 +- mysql-test/r/group_min_max.result | 10 +++---- mysql-test/r/innodb.result | 2 +- mysql-test/r/innodb_mysql.result | 22 ++++++++-------- mysql-test/r/merge.result | 2 +- mysql-test/r/order_by.result | 55 +++++++++++++++++++++++++++++++++++++++ mysql-test/r/select_found.result | 2 +- mysql-test/r/subselect.result | 2 +- 10 files changed, 81 insertions(+), 26 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result index 002dbc6ccb5..795d8956a08 100644 --- a/mysql-test/r/distinct.result +++ b/mysql-test/r/distinct.result @@ -209,16 +209,16 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index explain SELECT distinct t1.a from t1 order by a desc limit 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index +1 SIMPLE t1 index NULL PRIMARY 4 NULL 1 Using index explain SELECT distinct a from t3 order by a desc limit 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 index NULL a 5 NULL 204 Using index +1 SIMPLE t3 index NULL a 5 NULL 40 Using index explain SELECT distinct a,b from t3 order by a+1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ALL NULL NULL NULL NULL 204 Using temporary; Using filesort -explain SELECT distinct a,b from t3 order by a limit 10; +explain SELECT distinct a,b from t3 order by a limit 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 index NULL a 5 NULL 204 Using temporary +1 SIMPLE t3 index NULL a 5 NULL 2 Using temporary explain SELECT a,b from t3 group by a,b order by a+1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ALL NULL NULL NULL NULL 204 Using temporary; Using filesort diff --git a/mysql-test/r/endspace.result b/mysql-test/r/endspace.result index 6fb33dee826..9c8d12362c4 100644 --- a/mysql-test/r/endspace.result +++ b/mysql-test/r/endspace.result @@ -154,7 +154,7 @@ teststring teststring explain select * from t1 order by text1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL key1 34 NULL 3 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort alter table t1 modify text1 char(32) binary not null; select * from t1 order by text1; text1 diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index ebe59331357..e09c66ac362 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -1144,7 +1144,7 @@ CREATE TABLE t2 (a INT, b INT, KEY(a)); INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4); EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index NULL a 5 NULL 4 +1 SIMPLE t2 index NULL a 5 NULL 2 EXPLAIN SELECT a, SUM(b) FROM t2 IGNORE INDEX (a) GROUP BY a LIMIT 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result index b6bf7260dc2..02b1459afd0 100644 --- a/mysql-test/r/group_min_max.result +++ b/mysql-test/r/group_min_max.result @@ -1963,20 +1963,20 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using temporary; Using filesort explain select a1,a2,count(a2) from t1 group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL idx_t1_1 163 NULL 128 Using index +1 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 Using index explain extended select a1,a2,count(a2) from t1 where (a1 > 'a') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 128 75.00 Using where; Using index +1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 128 75.00 Using where; Using index Warnings: Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,count(`test`.`t1`.`a2`) AS `count(a2)` from `test`.`t1` where (`test`.`t1`.`a1` > _latin1'a') group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b` explain extended select sum(ord(a1)) from t1 where (a1 > 'a') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 128 75.00 Using where; Using index +1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 128 75.00 Using where; Using index Warnings: Note 1003 select sum(ord(`test`.`t1`.`a1`)) AS `sum(ord(a1))` from `test`.`t1` where (`test`.`t1`.`a1` > _latin1'a') group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b` explain select distinct(a1) from t1 where ord(a2) = 98; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL idx_t1_1 163 NULL 128 Using where; Using index +1 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 Using where; Using index select distinct(a1) from t1 where ord(a2) = 98; a1 a @@ -2256,7 +2256,7 @@ EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1_outer index NULL a 10 NULL 15 Using where; Using index -2 DEPENDENT SUBQUERY t1 index NULL a 10 NULL 15 Using index +2 DEPENDENT SUBQUERY t1 index NULL a 10 NULL 1 Using index EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); id select_type table type possible_keys key key_len ref rows Extra diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index 804c4b81c17..dce5e1a9a35 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -947,7 +947,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 4 NULL # explain select * from t1 order by b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL b 4 NULL # +1 SIMPLE t1 ALL NULL NULL NULL NULL # Using filesort explain select * from t1 order by c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL # Using filesort diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result index 273374d016b..bf12c31326a 100644 --- a/mysql-test/r/innodb_mysql.result +++ b/mysql-test/r/innodb_mysql.result @@ -851,13 +851,13 @@ EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a; id 1 select_type SIMPLE table t1 -type range +type index possible_keys bkey -key bkey -key_len 5 +key PRIMARY +key_len 4 ref NULL -rows 16 -Extra Using where; Using index; Using filesort +rows 32 +Extra Using where; Using index SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a; a b 1 2 @@ -946,13 +946,13 @@ EXPLAIN SELECT * FROM t2 WHERE b=1 ORDER BY a; id 1 select_type SIMPLE table t2 -type ref +type index possible_keys bkey -key bkey -key_len 5 -ref const -rows 8 -Extra Using where; Using index; Using filesort +key PRIMARY +key_len 4 +ref NULL +rows 16 +Extra Using where; Using index SELECT * FROM t2 WHERE b=1 ORDER BY a; a b c 1 1 1 diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index bb4fc654b38..5aa4288500c 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -86,7 +86,7 @@ a b 19 Testing explain select a from t3 order by a desc limit 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 index NULL a 4 NULL 1131 Using index +1 SIMPLE t3 index NULL a 4 NULL 10 Using index select a from t3 order by a desc limit 10; a 699 diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 25fbeadf21b..fd3fb89b5f4 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -1073,3 +1073,58 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY,b b 5 const 1 1 SIMPLE t2 ref a a 5 const 2 Using where; Using index DROP TABLE t1,t2; +CREATE TABLE t1( +id int auto_increment PRIMARY KEY, c2 int, c3 int, INDEX k2(c2), INDEX k3(c3)); +INSERT INTO t1 (c2,c3) VALUES +(31,34),(35,38),(34,31),(32,35),(31,39), +(11,14),(15,18),(14,11),(12,15),(11,19); +INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; +INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; +INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; +INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; +INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; +INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; +INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; +INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; +INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; +INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; +INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; +INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; +SELECT COUNT(*) FROM t1; +COUNT(*) +40960 +EXPLAIN SELECT id,c3 FROM t1 WHERE c2=11 ORDER BY c3 LIMIT 20; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index k2 k3 5 NULL 88 Using where +EXPLAIN SELECT id,c3 FROM t1 WHERE c2=11 ORDER BY c3 LIMIT 100; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref k2 k2 5 const 9300 Using where; Using filesort +EXPLAIN SELECT id,c3 FROM t1 WHERE c2 BETWEEN 10 AND 12 ORDER BY c3 LIMIT 100; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index k2 k3 5 NULL 316 Using where +EXPLAIN SELECT id,c3 FROM t1 WHERE c2 BETWEEN 10 AND 12 ORDER BY c3 LIMIT 2000; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range k2 k2 5 NULL 12937 Using where; Using filesort +SELECT id,c3 FROM t1 WHERE c2=11 ORDER BY c3 LIMIT 20; +id c3 +6 14 +16 14 +26 14 +36 14 +46 14 +56 14 +66 14 +76 14 +86 14 +96 14 +106 14 +116 14 +126 14 +136 14 +146 14 +156 14 +166 14 +176 14 +186 14 +196 14 +DROP TABLE t1; diff --git a/mysql-test/r/select_found.result b/mysql-test/r/select_found.result index 7abd65beb46..7896f8a9f4e 100644 --- a/mysql-test/r/select_found.result +++ b/mysql-test/r/select_found.result @@ -84,7 +84,7 @@ UNIQUE KEY e_n (email,name) EXPLAIN SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL LIMIT 10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 system PRIMARY,kid NULL NULL NULL 0 const row not found -1 SIMPLE t2 index NULL e_n 104 NULL 200 +1 SIMPLE t2 index NULL e_n 104 NULL 10 SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL LIMIT 10; email email1 diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 40b9e489577..a25183a0e6d 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -3419,7 +3419,7 @@ EXPLAIN SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where -2 DEPENDENT SUBQUERY t1 index NULL a 8 NULL 9 Using filesort +2 DEPENDENT SUBQUERY t1 index NULL a 8 NULL 1 Using filesort DROP TABLE t1; create table t1( f1 int,f2 int); insert into t1 values (1,1),(2,2); -- cgit v1.2.1 From a53510f0be0bfdbaff6e60366d5e8cfc1d2d197f Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 3 Aug 2007 01:58:21 +0500 Subject: Fixed bug #27352. The SELECT query with more than 31 nested dependent SELECT queries returned wrong result. New error message has been added: ER_TOO_HIGH_LEVEL_OF_NESTING_FOR_SELECT. It will be reported as: "Too high level of nesting for select". sql/sql_parse.cc: Fixed bug #27352. The Item_sum::register_sum_func method has been modified to return TRUE on exceeding of allowed level of SELECT nesting and to report corresponding error message. sql/unireg.h: Fixed bug #27352. Constant definition has been added: maximal allowed level of SELECT nesting. mysql-test/t/select.test: Updated test case for bug #27352. mysql-test/r/select.result: Updated test case for bug #27352. sql/share/errmsg.txt: Fixed bug #27352. New error message has been added: ER_TOO_HIGH_LEVEL_OF_NESTING_FOR_SELECT. --- mysql-test/r/select.result | 10 ++++++++++ 1 file changed, 10 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index bfe0b9d19df..5930c36029f 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -3995,4 +3995,14 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE f1 index inx inx 10 NULL 7 Using where; Using index 1 SIMPLE f2 ref inx inx 5 test.f1.b 1 Using where; Using index DROP TABLE t1; +CREATE TABLE t1 (c1 INT, c2 INT); +INSERT INTO t1 VALUES (1,11), (2,22), (2,22); +EXPLAIN SELECT c1 FROM t1 WHERE (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT COUNT(c2)))))))))))))))))))))))))))))))) > 0; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +31 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used +32 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used +EXPLAIN SELECT c1 FROM t1 WHERE (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT COUNT(c2))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))) > 0; +ERROR HY000: Too high level of nesting for select +DROP TABLE t1; End of 5.0 tests -- cgit v1.2.1 From 6930e7dedfec2723ef6b9e38f974e3d1123d022f Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 3 Aug 2007 13:07:37 +0500 Subject: Many files: Post-merge fix. mysql-test/r/binlog_unsafe.result: Post-merge fix. mysql-test/r/events_bugs.result: Post-merge fix. mysql-test/r/events_trans.result: Post-merge fix. mysql-test/r/sp.result: Post-merge fix. mysql-test/r/sp_gis.result: Post-merge fix. mysql-test/r/xml.result: Post-merge fix. --- mysql-test/r/binlog_unsafe.result | 4 ++-- mysql-test/r/events_bugs.result | 16 ++++++++-------- mysql-test/r/events_trans.result | 2 +- mysql-test/r/sp.result | 8 ++++---- mysql-test/r/sp_gis.result | 4 ++-- mysql-test/r/xml.result | 18 +++++++++--------- 6 files changed, 26 insertions(+), 26 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/binlog_unsafe.result b/mysql-test/r/binlog_unsafe.result index 624c7feec1f..fb89631f30b 100644 --- a/mysql-test/r/binlog_unsafe.result +++ b/mysql-test/r/binlog_unsafe.result @@ -5,9 +5,9 @@ CREATE TABLE t3 (b INT AUTO_INCREMENT PRIMARY KEY); CREATE VIEW v1(a,b) AS SELECT a,b FROM t2,t3; INSERT INTO t1 SELECT UUID(); Warnings: -Warning 1589 Statement is not safe to log in statement format. +Warning 1590 Statement is not safe to log in statement format. SHOW WARNINGS; Level Warning -Code 1589 +Code 1590 Message Statement is not safe to log in statement format. DROP TABLE t1,t2,t3; diff --git a/mysql-test/r/events_bugs.result b/mysql-test/r/events_bugs.result index 557c8e0b477..6e3b1404048 100644 --- a/mysql-test/r/events_bugs.result +++ b/mysql-test/r/events_bugs.result @@ -31,7 +31,7 @@ create event e_55 on schedule at 10000101000000 do drop table t; ERROR HY000: Incorrect AT value: '10000101000000' create event e_55 on schedule at 20000101000000 do drop table t; Warnings: -Note 1585 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. +Note 1586 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. show events; Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation create event e_55 on schedule at 20200101000000 starts 10000101000000 do drop table t; @@ -457,22 +457,22 @@ CREATE EVENT e4 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' DO SELECT 1; Warnings: -Note 1585 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. +Note 1586 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. CREATE EVENT e4 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' ENDS '1999-01-02 00:00:00' DISABLE DO SELECT 1; Warnings: -Note 1585 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. +Note 1586 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. CREATE EVENT e4 ON SCHEDULE AT '1999-01-01 00:00:00' DO SELECT 1; Warnings: -Note 1585 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. +Note 1586 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. CREATE EVENT e4 ON SCHEDULE AT '1999-01-01 00:00:00' DISABLE DO SELECT 1; Warnings: -Note 1585 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. +Note 1586 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. SHOW EVENTS; Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation events_test e1 root@localhost +05:00 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci @@ -482,19 +482,19 @@ The following should succeed giving a warning. ALTER EVENT e1 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' ENDS '1999-01-02 00:00:00' ON COMPLETION PRESERVE; Warnings: -Note 1541 Event execution time is in the past. Event has been disabled +Note 1542 Event execution time is in the past. Event has been disabled CREATE EVENT e4 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' ENDS '1999-01-02 00:00:00' ON COMPLETION PRESERVE DO SELECT 1; Warnings: -Note 1541 Event execution time is in the past. Event has been disabled +Note 1542 Event execution time is in the past. Event has been disabled CREATE EVENT e5 ON SCHEDULE AT '1999-01-01 00:00:00' ON COMPLETION PRESERVE DO SELECT 1; Warnings: -Note 1541 Event execution time is in the past. Event has been disabled +Note 1542 Event execution time is in the past. Event has been disabled The following should succeed without warnings. ALTER EVENT e2 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00'; ALTER EVENT e3 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' diff --git a/mysql-test/r/events_trans.result b/mysql-test/r/events_trans.result index 1f87bcea68e..dce08d3b9f9 100644 --- a/mysql-test/r/events_trans.result +++ b/mysql-test/r/events_trans.result @@ -63,7 +63,7 @@ begin work; insert into t1 (a) values ("OK: create event if not exists"); create event if not exists e1 on schedule every 2 day do select 2; Warnings: -Note 1534 Event 'e1' already exists +Note 1535 Event 'e1' already exists rollback work; select * from t1; a diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index ebdab4a3f89..163bbb4aab4 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -5670,7 +5670,7 @@ drop function if exists pi; create function pi() returns varchar(50) return "pie, my favorite desert."; Warnings: -Note 1582 This function 'pi' has the same name as a native function +Note 1583 This function 'pi' has the same name as a native function SET @save_sql_mode=@@sql_mode; SET SQL_MODE='IGNORE_SPACE'; select pi(), pi (); @@ -5719,15 +5719,15 @@ use test; create function `database`() returns varchar(50) return "Stored function database"; Warnings: -Note 1582 This function 'database' has the same name as a native function +Note 1583 This function 'database' has the same name as a native function create function `current_user`() returns varchar(50) return "Stored function current_user"; Warnings: -Note 1582 This function 'current_user' has the same name as a native function +Note 1583 This function 'current_user' has the same name as a native function create function md5(x varchar(50)) returns varchar(50) return "Stored function md5"; Warnings: -Note 1582 This function 'md5' has the same name as a native function +Note 1583 This function 'md5' has the same name as a native function SET SQL_MODE='IGNORE_SPACE'; select database(), database (); database() database () diff --git a/mysql-test/r/sp_gis.result b/mysql-test/r/sp_gis.result index b4fe0872d64..c640e5c46f2 100644 --- a/mysql-test/r/sp_gis.result +++ b/mysql-test/r/sp_gis.result @@ -7,11 +7,11 @@ return 1; create function x() returns int return 2; Warnings: -Note 1582 This function 'x' has the same name as a native function +Note 1583 This function 'x' has the same name as a native function create function y() returns int return 3; Warnings: -Note 1582 This function 'y' has the same name as a native function +Note 1583 This function 'y' has the same name as a native function select a(); a() 1 diff --git a/mysql-test/r/xml.result b/mysql-test/r/xml.result index 95ad19a886f..aac7c4e850a 100644 --- a/mysql-test/r/xml.result +++ b/mysql-test/r/xml.result @@ -647,32 +647,32 @@ select extractValue('a','/a'); extractValue('a','/a') NULL Warnings: -Warning 1522 Incorrect XML value: 'parse error at line 1 pos 5: unexpected END-OF-INPUT' +Warning 1523 Incorrect XML value: 'parse error at line 1 pos 5: unexpected END-OF-INPUT' select extractValue('a<','/a'); extractValue('a<','/a') NULL Warnings: -Warning 1522 Incorrect XML value: 'parse error at line 1 pos 6: END-OF-INPUT unexpected (ident or '/' wanted)' +Warning 1523 Incorrect XML value: 'parse error at line 1 pos 6: END-OF-INPUT unexpected (ident or '/' wanted)' select extractValue('aaaa' wanted)' +Warning 1523 Incorrect XML value: 'parse error at line 1 pos 8: END-OF-INPUT unexpected ('>' wanted)' select extractValue('a','/a'); extractValue('a','/a') NULL Warnings: -Warning 1522 Incorrect XML value: 'parse error at line 1 pos 12: '' unexpected (END-OF-INPUT wanted)' +Warning 1523 Incorrect XML value: 'parse error at line 1 pos 12: '' unexpected (END-OF-INPUT wanted)' select extractValue('a','/a'); extractValue('a','/a') NULL Warnings: -Warning 1522 Incorrect XML value: 'parse error at line 1 pos 7: '>' unexpected (ident or string wanted)' +Warning 1523 Incorrect XML value: 'parse error at line 1 pos 7: '>' unexpected (ident or string wanted)' select extractValue('1','position()'); ERROR HY000: XPATH syntax error: '' select extractValue('1','last()'); @@ -723,17 +723,17 @@ select extractValue('<01>10:39:15<02>140','//* extractValue('<01>10:39:15<02>140','//*') NULL Warnings: -Warning 1522 Incorrect XML value: 'parse error at line 1 pos 13: unknown token unexpected (ident or '/' wanted)' +Warning 1523 Incorrect XML value: 'parse error at line 1 pos 13: unknown token unexpected (ident or '/' wanted)' select extractValue('<.>test','//*'); extractValue('<.>test','//*') NULL Warnings: -Warning 1522 Incorrect XML value: 'parse error at line 1 pos 2: unknown token unexpected (ident or '/' wanted)' +Warning 1523 Incorrect XML value: 'parse error at line 1 pos 2: unknown token unexpected (ident or '/' wanted)' select extractValue('<->test','//*'); extractValue('<->test','//*') NULL Warnings: -Warning 1522 Incorrect XML value: 'parse error at line 1 pos 2: unknown token unexpected (ident or '/' wanted)' +Warning 1523 Incorrect XML value: 'parse error at line 1 pos 2: unknown token unexpected (ident or '/' wanted)' select extractValue('<:>test','//*'); extractValue('<:>test','//*') test -- cgit v1.2.1 From ac2217cb67ede63cedf1d91b8dfd0e2174a895f8 Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 4 Aug 2007 03:12:43 -0700 Subject: Post-merge fix. --- mysql-test/r/innodb_mysql.result | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result index bf12c31326a..c66082cae44 100644 --- a/mysql-test/r/innodb_mysql.result +++ b/mysql-test/r/innodb_mysql.result @@ -857,7 +857,7 @@ key PRIMARY key_len 4 ref NULL rows 32 -Extra Using where; Using index +Extra Using where SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a; a b 1 2 -- cgit v1.2.1 From 81d07e8cecdbf1caaf4dd88fb3841aa7ad33afb3 Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 4 Aug 2007 13:41:01 -0700 Subject: Made sure that the test case for bug 28404 use the correct statistics. --- mysql-test/r/order_by.result | 14 +++++++++----- 1 file changed, 9 insertions(+), 5 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index fd3fb89b5f4..79a9bf15cbf 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -1090,21 +1090,25 @@ INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1; +UPDATE t1 SET c2=20 WHERE id%100 = 0; SELECT COUNT(*) FROM t1; COUNT(*) 40960 +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK EXPLAIN SELECT id,c3 FROM t1 WHERE c2=11 ORDER BY c3 LIMIT 20; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index k2 k3 5 NULL 88 Using where -EXPLAIN SELECT id,c3 FROM t1 WHERE c2=11 ORDER BY c3 LIMIT 100; +EXPLAIN SELECT id,c3 FROM t1 WHERE c2=11 ORDER BY c3 LIMIT 4000; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref k2 k2 5 const 9300 Using where; Using filesort -EXPLAIN SELECT id,c3 FROM t1 WHERE c2 BETWEEN 10 AND 12 ORDER BY c3 LIMIT 100; +EXPLAIN SELECT id,c3 FROM t1 WHERE c2 BETWEEN 10 AND 12 ORDER BY c3 LIMIT 20; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index k2 k3 5 NULL 316 Using where -EXPLAIN SELECT id,c3 FROM t1 WHERE c2 BETWEEN 10 AND 12 ORDER BY c3 LIMIT 2000; +1 SIMPLE t1 index k2 k3 5 NULL 63 Using where +EXPLAIN SELECT id,c3 FROM t1 WHERE c2 BETWEEN 20 AND 30 ORDER BY c3 LIMIT 4000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range k2 k2 5 NULL 12937 Using where; Using filesort +1 SIMPLE t1 range k2 k2 5 NULL 349 Using where; Using filesort SELECT id,c3 FROM t1 WHERE c2=11 ORDER BY c3 LIMIT 20; id c3 6 14 -- cgit v1.2.1 From 4c33942f5a57bb0bf1fc52ebd1c559088de14655 Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 4 Aug 2007 17:15:33 -0700 Subject: Made the test case for bug 28404 platform independent. --- mysql-test/r/order_by.result | 25 ++++++++++++------------- 1 file changed, 12 insertions(+), 13 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 79a9bf15cbf..ff4882d6cd8 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -1094,22 +1094,21 @@ UPDATE t1 SET c2=20 WHERE id%100 = 0; SELECT COUNT(*) FROM t1; COUNT(*) 40960 -ANALYZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 analyze status OK -EXPLAIN SELECT id,c3 FROM t1 WHERE c2=11 ORDER BY c3 LIMIT 20; +CREATE TABLE t2 LIKE t1; +INSERT INTO t2 SELECT * FROM t1 ORDER BY id; +EXPLAIN SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 20; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index k2 k3 5 NULL 88 Using where -EXPLAIN SELECT id,c3 FROM t1 WHERE c2=11 ORDER BY c3 LIMIT 4000; +1 SIMPLE t2 index k2 k3 5 NULL 111 Using where +EXPLAIN SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 4000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref k2 k2 5 const 9300 Using where; Using filesort -EXPLAIN SELECT id,c3 FROM t1 WHERE c2 BETWEEN 10 AND 12 ORDER BY c3 LIMIT 20; +1 SIMPLE t2 ref k2 k2 5 const 7341 Using where; Using filesort +EXPLAIN SELECT id,c3 FROM t2 WHERE c2 BETWEEN 10 AND 12 ORDER BY c3 LIMIT 20; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index k2 k3 5 NULL 63 Using where -EXPLAIN SELECT id,c3 FROM t1 WHERE c2 BETWEEN 20 AND 30 ORDER BY c3 LIMIT 4000; +1 SIMPLE t2 index k2 k3 5 NULL 73 Using where +EXPLAIN SELECT id,c3 FROM t2 WHERE c2 BETWEEN 20 AND 30 ORDER BY c3 LIMIT 4000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range k2 k2 5 NULL 349 Using where; Using filesort -SELECT id,c3 FROM t1 WHERE c2=11 ORDER BY c3 LIMIT 20; +1 SIMPLE t2 range k2 k2 5 NULL 386 Using where; Using filesort +SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 20; id c3 6 14 16 14 @@ -1131,4 +1130,4 @@ id c3 176 14 186 14 196 14 -DROP TABLE t1; +DROP TABLE t1,t2; -- cgit v1.2.1