WEBSITE ALPHA · AI-assisted, source-reviewed documentation · Full-stack docs reconciled 2026-09-02
64x64base

SQLsel

Set-oriented SELECT over open work areas, verified against a SQLite oracle.

Overview

SQLsel names the SQL-facing query workflow in the DotTalk++ / x64base system.

Its subject is set-oriented retrieval: a SELECT statement that describes the rows it wants and returns them, rather than positioning a cursor and navigating. It is a supported surface as of 2026-07-29.

SQLSEL SID,LNAME,FNAME FROM STUDENTS WHERE MAJOR = "CSCI" ORDER BY LNAME LIMIT 10
SQLSEL * FROM STUDENTS
SQLSEL COUNT(*) FROM STUDENTS WHERE GPA >= 3.0

A statement is scoped to itself: it names its own table, ignores session filter and cursor state, restores every cursor it touches, and reads committed table truth. The table must already be open.

Current SQL-facing surfaces include:

  • SQLSEL ... FROM ... WHERE ... ORDER BY ... LIMIT
  • SQLSEL COUNT(*)
  • SQLSEL USAGE for the runtime grammar
  • REGRESSION SQLSEL_SELECT_V1, an oracle comparison against SQLite
  • a SQL conformance map in include/sql_ref.hpp

The SQL family

Six commands share the SQL prefix and are not interchangeable. SQLSEL is SQLsel, the statement surface described above. SQL is a reserved name: it scanned the current work area with a predicate until 2026-09-04, when that job moved to COUNT, and it now reports where the scanning and statement surfaces live. SQLHELP renders the SQL reference and conformance map. SQLERASE removes rows through the SQL surface. SQLITE bridges to an actual SQLite database. SQLVER reports SQLite availability. Each command states the boundary in its own USAGE output.

For a predicate scan or count over the current work area the verb is COUNT, which honours SET FILTER and SET DELETED as the logical rowset and carries the listing and tracing forms as COUNT LIST and COUNT VERBOSE.

Joins, and how they are proven

Pushed to development 2026-09-03 as e7d6ef130. SQLsel joins two tables on an equality key:

SQLSEL S.SID, S.LNAME, E.COURSE FROM SQLJSTU AS S
  INNER JOIN SQLJENR AS E ON S.SID = E.SID ORDER BY E.COURSE

Aliases, qualified columns, a joined WHERE, ORDER BY, LIMIT, COUNT(*), and refusals that name their reason -- an ambiguous bare column, an unqualified ON side, a table that is not open.

It reports which path it took, and the proof asserts the path and the answer separately. A CDX seek names the inner table, the tag, the probe count and the candidate count; a nested-loop scan names both row counts. The regression compares four row sets against an in-process SQLite oracle and pins the access-path composition, so a join that returned the right rows by silently falling back to a scan fails. Both halves were mutation-tested red.

It is statement-scoped: the ON clause is declared per statement and does not consult the declared relation graph or workspace state. That makes it an independent join path, not a second route to the graph RelTalk walks.

And it reads under a fence. A join takes a non-blocking two-table read fence, acquiring both tables in canonical path order before either is read, and lock contention refuses the statement rather than returning a partial answer -- proven with two real processes: while one held a lock on the left table, the other refused the join before emitting any row or any access path. Locks the caller already held are preserved, and only the fences SQLsel took are released.

The limit is stated because the mechanism invites overreading: this is a cooperative statement read fence, not MVCC and not cross-table write atomicity. It stops a concurrent writer from producing a fractured joined result. It does not version rows, and joined UPDATE / DELETE remain out of scope -- COMMIT ALL is still partial across DBF, memo and index storage.

The join family, and a refusal worth reading

INNER, LEFT, RIGHT, FULL and CROSS all ship (P4.1-P4.4), alongside DISTINCT, the set operations, GROUP BY / HAVING with aggregates, subqueries, SET MODE SQL, and transactional single-table INSERT / UPDATE / DELETE. Ten result multisets are compared against the SQLite oracle, outer joins on both indexed and scan paths, and CROSS on both a filtered result and a full 30-row Cartesian product.

An unmatched side produces an explicit absence cell -- a distinct kind from a real blank, so "nobody entered a value" and "no row matched" never collapse into the same thing. The regression asserts blank-versus-absence directly.

Then it stops, deliberately:

SQLSEL: LEFT JOIN with WHERE requires three-valued predicate support;
P4.3 refuses it.

WHERE over a produced absence needs TRUE / FALSE / UNKNOWN, and x64base's predicate evaluator is two-valued. Rather than answer the question wrongly, the statement is refused and the reason is named. This is the interesting half of the feature: the boundary is enforced in the engine, not written down in a document and hoped for.

It is also the first consumer of a discipline set in July: stored NULL would not be implemented then, but everything authored afterwards should be shaped so adding it later is an extension rather than a rewrite. The absence cell renders through a single routine, and its own comment says so: "future stored NULL must extend this switch."

That discipline has since been vindicated on the engine side. Stored NULL arrived on 2026-09-05 -- a VFP-flavour table carries a real _NullFlags bit, proven on every REGRESSION ALL by the NULLASSERT spec -- and it arrived as an extension, not a rewrite. SQLsel's own write surface is a separate question, and the boundary below states it.

The guards run by default, and promoting them found a bug

Fourteen fail-closed regression specifications cover SQLsel, and as of 8df02addf the four guarding the hardest surface -- outer joins, join edges, the full family, and buffer visibility -- run in the default suite rather than only on request. The default suite went from 22 specs to 26, green on both Windows and WSL/GCC.

The promotion is worth reporting because of what it cost. Running those specs by default exposed a real defect in an unrelated command: ERASE TABLE was leaving .cdx.meta sidecars behind. Teardown looked clean and was not. ERASE now removes that metadata, and five cleanup assertions fail closed if any residue survives; the fix was mutation-tested red before it was believed.

That is the argument for the house promotion rule stated as a result rather than a principle. A specification that exists but is never run by default is coverage already paid for and not collected -- and the first time these four were collected, they found something nobody was looking for.

Names resolve inside the current workspace

Workspaces are co-resident -- OPEN and LOAD are both additive since R128/R130 -- so "which STUDENTS?" is a real question. SQLsel answers it by resolving table names within the current workspace, keyed on the work area's own workspace handle, and refusing rather than guessing:

SQLSEL: table 'ORDERS' is not open in the current workspace.

The source states the reason it is done once rather than re-derived: repeating a logical-name lookup later "would throw the workspace decision away and become ambiguous when another workspace has the same table name."

This closes the hazard the workspaces page calls the sharp one, for SQLsel's surface. SELECT <name> in the native shell is a separate resolver and is not covered by it.

Deliberate boundaries

Stated as boundaries rather than a to-do list, because each is a decision with a reason:

  • SQLsel DML will not WRITE a stored NULL. It refuses rather than coercing a blank, a fail-closed control the SQLSEL_DML spec pins. Read the narrowing carefully, because an earlier wording of this bullet said "no stored SQL NULL" flatly and that is no longer true of the engine: x64base HAS a stored null on VFP-flavour tables. What this boundary describes is SQLsel's write path, not the database. Inside a statement, blank remains a value (R16) and outer-join absence remains a produced cell -- three distinct kinds, not two.
  • No memo-field transactional DML, and no cross-table atomic commit -- COMMIT ALL remains partial across DBF, memo and index storage.
  • No generalized multi-chain RIGHT / FULL join. The two-table forms ship and are oracle-checked on every run (SQLSEL_JOIN_FAMILY covers RIGHT, FULL and CROSS); it is the three-table CHAINS that remain INNER / LEFT only (SQLSEL_ADVANCED_JOIN). The bullet is about chains.

The surface moves faster than this page. The SQLsel manual is the authority for what the command accepts today.

Relationship to RelTalk

SQLsel and RelTalk both reach connected data and are not the same thing. RelTalk is navigational: relations are declared with REL ADD and followed. SQLsel is relational: a condition is stated and a set is returned. RelTalk is the older model, inherited through the xBase line; SQLsel is the newer one. Neither replaces the other.

See SQLsel and SQL Conformance for the verification detail and the conformance map, RelTalk docs, and the canonical public repo deraldg/x64base.