Incorrect SQL created when a table joins multiple self-joined relation tables

[kilahsu]

Version: 3.5.0
Module: quill-jdbc-monix
Database: PostgreSQL

Actual behavior

Consider the following tables

case class Person(name: String, age: Int)
case class Dog(name: String)
case class Cat(name: String)
case class PersonDogs(personName: String, dogName: String)
case class PersonCats(personName: String, catName: String)

Where PersonDogs and PersonCats are relation tables between Person and Dog/Cat tables

The goal is to implement an "AND-OF-ORs" query generation -- i.e. given something like

val dogFilter: Option[List[List[String]]] = Some(List(List("Dog1", "Dog2"), List("Dog3", "Dog4")))
val catFilter: Option[List[List[String]]] = Some(List(List("Cat1", "Cat2"))) 

Find all persons that has ((Dog1 OR Dog2) AND (Dog3 OR Dog4)) AND (Cat1 OR Cat2)
e.g. A person has Dog1 and Dog3 and Cat1 matches
while a person has Dog1 and Dog2 and Cat2 does not

The following helper function was created

implicit class AndOfOrFilterSet[DIGEST, RELATION](self: DynamicQuery[DIGEST]) {

  def filterSetContains[PKTYPE: Encoder](relationTableQuery: DynamicQuery[RELATION],
                                         setOfSetOpt: Option[List[List[PKTYPE]]])
                                        (attributeToFilter: (Quoted[RELATION]) => Quoted[PKTYPE],
                                         joinCondition: (Quoted[DIGEST], Quoted[RELATION]) => Quoted[Boolean],
                                         primaryKeyCondition: (Quoted[RELATION], Quoted[RELATION]) => Quoted[Boolean]) : DynamicQuery[DIGEST] = {
    setOfSetOpt match {
      case Some(setOfSet) if setOfSet.nonEmpty && setOfSet.head.nonEmpty =>
        val filteredOther: DynamicQuery[RELATION] = setOfSet.foldLeft(relationTableQuery) {
          (currentQuery, set) =>
            currentQuery
              .join(relationTableQuery)
              .on(primaryKeyCondition)
              .filter(qq => liftQuery(set).contains(attributeToFilter(qq._2)))
              .map(_._2)
        }
        self
          .join(filteredOther)
          .on(joinCondition)
          .map(_._1)
      case _ =>
        self
    }
  }
}

When filtering with just dogs, it works ok

val q0 = dynamicQuery[Person]
          .filterSetContains(dynamicQuery[PersonDogs], dogFilter)(_.dogName, _.name == _.personName, _.personName == _.personName)

generates

SELECT
   v02.name,
   v02.age 
FROM
   person v02 
   INNER JOIN
      (
         SELECT
            v11.personname AS _2personname 
         FROM
            (
               SELECT
                  v1.personname AS _2personname 
               FROM
                  persondogs v0 
                  INNER JOIN
                     persondogs v1 
                     ON v0.personname = v1.personname 
               WHERE
                  v1.dogname IN 
                  (
                     'Dog1',
                     'Dog2'
                  )
            )
            AS v0 
            INNER JOIN
               persondogs v11 
               ON v0._2personname = v11.personname 
         WHERE
            v11.dogname IN 
            (
               'Dog3',
               'Dog4'
            )
      )
      AS t 
      ON v02.name = t._2personname

However, if using more than one filter, it generates invalid SQL

SELECT
   v02.name,
   v02.age 
FROM
   person v02 
   INNER JOIN
      (
         SELECT
            v11.personname AS _2personname 
         FROM
            (
               SELECT
                  v1.personname AS _2personname 
               FROM
                  persondogs v0 
                  INNER JOIN
                     persondogs v1 
                     ON v0.personname = v1.personname 
               WHERE
                  v1.dogname IN 
                  (
                     'Dog1',
                     'Dog2'
                  )
            )
            AS v0 
            INNER JOIN
               persondogs v11 
               ON v0._2personname = v11.personname 
         WHERE
            v11.dogname IN 
            (
               'Dog3',
               'Dog4'
            )
      )
      AS t 
      ON v02.name = t._2personname 
   INNER JOIN
      (
         SELECT
            v13.name AS _2name,
            v13.personname AS _2personname 
         FROM
            personcats v03 
            INNER JOIN
               personcats v13 
               ON v03.personname = v13.personname 
         WHERE
            v0._2catname IN 
            (
               'Cat1',
               'Cat2'
            )
      )
      AS v0 
      ON v0._2name = v0._2personname

Note that the Cat part is wrong -- v13.name for example, does not exist.

Expected behavior

Correct SQL is generated

Steps to reproduce the behavior

The issue can be reproduced in https://scastie.scala-lang.org/Tgcmk73cT3emssPiWA2Nwg

Workaround

I have not found a workaround
@getquill/maintainers