[MDEV-9541] Selecting a time using a datetime fails Created: 2016-02-09  Updated: 2020-12-01

Status: Open
Project: MariaDB Server
Component/s: Data types, Temporal Types
Affects Version/s: 5.5.47, 10.0.23, 10.1.9, 5.3.13
Fix Version/s: 10.2

Type: Bug Priority: Major
Reporter: Scott Bronson Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: datatype
Environment:

Ver 15.1 Distrib 10.1.9-MariaDB, for osx10.11

Epic Link: Data type cleanups

 Description   

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.

 Comments   
Comment by Sergei Golubchik [ 2016-02-09 ]

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

Comment by Michael Widenius [ 2016-02-10 ]

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.
Comment by Alexander Barkov [ 2016-02-10 ]

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.

Comment by Alexander Barkov [ 2016-02-10 ]

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.

Comment by Alexander Barkov [ 2016-02-10 ]

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.

Comment by Sergei Golubchik [ 2016-02-10 ]

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.

Comment by Alexander Barkov [ 2016-02-10 ]

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.

Comment by Alexander Barkov [ 2016-02-10 ]

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.

Comment by Alexander Barkov [ 2016-02-11 ]

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

Comment by Alexander Barkov [ 2016-02-11 ]

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.

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