[MDEV-15310] Range optimizer does not work well for "WHERE temporal_column NOT IN (const_list)" - Jira

Details

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
Fix Version/s: 10.4(EOL)
Component/s: Data types, Optimizer, Temporal Types
Labels:
None

Epic Link:
Data type cleanups

Description

I create a table with a TIME column and populate it with some data, and run an EXPLAIN for a SELECT with a ``WHERE a NOT IN()'' condition:

CREATE OR REPLACE TABLE t1 (a TIME, filler CHAR(200), KEY(a));

INSERT INTO t1 VALUES ('23:00:01', 'no');

INSERT INTO t1 VALUES ('23:00:01', 'no');

INSERT INTO t1 VALUES ('23:00:01', 'no');

INSERT INTO t1 VALUES ('23:00:01', 'no');

INSERT INTO t1 VALUES ('23:00:01', 'no');

INSERT INTO t1 VALUES ('23:00:01', 'no');

INSERT INTO t1 VALUES ('23:00:01', 'no');

INSERT INTO t1 VALUES ('23:00:01', 'no');

INSERT INTO t1 VALUES ('23:00:01', 'no');

INSERT INTO t1 VALUES ('23:00:01', 'no');

INSERT INTO t1 VALUES ('23:00:01', 'no');

INSERT INTO t1 VALUES ('23:00:01', 'no');

INSERT INTO t1 VALUES ('23:00:01', 'no');

INSERT INTO t1 VALUES ('23:00:01', 'no');

INSERT INTO t1 VALUES ('23:00:01', 'no');

INSERT INTO t1 VALUES ('23:00:01', 'no');

INSERT INTO t1 VALUES ('23:00:01', 'no');

INSERT INTO t1 VALUES ('23:00:01', 'no');

INSERT INTO t1 VALUES ('23:00:01', 'no');

INSERT INTO t1 VALUES ('23:00:01', 'no');

INSERT INTO t1 VALUES ('23:00:01', 'no');

INSERT INTO t1 VALUES ('23:00:01', 'no');

INSERT INTO t1 VALUES ('23:00:01', 'no');

INSERT INTO t1 VALUES ('23:00:01', 'no');

INSERT INTO t1 VALUES ('23:00:02', 'no');

INSERT INTO t1 VALUES ('23:00:03', 'yes');

INSERT INTO t1 VALUES ('23:00:04', 'yes');

EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('23:00:01','23:00:02');

DROP TABLE t1;

+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+

| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |

+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+

|    1 | SIMPLE      | t1    | range | a             | a    | 4       | NULL |    4 | Using index condition |

+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+

Notice, the condition covers only two records. The optimizer correctly choses to use the range search.

Now I do the same for negative TIME values:

CREATE OR REPLACE TABLE t1 (a TIME, filler CHAR(200), KEY(a));

INSERT INTO t1 VALUES ('-23:00:01', 'no');

INSERT INTO t1 VALUES ('-23:00:01', 'no');

INSERT INTO t1 VALUES ('-23:00:01', 'no');

INSERT INTO t1 VALUES ('-23:00:01', 'no');

INSERT INTO t1 VALUES ('-23:00:01', 'no');

INSERT INTO t1 VALUES ('-23:00:01', 'no');

INSERT INTO t1 VALUES ('-23:00:01', 'no');

INSERT INTO t1 VALUES ('-23:00:01', 'no');

INSERT INTO t1 VALUES ('-23:00:01', 'no');

INSERT INTO t1 VALUES ('-23:00:01', 'no');

INSERT INTO t1 VALUES ('-23:00:01', 'no');

INSERT INTO t1 VALUES ('-23:00:01', 'no');

INSERT INTO t1 VALUES ('-23:00:01', 'no');

INSERT INTO t1 VALUES ('-23:00:01', 'no');

INSERT INTO t1 VALUES ('-23:00:01', 'no');

INSERT INTO t1 VALUES ('-23:00:01', 'no');

INSERT INTO t1 VALUES ('-23:00:01', 'no');

INSERT INTO t1 VALUES ('-23:00:01', 'no');

INSERT INTO t1 VALUES ('-23:00:01', 'no');

INSERT INTO t1 VALUES ('-23:00:01', 'no');

INSERT INTO t1 VALUES ('-23:00:01', 'no');

INSERT INTO t1 VALUES ('-23:00:01', 'no');

INSERT INTO t1 VALUES ('-23:00:01', 'no');

INSERT INTO t1 VALUES ('-23:00:01', 'no');

INSERT INTO t1 VALUES ('-23:00:02', 'no');

INSERT INTO t1 VALUES ('-23:00:03', 'yes');

INSERT INTO t1 VALUES ('-23:00:04', 'yes');

EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('-23:00:01','-23:00:02');

DROP TABLE t1;

+------+-------------+-------+------+---------------+------+---------+------+------+-------------+

| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+------+-------------+-------+------+---------------+------+---------+------+------+-------------+

|    1 | SIMPLE      | t1    | ALL  | a             | NULL | NULL    | NULL |   27 | Using where |

+------+-------------+-------+------+---------------+------+---------+------+------+-------------+

As in the first example, the condition matches only two records again, but the range search does not work any more and it goes through the full table scan.

Now I do the same for positive time values, but outside of the time-of-the-day range, greater than 23:59:59.

CREATE OR REPLACE TABLE t1 (a TIME, filler CHAR(200), KEY(a));

INSERT INTO t1 VALUES ('24:00:01', 'no');

INSERT INTO t1 VALUES ('24:00:01', 'no');

INSERT INTO t1 VALUES ('24:00:01', 'no');

INSERT INTO t1 VALUES ('24:00:01', 'no');

INSERT INTO t1 VALUES ('24:00:01', 'no');

INSERT INTO t1 VALUES ('24:00:01', 'no');

INSERT INTO t1 VALUES ('24:00:01', 'no');

INSERT INTO t1 VALUES ('24:00:01', 'no');

INSERT INTO t1 VALUES ('24:00:01', 'no');

INSERT INTO t1 VALUES ('24:00:01', 'no');

INSERT INTO t1 VALUES ('24:00:01', 'no');

INSERT INTO t1 VALUES ('24:00:01', 'no');

INSERT INTO t1 VALUES ('24:00:01', 'no');

INSERT INTO t1 VALUES ('24:00:01', 'no');

INSERT INTO t1 VALUES ('24:00:01', 'no');

INSERT INTO t1 VALUES ('24:00:01', 'no');

INSERT INTO t1 VALUES ('24:00:01', 'no');

INSERT INTO t1 VALUES ('24:00:01', 'no');

INSERT INTO t1 VALUES ('24:00:01', 'no');

INSERT INTO t1 VALUES ('24:00:01', 'no');

INSERT INTO t1 VALUES ('24:00:01', 'no');

INSERT INTO t1 VALUES ('24:00:01', 'no');

INSERT INTO t1 VALUES ('24:00:01', 'no');

INSERT INTO t1 VALUES ('24:00:01', 'no');

INSERT INTO t1 VALUES ('24:00:02', 'no');

INSERT INTO t1 VALUES ('24:00:03', 'yes');

INSERT INTO t1 VALUES ('24:00:04', 'yes');

EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('24:00:01','24:00:02');

DROP TABLE t1;

+------+-------------+-------+------+---------------+------+---------+------+------+-------------+

| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+------+-------------+-------+------+---------------+------+---------+------+------+-------------+

|    1 | SIMPLE      | t1    | ALL  | a             | NULL | NULL    | NULL |   27 | Using where |

+------+-------------+-------+------+---------------+------+---------+------+------+-------------+

It erroneously uses the full table scan again.

Now I do the same for a TIME(1) column with positive fractional data, within the time-of-the-day range:

CREATE OR REPLACE TABLE t1 (a TIME(1), filler CHAR(200), KEY(a));

INSERT INTO t1 VALUES ('23:00:01.1', 'no');

INSERT INTO t1 VALUES ('23:00:01.1', 'no');

INSERT INTO t1 VALUES ('23:00:01.1', 'no');

INSERT INTO t1 VALUES ('23:00:01.1', 'no');

INSERT INTO t1 VALUES ('23:00:01.1', 'no');

INSERT INTO t1 VALUES ('23:00:01.1', 'no');

INSERT INTO t1 VALUES ('23:00:01.1', 'no');

INSERT INTO t1 VALUES ('23:00:01.1', 'no');

INSERT INTO t1 VALUES ('23:00:01.1', 'no');

INSERT INTO t1 VALUES ('23:00:01.1', 'no');

INSERT INTO t1 VALUES ('23:00:01.1', 'no');

INSERT INTO t1 VALUES ('23:00:01.1', 'no');

INSERT INTO t1 VALUES ('23:00:01.1', 'no');

INSERT INTO t1 VALUES ('23:00:01.1', 'no');

INSERT INTO t1 VALUES ('23:00:01.1', 'no');

INSERT INTO t1 VALUES ('23:00:01.1', 'no');

INSERT INTO t1 VALUES ('23:00:01.1', 'no');

INSERT INTO t1 VALUES ('23:00:01.1', 'no');

INSERT INTO t1 VALUES ('23:00:01.1', 'no');

INSERT INTO t1 VALUES ('23:00:01.1', 'no');

INSERT INTO t1 VALUES ('23:00:01.1', 'no');

INSERT INTO t1 VALUES ('23:00:01.1', 'no');

INSERT INTO t1 VALUES ('23:00:01.1', 'no');

INSERT INTO t1 VALUES ('23:00:01.1', 'no');

INSERT INTO t1 VALUES ('23:00:02.1', 'no');

INSERT INTO t1 VALUES ('23:00:03.1', 'yes');

INSERT INTO t1 VALUES ('23:00:04.1', 'yes');

EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('23:00:01.1','23:00:02.1');

DROP TABLE t1;

+------+-------------+-------+------+---------------+------+---------+------+------+-------------+

| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+------+-------------+-------+------+---------------+------+---------+------+------+-------------+

|    1 | SIMPLE      | t1    | ALL  | a             | NULL | NULL    | NULL |   27 | Using where |

+------+-------------+-------+------+---------------+------+---------+------+------+-------------+

Again, although the condition matches only two records, it erroneously goes through the full table scan.

Now I do the same for a DATETIME column:

CREATE OR REPLACE TABLE t1 (a DATETIME, filler CHAR(200), KEY(a));

INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:02', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:03', 'yes');

INSERT INTO t1 VALUES ('2001-01-01 23:00:04', 'yes');

EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('2001-01-01 23:00:01','2001-01-01 23:00:02');

DROP TABLE t1;

+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+

| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |

+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+

|    1 | SIMPLE      | t1    | range | a             | a    | 6       | NULL |    4 | Using index condition |

+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+

Looks good. It uses the range search.

Now I do the same for a DATETIME(1) column, with fractional data:

CREATE OR REPLACE TABLE t1 (a DATETIME(1), filler CHAR(200), KEY(a));

INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:02.1', 'no');

INSERT INTO t1 VALUES ('2001-01-01 23:00:03.1', 'yes');

INSERT INTO t1 VALUES ('2001-01-01 23:00:04.1', 'yes');

EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('2001-01-01 23:00:01.1','2001-01-01 23:00:02.1');

DROP TABLE t1;

+------+-------------+-------+------+---------------+------+---------+------+------+-------------+

| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+------+-------------+-------+------+---------------+------+---------+------+------+-------------+

|    1 | SIMPLE      | t1    | ALL  | a             | NULL | NULL    | NULL |   27 | Using where |

+------+-------------+-------+------+---------------+------+---------+------+------+-------------+

It erroneously goes through the full table scan.

Attachments

Activity

Ascending order - Click to sort in descending order

Alexander Barkov created issue - 2018-02-14 05:19

Alexander Barkov made changes - 2018-02-14 05:20

Field	Original Value	New Value
Description	I create a table with a {{TIME}} column and populate it with some data, and run an {{EXPLAIN}} for a {{SELECT}} with a {{WHERE a NOT IN()}} condition: {code:sql} CREATE OR REPLACE TABLE t1 (a TIME, filler CHAR(200), KEY(a)); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:02', 'no'); INSERT INTO t1 VALUES ('23:00:03', 'yes'); INSERT INTO t1 VALUES ('23:00:04', 'yes'); EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('23:00:01','23:00:02'); DROP TABLE t1; {code} {noformat} +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+ \| 1 \| SIMPLE \| t1 \| range \| a \| a \| 4 \| NULL \| 4 \| Using index condition \| +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+ {noformat} Notice, the condition covers only two records. The optimizer correctly choses to use the range search. Now I do the same for negative {{TIME}} values: {code:sql} CREATE OR REPLACE TABLE t1 (a TIME, filler CHAR(200), KEY(a)); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:02', 'no'); INSERT INTO t1 VALUES ('-23:00:03', 'yes'); INSERT INTO t1 VALUES ('-23:00:04', 'yes'); EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('-23:00:01','-23:00:02'); DROP TABLE t1; {code} {noformat} +------+-------------+-------+------+---------------+------+---------+------+------+-------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +------+-------------+-------+------+---------------+------+---------+------+------+-------------+ \| 1 \| SIMPLE \| t1 \| ALL \| a \| NULL \| NULL \| NULL \| 27 \| Using where \| +------+-------------+-------+------+---------------+------+---------+------+------+-------------+ {noformat} As in the first example, the condition matches only two records again, but the range search does not work any more and it goes through the full table scan. Now I do the same for positive time values, but outside of the time-of-the-day range, greater than {{23:59:59}}. {code:sql} CREATE OR REPLACE TABLE t1 (a TIME, filler CHAR(200), KEY(a)); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:02', 'no'); INSERT INTO t1 VALUES ('24:00:03', 'yes'); INSERT INTO t1 VALUES ('24:00:04', 'yes'); EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('24:00:01','24:00:02'); DROP TABLE t1; {code} {noformat} +------+-------------+-------+------+---------------+------+---------+------+------+-------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +------+-------------+-------+------+---------------+------+---------+------+------+-------------+ \| 1 \| SIMPLE \| t1 \| ALL \| a \| NULL \| NULL \| NULL \| 27 \| Using where \| +------+-------------+-------+------+---------------+------+---------+------+------+-------------+ {noformat} It erroneously uses the full table scan again. Now I do the same for a {{TIME(1)}} column with positive fractional data, within the time-of-the-day range: {code:sql} CREATE OR REPLACE TABLE t1 (a TIME(1), filler CHAR(200), KEY(a)); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:02.1', 'no'); INSERT INTO t1 VALUES ('23:00:03.1', 'yes'); INSERT INTO t1 VALUES ('23:00:04.1', 'yes'); EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('23:00:01.1','23:00:02.1'); DROP TABLE t1; {code} {noformat} +------+-------------+-------+------+---------------+------+---------+------+------+-------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +------+-------------+-------+------+---------------+------+---------+------+------+-------------+ \| 1 \| SIMPLE \| t1 \| ALL \| a \| NULL \| NULL \| NULL \| 27 \| Using where \| +------+-------------+-------+------+---------------+------+---------+------+------+-------------+ {noformat} Again, although the condition matches only two records, it erroneously goes through the full table scan. Now I do the same for a {{DATETIME}} column: {code:sql} CREATE OR REPLACE TABLE t1 (a DATETIME, filler CHAR(200), KEY(a)); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:02', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:03', 'yes'); INSERT INTO t1 VALUES ('2001-01-01 23:00:04', 'yes'); EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('2001-01-01 23:00:01','2001-01-01 23:00:02'); DROP TABLE t1; {code} {noformat} +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+ \| 1 \| SIMPLE \| t1 \| range \| a \| a \| 6 \| NULL \| 4 \| Using index condition \| +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+ {noformat} Looks good. It uses the range search. Now I do the same for a {{DATETIME(1)}} column, with fractional data: {code:sql} CREATE OR REPLACE TABLE t1 (a DATETIME(1), filler CHAR(200), KEY(a)); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:02.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:03.1', 'yes'); INSERT INTO t1 VALUES ('2001-01-01 23:00:04.1', 'yes'); EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('2001-01-01 23:00:01.1','2001-01-01 23:00:02.1'); DROP TABLE t1; {code} {noformat} +------+-------------+-------+------+---------------+------+---------+------+------+-------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +------+-------------+-------+------+---------------+------+---------+------+------+-------------+ \| 1 \| SIMPLE \| t1 \| ALL \| a \| NULL \| NULL \| NULL \| 27 \| Using where \| +------+-------------+-------+------+---------------+------+---------+------+------+-------------+ {noformat} It erroneously goes through the full table scan.	I create a table with a {{TIME}} column and populate it with some data, and run an {{EXPLAIN}} for a {{SELECT}} with a ``{{WHERE a NOT IN()}}'' condition: {code:sql} CREATE OR REPLACE TABLE t1 (a TIME, filler CHAR(200), KEY(a)); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:01', 'no'); INSERT INTO t1 VALUES ('23:00:02', 'no'); INSERT INTO t1 VALUES ('23:00:03', 'yes'); INSERT INTO t1 VALUES ('23:00:04', 'yes'); EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('23:00:01','23:00:02'); DROP TABLE t1; {code} {noformat} +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+ \| 1 \| SIMPLE \| t1 \| range \| a \| a \| 4 \| NULL \| 4 \| Using index condition \| +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+ {noformat} Notice, the condition covers only two records. The optimizer correctly choses to use the range search. Now I do the same for negative {{TIME}} values: {code:sql} CREATE OR REPLACE TABLE t1 (a TIME, filler CHAR(200), KEY(a)); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:01', 'no'); INSERT INTO t1 VALUES ('-23:00:02', 'no'); INSERT INTO t1 VALUES ('-23:00:03', 'yes'); INSERT INTO t1 VALUES ('-23:00:04', 'yes'); EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('-23:00:01','-23:00:02'); DROP TABLE t1; {code} {noformat} +------+-------------+-------+------+---------------+------+---------+------+------+-------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +------+-------------+-------+------+---------------+------+---------+------+------+-------------+ \| 1 \| SIMPLE \| t1 \| ALL \| a \| NULL \| NULL \| NULL \| 27 \| Using where \| +------+-------------+-------+------+---------------+------+---------+------+------+-------------+ {noformat} As in the first example, the condition matches only two records again, but the range search does not work any more and it goes through the full table scan. Now I do the same for positive time values, but outside of the time-of-the-day range, greater than {{23:59:59}}. {code:sql} CREATE OR REPLACE TABLE t1 (a TIME, filler CHAR(200), KEY(a)); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:01', 'no'); INSERT INTO t1 VALUES ('24:00:02', 'no'); INSERT INTO t1 VALUES ('24:00:03', 'yes'); INSERT INTO t1 VALUES ('24:00:04', 'yes'); EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('24:00:01','24:00:02'); DROP TABLE t1; {code} {noformat} +------+-------------+-------+------+---------------+------+---------+------+------+-------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +------+-------------+-------+------+---------------+------+---------+------+------+-------------+ \| 1 \| SIMPLE \| t1 \| ALL \| a \| NULL \| NULL \| NULL \| 27 \| Using where \| +------+-------------+-------+------+---------------+------+---------+------+------+-------------+ {noformat} It erroneously uses the full table scan again. Now I do the same for a {{TIME(1)}} column with positive fractional data, within the time-of-the-day range: {code:sql} CREATE OR REPLACE TABLE t1 (a TIME(1), filler CHAR(200), KEY(a)); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:01.1', 'no'); INSERT INTO t1 VALUES ('23:00:02.1', 'no'); INSERT INTO t1 VALUES ('23:00:03.1', 'yes'); INSERT INTO t1 VALUES ('23:00:04.1', 'yes'); EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('23:00:01.1','23:00:02.1'); DROP TABLE t1; {code} {noformat} +------+-------------+-------+------+---------------+------+---------+------+------+-------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +------+-------------+-------+------+---------------+------+---------+------+------+-------------+ \| 1 \| SIMPLE \| t1 \| ALL \| a \| NULL \| NULL \| NULL \| 27 \| Using where \| +------+-------------+-------+------+---------------+------+---------+------+------+-------------+ {noformat} Again, although the condition matches only two records, it erroneously goes through the full table scan. Now I do the same for a {{DATETIME}} column: {code:sql} CREATE OR REPLACE TABLE t1 (a DATETIME, filler CHAR(200), KEY(a)); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:02', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:03', 'yes'); INSERT INTO t1 VALUES ('2001-01-01 23:00:04', 'yes'); EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('2001-01-01 23:00:01','2001-01-01 23:00:02'); DROP TABLE t1; {code} {noformat} +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+ \| 1 \| SIMPLE \| t1 \| range \| a \| a \| 6 \| NULL \| 4 \| Using index condition \| +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+ {noformat} Looks good. It uses the range search. Now I do the same for a {{DATETIME(1)}} column, with fractional data: {code:sql} CREATE OR REPLACE TABLE t1 (a DATETIME(1), filler CHAR(200), KEY(a)); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:02.1', 'no'); INSERT INTO t1 VALUES ('2001-01-01 23:00:03.1', 'yes'); INSERT INTO t1 VALUES ('2001-01-01 23:00:04.1', 'yes'); EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('2001-01-01 23:00:01.1','2001-01-01 23:00:02.1'); DROP TABLE t1; {code} {noformat} +------+-------------+-------+------+---------------+------+---------+------+------+-------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +------+-------------+-------+------+---------------+------+---------+------+------+-------------+ \| 1 \| SIMPLE \| t1 \| ALL \| a \| NULL \| NULL \| NULL \| 27 \| Using where \| +------+-------------+-------+------+---------------+------+---------+------+------+-------------+ {noformat} It erroneously goes through the full table scan.

Alexander Barkov made changes - 2018-02-14 05:24

Link

This issue blocks ~~MDEV-8894~~ [ ~~MDEV-8894~~ ]

Alexander Barkov made changes - 2018-02-14 05:24

Link

This issue blocks ~~MDEV-4912~~ [ ~~MDEV-4912~~ ]

Sergei Golubchik made changes - 2019-03-29 12:04

Fix Version/s

10.4 [ 22408 ]

Alexander Barkov made changes - 2019-11-15 13:05

Component/s

Data types [ 13906 ]

Julien Fritsch made changes - 2019-11-18 11:41

Epic Link

MDEV-21071 [ 80504 ]

Julien Fritsch made changes - 2019-11-18 11:55

Link

This issue blocks ~~MDEV-4912~~ [ ~~MDEV-4912~~ ]

Julien Fritsch made changes - 2020-05-05 07:19

Link

This issue blocks ~~MDEV-8894~~ [ ~~MDEV-8894~~ ]

Sergei Golubchik made changes - 2021-12-06 21:33

Workflow

MariaDB v3 [ 85575 ]

MariaDB v4 [ 140649 ]

Julien Fritsch made changes - 2023-04-27 14:21

Fix Version/s

10.3 [ 22126 ]

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 1 Start watching this issue

Dates

Created:: 2018-02-14 05:19

Updated:: 2023-04-27 14:21

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Activity

People

Dates

Git Integration