$30 off During Our Annual Pro Sale. View Details »

"Finding Bugs in Database Systems via Query Par...

Manuel Rigger
October 29, 2020
350

"Finding Bugs in Database Systems via Query Partitioning" at OOPSLA '20

Manuel Rigger

October 29, 2020
Tweet

More Decks by Manuel Rigger

Transcript

  1. 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/
  2. 4 Database Management Systems (DBMSs) PostgreSQL TiDB and CockroachDB are

    highly-popular, commercially developed NewSQL DBMSs
  3. 5 PostgreSQL Database Management Systems (DBMSs) DuckDB is an emerging

    embedded DBMS for analytical query workloads
  4. 6 Database Management Systems (DBMSs) PostgreSQL We found 193 bugs

    in these systems, 142 of which have been fixed
  5. 8 Motivating Example c0 0 c0 -0 t0 t1 SELECT

    * FROM t0, t1 WHERE t0.c0 = t1.c0; ?
  6. 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 ✓
  7. 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
  8. 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
  9. 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
  10. 17 Core Idea Novel test oracle: Partition a query into

    multiple partitioning queries, from which the original query’s result set can be derived
  11. 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
  12. 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’
  13. 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’)
  14. 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’ ✓ =
  15. 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’ ≠
  16. 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
  17. 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
  18. 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
  19. 29 How to Realize This Idea? Key challenge: find a

    valid partitioning strategy that stresses the DBMS
  20. 30 Ternary Logic Consider a predicate φ and a given

    row r. Exactly one of the following must hold: • φ • NOT φ • φ IS NULL
  21. 31 Ternary Logic Consider a predicate φ and a given

    row r. Exactly one of the following must hold: • φ • NOT φ • φ IS NULL
  22. 32 Ternary Logic Consider a predicate φ and a given

    row r. Exactly one of the following must hold: • φ • NOT φ • φ IS NULL φ NOT φ φ IS NULL
  23. 33 Ternary Logic Consider a predicate φ and a given

    row r. Exactly one of the following must hold: • φ • NOT φ • φ IS NULL ternary predicate variants
  24. 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?
  25. 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;
  26. 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; φ
  27. 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 φ
  28. 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 ≠ φ
  29. 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;
  30. 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
  31. 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)
  32. 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;
  33. 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
  34. 46 Scope • WHERE • GROUP BY • HAVING •

    DISTINCT queries • Aggregate functions
  35. 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
  36. 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
  37. 49 Scope • WHERE • GROUP BY • HAVING •

    DISTINCT queries • Aggregate functions
  38. 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 )
  39. 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
  40. 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);
  41. 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;
  42. 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; ≠
  43. 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 ))
  44. 58 SQLancer Generate a Database Generate a Query Validate the

    Query’s Result Heuristic database and query generators for DuckDB, H2 and TiDB
  45. 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!
  46. 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
  47. 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
  48. 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
  49. 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
  50. 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 […].
  51. 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.”
  52. 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/
  53. 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