Skip to content

Latest commit

 

History

History
558 lines (415 loc) · 19.1 KB

README.md

File metadata and controls

558 lines (415 loc) · 19.1 KB

Apache DataFusion sqllogictest

Apache DataFusion is an extensible query execution framework, written in Rust, that uses Apache Arrow as its in-memory format.

This crate is a submodule of DataFusion that contains an implementation of sqllogictest.

Overview

This crate uses sqllogictest-rs to parse and run .slt files in the test_files directory of this crate or the data/sqlite directory of the datafusion-testing repository.

Testing setup

  1. rustup update stable DataFusion uses the latest stable release of Rust
  2. git submodule init
  3. git submodule update --init --remote --recursive

Running tests: TLDR Examples

# Run all tests
cargo test --test sqllogictests
# Run all tests, with debug logging enabled
RUST_LOG=debug cargo test --test sqllogictests
# Run only the tests in `information_schema.slt`
cargo test --test sqllogictests -- information_schema
# Automatically update ddl.slt with expected output
cargo test --test sqllogictests -- ddl --complete
# Run ddl.slt, printing debug logging to stdout
RUST_LOG=debug cargo test --test sqllogictests -- ddl

Per-file timing summary

The sqllogictest runner can emit deterministic per-file elapsed timings to help identify slow test files.

Timing summary output is disabled by default and enabled with --timing-summary (or SLT_TIMING_SUMMARY=true).

When timing summary is enabled, periodic Progress: lines are suppressed by default to keep output stable.

# Show deterministic per-file elapsed timings (sorted slowest first)
cargo test --test sqllogictests -- --timing-summary
# Keep only the top 10 lines using standard shell tooling
cargo test --test sqllogictests -- --timing-summary | head -n 10
# Enable via environment variable
SLT_TIMING_SUMMARY=1 cargo test --test sqllogictests
# Optional debug logging for per-task slow files (>30s), disabled by default
SLT_TIMING_DEBUG_SLOW_FILES=1 cargo test --test sqllogictests

Memory drift

The runner compares the bytes reserved in MemoryPools with the bytes actually allocated, to find operators whose memory is not tracked by the pool (see #25650). It is enabled by default and prints the largest drift seen at the end of the run. It only logs and never fails a test.

Test files run concurrently by default, so the comparison is process-wide: allocated bytes across the whole process against reservations summed across all files. In this mode, the file in the output can be wrong. To attribute drift to one file, run one file at a time:

cargo test --test sqllogictests -- --test-threads 1

In both modes, the consumer in the output is the reservation change that took the sample, not necessarily the code that allocated the untracked memory. Most drift at this scale is memory that MemoryPool does not track by design (for example, in-flight batches and Parquet read buffers), so a large drift is a lead to investigate, not necessarily a bug.

# Log each 64 MB rise in drift, with the file and consumer that took the sample
RUST_LOG=datafusion_execution::memory_pool=info cargo test --test sqllogictests
# Log each 4 MB rise instead (also settable via SLT_MEMORY_DRIFT_LOG_THRESHOLD)
RUST_LOG=datafusion_execution::memory_pool=info cargo test --test sqllogictests -- \
  --test-threads 1 --memory-drift-log-threshold 4194304
# Disable (allocations are still counted, but pools are not wrapped)
cargo test --test sqllogictests -- --memory-drift false

Cookbook: Adding Tests

  1. Add queries

Add the setup and queries you want to run to a .slt file (my_awesome_test.slt in this example) using the following format:

query
CREATE TABLE foo AS VALUES (1);

query
SELECT * from foo;
  1. Fill in expected answers with --complete mode

Running the following command will update my_awesome_test.slt with the expected output:

cargo test --test sqllogictests -- my_awesome_test --complete
  1. Verify the content

In the case above, my_awesome_test.slt will look something like

statement ok
CREATE TABLE foo AS VALUES (1);

query I
SELECT * from foo;
----
1

Assuming it looks good, check it in!

Cookbook: Testing errors

To check an error message, write it as plain text after ----:

statement error
SELECT * FROM no_such_table;
----
DataFusion error: Error during planning: table 'datafusion.public.no_such_table' not found


The message must match exactly, and the block must end with two blank lines. With only one, the next record is read as part of the expected error: the test fails, and --complete deletes that record from the file.

To fill in the message, write the query, then ---- and two blank lines, and run --complete.

Avoid putting the whole message on the statement error line. There it is a regex, so --complete escapes every special character:

statement error DataFusion error: Error during planning: table 'datafusion\.public\.no_such_table' not found
SELECT * FROM no_such_table;

Use that line only to match part of a message:

statement error no_such_table
SELECT * FROM no_such_table;

Cookbook: Testing for whitespace

The sqllogictest runner will automatically strip trailing whitespace, meaning it requires an additional effort to verify that trailing whitespace is correctly produced

For example, the following test can't distinguish between Andrew and Andrew (with trailing space):

query T
select substr('Andrew Lamb', 1, 7)
----
Andrew

To test trailing whitespace, project additional non-whitespace column on the right. For example, by selecting '|' after the column of interest, the test can distinguish between Andrew and Andrew :

# Note two spaces between `Andrew` and `|`
query TT
select substr('Andrew Lamb', 1, 7), '|'
----
Andrew  |

# Note only one space between `Andrew` and `|`
query TT
select substr('Andrew Lamb', 1, 6), '|'
----
Andrew |

Cookbook: Ignoring volatile output

Sometimes parts of a result change every run (timestamps, counters, etc.). To keep the rest of the snapshot checked in, replace those fragments with the <slt:ignore> marker inside the expected block. During validation the marker acts like a wildcard, so only the surrounding text must match.

query TT
EXPLAIN ANALYZE SELECT * FROM generate_series(100);
----
Plan with Metrics LazyMemoryExec: partitions=1, batch_generators=[generate_series: start=0, end=100, batch_size=8192], metrics=[output_rows=101, elapsed_compute=<slt:ignore>, output_bytes=<slt:ignore>]

Cookbook: Sweeping config with configMatrix

Runs the same .slt once per combination of config values. Each directive is a comment:

# configMatrix: <key>=<v1>,<v2>[,...]
  • Repeat the directive to nest keys. Values are the cartesian product.
  • Whitespace-trimmed and deduped; repeated keys merge value lists.
  • Unknown key or invalid value fails fast, naming the file, key, and value.
  • Each override is applied like SET <key> = <value>, so datafusion.runtime.* keys (e.g. datafusion.runtime.memory_limit) and config-dependent UDFs behave exactly as they would for an in-file SET.
  • Supported by the default runner and by --substrait-round-trip. --complete rejects a file that declares directives, since it would overwrite the file with the output of a single combination. The Postgres runner ignores them.
  • Every combination runs even if an earlier one fails. Each failure is prefixed with the combination that produced it, and a file with more than one failing combination reports all of them.

Nested example (2 × 2 = 4 runs):

# configMatrix: datafusion.execution.parquet.coerce_int96=ms,us
# configMatrix: datafusion.execution.parquet.coerce_int96_tz=UTC,America/New_York

statement ok
CREATE EXTERNAL TABLE int96_from_spark
STORED AS PARQUET
LOCATION '../../parquet-testing/data/int96_from_spark.parquet';

query I
select count(*) from int96_from_spark
----
6

Failure output:

[configMatrix: datafusion.execution.parquet.coerce_int96=ms, datafusion.execution.parquet.coerce_int96_tz=UTC]
caused by
External error: 1 errors in file .../parquet_int96_matrix.slt

When more than one combination fails, all of them are reported together under a N configMatrix combinations failed: header, each still prefixed with its own combination.

Reference

Running tests: Validation Mode

In this mode, sqllogictests runs the statements and queries in a .slt file, comparing the expected output in the file to the output produced by that run.

For example, to run all tests suites in validation mode

cargo test --test sqllogictests

sqllogictests also supports cargo test style substring matches on file names to restrict which tests to run

# information_schema.slt matches due to substring matching `information`
cargo test --test sqllogictests -- information

Additionally, executing specific tests within a file is also supported. Tests are identified by line number within the .slt file; for example, the following command will run the test in line 709 for file information.slt along with any other preparatory statements:

cargo test --test sqllogictests -- information:709

Running tests: Postgres compatibility

Test files that start with prefix pg_compat_ verify compatibility with Postgres by running the same script files both with DataFusion and with Postgres

In order to have the sqllogictest run against an existing running Postgres instance, do:

PG_COMPAT=true PG_URI="postgresql://[email protected]/postgres" cargo test --features=postgres --test sqllogictests

The environment variables:

  1. PG_COMPAT instructs sqllogictest to run against Postgres (not DataFusion)
  2. PG_URI contains a libpq style connection string, whose format is described in the docs

One way to create a suitable a postgres container in docker is to use the Official Image with a command such as the following. Note the collation must be set to C otherwise ORDER BY will not match DataFusion and the tests will diff.

docker run \
  -p5432:5432 \
  -e POSTGRES_INITDB_ARGS="--encoding=UTF-8 --lc-collate=C --lc-ctype=C" \
  -e POSTGRES_HOST_AUTH_METHOD=trust \
  postgres

If you do not want to create a new postgres database and you have docker installed you can skip providing a PG_URI env variable and the sqllogictest runner will automatically create a temporary postgres docker container. For example:

PG_COMPAT=true cargo test --features=postgres --test sqllogictests

Running Tests: tpch

Test files in tpch directory runs against the TPCH data set (SF = 0.1), which must be generated before running. You can use following command to generate tpch data, assuming you are in the repository root, either with uvx and tpchgen-cli:

uvx tpchgen-cli tbl -v -s 0.1 --output-dir datafusion/sqllogictest/test_files/tpch/data

or with docker:

mkdir -p datafusion/sqllogictest/test_files/tpch/data
docker run -it \
  -v "$(realpath datafusion/sqllogictest/test_files/tpch/data)":/data \
  ghcr.io/scalytics/tpch-docker:main -vf -s 0.1

Then you need to add INCLUDE_TPCH=true to run tpch tests:

INCLUDE_TPCH=true cargo test --test sqllogictests

Running Tests: sqlite

Test files in data/sqlite directory of the datafusion-testing crate were sourced from the sqlite test suite and have been cleansed and updated to run within DataFusion's sqllogictest runner.

To run the sqlite tests you need to increase the rust stack size and add INCLUDE_SQLITE=true to run the sqlite tests:

export RUST_MIN_STACK=30485760;
INCLUDE_SQLITE=true cargo test --test sqllogictests

Note that there are well over 5 million queries in these tests and running the sqlite tests will take a long time. You may wish to run them in release-nonlto mode:

INCLUDE_SQLITE=true cargo test --profile release-nonlto --test sqllogictests

The sqlite tests can also be run with the postgres runner to verify compatibility:

export RUST_MIN_STACK=30485760;
PG_COMPAT=true INCLUDE_SQLITE=true cargo test --features=postgres --test sqllogictests

To update the sqlite expected answers use the datafusion/sqllogictest/regenerate_sqlite_files.sh script.

Note this must be run with an empty postgres instance. For example

PG_URI=postgresql://postgres@localhost:5432/postgres bash datafusion/sqllogictest/regenerate_sqlite_files.sh

Updating tests: Completion Mode

In test script completion mode, sqllogictests reads a prototype script and runs the statements and queries against the database engine. The output is a full script that is a copy of the prototype script with result inserted.

You can update the tests / generate expected output by passing the --complete argument.

To regenerate and complete the sqlite test suite's files in datafusion-testing/data/sqlite/ please refer to the './regenerate_sqlite_files.sh' file.

WARNING: The regenerate_sqlite_files.sh is experimental and should be understood and run with an abundance of caution. When run the script will clone a remote repository locally, replace the location of a dependency with a custom git version, will replace an existing .rs file with one from a github gist and will run various commands locally.

# Update ddl.slt with output from running
cargo test --test sqllogictests -- ddl --complete

Running tests: scratchdir

The DataFusion sqllogictest runner automatically creates a directory named test_files/scratch/<filename>, creating it if needed and clearing any file contents if it exists.

For example, the test_files/copy.slt file should use scratch directory test_files/scratch/copy.

Tests that need to write temporary files should write (only) to this directory to ensure they do not interfere with others concurrently running tests.

Running tests: Substrait round-trip mode

This mode runs the selected .slt test files in validation mode, adding a Substrait conversion round-trip for each generated DataFusion logical plan (SQL statement → DF logical → Substrait → DF logical → DF physical → execute).

Not all statements will be round-tripped, some statements like CREATE, INSERT, SET or EXPLAIN statements will be issued as is, but any other statement will be round-tripped to/from Substrait.

WARNING: this mode lives behind the substrait feature, and the full suite still reports failures. CI therefore runs limit.slt through cargo xtask ci step test substrait and optimized conditionless-join tests through cargo xtask ci step test substrait-optimized. Some of the failures are collected in #16248. To run the default suite in this mode:

cargo test --test sqllogictests --features substrait -- --substrait-round-trip

For focusing on one specific failing test, a file:line filter can be used:

cargo test --test sqllogictests --features substrait -- --substrait-round-trip binary.slt:23

Add --substrait-optimize to optimize the logical plan before serialization. This exercises producer inputs created by optimizer rewrites, such as conditionless joins from LEFT JOIN ... ON true and uncorrelated WHERE EXISTS:

cargo test --test sqllogictests --features substrait -- --substrait-round-trip --substrait-optimize joins_conditionless.slt

.slt file format

sqllogictest was originally written for SQLite to verify the correctness of SQL queries against the SQLite engine. The format is designed engine-agnostic and can parse sqllogictest files (.slt), runs queries against an SQL engine and compares the output to the expected output.

Tests in the .slt file are a sequence of query records generally starting with CREATE statements to populate tables and then further queries to test the populated data.

Each .slt file runs in its own, isolated SessionContext, to make the test setup explicit and so they can run in parallel. Thus it important to keep the tests from having externally visible side effects (like writing to a global location such as /tmp/)

Query records follow the format:

# <test_name>
query <type_string> <sort_mode>
<sql_query>
----
<expected_result>
  • test_name: Uniquely identify the test name (DataFusion only)
  • type_string: A short string that 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:
    • 'B' - Boolean,
    • 'D' - Datetime,
    • 'I' - Integer,
    • 'P' - timestamP,
    • 'R' - floating-point results,
    • 'T' - Text,
    • "?" - any other types
  • expected_result: In the results section, some values are converted according to some rules:
    • floating point values are rounded to the scale of "12" and "15" for Spark,
    • NULL values are rendered as NULL,
    • empty strings are rendered as (empty),
    • boolean values are rendered as true/false,
    • this list can be not exhaustive, check the datafusion/sqllogictest/src/engines/conversion.rs for details.
  • sort_mode: If included, it must be one of nosort (default), rowsort, or valuesort. In nosort mode, the results appear in exactly the order in which they were received from the database engine. 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. The rowsort mode gathers all output from the database engine then sorts it by rows on the client side. Sort comparisons use sort_unstable on the rendered text representation of the values. Hence, "9" sorts after "10", not before. The valuesort mode works like rowsort except that it does not honor row groupings. Each individual result value is sorted on its own.

⚠️ It is encouraged to either apply order by, or use rowsort for queries without explicit order by clauses.

Example

# group_by_distinct
query TTI
SELECT a, b, COUNT(DISTINCT c) FROM my_table GROUP BY a, b ORDER BY a, b
----
foo bar 10
foo baz 5
foo     4
        3