You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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;
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:
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:
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.
The text was updated successfully, but these errors were encountered: