This is an unofficial mirror of the sqllogictests provided by SQLite, available at https://www.sqlite.org/sqllogictest/doc/trunk/about.wiki. In addition to the tests themselves, this repository also provides a go parser and runner for executing the tests against a database of your choice. Simply provide a test harness, then point the runner at a file or directory containing the subset of the sqllogictests you want to run.
Additionally, we found that the tests did not match the values returned by modern MySQL (8.x), principally the return schemas. They have been regenerated to conform to what MySQL returns.
To measure Dolt's SQL correctness, we test each release of Dolt against the sqllogictest SQL test suite. This suite consists of 5.9 million SQL queries and the results of running them against MySQL. Here are our most recent benchmarks and metrics.
The parser for sqllogictest files can be found in parser.go. The parser supports reading all records for test files in this unofficial mirror, with the exception of hash-threshold control records, which are ignored.
Each test script contains many records, to be executed in order by a runner. In addition to standard getter methods the query string, results, etc., the Record object also contains several useful helper methods:
NumResultsreturns the number of expected results for a record, correctly handling both native and hashed resultsHashResultreturns the md5 string for expected results of the query, for records that expect a hash resultShouldExecuteForEngineconsiders theonlyifandskipifconditions for a record and returns whether a record should be run for a given engine.SortResultssorts a set of result strings according to the rules of the record
The harness for running a test against a database engine of your choice is defined in harness.go.
For an example of how to define a harness against a real database, see the harness defined by dolt. Additional examples for other databases will be added to this mirror over time.
The runner parses one or more test files, runs them against a harness you provide, and emits pass or failure results in a log format. For those interested in analyzing test results automatically, logs emitted by the runner can be parsed back into structs with the result_parser methods.
Example output of the runner looks like:
2019-10-16T16:02:18.3418683-07:00 evidence/in1.test:51: SELECT 1 NOT IN (2,3,4,5,6,7,8,9) ok
2019-10-16T16:02:18.3418683-07:00 evidence/in1.test:57: SELECT null IN () skipped
2019-10-16T16:02:18.3418683-07:00 evidence/in1.test:63: SELECT null NOT IN () skipped
2019-10-16T16:02:18.3428692-07:00 evidence/in1.test:68: CREATE TABLE t1(x INTEGER) not ok: Unexpected error no primary key columns
For dolt, we periodically publish dolt's performance against these tests to a dolt repository. You can clone the repository and inspect the results using the dolt tool:
% dolt clone Liquidata/dolt-sqllogictest-results
% cd dolt-sqllogictest-results
% dolt sql -q "select result, count(*) from dolt_results group by 1"
+---------+----------+
| result | COUNT(*) |
+---------+----------+
| skipped | 1315601 |
| ok | 1335695 |
| not ok | 4233009 |
+---------+----------+
The tests were regenerated to conform to what MySQL 8.x returns. The exceptions are some stored procedures and views, where MySQL made breaking changes to the syntax. Those tests fail against MySQL currently.