Missing implementation of scalar function: sql_sub(<date>, <month interval>)

[monetdb-team]

Date: 2014-12-12 17:23:51 +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:43 +0100

Comment 20529

Date: 2014-12-12 17:23:51 +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:

sql_add(, ) is implemented. Also
sql_sub(, ) and
sql_sub(, ) are implemented but
sql_sub(, ) is not!

For orthogonality and consistency
sql_sub(, )
should be implemented also.

Reproducible: Always

Steps to Reproduce:

1. start mserver5
2. start mclient
3. run sql commands:
   CREATE TABLE DATE_TBL (f1 date);
   INSERT INTO DATE_TBL VALUES ('2014-12-12');
   SELECT f1, sql_sub(f1, 365246060.0) FROM DATE_TBL;
   SELECT f1, sql_add(f1, cast(365246060 as interval second)) FROM DATE_TBL;
   SELECT f1, sql_sub(f1, cast(12 as interval month)) FROM DATE_TBL;
   SELECT f1, sql_sub(f1, cast(-18 as interval month)) FROM DATE_TBL;
   SELECT f1, sql_add(f1, cast(12 as interval month)) FROM DATE_TBL;
   SELECT f1, sql_add(f1, -cast(12 as interval month)) FROM DATE_TBL;
   DROP TABLE DATE_TBL;

Actual Results:

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 DATE_TBL (f1 date);
operation successful (2.730ms)
sql>INSERT INTO DATE_TBL VALUES ('2014-12-12');
1 affected row (1.058ms)
sql>SELECT f1, sql_sub(f1, 365246060.0) FROM DATE_TBL;
+------------+------------+
| f1 | sql_sub_f1 |
+============+============+
| 2014-12-12 | 2013-12-12 |
+------------+------------+
1 tuple (1.703ms)
sql>SELECT f1, sql_add(f1, cast(365246060 as interval second)) FROM DATE_TBL;
+------------+------------+
| f1 | sql_add_f1 |
+============+============+
| 2014-12-12 | 2015-12-12 |
+------------+------------+
1 tuple (1.811ms)
sql>SELECT f1, sql_sub(f1, cast(12 as interval month)) FROM DATE_TBL;
TypeException:user.s6_4[14]:'mtime.date_sub_month_interval' undefined in: _55:any := mtime.date_sub_month_interval(_53:date, _17:int)
program contains errors
sql>SELECT f1, sql_sub(f1, cast(-18 as interval month)) FROM DATE_TBL;
TypeException:user.s7_4[14]:'mtime.date_sub_month_interval' undefined in: _55:any := mtime.date_sub_month_interval(_53:date, _17:int)
program contains errors
sql>SELECT f1, sql_add(f1, cast(12 as interval month)) FROM DATE_TBL;
+------------+------------+
| f1 | sql_add_f1 |
+============+============+
| 2014-12-12 | 2015-12-12 |
+------------+------------+
1 tuple (1.257ms)
sql>SELECT f1, sql_add(f1, -cast(12 as interval month)) FROM DATE_TBL;
+------------+------------+
| f1 | sql_add_f1 |
+============+============+
| 2014-12-12 | 2013-12-12 |
+------------+------------+
1 tuple (1.393ms)
sql>DROP TABLE DATE_TBL;
operation successful (2.162ms)

Expected Results:

no errors

Comment 20534

Date: 2014-12-17 12:02:42 +0100
From: @njnes

function added

Comment 20535

Date: 2014-12-17 12:04:00 +0100
From: MonetDB Mercurial Repository <>

Changeset ae180fad42c8 made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=ae180fad42c8

Changeset description:

added missing substract month interval function, see Bug #3640.

Comment 20610

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

Oct2014-SP2 has been released.