Supported Statements

Data manipulation statements
Statement
Supported
Notes and limitations
CALL
✅
​
CREATE TABLE AS
❌
INSERT INTO SELECT * is supported.
CREATE TABLE LIKE
✅
​
DO
❌
​
DELETE
✅
No support for referring to more than one table in a single DELETE statement.
HANDLER
❌
​
IMPORT TABLE
❌
Use dolt table import
INSERT
✅
Including support for ON DUPLICATE KEY clauses.
LOAD DATA
✅
​
LOAD XML
❌
Use dolt table import
REPLACE
✅
​
SELECT
✅
Most select statements, including UNION and JOIN, are supported.
SELECT FROM AS OF
✅
Selecting from a table as of any known revision or commit timestamp is supported.
SELECT FOR UPDATE
❌
Row-level locks are not supported.
SUBQUERIES
✅
Subqueries work, but must be given aliases. Some limitations apply.
TABLE
❌
Equivalent to SELECT * FROM TABLE without a WHERE clause.
TRUNCATE
✅
​
UPDATE
✅
No support for referring to more than one table in a single UPDATE statement.
VALUES
✅
​
WITH
✅
​
SELECT INTO
✅
Selecting into a file is not supported.
Data definition statements
Statement
Supported
Notes and limitations
ADD COLUMN
✅
​
ADD CHECK
✅
​
ADD CONSTRAINT
✅
​
ADD FOREIGN KEY
✅
​
ADD PARTITION
❌
​
ALTER COLUMN
🟠
Name and order changes are supported. Some but not all type changes are supported.
ALTER DATABASE
❌
​
ALTER EVENT
✅
Moving events across databases using RENAME TO clause is not supported yet.
ALTER INDEX
❌
Indexes can be created and dropped, but not altered.
ALTER PRIMARY KEY
✅
​
ALTER TABLE
✅
Not all ALTER TABLE statements are supported. See the rest of this table for details.
ALTER TYPE
🟠
Some type changes are supported but not all.
ALTER VIEW
❌
Views can be created and dropped, but not altered.
CHANGE COLUMN
✅
​
CREATE DATABASE
✅
Creates a new dolt database rooted relative to the server directory
CREATE EVENT
🟠
Dolt stores event definitions but event execution is not supported yet.
CREATE FUNCTION
❌
​
CREATE INDEX
🟠
Fulltext indexes are not supported.
CREATE SCHEMA
✅
Creates a new dolt database rooted relative to the server directory
CREATE TABLE
✅
​
CREATE TABLE AS
❌
​
CREATE TRIGGER
✅
​
CREATE VIEW
✅
​
DESCRIBE TABLE
✅
​
DROP COLUMN
✅
​
DROP CONSTRAINT
✅
​
DROP DATABASE
✅
Deletes the dolt data directory. This is unrecoverable.
DROP EVENT
❌
​
DROP FUNCTION
❌
​
DROP INDEX
✅
​
DROP SCHEMA
✅
Deletes the dolt data directory. This is unrecoverable.
DROP TABLE
✅
​
DROP PARTITION
❌
​
DROP PROCEDURE
✅
​
DROP TRIGGER
✅
​
DROP VIEW
✅
​
MODIFY COLUMN
✅
​
RENAME COLUMN
✅
​
RENAME CONSTRAINT
❌
​
RENAME DATABASE
❌
Database names are read-only, but can be configured in the server config.
RENAME INDEX
❌
​
RENAME TABLE
✅
​
SHOW COLUMNS
✅
​
SHOW CONSTRAINTS
❌
​
SHOW CREATE FUNCTION
❌
​
SHOW CREATE PROCEDURE
✅
​
SHOW CREATE TABLE
✅
​
SHOW CREATE VIEW
✅
​
SHOW DATABASES
✅
​
SHOW FUNCTION CODE
❌
​
SHOW FUNCTION STATUS
❌
​
SHOW GRANTS
🟠
Database privileges, table privileges, and role assumption are not yet implemented.
SHOW INDEX
❌
​
SHOW PRIVILEGES
✅
​
SHOW PROCEDURE CODE
❌
​
SHOW PROCEDURE STATUS
✅
​
SHOW SCHEMAS
✅
​
SHOW TABLES
✅
SHOW FULL TABLES reveals whether a table is a base table or a view.
TRUNCATE TABLE
✅
​
Transactional statements
Dolt supports atomic transactions like other SQL databases. It's also possible for clients to connect to different heads, which means they will never see each other's edits until a merge between heads is performed. See Using Branches for more detail.
Dolt has two levels of persistence:
1.The SQL transaction layer, where a COMMIT statement atomically updates the working set for the connected head
2.The Dolt commit layer, where commits are added to the Dolt commit graph with an author, a parent commit, etc.
Statement
Supported
Notes and limitations
BEGIN
✅
Synonym for START TRANSACTION
COMMIT
✅
​
CALL DOLT_COMMIT()
✅
DOLT_COMMIT() creates a new Dolt commit using the content of the STAGED HEAD. See docs on DOLT_COMMIT() for details.​
LOCK TABLES
❌
LOCK TABLES parses correctly but does not prevent access to those tables from other sessions.
ROLLBACK
✅
​
SAVEPOINT
✅
​
RELEASE SAVEPOINT
✅
​
ROLLBACK TO SAVEPOINT
✅
​
@@autocommit
✅
​
SET TRANSACTION
❌
Different isolation levels are not yet supported.
START TRANSACTION
✅
​
UNLOCK TABLES
🟠
UNLOCK TABLES parses correctly, but since LOCK TABLES doesn't prevent concurrent access it's essentially a no-op.
Prepared statements
Statement
Supported
Notes and limitations
PREPARE
✅
Prepared statements do not work inside of a STORED PROCEDURE.
EXECUTE
✅
Execute statments do not work inside of a STORED PROCEDURE.
Access management statements
More information on how Dolt handles access management may be found in the access management page.
Statement
Supported
Notes and limitations
ALTER USER
❌
​
CREATE ROLE
✅
​
CREATE USER
🟠
Only supports basic user creation with an optional password
DROP ROLE
✅
​
DROP USER
✅
​
GRANT
🟠
Only handles static privileges down to the table level
RENAME USER
❌
​
REVOKE
🟠
Only handles static privileges down to the table level
SET DEFAULT ROLE
❌
​
SET PASSWORD
❌
​
SET ROLE
❌
​
Session management statements
Statement
Supported
Notes and limitations
SET
✅
​
SET CHARACTER SET
✅
​
SET NAMES
✅
​
KILL QUERY
✅
​
Utility statements
Statement
Supported
Notes and limitations
EXPLAIN
✅
​
USE
✅
​
Compound statements
Statement
Supported
Notes and limitations
BEGIN END
✅
​
STATEMENT LABELS
🟠
Labels are only supported for LOOP.
DECLARE
✅
Fully supports declaring variables.
DECLARE ... CONDITION
✅
​
DECLARE ... HANDLER
🟠
Partially supports handling the NOT FOUND condition when using cursors.
DECLARE ... CURSOR
✅
​
SET
✅
​
CASE
✅
​
IF
✅
​
ITERATE
✅
​
LEAVE
✅
​
LOOP
✅
​
REPEAT
✅
​
RETURN
❌
​
WHILE
❌
​
CLOSE
✅
​
FETCH
✅
​
OPEN
✅
​

Statement	Supported	Notes and limitations
`CALL`	✅
`CREATE TABLE AS`	❌	`INSERT INTO SELECT *` is supported.
`CREATE TABLE LIKE`	✅
`DO`	❌
`DELETE`	✅	No support for referring to more than one table in a single `DELETE` statement.
`HANDLER`	❌
`IMPORT TABLE`	❌	Use `dolt table import`
`INSERT`	✅	Including support for `ON DUPLICATE KEY` clauses.
`LOAD DATA`	✅
`LOAD XML`	❌	Use `dolt table import`
`REPLACE`	✅
`SELECT`	✅	Most select statements, including `UNION` and `JOIN`, are supported.
`SELECT FROM AS OF`	✅	Selecting from a table as of any known revision or commit timestamp is supported.
`SELECT FOR UPDATE`	❌	Row-level locks are not supported.
`SUBQUERIES`	✅	Subqueries work, but must be given aliases. Some limitations apply.
`TABLE`	❌	Equivalent to `SELECT * FROM TABLE` without a `WHERE` clause.
`TRUNCATE`	✅
`UPDATE`	✅	No support for referring to more than one table in a single `UPDATE` statement.
`VALUES`	✅
`WITH`	✅
`SELECT INTO`	✅	Selecting into a file is not supported.

Statement	Supported	Notes and limitations
`ADD COLUMN`	✅
`ADD CHECK`	✅
`ADD CONSTRAINT`	✅
`ADD FOREIGN KEY`	✅
`ADD PARTITION`	❌
`ALTER COLUMN`	🟠	Name and order changes are supported. Some but not all type changes are supported.
`ALTER DATABASE`	❌
`ALTER EVENT`	✅	Moving events across databases using RENAME TO clause is not supported yet.
`ALTER INDEX`	❌	Indexes can be created and dropped, but not altered.
`ALTER PRIMARY KEY`	✅
`ALTER TABLE`	✅	Not all `ALTER TABLE` statements are supported. See the rest of this table for details.
`ALTER TYPE`	🟠	Some type changes are supported but not all.
`ALTER VIEW`	❌	Views can be created and dropped, but not altered.
`CHANGE COLUMN`	✅
`CREATE DATABASE`	✅	Creates a new dolt database rooted relative to the server directory
`CREATE EVENT`	🟠	Dolt stores event definitions but event execution is not supported yet.
`CREATE FUNCTION`	❌
`CREATE INDEX`	🟠	Fulltext indexes are not supported.
`CREATE SCHEMA`	✅	Creates a new dolt database rooted relative to the server directory
`CREATE TABLE`	✅
`CREATE TABLE AS`	❌
`CREATE TRIGGER`	✅
`CREATE VIEW`	✅
`DESCRIBE TABLE`	✅
`DROP COLUMN`	✅
`DROP CONSTRAINT`	✅
`DROP DATABASE`	✅	Deletes the dolt data directory. This is unrecoverable.
`DROP EVENT`	❌
`DROP FUNCTION`	❌
`DROP INDEX`	✅
`DROP SCHEMA`	✅	Deletes the dolt data directory. This is unrecoverable.
`DROP TABLE`	✅
`DROP PARTITION`	❌
`DROP PROCEDURE`	✅
`DROP TRIGGER`	✅
`DROP VIEW`	✅
`MODIFY COLUMN`	✅
`RENAME COLUMN`	✅
`RENAME CONSTRAINT`	❌
`RENAME DATABASE`	❌	Database names are read-only, but can be configured in the server config.
`RENAME INDEX`	❌
`RENAME TABLE`	✅
`SHOW COLUMNS`	✅
`SHOW CONSTRAINTS`	❌
`SHOW CREATE FUNCTION`	❌
`SHOW CREATE PROCEDURE`	✅
`SHOW CREATE TABLE`	✅
`SHOW CREATE VIEW`	✅
`SHOW DATABASES`	✅
`SHOW FUNCTION CODE`	❌
`SHOW FUNCTION STATUS`	❌
`SHOW GRANTS`	🟠	Database privileges, table privileges, and role assumption are not yet implemented.
`SHOW INDEX`	❌
`SHOW PRIVILEGES`	✅
`SHOW PROCEDURE CODE`	❌
`SHOW PROCEDURE STATUS`	✅
`SHOW SCHEMAS`	✅
`SHOW TABLES`	✅	`SHOW FULL TABLES` reveals whether a table is a base table or a view.
`TRUNCATE TABLE`	✅

Statement	Supported	Notes and limitations
`BEGIN`	✅	Synonym for `START TRANSACTION`
`COMMIT`	✅
`CALL DOLT_COMMIT()`	✅	`DOLT_COMMIT()` creates a new Dolt commit using the content of the STAGED HEAD. See docs on DOLT_COMMIT() for details.
`LOCK TABLES`	❌	`LOCK TABLES` parses correctly but does not prevent access to those tables from other sessions.
`ROLLBACK`	✅
`SAVEPOINT`	✅
`RELEASE SAVEPOINT`	✅
`ROLLBACK TO SAVEPOINT`	✅
`@@autocommit`	✅
`SET TRANSACTION`	❌	Different isolation levels are not yet supported.
`START TRANSACTION`	✅
`UNLOCK TABLES`	🟠	`UNLOCK TABLES` parses correctly, but since `LOCK TABLES` doesn't prevent concurrent access it's essentially a no-op.

Statement	Supported	Notes and limitations
`PREPARE`	✅	Prepared statements do not work inside of a STORED PROCEDURE.
`EXECUTE`	✅	Execute statments do not work inside of a STORED PROCEDURE.

Statement	Supported	Notes and limitations
`ALTER USER`	❌
`CREATE ROLE`	✅
`CREATE USER`	🟠	Only supports basic user creation with an optional password
`DROP ROLE`	✅
`DROP USER`	✅
`GRANT`	🟠	Only handles static privileges down to the table level
`RENAME USER`	❌
`REVOKE`	🟠	Only handles static privileges down to the table level
`SET DEFAULT ROLE`	❌
`SET PASSWORD`	❌
`SET ROLE`	❌

Statement	Supported	Notes and limitations
`SET`	✅
`SET CHARACTER SET`	✅
`SET NAMES`	✅
`KILL QUERY`	✅

Statement	Supported	Notes and limitations
`EXPLAIN`	✅
`USE`	✅

Statement	Supported	Notes and limitations
`BEGIN END`	✅
`STATEMENT LABELS`	🟠	Labels are only supported for `LOOP`.
`DECLARE`	✅	Fully supports declaring variables.
`DECLARE ... CONDITION`	✅
`DECLARE ... HANDLER`	🟠	Partially supports handling the `NOT FOUND` condition when using cursors.
`DECLARE ... CURSOR`	✅
`SET`	✅
`CASE`	✅
`IF`	✅
`ITERATE`	✅
`LEAVE`	✅
`LOOP`	✅
`REPEAT`	✅
`RETURN`	❌
`WHILE`	❌
`CLOSE`	✅
`FETCH`	✅
`OPEN`	✅

Expressions, Functions, Operators

MySQL Information Schema

Last modified 4mo ago