CREATE COLLECTION engine spellings diverge: WITH (engine=...) + BITEMPORAL builds a broken schema; ENGINE = suffix silently ignores the timeseries engine

[mkhairi]

Tested against: origin/main @ 09b8d9de (post-v0.3.0 main head, 2026-07-05)

Severity: Medium — two documented-looking spellings of the same DDL produce structurally different (and in both cases broken) collections, with no parse error to warn the user.

Summary

CREATE COLLECTION accepts an engine both as a WITH (engine='...') options clause and as an ENGINE = ... suffix, but the two paths are not equivalent:

1. WITH (engine='document_strict') combined with the BITEMPORAL flag builds a corrupted bitemporal schema: plain SELECTs leak internal columns (__system_from_ms, __valid_from_ms, __valid_until_ms), and AS OF SYSTEM TIME NULL history projects only _ts_system — the user columns vanish from history reads. (The ENGINE = document_strict suffix + BITEMPORAL builds a correct one.)
2. ENGINE = timeseries is silently ignored: the collection is created as a plain document collection, so the declared TIME_KEY never becomes the row key and the second INSERT dies with duplicate key value '' on a phantom empty primary key. (WITH (engine='timeseries') works.)

Repro

-- face 1: WITH-spelling + BITEMPORAL = broken schema
CREATE COLLECTION bt_w (id TEXT PRIMARY KEY, actor TEXT, BITEMPORAL) WITH (engine='document_strict');
INSERT INTO bt_w (id, actor) VALUES ('1', 'a');
SELECT * FROM bt_w;
--  id | __system_from_ms | __valid_from_ms      | __valid_until_ms    | actor
--  1  | 1783183410546    | -9223372036854775808 | 9223372036854775807 | a
--                                    ^^^ internal columns leak into SELECT *
SELECT * FROM bt_w AS OF SYSTEM TIME NULL;
--  _ts_system
--  1783183410546        <- history projects ONLY _ts_system; id/actor gone

-- face 2: ENGINE-suffix ignores timeseries
CREATE COLLECTION ts_e (ts TIMESTAMP TIME_KEY, value FLOAT) ENGINE = timeseries;
INSERT INTO ts_e (ts, value) VALUES ('2026-05-09 10:00:00', 1.0);
INSERT INTO ts_e (ts, value) VALUES ('2026-05-09 10:05:00', 2.0);
-- ERROR:  constraint violation: unique: duplicate key value '' violates primary-key uniqueness on 'ts_e'

Expected

1. Both spellings produce identical collections for every engine, or the unsupported combination is a parse/validation error.
2. A bitemporal collection hides its internal versioning columns from SELECT * and projects user columns (plus _ts_system) on history reads — the ENGINE =-suffix spelling already behaves this way.
3. ENGINE = timeseries either builds a timeseries collection or errors; silently building a different engine is the worst outcome.

Files (best-guess)

• CREATE COLLECTION DDL parsing/registration — the WITH-options path and the ENGINE-suffix path resolve the engine + flags independently and disagree
• bitemporal schema construction under the WITH path — internal columns registered as user-visible; history projection built from the wrong column set

Operational context

Users pick whichever spelling they see first; both look accepted. One yields a bitemporal collection whose audit history loses all user data, the other quietly builds the wrong engine that breaks on the second insert.