Description:
When column type is year type and have index . The table of year_test column value is more than 31 . And the table records more than 6 . The follow sql: select count(*) FROM select_tab where 31 between 31 AND (year_test) have wrong result. Expect result is records count ,but actual is 0.
Mysql version is ：5.7.14
How to repeat:
Here is the test case:
mysql> /* Create table and index on year_test 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 6 records to table */
mysql> insert into select_tab (pk , year_test) values(1,2014);
Query OK, 1 row affected (0.00 sec)
mysql> insert into select_tab (pk , year_test) values(2,2014);
Query OK, 1 row affected (0.00 sec)
mysql> insert into select_tab (pk , year_test) values(3,2014);
Query OK, 1 row affected (0.00 sec)
mysql> insert into select_tab (pk , year_test) values(4,2014);
Query OK, 1 row affected (0.00 sec)
mysql> insert into select_tab (pk , year_test) values(5,2014);
Query OK, 1 row affected (0.00 sec)
mysql> insert into select_tab (pk , year_test) values(6,2014);
Query OK, 1 row affected (0.00 sec)
mysql> /* execute follow sql and have right result */
mysql> select count(*) FROM select_tab where 31 between 31 AND (year_test) ;
+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set (0.00 sec)
mysql> /*add one more records to table */
mysql> insert into select_tab (pk , year_test) values(7,2014);
Query OK, 1 row affected (0.00 sec)
mysql> /* execute follow sql and have wrong result ,expect result is 7 ,but actual is 0 */
mysql> select count(*) FROM select_tab where 31 between 31 AND (year_test);
+----------+
| count(*) |
+----------+
| 0 |
+----------+
Description: When column type is year type and have index . The table of year_test column value is more than 31 . And the table records more than 6 . The follow sql: select count(*) FROM select_tab where 31 between 31 AND (year_test) have wrong result. Expect result is records count ,but actual is 0. Mysql version is ：5.7.14 How to repeat: Here is the test case: mysql> /* Create table and index on year_test 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 6 records to table */ mysql> insert into select_tab (pk , year_test) values(1,2014); Query OK, 1 row affected (0.00 sec) mysql> insert into select_tab (pk , year_test) values(2,2014); Query OK, 1 row affected (0.00 sec) mysql> insert into select_tab (pk , year_test) values(3,2014); Query OK, 1 row affected (0.00 sec) mysql> insert into select_tab (pk , year_test) values(4,2014); Query OK, 1 row affected (0.00 sec) mysql> insert into select_tab (pk , year_test) values(5,2014); Query OK, 1 row affected (0.00 sec) mysql> insert into select_tab (pk , year_test) values(6,2014); Query OK, 1 row affected (0.00 sec) mysql> /* execute follow sql and have right result */ mysql> select count(*) FROM select_tab where 31 between 31 AND (year_test) ; +----------+ | count(*) | +----------+ | 6 | +----------+ 1 row in set (0.00 sec) mysql> /*add one more records to table */ mysql> insert into select_tab (pk , year_test) values(7,2014); Query OK, 1 row affected (0.00 sec) mysql> /* execute follow sql and have wrong result ,expect result is 7 ,but actual is 0 */ mysql> select count(*) FROM select_tab where 31 between 31 AND (year_test); +----------+ | count(*) | +----------+ | 0 | +----------+