SQLsel and SQL Conformance

What SQLsel is

SQLsel is a set-oriented SELECT statement over an open work area. It is a supported surface as of 2026-07-29.

SQLSEL is itself the verb -- a leading SELECT keyword parses but is redundant, and the syntax below omits it. The separate verb exists because xBase already spends SELECT on SELECT <area>, which switches the active work area rather than querying anything.

There is a second way in, and this page denied its existence until 2026-09-09: SET MODE SQL makes bare SELECT the statement. See the hard switch below. SQLSEL stays canonical in every mode, so nothing you write today stops working.

SQLSEL <col>[,<col>...] FROM <table>
       [WHERE <predicate>] [ORDER BY <field> [ASC|DESC]] [LIMIT <n>]
SQLSEL * FROM <table>
SQLSEL COUNT(*) FROM <table> [WHERE <predicate>]

It is deliberately statement-scoped. A statement names its own table, ignores session filter and cursor state, restores every cursor it touches, and reads committed table truth. Running one changes nothing about where you were.

How it is verified

Every result set is compared against an in-process SQLite oracle over identical data in the same run - the same rows loaded into both engines, the same query asked of both, the outputs compared row for row. That comparison is a registered regression (REGRESSION SQLSEL_SELECT_V1), not a one-time exercise.

The regression also asserts the parts that are easy to get quietly wrong:

• ORDER BY sorts the full match set BEFORE LIMIT cuts it. ORDER BY LNAME DESC LIMIT 2 returns the two highest rows, not the first two rows found.
• Cursor neutrality, proven by data - the cursor is parked on a known record before the statement and checked after.
• Corrective errors, never silent zeros - an unopened table, an expression where a column name belongs, a bad LIMIT, an unknown ORDER BY field, and ORDER BY on COUNT(*) each report what is wrong.
• LIMIT says what it withheld, and ORDER BY names its access path.

What it does and does not do

Corrected 2026-09-03 after this table contradicted itself: the JOIN row was updated when the joins landed and the rows beside it were not, so the same page announced GROUP BY shipping in one cell and "not implemented anywhere in the engine" in the next. When one row here changes, re-read the others -- the lane moves faster than a table does.

The table must already be open (USE <table>). SQLsel reads open work areas; it does not open files for you.

The hard switch: SET MODE SQL

SET MODE [TO] NATIVE|SQL|OTHER is a session-only language mode, and in SQL mode it routes. 7 verbs become aliases for SQLSEL, carrying the whole statement through -- BEGIN,COMMIT,DELETE,INSERT,ROLLBACK,SELECT,UPDATE, read out of the router itself. So SELECT ID,NAME FROM PEOPLE WHERE ID = 2 ORDER BY ID is a query in SQL mode and a work-area switch outside it, and the mode is what tells the two apart.

The switch also takes things away, deliberately, so a SQL statement cannot silently inherit cursor-oriented behaviour:

• REL, RELATIONS, REL_LIST, REL_REFRESH and SET RELATION are refused with one corrective message naming SQLSEL JOIN and SET MODE NATIVE.
• The native expression fallback is disabled, so a bare 1+1 is refused rather than evaluated.
• BEGIN requires SQL mode, so native COMMIT cannot bypass a SQL transaction's state.

SQLSEL remains canonical and available in every mode. SELECT <area> keeps its xBase meaning in NATIVE and OTHER. Nothing that works today stops working.

This is proven at runtime, not read off the source. SQLMODE_SMOKE runs the bare SELECT and the canonical SQLSEL form over the same rows and compares both against SQLite in the same run, then proves the mode transitions, both refusals, and that the work-area cursor is where it was before the statement. The honest caveat: that spec is explicit-run, not in the default suite, so REGRESSION ALL does not exercise this today.

What this page got wrong, and why the evidence could not have shown it

Until 2026-09-09 the row above read "still planned ... routing is not wired: sqlsel::session_mode() is read only by the prompt."

That cited fact is true. session_mode() really is read only by the prompt. It is also irrelevant, because dispatch does not call it -- it calls the wrapper sql_mode(), which has six call sites across three files, four of them in the shell's own command router. A search for the symbol the page named came back empty, and the emptiness was read as absence.

It is worth stating plainly next to a conformance page: an empty search result is evidence about the search, not about the code. The claim was never checked against the router; it was checked against a spelling.

The SQL command family

The shell registers 6 commands whose names begin with SQL, and they do different things. The names invite confusion, so the boundary is stated in each command's own contract, in its runtime USAGE output, and in HELP.

This table listed four until 2026-09-09. SQLHELP and SQLERASE were registered in the shell and missing here -- which is the ordinary way a hand-kept list goes wrong: nothing was ever wrong about the four rows that were present.

The registered tokens, read out of the shell's own registration list: SQL,SQLERASE,SQLHELP,SQLITE,SQLSEL,SQLVER.

Two consequences worth knowing.

SQL SELECT * FROM STUDENTS does not run a query. Until 2026-07-29 that line was parsed as a predicate and returned a confusing failure; it now detects the leading SELECT and redirects to SQLSEL with the real grammar rather than answering a question nobody asked.

Neither SQL nor SQLSEL scans a predicate any more. This section said until 2026-09-10 that SQL COUNT FOR <expr> and SQLSEL COUNT FOR <expr> were equivalent, and that the overlap was deliberate. It was, and then it was measured. COUNT treats SET FILTER and SET DELETED as part of the logical rowset and selects through the shared row selector; the two predicate forms did neither, each declaring its own deleted-row policy and walking the table by hand. So they could return different numbers over the same table and none of the three said so.

Both were retired into COUNT, which carries the two behaviours it had been missing as COUNT LIST and COUNT VERBOSE: SQL's on 2026-09-04, SQLSEL's on 2026-09-10. A bare predicate after SQLSEL now answers with a corrective error naming COUNT. COUNT owns predicate scans over the current work area, SQLSEL owns statements over named tables, and neither answers the other's question.

RelTalk and SQLsel: two ways to reach connected data

These are two different things that are related. One is old, one is new, and the new one does not replace the old one. Both are supported surfaces.

The distinction is the navigational/relational one. The navigational model asks "where do I go from here?" - you position on a parent record and follow declared links to its children, as CODASYL sets and xBase SET RELATION did. The relational model asks "which rows satisfy this?" - you state a condition and receive a set, with no notion of where you were positioned.

Both can be run against the same tables in the same session, which makes the difference observable rather than described. RelTalk is therefore not deprecated by the arrival of SQL; the two answer different questions.

They are related in that both ultimately read the same DBF tables through the same indexes, and both can produce join-shaped rows. They are not the same thing, and this documentation will not describe one as the other. That was written before SQL joins existed, predicting they would be built as their own set-matching path rather than compiled down to declared traversal, specifically so the distinction survived contact with the implementation. They have since arrived and the prediction held: a SQLSEL join names its own tables and ON columns in the statement and reads no REL or SET RELATION state at all.

See RelTalk for the relation-graph surface in full.

There is one further honest wrinkle, and it has two halves that must not be collapsed. SQL LEFT JOIN fills unmatched columns with NULL. x64base now HAS a NULL -- VFP-flavour tables carrying a _NullFlags column store a real null bit, .NULL. is a writable value, ISNULL(<field>) answers in a FOR predicate, and the encoding is byte-verified against files Visual FoxPro itself wrote. A blank is still a value, and a null is now a different value: the whole point of ISNULL is that a null cell and a blank cell both read as the empty string, so no value comparison can separate them.

Outer joins do not use it. An unmatched row carries the explicit <UNMATCHED> marker rather than a blank that could be mistaken for real data or a null that would claim more than the join knows, and the count of extended rows is reported. SQLSEL DML likewise refuses to store a null rather than coercing one. Those are deliberate rulings, not gaps: produced absence in a result set and a stored null in a cell are different facts, and the output says which one it is looking at.

SQL conformance map

The engine ships a machine-readable answer to "I know this SQL construct - does x64base do it, and how?" It lives in include/sql_ref.hpp, alongside the SQL reference itself, and covers equivalences as well as absences:

• SELECT basic and COUNT(*) - supported, SQLsel
• CREATE TABLE - equivalent, different syntax (CREATE X64, DDL CREATE DBF); xBase types are C/N/D/L/M. AUTOINCREMENT is now an equivalent in a second step, and this page said otherwise until 2026-09-09. The reference used to read "No AUTOINCREMENT: identity columns are not a storage feature here" -- true when written, false since 2026-09-07. SET UNIQUE FIELD <field> PRIMARY designates a column; the engine mints max+1 into a blank key on APPEND under the writer's table lock, reserves a deleted row's key until PACK because RECALL can bring the row back, and refuses a write that would change a minted key. The designation is stamped into the x64 field metadata (X64_FIELD_FLAG_PRIMARY), so it survives a restart. Two real differences remain, and neither is "not supported": it is declared in a separate statement rather than inside CREATE TABLE, and it is x64-flavor-scoped -- the flag lives in x64 metadata, and a reader that does not know it reads zero and enforces nothing, in silence
• CREATE INDEX - equivalent, two steps (INDEX ON ... TAG, then BUILDLMDB)
• INSERT - supported as SQL syntax (SQLSEL INSERT INTO ... VALUES), single-table and transactional; the two-step APPEND then REPLACE form remains the native path
• DELETE - equivalent with a real difference: DELETE marks, RECALL un-marks, PACK removes. Closer to a soft delete than SQL's
• BEGIN TRANSACTION / COMMIT / ROLLBACK - supported, via TABLE BUFFER ON, COMMIT, ROLLBACK, with a write-ahead journal fsynced before any DBF byte moves
• EXPLAIN QUERY PLAN - partial: there is no planner to explain, because access paths are chosen by the operator, not a cost model. What exists is reporting - SQLsel names its access path, and GPS shows physical record against logical row
• GROUP BY - supported (P4.6), with the aggregate family; blanks are skipped and the split is reported (R28)
• JOIN - ships, the full family, alongside the three RelTalk forms. It is statement-scoped: the ON clause is declared per statement and does not consult the declared relation graph, so it is an independent join path rather than a second route to the graph RelTalk walks. Table names resolve within the current workspace

The map holds 33 entries, 16 mapped and 17 not yet mapped. Entries nobody has verified yet are left empty on purpose. Empty means unchecked, not absent - guessing would defeat the point of the map, and half the map being unchecked is a fact about our verification effort rather than about the engine. A gate (sql_conformance_gate.py) checks that every command the map points at is a command the shell actually registers.

The map describes, it does not promise. It exists to answer "does x64base do this, and how?" for someone who already knows SQL, and to show where the gaps are. It is not a compatibility claim: x64base does not offer to run SQLite or MSSQL work, and an "equivalent" row means the engine achieves the same intent by different syntax with its own semantics - DELETE marks rather than removes, stored NULL is a VFP-flavor feature rather than a universal one (a real _NullFlags bit, asserted every run by the NULLASSERT regression; classic x64 and x32 fields carry blanks, not nulls, and SQLsel DML refuses to write a stored null rather than coercing one), and types are C/N/D/L/M rather than SQL types. Read those rows as translation guidance, not as portability.

Checking these claims

Each claim on this page is intended to be verifiable by running something:

• the SELECT behavior, via REGRESSION SQLSEL_SELECT_V1
• the mode router and the bare SELECT alias, via REGRESSION SQLMODE_SMOKE
• the conformance map's command references, via sql_conformance_gate.py
• the command's own grammar, via SQLSEL USAGE

A claim here that the engine does not honor is a defect in one or the other, and either is worth reporting.

How this page is kept honest

The counts above are not typed. They are derived from the engine tree by scripts/derive-sqlsel-authority.mjs into public/artifacts/sqlsel-conformance-v1.json, and a freshness contract holds this page to that artifact -- change the spec count, the command list, the alias set or the map's coverage in the engine without changing this page, and the build fails naming the sentence.

Derived from engine commit d876f3358 (2026-09-09).

Three of the corrections dated 2026-09-09 on this page were found by writing that generator, not by reading the page: the spec counts, the missing SQLHELP and SQLERASE rows, and the bare-SELECT row that had been false since the router was wired. None of them were catchable before, because every freshness contract on this site resolves its authority against the site root and so cannot see the engine at all. The generator does not close that gap -- a build runs on machines with no engine clone -- but it makes re-deriving one command against a checked-out engine tree, and the commit stamped above makes staleness visible without reading a single fact. See Primary Keys for the same arrangement applied to the key policy.