WHERE condition ignored after 2 LEFT JOINs with subqueries

[monetdb-team]

Date: 2012-02-23 15:41:05 +0100
From: Gatis Ozolins <<g.ozolins>>
To: SQL devs <>
Version: 11.7.5 (Dec2011) [obsolete]
CC: @njnes

Last updated: 2012-02-29 10:34:22 +0100

Comment 16983

Date: 2012-02-23 15:41:05 +0100
From: Gatis Ozolins <<g.ozolins>>

User-Agent: Mozilla/5.0 (Windows; Windows NT 6.1; rv:10.0) Gecko/20100101 Firefox/9.0
Build Identifier: Dec2011

Only happens if first left join is subquery and is followed by another left join.

Reproducible: Always

Steps to Reproduce:

Create sample data:
CREATE TABLE emp (
empno int,
ename varchar(10),
job varchar(9),
mgr int,
hiredate date,
sal decimal(7,2),
comm decimal(7,2),
deptno int
);

 INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800.00,NULL,20);
 INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600.00,300.00,30);
 INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250.00,500.00,30);
 INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975.00,NULL,20);
 INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250.00,1400.00,30);
 INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850.00,NULL,30);
 INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09',2450.00,NULL,10);
 INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,'1982-12-09',3000.00,NULL,20);
 INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000.00,NULL,10);
 INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500.00,0.00,30);
 INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,'1983-01-12',1100.00,NULL,20);
 INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,'1981-12-03',950.00,NULL,30);
 INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,'1981-12-03',3000.00,NULL,20);
 INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300.00,NULL,10);

SQL:
SELECT e.job, e.ename, ep.ename, epp.ename
FROM emp e
LEFT JOIN (SELECT * FROM emp) ep ON (e.mgr = ep.empno)
LEFT JOIN emp epp ON (ep.mgr = epp.empno)
WHERE e.job = 'SALESMAN'
ORDER BY 1,2,3,4

Actual Results:

ANALYST FORD JONES KING
ANALYST SCOTT JONES KING
CLERK ADAMS SCOTT JONES
CLERK JAMES BLAKE KING
CLERK MILLER CLARK KING
CLERK SMITH FORD JONES
MANAGER BLAKE KING
MANAGER CLARK KING
MANAGER JONES KING
PRESIDENT KING
SALESMAN ALLEN BLAKE KING
SALESMAN MARTIN BLAKE KING
SALESMAN TURNER BLAKE KING
SALESMAN WARD BLAKE KING

Expected Results:

SALESMAN ALLEN BLAKE KING
SALESMAN MARTIN BLAKE KING
SALESMAN TURNER BLAKE KING
SALESMAN WARD BLAKE KING

Comment 16989

Date: 2012-02-24 10:30:28 +0100
From: @njnes

Changeset 9f1cd8ded319 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=9f1cd8ded319

Changeset description:

fixed bug #3041. Make sure we push a select realy under a left join
not in a left join.