06 · LambdaQuery

An old query compiler, revisited

This goes back to 2017, and I've been revisiting it in 2026. The idea is to build a query out of Python expressions and have a compiler turn it into SQL.

The awkward part is keeping the meaning the same. If I count an empty list in Python I get zero. A SQL join can make that whole row disappear instead. A lot of the work here is in details like that.

pythonPython → SQL150 tests2017, rebuilt 2026

An example

What I want to ask the database

For each school with at least one program, find the average number of courses worth more than three credits across its departments. Here's what that looks like in the query API and in the generated SQL.

FIG. 1 · Three levels of correlation

writtenSchool.query(
  lambda x: x.get_foreign('Program')
             .count() > 0
).fmap(
  lambda x:
    x.get_foreign('Department').fmap(
      lambda y:
        y.get_foreign('Course')
         .filter(lambda z: z.credits > 3)
         .count()
    ).avg() % x.name
)

compiledSELECT q2.avg_n, s.name
FROM School AS s
LEFT JOIN (
  SELECT AVG(COALESCE(q1.n, 0)) AS avg_n,
         d.school_code
  FROM Department AS d
  LEFT JOIN (
    SELECT COUNT(c.course_code) AS n,
           c.dept_code
    FROM Course AS c
    WHERE c.credits > 3
    GROUP BY c.dept_code
  ) AS q1 ON q1.dept_code = d.dept_code
  GROUP BY d.school_code
) AS q2 ON q2.school_code = s.school_code
WHERE EXISTS (
  SELECT 1 FROM Program AS p
  WHERE p.school_code = s.school_code)

The EXISTS checks for a program. LEFT JOIN and COALESCE keep a department with no matching courses in the calculation, with a count of zero.

The nested lambdas can refer to outer rows. The compiler has to turn those references into joins while keeping the empty groups. That ends up being more involved than translating each method call into a SQL keyword.

Underneath

Untangling the nested queries

Outer references. Give an inner scope its own correlation domain, then pull the join conditions upwards. One pass can expose another reference, so this repeats until nothing changes.
Flattening. Merge subqueries where that's safe. Aggregates need more care, because flattening them can lose rows for empty groups.
Simplifying. A condition like count() > 0 can become EXISTS. These rewrites are intended to keep the result the same; comparing actual query outputs is how I check that.

One implementation detail that matters more than it first appears: shared subqueries are tracked by object identity. The passes work on one private copy. Copying again halfway through can give two different versions of what was meant to be the same subquery.

Checking the answers

Running the same question in Python and SQL

The useful check is to run the generated SQL in DuckDB and compare it with a plain Python comprehension over the same data.

The examples include schools with no departments and departments with no courses. Those are easy to overlook when a query looks right on a more convenient dataset.

Two bugs that this caught:

OR precedence. filter(A | B).filter(C) generated x OR y AND z. Without parentheses, SQL answers a different question. Checking the generated query's shape hadn't caught it.
Outer references. One non-key reference joined against an unconstrained copy of the outer table and quietly over-counted. Comparing the result with Python made the mistake visible.

Unfinished bits

What I'd work on next

Open question

I'd start the result-comparison checks much earlier if I were doing it again. They caught mistakes that plenty of tests of the query structure had missed.

Open question II

Child-to-parent traversal and Query.bind aren't implemented. There's also a limitation where filtering after fmap discards the projection, so this is still a project to work on rather than a finished query library.

MaterialsPython, DuckDB, networkx

Vintage2017, rebuilt 2026

NotesNested queries, empty groups and result comparisons

Source github/xnmp/LambdaQuery_2