Date: 2012-02-16 00:55:27 +0100
From: @skinkie
To: SQL devs <>
Version: 11.11.5 (Jul2012)
CC: @njnes, @drstmane

Last updated: 2012-07-17 13:46:39 +0200

Comment 16904

Date: 2012-02-16 00:55:27 +0100
From: @skinkie

User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/535.1 (KHTML, like Gecko) Chrome/14.0.835.159 Safari/535.1
Build Identifier:

While implementing the hamming distance for RD, I didn't have many issues using integers. After I started to do projections from WGS84 I ended up in the most strange sorting issues.

The formula itself is: (x1 - x2)^2 + (y1 - y2)^2, so in the most simple SQL: (x1 - x2)(x1 - x2) + (y1 - y2)(y1 - y2).

It seems that the multiplication overflows;

sql>SELECT (117992 - 87329.2316881)(117992 - 87329.2316881);
+-----------------------+
| sql_sub_single_value |
+=======================+
| -25184.88771707018591 |
+-----------------------+
1 tuple (1.489ms)
sql>select (117992 - 87329)(117992 - 87329);
+----------------------+
| sql_sub_single_value |
+======================+
| 940219569 |
+----------------------+
1 tuple (1.042ms)

Now after learning today about the great power function it seems:

sql>SELECT power(117992 - 87329.2316881, 2);
+--------------------------+
| sql_sub_single_value |
+==========================+
| 940205360.54925871 |
+--------------------------+
1 tuple (1.569ms)

...that just works. Since the power(a,2) seems to be more expensive than a*a, I would rather use that.

Reproducible: Always

Comment 16905

Date: 2012-02-16 01:26:21 +0100
From: @drstmane

Please note that in your original query, there are no double value, and hence there is no double multiplication.
(MonetDB/)SQL automatically assumes the smallest possible type for numerical literals; thus, 117992 is a 4-byte integer and 87329.2316881 is a decimal(12,7), internally represented as 8-byte integer; for arithmetics, the former is cast to the latter; and then the 8-byte integer multiplication overflows "silently" --- not detecting/reporting the overflow is the actual bug here (!) ---:

sql>trace SELECT (117992 - 87329.2316881)(117992 - 87329.2316881);
+-----------------------+
| sql_sub_single_value |
+=======================+
| -25184.88771707018591 |
+-----------------------+
1 tuple (19.969ms)
+-------+---------------------------------------------------------------------------------------------------+
| ticks | stmt |
+=======+===================================================================================================+
| 7 | X_7 := calc.lng(A0=117992,17,7); |
| 3 | X_10 := calc.lng(7,A1=873292316881:lng,17,7); |
| 3 | X_11 := calc.-(X_7=1179920000000:lng,X_10=873292316881:lng); |
| 3 | X_12 := calc.lng(A2=117992,17,7); |
| 3 | X_13 := calc.lng(7,A3=873292316881:lng,17,7); |
| 3 | X_14 := calc.-(X_12=1179920000000:lng,X_13=873292316881:lng); |
| 2 | X_15 := calc.(X_11=306627683119:lng,X_14=306627683119:lng); |
| 12 | sql.exportValue(1,".","sql_sub_single_value","decimal",19,14,8,X_15=-2518488771707018591:lng,""); |
| 340 | X_5:void := user.s2_1(117992,873292316881:lng,117992,873292316881:lng); |
+-------+---------------------------------------------------------------------------------------------------+
9 tuples (20.023ms)

You can enforce double arithmetics and thus avoid overflow (in this case) by casting (at least) one of the literals to double:

sql>trace SELECT (117992 - cast(87329.2316881 as double))(117992 - 87329.2316881);
+--------------------------+
| sql_sub_single_value |
+==========================+
| 940205360.54925871 |
+--------------------------+
1 tuple (26.341ms)
+-------+-------------------------------------------------------------------------------------------+
| ticks | stmt |
+=======+===========================================================================================+
| 3 | X_7 := calc.-(A0=117992,A1=87329.231688100001); |
| 4 | X_8 := calc.lng(A2=117992,17,7); |
| 3 | X_11 := calc.lng(7,A3=873292316881:lng,17,7); |
| 3 | X_12 := calc.-(X_8=1179920000000:lng,X_11=873292316881:lng); |
| 20750 | X_13 := calc.dbl(7,X_12=306627683119:lng); |
| 3 | X_14 := calc.(X_7=30662.768311899999,X_13=30662.768311899999); |
| 11 | sql.exportValue(1,".","sql_sub_single_value","double",53,7,9,X_14=940205360.54925871,""); |
| 21069 | X_5:void := user.s3_1(117992,87329.231688100001,117992,873292316881:lng); |
+-------+-------------------------------------------------------------------------------------------+
8 tuples (26.392ms)

Comment 16906

Date: 2012-02-16 02:05:41 +0100
From: @skinkie

Thanks, especially your "decimal(12,7), internally represented as 8-byte integer;" is an eye opener. After thinking about this, obviously it is the only true way to maintain bit precision. Opposed to transforming to double, which is a float. The following example also shows that when the storage does increase, the operation also 'just works' - because it doesn't overflow anymore. Notice also the rendering of A1.

sql>trace SELECT (117992 - 87329.23168810000000)(117992 - 87329.2316881);
+--------------------------+
| sql_sub_single_value |
+==========================+
| 940205360.54925871 |
+--------------------------+
1 tuple (8.348ms)
+-------+-------------------------------------------------------------------------------------------+
| ticks | stmt |
+=======+===========================================================================================+
| 5 | X_7 := calc.-(A0=117992,A1=87329.231688100001); |
| 7 | X_8 := calc.lng(A2=117992,17,7); |
| 3 | X_11 := calc.lng(7,A3=873292316881:lng,17,7); |
| 2 | X_12 := calc.-(X_8=1179920000000:lng,X_11=873292316881:lng); |
| 2 | X_13 := calc.dbl(7,X_12=306627683119:lng); |
| 3 | X_14 := calc.(X_7=30662.768311899999,X_13=30662.768311899999); |
| 13 | sql.exportValue(1,".","sql_sub_single_value","double",53,7,9,X_14=940205360.54925871,""); |
| 3 | end s13_10; |
| 25 | function user.s13_10(A0=117992,A1=87329.231688100001,A2=117992,A3=873292316881:lng); |
| 749 | X_5:void := user.s13_10(117992,87329.231688100001,117992,873292316881:lng); |
+-------+-------------------------------------------------------------------------------------------+
10 tuples (8.401ms)

Comment 17215

Date: 2012-05-02 21:24:01 +0200
From: @njnes

fixed in default.

Comment 17475

Date: 2012-07-17 13:46:39 +0200
From: @grobian

Fix delivered in Jul2012 release