λgitsite

~ober/jerboa-duckdb

Imported from ~/mine/jerboa-duckdb

download snapshot

about

# jerboa-duckdb

`jerboa-duckdb` is an independent DuckDB-compatible analytical database being
implemented in Jerboa Scheme. DuckDB is used as a pinned development oracle and is not
linked into the production engine.

The governing scope and delivery gates are in [Project.md](Project.md). The current code
implements a partial Phase 1 in-memory scalar slice: catalog tables, columnar chunks,
tokenizer/parser, binder, vectorized expression kernels, scan/filter/project/aggregate/
distinct/order/limit execution, GROUP BY validation with post-aggregate, aggregate `DISTINCT` arguments, aggregate
ORDER BY expressions, first-slice aggregate `FILTER (WHERE predicate)` for `count`/`sum`/`avg`/`min`/`max`,
`count()`/`count(*)`, NULL-literal and empty-set aggregate semantics, aggregate input validation,
basic `EXPLAIN` result rendering for supported query plans,
SQL-level named `PREPARE`/`EXECUTE` for the current statement subset,
first-slice connection-local `SET`/`RESET` for `threads` and `memory_limit`
plus typed `current_setting(...)`,
first-slice explicit `BEGIN`/`COMMIT`/`ROLLBACK` with in-memory rollback
snapshots for catalog and table data,
first-slice `ANALYZE`/`VACUUM` statement handling with optional table
validation,
first-slice `PRAGMA table_info`, `PRAGMA version`, and `PRAGMA database_list`
metadata results,
first-slice metadata table-function sources for `pragma_version()`,
`pragma_table_info(...)`, and a two-setting `duckdb_settings()` subset,
numeric BIGINT `range(...)` and `generate_series(...)` table-function sources,
first-slice `repeat(value, count)` table-function sources,
first-slice `glob(pattern)` table-function sources,
first-slice `SUMMARIZE table/query` metadata plans,
first-slice `duckdb_databases()`/`duckdb_schemas()`/`duckdb_views()`/
`duckdb_indexes()`/`duckdb_constraints()`/`duckdb_types()`/
`duckdb_sequences()`/`duckdb_dependencies()`/`duckdb_functions()`/
`duckdb_extensions()`/`duckdb_optimizers()`/`duckdb_memory()`/`duckdb_logs()`/
`duckdb_prepared_statements()`/`duckdb_variables()`/`duckdb_secrets()`/
`duckdb_temporary_files()`/`duckdb_external_file_cache()`/`duckdb_secret_types()`/
`duckdb_approx_database_count()`/`duckdb_connection_count()`/
`duckdb_coordinate_systems()`/`duckdb_keywords()`/`duckdb_log_contexts()`/
`duckdb_table_sample(...)`/
`duckdb_tables()`/`duckdb_columns()` system catalog table functions,
first-slice `SHOW TABLES` and `SHOW ALL TABLES` catalog metadata results for tables and views,
first-slice `DESCRIBE table`, `DESCRIBE SELECT/VALUES ...`, and
`DESCRIBE` table-source metadata results,
first-slice `INNER JOIN ... ON ...`, `LEFT [OUTER] JOIN ... ON ...`,
`RIGHT [OUTER] JOIN ... ON ...`, `FULL [OUTER] JOIN ... ON ...`,
`SEMI JOIN ... ON ...`, and `ANTI JOIN ... ON ...`
execution plus first-slice `JOIN ... USING(...)`, `NATURAL` inner/outer/semi/anti joins,
`POSITIONAL JOIN`, and `ASOF` inner/left/right/full joins,
the first-slice `row_number() OVER ()`, `row_number() OVER (PARTITION BY ...)`, bounded single-window `ORDER BY` subset, bounded `rank()`/`dense_rank()`/`percent_rank()`/`cume_dist()` windows, adjacent-row `lag()`/`lead()` windows with optional partitioning, and bounded `QUALIFY` filtering over those projected windows,
including right/full coalesced key output, `CROSS JOIN`,
and comma-style cross joins with qualified column binding,
implicit SELECT aliases, table aliases and qualified column
references, schema-qualified table names and `CREATE [TEMP] TABLE [IF NOT EXISTS]` for the in-memory `main`/`temp` catalog,
first-slice `CREATE [TEMP] TABLE [IF NOT EXISTS] name [(aliases...)] AS SELECT/VALUES ...`,
first-slice `CREATE [OR REPLACE] [TEMP] VIEW [IF NOT EXISTS] ... AS
SELECT/VALUES ...` with rebound view scans, `DESCRIBE view`, and
`DROP VIEW [IF EXISTS]`,
DuckDB identifier behavior for keyword-like column names such as `unknown`,
`DROP TABLE [IF EXISTS]`, basic `DELETE FROM ... [WHERE ...]`, basic
`UPDATE ... SET ... [WHERE ...]`, first-slice `ALTER TABLE name RENAME TO new_name`,
first-slice `ALTER TABLE name RENAME [COLUMN] old_column TO new_column`,
first-slice `ALTER TABLE name ADD [COLUMN] [IF NOT EXISTS] column type [DEFAULT expr]`,
first-slice `ALTER TABLE name DROP [COLUMN] [IF EXISTS] column`,
first-slice `ALTER TABLE name ALTER [COLUMN] column SET/DROP DEFAULT`,
first-slice `ALTER TABLE name ALTER [COLUMN] column TYPE/SET DATA TYPE type [USING expr]`,
first-slice `ALTER TABLE name ALTER [COLUMN] column SET/DROP NOT NULL`,
`INSERT` with full-row, column-list, `DEFAULT` row items, or `DEFAULT VALUES` sources plus `DEFAULT`/`NOT NULL` column handling, expression-bearing
`VALUES` statements and `VALUES` table sources, derived `SELECT` and
`DESCRIBE` table sources, first-slice uncorrelated scalar subqueries,
first-slice uncorrelated `EXISTS`/`NOT EXISTS` and `IN (SELECT/VALUES/WITH ...)`
subqueries, plus bounded one-level correlated `EXISTS`/`NOT EXISTS` and
`IN`/`NOT IN` subsets;
bounded one-level correlated scalar subqueries are implemented in expression
position, and bounded one-level correlated `IN`/`NOT IN` subqueries are
implemented in expression position; nested correlated levels, `LATERAL`,
aggregate outer-shape limitations, and broader correlated subquery forms are
not included,
first-slice non-recursive `WITH` CTEs over
`SELECT`/`VALUES` queries and `INSERT ... SELECT` sources, including
`AS [NOT] MATERIALIZED` modifiers,
basic `UNION`/`EXCEPT`/`INTERSECT` set operations
including `ALL` over `SELECT` and `VALUES` with final `ORDER BY`/`LIMIT`,
DuckDB-style literal coercion and
`typeof` including DuckDB's quoted NULL type name in the covered scalar subset, parameterized DECIMAL type metadata plus DECIMAL casts/rendering, DuckDB SQL type-name aliases in DDL for unsigned integer spellings, bare `DEC`/`DECIMAL`/`NUMERIC`, `TIMETZ`/`TIME WITH TIME ZONE`, `JSON`, and `GEOMETRY`, DATE/TIME/TIMESTAMP casts/rendering, INTERVAL casts/rendering, DATE-to-TIMESTAMP coercion, direct temporal extraction aliases (`year`, `month`, `day`, `dayofmonth`, `quarter`, `century`, `decade`, `millennium`, `era`, `dayofweek`, `weekday`, `dayofyear`, `week`, `weekofyear`, `isoyear`, `isodow`, `yearweek`, `hour`, `minute`, `second`), generic `date_part`/`datepart` plus `extract(... FROM ...)` temporal extraction, `date_diff`/`datediff` plus `date_sub`/`datesub` temporal difference functions, `date_trunc`/`datetrunc`, `last_day`, scalar `make_date`/`make_time`, `julian`, `epoch`, and `epoch_ms`/`epoch_us`/`epoch_ns`,
`IN` list comparison literal coercion, `LIKE`/`ILIKE` including `ESCAPE` and DuckDB
`~~`/`!~~`/`~~*`/`!~~*` operator aliases, BLOB casts/rendering,
boolean/numeric comparison coercion,
typed string equality coercion against numeric/boolean operands,
mixed numeric `//`/`divide` and `%`/`mod` zero-divisor semantics,
DuckDB scalar `abs` including DECIMAL preservation and signed-min overflow checks, unary DECIMAL negation preserving width/scale,
DuckDB half-away-from-zero scalar `round` including scale arguments, truncation
toward zero with `trunc`, numeric `sign`, `signbit`, `isfinite`, `isinf`, and
`isnan`, `pi`, `radians`/`degrees`, trigonometric and hyperbolic scalars,
`pow`/`power`, `factorial`, `even`, `gcd`/`lcm`, `bit_count`, integer `xor`,
`cbrt`, `exp`, `ln`, `log2`, `log10`, `log`, `gamma`, `lgamma`, and `nextafter`,
scalar function arity/type checks and NULL overload defaults, `version`, `current_schema`, `current_database`, `ceiling`, `ucase`/`lcase`, and `substr` alias support, `format_bytes`/`formatReadableDecimalSize`, `to_base`, `bin`/`to_binary`, `hex`/`to_hex`, `unhex`/`unbin`, `md5`, `sha1`, `sha256`, `concat_ws`, length aliases plus `strlen`/string `bit_length`, `substring` including `FROM`/`FOR` syntax, `ascii`/`unicode`/`ord`/`chr`, `hamming`/`mismatches`, `levenshtein`/`editdist3`, `damerau_levenshtein`, `jaccard`, `left`/`right`/`repeat`/`lpad`/`rpad`/`replace`/`translate`/`reverse`, `url_encode`/`url_decode`, `position(... IN ...)`, `instr`/`strpos`, string predicates `contains`/`prefix`/`suffix` plus `starts_with`/`ends_with`, `trim`/`ltrim`/`rtrim` including standard `FROM` syntax, `constant_or_null`, `can_cast_implicitly`, scalar `error`, and mixed numeric `coalesce`/`ifnull`/`greatest`/`least`,
`nullif` comparison coercion including typed string equality, CASE string-literal result normalization,
ternary `if` as CASE-compatible sugar, unary integer typing, boolean string cast aliases,
integer cast range checks, and BIGINT overflow checks,
wide signed/unsigned integer boundary storage, unsigned 128-bit literal typing,
and arithmetic overflow checks,
assignment casts for inserted values, and a small
public API with cached prepared plans for unparameterized `SELECT`/`VALUES`.
Prepared parameter substitution covers scalar expressions, `VALUES` table sources,
`INSERT ... VALUES`, `INSERT ... SELECT`, `DELETE ... WHERE`, and
`UPDATE ... SET/WHERE`, plus `SUMMARIZE SELECT/VALUES` payloads, including mixed `?` and `$n`
placeholders, with count validation for missing or extra arguments. `LIMIT`/`OFFSET`,
including standalone `OFFSET`, support DuckDB constant coercion for numeric, string,
boolean, NULL, `LIMIT ALL`, and prepared-parameter inputs in the current subset.
SQL `PREPARE name AS ...` and `EXECUTE name(...)` are connection-local and
reuse the same prepared execution path as the public Scheme API.
SQL `SET name=value` and `RESET name` currently cover connection-local
`threads`, `memory_limit`, and `preserve_insertion_order`; `current_setting('threads')`
returns `BIGINT`, `current_setting('memory_limit')` returns `VARCHAR`, and
`current_setting('preserve_insertion_order')` returns `BOOLEAN`.
`JOIN ... USING(...)` exposes DuckDB-style visible key output and keeps
qualified references to the original left/right key columns available.
SQL `BEGIN`/`COMMIT`/`ROLLBACK` currently cover one active transaction per
connection and restore catalog/table data on rollback; MVCC and concurrent
transaction semantics are still later work.
SQL `ANALYZE [table]` and `VACUUM [table]` currently validate optional target
tables and return an empty result; file-backed database snapshots can be
reopened through `duckdb-open`/`duckdb-close`, while WAL/MVCC and full DuckDB
storage-file compatibility remain later phases.
SQL `PRAGMA table_info`, `PRAGMA version`, and `PRAGMA database_list` expose
the first in-memory metadata subset; `CALL pragma_version()`,
`CALL pragma_table_info(...)`, and a two-setting `CALL duckdb_settings()` slice
are implemented as the first table-function-style `CALL` metadata coverage.
The same `pragma_version()`, `pragma_table_info(...)`, and `duckdb_settings()`
metadata slice can also be used as table functions in `FROM`.
Numeric BIGINT `range(...)` and `generate_series(...)` table-function sources
cover DuckDB's one/two/three argument forms, exclusive versus inclusive stop
semantics, negative steps, NULL-empty results, and prepared arguments.
`repeat(value, count)` table-function sources cover constant scalar value
expressions, typed value output, zero-row counts, NULL values, prepared counts,
and DuckDB-shaped count errors.
`glob(pattern)` table-function sources cover non-recursive regular-file
patterns with `*`/`?`, empty matches, and prepared string patterns.
`SUMMARIZE table/query` returns DuckDB-shaped column summary metadata for the
current in-memory table/query subset, including direct statement and
parenthesized table-source forms.
`duckdb_databases()`, `duckdb_schemas()`, `duckdb_views()`,
`duckdb_indexes()`, `duckdb_constraints()`, `duckdb_types()`,
`duckdb_sequences()`, `duckdb_dependencies()`, `duckdb_functions()`,
`duckdb_extensions()`, `duckdb_optimizers()`, `duckdb_memory()`,
`duckdb_logs()`, `duckdb_prepared_statements()`, `duckdb_variables()`,
`duckdb_secrets()`, `duckdb_temporary_files()`,
`duckdb_external_file_cache()`, `duckdb_secret_types()`,
`duckdb_approx_database_count()`, `duckdb_connection_count()`,
`duckdb_coordinate_systems()`, `duckdb_keywords()`, `duckdb_log_contexts()`,
`duckdb_table_sample(...)`, `duckdb_tables()`,
and `duckdb_columns()` expose the current in-memory catalog as first-slice
system catalog table functions, including direct `CALL` and `FROM` forms for stable
database/schema/view/index/constraint/type/sequence/dependency/function/
extension/optimizer/memory/log/prepared/variable/secret/temp/cache/count/
coordinate/keyword/sample/table/column metadata fields.
`duckdb_constraints()` currently populates `NOT NULL` column constraints;
`duckdb_types()` exposes built-in type and alias metadata, including the
`geometry` alias rows and system-only `JSON` alias row from the pinned oracle;
`duckdb_functions()` exposes first-slice metadata for supported scalar and table
functions;
`duckdb_extensions()` exposes the v1.5.1 built-in/available extension list;
`duckdb_optimizers()` exposes the v1.5.1 optimizer-name list;
`duckdb_memory()` exposes v1.5.1 memory categories with first-slice zero counters;
`duckdb_secret_types()` exposes the built-in `http` secret type;
`duckdb_approx_database_count()` and `duckdb_connection_count()` expose
fresh in-memory count metadata;
`duckdb_coordinate_systems()` exposes the stable v1.5.1 `OGC:CRS83` and
`OGC:CRS84` coordinate-system rows with first-slice empty `projjson`/WKT fields;
`duckdb_keywords()` exposes the full v1.5.1 keyword/category list;
`duckdb_table_sample('table')` returns the target table schema with first-slice
empty sample rows;
`CREATE [TEMP] SEQUENCE`, `DROP SEQUENCE`, `nextval()`, and `currval()` are
implemented for the in-memory catalog; `duckdb_dependencies()` remains a
schema-correct empty result set;
`duckdb_logs()`, `duckdb_log_contexts()`, `duckdb_prepared_statements()`, `duckdb_variables()`,
`duckdb_secrets()`, `duckdb_temporary_files()`, and
`duckdb_external_file_cache()` currently expose schema-correct empty result
sets until those subsystems have backing state;
`duckdb_views()` exposes first-slice user view metadata from `CREATE VIEW`, and
`CREATE [UNIQUE] INDEX [IF NOT EXISTS] name ON table(cols...)` and
`DROP INDEX [IF EXISTS]` store first-slice in-memory index metadata;
`duckdb_indexes()` lists those rows and `duckdb_tables().index_count`
reflects them. Unique indexes reject duplicate non-NULL keys on create,
insert, and update. Indexes are not used for scan planning yet.
Full DuckDB pragma and table-function coverage remains later work.
SQL `SHOW TABLES` and `SHOW ALL TABLES` expose the current in-memory table
and view catalog with DuckDB-compatible display ordering.
SQL `DESCRIBE table` returns DuckDB-shaped column metadata from the catalog;
`DESCRIBE SELECT/VALUES ...` returns bound output names/types with conservative
nullability metadata; relation forms such as `SELECT * FROM (DESCRIBE t)` are
available in the current table-source subset.
`EXPLAIN` returns `explain_key`/`explain_value` rows for supported query plans;
`EXPLAIN ANALYZE` executes supported query plans and returns a first-slice
`analyzed_plan` row with elapsed time, row count, and plan summary.
The SQLLogicTest runner currently covers basic `statement ok/error/maybe` and
`query` blocks, expected error fragments, persistent `mode skip/unskip`,
`skipif`/`onlyif` conditionals, `halt`, `reconnect`, `restart`,
`require`/`require-env`-driven skips, `test-env` defaults, labels, named connections,
hashed expected-output blocks, and
`rowsort`/`valuesort` result normalization. `loop`/`foreach` expansion covers
the current sequential subset, including `continue`, nested blocks, and `{name}`/`$name`
substitution. `test/sqllogic/run.ss --events PATH ...` emits JSONL run, file,
statement, query, and summary events for tooling.

## Requirements

- `jerbuild` on `PATH`
- Pinned DuckDB source is fetched into `vendor/duckdb`; set `DUCKDB_SOURCE` to use an explicit checkout
- `git` for upstream revision verification

## Commands

```sh
make build            # compile all DuckDB Jerboa library modules
make contracts        # regenerate the checked-in upstream inventory
make contracts-check  # verify revision and deterministic generated output
make unit             # run Jerboa unit tests
make diff             # run the current DuckDB CLI differential tests
make bench            # run the current scan/aggregate/group/order benchmark
make sqllogic         # run the current SQLLogicTest subset
make sqllogic-events  # smoke-test SQLLogicTest JSONL event output
make check-docs       # check Markdown hygiene
make verify           # run all current gates
```

The compatibility ledger begins at `contracts/compatibility.sexp`. The dependency-ordered
roadmap for removing the remaining DuckDB gaps is in `FULL_COMPATIBILITY_PLAN.md`. SQL execution support is
still partial; unsupported work includes remaining advanced join forms, correlated subqueries beyond the implemented one-level `EXISTS`/`NOT EXISTS`, scalar-subquery, and `IN`/`NOT IN` subsets, aggregate outer-shape limitations for correlated subqueries, and broader subquery forms,
true materialization guarantees, advanced
set-operation clauses, other window functions, frames, named windows, multiple differing window orders, aggregate or advanced `QUALIFY` shapes, and full cross-chunk/global window semantics beyond the bounded `row_number()`/`rank()`/`dense_rank()` `ORDER BY`/`QUALIFY` subset, persistent storage, and external file formats.

recent commits