MySQL Bugs: #44094: Innodb and MyISAM differ in their handling of invalid YEAR values

Bug #44094	Innodb and MyISAM differ in their handling of invalid YEAR values
Submitted:	5 Apr 2009 11:45	Modified:	5 Apr 2009 12:47
Reporter:	Philip Stoev	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.1.34, 6.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When executing UPDATEs or SELECTs containing invalid YEAR values, MyISAM and Innodb will return different results. In addition, the result is dependent on the optimizer plan that was selected.

Record the attached test case under MyISAM and replay it under Innodb. From the two SELECTs at the end, only SELECT `year_key` ... returns rows and only under Innodb. In all other cases, no rows are returned.

How to repeat:
--source include/have_innodb.inc

CREATE TABLE `table10` (
        `year_key` year,
        `year` year,
        key (`year_key` )
);

INSERT IGNORE INTO table10 VALUES  ('2007', '2000') ,  ('2003', '2001') ,  ('2000', '2001') ,  ('2003', '2003') ,  ('2005', '2004') ,  ('2006', '2002') ,  ('2008', '2000') ,  ('2006', '2004') ,  ('2005', '2001') ,  ('2001', '2008');

UPDATE `table10` SET `year` = 8693157 WHERE `year` = '2008';
UPDATE `table10` SET `year_key` = '2000' WHERE `year` = '2006';
UPDATE `table10` SET `year` = '2004' WHERE `year_key` < 573299;
UPDATE `table10` SET `year_key` = 7484894 WHERE `year` > 3726631;
UPDATE `table10` SET `year` = '2002' WHERE `year_key` = 8949780;
UPDATE `table10` SET `year` = 499983 WHERE `year` < 14816386;
UPDATE `table10` SET `year` = '2003' WHERE `year_key` > '2005';
UPDATE `table10` SET `year` = 5938112 WHERE `year_key` = 12804925;
UPDATE `table10` SET `year` = 1074871 WHERE `year_key` < '2003';
UPDATE `table10` SET `year` = '2007' WHERE `year` > '2003';
UPDATE `table10` SET `year` = '2004' WHERE `year_key` > 4513088;
UPDATE `table10` SET `year_key` = 7926815 WHERE `year_key` > 12461525;
UPDATE `table10` SET `year` = 8526978 WHERE `year` > '2002';
UPDATE `table10` SET `year_key` = 6649033 WHERE `year_key` > '2001';
UPDATE `table10` SET `year_key` = 1536062 WHERE `year` < '2005';
UPDATE `table10` SET `year` = 4861366 WHERE `year_key` > '2007';
UPDATE `table10` SET `year` = '2004' WHERE `year` > 7187064;
UPDATE `table10` SET `year_key` = 5011022 WHERE `year_key` < 3428560;
UPDATE `table10` SET `year_key` = 10923990 WHERE `year_key` = '2000';
UPDATE `table10` SET `year` = 3618011 WHERE `year` < '2009';
UPDATE `table10` SET `year_key` = '2002' WHERE `year` < '2000';
UPDATE `table10` SET `year` = '2008' WHERE `year` = 10457106;
UPDATE `table10` SET `year_key` = 12223164 WHERE `year` = '2007';
UPDATE `table10` SET `year` = 134263 WHERE `year` = 15541585;

SELECT `year` FROM `table10` WHERE `year_key` < 7709890;
SELECT `year_key` FROM `table10` WHERE `year_key` < 7709890;

DROP TABLE table10;

Suggested fix:
I think rows should be returned for both SELECTs under both storage engines. Or, at least, the behavior must be identical across storage engines and for all optimizer plans.

Verified with recent 6.0.11 from bzr:

mysql> CREATE TABLE `table10` (
    ->         `year_key` year,
    ->         `year` year,
    ->         key (`year_key` )
    -> ) engine=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT IGNORE INTO table10 VALUES  ('2007', '2000') ,  ('2003', '2001') ,  ('2000',
    -> '2001') ,  ('2003', '2003') ,  ('2005', '2004') ,  ('2006', '2002') ,  ('2008', '2000') ,
    -> ('2006', '2004') ,  ('2005', '2001') ,  ('2001', '2008');
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql>
mysql> UPDATE `table10` SET `year` = 8693157 WHERE `year` = '2008';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> UPDATE `table10` SET `year_key` = '2000' WHERE `year` = '2006';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> UPDATE `table10` SET `year` = '2004' WHERE `year_key` < 573299;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> UPDATE `table10` SET `year_key` = 7484894 WHERE `year` > 3726631;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> UPDATE `table10` SET `year` = '2002' WHERE `year_key` = 8949780;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> UPDATE `table10` SET `year` = 499983 WHERE `year` < 14816386;
Query OK, 9 rows affected, 10 warnings (0.00 sec)
Rows matched: 10  Changed: 9  Warnings: 10

mysql> UPDATE `table10` SET `year` = '2003' WHERE `year_key` > '2005';
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> UPDATE `table10` SET `year` = 5938112 WHERE `year_key` = 12804925;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> UPDATE `table10` SET `year` = 1074871 WHERE `year_key` < '2003';
Query OK, 0 rows affected, 2 warnings (0.00 sec)
Rows matched: 2  Changed: 0  Warnings: 2

mysql> UPDATE `table10` SET `year` = '2007' WHERE `year` > '2003';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> UPDATE `table10` SET `year` = '2004' WHERE `year_key` > 4513088;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> UPDATE `table10` SET `year_key` = 7926815 WHERE `year_key` > 12461525;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> UPDATE `table10` SET `year` = 8526978 WHERE `year` > '2002';
Query OK, 4 rows affected, 4 warnings (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 4

mysql> UPDATE `table10` SET `year_key` = 6649033 WHERE `year_key` > '2001';
Query OK, 8 rows affected, 8 warnings (0.00 sec)
Rows matched: 8  Changed: 8  Warnings: 8

mysql> UPDATE `table10` SET `year_key` = 1536062 WHERE `year` < '2005';
Query OK, 2 rows affected, 10 warnings (0.00 sec)
Rows matched: 10  Changed: 2  Warnings: 10

mysql> UPDATE `table10` SET `year` = 4861366 WHERE `year_key` > '2007';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> UPDATE `table10` SET `year` = '2004' WHERE `year` > 7187064;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> UPDATE `table10` SET `year_key` = 5011022 WHERE `year_key` < 3428560;
Query OK, 0 rows affected, 10 warnings (0.00 sec)
Rows matched: 10  Changed: 0  Warnings: 10

mysql> UPDATE `table10` SET `year_key` = 10923990 WHERE `year_key` = '2000';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> UPDATE `table10` SET `year` = 3618011 WHERE `year` < '2009';
Query OK, 0 rows affected, 10 warnings (0.00 sec)
Rows matched: 10  Changed: 0  Warnings: 10

mysql> UPDATE `table10` SET `year_key` = '2002' WHERE `year` < '2000';
Query OK, 10 rows affected (0.00 sec)
Rows matched: 10  Changed: 10  Warnings: 0

mysql> UPDATE `table10` SET `year` = '2008' WHERE `year` = 10457106;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> UPDATE `table10` SET `year_key` = 12223164 WHERE `year` = '2007';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> UPDATE `table10` SET `year` = 134263 WHERE `year` = 15541585;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql>
mysql> SELECT `year` FROM `table10` WHERE `year_key` < 7709890;
+------+
| year |
+------+
| 0000 |
| 0000 |
| 0000 |
| 0000 |
| 0000 |
| 0000 |
| 0000 |
| 0000 |
| 0000 |
| 0000 |
+------+
10 rows in set (0.00 sec)

mysql> SELECT `year_key` FROM `table10` WHERE `year_key` < 7709890;
Empty set (0.00 sec)

mysql> explain SELECT `year` FROM `table10` WHERE `year_key` < 7709890;
+----+-------------+---------+-------+---------------+----------+---------+------+------+----------------------------------+
| id | select_type | table   | type  | possible_keys | key      | key_len | ref  | rows | Extra                            |
+----+-------------+---------+-------+---------------+----------+---------+------+------+----------------------------------+
|  1 | SIMPLE      | table10 | range | year_key      | year_key | 2       | NULL |    1 | Using index condition; Using MRR |
+----+-------------+---------+-------+---------------+----------+---------+------+------+----------------------------------+
1 row in set (0.01 sec)

mysql> explain SELECT `year_key` FROM `table10` WHERE `year_key` < 7709890;
+----+-------------+---------+-------+---------------+----------+---------+------+------+--------------------------+
| id | select_type | table   | type  | possible_keys | key      | key_len | ref  | rows | Extra                    |
+----+-------------+---------+-------+---------------+----------+---------+------+------+--------------------------+
|  1 | SIMPLE      | table10 | range | year_key      | year_key | 2       | NULL |    1 | Using where; Using index |
+----+-------------+---------+-------+---------------+----------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

So, as one can see, SELECT `year` ... returns rows in case of InnoDB table.

Same problem with 5.1.34:

mysql> SELECT `year` FROM `table10` WHERE `year_key` < 7709890;
+------+
| year |
+------+
| 0000 |
| 0000 |
| 0000 |
| 0000 |
| 0000 |
| 0000 |
| 0000 |
| 0000 |
| 0000 |
| 0000 |
+------+
10 rows in set (0.00 sec)

mysql> SELECT `year_key` FROM `table10` WHERE `year_key` < 7709890;
Empty set (0.00 sec)

mysql> explain SELECT `year` FROM `table10` WHERE `year_key` < 7709890;
+----+-------------+---------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | table10 | range | year_key      | year_key | 2       | NULL |    1 | Using where |
+----+-------------+---------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.1.34-debug |
+--------------+
1 row in set (0.00 sec)

mysql> alter table table10 engine=MyISAM;
Query OK, 10 rows affected (0.07 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SELECT `year` FROM `table10` WHERE `year_key` < 7709890;
Empty set (0.01 sec)

mysql> SELECT `year_key` FROM `table10` WHERE `year_key` < 7709890;
Empty set (0.01 sec)