Issue 117093 - Multiple Left Join gives wrong result
Summary: Multiple Left Join gives wrong result
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: DEV300m100
Hardware: PC Windows 7
: P3 Normal (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Keywords: regression
Depends on:
Reported: 2011-02-26 13:57 UTC by r4zoli
Modified: 2013-08-07 15:45 UTC (History)
2 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

document to reproduce the bug case (4.57 KB, application/octet-stream)
2011-03-28 10:55 UTC, Frank Schönheit
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
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:

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 
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.