SQL: incorrect cast from double to int

[monetdb-team]

Date: 2010-07-05 11:08:47 +0200
From: @yzchang
To: SQL devs <>
Version: 11.11.7 (Jul2012-SP1)
CC: @romulogoncalves, @njnes, simon.brodt, @drstmane

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

Comment 14215

Date: 2010-07-05 11:08:47 +0200
From: @yzchang

User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-GB; rv:1.9.1.10) Gecko/20100622 Fedora/3.5.10-1.fc12 Firefox/3.5.10
Build Identifier:

In the following query, the simple/small doubles are incorrectly cast to integers:

sql>select 1.0/2.0;
+----------------------+
| sql_div_single_value |
+======================+
| 0 |
+----------------------+
1 tuple
Timer 0.431 msec 1 rows

I can only get the expected result using a cast:

sql>select cast (1 as double)/2;
+------------------------+
| sql_div_single_value |
+========================+
| 0.5 |
+------------------------+
1 tuple
Timer 0.324 msec 1 rows

Reproducible: Always

Comment 14216

Date: 2010-07-05 11:16:38 +0200
From: @drstmane

there is IMHO no cast from double to int;
rather, both 1.0 & 2.0 are parsed as decimal and "optimized" to 0 digits, and the div keeps the 0 digits; cf.,

sql>\fraw

sql>select 1.0/2.0;
% . table_name
% sql_div_single_value name
% decimal type
% 3 length
[ 0 ]

sql>select 1.0/2.1;
% . table_name
% sql_div_single_value name
% decimal type
% 7 length
[ 0.476 ]

sql>select 1.1/2.1;
% . table_name
% sql_div_single_value name
% decimal type
% 7 length
[ 0.523 ]

sql>select 1.1/2.0;
% . table_name
% sql_div_single_value name
% decimal type
% 4 length
[ 0.5 ]

I cannot tell what SQL semantics require in such cases (if at all)

Comment 14229

Date: 2010-07-06 17:58:38 +0200
From: @drstmane

Changeset 75fb1faa9477 made by Stefan Manegold Stefan.Manegold@cwi.nl in the MonetDB repo, refers to this bug.

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

Changeset description:

merged with Jennie's new tests from Jun2010:
"
Added tests for bugs 2579 and 2580

bug #2579 is nog fixed yet.
bug #2580 is fixed by Niels' yesterday's check-in.
"

Comment 14842

Date: 2010-08-30 09:23:20 +0200
From: @sjoerdmullender

The Jun2010-SP2 version has been released.

Comment 14944

Date: 2010-09-28 11:46:29 +0200
From: @sjoerdmullender

Looking at the code that is produced (plan, explain, debug), I noticed that the values 1.0 and 2.0 lost the ".0" very early on (i.e., they didn't occur in the code at all). Looking at the parser and scanner, I noticed that the parser strips trailing zeroes from numbers with decimal points, so the information that we were dealing with a decimal is lost there.
If I change the code in the parser to not strip those zeroes, the result of the query becomes
sql>select 1.0/2.0;
+----------------------+
| sql_div_single_value |
+======================+
| 0.500 |
+----------------------+
1 tuple (1.073ms)

The patch is simply to replace the call strip_extra_zeros(sa_strdup(SA, $1)) with sa_strdup(SA, $1) in the section dealing with INTNUM.

Unfortunately, this change causes a bunch of other tests to fail.

Comment 15684

Date: 2011-03-28 17:36:31 +0200
From: @sjoerdmullender

The Mar2011 version has been released.

Comment 15988

Date: 2011-07-27 17:29:55 +0200
From: @njnes

Changeset 89e3602630f4 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=89e3602630f4

Changeset description:

don't strip zero's from literals anymore. Fixes bug #2579 (ie 1.0/2.0 now results in 0.500)

some more fixes to make sure we can handle q14 on larger scales.

Comment 16048

Date: 2011-07-29 11:03:54 +0200
From: @sjoerdmullender

Apr2011-SP2 has been released.

Comment 16239

Date: 2011-09-16 15:04:33 +0200
From: @sjoerdmullender

The Aug2011 version has been released.

Comment 17411

Date: 2012-07-04 15:48:37 +0200
From: Simon Brodt <<simon.brodt>>

Problem still exists for:

sql>SELECT '3.1'/'5.1';
+----------------------+
| sql_div_single_value |
+======================+
| 0 |
+----------------------+
1 tuple (0.804ms)

The following returns the correct result:

sql>SELECT '3.1'/5.1;
+----------------------+
| sql_div_single_value |
+======================+
| 0.607 |
+----------------------+
1 tuple (0.755ms)
sql>SELECT 3.1/5.1;
+----------------------+
| sql_div_single_value |
+======================+
| 0.607 |
+----------------------+
1 tuple (0.667ms)
sql>SELECT 3.1/'5.1';
+----------------------+
| sql_div_single_value |
+======================+
| 0.607 |
+----------------------+
1 tuple (0.741ms)

Comment 17678

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

Jul2012-SP1 has been released.

Comment 18030

Date: 2012-11-27 11:27:38 +0100
From: @romulogoncalves

A test was added to the Bug_tracker_2012 named incorrect_cast_from_double_to_int.Bug-2579

The correct out and err output as well.

Comment 18172

Date: 2012-11-28 10:54:45 +0100
From: @njnes

SELECT '3.1'/'5.1'; division of 2 strings. The compiler cannot guess the proper type here.

Comment 18211

Date: 2012-11-28 14:44:15 +0100
From: @njnes

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

Changeset description:

approved output. Dividing 2 strings is simply not supported. Compiler
just takes a division operator (and by bad luck) its the byte/byte version.
Bug #2579

Comment 18366

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

Oct2012-SP3 has been released.