Advanced MySQL compatibility

[JanJakes]

This is a tracking issue for introducing a new MySQL query parser and an AST-based SQLite driver to significantly improve MySQL compatibility and expand WordPress plugin support.

I think the project can be represented in roughly the following phases:

1. Initial version of the MySQL parser — reasonably fast, small, and correct ("make it work").
2. Initial version of the SQLite driver — reach parity with the current test suite ("make it work").
3. Advanced MySQL support in the SQLite driver — see also test suites below ("make it right").
4. Advanced MySQL parser — add version support, verify against MySQL server grammar, fix edge cases ("make it right").
5. Advanced tooling — implement a custom parser for ANTLR or ENBF, add custom grammar tooling ("make it right").
6. Performance optimizations — explore possibilities to further optimize the new parser ("make it fast").

Here's a list of tasks and issues:

Core

• Exhaustive MySQL Parser
• AST-based SQLite driver Automattic/sqlite-database-integration#1 — match current MySQL compatibility
• SQLite driver — improve compatibility to satisfy multiple extensive test suites (see below)

Test suites

• SQL errors when activating most popular WP plugins: Playground Tester → SQL errors
• WP unit tests: Pass WordPress unit tests wordpress-playground#111 (comment)
• Statically extracted queries from WP plugins: CI: Monitor support for all the SQL queries used by all the WordPress plugins #159
• Supporting all WP CLI commands: Some of the WP-CLI commands don't work wordpress-playground#1618 (comment)

Advanced parser and tooling

• MySQL server version support in the new parser
• A generic grammar parser (ANTLR or [Data Liberation] EBNF processor wordpress-playground#1981)

Supported MySQL constructs

CREATE TABLE

• CREATE TABLE t (id INT)
• CREATE TABLE t (id INT NOT NULL)
• CREATE TABLE t (id INT) DEFAULT 0
• CREATE TABLE t (...) ENGINE=InnoDB
• CREATE TABLE t (id INT) COLLATE utf8mb4_czech_ci
• CREATE TABLE t (id INT PRIMARY KEY)
• CREATE TABLE t (id INT PRIMARY KEY AUTOINCREMENT)
• CREATE TABLE t (id INT AUTOINCREMENT, PRIMARY KEY(id))
• CREATE TABLE t (a INT AUTOINCREMENT, b INT, c INT, PRIMARY KEY(a, b, c))
• CREATE TABLE t (id INT, UNIQUE(id))
• CREATE TABLE t LIKE tt
• CREATE TABLE t [AS] SELECT * FROM tt
• CREATE TABLE t (...) [AS] SELECT * FROM tt
• CREATE TABLE t (...) ENGINE=InnoDB [AS] SELECT * FROM tt
• CREATE TEMPORARY TABLE t (...)
• CREATE TEMPORARY TABLE t LIKE tt
• CREATE TABLE IF NOT EXISTS t LIKE tt
• CREATE TABLE IF NOT EXISTS t (...)
• CREATE TEMPORARY TABLE IF NOT EXISTS t (...)
• CREATE TEMPORARY TABLE IF NOT EXISTS t LIKE tt

ALTER TABLE

• ALTER TABLE t ADD [COLUMN] c INT
• ALTER TABLE t ADD [COLUMN] c1 INT, c2 TEXT, ...
• ALTER TABLE t ADD [COLUMN] c INT NOT NULL
• ALTER TABLE t ADD [COLUMN] c INT DEFAULT 0
• ALTER TABLE t ADD [COLUMN] c INT NOT NULL DEFAULT 0
• ALTER TABLE t ADD [COLUMN] c INT [PRIMARY] KEY
• ALTER TABLE t ADD [COLUMN] c INT UNIQUE [KEY]
• ALTER TABLE t ADD [COLUMN] c INT AUTO_INCREMENT
• ALTER TABLE t ADD [COLUMN] c INT COMMENT 'abc'
• ALTER TABLE t ADD [COLUMN] c TEXT COLLATE 'utf8mb4_0900_ai_ci'
• ALTER TABLE t ADD [COLUMN] c INT [GENERATED ALWAYS] AS (...)
• ALTER TABLE t ADD [COLUMN] c INT VIRTUAL
• ALTER TABLE t ADD [COLUMN] c INT STORED
• ALTER TABLE t ADD [COLUMN] c INT FIRST
• ALTER TABLE t ADD [COLUMN] c INT AFTER cc
• ALTER TABLE t ADD [COLUMN] c INT VISIBLE
• ALTER TABLE t ADD [COLUMN] c INT INVISIBLE
• ALTER TABLE t ADD [COLUMN] c INT COLUMN_FORMAT DEFAULT
• ALTER TABLE t ADD [COLUMN] c INT COLUMN_FORMAT FIXED
• ALTER TABLE t ADD [COLUMN] c INT COLUMN_FORMAT DYNAMIC
• ALTER TABLE t ADD [COLUMN] c INT ENGINE_ATTRIBUTE = '...'
• ALTER TABLE t ADD [COLUMN] c INT SECONDARY_ENGINE_ATTRIBUTE = '...'
• ALTER TABLE t ADD [COLUMN] c INT STORAGE DISK
• ALTER TABLE t ADD [COLUMN] c INT STORAGE MEMORY
• ALTER TABLE t ADD [COLUMN] c REFERENCES tt (id)
• ALTER TABLE t ADD [COLUMN] c REFERENCES tt (id) MATCH FULL
• ALTER TABLE t ADD [COLUMN] c REFERENCES tt (id) MATCH PARTIAL
• ALTER TABLE t ADD [COLUMN] c REFERENCES tt (id) MATCH SIMPLE
• ALTER TABLE t ADD [COLUMN] c REFERENCES tt (id) ON UPDATE ...
• ALTER TABLE t ADD [COLUMN] c REFERENCES tt (id) ON DELETE ...
• ALTER TABLE t ADD [COLUMN] c [CONSTRAINT chck] CHECK (...)
• ALTER TABLE t ADD [COLUMN] c [CONSTRAINT chck] CHECK (...) ENFORCED
• ALTER TABLE t ADD [COLUMN] c [CONSTRAINT chck] CHECK (...) NOT ENFORCED
• ALTER TABLE t DROP [COLUMN] c
• ALTER TABLE t DROP [COLUMN] c1, c2, ...
• ALTER TABLE t DROP INDEX idx
• ALTER TABLE t DROP KEY idx
• ALTER TABLE t DROP PRIMARY KEY
• ALTER TABLE t DROP FOREIGN KEY fk
• ALTER TABLE t DROP CHECK chck
• ALTER TABLE t DROP CONSTRAINT chck
• ALTER TABLE t ADD [COLUMN] c1 INT, DROP [COLUMN] c2

Supported MySQL INFORMATION_SCHEMA tables

INFORMATION_SCHEMA.TABLES

• TABLE_CATALOG
• TABLE_SCHEMA
• TABLE_NAME
• TABLE_TYPE
• ENGINE
• VERSION
• ROW_FORMAT
• TABLE_ROWS
• ĄVG_ROW_LENGTH
• DATA_LENGTH
• MAX_DATA_LENGTH
• INDEX_LENGTH
• DATA_FREE
• AUTO_INCREMENT
• CREATE_TIME
• UPDATE_TIME — should be easy to add
• CHECK_TIME
• TABLE_COLLATION
• CHECKSUM
• CREATE_OPTIONS
• TABLE_COMMENT

Related issues

Here's a list of issues that are likely related. This may need further review and triage:

• Support for information_schema tables #146
• Unknown operation: DEFAULT CHARACTER SET #144
• Add AUTO_INCREMENT sequence to show create statements #140
• show create generates invalid default values for columns #125
• SQL syntax error in get_keys() breaks ON DUPLICATE KEY translation #119
• Unknown drop type: INDEX #116
• Support CREATE TABLE wp_posts_2 LIKE wp_posts #115
• Support ALTER TABLE x AUTO_INCREMENT = 100 #114
• Support UNION queries #107
• Support nuances of the SELECT syntax: WITH, UNION, subqueries etc. #106
• Support DELETE LIMIT #100
• Prevent SQLite from inserting NULL values into NOT NULL columns #97
• Date operations not supported #90
• No support for BINARY compare e.g. ON BINARY wp_user_meta.meta_value = mollie_customer.mollie_id #80
• Queries with INSERT INGORE are not working? #79
• Support SELECT FROM information_schema #78
• Support SHOW CREATE TABLE #74
• Support GROUP_CONCAT #70
• Wordpress sqlite driver throw constraint exception when updating options #59
• Missing plugin compatibility and overall attention #58
• WooCommerce : Unable to create order. #53 ?
• Date differences when getting post_date with SQLite #52
• Support regex_replace #47
• CREATE INDEX and ALTER TABLE not working correctly? #39
• support ALTER TABLE AUTO_INCREMENT #35
• Fatal errors with "show variables like X" queries #34
• Support a = BINARY b #31
• SHOW COLUMNS FROM table needs correctly named columns in result set. #26
• Errors running WooCommerce #19

[xnau]

I don't see this in your list, but while running the plugin I'm developing in Playground, I'm getting a lot of syntax errors due to keywords in the queries, which SQLite doesn't tolerate.

For example

SELECT v.name, g.title AS grouptitle, g.id, g.mode FROM wp_participants_database_fields v INNER JOIN wp_participants_database_groups g ON v.group = g.name ORDER BY v.id

The keyword "group" needs to be escaped for SQLite, while MariaDb is fine with it.

SELECT v.name, g.title AS grouptitle, g.id, g.mode FROM wp_participants_database_fields v INNER JOIN wp_participants_database_groups g ON v.`group` = g.name ORDER BY v.id

[JanJakes]

@xnau Thanks for the report! Indeed, some keywords that are non-reserved in MySQL can be reserved in SQLite. We can address this on a per-keyword basis, or by quoting all identifiers.