Skip to content

Latest commit

 

History

History
189 lines (144 loc) · 6.8 KB

README.md

File metadata and controls

189 lines (144 loc) · 6.8 KB
Raw

Maven Central Build Status

SQL Logic Test

SQL Logic Test is a suite of more than 7 million tests that test core aspects of SQL.

This project uses SqlLogicTests https://www.sqlite.org/sqllogictest/doc/trunk/about.wiki. for testing the database query engines. This is a suite of more than 7 million tests that are designed to be portable. They only test core aspects of SQL. For example, the tests only cover integers, strings, and floating point values, and do not test corner cases, such as overflows.

The tests in the SQL logic test suite are given as ".test" files. Each test file is a combination of SQL statements that set up the test by creating and populating tables, followed by a sequence of queries over the created tables.

Some SQL Logic Test queries are written multiple times, in different SQL dialects; the test scripts will only execute only the queries in the chosen dialect. In our implementation we use the Postgres dialect.

Running the tests

This project provides a standalone Main class with a main function, and it can be compiled into a standalone executable. The executable recognizes the following command-line arguments:

slt [options] files_or_directories_with_tests
Executes the SQL Logic Tests using a SQL execution engine
See https://www.sqlite.org/sqllogictest/doc/trunk/about.wiki
Options:
-h            Show this help message and exit
-x            Stop at the first encountered query error
-n            Do not execute, just parse the test files
-e executor   Executor to use
-b filename   Load a list of buggy commands to skip from this file
-v            Increase verbosity (can be repeated)
-u username   Postgres user name
-p password   Postgres password
Registered executors:
       hsql
       psql
       none

Here is an example invocation:

slt -e hsql select1.test index

This invocation will run the tests through HSQLDB, and will execute the test script select1.test and all the test scripts in the index directory.

If some statements or queries are expected to fail, they can be added to a file which is supplied with the -b flag. Each such statement/query must be on a separate line of the file and must be spelled identically to the way the statement/query appears in the test file, but with the newline characters removed.

The -x flag will stop at the first query error encountered.

The program will run all queries in the specified files and at the end print a list of failures including an explanation for each failure. A query can fail either because it triggers an internal database, or because it produces incorrect results. All the queries have been validated with sqlite, so a failure is very likely to be a true bug in the tested engine.

The verbosity flag -v controls both the logging messages printed while running the tests, and the details about each encountered error, after the tests have completed. The flag can be repeated to increase verbosity; meaninful values are between 0 and 2.

Listing all available tests

You can achieve this using the following invocation:

slt -e none -v

How tests are executed

This program parses SqlLogicTest files and delegates their execution to "test executors". We provide multiple executors:

The none test executor

This executor does not really run any tests. But it can still be used by the test loading mechanism to check that we correctly parse all SQL logic test files.

The JDBC executor

This executor parallels the standard ODBC executor from the SQLLogicTest suite, (which is written in C). It sends both the statements and queries to a database to be executed. The JDBC executor is an abstract Java class which can be subclassed to implement concrete executors.

The hsql executor

This is an extension of the JDBC executor that uses the HSQLDB http://hsqldb.org/ embedded database.

The psql executor

This executor is an extension of the JDBC executor that uses Postgres <> as a database. Prior to using this executor one must create a Postgres database named "slt" and users with appropriate permissions to access the database.

Adding new executors

New executors can be added by extending the SqlSLTTestExecutor class.

Get SQL Logic Test

From Maven

Get sql-logic-Test from Maven central:

<dependency>
  <groupId>net.hydromatic</groupId>
  <artifactId>sql-logic-test</artifactId>
  <version>0.3</version>
</dependency>

Download and build

You need Java (8 or higher) and Git.

$ git clone git://github.com/hydromatic/sql-logic-test.git
$ cd sql-logic-test
$ ./mvnw compile

On Windows, the last line is

> mvnw install

On Java versions less than 11, you should add parameters -Dcheckstyle.version=9.3.

More information