CSharpDB SQL Compatibility

ALTER TABLE users ADD COLUMN status TEXT NOT NULL DEFAULT 'new'

Limitations

• The default must be one of the supported typed literal or NULL forms. Adding a required column without a value for existing rows is rejected. A failed rewrite makes either explicit transaction API rollback-only.

Intentional deviations

• Existing rows are streamed through a shadow table root and receive the persisted default before the catalog root is swapped.

First supported version: 4.0.4 · Related documentation

ALTER TABLE users ADD COLUMN nickname TEXT

Limitations

• Computed values are not supported. Literal-default backfill and selected ALTER COLUMN metadata actions are qualified in separate rows.

First supported version: 4.0.4 · Related documentation

ALTER TABLE orders ADD CONSTRAINT ck_total CHECK (total >= 0)

Limitations

• Only named row-local deterministic CHECK expressions are accepted. Adding a constraint validates every existing row before the catalog is changed.

Intentional deviations

• A CHECK expression that evaluates to UNKNOWN passes, matching CREATE TABLE CHECK behavior.

First supported version: 4.0.4 · Related documentation

ALTER TABLE children DROP CONSTRAINT fk_children_parent

Limitations

• Only existing foreign-key constraints are handled. Other constraint kinds do not yet exist, and the EF provider rejects standalone foreign-key changes.

First supported version: 4.0.4 · Related documentation

ALTER TABLE users DROP COLUMN obsolete_value

Limitations

• Indexed, constrained, trigger-, validation-rule-, or stored-view-dependent, primary-key, and last-column drops are rejected. Stored-view preflight is deliberately table-conservative and follows direct/transitive view chains rather than attempting column-level SQL rewriting. Deterministic fault-injection and process-interruption coverage remain follow-on work.

First supported version: 4.0.4 · Related documentation

ALTER TABLE users RENAME COLUMN old_name TO new_name

Limitations

• Dependency repair is implemented for current indexes, keys, checks, and foreign keys. Stored-view, trigger, and applicable validation-rule dependencies are rejected until their definitions can be rewritten atomically; a dedicated successful-rename reopen proof is not yet linked in this development snapshot.

First supported version: 4.0.4 · Related documentation

ALTER TABLE users RENAME TO customers

Limitations

• This row does not claim arbitrary vendor-specific rename syntax. Renames reject stored-view, trigger, and validation-rule dependencies until those definitions can be rewritten atomically.

First supported version: 4.0.4 · Related documentation

ALTER TABLE users ALTER COLUMN status SET DEFAULT 'new'

Limitations

• SET DEFAULT accepts only the literal default forms supported by CREATE TABLE; it changes future writes and does not rewrite existing values.

First supported version: 4.0.4 · Related documentation

ALTER TABLE users ALTER COLUMN email SET NOT NULL

Limitations

• SET NOT NULL scans existing rows and refuses the metadata change if any value is NULL. Type and collation changes are not part of this feature.

First supported version: 4.0.4 · Related documentation

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL)

Limitations

• This row covers primitive columns, nullability, and a single-column primary key; defaults, checks, and composite primary keys are separate rows.

First supported version: 4.0.4 · Related documentation

amount INTEGER CONSTRAINT ck_amount CHECK (amount >= 0)

Limitations

• A column-scoped CHECK may reference only its own column and otherwise follows the deterministic row-local expression limits of table CHECK. Core SQL covers enforcement, persistence, catalog exposure, column/table rename repair, ADO.NET metadata, direct/REST/gRPC schema transport, archive round trips, and EF create-table checks.

Intentional deviations

• A false result rejects the write, while NULL/UNKNOWN passes. Dropping a column with a CHECK dependency is rejected.

First supported version: 4.0.4 · Related documentation

CONSTRAINT pk_order_lines PRIMARY KEY (order_id, line_no)

Limitations

• This row covers PRIMARY KEY declarations inside CREATE TABLE. A composite key is a logical constraint and does not infer integer identity behavior; standalone ADD/DROP PRIMARY KEY remains outside this feature.

Intentional deviations

• One primary key is allowed per table and every participating value must be non-NULL.

First supported version: 4.0.4 · Related documentation

CONSTRAINT uq_tenant_email UNIQUE (tenant_id, email)

Limitations

• This row covers UNIQUE declarations inside CREATE TABLE. Standalone ADD UNIQUE remains outside this feature; named UNIQUE constraints can be removed with DROP CONSTRAINT when no foreign key depends on them.

Intentional deviations

• Rows containing NULL in any participating column do not collide with one another.

First supported version: 4.0.4 · Related documentation

INSERT INTO orders (status) VALUES (DEFAULT)

Limitations

• Core SQL covers INSERT DEFAULT VALUES and DEFAULT markers in value tuples against literal column defaults. Feature-specific ADO.NET, REST, gRPC, and EF query paths are not yet linked, and the available default expression shapes are limited by the literal-only column DEFAULT row.

Intentional deviations

• An explicit NULL does not request a default; it remains NULL.

First supported version: 4.0.4 · Related documentation

name TEXT NOT NULL

Limitations

• Core tests cover SQL INSERT rejection, shared direct InsertBatch validation, and sys.columns nullability. A narrow reopen proof and feature-specific ADO.NET, REST, and gRPC write tests are not yet linked.

Intentional deviations

• NOT NULL is enforced independently from CHECK; CHECK may accept UNKNOWN while NOT NULL still rejects NULL.

First supported version: 4.0.4 · Related documentation

status TEXT DEFAULT 'new'

Limitations

• The first slice accepts typed literal and NULL defaults only. Column references, parameters, functions including CURRENT_TIMESTAMP, subqueries, computed expressions, and EF DefaultValueSql remain rejected. Core SQL, ADO.NET metadata, direct/REST/gRPC schema transport, archive round trips, and EF create-table literal defaults have focused coverage.

Intentional deviations

• Omitted columns and explicit DEFAULT apply the persisted literal; explicit NULL remains NULL and is checked independently against nullability.

First supported version: 4.0.4 · Related documentation

CONSTRAINT ck_total CHECK (total >= 0)

Limitations

• Checks must be deterministic and row-local. Parameters, functions, subqueries, and qualified references are rejected. Core SQL covers enforcement, persistence, catalog exposure, rename dependency repair, named ALTER add/drop, ADO.NET metadata, direct/REST/gRPC schema transport, archive round trips, and EF create-table checks.

Intentional deviations

• A false result rejects the write, while NULL/UNKNOWN passes in accordance with SQL CHECK semantics. Enforcement is immediate.

First supported version: 4.0.4 · Related documentation

CREATE TABLE "order details" ("order id" INTEGER)

Limitations

• Core SQL tests cover reserved words, whitespace, and doubled-quote escapes through CREATE, INSERT, and SELECT. Names are limited to 128 characters and cannot contain NUL. A dedicated reopen/catalog proof and feature-specific ADO.NET, REST, gRPC, and EF query/migration round trips are not yet linked; the EF proof covers identifier generation only.

Intentional deviations

• Double quotes delimit identifiers and a doubled double quote escapes a quote. Name resolution remains ordinal case-insensitive rather than introducing case-sensitive quoted names.

First supported version: 4.0.4 · Related documentation

CREATE TABLE customer_orders (order_id INTEGER PRIMARY KEY)

Limitations

• An unquoted name must begin with a letter recognized by .NET or underscore and continue with letters, decimal digits, or underscores. Reserved keywords require quoting, NUL is rejected, and the maximum length is 128 characters.

Intentional deviations

• Name resolution is ordinal case-insensitive on current catalog paths.

First supported version: 4.0.4 · Related documentation

NULL = 1; 1 AND NULL; 0 OR NULL

Limitations

• Direct evaluator tests cover NULL comparisons, NOT NULL, and the AND/OR truth-table cases exercised by CHECK enforcement. This row does not yet qualify every WHERE, JOIN, IN/NOT IN, aggregate, transport, ADO.NET, or EF-generated expression shape.

Intentional deviations

• UNKNOWN is represented as NULL; logical operators short-circuit to FALSE or TRUE where SQL truth tables permit a determinate result.

First supported version: 4.0.4 · Related documentation

FOREIGN KEY (order_id, line_no) REFERENCES lines(order_id, line_no)

Limitations

• The referenced columns must exactly match the order, types, and collations of a primary or unique candidate key. MATCH FULL, MATCH PARTIAL, deferred validation, ON UPDATE actions, and EF composite-FK generation are not supported.

Intentional deviations

• MATCH SIMPLE is used: any NULL child component suppresses the parent lookup.

First supported version: 4.0.4 · Related documentation

parent_id INTEGER REFERENCES parents(id) ON DELETE CASCADE

Limitations

• CASCADE is immediate for scalar and composite keys. Deferred constraints and standalone EF foreign-key changes are not supported.

First supported version: 4.0.4 · Related documentation

parent_id INTEGER REFERENCES parents(id)

Limitations

• RESTRICT is immediate for scalar and composite keys. Deferred constraints and standalone EF foreign-key changes are not supported.

Intentional deviations

• Omitting ON DELETE selects immediate RESTRICT.

First supported version: 4.0.4 · Related documentation

REFERENCES parents(id) ON UPDATE CASCADE

Limitations

• Every ON UPDATE action is rejected.

First supported version: not supported · Related documentation

parent_id INTEGER REFERENCES parents(id)

Limitations

• The referenced column must be an ordered primary or unique candidate key with a compatible type and collation. EF supports inline single-column create-table relationships but rejects standalone foreign-key changes.

Intentional deviations

• Enforcement is immediate; deferred validation is not supported.

First supported version: 4.0.4 · Related documentation

FOREIGN KEY (parent_id) REFERENCES parents(id)

Limitations

• SQL supports table-level scalar and composite declarations. The EF provider generates inline scalar relationships but still rejects composite and standalone foreign-key operations.

Intentional deviations

• Constraint validation is immediate and uses MATCH SIMPLE for nullable composite child keys.

First supported version: 4.0.4 · Related documentation

dotnet ef database update

Limitations

• The supported operation set is create/drop table, add/drop/rename column, rename table, and create/drop index within the provider's schema restrictions.

First supported version: 4.0.4 · Related documentation

db.Users.Where(u => u.Id == id).ToListAsync()

Limitations

• The first-party provider is embedded/direct only and supports a deliberately bounded EF Core 10 relational subset.

First supported version: 4.0.4 · Related documentation

IMigrator.GenerateScript(options: Idempotent)

Limitations

• The provider throws a clear NotSupportedException for idempotent scripts; a migration-history and locking design is not part of the first compatibility release.

First supported version: not supported · Related documentation

connection.GetSchema("Columns")

Limitations

• Supported collections are MetaDataCollections, Restrictions, DataSourceInformation, DataTypes, ReservedWords, Tables, Columns, Indexes, IndexColumns, Views, ViewColumns, ForeignKeys, Defaults, CheckConstraints, KeyConstraints, and KeyColumns. Collection-specific restrictions are documented by the Restrictions collection.

First supported version: 4.0.4 · Related documentation

SELECT * FROM sys.columns

Limitations

• This row covers the existing core catalogs, not information_schema or future generalized constraint catalogs.

Intentional deviations

• CSharpDB uses sys.* virtual catalogs and selected underscored aliases.

First supported version: 4.0.4 · Related documentation

reader.GetSchemaTable()

Limitations

• The schema table reports intrinsic result metadata including ordinal, CLR/provider types, nullability, sizes, key/unique/identity flags when known, and expression shape. The current binder does not carry reliable source lineage for every projected expression, so unavailable base catalog, table, and column names are deliberately returned as NULL instead of being invented.

First supported version: 4.0.4 · Related documentation

SELECT * FROM sys.functions

Limitations

• The registry centralizes names, aliases, kinds, arity, return metadata, determinism, and registered callbacks. Rich binder type signatures, generated documentation tables, and a broader conformance corpus remain follow-on work.

Intentional deviations

• CSharpDB exposes this metadata through sys.functions and the sys_functions alias.

First supported version: 4.0.4 · Related documentation

SELECT id, name FROM users WHERE id = @id

Limitations

• Advanced expressions and generated provider shapes are qualified separately.

First supported version: 4.0.4 · Related documentation

SELECT o.id, c.name FROM orders o JOIN customers c ON o.customer_id = c.id

Limitations

• This row does not qualify every join type, predicate shape, or EF-generated join.

First supported version: 4.0.4 · Related documentation

WITH active AS (SELECT id FROM users WHERE enabled = 1) SELECT * FROM active

Limitations

• Recursive evaluation is not claimed by this row.

First supported version: 4.0.4 · Related documentation

ANALYZE orders

Limitations

• Temporary tables are excluded in the current version.

Intentional deviations

• ANALYZE collects planner statistics; it does not show or execute a query plan.

First supported version: 4.0.4 · Related documentation

EXPLAIN ESTIMATE FOR SELECT * FROM orders WHERE customer_id = 42

Limitations

• Output describes estimate inputs and planner decisions. It follows read-only transaction routing and prepared commands discover/rebind parameters inside the target. It is not a complete physical operator tree and reports no actual execution metrics.

Intentional deviations

• The target query is not executed; mutation targets are rejected.

First supported version: 4.0.4 · Related documentation

EXPLAIN ANALYZE SELECT * FROM orders

Limitations

• Per-operator actual rows, loops, timings, memory, and stop states are not instrumented or exposed.

First supported version: not supported · Related documentation

EXPLAIN SELECT * FROM orders WHERE customer_id = 42

Limitations

• The runtime does not yet expose one shared physical-plan descriptor for every execution path.

First supported version: not supported · Related documentation

SELECT id FROM current_users UNION SELECT id FROM archived_users

Limitations

• Current execution materializes both inputs before duplicate elimination.

Intentional deviations

• UNION removes duplicates; duplicate-preserving UNION ALL is qualified separately.

First supported version: 4.0.4 · Related documentation

SELECT id FROM current_users UNION ALL SELECT id FROM archived_users

Limitations

• Core tests cover duplicate and NULL preservation, first-branch column names, numeric promotion, incompatible-type rejection, parenthesized operands, view serialization, outer LIMIT short-circuiting, and cancellation cleanup. Feature-specific ADO.NET, REST, gRPC, and EF query proofs are not yet linked; INTERSECT ALL and EXCEPT ALL remain unsupported.

Intentional deviations

• Branch types must match or have a supported numeric common type; incompatible branches fail before enumeration.

First supported version: 4.0.4 · Related documentation

ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at)

Limitations

• Experimental in-memory slice only: one compatible window specification per SELECT; ROW_NUMBER, RANK, DENSE_RANK, and non-DISTINCT COUNT, SUM, AVG, MIN, and MAX over direct source rows. Star and mixed-expression projections preserve public SELECT-list order and hide internal window slots. Explicit frames, named windows, nested windows, mixed ordinary aggregation/grouping, incompatible specifications, navigation functions, and spill-to-disk are rejected. Large inputs remain bounded only by available process memory, although long sort/evaluation loops poll cancellation.

Intentional deviations

• Default frames are SQL-consistent: an unordered window covers the whole partition, while an ordered aggregate window uses peer-aware unbounded-preceding through current-row semantics.

First supported version: 4.0.4 · Related documentation