SQL support and differences

ASQL takes MySQL and MariaDB SQL, plus some PostgreSQL forms.

Supported

• Tables: CREATE/ALTER/DROP TABLE, PRIMARY KEY, UNIQUE, AUTO_INCREMENT, DEFAULT, views, foreign keys with CASCADE and SET NULL.
• Writes: INSERT, INSERT ... ON DUPLICATE KEY UPDATE, REPLACE, UPDATE, DELETE, multi-row inserts.
• Queries: inner, left, right, cross and natural joins; subqueries; UNION; WITH and WITH RECURSIVE; window functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, OVER (PARTITION BY ...)).
• PostgreSQL forms: expr::type, generate_series(), LATERAL, INSERT ... ON CONFLICT DO UPDATE | DO NOTHING.
• JSON: JSON_EXTRACT, JSON_SET, JSON_INSERT, JSON_REPLACE, JSON_ARRAY_APPEND, JSON_ARRAYAGG, JSON_OBJECTAGG.
• Programs: stored procedures, functions, triggers and events.
• Search: MATCH ... AGAINST full-text search, WKT geometry functions.
• Transactions: BEGIN/COMMIT/ROLLBACK, savepoints, SELECT ... FOR UPDATE.
• Loading: LOAD DATA [LOCAL] INFILE. See Import and export.

Differences from MySQL and MariaDB

Reads see uncommitted values. A SELECT sees the latest value of a row, including one another transaction has not committed yet (READ UNCOMMITTED), although @@transaction_isolation reports REPEATABLE-READ. Writers lock rows: an absolute SET, an insert or a delete takes the row for its transaction, while col = col + k updates from two transactions both go ahead. A deadlock is error 1213; a lock wait over 50 seconds is error 1205.

Strict mode is off by default. The default sql_mode is ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION, so invalid dates and too-long values are adjusted, not refused. Set AIOS_SQL_STRICT=1 to add STRICT_TRANS_TABLES, or SET sql_mode per session. Primary keys and unique keys are always enforced.

ROUND of a DOUBLE rounds as written. ROUND(x, d) on a double first takes its value to 15 significant digits, then rounds half away from zero, as PHP does. MariaDB rounds the binary value, so a DOUBLE holding 1.005 rounds to 1.01 in ASQL and 1.00 in MariaDB. A DECIMAL rounds the same in both.

Indexes are built by the engine. CREATE INDEX and index definitions are accepted. Lookups use the primary key and hash or sorted indexes the engine builds per column on first use. There is no ordered scan of the primary key.

Rows of a table without a primary key cannot be locked by a transaction. FOR UPDATE locks rows of the first table in the query only.

Not supported

PARTITION BY on tables, LOAD XML, fixed-width LOAD DATA, and most character sets other than UTF-8 and latin1.