Date: 2014-12-18 12:37:13 +0100
From: @yzchang
To: SQL devs <>
Version: 11.19.7 (Oct2014-SP1)
CC: b.kaptijn, @njnes

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

Comment 20538

Date: 2014-12-18 12:37:13 +0100
From: @yzchang

Please try the queries below. All three variations of Q1 produce incorrect result, i.e., they return one tuple, while they should return zero tuple. The problem seems to be cause by the combination of '<' and '=' (see Q3), but somehow subquery helps to avoid this problem (see Q2).

Thanks in advance!

Jennie

CREATE TABLE t1 (rec CLOB, datum DATE, ref CLOB, nr CLOB);
CREATE TABLE t2 (rec CLOB, begindatum DATE, einddatum DATE, ref CLOB, nr CLOB);
INSERT INTO t1 VALUES ('1','2012-03-14','8','1234');
INSERT INTO t2 VALUES ('2','2012-03-01','2012-03-31','8','1234');

-- Q1: the following query and its two variations return incorrect result

SELECT DISTINCT t1.rec FROM t1 INNER JOIN t2 ON t1.datum >= t2.begindatum AND t1.datum <= t2.einddatum WHERE t1.ref < t2.ref AND t1.nr = t2.nr;

SELECT DISTINCT t1.rec FROM t1 INNER JOIN t2 ON t1.datum >= t2.begindatum AND t1.datum <= t2.einddatum AND t1.ref < t2.ref WHERE t1.nr = t2.nr;

SELECT DISTINCT t1.rec FROM t1 INNER JOIN t2 ON t1.datum >= t2.begindatum AND t1.datum <= t2.einddatum AND t1.ref < t2.ref AND t1.nr = t2.nr;

-- Q2: alternatives of Q1, but returns correct result, using subqueries to avoid the problem

SELECT DISTINCT rec FROM ( SELECT t1.rec as rec, t1.ref as refA, t2.ref as refB FROM t1 INNER JOIN t2 ON t1.nr = t2.nr WHERE t1.datum >= t2.begindatum AND t1.datum <= t2.einddatum) as tmp WHERE tmp.refA < tmp.refB;

SELECT DISTINCT rec FROM ( SELECT t1.rec as rec, t1.ref as refA, t2.ref as refB, t1.nr AS nrA, t2.nr AS nrB FROM t1 INNER JOIN t2 ON t1.datum >= t2.begindatum AND t1.datum <= t2.einddatum) as tmp WHERE tmp.refA < tmp.refB AND nrA = nrB;

-- Q3: with one condiction less than Q1, but returns correct result
SELECT DISTINCT t1.rec FROM t1 INNER JOIN t2 ON t1.datum >= t2.begindatum AND t1.datum <= t2.einddatum WHERE t1.ref < t2.ref;

DROP TABLE t1;
DROP TABLE t2;

Comment 20539

Date: 2014-12-18 12:42:42 +0100
From: MonetDB Mercurial Repository <>

Changeset 1302c3d9425e 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=1302c3d9425e

Changeset description:

Added test for Bug #3642

Comment 20540

Date: 2014-12-19 14:18:18 +0100
From: MonetDB Mercurial Repository <>

Changeset 646c11e1fdac 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=646c11e1fdac

Changeset description:

add selections in range - multi column selects, fixes Bug #3642

Comment 20541

Date: 2014-12-19 14:18:53 +0100
From: @njnes

fixed by adding the selection (when this is not the first) in range (multicolumn)selects

Comment 20601

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

Oct2014-SP2 has been released.