timestamp minus date results in program error

[monetdb-team]

Date: 2012-01-18 16:27:35 +0100
From: @skinkie
To: SQL devs <>
Version: 11.11.7 (Jul2012-SP1)
CC: @njnes, sellam

Last updated: 2013-01-22 09:29:13 +0100

Comment 16781

Date: 2012-01-18 16:27:35 +0100
From: @skinkie

User-Agent: Mozilla/5.0 (X11; Linux i686) AppleWebKit/535.2 (KHTML, like Gecko) Chrome/15.0.874.83 Safari/535.2
Build Identifier:

select (cast('1970-01-01 0:00' as timestamp) - cast('1970-01-01' as date));
TypeException:user.s9_3[7]:'calc.second_interval' undefined in: _12:any := calc.second_interval(_11:date, _13:int, _14:int)
39000!program contains errors

sql>select (cast('1970-01-01 0:00' as timestamp) - cast('1970-01-01' as timestamp));
+----------------------+
| sql_sub_single_value |
+======================+
| 0 |
+----------------------+
1 tuple (1.363ms)

Reproducible: Always

/opt/monetdb/bin/mserver5 --version
MonetDB 5 server v11.8.0 (64-bit, 64-bit oids)
This is an unreleased version
Copyright (c) 1993-July 2008 CWI
Copyright (c) August 2008-2012 MonetDB B.V., all rights reserved
Visit http://www.monetdb.org/ for further information
Found 3.9GiB available memory, 1 available cpu core
Libraries:
libpcre: 8.20 2011-10-21 (compiled with 8.20)
openssl: OpenSSL 1.0.0e 6 Sep 2011 (compiled with OpenSSL 1.0.0e 6 Sep 2011)
libxml2: 2.7.8 (compiled with 2.7.8)
Compiled by: root@openov_cxx (x86_64-unknown-linux-gnu)
Compilation: gcc -g -Werror -Wall -Wextra -W -Werror-implicit-function-declaration -Wpointer-arith -Wdeclaration-after-statement -Wundef -Wformat=2 -Wno-format-nonliteral -Winit-self -Winvalid-pch -Wmissing-declarations -Wmissing-format-attribute -Wmissing-prototypes -Wold-style-definition -Wpacked -Wunknown-pragmas -Wvariadic-macros -fstack-protector-all -Wstack-protector -Wpacked-bitfield-compat -Wsync-nand -Wjump-misses-init -Wmissing-include-dirs -Wlogical-op -Wunreachable-code -Wp,-D_FORTIFY_SOURCE=2
Linking : /usr/x86_64-pc-linux-gnu/bin/ld -m elf_x86_64

Comment 17553

Date: 2012-07-21 19:04:55 +0200
From: @grobian

problem still exists in current Jul2012

Comment 17554

Date: 2012-07-21 19:05:15 +0200
From: @grobian

sql bug

Comment 17680

Date: 2012-08-24 14:55:57 +0200
From: @sjoerdmullender

Jul2012-SP1 has been released.

Comment 18029

Date: 2012-11-27 11:25:01 +0100
From: sellam

This combination is not allowed in SQL-99.
You can perform arithmetic on

• Date and Interval
• Timestamp and Interval
• Date and Date
• Timestamp and timestamp
  but not timestamp/interval

(In reply to comment 3)

Jul2012-SP1 has been released.

Comment 18031

Date: 2012-11-27 11:27:40 +0100
From: sellam

oops typo, I meant:

but not timestamp and date

(In reply to comment 4)

This combination is not allowed in SQL-99.
You can perform arithmetic on

• Date and Interval
• Timestamp and Interval
• Date and Date
• Timestamp and timestamp
  but not timestamp/interval

(In reply to comment 3)

Jul2012-SP1 has been released.

Comment 18032

Date: 2012-11-27 11:30:06 +0100
From: @skinkie

(In reply to comment 4)

This combination is not allowed in SQL-99.
You can perform arithmetic on

• Date and Interval
• Timestamp and Interval
• Date and Date
• Timestamp and timestamp
  but not timestamp/interval

It works as a charm on PostgreSQL.

select (cast('1970-01-01 0:00' as timestamp) - cast('1970-01-01' as date));
?column?

00:00:00
(1 row)

Date can be converted to timestamp by the parser.

Comment 18197

Date: 2012-11-28 13:45:27 +0100
From: sellam

Changeset 389c9f8e78f4 made by Thibault Sellam sellam@cwi.nl in the MonetDB repo, refers to this bug.

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

Changeset description:

Added test for bug #2977

Comment 18212

Date: 2012-11-28 16:23:36 +0100
From: @njnes

Changeset 1ee4a27c1126 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=1ee4a27c1126

Changeset description:

fixed bugs 2977 and 3143

return errors in psm when we don't expect select statements (solves 3143)

Give proper error when a function isn't found and don't allow converts from
date into second intervals. (solves 2977)

Comment 18214

Date: 2012-11-28 16:24:00 +0100
From: @njnes

give proper error now

Comment 18373

Date: 2013-01-22 09:29:13 +0100
From: @sjoerdmullender

Oct2012-SP3 has been released.