Upgrade to Pro — share decks privately, control downloads, hide ads and more …

"Detecting Optimization Bugs in Database Engines via Non-Optimizing Reference Engine Construction" at ESEC/FSE '20

Manuel Rigger
October 29, 2020
160

"Detecting Optimization Bugs in Database Engines via Non-Optimizing Reference Engine Construction" at ESEC/FSE '20

Manuel Rigger

October 29, 2020
Tweet

Transcript

  1. Detecting Optimization Bugs in Database Engines via Non-optimizing Reference Engine

    Construction Manuel Rigger Zhendong Su ETH Zurich, Switzerland ESEC/FSE 2020 @RiggerManuel @ast_eth https://people.inf.ethz.ch/suz/
  2. 3 Database Management Systems (DBMSs) PostgreSQL SQLite is used in

    mobile phones, web browsers, OSs, TVs, …
  3. 6 PostgreSQL Database Management Systems (DBMSs) We found 159 unique

    bugs in these systems, 141 of which have been fixed
  4. 7 Goal: Find Logic Bugs Optimization bugs: logic bugs in

    the query optimizer that cause the DBMS to return an incorrect result set
  5. 8 Motivating Example CREATE TABLE t0(c0 UNIQUE); INSERT INTO t0

    VALUES (-1) ; SELECT * FROM t0 WHERE t0.c0 GLOB '-*'; c0 -1 t0 {}  Optimizer https://www.sqlite.org/src/tktview?name=0f0428096f
  6. 9 Example SQLite3 CREATE TABLE t0(c0 UNIQUE); INSERT INTO t0

    VALUES (-1) ; SELECT * FROM t0 WHERE t0.c0 GLOB '-*'; c0 -1 t0 {}  Optimizer https://www.sqlite.org/src/tktview?name=0f0428096f The LIKE optimization malfunctioned for non-text columns and a pattern prefix of “-”
  7. 10 Motivating Example CREATE TABLE t0(c0 UNIQUE); INSERT INTO t0

    VALUES (-1) ; SELECT * FROM t0 WHERE t0.c0 GLOB '-*'; c0 -1 t0 Optimizer -1 ✓ https://www.sqlite.org/src/tktview?name=0f0428096f
  8. 12 Background: Differential Testing SELECT * FROM t0 WHERE t0.c0

    GLOB '-*'; -O3 -O0 Optimizer Optimizer {}
  9. 13 Background: Differential Testing SELECT * FROM t0 WHERE t0.c0

    GLOB '-*'; -O3 -O0 Optimizer Optimizer {} -1
  10. 14 Background: Differential Testing SELECT * FROM t0 WHERE t0.c0

    GLOB '-*'; -O3 -O0 Optimizer Optimizer {}  ≠ -1
  11. 19 Background: Lines of Code (LOC) PostgreSQL 0.3 M LOC

    3.6 M LOC 1.4 M LOC 1.1 M LOC Retrospectively adding options to disable optimizations might be impractical
  12. 25 Given Query Consider the following format for the optimized

    query: SELECT * FROM t0 WHERE φ; t0.c0 GLOB '-*'
  13. 26 Given Query Consider the following format for the optimized

    query: SELECT * FROM t0 WHERE φ; t0.c0 GLOB '-*' It is unobvious how we could derive an unoptimized query
  14. 27 Insight First Insight: The predicate φ must always evaluate

    to the same value, irrespective of its context
  15. 32 SELECT φ FROM t0; Translation Step (Correct Case) SELECT

    * FROM t0 WHERE φ; -1 φ evaluates to TRUE for one row φ evaluates to TRUE for one row TRUE
  16. 35 Translation Step SELECT * FROM t0 WHERE φ; QUERY

    PLAN `--SEARCH TABLE t0 USING COVERING INDEX sqlite_autoindex_t0_1 (c0>? AND c0<?) Optimizer
  17. 36 Translation Step SELECT φ FROM t0; SELECT * FROM

    t0 WHERE φ; QUERY PLAN `--SEARCH TABLE t0 USING COVERING INDEX sqlite_autoindex_t0_1 (c0>? AND c0<?) QUERY PLAN `--SCAN TABLE t0 Optimizer Optimizer
  18. 37 SELECT φ FROM t0; Translation Step SELECT * FROM

    t0 WHERE φ; TRUE {} Optimizer Optimizer
  19. 38 SELECT φ FROM t0; Translation Step SELECT * FROM

    t0 WHERE φ; TRUE {} Result should contain one row Optimizer Optimizer
  20. 39 SELECT φ FROM t0; Translation Step SELECT * FROM

    t0 WHERE φ; TRUE {} Result should contain one row  Optimizer Optimizer
  21. 40 Counting Implementation SELECT COUNT(*) FROM … WHERE φ SELECT

    SUM(count) FROM ( SELECT φ IS TRUE as count FROM <tables> ); Optimizer Optimizer
  22. 41 Counting Implementation 0 1 ≠  SELECT COUNT(*) FROM

    … WHERE φ SELECT SUM(count) FROM ( SELECT φ IS TRUE as count FROM <tables> ); Optimizer Optimizer
  23. 44 Scope • Multiple tables and rows • Join clauses

    • ORDER BYs • GROUP BYs SELECT * FROM t0 LEFT JOIN t1 ON t0.c0 = t1.c0 JOIN t2 ON t2.c0 > t0.c1 WHERE t2.c0 = 5; SELECT t2.c0 = 5 FROM t0 LEFT JOIN t1 ON t0.c0 = t1.c0 JOIN t2 ON t2.c0 > t0.c1;
  24. 45 Scope • Multiple tables and rows • Join clauses

    • ORDER BYs • GROUP BYs ORDER BY does not affect which rows are fetched
  25. 49 SQLancer Generate a Database Generate a Query Validate the

    Query’s Result Heuristic database and query generators for CockroachDB and MariaDB
  26. 51 Evaluation: Found Bugs Closed DBMS Fixed Verified Intended Duplicate

    SQLite 110 0 6 0 MariaDB 1 5 0 1 PostgreSQL 5 2 1 0 CockroachDB 28 7 0 1 We found 159 bugs, 141 of which have been fixed!
  27. 52 Evaluation: Found Bugs Closed DBMS Fixed Verified Intended Duplicate

    SQLite 110 0 6 0 MariaDB 1 5 0 1 PostgreSQL 5 2 1 0 CockroachDB 28 7 0 1 We concentrated on testing SQLite
  28. 53 Evaluation: Test Oracles Crash DBMS Logic Error Release Debug

    SQLite 39 30 15 26 MariaDB 5 0 1 0 PostgreSQL 0 4 2 1 CockroachDB 7 24 4 0
  29. 54 Evaluation: Test Oracles Crash DBMS Logic Error Release Debug

    SQLite 39 30 15 26 MariaDB 5 0 1 0 PostgreSQL 0 4 2 1 CockroachDB 7 24 4 0 We found 51 optimization bugs!
  30. 55 Selected Bugs: SQLite CREATE TABLE t0(c0 COLLATE NOCASE, c1);

    CREATE INDEX i0 ON t0(0) WHERE c0 >= c1; INSERT INTO t0 VALUES ('a', 'B'); SELECT * FROM t0 WHERE t0.c1 <= t0.c0; c0 c1 'a' 'B' t0 https://www.sqlite.org/src/tktview?name=767a8cbc6d
  31. 56 Selected Bugs: SQLite CREATE TABLE t0(c0 COLLATE NOCASE, c1);

    CREATE INDEX i0 ON t0(0) WHERE c0 >= c1; INSERT INTO t0 VALUES ('a', 'B'); SELECT * FROM t0 WHERE t0.c1 <= t0.c0; c0 c1 'a' 'B' t0 https://www.sqlite.org/src/tktview?name=767a8cbc6d Comparisons that involve c0 will assume lower/uppercase characters to be equal
  32. 57 Selected Bugs: SQLite CREATE TABLE t0(c0 COLLATE NOCASE, c1);

    CREATE INDEX i0 ON t0(0) WHERE c0 >= c1; INSERT INTO t0 VALUES ('a', 'B'); SELECT * FROM t0 WHERE t0.c1 <= t0.c0; c0 c1 'a' 'B' t0 https://www.sqlite.org/src/tktview?name=767a8cbc6d {} 
  33. 58 Selected Bugs: SQLite CREATE TABLE t0(c0 COLLATE NOCASE, c1);

    CREATE INDEX i0 ON t0(0) WHERE c0 >= c1; INSERT INTO t0 VALUES ('a', 'B'); SELECT * FROM t0 WHERE t0.c1 <= t0.c0; c0 c1 'a' 'B' t0 https://www.sqlite.org/src/tktview?name=767a8cbc6d {}  SQLite commuted the comparison operator and unexpectedly used the partial index
  34. 59 Selected Bugs: SQLite CREATE TABLE t0(c0 COLLATE NOCASE, c1);

    CREATE INDEX i0 ON t0(0) WHERE c0 >= c1; INSERT INTO t0 VALUES ('a', 'B'); SELECT * FROM t0 WHERE t0.c1 <= t0.c0; c0 c1 'a' 'B' t0 https://www.sqlite.org/src/tktview?name=767a8cbc6d SQLite commuted the comparison operator and unexpectedly used the partial index 'a' 'B' ✓
  35. 60 Selected Bugs: CockroachDB CREATE TABLE t0(c0 BOOL UNIQUE, c1

    BOOL CHECK (true)); INSERT INTO t0 (c0) VALUES (true); SELECT * FROM t0 WHERE t0.c0 AND (false NOT BETWEEN SYMMETRIC t0.c0 AND NULL AND true); c0 TRUE t0 https://github.com/cockroachdb/cockroach/issues/44154
  36. 61 Selected Bugs: CockroachDB CREATE TABLE t0(c0 BOOL UNIQUE, c1

    BOOL CHECK (true)); INSERT INTO t0 (c0) VALUES (true); SELECT * FROM t0 WHERE t0.c0 AND (false NOT BETWEEN SYMMETRIC t0.c0 AND NULL AND true); c0 TRUE t0 https://github.com/cockroachdb/cockroach/issues/44154  TRUE
  37. 62 Selected Bugs: CockroachDB CREATE TABLE t0(c0 BOOL UNIQUE, c1

    BOOL CHECK (true)); INSERT INTO t0 (c0) VALUES (true); SELECT * FROM t0 WHERE t0.c0 AND (false NOT BETWEEN SYMMETRIC t0.c0 AND NULL AND true); c0 TRUE t0 https://github.com/cockroachdb/cockroach/issues/44154  TRUE Part of the WHERE clause was incorrectly discarded due to the CHECK constraint
  38. 63 Selected Bugs: CockroachDB CREATE TABLE t0(c0 BOOL UNIQUE, c1

    BOOL CHECK (true)); INSERT INTO t0 (c0) VALUES (true); SELECT * FROM t0 WHERE t0.c0 AND (false NOT BETWEEN SYMMETRIC t0.c0 AND NULL AND true); c0 TRUE t0 https://github.com/cockroachdb/cockroach/issues/44154 Part of the WHERE clause was incorrectly discarded due to the CHECK constraint ✓ {}
  39. 64 Selected Bugs: MariaDB CREATE TABLE t0(c0 INT); INSERT INTO

    t0 VALUES (1); CREATE INDEX i0 ON t0 (c0); SELECT * FROM t0 WHERE 0.5 = c0; c0 1 t0 https://jira.mariadb.org/browse/MDEV-21032
  40. 65 Selected Bugs: MariaDB CREATE TABLE t0(c0 INT); INSERT INTO

    t0 VALUES (1); CREATE INDEX i0 ON t0 (c0); SELECT * FROM t0 WHERE 0.5 = c0; c0 1 t0  https://jira.mariadb.org/browse/MDEV-21032 1
  41. 66 Selected Bugs: MariaDB CREATE TABLE t0(c0 INT); INSERT INTO

    t0 VALUES (1); CREATE INDEX i0 ON t0 (c0); SELECT * FROM t0 WHERE 0.5 = c0; c0 1 t0  https://jira.mariadb.org/browse/MDEV-21032 This bug report has not yet been addressed 1
  42. 67 Evaluation: Test Oracles Crash DBMS Logic Error Release Debug

    SQLite 39 30 15 26 MariaDB 5 0 1 0 PostgreSQL 0 4 2 1 CockroachDB 7 24 4 0 Error and crash bugs seem to be more common/easier to find
  43. 68 Limitations • Aggregate functions • Ordering of the result

    • Nondeterministic functions • Ambiguous queries (in SQLite)
  44. 69 Ambiguous Queries CREATE TABLE t0(c0); INSERT INTO t0(c0) VALUES

    (0.0), (0); CREATE VIEW v0(c0) AS SELECT DISTINCT c0 FROM t0;
  45. 70 Ambiguous Queries CREATE TABLE t0(c0); INSERT INTO t0(c0) VALUES

    (0.0), (0); CREATE VIEW v0(c0) AS SELECT DISTINCT c0 FROM t0; The view either fetches the floating-point or integer zero
  46. 71 Ambiguous Queries CREATE TABLE t0(c0); INSERT INTO t0(c0) VALUES

    (0.0), (0); CREATE VIEW v0(c0) AS SELECT DISTINCT c0 FROM t0; SELECT COUNT(*) FROM v0 WHERE v0.c0 || 0.1;
  47. 72 Ambiguous Queries CREATE TABLE t0(c0); INSERT INTO t0(c0) VALUES

    (0.0), (0); CREATE VIEW v0(c0) AS SELECT DISTINCT c0 FROM t0; SELECT COUNT(*) FROM v0 WHERE v0.c0 || 0.1; It is unspecified whether the row is fetched
  48. 73 Ambiguous Queries CREATE TABLE t0(c0); INSERT INTO t0(c0) VALUES

    (0.0), (0); CREATE VIEW v0(c0) AS SELECT DISTINCT c0 FROM t0; SELECT COUNT(*) FROM v0 WHERE v0.c0 || 0.1; It is unspecified whether the row is fetched 0 || 0.1 → TRUE 0.0 || 0.1 → FALSE
  49. 74 Ambiguous Queries CREATE TABLE t0(c0); INSERT INTO t0(c0) VALUES

    (0.0), (0); CREATE VIEW v0(c0) AS SELECT DISTINCT c0 FROM t0; SELECT COUNT(*) FROM v0 WHERE v0.c0 || 0.1; SELECT (v0.c0 || 0.1) FROM v0;
  50. 75 Ambiguous Queries CREATE TABLE t0(c0); INSERT INTO t0(c0) VALUES

    (0.0), (0); CREATE VIEW v0(c0) AS SELECT DISTINCT c0 FROM t0; SELECT COUNT(*) FROM v0 WHERE v0.c0 || 0.1; SELECT (v0.c0 || 0.1) FROM v0; 1 0
  51. 76 Ambiguous Queries CREATE TABLE t0(c0); INSERT INTO t0(c0) VALUES

    (0.0), (0); CREATE VIEW v0(c0) AS SELECT DISTINCT c0 FROM t0; SELECT COUNT(*) FROM v0 WHERE v0.c0 || 0.1; SELECT (v0.c0 || 0.1) FROM v0; 1 0 The result sets disagreed due to this ambiguity! ≠
  52. 77 Developer Reception […] Dr. Rigger's fuzzers, on the other

    hand, are able to find cases where SQLite computes an incorrect answer. Rigger has found many such cases. Most of these finds are fairly obscure corner cases involving type conversions and affinity transformations, and a good number of the finds are against unreleased features. Nevertheless, his finds are still important as they are real bugs, and the SQLite developers are grateful to be able to identify and fix the underlying problems. Rigger's work is currently unpublished. When it is released, it could be as influential as Zalewski's invention of AFL and profile-guided fuzzing. https://www.sqlite.org/testing.html
  53. 78 Bug Importance https://www.mail-archive.com/[email protected]/msg117440.html CREATE TABLE t0 (c0); CREATE TABLE

    t1 (c1); INSERT INTO t0 VALUES (1); SELECT c0 FROM t0 LEFT JOIN t1 ON c1=c0 WHERE NOT (c1 IS NOT NULL AND c1=2);
  54. 79 Bug Importance This is a cut-down example, right ?

    You can't possibly mean to do that WHERE clause in production code. https://www.mail-archive.com/[email protected]/msg117440.html CREATE TABLE t0 (c0); CREATE TABLE t1 (c1); INSERT INTO t0 VALUES (1); SELECT c0 FROM t0 LEFT JOIN t1 ON c1=c0 WHERE NOT (c1 IS NOT NULL AND c1=2);
  55. 80 Bug Importance I might not spell it like that

    myself, but a code generator would do it (and much worse!). This example was simplified from a query generated by a Django ORM queryset using .exclude(nullable_joined_table__column=1), for instance. This is a cut-down example, right ? You can't possibly mean to do that WHERE clause in production code. https://www.mail-archive.com/[email protected]/msg117440.html CREATE TABLE t0 (c0); CREATE TABLE t1 (c1); INSERT INTO t0 VALUES (1); SELECT c0 FROM t0 LEFT JOIN t1 ON c1=c0 WHERE NOT (c1 IS NOT NULL AND c1=2);
  56. 81 Bug Importance I might not spell it like that

    myself, but a code generator would do it (and much worse!). This example was simplified from a query generated by a Django ORM queryset using .exclude(nullable_joined_table__column=1), for instance. This is a cut-down example, right ? You can't possibly mean to do that WHERE clause in production code. https://www.mail-archive.com/[email protected]/msg117440.html Even “obscure” bugs might affect users CREATE TABLE t0 (c0); CREATE TABLE t1 (c1); INSERT INTO t0 VALUES (1); SELECT c0 FROM t0 LEFT JOIN t1 ON c1=c0 WHERE NOT (c1 IS NOT NULL AND c1=2);
  57. 82 @RiggerManuel [email protected] Summary Goal: detect optimization bugs Differential testing

    is inapplicable NoREC derives an unoptimized query Evaluation: Over 150 bugs in DBMSs