Issue 117093

Summary: Multiple Left Join gives wrong result
Product: Base Reporter: r4zoli <r4zoli>
Component: codeAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Normal    
Priority: P3 CC: frank.schoenheit, issues
Version: DEV300m100Keywords: regression
Target Milestone: ---   
Hardware: PC   
OS: Windows 7   
URL: https://bugs.freedesktop.org/show_bug.cgi?id=34410
Issue Type: DEFECT Latest Confirmation in: ---
Developer Difficulty: ---
Attachments:
Description Flags
document to reproduce the bug case none

Description r4zoli 2011-02-26 13:57:10 UTC 
This happens in OOo 3.3, dev300m100, cws dba34d.

This happens, too, in libO 3.3 see bug report 34410: https://bugs.freedesktop.org/show_bug.cgi?id=34410

SELECT "Funktion"."Funktion", "Name"."Name", "Name"."ID" AS "MitgliedNr.", "rel_Fun_Nam"."Datum" 
FROM "Funktion" 
LEFT JOIN "rel_Fun_Nam" 
ON "rel_Fun_Nam"."funID" = "Funktion"."ID" AND "rel_Fun_Nam"."EndDat" IS NULL 
LEFT JOIN "Name" 
ON "rel_Fun_Nam"."namID" = "Name"."ID" 
ORDER BY "Funktion"."ID" ASC

To reproduce open odb file attached to the LibO bug.

Run query, the results contains,
 - 0  in  fields "MitgliedNr., 
 - 1-01-02 - wrong date - instead of null in both places.
 
If I run query in SQL direct mode - get same result.

If I run query in hsqldb manager, gives correct results.

If you open query for edit in dba34d cws I get assertion:
---------------------------
Debug Output
---------------------------
Error: Dialog::StartExecuteModal() - Parent not visible
From File D:/tools/o_dba34d/DEV300/ooo/vcl/source/window/dialog.cxx at Line 636
Abort ? (Yes=abort / No=ignore / Cancel=core dump)
---------------------------
Igen   Nem   Mégse   
---------------------------

When click on no, then opens in SQL view.

If you try to switch from SQL view to design view, I get error:

SQL Status: HY000
Error code: 1000

Join could not be processed
Comment 1 Frank Schönheit 2011-03-28 10:55:04 UTC 
Created attachment 76202 [details]
document to reproduce the bug case

(bug doc transferred from LibO bug, for sake of this bug report being self-contained)
Comment 2 Frank Schönheit 2011-03-28 10:57:02 UTC 
confirming, assigning, targeting
adding "regression" keyword, since the query works fine in OOo 3.1.1, but not in OOo 3.2.0 and later
Comment 3 ocke.janssen 2011-04-05 05:37:15 UTC 
The fix is done in the newest version of hsqldb 2.1.
Comment 4 Oliver-Rainer Wittmann 2012-06-13 12:23:34 UTC 
getting rid of value "enhancement" for field "severity".
For enhancement the field "issue type" shall be used.