[MDEV-10192] Comparison result for column w/ YEAR(4) and const w/ 2 digits is incorrect Created: 2016-06-08  Updated: 2016-06-09  Resolved: 2016-06-09

Status: Closed
Project: MariaDB Server
Component/s: Temporal Types
Affects Version/s: 10.1.10
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Dylan Su Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

Description:
Output:
===

mysql> drop table t1; create table t1(a year(4));
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
Query OK, 0 rows affected (0.01 sec)
 
 
 
mysql> insert into t1 values (1930);
 
Query OK, 1 row affected (0.00 sec)
 
 
 
mysql> select a, a < 10 from t1;
 
+------+--------+
 
| a    | a < 10 |
 
+------+--------+
 
| 1930 |      1 |
 
+------+--------+
 
1 row in set (0.00 sec)
 
 
 
mysql> select version();
 
+---------------------+
 
| version()           |
 
+---------------------+
 
| 10.1.10-MariaDB-log |
 
+---------------------+
 
1 row in set (0.00 sec)

Problem:
===
"a < 10" is not reasonable.
It is expected to compare like ' a < 1910 '.

How to repeat:

drop table t1; create table t1(a year(4));
 
insert into t1 values (1930);
 
select a, a < 10 from t1;

Suggested fix:
"a < 10" returns 0.

 Comments   
Comment by Elena Stepanova [ 2016-06-09 ]

dylan,

Rules for interpreting 2-digit year representation might be not very intuitive, but they are explicitly described in documentation.
MySQL manual:

MySQL interprets dates specified with ambiguous year values using these rules:

Year values in the range 00-69 are converted to 2000-2069.

Year values in the range 70-99 are converted to 1970-1999.

MariaDB KB:

In two-digit format, the allowable values are 70 to 69, representing years from 1970 to 2069.

So, in your case 10 is not 1910, it's 2010.

Generated at Thu Feb 08 07:40:21 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.