| Bug #31099 | Query with LEFT JOIN in subquery incorrectly treats WHERE condition | ||
|---|---|---|---|
| Submitted: | 19 Sep 2007 15:08 | Modified: | 30 Sep 2008 16:27 |
| Reporter: | Giedrius Noreikis | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | MaxDB | Severity: | S1 (Critical) |
| Version: | 7.6.00.37 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | left join | ||
[19 Sep 2007 15:11]
Valeriy Kravchuk
Thank you for a problem report. What exact version is it? 7.6.00.37 is the latest released in 7.6 branch.
[20 Sep 2007 11:05]
Giedrius Noreikis
Thank you for a quick reply. Sorry, I've got a little bit confused about the MaxDB versions. The version I was using is 7.6.03.07 (SAP DB KERNEL 7.6.03 BUILD 007-121-157-515 is displayed on connection). I've found out it was downloaded from sdn, so I've asked to install the latest 7.6.00.37 from your site (message SAP DB KERNEL 7.6.00 BUILD 037-121-149-748 is displayed now when connecting). Nevertheless, the same situation can be reproduced in this version too.
[21 Sep 2007 9:30]
Burkhard Diesing
Hello, thanks reporting the bug and thanks for the detailed description. I have made an error entry in our bug tracking system and we will fixes this in the next version. You can follow the state with http://www.sapdb.org/webpts?wptsdetail=yes&ErrorType=0&ErrorID=1150727 For future help and questions about MaxDB please consult the MaxDB forum on the SAP Developer Network. https://forums.sdn.sap.com/forum.jspa?forumID=90&start=0 Regards, Burkhard --- Burkhard Diesing Development Manager MaxDB&liveCache SAP AG mailto:burkhard.diesing@sap.com www.sap.com Sitz der Gesellschaft/Registered Office: Walldorf, Germany Vorstand/SAP Executive Board: Henning Kagermann (Sprecher/CEO), Shai Agassi, Léo Apotheker, Werner Brandt, Claus Heinrich, Gerhard Oswald, Peter Zencke Vorsitzender des Aufsichtsrats/Chairperson of the SAP Supervisory Board: Hasso Plattner Registergericht/Commercial Register Mannheim No HRB 350269
[30 Sep 2008 16:27]
Konstantin Osipov
MaxDB is no longer under MySQL umbrella
Description: Query with LEFT JOIN in subquery incorrectly treats WHERE condition, producing wrong result set. The query SELECT * FROM ( SELECT a.id a_id,b.id b_id FROM a LEFT JOIN b ON a.id = b.id ) a_b JOIN c ON a_b.a_id = c.id WHERE c.v = 1 produces result set keeping all rows from a_b and generating null values for c.v, despite the condition "c.v = 1". How to repeat: Create the following tables: CREATE TABLE A ( ID INTEGER NOT NULL ) CREATE TABLE B ( ID INTEGER NOT NULL ) CREATE TABLE C ( ID INTEGER NOT NULL, V INTEGER NOT NULL ) Insert the following values: INSERT INTO A(ID) VALUES(1) INSERT INTO A(ID) VALUES(2) INSERT INTO A(ID) VALUES(3) INSERT INTO B(ID) VALUES(1) INSERT INTO B(ID) VALUES(2) INSERT INTO B(ID) VALUES(3) INSERT INTO C(ID,V) VALUES(1,1) INSERT INTO C(ID,V) VALUES(2,2) INSERT INTO C(ID,V) VALUES(3,3) Now execute the query: SELECT * FROM ( SELECT a.id a_id,b.id b_id FROM a LEFT JOIN b ON a.id = b.id ) a_b JOIN c ON a_b.a_id = c.id WHERE c.v = 1 The returned result set is: A_ID B_ID ID V ---- ---- ------ ------ 1 1 1 1 2 2 (null) (null) 3 3 (null) (null) The last two rows with null values should not be included in the result set. BTW, changing left join to inner join in the subquery gives the correct result: SELECT * FROM ( SELECT a.id a_id,b.id b_id FROM a JOIN b ON a.id = b.id ) a_b JOIN c ON a_b.a_id = c.id WHERE c.v = 1 returns A_ID B_ID ID V ---- ---- -- - 1 1 1 1 The bug was noticed in MaxDB 7.6.03, after upgrade from 7.5.0, which handled such queries correctly..