From ef452e19bff71a66ca555146d9a7526ec0d3f092 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 28 Jul 2006 21:27:01 +0400 Subject: BUG#14940 "MySQL choose wrong index", v.2 - Make the range-et-al optimizer produce E(#table records after table condition is applied), - Make the join optimizer use this value, - Add "filtered" column to EXPLAIN EXTENDED to show fraction of records left after table condition is applied - Adjust test results, add comments mysql-test/r/archive_gis.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/auto_increment.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/bdb_gis.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/bench_count_distinct.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/binlog_stm_blackhole.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/case.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/cast.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/compress.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/ctype_collate.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/ctype_cp1250_ch.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/date_formats.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/distinct.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/fulltext.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/func_compress.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/func_crypt.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/func_default.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/func_encrypt.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/func_gconcat.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/func_group.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/func_if.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/func_in.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/func_like.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/func_math.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/func_op.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/func_regexp.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/func_set.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/func_str.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/func_system.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/func_test.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/func_time.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/gis.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/group_by.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/group_min_max.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/having.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/heap.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/heap_hash.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/index_merge.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/index_merge_innodb.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/index_merge_ror.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/innodb_gis.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/insert_update.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/join.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/join_nested.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/key_diff.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/myisam.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/ndb_gis.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/negation_elimination.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/null.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/olap.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/partition_pruning.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/query_cache.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/row.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/rpl_get_lock.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/rpl_master_pos_wait.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/select.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/ssl.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/ssl_compress.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/subselect.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/type_blob.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/union.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/varbinary.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/variables.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/r/view.result: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/t/ctype_cp1250_ch.test: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/t/func_like.test: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/t/group_min_max.test: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/t/index_merge_ror.test: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/t/index_merge_ror_cpk.test: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/t/join.test: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column mysql-test/t/partition_pruning.test: BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column sql/opt_range.cc: BUG#14940: Make range/index_merge/group-min-max optimizer produce TABLE::quick_condition_rows - estimate of #records that will match the table condition. sql/sql_class.cc: BUG#14940: Add "filtered" column to output of EXPLAIN EXTENDED sql/sql_select.cc: BUG#14940: - Make the join optimizer to use TABLE::quick_condition_rows= = E(#table records after filtering with table condition) - Add "filtered" column to output of EXPLAIN EXTENDED sql/sql_select.h: BUG#14940: Added comments sql/table.h: BUG#14940: Added comments --- mysql-test/t/partition_pruning.test | 16 ++++++++++++++++ 1 file changed, 16 insertions(+) (limited to 'mysql-test/t/partition_pruning.test') diff --git a/mysql-test/t/partition_pruning.test b/mysql-test/t/partition_pruning.test index 976466e1578..53db645adcb 100644 --- a/mysql-test/t/partition_pruning.test +++ b/mysql-test/t/partition_pruning.test @@ -407,13 +407,29 @@ explain partitions select * from t2 where a = 833; explain partitions select * from t2 where (a = 100 OR a = 900); explain partitions select * from t2 where (a > 100 AND a < 600); explain partitions select * from t2 where b = 4; + +explain extended select * from t2 where b = 6; explain partitions select * from t2 where b = 6; + +explain extended select * from t2 where b in (1,3,5); explain partitions select * from t2 where b in (1,3,5); + +explain extended select * from t2 where b in (2,4,6); explain partitions select * from t2 where b in (2,4,6); + +explain extended select * from t2 where b in (7,8,9); explain partitions select * from t2 where b in (7,8,9); + +explain extended select * from t2 where b > 5; explain partitions select * from t2 where b > 5; + +explain extended select * from t2 where b > 5 and b < 8; explain partitions select * from t2 where b > 5 and b < 8; + +explain extended select * from t2 where b > 5 and b < 7; explain partitions select * from t2 where b > 5 and b < 7; + +explain extended select * from t2 where b > 0 and b < 5; explain partitions select * from t2 where b > 0 and b < 5; flush status; -- cgit v1.2.1