jsonArrayFrom inside left lateral join returns column named "coalesce"

[weeksie]

First off, great library. I can't believe how few corner cases I run into. Really just awesome stuff.

Here's the relevant section of a query where I've run into this.

        eb
          .selectFrom("connectedDivisions")
          .leftJoinLateral(
            ({ selectFrom }) =>
              // throwing a bare jsonArrayFrom over this query 
              // doesn't let you add an alias for the column so 
              // you end up with `coalesce` as the column name 
              // b/c under the hood the query is
              //    SELECT coalesce(json_agg(...
              jsonArrayFrom(
                selectFrom("connectionDivisionFulfillmentRules")
                  .innerJoin(
                    "fulfillmentRules",
                    "fulfillmentRules.id",
                    "connectionDivisionFulfillmentRules.fulfillmentRuleId"
                  )
                  .whereRef(
                    "connectionDivisionFulfillmentRules.connectionDivisionId",
                    "=",
                    "connectedDivisions.id"
                  )
                  .selectAll("fulfillmentRules")
                  .select(({ ref }) => [
                    jsonObjectFrom(joinAddress(ref("fulfillmentRules.addressId"))).as("address"),
                    jsonObjectFrom(joinSchedule(ref("fulfillmentRules.scheduleId")))
                      .$notNull()
                      .as("schedule"),
                  ])
              ).as("rules"),
            (join) => join.onTrue()
          )
          .selectAll(["connectedDivisions"])
          // see left lateral join for why `rules.coalesce`
          .select(() => [sql`rules.coalesce`.as("rules")])
          .whereRef("connectedDivisions.connectionId", "=", "filteredConnections.id")

The reason I assume it's bug behavior is because the select clause can't pick up the rules.coalesce column so I have to dip into sql to get to it.

at the mechanical level it's clear that the coalesce function that's created by jsonArrayFrom isn't getting an alias so it's using the function name.

[igalklebanov]

Hey 👋

Can you provide a kyse.link that reproduces this?

[koskimas]

Is there a reason you're using lateral join instead of selecting the rules?

[weeksie]

@igalklebanov ah soz, I should have led with that. https://kyse.link/Zrxmu

@koskimas this is part of a larger query and even though you wouldn't necessarily think it from the query plan, the left lateral performs significantly better than a subselect in practice (in this case)