You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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.
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.
The text was updated successfully, but these errors were encountered:
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:
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:
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.
The text was updated successfully, but these errors were encountered: