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

Details

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 5.5.47, 10.0.23, 10.1.9, 5.3.13
Fix Version/s: 10.2(EOL)
Component/s: Data types, Temporal Types
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.

Attachments

Activity

Ascending order - Click to sort in descending order

View 5 older comments

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 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.

People

Assignee:: Alexander Barkov

Reporter:: Scott Bronson

Votes:: 0 Vote for this issue

Watchers:: 5 Start watching this issue

Dates

Created:: 2016-02-09 22:17

Updated:: 2020-12-01 12:38

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server