Lone left join doesn't compile to left join

[ollef]

Hey, and thanks for a great library!

I ran into something surprising today.

Consider the following (nonsensical) queries:

test :: IO ()
test = dumpSqlSelect $ do
  dep <- all_ (_departments employeeDbSettings)
  role <- leftJoin_
    (all_ (_roles employeeDbSettings))
    (\_ -> val_ False)
  pure (dep, role)

test2 :: IO ()
test2 = dumpSqlSelect $ do
  leftJoin_
    (all_ (_roles employeeDbSettings))
    (\_ -> val_ False)

They result in:

λ> test
SELECT `t0`.`name` AS "res0", `t0`.`head__first_name` AS "res1", `t0`.`head__last_name` AS "res2", `t0`.`head__created` AS "res3", `t1`.`for_employee__first_name` AS "res4", `t1`.`for_employee__last_name` AS "res5", `t1`.`for_employee__created` AS "res6", `t1`.`name` AS "res7", `t1`.`started` AS "res8"
FROM "departments" AS "t0"
LEFT JOIN "roles" AS "t1" ON (FALSE)
λ> test2
SELECT `t0`.`for_employee__first_name` AS "res0", `t0`.`for_employee__last_name` AS "res1", `t0`.`for_employee__created` AS "res2", `t0`.`name` AS "res3", `t0`.`started` AS "res4"
FROM "roles" AS "t0"
WHERE FALSE

The second does an ordinary select, so it won't return a null role row when the condition fails.

Is this the intended behaviour? It was quite surprising to me. I suppose a left join without a left table is an odd thing to do, but the types allow it after all...

For context, I had a query parameterised by what to select before a leftJoin_, and in some cases I already knew what row I needed so I passed a pure $ val_ something. I.e. I sometimes did do x <- all_ blah; leftJoin_ ..., and sometimes just do x <- pure (val_ something); leftJoin_ .... I would have expected the second version to work the same as when the all_ in the first version returns a single element something.

[tathougies]

I believe it was intended, in the sense that I figured there wasn't anything particularly sensible. NULL in SQL means 'unknown' so having an 'unknown' row seems odd. That being said, I'm open to changing the behavior.

[ollef]

Thanks for the answer. I might have a go at changing this if i can figure out something more sensible. Where in the code do you reckon I should be looking?

[tathougies]

Look in Query/SQL92.hs. That implements a 'compiler' from the free monad Q to the actual lower-level syntax. It's somewhat convoluted, because there's a lot of corner cases. Please add a test for the new behavior and documentation as well.

[ollef]

Hey again!

I couldn't quite understand how to achieve what I wanted.

I think I found the right place for the fix, namely right around https://github.com/tathougies/beam/blob/24a96f2f8d53558c33f1fe4031bd0687f9511e01/beam-core/Database/Beam/Query/SQL92.hs#L457 and some similar code in the next case arm.

My idea then is that we could select from a single value in the oldFrom if qbFrom returns Nothing, such that the code generated is something like:

SELECT ... FROM (VALUES (1)) x
LEFT JOIN "roles" AS "t1" ON (FALSE)

This would then give us the correct join semantics for code like do v <- pure x; leftJoin_ ...

I wasn't able to achieve this though, so have given up for now.