Wrong result for HAVING with floating-point constant

[monetdb-team]

Date: 2014-12-01 18:11:27 +0100
From: Richard Hughes <<richard.monetdb>>
To: SQL devs <>
Version: 11.19.3 (Oct2014)
CC: @njnes

Last updated: 2015-01-29 14:07:29 +0100

Comment 20502

Date: 2014-12-01 18:11:27 +0100
From: Richard Hughes <<richard.monetdb>>

sql>set optimizer='minimal_pipe';
operation successful (1.050ms)
sql>create table foo (a int, b int);
operation successful (1.886ms)
sql>insert into foo values (1,1);
1 affected row (1.452ms)
sql>select a from foo group by a having sum(b)>count().5;
+---+
| a |
+===+
+---+
0 tuples (2.002ms)

Expected result:
+---+
| a |
+===+
| 1 |
+---+

The same calculation in the select clause gets it right:

sql>select a,sum(b)>count().5 from foo group by a;
+------+-------+
| a | >_L1 |
+======+=======+
| 1 | true |
+------+-------+
1 tuple (2.039ms)

sql>explain select a from foo group by a having sum(b)>count().5;
+-----------------------------------------------------------------------------+
| mal |
+=============================================================================+
| function user.s46_1{autoCommit=true}(A0:bte):void; |
| X_3 := sql.mvc(); |
| X_4:bat[:oid,:oid] := sql.tid(X_3,"sys","foo"); |
| X_7:bat[:oid,:int] := sql.bind(X_3,"sys","foo","b",0); |
| (X_10,r1_10) := sql.bind(X_3,"sys","foo","b",2); |
| X_13:bat[:oid,:int] := sql.bind(X_3,"sys","foo","b",1); |
| X_15 := sql.delta(X_7,X_10,r1_10,X_13); |
| X_16 := algebra.leftfetchjoin(X_4,X_15); |
| X_17:bat[:oid,:int] := sql.bind(X_3,"sys","foo","a",0); |
| (X_19,r1_19) := sql.bind(X_3,"sys","foo","a",2); |
| X_21:bat[:oid,:int] := sql.bind(X_3,"sys","foo","a",1); |
| X_22 := sql.delta(X_17,X_19,r1_19,X_21); |
| X_23 := algebra.leftfetchjoin(X_4,X_22); |
| (X_24,r1_24,r2_24) := group.subgroupdone(X_23); |
| X_27:bat[:oid,:lng] := aggr.subsum(X_16,X_24,r1_24,true,true); |
| X_28:bat[:oid,:wrd] := aggr.subcount(X_24,X_24,r1_24,false); |
| X_30:bat[:oid,:lng] := batcalc.lng(X_28,19,1); |
| X_34 := A0; |
| X_35 := calc.lng(1,X_34,19,1); |
| X_36:bat[:oid,:lng] := batcalc.(X_30,X_35); |
| X_38:bat[:oid,:lng] := batcalc.lng(2,X_36); |
| X_39:bat[:oid,:bit] := batcalc.>(X_27,X_38); |
| X_41 := algebra.subselect(X_39,true,true,true,true,false); |
| X_43 := algebra.leftfetchjoin(r1_24,X_23); |
| X_44 := algebra.leftfetchjoin(X_41,X_43); |
| X_45 := sql.resultSet(1,1,X_44); |
| sql.rsColumn(X_45,"sys.foo","a","int",32,0,X_44); |
| X_51 := io.stdout(); |
| sql.exportResult(X_51,X_45); |
| end s46_1; |
| querylog.define("select a from foo group by a having sum(b)>count()*.5;" |
: ,"minimal_pipe") :
+-----------------------------------------------------------------------------+

The batcalc.lng calls don't look quite right to me.

Comment 20503

Date: 2014-12-01 19:03:17 +0100
From: Richard Hughes <<richard.monetdb>>

Created attachment 312
Proposed fix

How's this for a fix? That code is scary-complicated.

Attached file: having-float-wrong-result.patch (text/plain, 510 bytes)
Description: Proposed fix

Comment 20507

Date: 2014-12-03 13:13:36 +0100
From: @njnes

Added a test. incorrect_floating_point_cast.Bug-3633
Fixed by proper type checks

Comment 20508

Date: 2014-12-03 13:15:35 +0100
From: MonetDB Mercurial Repository <>

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

Changeset description:

fixed bug #3633
ie properly check types on compare expressions
made string a higher type in auto coersion.

Comment 20598

Date: 2015-01-29 14:07:29 +0100
From: @sjoerdmullender

Oct2014-SP2 has been released.