Date: 2011-09-13 12:59:32 +0200
From: andrij.usov
To: SQL devs <>
Version: 11.5.1 (Aug2011) [obsolete]
CC: @mlkersten, @njnes

Last updated: 2011-09-30 10:58:46 +0200

Comment 16214

Date: 2011-09-13 12:59:32 +0200
From: andrij.usov

User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:6.0.2) Gecko/20100101 Firefox/6.0.2
Build Identifier:

DB dump and the SQL Query

Query:

SELECT M_1.VC_4 AS V_1, M_1.VC_3 AS V_2, M_1.VC_1 AS V_3, M_1.VC_2 AS V_4
FROM
( SELECT COALESCE(T_2.o, T_4.o) AS VC_1, COALESCE(T_3.o, T_5.o) AS VC_2, T_1.o AS VC_3, T_1.s AS VC_4
FROM
( SELECT *
FROM Triples AS T_1
WHERE ( T_1.p = 'http://example/p' )
) AS T_1
LEFT OUTER JOIN
Triples AS T_2
INNER JOIN
Triples AS T_3
ON ( T_2.p = 'http://example/q1'
AND T_3.p = 'http://example/q2'
AND T_2.s = T_3.s )
ON ( T_1.o = T_2.s )
LEFT OUTER JOIN
Triples AS T_4
INNER JOIN
Triples AS T_5
ON ( T_4.p = 'http://example/q2'
AND T_5.p = 'http://example/q2'
AND T_4.s = T_5.s )
ON ( T_1.o = T_4.s
AND ( ( T_2.o IS NULL ) OR ( T_2.o = T_4.o ) )
AND ( ( T_3.o IS NULL ) OR ( T_3.o = T_5.o ) ) )
) AS M_1;

DB dump:

CREATE TABLE triples (
s varchar(255),
p varchar(255),
o varchar(255),
PRIMARY KEY (s, p, o)
);

INSERT INTO triples (s, p, o) VALUES
('http://example/a', 'http://example/q1', 'http://example/z11'),
('http://example/a', 'http://example/q1', 'http://example/z12'),
('http://example/a', 'http://example/q2', 'http://example/z21'),
('http://example/a', 'http://example/q2', 'http://example/z22'),
('http://example/b', 'http://example/q2', 'http://example/y21'),
('http://example/b', 'http://example/q2', 'http://example/y22'),
('http://example/c', 'http://example/r', 'http://example/rr'),
('http://example/x', 'http://example/p', 'http://example/a'),
('http://example/x', 'http://example/p', 'http://example/b'),
('http://example/x', 'http://example/p', 'http://example/c');

Reproducible: Always

Steps to Reproduce:

1. Load DB Dump
2. Run SQL query
3. Enjoy

Actual Results:

5 Rows:

http://example/x http://example/b http://example/y21 http://example/y21
http://example/x http://example/b http://example/y21 http://example/y22
http://example/x http://example/b http://example/y22 http://example/y21
http://example/x http://example/b http://example/y22 http://example/y22
http://example/x http://example/c

Expected Results:

9 Rows:

http://example/x http://example/a http://example/z11 http://example/z21
http://example/x http://example/a http://example/z11 http://example/z22
http://example/x http://example/a http://example/z12 http://example/z21
http://example/x http://example/a http://example/z12 http://example/z22
http://example/x http://example/b http://example/y21 http://example/y21
http://example/x http://example/b http://example/y21 http://example/y22
http://example/x http://example/b http://example/y22 http://example/y21
http://example/x http://example/b http://example/y22 http://example/y22
http://example/x http://example/c NULL NULL

Comment 16215

Date: 2011-09-14 14:51:54 +0200
From: @mlkersten

It looks like an operator priority issue in combination with presumed interpretation semantics.
In general, the join order and nesting should not affect the outcome.
The best way forward is to simply split the query into multiple easier pieces.
If my hypothesis is correct, then a fix will be included in the next bug-fix release.

Comment 16217

Date: 2011-09-14 15:11:55 +0200
From: andrij.usov

(In reply to comment 1)

It looks like an operator priority issue in combination with presumed
interpretation semantics.
In general, the join order and nesting should not affect the outcome.
The best way forward is to simply split the query into multiple easier pieces.
If my hypothesis is correct, then a fix will be included in the next bug-fix
release.

The query has been automatically produced by a framework. It is impossible to significantly modify it without re-developing large parts of this framework.

Comment 16220

Date: 2011-09-14 18:29:00 +0200
From: @mlkersten

The please inform us and the community, which general available framework you have been using to generate these queries. As you can imagine, a bug resulting from an open-source and widely used package receives more attention.

Comment 16221

Date: 2011-09-14 19:53:55 +0200
From: @njnes

seems a bug in the outer join with 'or'

Comment 16222

Date: 2011-09-14 20:41:37 +0200
From: andrij.usov

(In reply to comment 3)

The please inform us and the community, which general available framework you
have been using to generate these queries. As you can imagine, a bug resulting
from an open-source and widely used package receives more attention.

We are trying integrate Jena (Semantic Web framework for Java) (http://jena.sourceforge.net/).

Comment 16282

Date: 2011-09-16 15:10:41 +0200
From: @sjoerdmullender

The Aug2011 version has been released.

Comment 16294

Date: 2011-09-17 12:53:13 +0200
From: @mlkersten

MonetDB is not integrated (yet) with Jena. We appreciate if you take the lead and run the SDB test set to assess what features are missing and/or related bugs.
The outcome will be judged on impact and scheduled appropriately for resolution.

Comment 16298

Date: 2011-09-19 16:01:37 +0200
From: @sjoerdmullender

Changeset 248294d05358 made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

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

Changeset description:

Added test for bug #2879.

Comment 16328

Date: 2011-09-25 10:55:31 +0200
From: @njnes

fixed the implementation of outerjoins

Comment 16355

Date: 2011-09-30 10:58:46 +0200
From: @grobian

Released in Aug2011-SP1