bug in sql compiler

[tpolecat]

This seems like a bug because it's producing invalid SQL, but it's also possible I'm doing something wrong.

I have the following mapping, where views are joined by a compound key. Note lines marked as 1 and 2.

ObjectMapping(
  tpe = ConstraintSetGroupType,
  fieldMappings = List(
    SqlField("key", ConstraintSetGroupView.ConstraintSetKey, key = true, hidden = true),
    SqlField("program_id", ConstraintSetGroupView.ProgramId, key = true, hidden = true),
    SqlObject("observations",
      /* 1 */ Join(ConstraintSetGroupView.ProgramId, ObservationView.ProgramId),
      /* 2 */ Join(ConstraintSetGroupView.ConstraintSetKey, ObservationView.ConstraintSetKey),
    ),
  )
)

object ConstraintSetGroupView extends TableDef("v_constraint_set_group") {
  val ProgramId = col("c_program_id",  program_id)
  val ConstraintSetKey = col("c_conditions_key", text)
  ...
}

object ObservationView extends TableDef("v_observation") {
  val ProgramId = col("c_program_id", program_id)
  val ConstraintSetKey = col("c_conditions_key", text)
  ...
}

GraphQL Schema and query look like this:

type Query {

  # Observations grouped by commonly held constraints
  constraintSetGroups(
    programId: ProgramId!
    ...
  ): [ConstraintSetGroup!]! 

}

# example query
query {
  constraintSetGroups(programId: "p-101") {
    observations {
      id
    }
  }
}

The elaborator discards all the arguments for now, so Select(constraintSetGroups, Nil, child) is what the compiler is seeing.

If I comment out 1 above, the query is correctly compiled to:

SELECT
  v_observation.c_observation_id,
  v_constraint_set_group.c_conditions_key,
  v_constraint_set_group.c_program_id
FROM
  v_constraint_set_group
  LEFT JOIN v_observation ON (
    v_observation.c_conditions_key = v_constraint_set_group.c_conditions_key
  )

If I comment out 2 above, the query is correctly compiled to:

SELECT
  v_observation.c_observation_id,
  v_constraint_set_group.c_conditions_key,
  v_constraint_set_group.c_program_id
FROM
  v_constraint_set_group
  LEFT JOIN v_observation ON (
    v_observation.c_program_id = v_constraint_set_group.c_program_id
  )

However if I leave them both in, it's compiled to this, which is invalid SQL because table
v_constraint_set_group is introduced twice. It's also quite complicated.

SELECT
  v_observation_v_constraint_set_group_nested.c_observation_id,
  v_constraint_set_group.c_conditions_key,
  v_constraint_set_group.c_program_id
FROM
  v_constraint_set_group
  LEFT JOIN v_constraint_set_group ON (
    v_constraint_set_group.c_program_id = v_constraint_set_group.c_program_id
  )
  LEFT JOIN LATERAL (
    SELECT
      v_observation.c_conditions_key AS c_conditions_key_alias_0,
      v_observation.c_observation_id
    FROM
      v_observation
      INNER JOIN v_constraint_set_group ON (
        v_constraint_set_group.c_conditions_key = v_observation.c_conditions_key
      )
  ) AS v_observation_v_constraint_set_group_nested ON (
    v_observation_v_constraint_set_group_nested.c_conditions_key_alias_0 = v_constraint_set_group.c_conditions_key
  )

I would have expected something like:

SELECT
  v_observation.c_observation_id,
  v_constraint_set_group.c_conditions_key,
  v_constraint_set_group.c_program_id
FROM
  v_constraint_set_group
  LEFT JOIN v_observation ON (
    v_observation.c_conditions_key = v_constraint_set_group.c_conditions_key,
    v_observation.c_program_id = v_constraint_set_group.c_program_id
  )