Missing implementations of scalar function: sql_sub(<timetz>, arg2)

[monetdb-team]

Date: 2014-12-19 18:44:25 +0100
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.19.7 (Oct2014-SP1)
CC: @njnes

Last updated: 2015-01-29 14:07:37 +0100

Comment 20543

Date: 2014-12-19 18:44:25 +0100
From: Martin van Dinther <<martin.van.dinther>>

User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:34.0) Gecko/20100101 Firefox/34.0
Build Identifier:

missing a few
sql_sub(, arg2)
function implementations which do exist for
sql_sub(, arg2).

See also sql/test/pg_regress/timetz.sql
Would be good if they could be added.

Reproducible: Always

Steps to Reproduce:

CREATE TABLE TIMETZ_TBL (f1 time(2) with time zone);
INSERT INTO TIMETZ_TBL VALUES ('11:59:59.99 PM PDT');
SELECT f1 AS "Time TZ" FROM TIMETZ_TBL;
SELECT f1, sql_sub(f1, 126060.0 + 66) FROM TIMETZ_TBL;
SELECT f1, sql_sub(f1, cast(26060 as interval second)) FROM TIMETZ_TBL;
SELECT f1, sql_sub(f1, cast('23:22:21' as timetz)) FROM TIMETZ_TBL;
SELECT f1, sql_sub(f1, cast('23:22:21' as time)) FROM TIMETZ_TBL;
SELECT f1, sql_sub(cast('23:22:21' as timetz), f1) FROM TIMETZ_TBL;
DROP TABLE TIMETZ_TBL;

Actual Results:

mclient
Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased)
Database: MonetDB v11.20.0 (unreleased), 'demo'
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>CREATE TABLE TIMETZ_TBL (f1 time(2) with time zone);
operation successful (2.778ms)
sql>INSERT INTO TIMETZ_TBL VALUES ('11:59:59.99 PM PDT');
1 affected row (1.988ms)
sql>SELECT f1 AS "Time TZ" FROM TIMETZ_TBL;
+-------------------+
| Time TZ |
+===================+
| 12:59:59.99+01:00 |
+-------------------+
1 tuple (3.382ms)
sql>SELECT f1, sql_sub(f1, 126060.0 + 66) FROM TIMETZ_TBL;
SELECT: no such binary operator 'sql_sub(timetz,decimal)'
sql>SELECT f1, sql_sub(f1, cast(26060 as interval second)) FROM TIMETZ_TBL;
SELECT: no such binary operator 'sql_sub(timetz,sec_interval)'
sql>SELECT f1, sql_sub(f1, cast('23:22:21' as timetz)) FROM TIMETZ_TBL;
SELECT: no such binary operator 'sql_sub(timetz,timetz)'
sql>SELECT f1, sql_sub(f1, cast('23:22:21' as time)) FROM TIMETZ_TBL;
SELECT: no such binary operator 'sql_sub(timetz,time)'
sql>SELECT f1, sql_sub(cast('23:22:21' as timetz), f1) FROM TIMETZ_TBL;
SELECT: no such binary operator 'sql_sub(timetz,timetz)'
sql>DROP TABLE TIMETZ_TBL;
operation successful (2.137ms)

Expected Results:

no errors

Comment 20544

Date: 2014-12-22 09:17:58 +0100
From: @njnes

function added

Comment 20604

Date: 2015-01-29 14:07:37 +0100
From: @sjoerdmullender

Oct2014-SP2 has been released.