Bug #90100 Year type column have index, query value more than 2156 , the result is wrong
Submitted: 16 Mar 6:15 Modified: 16 Mar 6:49
Reporter: i i Email Updates:
Status: Verified Impact on me:
None 
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 6:15] i i
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)
[16 Mar 6:18] i i
modify  Synopsis
[16 Mar 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 6:49] Umesh Shastry
Hello!

Thank you for the report and test case.
Observed that 5.6.39, 5.7.21 affected.

Thanks,
Umesh
[16 Mar 6:50] Umesh Shastry
test results

Attachment: 90100.results (application/octet-stream, text), 10.46 KiB.

[14 Sep 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 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)