Details
Description
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.
Attachments
Issue Links
- blocks
-
MDEV-20519 Query plan regression with optimizer_use_condition_selectivity > 1
- Closed
- relates to
-
MDEV-20519 Query plan regression with optimizer_use_condition_selectivity > 1
- Closed
-
MDEV-20595 Assertion `0 < sel && sel <= 2.0' failed in table_cond_selectivity
- Stalled