Date: 2015-10-23 17:54:24 +0200
From: @yzchang
To: SQL devs <>
Version: 11.21.5 (Jul2015)
CC: @njnes, @yzchang

Last updated: 2015-11-03 10:18:28 +0100

Comment 21389

Date: 2015-10-23 17:54:24 +0200
From: @yzchang

Run the following queries, and observe that the SELECT query returns empty result, while the tuple with "mydate" value '2012-03-09' does satisfy the WHERE condition:

CREATE TABLE datepoint (mydate DATE, insiderange BOOLEAN DEFAULT FALSE, rangename CHAR(8));
INSERT INTO datepoint (mydate) VALUES ('2012-05-09');
INSERT INTO datepoint (mydate) VALUES ('2012-03-09');
CREATE TABLE daterange (startdate DATE, enddate DATE, name CHAR(8));
INSERT INTO daterange (startdate, enddate, name) VALUES ('2012-03-01','2012-03-31','A');
-- returns empty result, which is wrong:
select * from datepoint A, daterange B where A.mydate between B.startdate and B.enddate;

This problem seems rather specific with the number of tuples inserted into "datepoint", OR the order in which they are inserted.

The only difference in the following queries is that one more tuple is inserted into "datepoint", then the SELECT query returns correct results:
CREATE TABLE datepoint (mydate DATE, insiderange BOOLEAN DEFAULT FALSE, rangename CHAR(8));
INSERT INTO datepoint (mydate) VALUES ('2012-05-09');
INSERT INTO datepoint (mydate) VALUES ('2012-03-09');
INSERT INTO datepoint (mydate) VALUES ('2012-04-09');
CREATE TABLE daterange (startdate DATE, enddate DATE, name CHAR(8));
INSERT INTO daterange (startdate, enddate, name) VALUES ('2012-03-01','2012-03-31','A');
-- returns correct result: one tuple with "mydate" value '2012-03-09'
select * from datepoint A, daterange B where A.mydate between B.startdate and B.enddate;

The only difference in the following queries (compared with the first set) is that the tuples are inserted in the reverse order, then again the SELECT query returns correct results:
CREATE TABLE datepoint (mydate DATE, insiderange BOOLEAN DEFAULT FALSE, rangename CHAR(8));
INSERT INTO datepoint (mydate) VALUES ('2012-03-09');
INSERT INTO datepoint (mydate) VALUES ('2012-05-09');
CREATE TABLE daterange (startdate DATE, enddate DATE, name CHAR(8));
INSERT INTO daterange (startdate, enddate, name) VALUES ('2012-03-01','2012-03-31','A');
-- returns correct result: one tuple with "mydate" value '2012-03-09'
select * from datepoint A, daterange B where A.mydate between B.startdate and B.enddate;

Comment 21390

Date: 2015-10-23 18:10:14 +0200
From: MonetDB Mercurial Repository <>

Changeset 91007a50e91b made by Jennie Zhang y.zhang@cwi.nl in the MonetDB repo, refers to this bug.

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

Changeset description:

Added test for bug #3834 , and stabel out, err

Comment 21391

Date: 2015-10-23 18:16:15 +0200
From: @yzchang

The strange thing is, when running the query with Mtest.py, the first SELECT query does return correct results. While the problem occurs with just "mclient date_comparison_incorrect_results.Bug-3834.sql" ...

Comment 21402

Date: 2015-10-25 00:11:23 +0200
From: MonetDB Mercurial Repository <>

Changeset 565c496e66c0 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=565c496e66c0

Changeset description:

fix for bug #3834, ie handle reverse sorted bats in rangejoin

Comment 21403

Date: 2015-10-25 00:12:02 +0200
From: @njnes

fixed. The reverse sorted case is now properly handled in the rangejoin code.

Comment 21409

Date: 2015-10-27 15:06:10 +0100
From: MonetDB Mercurial Repository <>

Changeset 54e21e04d706 made by Jennie Zhang y.zhang@cwi.nl in the MonetDB repo, refers to this bug.

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

Changeset description:

Extended the test for Bug #3834 to test that the original UPDATE statement also produces correct results.

Comment 21445

Date: 2015-11-03 10:18:28 +0100
From: @sjoerdmullender

Jul2015 SP1 has been released.