Date: 2011-04-06 15:01:31 +0200
From: Alfred Nordman <<alfred.nordman>>
To: SQL devs <>
Version: -- development
CC: @njnes

Last updated: 2011-04-28 16:25:39 +0200

Comment 15727

Date: 2011-04-06 15:01:31 +0200
From: Alfred Nordman <<alfred.nordman>>

User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:2.0) Gecko/20100101 Firefox/4.0
Build Identifier:

WHERE expressions are disregarded when their condition involves two columns of the same table which is being JOINed with another table in a nested SELECT expression, for example:

SELECT * FROM (SELECT * FROM t1 INNER JOIN t2 ON a = c) abc WHERE abc.a > abc.b;

when a and b originate in t1 (or t2).

However, inserting the WHERE expression inside the nested SELECT expression seems to produce the correct result.

Reproducible: Always

Steps to Reproduce:

CREATE TABLE t1 (a INT, b INT);
INSERT INTO t1 VALUES (1, 3);
CREATE TABLE t2 (c INT, d INT);
INSERT INTO t2 VALUES (1, 4);
SELECT * FROM (SELECT * FROM t1 INNER JOIN t2 ON a = c) abc WHERE abc.a > abc.b;

Actual Results:

+------+------+------+------+
| a | b | c | d |
+======+======+======+======+
| 1 | 3 | 1 | 4 |
+------+------+------+------+

Expected Results:

+---+---+---+---+
| a | b | c | d |
+===+===+===+===+
+---+---+---+---+

MonetDB 5 server v11.1.1 (64-bit, 32-bit oids)
Release Mar2011
Copyright (c) 1993-July 2008 CWI
Copyright (c) August 2008-2011 MonetDB B.V., all rights reserved
Visit http://monetdb.cwi.nl/ for further information
Found 8.0GiB available memory
Configured for prefix: C:\Users\sjoerd\Documents\src\MonetDB\stable\NTv64
Libraries:
libpcre: 7.9 2009-04-11
openssl: OpenSSL 1.0.0a 1 Jun 2010
libxml2: 2.7.7
Compiled by: sjoerd@ALGOL (x86_64-pc-winnt)
Compilation: cl -GF -W3 -wd4273 -wd4102 -MD -nologo -Ox
Linking : cl -GF -W3 -wd4273 -wd4102 -MD -nologo -Ox

Comment 15729

Date: 2011-04-08 15:48:46 +0200
From: @njnes

Changeset 1f35ebc64aea 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=1f35ebc64aea

Changeset description:

fixed bug #2801, push select down properly.

Comment 15730

Date: 2011-04-08 16:23:12 +0200
From: @njnes

fixed by pushing select down

Comment 15753

Date: 2011-04-28 16:25:39 +0200
From: @sjoerdmullender

The Apr2011 release has been uploaded.