Sqllogictest is a program designed to verify that an SQL database engine computes correct results by comparing the results to identical queries from other SQL database engines.
The purpose of sqllogictest is to validate the logic behind the evaluation of SQL statements.
The sqllogictest program is driven by test scripts containing SQL statements and queries and, sometimes, query results.
There is two kind of scripts:
Test scripts are:
A statement is an SQL command that is to be evaluated but from which we do not expect to get results (other than success or failure). A statement might be a CREATE TABLE or an INSERT or an UPDATE or a DROP INDEX.
A statement record begins with one of the following expectations lines:
Then:
A query is an SQL command from which we expect to receive results.
A query record begins with a line of the following form:
query <type-string> <sort-mode> <label>
where:
Sorting is needed because databases handle null sorting differently then avoid ORDER BY and LIMIT clauses and use the “rowsort” or “valuesort” parameter on queries that might return NULLs.
Then:
Result section:
List:
A control record is one of the following:
halt
hash-threshold <max-result-set-size>
where:
Conditional Records Statement and query records can be prefixed with zero or more conditionals of the following form:
The skipif and onlyif prefixes can be used to implement test cases where the SQL syntax varies from one database engine to another.
Statement and query records can be prefixed with zero or more conditionals of the following form:
skipif <database-name>
onlyif <database-name>
Example:
query III rowsort label-xyzzy
SELECT a AS x, b AS y, c AS z FROM t1
skipif postgresql
query III rowsort label-xyzzy
SELECT a x, b y, c z FROM t1
skipif mysql
query I rowsort label-plover
SELECT a/b FROM t1
onlyif mysql
query I rowsort label-plover
SELECT a DIV b FROM t1
The sqllogictest program operates in two modes:
In test script completion mode, the sqllogictest program reads a prototype script and runs the statements and queries against a reference database engine.
The output is a full script that is a copy of the prototype script with result inserted.
Another way of thinking about completion mode is that it copies the script from input to output, replacing all “—-” lines and subsequent result values with the actual results from running the query.
In validation mode, the sqllogictest program reads a full script and runs the statements and queries contained therein against a database engine under test.
The results received back from the database engine are compared against the results in the full script to validate the output of the database engine
To compare two databases output, you may use this two methods.
Steps:
sqllogictest -odbc DSN=mysqlslt MySQL prototype.test >full.test
REM The -verify command-line option is used to activate validation mode.
sqllogictest -verify full.test
sqllogictest -odbc DSN=mysqlslt prototype.test >full-1.test
sqllogictest prototype.test >full-2.test
REM or / same as
REM sqllogictest full-1.test >full-2.test
diff full-1.test full-2.test