Date: 2015-10-01 13:39:43 +0200
From: Sherzod Mutalov <>
To: SQL devs <>
Version: 11.21.19 (Jul2015-SP4)
CC: @mlkersten, @PedroTadim, @drstmane

Last updated: 2020-10-19 11:06:21 +0200

Comment 21314

Date: 2015-10-01 13:39:43 +0200
From: Sherzod Mutalov <>

User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:39.0) Gecko/20100101 Firefox/39.0
Build Identifier:

When executing SQL query, which contains implicit or explicit float to integer (I mean number types, not related to their data sizes) conversions I am getting incorrect results.

Reproducible: Always

Steps to Reproduce:

1. Execute on of the following queries:

SELECT CONVERT(
CONVERT( (str_to_timestamp('2015-10-01', '%Y-%m-%d')
- str_to_timestamp('2015-09-30', '%Y-%m-%d'))
, BIGINT) /86400
, BIGINT);

SELECT CONVERT( (str_to_timestamp('2015-10-01', '%Y-%m-%d')
- str_to_timestamp('2015-09-30', '%Y-%m-%d'))/86400, BIGINT);

SELECT (str_to_timestamp('2015-10-01', '%Y-%m-%d')
- str_to_timestamp('2015-09-30', '%Y-%m-%d'))/86400.0;

Actual Results:

1. and 2) produces: 1000
2. produces: 0.100

Expected Results:

1. and 2) expected as: 1
2. expected as: 1.000

Following query generates expected result, but in floating type.

SELECT ( (str_to_timestamp('2015-10-01', '%Y-%m-%d')
- str_to_timestamp('2015-09-30', '%Y-%m-%d'))/86400);

Comment 21315

Date: 2015-10-01 14:08:37 +0200
From: @drstmane

Apparently, conversion from sec_interval to bigint does not respect decimal point (correctly):

sql>select str_to_timestamp('2015-10-01', '%Y-%m-%d') - str_to_timestamp('2015-09-30', '%Y-%m-%d');
+-------------------------------+
| str_to_timestamp_single_value |
+===============================+
| 86400.000 |
+-------------------------------+
1 tuple (1.085ms)

sql>select convert(str_to_timestamp('2015-10-01', '%Y-%m-%d') - str_to_timestamp('2015-09-30', '%Y-%m-%d'), bigint);
+----------+
| L1 |
+==========+
| 86400000 |
+----------+
1 tuple (0.549ms)

sql>\fraw

sql>select str_to_timestamp('2015-10-01', '%Y-%m-%d') - str_to_timestamp('2015-09-30', '%Y-%m-%d');
% .L table_name
% str_to_timestamp_single_value name
% sec_interval type
% 9 length
[ 86400.000 ]

sql>select convert(str_to_timestamp('2015-10-01', '%Y-%m-%d') - str_to_timestamp('2015-09-30', '%Y-%m-%d'), bigint);
% .L1 table_name
% L1 name
% bigint type
% 8 length
[ 86400000 ]

sql>select (str_to_timestamp('2015-10-01', '%Y-%m-%d') - str_to_timestamp('2015-09-30', '%Y-%m-%d')) / 86400;
% .L table_name
% str_to_timestamp_single_value name
% sec_interval type
% 5 length
[ 1.000 ]

sql>select convert((str_to_timestamp('2015-10-01', '%Y-%m-%d') - str_to_timestamp('2015-09-30', '%Y-%m-%d')) / 86400,bigint);
% .L1 table_name
% L1 name
% bigint type
% 4 length
[ 1000 ]

Comment 21338

Date: 2015-10-14 19:02:09 +0200
From: MonetDB Mercurial Repository <>

Changeset e21bad0f10c1 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=e21bad0f10c1

Changeset description:

added tests for bug #3815 and bug #3816

Comment 27598

Date: 2020-03-15 11:18:51 +0100
From: @mlkersten

This bug report can be closed?

Comment 27602

Date: 2020-03-16 19:19:11 +0100
From: MonetDB Mercurial Repository <>

Changeset a43148c12457 made by Pedro Ferreira pedro.ferreira@monetdbsolutions.com in the MonetDB repo, refers to this bug.

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

Changeset description:

Bug #3815 has not been fixed yet

Comment 27609

Date: 2020-03-18 17:07:19 +0100
From: MonetDB Mercurial Repository <>

Changeset 7c564d9f9562 made by Pedro Ferreira pedro.ferreira@monetdbsolutions.com in the MonetDB repo, refers to this bug.

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

Changeset description:

Bug #3815 is known to fail