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
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:
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.
The text was updated successfully, but these errors were encountered: