Share
Embed
"Finding Bugs in Database Systems via Query Partitioning" at OOPSLA '20
Manuel Rigger
October 29, 2020
Transcript
72 slides
-
Finding Bugs in Database Systems via Query Partitioning Manuel Rigger
Zhendong Su ETH Zurich, Switzerland OOPSLA 2020 @RiggerManuel @ast_eth https://people.inf.ethz.ch/suz/ -
3 PostgreSQL Database Management Systems (DBMSs) MySQL is the most
popular open-source DBMS -
4 Database Management Systems (DBMSs) PostgreSQL TiDB and CockroachDB are
highly-popular, commercially developed NewSQL DBMSs -
5 PostgreSQL Database Management Systems (DBMSs) DuckDB is an emerging
embedded DBMS for analytical query workloads -
6 Database Management Systems (DBMSs) PostgreSQL We found 193 bugs
in these systems, 142 of which have been fixed -
7 Goal: Find Logic Bugs Logic bugs: DBMS returns an
incorrect result set -
8 Motivating Example c0 0 c0 -0 t0 t1 SELECT
* FROM t0, t1 WHERE t0.c0 = t1.c0; ? -
9 Motivating Example c0 0 c0 -0 t0 t1 SELECT
* FROM t0, t1 WHERE t0.c0 = t1.c0; t0.c0 t0.c1 0 -0 The DBMS is expected to fetch the row since 0 = -0 should evaluate to TRUE ✓ -
10 Motivating Example c0 0 c0 -0 t0 t1 SELECT
* FROM t0, t1 WHERE t0.c0 = t1.c0; t0.c0 t0.c1 The latest version of MySQL that we tested failed to fetch the row https://bugs.mysql.com/bug.php?id=99122 -
11 Motivating Example c0 0 c0 -0 t0 t1 SELECT
* FROM t0, t1 WHERE t0.c0 = t1.c0; t0.c0 t0.c1 Challenge: Finding a test oracle that can automatically validate the result set Incorrect result! https://bugs.mysql.com/bug.php?id=99122 -
14 Background: Differential Testing Query Generator RS1 = RS2 =
RS3 ? RS1 RS2 RS3 -
16 Background: Differential Testing DBMS- specific SQL Common SQL Core
“We are unable to use Postgres as an oracle because CockroachDB has slightly different semantics and SQL support, and generating queries that execute identically on both is tricky […].” – Cockroach Labs -
17 Core Idea Novel test oracle: Partition a query into
multiple partitioning queries, from which the original query’s result set can be derived -
19 Query Partitioning Query Generator Q RS(Q) Q denotes the
(randomly-generated) original query -
20 Query Partitioning Query Generator Q RS(Q) RS(Q) denotes the
original query’s result set -
21 Query Partitioning Query Generator Q RS(Q) RS(Q’1 ) RS(Q’2
) RS(Q’3 ) RS(Q’n ) Q’1 Q’2 Q’3 Q’n Partition the result set -
22 Query Partitioning Query Generator Q RS(Q) RS(Q’) RS(Q’1 )
RS(Q’2 ) RS(Q’3 ) RS(Q’n ) Q’1 Q’2 Q’3 Q’n ♢ Q’ -
23 Query Partitioning Query Generator Q RS(Q) RS(Q’) RS(Q’1 )
RS(Q’2 ) RS(Q’3 ) RS(Q’n ) Q’1 Q’2 Q’3 Q’n ♢ Q’ Combine the results so that RS(Q)=RS(Q’) -
24 Query Partitioning Query Generator Q RS(Q) RS(Q’) RS(Q’1 )
RS(Q’2 ) RS(Q’3 ) RS(Q’n ) Q’1 Q’2 Q’3 Q’n ♢ Q’ ✓ = -
25 Query Partitioning Query Generator Q RS(Q) RS(Q’) RS(Q’1 )
RS(Q’2 ) RS(Q’3 ) RS(Q’n ) Q’1 Q’2 Q’3 Q’n ♢ Q’ ≠ -
26 Query Partitioning Query Generator Q RS(Q) RS(Q’) RS(Q’1 )
RS(Q’2 ) RS(Q’3 ) RS(Q’n ) Q’1 Q’2 Q’3 Q’n ♢ Q’ Partitioning queries -
27 Query Partitioning Query Generator Q RS(Q) RS(Q’) RS(Q’1 )
RS(Q’2 ) RS(Q’3 ) RS(Q’n ) Q’1 Q’2 Q’3 Q’n ♢ Q’ Partitions -
28 Query Partitioning Query Generator Q RS(Q) RS(Q’) RS(Q’1 )
RS(Q’2 ) RS(Q’3 ) RS(Q’n ) Q’1 Q’2 Q’3 Q’n ♢ Q’ Composition operator -
29 How to Realize This Idea? Key challenge: find a
valid partitioning strategy that stresses the DBMS -
30 Ternary Logic Consider a predicate φ and a given
row r. Exactly one of the following must hold: • φ • NOT φ • φ IS NULL -
31 Ternary Logic Consider a predicate φ and a given
row r. Exactly one of the following must hold: • φ • NOT φ • φ IS NULL -
32 Ternary Logic Consider a predicate φ and a given
row r. Exactly one of the following must hold: • φ • NOT φ • φ IS NULL φ NOT φ φ IS NULL -
33 Ternary Logic Consider a predicate φ and a given
row r. Exactly one of the following must hold: • φ • NOT φ • φ IS NULL ternary predicate variants -
34 Motivating Example c0 0 c0 -0 t0 t1 SELECT
* FROM t0, t1 WHERE t0.c0 = t1.c0; t0.c0 t0.c1 https://bugs.mysql.com/bug.php?id=99122 How did this insight allow us to detect this bug? -
37 Example: MySQL t0.c0 t0.c1 0 -0 SELECT * FROM
t0, t1 WHERE t0.c0=t1.c0 UNION ALL SELECT * FROM t0, t1 WHERE NOT (t0.c0=t1.c0) UNION ALL SELECT * FROM t0, t1 WHERE (t0.c0=t1.c0) IS NULL; SELECT * FROM t0, t1; -
38 Example: MySQL t0.c0 t0.c1 0 -0 SELECT * FROM
t0, t1 WHERE t0.c0=t1.c0 UNION ALL SELECT * FROM t0, t1 WHERE NOT (t0.c0=t1.c0) UNION ALL SELECT * FROM t0, t1 WHERE (t0.c0=t1.c0) IS NULL; SELECT * FROM t0, t1; φ -
39 Example: MySQL t0.c0 t0.c1 0 -0 SELECT * FROM
t0, t1 WHERE t0.c0=t1.c0 UNION ALL SELECT * FROM t0, t1 WHERE NOT (t0.c0=t1.c0) UNION ALL SELECT * FROM t0, t1 WHERE (t0.c0=t1.c0) IS NULL; SELECT * FROM t0, t1; t0.c0 t0.c1 φ -
40 Example: MySQL t0.c0 t0.c1 0 -0 SELECT * FROM
t0, t1 WHERE t0.c0=t1.c0 UNION ALL SELECT * FROM t0, t1 WHERE NOT (t0.c0=t1.c0) UNION ALL SELECT * FROM t0, t1 WHERE (t0.c0=t1.c0) IS NULL; SELECT * FROM t0, t1; t0.c0 t0.c1 ≠ φ -
41 Ternary Logic Partitioning (TLP) Query Generator Q RS(Q) RS(Q’)
Q’p Q’p IS NULL ♢ Q’ Q ¬p RS(Q’p ) RS(Q ¬p ) RS(Q’p IS NULL ) SELECT * FROM t0, t1; -
42 Ternary Logic Partitioning (TLP) Query Generator Q RS(Q) RS(Q’)
Q’p Q’p IS NULL ♢ Q’ Q ¬p RS(Q’p ) RS(Q ¬p ) RS(Q’p IS NULL ) SELECT * FROM t0, t1 WHERE t0.c0=t1.c0 -
43 Ternary Logic Partitioning (TLP) Query Generator Q RS(Q) RS(Q’)
Q’p Q’p IS NULL ♢ Q’ Q ¬p RS(Q’p ) RS(Q ¬p ) RS(Q’p IS NULL ) SELECT * FROM t0, t1 WHERE NOT (t0.c0=t1.c0) -
44 Ternary Logic Partitioning (TLP) Query Generator Q RS(Q) RS(Q’)
Q’p Q’p IS NULL ♢ Q’ Q ¬p RS(Q’p ) RS(Q ¬p ) RS(Q’p IS NULL ) SELECT * FROM t0, t1 WHERE (t0.c0=t1.c0) IS NULL; -
45 Ternary Logic Partitioning (TLP) Query Generator Q RS(Q) RS(Q’)
Q’p Q’p IS NULL ♢ Q’ Q ¬p RS(Q’p ) RS(Q ¬p ) RS(Q’p IS NULL ) UNION ALL -
46 Scope • WHERE • GROUP BY • HAVING •
DISTINCT queries • Aggregate functions -
47 Testing WHERE Clauses Q Q’ptern ♢(Q’p , Q’¬p ,
Q’p IS NULL ) SELECT <columns> FROM <tables> [<joins>] SELECT <columns> FROM <tables> [<joins>] WHERE ptern Q′p ⊎ Q′¬p ⊎ Q′p IS NULL -
48 Testing WHERE Clauses Q Q’ptern ♢(Q’p , Q’¬p ,
Q’p IS NULL ) SELECT <columns> FROM <tables> [<joins>] SELECT <columns> FROM <tables> [<joins>] WHERE ptern Q′p ⊎ Q′¬p ⊎ Q′p IS NULL The multiset addition can be implemented using UNION ALL -
49 Scope • WHERE • GROUP BY • HAVING •
DISTINCT queries • Aggregate functions -
50 Testing Self-decomposable Aggregate Functions Q Q’ptern ♢(Q’p , Q’¬p
, Q’p IS NULL ) SELECT MAX(<e>) FROM <tables> [<joins>] SELECT MAX(<e>) FROM <tables> [<joins>] WHERE ptern; MAX(Q′p ⊎ Q′¬p ⊎ Q′p IS NULL ) -
51 Testing Self-decomposable Aggregate Functions Q Q’ptern ♢(Q’p , Q’¬p
, Q’p IS NULL ) SELECT MAX(<e>) FROM <tables> [<joins>] SELECT MAX(<e>) FROM <tables> [<joins>] WHERE ptern; MAX(Q′p ⊎ Q′¬p ⊎ Q′p IS NULL ) A partition is an intermediate result, rather than a subset of the result set -
52 Bug Example: CockroachDB SET vectorize=experimental_on; CREATE TABLE t0(c0 INT);
CREATE TABLE t1(c0 BOOL) INTERLEAVE IN PARENT t0(rowid); INSERT INTO t0(c0) VALUES (0); INSERT INTO t1(rowid, c0) VALUES(0, TRUE); -
53 Bug Example: CockroachDB SET vectorize=experimental_on; CREATE TABLE t0(c0 INT);
CREATE TABLE t1(c0 BOOL) INTERLEAVE IN PARENT t0(rowid); INSERT INTO t0(c0) VALUES (0); INSERT INTO t1(rowid, c0) VALUES(0, TRUE); NULL SELECT MAX(t1.rowid) FROM t1; -
54 Bug Example: CockroachDB SET vectorize=experimental_on; CREATE TABLE t0(c0 INT);
CREATE TABLE t1(c0 BOOL) INTERLEAVE IN PARENT t0(rowid); INSERT INTO t0(c0) VALUES (0); INSERT INTO t1(rowid, c0) VALUES(0, TRUE); NULL 0 SELECT MAX(aggr) FROM ( SELECT MAX(t1.rowid) as aggr FROM t1 WHERE '+' >= t1.c0 UNION ALL SELECT MAX(t1.rowid) as aggr FROM t1 WHERE NOT('+' >= t1.c0) UNION ALL SELECT MAX(t1.rowid) as aggr FROM t1 WHERE ('+' >= t1.c0) IS NULL ); SELECT MAX(t1.rowid) FROM t1; ≠ -
55 Testing Decomposable Aggregate Functions Q Q’ptern ♢(Q’p , Q’¬p
, Q’p IS NULL ) SELECT AVG(<e>) FROM <tables> [<joins>]; SELECT SUM(<e>) as s, COUNT(<e>) as s FROM <tables> [<joins>]; SUM(s(Q′p ⊎ Q′¬p ⊎ Q′p IS NULL )) SUM(c(Q′p ⊎ Q′¬p ⊎ Q′p IS NULL )) -
58 SQLancer Generate a Database Generate a Query Validate the
Query’s Result Heuristic database and query generators for DuckDB, H2 and TiDB -
59 SQLancer Generate a Database Generate a Query Validate the
Query’s Result TLP test oracles -
60 Evaluation: Found Bugs Closed DBMS Fixed Verified Intended Duplicate
SQLite 4 0 0 0 MySQL 1 6 3 0 H2 16 2 0 1 CockroachDB 23 8 0 0 TiDB 26 35 0 1 DuckDB 72 0 0 2 We found 193 unique, previously unknown bugs, 142 of which have been fixed! -
61 Evaluation: Found Bugs Query Partitioning Oracle DBMS WHERE Aggregate
GROUP BY HAVING DISTINCT Error Crash SQLite 0 3 0 0 1 0 0 CockroachDB 3 3 0 1 0 22 2 TiDB 29 0 1 0 0 27 4 MySQL 7 0 0 0 0 0 0 DuckDB 21 4 1 2 1 13 19 H2 2 0 0 0 0 16 0 -
62 Query Partitioning Oracle DBMS WHERE Aggregate GROUP BY HAVING
DISTINCT Error Crash SQLite 0 3 0 0 1 0 0 CockroachDB 3 3 0 1 0 22 2 TiDB 29 0 1 0 0 27 4 MySQL 7 0 0 0 0 0 0 DuckDB 21 4 1 2 1 13 19 H2 2 0 0 0 0 16 0 Evaluation: Found Bugs The WHERE oracle is the simplest, but most effective oracle -
63 Query Partitioning Oracle DBMS WHERE Aggregate GROUP BY HAVING
DISTINCT Error Crash SQLite 0 3 0 0 1 0 0 CockroachDB 3 3 0 1 0 22 2 TiDB 29 0 1 0 0 27 4 MySQL 7 0 0 0 0 0 0 DuckDB 21 4 1 2 1 13 19 H2 2 0 0 0 0 16 0 Evaluation: Found Bugs The other oracles found interesting, but fewer bugs -
64 Query Partitioning Oracle DBMS WHERE Aggregate GROUP BY HAVING
DISTINCT Error Crash SQLite 0 3 0 0 1 0 0 CockroachDB 3 3 0 1 0 22 2 TiDB 29 0 1 0 0 27 4 MySQL 7 0 0 0 0 0 0 DuckDB 21 4 1 2 1 13 19 H2 2 0 0 0 0 16 0 Evaluation: Found Bugs We tested them only for DBMSs for which the TLP WHERE oracle could not find additional bugs -
65 Developer Reception: DuckDB This work is tremendously helpful for
us, and I imagine anyone working on a DBMS. Usually these bugs would be slowly found by users over the years, not only negatively affecting the experience of those users but also requiring much more effort to debug and reproduce […]. -
69 Adoption https://www.monetdb.org/blog/faster-robuster-and-feature-richer-monetdb-in-2020-and-beyond “With the help of SQLancer, an automatic
DBMS testing tool, we have been able to identify >100 potential problems in corner cases of the SQL processor.” -
71 Adoption Mining for logic bugs in the Citus extension
to Postgres with SQLancer by Nazli Ugur Koyluoglu https://www.citusdata.com/blog/2020/09/04/mining-for-logic-bugs-in-citus-with-sqlancer/ -
72 @RiggerManuel [email protected] Summary Goal: detect logic bugs Query Partitioning
as a general concept TLP as a concrete partition strategy Evaluation: Close to 200 bugs in DBMSs