small doubles end up as NULL after arithmetic

[monetdb-team]

Date: 2009-12-26 06:01:31 +0100
From: @skinkie
To: SQL devs <>
Version: 11.13.9 (Oct2012-SP3)
CC: @njnes, sellam

Last updated: 2013-02-19 13:17:59 +0100

Comment 12911

Date: 2009-12-26 06:01:31 +0100
From: @skinkie

sql>select (4.4054292 - 4.40572025343667)^2;
+------------------------+
| sql_sub_single_value |
+========================+
| 8.4712102997417702e-08 |
+------------------------+
1 tuple
sql>select (52.0903881 - 52.091375762174)^2;
+------------------------+
| sql_sub_single_value |
+========================+
| 9.7547656995040638e-07 |
+------------------------+
1 tuple
sql>select (4.4054292 - 4.40572025343667)^2 + (52.0903881 - 52.091375762174)^2;
+------------------------+
| sql_sub_single_value |
+========================+
| null |
+------------------------+
1 tuple

I would expect 1.0602e-06. As the outcome of the following would be:
sql>select cast((4.4054292 - 4.40572025343667)^2 as double) + cast((52.0903881 - 52.091375762174)^2 as double);
+------------------------+
| sql_sub_single_value |
+========================+
| 1.0601886729478242e-06 |
+------------------------+
1 tuple
sql>select cast((4.4054292 - 4.40572025343667)^2 + (52.0903881 - 52.091375762174)^2 as double);
+------------------------+
| sql_sub_single_value |
+========================+
| null |
+------------------------+
1 tuple

Comment 12912

Date: 2010-05-04 09:32:09 +0200
From: Pseudo user for Sourceforge import <>

This bug was previously known as tracker item 2921310 at http://sourceforge.net/support/tracker.php?aid=2921310

Comment 15425

Date: 2011-01-25 15:13:56 +0100
From: @grobian

Changeset ff0617c4732c made by Fabian Groffen fabian@cwi.nl in the MonetDB repo, refers to this bug.

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

Changeset description:

Add test for bug #2291

Comment 15426

Date: 2011-01-25 15:17:30 +0100
From: @grobian

Feels like the automagic casting isn't working correctly here. With larger values it indeed works fine without the explicit double casts.

Comment 15685

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

The Mar2011 version has been released.

Comment 16037

Date: 2011-07-29 11:00:04 +0200
From: @sjoerdmullender

Apr2011-SP2 has been released.

Comment 16193

Date: 2011-09-07 17:15:24 +0200
From: @sjoerdmullender

The problem is completely different from what is being suggested.
The problem has to do with operator priorities.
sql>\fcsv
sql>select (4.4054292 - 4.40572025343667)^2 + (52.0903881 - 52.091375762174)^2;

sql>select ((4.4054292 - 4.40572025343667)^2) + ((52.0903881 - 52.091375762174)^2);
1.0601886729478242e-06

The reason for the result can be found in sql_parser.y. There we have:
...
%left '='
%left '&' '|' '^' LEFT_SHIFT RIGHT_SHIFT
%left '+' '-'
%left '*'
%left '/' '%'
...

In other words, the ^ operator has equal priority to & and |, which is lower than + and - (and certainly lower than * and /).

Comment 16274

Date: 2011-09-16 15:10:38 +0200
From: @sjoerdmullender

The Aug2011 version has been released.

Comment 17227

Date: 2012-05-07 13:55:14 +0200
From: @njnes

are there changes needed or should we close this bug as is.

Comment 17228

Date: 2012-05-07 14:08:22 +0200
From: @sjoerdmullender

We should do something about the priority of the operators, or we should do something about the semantics of the operators.
It makes no sense to have a "power" operator with the same priority as bitwise AND and OR. The power operator should have a priority higher than multiplication.
Also, I find it strange to have C-like bitwise AND and OR operators, but have the ^ operator mean something totally different than what it does in C. Although we can't do this in the current release cycle, I'd like to use a different operator for power.

Comment 17679

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

Jul2012-SP1 has been released.

Comment 18204

Date: 2012-11-28 13:45:44 +0100
From: sellam

Changeset 6e6c5d533911 made by Thibault Sellam sellam@cwi.nl in the MonetDB repo, refers to this bug.

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

Changeset description:

Added test for bug #2291

Comment 18216

Date: 2012-11-28 18:57:44 +0100
From: @njnes

lets fix this in default.
No longer use ^ for power but for binary xor.
Add ~ (binary not).

Solve the operator precedence

Comment 18372

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

Oct2012-SP3 has been released.

Comment 18438

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

Correct version / fix information.
The problem was resolved in the default branch before the Feb2013 branch was branched off.

Comment 18512

Date: 2013-02-19 13:17:59 +0100
From: @sjoerdmullender

Feb2013 has been released.