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

Cast value to integer gives wrong result #3310

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

Cast value to integer gives wrong result #3310

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

Comments

@monetdb-team
Copy link

Date: 2013-06-17 23:39:39 +0200
From: @bartscheers
To: SQL devs <>
Version: 11.15.11 (Feb2013-SP3)
CC: @njnes

Last updated: 2013-09-27 13:47:19 +0200

Comment 18856

Date: 2013-06-17 23:39:39 +0200
From: @bartscheers

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

sql>SELECT CAST(0.94 AS INTEGER);
+--------------+
| single_value |
+==============+
| 0 |
+--------------+
1 tuple (0.389ms)
sql>SELECT CAST(0.95 AS INTEGER);
+--------------+
| single_value |
+==============+
| 1 |
+--------------+
1 tuple (0.298ms)

Reproducible: Always

Steps to Reproduce:

  1. See details

Actual Results:

0.94 -> 0
0.95 -> 1

Expected Results:

0.94 -> 0
0.95 -> 0

We use this a lot in our code base, which may lead to unexpected results.

Comment 18857

Date: 2013-06-18 11:30:44 +0200
From: MonetDB Mercurial Repository <>

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

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

Changeset description:

Fix for bug #3310.
Not entirely the way the bug suggests: we round, we do not truncate.
Whether this is correct is up to Niels.
At least rounding now happens correctly.

Comment 18858

Date: 2013-06-18 12:29:55 +0200
From: @bartscheers

Shouldn't casting a double to an integer do an intrinsic truncate?

Notice the differences:

sql>select cast(0.98 as integer);
+--------------+
| single_value |
+==============+
| 1 |
+--------------+
1 tuple (0.143ms)
sql>select cast(cast(0.98 as double) as integer);
+--------------+
| single_value |
+==============+
| 0 |
+--------------+
1 tuple (0.295ms)

where the second one is correct.

Comment 18859

Date: 2013-06-18 12:46:02 +0200
From: @sjoerdmullender

What does the standard say?

Comment 18860

Date: 2013-06-18 13:19:57 +0200
From: @bartscheers

I don't know, probably depends on the compiler.
So we have to do an explicit truncate/round/ceil before casting.

Comment 18861

Date: 2013-06-18 15:47:17 +0200
From: @sjoerdmullender

This is what the standard (SQL 2008) says:
6.12
[...]
8) If TD is exact numeric, then
Case:
a) If SD is exact numeric or approximate numeric, then
Case:
i) If there is a representation of SV in the data type TD that does not lose any leading significant digits after rounding or truncating if necessary, then TV is that representation. The choice of whether to round or truncate is implementation-defined.

(TD is the target type, SD is the source type.)
Note the last sentence.
This is the case that matches the use case here.
In other words, technically what we're doing is ok. It may not be pretty to have different results for casting from double to integer vs. from decimal to integer.

@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 Nov 9, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working normal SQL
Projects
None yet
Development

No branches or pull requests

2 participants