Despite the existence of the SQL standard, there are still many differences between different SQL engines. Some differences are due to ambiguities or lack of detail in the standard, others are due to historical or political reasons. Those differences make it very hard to write portable SQL queries and generally hinder progress.
The SQL Acid Test defines a common sane understanding of details in SQL SELECT query semantics. This test is inspired by the influential Acid2 test for Web browsers. The test goes above and beyond the SQL standard and attempts to define sane, desireable behavior. The test should not be confused with the ACID principles for database transactions.
Tests are written in standard SQL and compiled into a single query. If the tests pass, a smiley face as shown below is displayed. If tests fail, the image is either distorted or not shown at all due to errors in query evaluation. To test your specific SQL system, copy the query in the file acid.sql below and run it.
+-----------------+
|......#####......|
|....##.....##....|
|...#.........#...|
|..#..()...()..#..|
|..#.....o.....#..|
|.#.............#.|
|..#..\...../..#..|
|..#...-----...#..|
|...#.........#...|
|....##.....##....|
|......#####......|
+-----------------+
You can use the test_binary.py script to easily run all tests on a local binary. Example usage:
python3 test_binary.py --program sqlite3
python3 test_binary.py --program mysql --extra "-u root"
python3 test_binary.py --program psql --extra "-d postgres"
python3 test_binary.py --program duckdbWe welcome contributions with additional test cases. To add a test case, add a .sql file in the sql directory and run the stitch.sh script.
The project was started as part of the Dagstuhl Seminar 23441 : Ensuring the Reliability and Robustness of Database Management Systems