[MDEV-9541] Selecting a time using a datetime fails - Jira

Scott Bronson created issue - 2016-02-09 22:17

Scott Bronson made changes - 2016-02-09 22:18

Field	Original Value	New Value
Description	MariaDB seems to behave differently from other databases when selecting a time with a datetime. Maria requires truncating the date first, otherwise it will return the empty set. MySQL, Postgres, Sqlite, and SQL Server ignore the date and return matching rows. Here are the examples: https://gist.github.com/bronson/0a49e7d3fe7242cb5cd3 (thanks to litheum for this table) Monty says: what looks wrong at my end is that we are converting the string to this time: "838:49:59.99999" and then trying to compare that. This is stored in a Item_time_literal.	MariaDB seems to behave differently from other databases when selecting a time with a datetime. Maria requires truncating the date first, otherwise it will return the empty set. MySQL, Postgres, Sqlite, and SQL Server ignore the date and return rows matching the time portion. Here are the examples: https://gist.github.com/bronson/0a49e7d3fe7242cb5cd3 (thanks to litheum for this table) Monty says: what looks wrong at my end is that we are converting the string to this time: "838:49:59.99999" and then trying to compare that. This is stored in a Item_time_literal.

Scott Bronson made changes - 2016-02-09 22:19

Description

MariaDB seems to behave differently from other databases when selecting a time with a datetime. Maria requires truncating the date first, otherwise it will return the empty set. MySQL, Postgres, Sqlite, and SQL Server ignore the date and return rows matching the time portion.

Here are the examples:

https://gist.github.com/bronson/0a49e7d3fe7242cb5cd3 (thanks to litheum for this table)

Monty says: what looks wrong at my end is that we are converting the string to this time: "838:49:59.99999" and then trying to compare that. This is stored in a Item_time_literal.

MariaDB seems to behave differently from other databases when selecting a time with a datetime. Maria requires truncating the date first, otherwise it returns the empty set. MySQL, Postgres, Sqlite, and SQL Server ignore the date and return rows matching the time portion.

Here are the examples:

https://gist.github.com/bronson/0a49e7d3fe7242cb5cd3 (thanks to litheum for this table)

Monty says: what looks wrong at my end is that we are converting the string to this time: "838:49:59.99999" and then trying to compare that. This is stored in a Item_time_literal.

Sergei Golubchik added a comment - 2016-02-09 23:54 - edited

Saving the data from the paste:

MariaDB-5.3, 5.5, 10.0, 10.1
mariadb 10.1.8-MariaDB-log (root) [test]> create table t1 (id serial, t time);
Query OK, 0 rows affected (0.01 sec)

mariadb 10.1.8-MariaDB-log (root) [test]> insert into t1 (t) values ('12:30:00');
Query OK, 1 row affected (0.00 sec)

mariadb 10.1.8-MariaDB-log (root) [test]> select * from t1 where t = '2000-01-01 12:30:00';
Empty set (0.00 sec)

MySQL, MariaDB-5.1, MariaDB-5.2
mysql 5.6.26-log (root) [test]> create table t1 (id serial, t time);
Query OK, 0 rows affected (0.01 sec)

mysql 5.6.26-log (root) [test]> insert into t1 (t) values ('12:30:00');
Query OK, 1 row affected (0.00 sec)

mysql 5.6.26-log (root) [test]> select * from t1 where t = '2000-01-01 12:30:00';
+----+----------+
\| id \| t \|
+----+----------+
\| 1 \| 12:30:00 \|
+----+----------+
1 row in set (0.00 sec)

MariaDB [test]> select * from t1 where t = TIMESTAMP('2000-01-01 12:30:00');
+----+----------+
\| id \| t \|
+----+----------+
\| 1 \| 12:30:00 \|
+----+----------+

MySQL [test]> select * from t1 where coalesce(t) = coalesce(TIMESTAMP'2000-01-01 12:30:00');
Empty set (0.00 sec)

MySQL [test]> select * from t1 where t = 20000101123000;
+----+----------+
\| id \| t \|
+----+----------+
\| 1 \| 12:30:00 \|
+----+----------+
1 row in set, 1 warning (0.00 sec)

PostgreSQL
postgresql=# create table t1 (id serial, t time);
CREATE TABLE
postgresql=# insert into t1 (t) values ('12:30:00');
INSERT 0 1
postgresql=# select * from t1 where t = '2000-01-01 12:30:00';
t
----------
12:30:00
(1 row)

SQL Server
create table t1 (id int identity(1,1), t time);
insert into t1 (t) values ('12:30:00');
select * from t1 where t = '2000-01-01 12:30:00';
1 12:30:00.0000000

Sergei Golubchik added a comment - 2016-02-09 23:54 - edited Saving the data from the paste: MariaDB-5.3, 5.5, 10.0, 10.1 mariadb 10.1.8-MariaDB-log (root) [test]> create table t1 (id serial, t time); Query OK, 0 rows affected (0.01 sec) mariadb 10.1.8-MariaDB-log (root) [test]> insert into t1 (t) values ('12:30:00'); Query OK, 1 row affected (0.00 sec) mariadb 10.1.8-MariaDB-log (root) [test]> select * from t1 where t = '2000-01-01 12:30:00'; Empty set (0.00 sec) MySQL, MariaDB-5.1, MariaDB-5.2 mysql 5.6.26-log (root) [test]> create table t1 (id serial, t time); Query OK, 0 rows affected (0.01 sec) mysql 5.6.26-log (root) [test]> insert into t1 (t) values ('12:30:00'); Query OK, 1 row affected (0.00 sec) mysql 5.6.26-log (root) [test]> select * from t1 where t = '2000-01-01 12:30:00'; +----+----------+ | id | t | +----+----------+ | 1 | 12:30:00 | +----+----------+ 1 row in set (0.00 sec) MariaDB [test]> select * from t1 where t = TIMESTAMP('2000-01-01 12:30:00'); +----+----------+ | id | t | +----+----------+ | 1 | 12:30:00 | +----+----------+ MySQL [test]> select * from t1 where coalesce(t) = coalesce(TIMESTAMP'2000-01-01 12:30:00'); Empty set (0.00 sec) MySQL [test]> select * from t1 where t = 20000101123000; +----+----------+ | id | t | +----+----------+ | 1 | 12:30:00 | +----+----------+ 1 row in set, 1 warning (0.00 sec) PostgreSQL postgresql=# create table t1 (id serial, t time); CREATE TABLE postgresql=# insert into t1 (t) values ('12:30:00'); INSERT 0 1 postgresql=# select * from t1 where t = '2000-01-01 12:30:00'; t ---------- 12:30:00 (1 row) SQL Server create table t1 (id int identity(1,1), t time); insert into t1 (t) values ('12:30:00'); select * from t1 where t = '2000-01-01 12:30:00'; 1 12:30:00.0000000

Michael Widenius added a comment - 2016-02-10 10:12 - edited

The issue is caused by the code in item.cc::Item::get_time_with_conversion()

  if (ltime->time_type != MYSQL_TIMESTAMP_TIME)

    MYSQL_TIME ltime2;

    if ((thd->variables.old_behavior & OLD_MODE_ZERO_DATE_TIME_CAST) &&

        (ltime->year || ltime->day || ltime->month))

/*

        Old mode conversion from DATETIME with non-zero YYYYMMDD part

        to TIME works very inconsistently. Possible variants:

        - truncate the YYYYMMDD part

        - add (MM*33+DD)*24 to hours

        - add (MM*31+DD)*24 to hours

        Let's return NULL here, to disallow equal field propagation.

        Note, If we start to use this method in more pieces of the code other

        than eqial field propagation, we should probably return

        NULL only if some flag in fuzzydate is set.

*/

      return (null_value= true);

    if (datetime_to_time_with_warn(thd, ltime, &ltime2, TIME_SECOND_PART_DIGITS))

If OLD_MODE_ZERO_DATE_TIME_CAST is set, then the above returns null, which will cause the comparison to fail in equal field propagations. This will delay the comparison to Arg_comparator::compare_temporal() which will compare a time to a datetime which will of course fail. This is not how MySQL did it so the option doesn't deliver on it's promises.

If OLD_MODE_ZERO_DATE_TIME_CAST is not set datetime_to_time_with_warn() is called, which will convert the datetime to a time value relative to the current time, which doesn't make any sense to me (No other database seems to do it this way and it doesn't match how insert is working). This function also tests the above flag, which is pointless in this context as it was already tested in the calling function. We only come here if the flag is set and all date parts are zero, which doesn't help this particular problem.

How things should work:

We should be compatible with the other databases.
Same transformation of constants should happen on compare as it happens on insert.
Conclusion from above is that when comparing a datetime constant with a time, we should only compare the time part.

Suggestion:

in Item_get_time_with_conversion() replace call to datetime_to_time_with_warn() with datetime_to_time().
Add tests to the test suite to fully test all usage of ZERO_DATE_TIME_CAST
Add to https://mariadb.com/kb/en/mariadb/time the rules for how date columns are compared with constants.
Also test comparison with 20020103133435
Test what happens with UPDATE and DELETE. Both with comparison against key and normal field.

Open questions:

What should happen if we compare a time field with a datetime field?
On insert we in MariaDB copy the time value part, like we do with constant strings.

Michael Widenius added a comment - 2016-02-10 10:12 - edited The issue is caused by the code in item.cc::Item::get_time_with_conversion() if (ltime->time_type != MYSQL_TIMESTAMP_TIME) { MYSQL_TIME ltime2; if ((thd->variables.old_behavior & OLD_MODE_ZERO_DATE_TIME_CAST) && (ltime->year || ltime->day || ltime->month)) { /* Old mode conversion from DATETIME with non-zero YYYYMMDD part to TIME works very inconsistently. Possible variants: - truncate the YYYYMMDD part - add (MM*33+DD)*24 to hours - add (MM*31+DD)*24 to hours Let's return NULL here, to disallow equal field propagation. Note, If we start to use this method in more pieces of the code other than eqial field propagation, we should probably return NULL only if some flag in fuzzydate is set. */ return (null_value= true); } if (datetime_to_time_with_warn(thd, ltime, &ltime2, TIME_SECOND_PART_DIGITS)) If OLD_MODE_ZERO_DATE_TIME_CAST is set, then the above returns null, which will cause the comparison to fail in equal field propagations. This will delay the comparison to Arg_comparator::compare_temporal() which will compare a time to a datetime which will of course fail. This is not how MySQL did it so the option doesn't deliver on it's promises. If OLD_MODE_ZERO_DATE_TIME_CAST is not set datetime_to_time_with_warn() is called, which will convert the datetime to a time value relative to the current time, which doesn't make any sense to me (No other database seems to do it this way and it doesn't match how insert is working). This function also tests the above flag, which is pointless in this context as it was already tested in the calling function. We only come here if the flag is set and all date parts are zero, which doesn't help this particular problem. How things should work: We should be compatible with the other databases. Same transformation of constants should happen on compare as it happens on insert. Conclusion from above is that when comparing a datetime constant with a time, we should only compare the time part. Suggestion: in Item_get_time_with_conversion() replace call to datetime_to_time_with_warn() with datetime_to_time() . Add tests to the test suite to fully test all usage of ZERO_DATE_TIME_CAST Add to https://mariadb.com/kb/en/mariadb/time the rules for how date columns are compared with constants. Also test comparison with 20020103133435 Test what happens with UPDATE and DELETE. Both with comparison against key and normal field. Open questions: What should happen if we compare a time field with a datetime field? On insert we in MariaDB copy the time value part, like we do with constant strings.

Alexander Barkov added a comment - 2016-02-10 10:35 - edited

Some background:

The SQL standard does not allow direct copying or comparison between TIME and DATETIME/TIMESTAMP.
It requires explicit CAST. The intention of the recent (nearly 3-4 years ago) changes made in MySQL (and then in MariaDB) were to reproduce this automatic CAST.

The CAST from TIME to DATETIME/TIMESTAMP works as follows:

If TD is the datetime data type TIMESTAMP WITHOUT TIME ZONE...
If SD is TIME WITHOUT TIME ZONE, then the <primary datetime field>s year, month, and day of TV are set to their respective values in an execution of CURRENT_DATE and the <primary datetime field>s hour, minute, and second of TV are set to their respective values in SV, with implementation-defined rounding or truncation if necessary.

Note, the SQL standard also supports only time in the range 00:00:00..23:59:59.
So our CAST from TIME to DATETIME/TIMESTAMP actually adds CURRENT_DATE to the TIME value
(instead of just copying the YYYY-MM-DD part). This is needed for consistent behavior of TIME values greater than 23:59:59 and less than 00:00:00.

So

INSERT INTO t1 (datetime_or_timestamp_column) VALUES (TIME'10:20:30');

SELECT * FROM t1 WHERE datetime_or_timestamp_column = TIME'10:20:30';

was intended to work as:

INSERT INTO t1 (datetime_or_timestamp_column) VALUES (CAST(TIME'10:20:30' AS DATETIME));

SELECT * FROM t1 WHERE datetime_or_timestamp_column = CAST(TIME'10:20:30' AS DATETIME);

If in some cases does not work that way, it should be fixed.

Alexander Barkov added a comment - 2016-02-10 10:35 - edited Some background: The SQL standard does not allow direct copying or comparison between TIME and DATETIME/TIMESTAMP. It requires explicit CAST. The intention of the recent (nearly 3-4 years ago) changes made in MySQL (and then in MariaDB) were to reproduce this automatic CAST. The CAST from TIME to DATETIME/TIMESTAMP works as follows: If TD is the datetime data type TIMESTAMP WITHOUT TIME ZONE... If SD is TIME WITHOUT TIME ZONE, then the <primary datetime field>s year, month, and day of TV are set to their respective values in an execution of CURRENT_DATE and the <primary datetime field>s hour, minute, and second of TV are set to their respective values in SV, with implementation-defined rounding or truncation if necessary. Note, the SQL standard also supports only time in the range 00:00:00..23:59:59. So our CAST from TIME to DATETIME/TIMESTAMP actually adds CURRENT_DATE to the TIME value (instead of just copying the YYYY-MM-DD part). This is needed for consistent behavior of TIME values greater than 23:59:59 and less than 00:00:00. So INSERT INTO t1 (datetime_or_timestamp_column) VALUES (TIME'10:20:30'); SELECT * FROM t1 WHERE datetime_or_timestamp_column = TIME'10:20:30'; was intended to work as: INSERT INTO t1 (datetime_or_timestamp_column) VALUES (CAST(TIME'10:20:30' AS DATETIME)); SELECT * FROM t1 WHERE datetime_or_timestamp_column = CAST(TIME'10:20:30' AS DATETIME); If in some cases does not work that way, it should be fixed.

Alexander Barkov added a comment - 2016-02-10 10:45 - edited

Also, I think we should add a note whenever automatic conversion between TIME and TIMESTAMP/DATETIME happens (both directions),
and the warning should suggest to use an EXPLICIT cast to avoid unexpected results.

Alexander Barkov added a comment - 2016-02-10 10:45 - edited Also, I think we should add a note whenever automatic conversion between TIME and TIMESTAMP/DATETIME happens (both directions), and the warning should suggest to use an EXPLICIT cast to avoid unexpected results.

Alexander Barkov added a comment - 2016-02-10 11:01 - edited

The SQL standard suggests to through away the DATE part when casting from TIMESTAMP to TIME:

15) If TD is the datetime data type TIME WITHOUT TIME ZONE, then ...
d) If SD is TIMESTAMP WITHOUT TIME ZONE, then TV is the hour, minute, and second <primary
datetime field>s of SV, with implementation-defined rounding or truncation if necessary.

We do the same on INSERT:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a TIME);

INSERT INTO t1 VALUES ('2001-10-10 20:30:40');

SHOW WARNINGS;

SELECT * FROM t1;

+-------+------+----------------------------------------+

| Level | Code | Message                                |

+-------+------+----------------------------------------+

| Note  | 1265 | Data truncated for column 'a' at row 1 |

+-------+------+----------------------------------------+

+----------+

| a        |

+----------+

| 20:30:40 |

+----------+

The question is what should happen in these cases:

SELECT * FROM t1 WHERE time_field = timestamp_or_datetime_expression;

SELECT * FROM t1 WHERE timestamp_of_datetime_field = time_expression;

SELECT * FROM t1 WHERE timestamp_of_datetime_field = time_field;

Should we cast both sides to TIME or to TIMESTAMP/DATETIME?

The SQL standard says:
Items of type datetime are comparable only if they have the same <primary datetime field>s.

Alexander Barkov added a comment - 2016-02-10 11:01 - edited The SQL standard suggests to through away the DATE part when casting from TIMESTAMP to TIME: 15) If TD is the datetime data type TIME WITHOUT TIME ZONE, then ... d) If SD is TIMESTAMP WITHOUT TIME ZONE, then TV is the hour, minute, and second <primary datetime field>s of SV, with implementation-defined rounding or truncation if necessary. We do the same on INSERT: DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a TIME); INSERT INTO t1 VALUES ('2001-10-10 20:30:40'); SHOW WARNINGS; SELECT * FROM t1; +-------+------+----------------------------------------+ | Level | Code | Message | +-------+------+----------------------------------------+ | Note | 1265 | Data truncated for column 'a' at row 1 | +-------+------+----------------------------------------+ +----------+ | a | +----------+ | 20:30:40 | +----------+ The question is what should happen in these cases: SELECT * FROM t1 WHERE time_field = timestamp_or_datetime_expression; SELECT * FROM t1 WHERE timestamp_of_datetime_field = time_expression; SELECT * FROM t1 WHERE timestamp_of_datetime_field = time_field; Should we cast both sides to TIME or to TIMESTAMP/DATETIME? The SQL standard says: Items of type datetime are comparable only if they have the same <primary datetime field>s.

Alexander Barkov made changes - 2016-02-10 11:30

Assignee

Alexander Barkov [ bar ]

Sergei Golubchik added a comment - 2016-02-10 12:01

How other data types behave:

In comparing CHAR(4) and CHAR(10), the shorter string is expanded, not the longer truncated:
SELECT 'aaa' = 'aaa a';
In comparing DECIMAL(6,5) and DECIMAL(10,5) the shorter number is expanded, not the longer truncated.
SELECT 5.6789 = 12345.6789;
in comparing two strings in different character sets, say, in latin1 and utf8, the string in a more limiting character set is converted to a string in a more generic character set (if possible), not the other way around.

All the above also work with columns and tables, like

create table t1 (a char(5), b char(10));

create table t2 (a decimal(6,5), b decimal (10,5));

create table t3 (a char(10) charset latin1, b char(10) charset utf8);

In all these cases the conversion is done in a direction to avoid data loss.

Sergei Golubchik added a comment - 2016-02-10 12:01 How other data types behave: In comparing CHAR(4) and CHAR(10), the shorter string is expanded, not the longer truncated: SELECT 'aaa' = 'aaa a' ; In comparing DECIMAL(6,5) and DECIMAL(10,5) the shorter number is expanded, not the longer truncated. SELECT 5.6789 = 12345.6789; in comparing two strings in different character sets, say, in latin1 and utf8, the string in a more limiting character set is converted to a string in a more generic character set (if possible), not the other way around. All the above also work with columns and tables, like create table t1 (a char (5), b char (10)); create table t2 (a decimal (6,5), b decimal (10,5)); create table t3 (a char (10) charset latin1, b char (10) charset utf8); In all these cases the conversion is done in a direction to avoid data loss.

Alexander Barkov added a comment - 2016-02-10 12:01 - edited

PostgreSQL follows the standard (almost):

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a time, b timestamp);

INSERT INTO t1 VALUES ('10:20:30','2001-01-01 10:20:30');

DROP TABLE IF EXISTS t2;

CREATE TABLE t2 (c VARCHAR(20));

INSERT INTO t2 VALUES ('10:20:30'),('2001-01-01 10:20:30');

SELECT * FROM t1 WHERE a='2001-01-01 10:20:30';

    a     |          b

----------+---------------------

 10:20:30 | 2001-01-01 10:20:30

SELECT * FROM t1 WHERE b='10:20:30';

ERROR:  invalid input syntax for type timestamp: "10:20:30"

SELECT * FROM t1 WHERE a=TIMESTAMP'2001-01-01 10:20:30';

ERROR:  operator does not exist: time without time zone = timestamp without time zone

SELECT * FROM t1 WHERE b=TIME'10:20:30';

ERROR:  operator does not exist: timestamp without time zone = time without time zone

SELECT * FROM t1 WHERE a=b;

ERROR:  operator does not exist: time without time zone = timestamp without time zone

SELECT * FROM t1 WHERE a=COALESCE(TIMESTAMP'2001-01-01 10:20:30');

ERROR:  operator does not exist: time without time zone = timestamp without time zone

SELECT * FROM t1 WHERE a=COALESCE(TIMESTAMP'2001-01-01 10:20:30');

ERROR:  operator does not exist: time without time zone = timestamp without time zone

-- Now compare temporal to VARCHAR

SELECT * FROM t1,t2 WHERE a=c;

ERROR:  operator does not exist: time without time zone = character varying

SELECT * FROM t1,t2 WHERE b=c;

ERROR:  operator does not exist: timestamp without time zone = character varying

SELECT TIME'10:20:30'=c FROM t2;

ERROR:  operator does not exist: time without time zone = character varying

SELECT TIMESTAMP'2001-01-01 10:20:30'=c FROM t2;

ERROR:  operator does not exist: timestamp without time zone = character varying

Notice, comparison of different types is generally not allowed, like the standard suggests.

Comparing a TIME field to a TIMESTAMP-alike typeless string constant is possible though (but not the other way around!). It would be good to find an explanation in the PostgreSQL documentation about this.

Alexander Barkov added a comment - 2016-02-10 12:01 - edited PostgreSQL follows the standard (almost): DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a time, b timestamp); INSERT INTO t1 VALUES ('10:20:30','2001-01-01 10:20:30'); DROP TABLE IF EXISTS t2; CREATE TABLE t2 (c VARCHAR(20)); INSERT INTO t2 VALUES ('10:20:30'),('2001-01-01 10:20:30'); SELECT * FROM t1 WHERE a='2001-01-01 10:20:30'; a | b ----------+--------------------- 10:20:30 | 2001-01-01 10:20:30 SELECT * FROM t1 WHERE b='10:20:30'; ERROR: invalid input syntax for type timestamp: "10:20:30" SELECT * FROM t1 WHERE a=TIMESTAMP'2001-01-01 10:20:30'; ERROR: operator does not exist: time without time zone = timestamp without time zone SELECT * FROM t1 WHERE b=TIME'10:20:30'; ERROR: operator does not exist: timestamp without time zone = time without time zone SELECT * FROM t1 WHERE a=b; ERROR: operator does not exist: time without time zone = timestamp without time zone SELECT * FROM t1 WHERE a=COALESCE(TIMESTAMP'2001-01-01 10:20:30'); ERROR: operator does not exist: time without time zone = timestamp without time zone SELECT * FROM t1 WHERE a=COALESCE(TIMESTAMP'2001-01-01 10:20:30'); ERROR: operator does not exist: time without time zone = timestamp without time zone -- Now compare temporal to VARCHAR SELECT * FROM t1,t2 WHERE a=c; ERROR: operator does not exist: time without time zone = character varying SELECT * FROM t1,t2 WHERE b=c; ERROR: operator does not exist: timestamp without time zone = character varying SELECT TIME'10:20:30'=c FROM t2; ERROR: operator does not exist: time without time zone = character varying SELECT TIMESTAMP'2001-01-01 10:20:30'=c FROM t2; ERROR: operator does not exist: timestamp without time zone = character varying Notice, comparison of different types is generally not allowed, like the standard suggests. Comparing a TIME field to a TIMESTAMP-alike typeless string constant is possible though (but not the other way around!). It would be good to find an explanation in the PostgreSQL documentation about this.

Alexander Barkov made changes - 2016-02-10 16:02

Affects Version/s		10.0.23 [ 20401 ]
Affects Version/s		5.5.47 [ 20300 ]
Affects Version/s		5.3.13 [ 12602 ]

Alexander Barkov added a comment - 2016-02-10 16:28

MySQL-5.7:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a TIME, b DATETIME);

INSERT INTO t1 VALUES (CURRENT_TIME, CURRENT_TIMESTAMP), (ADDTIME(CURRENT_TIME,'24:00:00'), DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 24 HOUR));

SELECT *, a=b FROM t1;

returns

+----------+---------------------+------+

| a        | b                   | a=b  |

+----------+---------------------+------+

| 18:27:32 | 2016-02-10 18:27:32 |    1 |

| 42:27:32 | 2016-02-11 18:27:32 |    1 |

+----------+---------------------+------+

Conversion from TIME to DATETIME happens.

Alexander Barkov added a comment - 2016-02-10 16:28 MySQL-5.7: DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a TIME, b DATETIME); INSERT INTO t1 VALUES (CURRENT_TIME, CURRENT_TIMESTAMP), (ADDTIME(CURRENT_TIME,'24:00:00'), DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 24 HOUR)); SELECT *, a=b FROM t1; returns +----------+---------------------+------+ | a | b | a=b | +----------+---------------------+------+ | 18:27:32 | 2016-02-10 18:27:32 | 1 | | 42:27:32 | 2016-02-11 18:27:32 | 1 | +----------+---------------------+------+ Conversion from TIME to DATETIME happens.

Alexander Barkov added a comment - 2016-02-11 06:19 - edited

SQL Server 2012 works as follows:

DROP TABLE t1;

CREATE TABLE t1 (t time, dt datetime);

INSERT INTO t1 VALUES ('10:20:30','1900-01-01 10:20:30');

INSERT INTO t1 VALUES ('10:20:30','2001-01-01 10:20:30');

INSERT INTO t1 VALUES ('10:20:30','2016-02-11 10:20:30');

DROP TABLE t2;

CREATE TABLE t2 (c VARCHAR(20));

INSERT INTO t2 VALUES ('10:20:30')

INSERT INTO t2 VALUES ('1900-01-01 10:20:30');

INSERT INTO t2 VALUES ('2001-01-01 10:20:30');

-- TIME field + literal: compared as TIME

SELECT * FROM t1 WHERE t='2000-01-01 10:20:30';

SELECT * FROM t1 WHERE t='2001-01-01 10:20:30';

SELECT * FROM t1 WHERE t={dt '2001-01-01 10:20:30'};

+-----------------+------------------------+

| t               | dt                     |

+-----------------+------------------------+

| 10:20:30.0000000| 1900-01-01 10:20:30.000|

| 10:20:30.0000000| 2001-01-01 10:20:30.000|

| 10:20:30.0000000| 2016-02-11 10:20:30.000|

+-----------------+------------------------+

-- COALESCE(time_field) + literal: compared as TIME

SELECT * FROM t1 WHERE t=COALESCE({dt '2001-01-01 10:20:30'},NULL);

SELECT * FROM t1 WHERE COALESCE(t,NULL)='2000-01-01 10:20:30';

SELECT * FROM t1 WHERE COALESCE(t,NULL)='2001-01-01 10:20:30';

SELECT * FROM t1 WHERE COALESCE(t,NULL)={dt '2001-01-01 10:20:30'};

SELECT * FROM t1 WHERE COALESCE(t,NULL)=COALESCE({dt '2001-01-01 10:20:30'},NULL);

+-----------------+------------------------+

| t               | dt                     |

+-----------------+------------------------+

| 10:20:30.0000000| 1900-01-01 10:20:30.000|

| 10:20:30.0000000| 2001-01-01 10:20:30.000|

| 10:20:30.0000000| 2016-02-11 10:20:30.000|

+-----------------+------------------------+

-- DATETIME field + literal: compared is done by DATETIME

SELECT * FROM t1 WHERE dt='10:20:30';

SELECT * FROM t1 WHERE dt={t'10:20:30'};

+-----------------+------------------------+

| t               | dt                     |

+-----------------+------------------------+

| 10:20:30.0000000| 1900-01-01 10:20:30.000|

+-----------------+------------------------+

-- COALESCE(datetime_field) + literal: compared as DATETIME

SELECT * FROM t1 WHERE dt=COALESCE({t '10:20:30'},NULL);

SELECT * FROM t1 WHERE COALESCE(dt,NULL)='10:20:30';

SELECT * FROM t1 WHERE COALESCE(dt,NULL)={t '10:20:30'};

SELECT * FROM t1 WHERE COALESCE(dt,NULL)=COALESCE({t '10:20:30'},NULL);

+-----------------+------------------------+

| t               | dt                     |

+-----------------+------------------------+

| 10:20:30.0000000| 1900-01-01 10:20:30.000|

+-----------------+------------------------+

-- DATETIME and DATETIME-alike literal + TIME function: compared as TIME

SELECT CASE WHEN '2001-01-01 10:20:30' = TIMEFROMPARTS(10,20,30,0,0) THEN 1 ELSE 0 END;

SELECT CASE WHEN {dt'2001-01-01 10:20:30'} = TIMEFROMPARTS(10,20,30,0,0) THEN 1 ELSE 0 END;

+------------+

|            |

+------------+

| 1          |

+------------+

-- These queries mixing TIME and DATETIME arguments return an error:

-- The data types time and datetime are incompatible in the equal to operator.

SELECT * FROM t1 WHERE t=dt;

SELECT * FROM t1 WHERE dt=TIMEFROMPARTS(10,20,30,0,0);

SELECT * FROM t1 WHERE t=DATETIMEFROMPARTS(1900,1,1,10,20,30,0);

SELECT * FROM t1 WHERE t=COALESCE(dt,NULL);

SELECT * FROM t1 WHERE COALESCE(t,NULL)=dt;

SELECT * FROM t1 WHERE COALESCE(t,NULL)=COALESCE(dt,NULL);

SELECT * FROM t1 WHERE dt=COALESCE(TIMEFROMPARTS(10,20,30,0,0),NULL);

SELECT * FROM t1 WHERE t=COALESCE(DATETIMEFROMPARTS(1900,1,1,10,20,30,0),NULL);

SELECT * FROM t1 WHERE COALESCE(dt,NULL)=TIMEFROMPARTS(10,20,30,0,0);

SELECT * FROM t1 WHERE COALESCE(t,NULL)=DATETIMEFROMPARTS(1900,1,1,10,20,30,0);

SELECT * FROM t1 WHERE dt=COALESCE(TIMEFROMPARTS(10,20,30,0,0),'2001-01-01 10:20:30');

SELECT * FROM t1 WHERE dt=COALESCE('2001-01-01 10:20:30',TIMEFROMPARTS(10,20,30,0,0));

SELECT CASE WHEN CAST('2001-01-01 10:20:30' AS DATETIME) = CAST('10:20:30' AS TIME) THEN 1 ELSE 0 END;

SELECT CASE WHEN CAST('2001-01-01 10:20:30' AS DATETIME) = TIMEFROMPARTS(10,20,30,0,0) THEN 1 ELSE 0 END;

-- TIME and TIME-alike literal + DATETIME function: compared as DATETIME

SELECT CASE WHEN {t'10:20:30'}=DATETIMEFROMPARTS(1900,1,1,10,20,30,0) THEN 1 ELSE 0 END AS c1;

SELECT CASE WHEN '10:20:30'=DATETIMEFROMPARTS(1900,1,1,10,20,30,0) THEN 1 ELSE 0 END AS c1;

+------------+

| c1         |

+------------+

| 1          |

+------------+

SELECT CASE WHEN {t'10:20:30'}=DATETIMEFROMPARTS(1901,1,1,10,20,30,0) THEN 1 ELSE 0 END;

SELECT CASE WHEN '10:20:30'=DATETIMEFROMPARTS(1901,1,1,10,20,30,0) THEN 1 ELSE 0 END;

+------------+

|            |

+------------+

| 0          |

+------------+

-- TIME and TIME-alike literals + DATETIME cast: compares as DATETIME

SELECT CASE WHEN '2001-01-01 10:20:30' = CAST(TIMEFROMPARTS(10,20,30,0,0) AS DATETIME) THEN 1 ELSE 0 END;

SELECT CASE WHEN {dt'2001-01-01 10:20:30'} = CAST(TIMEFROMPARTS(10,20,30,0,0) AS DATETIME) THEN 1 ELSE 0 END;

+------------+

|            |

+------------+

| 0          |

+------------+

SELECT CASE WHEN {dt'1900-01-01 10:20:30'} = CAST(TIMEFROMPARTS(10,20,30,0,0) AS DATETIME) THEN 1 ELSE 0 END;

+------------+

|            |

+------------+

| 1          |

+------------+

-- DATETIME literal + INT: an error (notice the literal is actually VARCHAR not DATETIME):

-- Conversion failed when converting the varchar value '2001-01-01 10:20:30' to data type int

SELECT CASE WHEN {dt'2001-01-01 10:20:30'} = 1 THEN 1 ELSE 0 END;

-- TIME + VARCHAR, compared as TIME

SELECT t,c,CASE WHEN t=c THEN 1 ELSE 0 END FROM t1,t2;

SELECT t,c,CASE WHEN COALESCE(t,NULL)=c THEN 1 ELSE 0 END FROM t1,t2;

SELECT t,c,CASE WHEN COALESCE(t,NULL)=COALESCE(c,NULL) THEN 1 ELSE 0 END FROM t1,t2;

SELECT t,c,CASE WHEN t=COALESCE(c,NULL) THEN 1 ELSE 0 END FROM t1,t2;

+-----------------+---------------------+------------+

| t               | c                   |            |

+-----------------+---------------------+------------+

| 10:20:30.0000000| 10:20:30            | 1          |

| 10:20:30.0000000| 10:20:30            | 1          |

| 10:20:30.0000000| 10:20:30            | 1          |

| 10:20:30.0000000| 1900-01-01 10:20:30 | 1          |

| 10:20:30.0000000| 1900-01-01 10:20:30 | 1          |

| 10:20:30.0000000| 1900-01-01 10:20:30 | 1          |

| 10:20:30.0000000| 2001-01-01 10:20:30 | 1          |

| 10:20:30.0000000| 2001-01-01 10:20:30 | 1          |

| 10:20:30.0000000| 2001-01-01 10:20:30 | 1          |

+-----------------+---------------------+------------+

-- TIME + VARCHAR function, compared as TIME

SELECT c,CASE WHEN TIMEFROMPARTS(10,20,30,0,0)=c THEN 1 ELSE 0 END FROM t2;

+---------------------+------------+

| c                   |            |

+---------------------+------------+

| 10:20:30            | 1          |

| 1900-01-01 10:20:30 | 1          |

| 2001-01-01 10:20:30 | 1          |

+---------------------+------------+

-- DATETIME + VARCHAR, compared as DATETIME

SELECT dt,c,CASE WHEN dt=c THEN 1 ELSE 0 END FROM t1,t2;

SELECT dt,c,CASE WHEN COALESCE(dt,NULL)=c THEN 1 ELSE 0 END FROM t1,t2;

SELECT dt,c,CASE WHEN COALESCE(dt,NULL)=COALESCE(c,NULL) THEN 1 ELSE 0 END FROM t1,t2;

SELECT dt,c,CASE WHEN dt=COALESCE(c,NULL) THEN 1 ELSE 0 END FROM t1,t2;

+------------------------+---------------------+------------+

| dt                     | c                   |            |

+------------------------+---------------------+------------+

| 1900-01-01 10:20:30.000| 10:20:30            | 1          |

| 2001-01-01 10:20:30.000| 10:20:30            | 0          |

| 2016-02-11 10:20:30.000| 10:20:30            | 0          |

| 1900-01-01 10:20:30.000| 1900-01-01 10:20:30 | 1          |

| 2001-01-01 10:20:30.000| 1900-01-01 10:20:30 | 0          |

| 2016-02-11 10:20:30.000| 1900-01-01 10:20:30 | 0          |

| 1900-01-01 10:20:30.000| 2001-01-01 10:20:30 | 0          |

| 2001-01-01 10:20:30.000| 2001-01-01 10:20:30 | 1          |

| 2016-02-11 10:20:30.000| 2001-01-01 10:20:30 | 0          |

+------------------------+---------------------+------------+

-- TIME + VARCHAR function, compared as TIME

SELECT c,CASE WHEN DATETIMEFROMPARTS(1900,1,1,10,20,30,0)=c THEN 1 ELSE 0 END FROM t2;

+---------------------+------------+

| c                   |            |

+---------------------+------------+

| 10:20:30            | 1          |

| 1900-01-01 10:20:30 | 1          |

| 2001-01-01 10:20:30 | 0          |

+---------------------+------------+

Summary:

Comparison of TIME + VARCHAR is done as TIME
Comparison of DATETIME + VARCHAR is done as DATETIME
Comparison of TIME and DATETIME is not allowed
Comparison of TIME and INT is not allowed
Comparison of DATETIME and INT is allowed (not sure how exactly it works though)
Temporal literals {t'10:20:30'}
have data type VARCHAR

Alexander Barkov added a comment - 2016-02-11 06:19 - edited SQL Server 2012 works as follows: DROP TABLE t1; CREATE TABLE t1 (t time, dt datetime); INSERT INTO t1 VALUES ('10:20:30','1900-01-01 10:20:30'); INSERT INTO t1 VALUES ('10:20:30','2001-01-01 10:20:30'); INSERT INTO t1 VALUES ('10:20:30','2016-02-11 10:20:30'); DROP TABLE t2; CREATE TABLE t2 (c VARCHAR(20)); INSERT INTO t2 VALUES ('10:20:30') INSERT INTO t2 VALUES ('1900-01-01 10:20:30'); INSERT INTO t2 VALUES ('2001-01-01 10:20:30'); -- TIME field + literal: compared as TIME SELECT * FROM t1 WHERE t='2000-01-01 10:20:30'; SELECT * FROM t1 WHERE t='2001-01-01 10:20:30'; SELECT * FROM t1 WHERE t={dt '2001-01-01 10:20:30'}; +-----------------+------------------------+ | t | dt | +-----------------+------------------------+ | 10:20:30.0000000| 1900-01-01 10:20:30.000| | 10:20:30.0000000| 2001-01-01 10:20:30.000| | 10:20:30.0000000| 2016-02-11 10:20:30.000| +-----------------+------------------------+ -- COALESCE(time_field) + literal: compared as TIME SELECT * FROM t1 WHERE t=COALESCE({dt '2001-01-01 10:20:30'},NULL); SELECT * FROM t1 WHERE COALESCE(t,NULL)='2000-01-01 10:20:30'; SELECT * FROM t1 WHERE COALESCE(t,NULL)='2001-01-01 10:20:30'; SELECT * FROM t1 WHERE COALESCE(t,NULL)={dt '2001-01-01 10:20:30'}; SELECT * FROM t1 WHERE COALESCE(t,NULL)=COALESCE({dt '2001-01-01 10:20:30'},NULL); +-----------------+------------------------+ | t | dt | +-----------------+------------------------+ | 10:20:30.0000000| 1900-01-01 10:20:30.000| | 10:20:30.0000000| 2001-01-01 10:20:30.000| | 10:20:30.0000000| 2016-02-11 10:20:30.000| +-----------------+------------------------+ -- DATETIME field + literal: compared is done by DATETIME SELECT * FROM t1 WHERE dt='10:20:30'; SELECT * FROM t1 WHERE dt={t'10:20:30'}; +-----------------+------------------------+ | t | dt | +-----------------+------------------------+ | 10:20:30.0000000| 1900-01-01 10:20:30.000| +-----------------+------------------------+ -- COALESCE(datetime_field) + literal: compared as DATETIME SELECT * FROM t1 WHERE dt=COALESCE({t '10:20:30'},NULL); SELECT * FROM t1 WHERE COALESCE(dt,NULL)='10:20:30'; SELECT * FROM t1 WHERE COALESCE(dt,NULL)={t '10:20:30'}; SELECT * FROM t1 WHERE COALESCE(dt,NULL)=COALESCE({t '10:20:30'},NULL); +-----------------+------------------------+ | t | dt | +-----------------+------------------------+ | 10:20:30.0000000| 1900-01-01 10:20:30.000| +-----------------+------------------------+ -- DATETIME and DATETIME-alike literal + TIME function: compared as TIME SELECT CASE WHEN '2001-01-01 10:20:30' = TIMEFROMPARTS(10,20,30,0,0) THEN 1 ELSE 0 END; SELECT CASE WHEN {dt'2001-01-01 10:20:30'} = TIMEFROMPARTS(10,20,30,0,0) THEN 1 ELSE 0 END; +------------+ | | +------------+ | 1 | +------------+ -- These queries mixing TIME and DATETIME arguments return an error: -- The data types time and datetime are incompatible in the equal to operator. SELECT * FROM t1 WHERE t=dt; SELECT * FROM t1 WHERE dt=TIMEFROMPARTS(10,20,30,0,0); SELECT * FROM t1 WHERE t=DATETIMEFROMPARTS(1900,1,1,10,20,30,0); SELECT * FROM t1 WHERE t=COALESCE(dt,NULL); SELECT * FROM t1 WHERE COALESCE(t,NULL)=dt; SELECT * FROM t1 WHERE COALESCE(t,NULL)=COALESCE(dt,NULL); SELECT * FROM t1 WHERE dt=COALESCE(TIMEFROMPARTS(10,20,30,0,0),NULL); SELECT * FROM t1 WHERE t=COALESCE(DATETIMEFROMPARTS(1900,1,1,10,20,30,0),NULL); SELECT * FROM t1 WHERE COALESCE(dt,NULL)=TIMEFROMPARTS(10,20,30,0,0); SELECT * FROM t1 WHERE COALESCE(t,NULL)=DATETIMEFROMPARTS(1900,1,1,10,20,30,0); SELECT * FROM t1 WHERE dt=COALESCE(TIMEFROMPARTS(10,20,30,0,0),'2001-01-01 10:20:30'); SELECT * FROM t1 WHERE dt=COALESCE('2001-01-01 10:20:30',TIMEFROMPARTS(10,20,30,0,0)); SELECT CASE WHEN CAST('2001-01-01 10:20:30' AS DATETIME) = CAST('10:20:30' AS TIME) THEN 1 ELSE 0 END; SELECT CASE WHEN CAST('2001-01-01 10:20:30' AS DATETIME) = TIMEFROMPARTS(10,20,30,0,0) THEN 1 ELSE 0 END; -- TIME and TIME-alike literal + DATETIME function: compared as DATETIME SELECT CASE WHEN {t'10:20:30'}=DATETIMEFROMPARTS(1900,1,1,10,20,30,0) THEN 1 ELSE 0 END AS c1; SELECT CASE WHEN '10:20:30'=DATETIMEFROMPARTS(1900,1,1,10,20,30,0) THEN 1 ELSE 0 END AS c1; +------------+ | c1 | +------------+ | 1 | +------------+ SELECT CASE WHEN {t'10:20:30'}=DATETIMEFROMPARTS(1901,1,1,10,20,30,0) THEN 1 ELSE 0 END; SELECT CASE WHEN '10:20:30'=DATETIMEFROMPARTS(1901,1,1,10,20,30,0) THEN 1 ELSE 0 END; +------------+ | | +------------+ | 0 | +------------+ -- TIME and TIME-alike literals + DATETIME cast: compares as DATETIME SELECT CASE WHEN '2001-01-01 10:20:30' = CAST(TIMEFROMPARTS(10,20,30,0,0) AS DATETIME) THEN 1 ELSE 0 END; SELECT CASE WHEN {dt'2001-01-01 10:20:30'} = CAST(TIMEFROMPARTS(10,20,30,0,0) AS DATETIME) THEN 1 ELSE 0 END; +------------+ | | +------------+ | 0 | +------------+ SELECT CASE WHEN {dt'1900-01-01 10:20:30'} = CAST(TIMEFROMPARTS(10,20,30,0,0) AS DATETIME) THEN 1 ELSE 0 END; +------------+ | | +------------+ | 1 | +------------+ -- DATETIME literal + INT: an error (notice the literal is actually VARCHAR not DATETIME): -- Conversion failed when converting the varchar value '2001-01-01 10:20:30' to data type int SELECT CASE WHEN {dt'2001-01-01 10:20:30'} = 1 THEN 1 ELSE 0 END; -- TIME + VARCHAR, compared as TIME SELECT t,c,CASE WHEN t=c THEN 1 ELSE 0 END FROM t1,t2; SELECT t,c,CASE WHEN COALESCE(t,NULL)=c THEN 1 ELSE 0 END FROM t1,t2; SELECT t,c,CASE WHEN COALESCE(t,NULL)=COALESCE(c,NULL) THEN 1 ELSE 0 END FROM t1,t2; SELECT t,c,CASE WHEN t=COALESCE(c,NULL) THEN 1 ELSE 0 END FROM t1,t2; +-----------------+---------------------+------------+ | t | c | | +-----------------+---------------------+------------+ | 10:20:30.0000000| 10:20:30 | 1 | | 10:20:30.0000000| 10:20:30 | 1 | | 10:20:30.0000000| 10:20:30 | 1 | | 10:20:30.0000000| 1900-01-01 10:20:30 | 1 | | 10:20:30.0000000| 1900-01-01 10:20:30 | 1 | | 10:20:30.0000000| 1900-01-01 10:20:30 | 1 | | 10:20:30.0000000| 2001-01-01 10:20:30 | 1 | | 10:20:30.0000000| 2001-01-01 10:20:30 | 1 | | 10:20:30.0000000| 2001-01-01 10:20:30 | 1 | +-----------------+---------------------+------------+ -- TIME + VARCHAR function, compared as TIME SELECT c,CASE WHEN TIMEFROMPARTS(10,20,30,0,0)=c THEN 1 ELSE 0 END FROM t2; +---------------------+------------+ | c | | +---------------------+------------+ | 10:20:30 | 1 | | 1900-01-01 10:20:30 | 1 | | 2001-01-01 10:20:30 | 1 | +---------------------+------------+ -- DATETIME + VARCHAR, compared as DATETIME SELECT dt,c,CASE WHEN dt=c THEN 1 ELSE 0 END FROM t1,t2; SELECT dt,c,CASE WHEN COALESCE(dt,NULL)=c THEN 1 ELSE 0 END FROM t1,t2; SELECT dt,c,CASE WHEN COALESCE(dt,NULL)=COALESCE(c,NULL) THEN 1 ELSE 0 END FROM t1,t2; SELECT dt,c,CASE WHEN dt=COALESCE(c,NULL) THEN 1 ELSE 0 END FROM t1,t2; +------------------------+---------------------+------------+ | dt | c | | +------------------------+---------------------+------------+ | 1900-01-01 10:20:30.000| 10:20:30 | 1 | | 2001-01-01 10:20:30.000| 10:20:30 | 0 | | 2016-02-11 10:20:30.000| 10:20:30 | 0 | | 1900-01-01 10:20:30.000| 1900-01-01 10:20:30 | 1 | | 2001-01-01 10:20:30.000| 1900-01-01 10:20:30 | 0 | | 2016-02-11 10:20:30.000| 1900-01-01 10:20:30 | 0 | | 1900-01-01 10:20:30.000| 2001-01-01 10:20:30 | 0 | | 2001-01-01 10:20:30.000| 2001-01-01 10:20:30 | 1 | | 2016-02-11 10:20:30.000| 2001-01-01 10:20:30 | 0 | +------------------------+---------------------+------------+ -- TIME + VARCHAR function, compared as TIME SELECT c,CASE WHEN DATETIMEFROMPARTS(1900,1,1,10,20,30,0)=c THEN 1 ELSE 0 END FROM t2; +---------------------+------------+ | c | | +---------------------+------------+ | 10:20:30 | 1 | | 1900-01-01 10:20:30 | 1 | | 2001-01-01 10:20:30 | 0 | +---------------------+------------+ Summary: Comparison of TIME + VARCHAR is done as TIME Comparison of DATETIME + VARCHAR is done as DATETIME Comparison of TIME and DATETIME is not allowed Comparison of TIME and INT is not allowed Comparison of DATETIME and INT is allowed (not sure how exactly it works though) Temporal literals {t'10:20:30'} have data type VARCHAR

Alexander Barkov added a comment - 2016-02-11 08:30 - edited

Oracle 11.2 works as follows:

alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss.ff';

DROP TABLE t1;

CREATE TABLE t1 (ts timestamp(3));

INSERT INTO t1 VALUES ('2001-01-01 10:20:30.000');

DROP TABLE t2;

CREATE TABLE t2 (c VARCHAR(30));

INSERT INTO t2 VALUES ('2001-01-01 10:20:30');

INSERT INTO t2 VALUES ('2001-01-01 10:20:30.0');

-- TIMESTAMP field or function + string literal: compared as TIMESTAMP

SELECT * FROM t1 WHERE ts='2001-01-01 10:20:30';

SELECT * FROM t1 WHERE COALESCE(ts,NULL)='2001-01-01 10:20:30';

> TS

> ---------------------------------------------------------------------------

> 2001-01-01 10:20:30.000

-- All these queries compare as TIMESTAMP:

SELECT c FROM t1,t2 WHERE ts=c;

SELECT c FROM t1,t2 WHERE ts=CAST(c AS CHAR(30));

SELECT c FROM t2 WHERE c=COALESCE(TIMESTAMP'2001-01-01 10:20:30',NULL);

SELECT c FROM t2 WHERE c=TIMESTAMP'2001-01-01 10:20:30';

> C

> ------------------------------

> 2001-01-01 10:20:30

> 2001-01-01 10:20:30.0

I.e. TIMESTAMP always wins VARCHAR.
Note, Oracle does not have the TIME data type.

Alexander Barkov added a comment - 2016-02-11 08:30 - edited Oracle 11.2 works as follows: alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss.ff'; DROP TABLE t1; CREATE TABLE t1 (ts timestamp(3)); INSERT INTO t1 VALUES ('2001-01-01 10:20:30.000'); DROP TABLE t2; CREATE TABLE t2 (c VARCHAR(30)); INSERT INTO t2 VALUES ('2001-01-01 10:20:30'); INSERT INTO t2 VALUES ('2001-01-01 10:20:30.0'); -- TIMESTAMP field or function + string literal: compared as TIMESTAMP SELECT * FROM t1 WHERE ts='2001-01-01 10:20:30'; SELECT * FROM t1 WHERE COALESCE(ts,NULL)='2001-01-01 10:20:30'; > TS > --------------------------------------------------------------------------- > 2001-01-01 10:20:30.000 -- All these queries compare as TIMESTAMP: SELECT c FROM t1,t2 WHERE ts=c; SELECT c FROM t1,t2 WHERE ts=CAST(c AS CHAR(30)); SELECT c FROM t2 WHERE c=COALESCE(TIMESTAMP'2001-01-01 10:20:30',NULL); SELECT c FROM t2 WHERE c=TIMESTAMP'2001-01-01 10:20:30'; > C > ------------------------------ > 2001-01-01 10:20:30 > 2001-01-01 10:20:30.0 I.e. TIMESTAMP always wins VARCHAR. Note, Oracle does not have the TIME data type.

Alexander Barkov made changes - 2017-04-17 05:16

Labels

datatype

Alexander Barkov made changes - 2017-04-17 05:16

Component/s

Temporal Types [ 11000 ]

Alexander Barkov made changes - 2017-04-17 05:16

Link

This issue blocks ~~MDEV-4912~~ [ ~~MDEV-4912~~ ]

Elena Stepanova made changes - 2017-11-05 16:13

Fix Version/s		10.0 [ 16000 ]
Fix Version/s		10.1 [ 16100 ]
Fix Version/s		10.2 [ 14601 ]

Alexander Barkov made changes - 2019-11-15 13:03

Component/s

Data types [ 13906 ]

Julien Fritsch made changes - 2019-11-18 11:41

Epic Link

MDEV-21071 [ 80504 ]

Julien Fritsch made changes - 2019-11-18 11:52

Link

This issue blocks ~~MDEV-4912~~ [ ~~MDEV-4912~~ ]

Julien Fritsch made changes - 2020-12-01 12:34

Fix Version/s

10.0 [ 16000 ]

Julien Fritsch made changes - 2020-12-01 12:38

Fix Version/s

10.1 [ 16100 ]

Sergei Golubchik made changes - 2021-12-06 21:32

Workflow

MariaDB v3 [ 73921 ]

MariaDB v4 [ 139940 ]

MariaDB Server

Selecting a time using a datetime fails

Details

Description

Attachments

Activity

People

Dates

Git Integration