using Postgres lateral_ with guard_ generates incorrect SQL

[maerten]

It seems that using Postgres lateral_ and guard_ results in incorrect SQL.

At the end of the generated query, I see a clause:

WHERE ("t0"."res3") IS NOT DISTINCT FROM ("t0"."res5")
         ^--    (both referencing t0)    ---^

This is odd as the guard_ clause references two different Entities with the ==. operator.

Investigating this a bit further, it appears that all CROSS JOIN LATERAL end up with an alias AS "t0".

[kmicklas]

Can you post a minimal query that produces incorrect SQL?

[maerten]

Here's a sample project which reproduces the issue: https://github.com/maerten/beam-issue-example

[tathougies]

This is caused by buildQuery in beam-core/Database/Beam/Query/SQL92.hs assuming we can use the same table names in sub queries in joins, which is not a good assumption for lateral join. I think the proper approach is to thread the table prefix in buildQuery so that we can disambiguate the names. I will put up a PR

[tathougies]

Just pushed PR #647. @maerten would you mind verifying?

I did notice an error in your repository, you should use

deriving instance Show User

instead of

instance Show User

The former uses GHC's deriving mechanism to derive a proper implementation. The latter uses the default implementations in the Show class which are mutually recursive, thus leads to a stack overflow.

[maerten]

@tathougies thanks for the fix, the fix works in the example project, and in another project too.

Expanding on the lateral_ queries, what's the right place to add limit the results of a lateral_ query?
I tried adding limit_ as shown in the example at https://hackage.haskell.org/package/beam-postgres-0.5.2.1/docs/Database-Beam-Postgres-Full.html#g:3

However that results in a type error:

    • Couldn't match type ‘QBaseScope’
                     with ‘Database.Beam.Query.Internal.QNested QBaseScope’

[maerten]

And one last question, it looks like lateral_ always generates a CROSS LATERAL JOIN, even when using a leftJoin_.
This was unexpected to me, as the code for lateral_ looks like it would create different type of join if there is an 'on' clause. But perhaps I'm misunderstanding things..

My goal is to get something like a LATERAL LEFT JOIN, so the query always some results even if the joined tables are empty.

(Let me know if you think these are bugs, and they should get their own github issues instead!)

-- expanding on https://github.com/maerten/beam-issue-example
testQuery2 = do
    conn <- connectPostgreSQL "host=localhost dbname=testdb"
    result <- runBeamPostgresDebug putStrLn conn $
        runSelectReturningList $
          select $ do
            users <- all_ $ dbUsers myDb
            Pg.lateral_ (users) $ \user -> do
                -- using leftJoin, it still generates a CROSS LATERAL JOIN
                post <- leftJoin_ (all_ $ dbPosts myDb) (\p -> (postUsername p ==. userName user))
                return (user, post)
    print result