Function applyOptimizations

packages/db/src/query/optimizer.ts:715–816

* Step 4: Apply optimizations by lifting single-source clauses into subqueries. * * Creates a new QueryIR with single-source WHERE clauses moved to subqueries * that wrap the original table references. This ensures immutability and prevents * infinite recursion issues. * * @param query - Origi

(
  query: QueryIR,
  groupedClauses: GroupedWhereClauses,
)

 * @returns New QueryIR with optimizations applied
 */
function applyOptimizations(
  query: QueryIR,
  groupedClauses: GroupedWhereClauses,
): QueryIR {
  // Track which single-source clauses were actually optimized
  const actuallyOptimized = new Set<string>()

  // Determine which source aliases are on the nullable side of outer joins.
  const nullableSources = getNullableJoinSources(query)

  // Build a filtered copy of singleSource that excludes nullable-side clauses.
  // Pushing a WHERE clause into the nullable side's subquery pre-filters the
  // data before the join, converting "matched but WHERE-excluded" rows into
  // "unmatched" outer-join rows.  These are indistinguishable from genuinely
  // unmatched rows, so the residual WHERE cannot correct the result.
  const pushableSingleSource = new Map<string, BasicExpression<boolean>>()
  for (const [source, clause] of groupedClauses.singleSource) {
    if (!nullableSources.has(source)) {
      pushableSingleSource.set(source, clause)
    }
  }

  // Optimize the main FROM clause and track what was optimized
  const optimizedFrom = optimizeFromWithTracking(
    query.from,
    pushableSingleSource,
    actuallyOptimized,
  )

  // Optimize JOIN clauses and track what was optimized
  const optimizedJoins = query.join
    ? query.join.map((joinClause) => ({
        ...joinClause,
        from: optimizeJoinFromWithTracking(
          joinClause.from,
          pushableSingleSource,
          actuallyOptimized,
        ),
      }))
    : undefined

  // Build the remaining WHERE clauses: multi-source + residual single-source clauses
  const remainingWhereClauses: Array<Where> = []

  // Add multi-source clauses
  if (groupedClauses.multiSource) {
    remainingWhereClauses.push(groupedClauses.multiSource)
  }

  // Determine if we need residual clauses (when query has outer JOINs)
  const hasOuterJoins = nullableSources.size > 0

  // Add single-source clauses
  for (const [source, clause] of groupedClauses.singleSource) {
    if (!actuallyOptimized.has(source)) {
      // Wasn't optimized at all - keep as regular WHERE clause
      remainingWhereClauses.push(clause)
    } else if (hasOuterJoins) {