SQLsmith is a tool that can generate random SQL queries. Its paragon is Csmith, which proved valuable for quality assurance in C compilers.
SQLsmith is still in an early prototyping stage but already found a couple of minor bugs in Postgres. The only supported RDBMS is Postgres at the moment.
It might also be useful in its current stage for safely putting arbitrary databases under random load.
- C++11
- libpqxx
sqlsmith connects to the target database to retrieve the schema for query generation and to send the generated queries to. Currently, all generated statements are rolled back.
Example invocation:
cd sqlsmith make sqlsmith ./sqlsmith --verbose --target="host=/tmp port=65432"
The following options are currently supported:
--target=connstr | target database (default: libpq defaults) |
--log-to=connstr | database for logging errors (default: don’t log) |
--verbose | emit progress output |
--version | show version information |
--seed=int | seed RNG with specified integer instead of PID |
--dry-run | print queries instead of executing them |
--max-queries=long | terminate after generating this many queries |
Sample output:
--verbose makes sqlsmith emit some progress indication to stderr. A
symbol is output for each query sent to the server. Currently the
following ones are generated:
| symbol | meaning | details |
|---|---|---|
| . | ok | Query generated and executed with ok sqlstate |
| s | syntax error | These are bugs in sqlsmith - please report |
| t | timeout | SQLsmith sets a statement timeout of 1s |
| c | broken connection | These happen when a query crashes the server |
| e | other error |
It also periodically emits error reports. In the following example, these are mostly caused by the primitive type system.
queries: 39000 (202.399 gen/s, 298.942 exec/s) AST stats (avg): height = 5.599 nodes = 37.8489 82 ERROR: invalid regular expression: quantifier operand invalid 70 ERROR: canceling statement due to statement timeout 44 ERROR: operator does not exist: point = point 27 ERROR: operator does not exist: xml = xml 22 ERROR: cannot compare arrays of different element types 11 ERROR: could not determine which collation to use for string comparison 5 ERROR: invalid regular expression: nfa has too many states 4 ERROR: cache lookup failed for index 2619 4 ERROR: invalid regular expression: brackets [] not balanced 3 ERROR: operator does not exist: polygon = polygon 2 ERROR: invalid regular expression: parentheses () not balanced 1 ERROR: invalid regular expression: invalid character range error rate: 0.00705128
The only one that looks interesting here is the cache lookup one. Taking a closer look at it reveals that it happens when you query a certain catalog view like this:
self=# select indexdef from pg_catalog.pg_indexes where indexdef is not NULL; FEHLER: cache lookup failed for index 2619
This is because the planner then puts pg_get_indexdef(oid) in a
context where it sees non-index-oids, which causes it to croak:
QUERY PLAN
------------------------------------------------------------------------------------
Hash Join (cost=17.60..30.65 rows=9 width=4)
Hash Cond: (i.oid = x.indexrelid)
-> Seq Scan on pg_class i (cost=0.00..12.52 rows=114 width=8)
Filter: ((pg_get_indexdef(oid) IS NOT NULL) AND (relkind = 'i'::"char"))
-> Hash (cost=17.31..17.31 rows=23 width=4)
-> Hash Join (cost=12.52..17.31 rows=23 width=4)
Hash Cond: (x.indrelid = c.oid)
-> Seq Scan on pg_index x (cost=0.00..4.13 rows=113 width=8)
-> Hash (cost=11.76..11.76 rows=61 width=8)
-> Seq Scan on pg_class c (cost=0.00..11.76 rows=61 width=8)
Filter: (relkind = ANY ('{r,m}'::"char"[]))
Now this is more of a curiosity than a bug, but maybe someday SQLsmith finds a real one…
In order to build on Mac OSX, assuming you use Homebrew, run the following
brew install libpqxx automake libtool autoconf autoconf-archive cd sqlsmith autoreconf -i ./configure make sqlsmith
See COPYING for using and distributing this code.
Andreas Seltenreich <seltenreich@gmx.de>