Navigation Menu

Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

cast(interval second value to int or decimal) is wrong (by a factor of 1000), cast(interval month value to decimal or floating point) fails #6793

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Closed

cast(interval second value to int or decimal) is wrong (by a factor of 1000), cast(interval month value to decimal or floating point) fails #6793

monetdb-team opened this issue Nov 30, 2020 · 0 comments
Labels
bug Something isn't working normal SQL
Milestone
Ancient Release

Comments

@monetdb-team
Copy link

Date: 2019-11-28 19:57:28 +0100
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.35.3 (Nov2019)

Last updated: 2019-12-20 15:36:32 +0100

Comment 27429

Date: 2019-11-28 19:57:28 +0100
From: Martin van Dinther <<martin.van.dinther>>

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

casting an interval second value to an int/bigint/smallint type or decimal type is wrong (by a factor of 1000).

casting an interval month value to a decimal or floating point (real, float, double) type fails, it issues an error, while casting to int type succeeds.

Reproducible: Always

Steps to Reproduce:

CREATE TABLE INTERVAL_TBL (f1 interval second);
INSERT INTO INTERVAL_TBL (f1) VALUES (1.123);
INSERT INTO INTERVAL_TBL (f1) VALUES (2.123);
INSERT INTO INTERVAL_TBL (f1) VALUES (3.123);
INSERT INTO INTERVAL_TBL (f1) VALUES (4.123);
SELECT f1, cast(f1 as int), cast(f1 as dec(8,3)), cast(f1 as real) FROM INTERVAL_TBL;
SELECT count(f1), sum(f1), cast(sum(f1) as int), cast(sum(f1) as dec(8,3)), cast(sum(f1) as real) FROM INTERVAL_TBL;
DROP TABLE INTERVAL_TBL;

CREATE TABLE INTERVAL_TBL (f1 interval month);
INSERT INTO INTERVAL_TBL (f1) VALUES (1);
INSERT INTO INTERVAL_TBL (f1) VALUES (2);
INSERT INTO INTERVAL_TBL (f1) VALUES (3);
INSERT INTO INTERVAL_TBL (f1) VALUES (4);
SELECT f1, cast(f1 as int) FROM INTERVAL_TBL;
SELECT f1, cast(f1 as dec(3,0)) FROM INTERVAL_TBL; -- returns error: types month_interval(3,0) and decimal(3,0) are not equal for column 'f1'
SELECT f1, cast(f1 as real) FROM INTERVAL_TBL; -- returns error: types month_interval(3,0) and real(24,0) are not equal for column 'f1'
SELECT count(f1), sum(f1), cast(sum(f1) as int), cast(sum(f1) as dec(8,3)), cast(sum(f1) as real) FROM INTERVAL_TBL;
DROP TABLE INTERVAL_TBL;

Actual Results:

sql>CREATE TABLE INTERVAL_TBL (f1 interval second);
operation successful
sql>INSERT INTO INTERVAL_TBL (f1) VALUES (1.123);
1 affected row
sql>INSERT INTO INTERVAL_TBL (f1) VALUES (2.123);
1 affected row
sql>INSERT INTO INTERVAL_TBL (f1) VALUES (3.123);
1 affected row
sql>INSERT INTO INTERVAL_TBL (f1) VALUES (4.123);
1 affected row
sql>SELECT f1, cast(f1 as int), cast(f1 as dec(8,3)), cast(f1 as real) FROM INTERVAL_TBL;
+-------+------+------------+-----------------+
| f1 | L1 | L2 | L3 |
+=======+======+============+=================+
| 1.123 | 1123 | 1123.000 | 1.123 |
| 2.123 | 2123 | 2123.000 | 2.123 |
| 3.123 | 3123 | 3123.000 | 3.123 |
| 4.123 | 4123 | 4123.000 | 4.123 |
+-------+------+------------+-----------------+
4 tuples
sql>SELECT count(f1), sum(f1), cast(sum(f1) as int), cast(sum(f1) as dec(8,3)), cast(sum(f1) as real) FROM INTERVAL_TBL;
+------+--------+-------+------------+-----------------+
| L2 | L3 | L5 | L7 | L11 |
+======+========+=======+============+=================+
| 4 | 10.492 | 10492 | 10492.000 | 10.492 |
+------+--------+-------+------------+-----------------+
1 tuple
sql>DROP TABLE INTERVAL_TBL;
operation successful
sql>
sql>CREATE TABLE INTERVAL_TBL (f1 interval month);
operation successful
sql>INSERT INTO INTERVAL_TBL (f1) VALUES (1);
1 affected row
sql>INSERT INTO INTERVAL_TBL (f1) VALUES (2);
1 affected row
sql>INSERT INTO INTERVAL_TBL (f1) VALUES (3);
1 affected row
sql>INSERT INTO INTERVAL_TBL (f1) VALUES (4);
1 affected row
sql>SELECT f1, cast(f1 as int) FROM INTERVAL_TBL;
+------+------+
| f1 | L1 |
+======+======+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+------+------+
4 tuples
sql>SELECT f1, cast(f1 as dec(3,0)) FROM INTERVAL_TBL; -- returns error:
types month_interval(3,0) and decimal(3,0) are not equal for column 'f1'
sql>SELECT f1, cast(f1 as real) FROM INTERVAL_TBL; -- returns error:
types month_interval(3,0) and real(24,0) are not equal for column 'f1'
sql>SELECT count(f1), sum(f1), cast(sum(f1) as int), cast(sum(f1) as dec(8,3)), cast(sum(f1) as real) FROM INTERVAL_TBL; -- returns error:
types month_interval(32,0) and decimal(8,3) are not equal for column 'L6'
sql>DROP TABLE INTERVAL_TBL;
operation successful
sql>

Expected Results:

sql>CREATE TABLE INTERVAL_TBL (f1 interval SECOND);
operation successful
sql>INSERT INTO INTERVAL_TBL (f1) VALUES (1.123);
1 affected row
sql>INSERT INTO INTERVAL_TBL (f1) VALUES (2.123);
1 affected row
sql>INSERT INTO INTERVAL_TBL (f1) VALUES (3.123);
1 affected row
sql>INSERT INTO INTERVAL_TBL (f1) VALUES (4.123);
1 affected row
sql>SELECT f1, cast(f1 as int), cast(f1 as dec(8,3)), cast(f1 as real) FROM INTERVAL_TBL;
+-------+----+---------+-----------------+
| f1 | L1 | L2 | L3 |
+=======+====+=========+=================+
| 1.123 | 1 | 1.123 | 1.123 |
| 2.123 | 2 | 2.123 | 2.123 |
| 3.123 | 3 | 3.123 | 3.123 |
| 4.123 | 4 | 4.123 | 4.123 |
+-------+----+---------+-----------------+
4 tuples
sql>SELECT count(f1), sum(f1), cast(sum(f1) as int), cast(sum(f1) as dec(8,3)), cast(sum(f1) as real) FROM INTERVAL_TBL;
+------+--------+-------+------------+-----------------+
| L2 | L3 | L5 | L7 | L11 |
+======+========+=======+============+=================+
| 4 | 10.492 | 10 | 10.492 | 10.492 |
+------+--------+-------+------------+-----------------+
1 tuple
sql>DROP TABLE INTERVAL_TBL;
operation successful
sql>
sql>CREATE TABLE INTERVAL_TBL (f1 interval MONTH);
operation successful
sql>INSERT INTO INTERVAL_TBL (f1) VALUES (1);
1 affected row
sql>INSERT INTO INTERVAL_TBL (f1) VALUES (2);
1 affected row
sql>INSERT INTO INTERVAL_TBL (f1) VALUES (3);
1 affected row
sql>INSERT INTO INTERVAL_TBL (f1) VALUES (4);
1 affected row
sql>SELECT f1, cast(f1 as int) FROM INTERVAL_TBL;
+------+------+
| f1 | L1 |
+======+======+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+------+------+
4 tuples
sql>SELECT f1, cast(f1 as dec(3,0)) FROM INTERVAL_TBL;
+------+------+
| f1 | L1 |
+======+======+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+------+------+
4 tuples
sql>SELECT f1, cast(f1 as real) FROM INTERVAL_TBL;
+------+------+
| f1 | L1 |
+======+======+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+------+------+
4 tuples
sql>SELECT count(f1), sum(f1), cast(sum(f1) as int), cast(sum(f1) as dec(8,3)), cast(sum(f1) as real) FROM INTERVAL_TBL;
+------+------+------+------------+-----------------+
| L2 | L3 | L5 | L10 | L13 |
+======+======+======+============+=================+
| 4 | 10 | 10 | 10.000 | 10 |
+------+------+------+------------+-----------------+
1 tuple
sql>DROP TABLE INTERVAL_TBL;
operation successful
sql>

Tests inspired by tests of bug #3533

Comment 27430

Date: 2019-11-28 20:01:24 +0100
From: MonetDB Mercurial Repository <>

Changeset ddff28ebf5f3 made by Martin van Dinther martin.van.dinther@monetdbsolutions.com in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=ddff28ebf5f3

Changeset description:

Corrected the desired output. instead of 10000 it should have returned 10.
The issue is reported separately in bug #6793.

Comment 27442

Date: 2019-12-05 13:41:14 +0100
From: MonetDB Mercurial Repository <>

Changeset e7d28370aee7 made by Martin van Dinther martin.van.dinther@monetdbsolutions.com in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=e7d28370aee7

Changeset description:

Add test for Bug #6793

Comment 27466

Date: 2019-12-09 15:38:54 +0100
From: MonetDB Mercurial Repository <>

Changeset 356d665aeb10 made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=356d665aeb10

Changeset description:

When converting second interval to any numeric type take decimals into account.
This fixes part of bug #6793.

Comment 27467

Date: 2019-12-09 16:41:10 +0100
From: MonetDB Mercurial Repository <>

Changeset 7ff84bea66d4 made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=7ff84bea66d4

Changeset description:

Allow conversion from month interval to decimal and float.
This fixes the second part of bug #6793.
@monetdb-team monetdb-team added bug Something isn't working normal SQL labels Nov 30, 2020
@sjoerdmullender sjoerdmullender added this to the Ancient Release milestone Feb 7, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug Something isn't working normal SQL
Projects
None yet
Milestone
Ancient Release
Development

No branches or pull requests
2 participants
@sjoerdmullender @monetdb-team