SQL - Sqllogictest

> (Data|State) Management and Processing > (Data Type|Data Structure) > (Relation|Table) - Tabular data > Structured Query Language (SQL)

1 - About

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.

2 - Methodology

  • When sqllogictest runs a test script, it begins with a completely empty database.
  • A typical test script will begin with some CREATE statements followed by some INSERT statements to add initial data. This is followed by thousands of randomly generate UPDATE, DELETE, and INSERT statements. Several SELECT statements typical follow each UPDATE, DELETE, or INSERT in order to verify that the content of the database is as expected.
Advertising

3 - Script

3.1 - Type

The sqllogictest program is driven by test scripts containing SQL statements and queries and, sometimes, query results.

There is two kind of scripts:

  • a prototype script is a test script that omits the results. Prototype scripts will try queries using all kinds and combinations of constraints, column orders, joins, indexes, and so forth in order to exercize as many paths through the logic of the database engine as possible.
  • a full script is a test script that includes results

3.2 - Format

Test scripts are:

  • line-oriented ASCII text files.
  • zero or more records (executed from top to bottom)
  • A record is:
  • One or more Blank line separate each record
  • Comment are defined by a sharp character (“#”, ASCII code 35) and may placed anywhere
Advertising

3.2.1 - Statement Record

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:

  • statement ok - expected to succeed
  • statement error - expected to failed - For example, an INSERT statement that violates a UNIQUE or CHECK or NOT NULL constraint might fail.

Then:

  • The SQL command to be evaluated is found on the second and all subsequent lines of the record.
  • Only a single SQL command is allowed per statement.
  • The SQL should not have a semicolon or other terminator at the end; any required terminators will be added by the database engine interface module.

3.2.2 - Query Record

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:

  • <type-string> specifies the number of result columns and the expected datatype of each result column. There is one character in the <type-string> for each result column. The characters codes are:
    • T for a text result,
    • I for an integer result,
    • R for a floating-point result.
  • <sort-mode> argument is optional. If included, it must be one of:
    • nosort, (Default) The nosort mode should only be used on queries that have an ORDER BY clause or which only have a single row of result, since otherwise the order of results is undefined and might vary from one database engine to another.
    • rowsort, gathers all output from the database engine then sorts it by rows on the client side. Sort comparisons use strcmp() on the rendered ASCII text representation of the values. Hence, “9” sorts after “10”, not before.
    • valuesort. It works like rowsort except that it does not honor row groupings. Each individual result value is sorted on its own.
  • <label> optional. If included, sqllogictest stores a hash of the results of this query under the given label. If the label is reused, then sqllogictest verifies that the results are the same. This can be used to verify that two or more queries in the same test script that are logically equivalent always generate the same output.

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:

  • The SQL for the query is found on second an subsequent lines of the record up to first line of the form ---- or until the end of the record.
  • Lines following the ---- are expected results of the query, one value per line.
  • If the ---- and/or the results are omitted, then the query is expected to return an empty set.
  • The ---- and results are also omitted from prototype scripts and are always ignored when the sqllogictest program is operating in completion mode.

Result section:

  • Integer values are rendered as if by printf(“%d”).
  • Floating point values are rendered as if by printf(“%.3f”).
  • NULL values are rendered as “NULL”.
  • Empty strings are rendered as “(empty)”.
  • Within non-empty strings, all control characters and unprintable characters are rendered as “@”.
Advertising

3.2.3 - Control record

3.2.3.1 - Halt record

A control record is one of the following:

halt
hash-threshold <max-result-set-size>

where:

  • a halt record is intended for debugging use only. A halt record merely causes sqllogictest to ignore the rest of the test script. A halt record can be inserted after a query that is giving an anomalous result, causing the database to be left in the state where it gives the unexpected answer. After sqllogictest exist, manually debugging can then proceed.
  • The “hash-threshold” record sets a limit on the number of values that can appear in a result set. If the number of values exceeds this, then instead of recording each individual value in the full test script, an MD5 hash of all values is computed in stored. This makes the full test scripts much shorter, but at the cost of obscuring the results. If the hash-threshold is 0, then results are never hashed. A hash-threshold of 10 or 20 is recommended. During debugging, it is advantage to set the hash-threshold to zero so that all results can be seen.

Conditional Records Statement and query records can be prefixed with zero or more conditionals of the following form:

3.2.3.2 - If record

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:

  • SQL syntax varies from one database engine to another.
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
  • MySQL “/” operator is always floating-point division even when both operands are integers. To get integer division in MySQL you have to use the “DIV” operator.
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

4 - Execution Mode

The sqllogictest program operates in two modes:

  • test script completion mode (default mode)
  • and test script validation mode.

4.1 - Completion

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.

4.2 - Validation

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

5 - Example

5.1 - SQLite output same as MySQL with a prototype script

To compare two databases output, you may use this two methods.

5.1.1 - sqllogictest validation

Steps:

  • Create a MySQL test output (SQL+result output) - (completion mode - the default)
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

5.1.2 - File Diff

  • Prototype against MySQL
sqllogictest -odbc DSN=mysqlslt prototype.test >full-1.test
  • SQL against SQLite
sqllogictest prototype.test >full-2.test
REM or / same as
REM sqllogictest full-1.test >full-2.test
  • File Content Diff
diff full-1.test full-2.test