Oracle: outer join retrieves no result

[object]

When using query with outer join syntax (operator !!) no results are retrieved in case the joined table has no matching rows, so outer join works as inner join.

Example:

query { for p in dbContext.Granitt.Programmes do
        join d in (!!) dbContext.Granitt.ProgrammesDistributions on (p.ProgrammeId = d.ProgrammeId)
        where (p.PiProgId = "KOID35004614") 
        select p.PiProgId }

dbContext.Granitt.Programmes has a record with PiProgId "KOID35004614", so if remove a join on dbContext.Granitt.ProgrammesDistributions, I get back correct result. But if I join two tables on ProgrammeId (primary key on Programmes and foreign key on ProgrammesDistributions), then I get no results.

The database is Oracle.

Here's a generated query:

Executing SQL: SELECT p.PI_PROG_ID as "p.PI_PROG_ID",p.PROGRAMME_ID as "p.PROGRAMME_ID",d.PROGRAMME_DISTRIBUTION_ID as "d.PROGRAMME_DISTRIBUTION_ID" FROM GRANITT.PROGRAMMES p INNER JOIN GRANITT.PROGRAMMES_DISTRIBUTIONS d on p.PROGRAMME_ID = d.PROGRAMME_ID WHERE ((p.PI_PROG_ID = :param1)) - params :param1 - "KOID35004614";

As you can see, it's an INNER JOIN in the SQL statement.

[pezipink]

It might be useful in this case if we can have a look at what SQL it is generating, you can use FSharp.Data.Sql.Common.QueryEvents.SqlQueryEvent |> Event.add (printfn "Executing SQL: %s") to listen in to what is being submitted to the database.

[object]

I have updated the issue with generated SQL statement. It's an inner join that is generated.

[pezipink]

Sorry, I totally forgot about this. The Oracle code seems to be dealing with left joins appropriately, but I don't have an Oracle system to test it with. @colinbull ?

[object]

If you need to verify the fix, I have access to an Oracle system.

[colinbull]

I can have a look at this but unlikely to get to it until next week. So if someone can verify / fix sooner that be awesome.

[object]

Just let me know when there is a commit I can check.

[pezipink]

@colinbull did this get fixed in the end? I remember we (you) were looking at it at F#ex ..

[colinbull]

@pezi_pink no I do know what is wrong thou.. it is todo with the resolution of the outerjoin pattern not matching correctly probably needs to match on 'op_BangBang' or something like that.

[OnurGumus]

Any love for this issue? Working without left join is difficult.

[OnurGumus]

This is working now!