060. Share repeated PostgreSQL flatmap expressions

• Status: Implemented
• Date: 2026-10-16

Problem and mechanism

A flatmap() callback may emit repeated pure expressions among other list leaves. Lowering each repeated copy independently repeats row expansion and its SQL dependencies. A candidate PostgreSQL lowering validates the original constructor, then groups repeated leaves by strict typed-tree identity and shares one ordered row source for each eligible group.

Identity includes node kind and resolved type, no-value capability, lexical binding identity and source path, typed literal value (including Float bits), operator and field details, guard constraints, resolved built-in method signature, child order, and backend order demand. Each shared expression must contain at least one bound lexical reference; it may be a captured outer binding. Unbound literal leaves remain independent. The proof does not use SQL-text or result-type equality. Callback nodes, custom methods, unsupported nodes, and unsupported literal forms make the whole callback list use ordinary lowering. A shared row source preserves each group's original output positions, order, null values, no-value failure from any leaf, captures, and first descriptor. The position map remains separate, so later literal indexing can select the corresponding original descriptor without reconstructing all row alternatives. Guarded-list normalization remains in its owning descriptor scope. A subsequent transformation discards this literal-position view; its consumer must use a separately supported lowering path. The original constructor validation still runs before the shared source is used.

Applicability

Reproduction

The baseline is 92803d8. The candidate implementation accompanies this record and is based on that revision. The public API generator uses Go 1.27.1 and emits parameterized WHERE SQL and ordered arguments for JSON and JSONB; it does not execute queries or measure runtime performance.

Save the 14-case audit program as /private/tmp/hff-repeat-mixed-audit.go:

package main

import (
	"context"
	"encoding/json"
	"fmt"
	"os"
	"strings"

	"github.com/ornew/hff"
	sql "github.com/ornew/hff/backends/sql"
	"github.com/ornew/hff/capabilities"
	"github.com/ornew/hff/engine"
	"github.com/ornew/hff/schema"
)

func main() {
	e, err := engine.New(
		engine.WithSchema(schema.Struct(schema.Optional(schema.Nullable(
			schema.Map("labels", schema.NullableType(schema.BytesType)),
		)))),
		engine.WithCapabilities(capabilities.Standard()),
	)
	if err != nil {
		panic(err)
	}

	sources := []string{
		`labels:items:flatmap(p=>[p[1],p[1]]):map(v=>v:type):json:exists`,
		`labels:items:flatmap(p=>[p[1],p[1],p[1]]):map(v=>v:type):json:exists`,
		`labels:items:flatmap(p=>[p[1]]:concat([p[1]])):map(v=>v:type):json:exists`,
		`labels:items:flatmap(p=>[p[1]!,p[1]!]):map(v=>v:type):json:exists`,
		`labels:items:flatmap(p=>[p[1]:[bytes],p[1]:[bytes]]):map(v=>v:type):json:exists`,
		`labels:items:flatmap(p=>[p[0]:len+1,p[0]:len+1]):json:exists`,
		`labels:items:flatmap(p=>[p[1]:base64,p[1]:base64]):json:exists`,
		`labels:items:flatmap(p=>[[p[1]],[p[1]]]):flatmap(v=>v):map(v=>v:type):json:exists`,
		`labels:items:flatmap(p=>[{"payload":p[1]},{"payload":p[1]}]):map(v=>v.payload:type):json:exists`,
		`labels:items:flatmap(p=>[p[labels:size-1],p[labels:size-1]]):map(v=>v:type):json:exists`,
		`labels:items:flatmap(p=>[p[1],p[1]]):flatmap(v=>[v,v]):map(v=>v:type):json:exists`,
		`labels:items:flatmap(p=>[p[1],p[0],p[1]]):map(v=>v:type):json:exists`,
		`labels:items:flatmap(p=>[p[1],p[0],p[1],p[0]]):map(v=>v:type):json:exists`,
		`labels:items:flatmap(p=>[p[1],[],p[0],p[1],null]):map(v=>v:type):json:exists`,
	}

	var results []map[string]any
	for _, format := range []sql.JSONFormat{sql.JSON, sql.JSONB} {
		for _, source := range sources {
			program, err := e.Compile(context.Background(), source)
			if err != nil {
				panic(err)
			}
			query, err := sql.Build(context.Background(), program,
				sql.WithDialect(sql.PostgreSQL),
				sql.WithBindings(sql.BindJSON(hff.Path{"labels"}, sql.Column("records", "labels"), format)),
			)
			if err != nil {
				panic(err)
			}
			results = append(results, map[string]any{
				"format": format,
				"source": source,
				"where":  query.Where(),
				"args":   query.Args(),
			})
			fmt.Fprintf(os.Stderr, "%s %dB %dargs %dunion %darray-sites %s\n",
				format, len(query.Where()), len(query.Args()),
				strings.Count(query.Where(), "UNION ALL"),
				strings.Count(query.Where(), "json_array_elements(")+strings.Count(query.Where(), "jsonb_array_elements("),
				source,
			)
		}
	}
	if err := json.NewEncoder(os.Stdout).Encode(results); err != nil {
		panic(err)
	}
}

Run the same generator from the baseline and candidate checkouts:

GOTOOLCHAIN=go1.27.1 go run /private/tmp/hff-repeat-mixed-audit.go \
  > /private/tmp/hff-repeat-mixed-before.json \
  2> /private/tmp/hff-repeat-mixed-before.log

GOTOOLCHAIN=go1.27.1 go run /private/tmp/hff-repeat-mixed-audit.go \
  > /private/tmp/hff-repeat-mixed-after.json \
  2> /private/tmp/hff-repeat-mixed-after.log

The 14-case audit compared generated SQL size and argument counts; the matching raw JSON and log files are retained under /private/tmp/hff-repeat-mixed-*. These observations describe SQL shape, not runtime performance.

Results

Generated WHERE byte counts, baseline → candidate:

The audit did not measure query execution, allocations, or runtime. The key-length arithmetic and dynamic-index controls retained their original SQL shape. Plain EXPLAIN (without ANALYZE) was collected for repeated slot, triple-slot, base64, nested, object, and nested-chain workloads on PostgreSQL 18.6, with JIT disabled and a 30-second statement timeout. The fixture used records(labels) with {"app":"YQ==","z":null}. Baseline → candidate counts for Function Scan / Append / Aggregate / Values Scan / Materialize were:

These are plan-shape observations only; they do not establish execution counts or runtime improvement.

Correctness and tradeoffs

The optimization applies only when strict typed-tree identity proves repeated leaves equivalent and each shared group contains a bound lexical reference, which may be a capture. Other expressions and unbound literals remain in their original positions. The published 14-case generator reproduced all 28 JSON and JSONB SQL-and-argument records. The related PostgreSQL selection passed 230,532 logged query/record results across 418 groups. The 89-source by 19-fixture JSON/JSONB matrix is included in that count, with negative Build cases excluded. Separate exact-output and raw-Float boundary assertions also passed; those are not included in the logged total. Build tests checked 112 negative combinations and confirmed their diagnostics against the baseline. Root unit, vet, integration vet, SQL race, and WASM checks passed. This selected matrix is not a claim of full backend conformance. Logs are retained at /private/tmp/hff-repeat-mixed-final2-pg.log and /private/tmp/hff-repeat-mixed-final2-checks.log.

The plain EXPLAIN rows above compare plan shape, not execution counts or runtime. No benchmark or EXPLAIN ANALYZE was run.

Decision and limits

Extend sharing to repeated groups that meet the proof above while retaining the existing parameter-only path. Keep ordinary lowering for unsupported callback lists; this is not general common-subexpression elimination across arbitrary callbacks or transformations. The earlier parameter-only evidence remains in the parameter record.