Original Test Suites

Find original test suites of each DBMS at: Original Test Suites

They are downloaded from:

• DuckDB: DuckDB Test SQL
• SQLite: SQLite Test Suite
• PostgreSQL: PostgreSQL Test
• ClickHouse: ClickHouse Test Queries

Compiled Test Cases

Each test case stored in 3 files

1. db.sql: contains SQL statements for setting up database (none if doesn't need set up statements)
2. test.sql: contains the test query
3. result.csv: contains the query result.

Directory structure

1. Test cases are organized into test collections, each test collection consists of multiple test cases.

2. All test cases inside a test collection share the same db.sql file as set-up statements. (none if doesn't need set up statements)

3. Each test case consists of one test.sql file and one result.csv file.

Dialect Comparison

1. All query results are located at: Dialect Comparison
2. Top level is the guest DBMS
3. Execution of the same test case on different DBMS are stored together. File name format: {host_db}_result_{success|error}.csv

To Reproduce the Results

To reproduce the results, follow the steps outlined in the documentation provided in each test suite repository.

Environment Setup

# Create a Python virtual environment
python3 -m venv venv

# Activate the virtual environment
source venv/bin/activate

# Install dependencies
pip install duckdb psycopg2 clickhouse-connect docker
docker pull postgres:latest

To reproduce the results

python scripts/generate_result guest_db host_db
# e.g. to run SQLite's test cases on DuckDB:
python scripts/generate_result sqlite duck

Sidenote

We encountered some issues while working with ClickHouse. There were session lock issues that we couldn't resolve, and as a result, we didn't include results from ClickHouse in the report.