for-comprehension with if-join and leftJoin results in mixing implicit join and explicit left join.

[mxl]

Version: 1.0.1-SNAPSHOT
Module: quill-async-postgres
Database: postgres

package app

import io.getquill.{PostgresAsyncContext, SnakeCase}

import scala.concurrent.Await
import scala.concurrent.duration.Duration

case class A(id: Int)

case class B(id: Int)

case class C(id: Int)

object Main extends App {
  implicit val context = new PostgresAsyncContext[SnakeCase]("database")

  import context._
  import scala.concurrent.ExecutionContext.Implicits.global

  val quote1 = quote {
    for {
      a <- query[A]
      b <- query[B] if a.id == b.id
      c <- query[C].leftJoin(_.id == a.id)
    } yield (a.id, b.id, c.map(_.id))
  }

  val quote2 = quote {
    for {
      a <- query[A]
      b <- query[B].join(_.id == a.id)
      c <- query[C].leftJoin(_.id == a.id)
    } yield (a.id, b.id, c.map(_.id))
  }

  Await.result(context.run(quote1), Duration.Inf)
  Await.result(context.run(quote2), Duration.Inf)
}

Expected behavior

It's expected that both quote1 and quote2 compile to the same valid query.

Actual behavior

quote1 compiles to:

SELECT a.id, b.id, x1.id FROM a a, b b LEFT JOIN c x1 ON x1.id = a.id WHERE a.id = b.id

which fails in runtime with:

[error] (run-main-3) com.github.mauricio.async.db.postgresql.exceptions.GenericDatabaseException: ErrorMessage(fields=Map(Position -> 66, Line -> 2846, Hint -> There is a
n entry for table "a", but it cannot be referenced from this part of the query., File -> parse_relation.c, SQLSTATE -> 42P01, Routine -> errorMissingRTE, Message -> inval
id reference to FROM-clause entry for table "a", Severity -> ERROR))
com.github.mauricio.async.db.postgresql.exceptions.GenericDatabaseException: ErrorMessage(fields=Map(Position -> 66, Line -> 2846, Hint -> There is an entry for table "a"
, but it cannot be referenced from this part of the query., File -> parse_relation.c, SQLSTATE -> 42P01, Routine -> errorMissingRTE, Message -> invalid reference to FROM-
clause entry for table "a", Severity -> ERROR))
at com.github.mauricio.async.db.postgresql.PostgreSQLConnection.onError(PostgreSQLConnection.scala:172)
at com.github.mauricio.async.db.postgresql.codec.PostgreSQLConnectionHandler.channelRead0(PostgreSQLConnectionHandler.scala:206)
at io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:105)
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:348)
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:334)
at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:326)
at io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:293)
at io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:267)
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:348)
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:334)
at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:326)
at io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1320)
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:348)
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:334)
at io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:905)
at io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(AbstractNioByteChannel.java:123)
at io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.java:563)
at io.netty.channel.nio.NioEventLoop.processSelectedKeysOptimized(NioEventLoop.java:504)
at io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.java:418)
at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:390)
at io.netty.util.concurrent.SingleThreadEventExecutor$5.run(SingleThreadEventExecutor.java:742)
at java.lang.Thread.run(Thread.java:745)

running this query in postgres CLI also gives:

LINE 1: ...id, x1.id FROM a a, b b LEFT JOIN c x1 ON x1.id = a.id WHERE...
                                                             ^
HINT:  There is an entry for table "a", but it cannot be referenced from this part of the query.

It seems that according to this SO answer explicit joins are performed before implicit ones.

quote2 compiles to:

SELECT a.id, x3.id, x4.id FROM a a INNER JOIN b x3 ON x3.id = a.id LEFT JOIN c x4 ON x4.id = a.id

which is valid and currently it's a workaround.

@getquill/maintainers