#moonlake

Status: all 22 TPC-H queries pass on official texts. The evaluator is columnar — vectorized expression kernels, a columnar hash join, and bind-time projection pruning — but APIs may still change.

#What it is

• Register CSV/Parquet files as tables, run SQL, get columnar result batches. CSV column types are inferred (int32 → int64 → float64 for integers that outgrow their type, bool, date, string) — out-of-range values widen, never wrap.
• Parquet decoding is in-house (sources/parquet) and scoped on purpose: flat schemas, v1 data pages, PLAIN, dictionary, the DELTA family and BYTE_STREAM_SPLIT page encodings (the last five through colenc), RLE boolean values, UNCOMPRESSED and SNAPPY. Nested fields, INT96 timestamps, v2 pages and the other codecs are refused with a pointed error rather than guessed at. Encoding a table back out is the same scope: write_bytes / write_file emit one row group per batch with v1 pages, PLAIN and no compression; a column that holds a NULL is written OPTIONAL with RLE definition levels, the rest REQUIRED.
• Ships as a native single binary for ad-hoc command-line analysis, embeds as a MoonBit library, and compiles to WebAssembly (GC) from the same codebase.
• No FFI, no external database process, no storage engine — moonlake reads external files and computes in memory. Queries larger than memory degrade honestly: a CLI --memory budget (library execute_with + a spill store) spills a blocking ORDER BY to sorted runs and streams the result back; large joins and GROUP BYs partition their inputs to disk the same way.

#SQL surface

• SELECT (including *) / FROM (multi-table) / WHERE / GROUP BY / HAVING / ORDER BY / LIMIT
• INNER / LEFT / RIGHT / FULL / CROSS joins, UNION [ALL] / INTERSECT / EXCEPT (INTERSECT binds tighter; ALL keeps multisets) (hash join; join order chosen greedily by connectivity, LEFT keeps written order)
• Expressions: arithmetic, comparison, CASE WHEN, IN, NOT IN, BETWEEN, NOT BETWEEN, LIKE, NOT LIKE, IS [NOT] NULL, NULL as a comparison/arithmetic operand, EXTRACT(year/month/day), DATE literals, date +/- INTERVAL n day/month/year (calendar-aware, day-of-month clamping), CAST, string functions (substring — both argument styles, length, upper, lower, concat), abs, round, three-valued NULL logic throughout. Division is DuckDB-style: / is always DOUBLE (x/0 is ±inf, 0/0 is nan) and % follows fmod (x % 0 is NULL). Integer literals widen int32 → int64 → float64 to stay exact. Identifiers: the DATE-part keywords year/month/day/date double as bare column/table/alias names; double-quoted identifiers ("left", with "" escape) reach every other reserved word
• Aggregates: sum / avg / min / max / count / count(*) / count(DISTINCT) / stddev (+samp/pop) / variance (+samp/pop) / median / string_agg (literal separator); aggregates over int64 that overflow raise instead of wrapping
• GROUP BY 1 ordinals resolve to select items
• Window functions in the select list: row_number(), rank(), dense_rank() and every aggregate as agg(expr) OVER (PARTITION BY ... ORDER BY ...) (NULLS FIRST/LAST per key). Aggregates use the standard default frame — RANGE UNBOUNDED PRECEDING TO CURRENT ROW (a running aggregate whose peers share the value); without ORDER BY the frame is the whole partition. Explicit frame clauses (ROWS/RANGE BETWEEN ...), window functions in WHERE/ORDER BY/HAVING/GROUP BY (order by the output alias instead) and windows inside correlated subqueries are rejected with pointed errors
• Subqueries: correlated EXISTS / NOT EXISTS / IN / NOT IN — including a HAVING inside the EXISTS (decorrelated to grouped semi/anti joins, residuals and all) — and correlated scalar aggregate subqueries in WHERE and in the select list (decorrelated to grouped LEFT joins) — nested to the depth TPC-H asks; derived tables (FROM (SELECT ...) AS t), with optional column alias lists (AS t (a, b))
• WITH name AS (SELECT ...) — non-recursive CTEs, statement level; a name is its own qualifier, later CTEs may reference earlier ones
• Scalar functions: coalesce / nullif / greatest / least, date_trunc(unit, date), date_diff(unit, start, end); SELECT works without FROM and a bare NULL select item reads as INTEGER
• Pushdown: single-table predicates from WHERE/ON are applied as build/probe filters at each hash join; equalities become hash keys even when implied by disjunctions. At joins that preserve a side (LEFT/RIGHT/FULL), WHERE predicates — spanning ones included — stay post-join so NULL-extended rows still meet them
• Larger than memory: exec --memory <n>[K|M|G] [--spill-dir <dir>] bounds the blocking ORDER BY of non-aggregate queries — sorted runs spill to disk and the final merge streams into projection/DISTINCT/OFFSET/LIMIT, so a LIMIT query holds only its output window (library form: execute_with with ExecOptions + a SpillStore; the engine itself never touches the filesystem)

#TPC-H cross-validation

uv run --with duckdb python harness/gen_tpch.py   # TPC-H SF0.01 data + goldens
bash harness/check_all.sh                         # 27 goldens: all PASS (22 TPC-H + parquet + 3 multi-row-group)
MOONLAKE_DATA=harness/data/sf01 bash harness/check_all.sh   # same at SF0.1 (regenerate first: gen_tpch.py 0.1)

#Quickstart

git clone https://github.com/superbigcup325/moonlake && cd moonlake
moon run cmd/main -- exec --csv harness/data/sf001/lineitem.csv \
  "SELECT l_returnflag, sum(l_extendedprice) FROM lineitem WHERE l_shipdate <= date '1998-09-02' GROUP BY l_returnflag ORDER BY l_returnflag"
# l_returnflag|sum_2
# A|532348211.6499983
# ...

moon build cmd/main --target native --release
install -m755 _build/native/release/build/cmd/main/main.exe ~/.local/bin/moonlake
moonlake exec --csv lineitem.csv --file q6.sql     # SQL from a file: no shell quoting

moon add superbigcup325/moonlake

moon add gives you the latest release from mooncakes (0.3.1). Everything this README describes is in it.

moon update   # refresh the registry index; the graph downloads on the first check
moon check

ln -s ../colenc .colenc
printf 'members = [".", "./.colenc"]\n' > moon.work

// a @catalog.Catalog with registered table entries — @csv/@parquet
// scan into one; note the argument order: content/name, not name/content.
// registration is lazy: the schema is available at once, and a query decodes
// only the columns it projects, in 4096-row batches

///|
fn main raise {
  let cat = @catalog.Catalog::new()
  cat.register(@csv.scan_string("region,amount\nnorth,100\n", "events"))

  // read results back cell by cell: names() gives the columns,
  // row_count() the rows, get(row, col) a @types.Scalar (null via Null)
  let result = @moonlake.execute(
    "SELECT region, count(*) FROM events GROUP BY region", cat,
  )
  for r in 0..<result.row_count() {
    println(result.get(r, 0).to_string() + "|" + result.get(r, 1).to_string())
  }
}

#Docs

• Playground (open): drag in one CSV/Parquet file, write SQL, and see the result table — the whole engine as wasm-gc, all client-side. (One uploaded table at a time, so cross-file joins are a CLI/library feature; the physical plan is --explain on the CLI.)
• docs/DEMO.md: the reproducible demo, from running the engine to the DuckDB cross-check and the benchmark.
• docs/verification.md: the correctness and performance report (differential and property testing, pushdown fuzzing, memory and spill numbers).
• docs/bench-baseline.md: the release performance baseline (scan/join/gather/spill) and the machine it belongs to, with a committed JSON record and a --compare mode.
• docs/spill-survey.md: how six systems handle larger-than-memory queries, and where moonlake's design lands.
• docs/devlog.md: the build log, week by week.
• CHANGELOG.md: release notes.

#Where moonlake sits

#Development

moon check           # type-check
moon test            # run tests
moon fmt             # format
moon run cmd/main    # run the CLI
git config core.hooksPath hooks   # once per clone: pre-commit gate

#Acknowledgements

• moonbitlang/x (Apache-2.0) — filesystem, argument parsing and encoding utilities
• moonbitlang/quickcheck (Apache-2.0) — property-based testing
• apache/parquet-format (Apache-2.0) — the Parquet format and encoding specifications that sources/parquet implements; its parquet-testing corpus supplies the files the reader is verified against

#License