| Bug #90100 | Year type column have index, query value more than 2156 , the result is wrong | ||
|---|---|---|---|
| Submitted: | 16 Mar 2018 6:15 | Modified: | 8 Feb 2019 19:24 |
| Reporter: | i i | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.6 , 5.7, 5.6.39, 5.7.21 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | INDEX, regression, year type column | ||
[16 Mar 2018 6:18]
i i
modify Synopsis
[16 Mar 2018 6:28]
i i
Modfiy Description to follow: Column type is year type and have index. The sql contain year column ,and year column query value less than 2155 ,the result is right .But year column query value more than 2156 ,the result is wrong.
[16 Mar 2018 6:49]
MySQL Verification Team
Hello! Thank you for the report and test case. Observed that 5.6.39, 5.7.21 affected. Thanks, Umesh
[16 Mar 2018 6:50]
MySQL Verification Team
test results
Attachment: 90100.results (application/octet-stream, text), 10.46 KiB.
[14 Sep 2018 4:17]
Sreeharsha Ramanavarapu
Posted by developer: Possible workaround would be ignore the secondary index. mysql> select count(*) FROM select_tab IGNORE INDEX(index_year_test) where year_test < 2156; +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec)
[9 Oct 2018 8:58]
Sreeharsha Ramanavarapu
Posted by developer:
CREATE TABLE t1 (pk INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
year_test YEAR(4) DEFAULT NULL,
KEY index_year_test (year_test) );
INSERT INTO t1 (pk , year_test) VALUES (1,2014);
mysql> SELECT COUNT(*) FROM t1 WHERE year_test < 2156;
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
EXPLAIN SELECT COUNT(*) FROM t1 WHERE year_test < 2156;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
The range of YEAR(4) column is in the range 1901 to 2155. When a number greater than the max value is supplied, optimizer is unable to convert 2156 into a valid value in the YEAR column's range resulting in constant folding. That is internally the statement becomes "SELECT COUNT(*) FROM t1 WHERE FALSE ".
This problem will be fixed by WL#11935.
Workaround: Ignore the secondary index so that table scan is used.
mysql> SELECT COUNT(*) FROM t1 IGNORE INDEX (index_year_test) WHERE year_test < 2156;
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
[8 Feb 2019 19:24]
Jon Stephens
Fixed in MySQL 8.0.16 by WL#11935 "Add folding of constants when compared to fields". Closed.
Description: Column type is year type and have index . The sql contain year column When column type is year type and have index . The follow sql: select count(*) FROM select_tab where 31 between 31 AND (year_test) . and year_test column data is more than 31 . when table records more than 6 , the result is wrong . MYSQL version is 5.6 , 5.7.14 How to repeat: mysql> /* Create table and create index on year type column */ mysql> CREATE TABLE `select_tab` ( -> `pk` bigint(11) NOT NULL AUTO_INCREMENT, -> `year_test` year(4) DEFAULT NULL, -> PRIMARY KEY (`pk`), -> KEY `index_year_test` (`year_test`) -> ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=gbk ROW_FORMAT=DYNAMIC; mysql> /* insert one records to table */ mysql> insert into select_tab (pk , year_test) values(1,2014); Query OK, 1 row affected (0.02 sec) mysql> /* when year_test query value less than 2155 ，the result is right */ mysql> select count(*) FROM select_tab where year_test < 2155; +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) mysql> /* when year_test query value more than 2155 ，the result is wrong */ mysql> select count(*) FROM select_tab where year_test < 2156; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec)