|
After adding the assertion
|
DBUG_ASSERT(0.0 < sel && sel <= 1.0);
|
to check the validity of selectivity values returned by the function
table_cond_selectivity() the following test case from selectivity.test fails:
CREATE TABLE t1 (a varchar(16), b int, PRIMARY KEY(a), KEY(b));
|
INSERT INTO t1 VALUES
|
('USAChinese',10), ('USAEnglish',20), ('USAFrench',30);
|
|
CREATE TABLE t2 (i int);
|
INSERT INTO t2 VALUES
|
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(1),(2),(3),(4);
|
ANALYZE TABLE t1, t2;
|
|
set use_stat_tables='preferably';
|
set optimizer_use_condition_selectivity=3;
|
|
EXPLAIN EXTENDED
|
SELECT * FROM t1, t2
|
WHERE a <> 'USARussian' AND b IS NULL;
|
The added assertion also fails for the test cases:
set @@optimizer_use_condition_selectivity=2;
|
|
set names utf8;
|
|
CREATE DATABASE world;
|
|
use world;
|
|
--source include/world_schema.inc
|
|
--disable_query_log
|
--disable_result_log
|
--disable_warnings
|
--source include/world.inc
|
--enable_warnings
|
--enable_result_log
|
--enable_query_log
|
|
CREATE INDEX Name ON City(Name);
|
CREATE INDEX CountryPopulation ON City(Country,Population);
|
CREATE INDEX CountryName ON City(Country,Name);
|
|
--disable_query_log
|
--disable_result_log
|
--disable_warnings
|
ANALYZE TABLE City;
|
--enable_warnings
|
--enable_result_log
|
--enable_query_log
|
|
set @@optimizer_use_condition_selectivity=2;
|
|
EXPLAIN
|
SELECT * FROM City WHERE Country='FIN';
|
|
DROP DATABASE world;
|
|
CREATE TABLE t1 (
|
a INT,
|
b INT NOT NULL,
|
c char(100),
|
KEY (b, c),
|
KEY (b, a, c)
|
)
|
DEFAULT CHARSET = utf8;
|
|
INSERT INTO t1 VALUES
|
(1, 1, 1),
|
(2, 2, 2),
|
(3, 3, 3),
|
(4, 4, 4),
|
(5, 5, 5),
|
(6, 6, 6),
|
(7, 7, 7),
|
(8, 8, 8),
|
(9, 9, 9);
|
|
INSERT INTO t1 SELECT a + 10, b, c FROM t1;
|
INSERT INTO t1 SELECT a + 20, b, c FROM t1;
|
INSERT INTO t1 SELECT a + 40, b, c FROM t1;
|
INSERT INTO t1 SELECT a + 80, b, c FROM t1;
|
INSERT INTO t1 SELECT a + 160, b, c FROM t1;
|
INSERT INTO t1 SELECT a + 320, b, c FROM t1;
|
INSERT INTO t1 SELECT a + 640, b, c FROM t1;
|
INSERT INTO t1 SELECT a + 1280, b, c FROM t1 LIMIT 80;
|
|
EXPLAIN
|
SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9;
|
SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9;
|
|
set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
|
|
DROP TABLE t1;
|
The latter two test cases are variants of the test cases taken from range_vs_index_merge.test and order_by.test correspondingly.
They has to be run with the setting
set @@optimizer_use_condition_selectivity=2;
|
to make the added assertion fire.
|