Left join operator - broken?

[piaste]

( ... Unless I've completely misunderstood how it's supposed to work? ... )

I tried to use a left join in a query and neither the left-joined entity nor its columns are marked as optional. This happens on Postgres, MSSQL, and SQLite, using UseOptionTypes=true.

I thought I mustbe doing something wrong since there was a unit test for left join, but then I noticed that that unit test is wrong: by calling fst it checks the None status of the column in the main table, rather than the left-joined one. The column in the main table happens to be nullable so the test passes.

I added a unit test for PostgreSQL and it does make the build fail.

Diving into the code didn't quite work. I found what I strongly suspect is a bug here, because the compiler treats (!!) as an arbitrary pattern match identifier rather than matching against the actual operator, but changing it to MethodWithName "op_BangBang" didn't fix the test.

I even went back and installed older versions of the library, but couldn't find one where (!!) worked as I expected.

[Thorium]

I think this is duplicate of #349

[Thorium]

Maybe if the provider has already decided that the manager-entity .LastName property is non-nullable, it's not easily converted to .IsSome / .IsNone and the column is returning Unchecked.defaultof<T> or something else like empty string. So the filtering can be done in the where-clause e.g. box(manager.LastName) = null or box(manager.LastName) <> null but not easily after the query has already been executed.

The join query seems to be a LEFT OUTER JOIN query so this is more about parsing the results than creating the query.

[piaste]

To be honest I don't think I understand how it was meant to work in the first place. (Pinging @pezipink ?)

The type provider emits a type called, say, ``SqlProvider<...>.dataContext.public.employeeEntity``, and that type has a string property called LastName. If you left join on employees, it cannot emit the same type but with a string option property instead. Is it supposed to create a new type, like ``SqlProvider<...>.dataContext.public.employee_1Entity``?

Speaking in principle, it would make far more sense to return an option of the whole type, that is, manager should be employeeEntity option. That would actually be more expressive, as it would correctly represent the effects of a left join (either all columns are null, or all columns retain their respective nullable or non-nullable status). I'm not sure this approach would be possible either, I haven't looked into it, but types-wise it feels more achievable.

[Thorium]

I think that we cannot reason about resultset, if the whole entity is null just because of left join, or is it just that all the columns happen to be null (if that even matters)?

TableA_Col1	TableB_Col1	TableB_Col2
Item1	Null	Null

[OnurGumus]

Left join is working with latest version now.

[Thorium]

Even if they are not option types, you can test the nullness in where-conditions with boxing, (box a.Id) = null

[jam40jeff]

I believe we can reason about the entity as a whole. If the join column is NULL on the table being joined to (right side), then the other (right side) entity should be None. A join table can only be joined by a matching non-NULL value. (I did test this by joining two nullable columns together and verifying that NULL will not join to NULL.)

As it stands right now, it is impossible to tell if a left join succeeded or not as a row on the join table with all default values would appear the same as a left join that did not match.