[MDEV-4882] Conversion of TIME to DATETIME works differently in MariaDB and MySQL Created: 2013-08-10  Updated: 2013-08-20  Resolved: 2013-08-10

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.4
Fix Version/s: 10.0.5

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Alexander Barkov
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

I haven't found this one among filed bugs, maybe it was just put in a different way; please feel free to close as a duplicate if so

There was a change in MySQL 5.6.4:

TIME values are converted to DATETIME by adding the time to the current date. (This means that the date part of the result differs from the current date if the time value is outside the range from '00:00:00' to '23:59:59'.) Previously, conversion of TIME values to DATETIME was unreliable.

It works in MySQL 5.6, but not in MariaDB 10.0.4 candidate:

MySQL [test]> select cast(current_time() as datetime);
 
+----------------------------------+
 
| cast(current_time() as datetime) |
 
+----------------------------------+
 
| 2013-08-10 14:19:15              |
 
+----------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MySQL [test]> select @@version;
 
+-----------+
 
| @@version |
 
+-----------+
 
| 5.6.10    |
 
+-----------+
 

MariaDB [test]> select cast(current_time() as datetime);
 
+----------------------------------+
 
| cast(current_time() as datetime) |
 
+----------------------------------+
 
| 0000-00-00 14:19:24              |
 
+----------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select @@version;
 
+----------------------+
 
| @@version            |
 
+----------------------+
 
| 10.0.3-MariaDB-debug |
 
+----------------------+



 Comments   
Comment by Sergei Golubchik [ 2013-08-10 ]

It was intentionally implemented deviation from MySQL behavior

Comment by Elena Stepanova [ 2013-08-10 ]

Okay, but then I think we need a proper explanation why it was chosen this way, because people will be asking, and a deviation without a reason doesn't look good. I haven't found it in KB in types section, but maybe it's somewhere else?

Comment by Alexander Barkov [ 2013-08-12 ]

IIRC, the SQL standard does not have implicit conversion from TIME to TIMESTAMP.
So the SQL standard part that suites best "conversion of TIME to DATETIME"
would be how "CAST( time_expression AS TIMESTAMP)" works.

"SQL:2011 Part 2 Foundation, section <cast specification>" says the following about cast from TIME to TIMESTAMP:

> The fields year, month, and day of TV (the target value) are set to their
> respective values in an execution of CURRENT_DATE and
> the fields hour, minute, and second of TV are set to their respective values in SV (the source value),
> with implementation-defined rounding or truncation if necessary.

I'd vote to apply the same rules to for CAST and for implicit conversion,
like MySQL-5.6 does.

Serg, what was the reason not to use these rules in MariaDB?

Comment by Sergei Golubchik [ 2013-08-14 ]

Simply because we found it so weird and counter-intuitive that we intentionally diverged from the standard here. The notion that a result of a type cast depends not only on the original value and the desired type, but also on the current date? And produces non-repeatable results? That's a serious gotcha. I believe our approach is more logical, even if non-standard.

Comment by Alexander Barkov [ 2013-08-20 ]

The way it's implemented now is make it depend on sql_mode,
not ONLY on the original value. In sql_mode=no_zero_date it always
returns NULL. I think the Standard implementation is more useful.

----------------------------------

cast(time'10:20:30' as datetime)

----------------------------------

0000-00-00 10:20:30

----------------------------------
1 row in set (0.05 sec)

MariaDB [test]> set sql_mode=no_zero_date;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select cast(time'10:20:30' as datetime);
----------------------------------

cast(time'10:20:30' as datetime)

----------------------------------

NULL

----------------------------------
1 row in set, 1 warning (0.00 sec)

Generated at Thu Feb 08 06:59:55 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.