[MDEV-10213] Inconsistent handling of 2-digit year in const and column Created: 2016-06-12  Updated: 2017-10-20

Status: Open
Project: MariaDB Server
Component/s: Temporal Types
Affects Version/s: 10.1.10
Fix Version/s: 10.1

Type: Bug Priority: Minor
Reporter: Dylan Su Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Output:
===

mysql> drop table if exists sb;
 
Query OK, 0 rows affected (0.01 sec)
 
 
 
mysql>  create table sb(e year(4), i int);
 
Query OK, 0 rows affected (0.01 sec)
 
 
 
mysql>  insert into sb values (1932, 19);
 
Query OK, 1 row affected (0.01 sec)
 
 
 
mysql>  select e < i, e < 19 from sb;
 
+-------+--------+
 
| e < i | e < 19 |
 
+-------+--------+
 
|     0 |      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:
===
Since 19 is expected to be treated as 2019, 'e<i' should return true(1).

Recreate:
===

drop table if exists sb;
 
 create table sb(e year(4), i int);
 
 insert into sb values (1932, 19);
 
 select e < i, e < 19 from sb;



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

You are making an assumption that comparison to a constant is the same as comparison to a column value. The assumption is not justified.
See MySQL manual which describes a similar difference:
http://dev.mysql.com/doc/refman/5.6/en/type-conversion.html

If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed.
<...>

A single-row subquery from a table or tables is not considered a constant. For example, if a subquery returns an integer to be compared to a DATETIME value, the comparison is done as two integers. The integer is not converted to a temporal value.

Since the manual does not mention the YEAR type specifically in this context, I will assign it to bar to confirm that it applies to YEAR (and to a simple column value rather than a subquery result).

Comment by Elena Stepanova [ 2017-10-20 ]

Also, if the problem is limited to YEAR(2), it's been deprecated for a long time and will probably be removed soon, so there is no point fixing anything for it specifically. I'll leave it to bar to decide.

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