KEY partitioning on a SET column, or column statistics on an ENUM or SET column, change the query result: a string range misses rows

Likely cause: old code in Field::can_optimize_scalar_range(), not a recent change. MySQL 5.5 to 9.1 give the same result for t1.

--source include/have_partition.inc

CREATE TABLE t1 (c SET('x','b','a')) PARTITION BY KEY (c) PARTITIONS 7;

INSERT INTO t1 VALUES (''),('x'),('x,b'),('a,b');

--let $n= query_get_value(SELECT COUNT(*) AS n FROM t1 WHERE c > 'b' AND c < 'x', n, 1)

if ($n != 1)

{

  --die KEY partitioning on a SET column: c > 'b' AND c < 'x' finds $n rows and not 1

}

CREATE TABLE t2 (e ENUM('x','b','a'));

INSERT INTO t2 VALUES ('x'),('b'),('a'),('b');

ANALYZE TABLE t2 PERSISTENT FOR ALL;

--let $n= query_get_value(SELECT COUNT(*) AS n FROM t2 WHERE e > 'a' AND e < 'x', n, 1)

if ($n != 2)

{

  --die Column statistics on an ENUM column: e > 'a' AND e < 'x' finds $n rows and not 2

}

DROP TABLE t1, t2;

Leads to:

mysqltest: At line 8: KEY partitioning on a SET column: c > 'b' AND c < 'x' finds 0 rows and not 1

With the first check removed, the testcase gives:

mysqltest: At line 12: Column statistics on an ENUM column: e > 'a' AND e < 'x' finds 0 rows and not 2

EXPLAIN gives "Impossible WHERE noticed after reading const tables" for both queries. Without partitioning and without column statistics, both find the right rows, and so does t2 with optimizer_use_condition_selectivity=1. A SET column with column statistics gives the same result, and c BETWEEN 'a,b' AND 'x,b' on t1 finds no row instead of 3. Debug builds give the same result. For t1, MySQL 5.5.62, 5.6.51, 5.7.44, 8.0.36 and 9.1.0 give the same result, also for the BETWEEN.

From the code it seems that the range analysis without a real index, used for partition pruning and for the selectivity from column statistics, builds the range from the numbers that ENUM and SET store, while the condition compares the strings. In t1, 'b' is 2 and 'x' is 1, so c > 'b' AND c < 'x' becomes the empty range 2 < c < 1, and the WHERE is taken as impossible. For a real index, Field::can_optimize_scalar_range() in sql/opt_range.cc uses no range other than equality on these columns, as Field_enum::optimize_range() is false, but that check is only done when using_real_indexes is set. fields_ok_for_partition_index() keeps ENUM out of partition pruning, but not SET.