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

389c8e3d83119ec458c5c57e8d92da2a?s=47 Manuel Rigger
October 29, 2020
12

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

389c8e3d83119ec458c5c57e8d92da2a?s=128

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. 2 Database Management Systems (DBMSs) PostgreSQL

  3. 3 Database Management Systems (DBMSs) PostgreSQL SQLite is used in

    mobile phones, web browsers, OSs, TVs, …
  4. 4 PostgreSQL Database Management Systems (DBMSs) CockroachDB is a highly-popular,

    commercially developed NewSQL DBMS
  5. 5 PostgreSQL Database Management Systems (DBMSs) It is crucial that

    these DBMSs work correctly!
  6. 6 PostgreSQL Database Management Systems (DBMSs) We found 159 unique

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

    the query optimizer that cause the DBMS to return an incorrect result set
  8. 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
  9. 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 “-”
  10. 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
  11. 11 Background: Differential Testing SELECT * FROM t0 WHERE t0.c0

    GLOB '-*'; -O3 -O0 Optimizer Optimizer
  12. 12 Background: Differential Testing SELECT * FROM t0 WHERE t0.c0

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

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

    GLOB '-*'; -O3 -O0 Optimizer Optimizer {}  ≠ -1
  15. 15 Background: Differential Testing https://www.sqlite.org/pragma.html

  16. 16 Background: Differential Testing https://www.sqlite.org/pragma.html PRAGMA case_sensitive_like = boolean;

  17. 17 Background: Differential Testing https://www.sqlite.org/pragma.html PRAGMA case_sensitive_like = boolean; DBMSs

    typically provide only very limited control over optimizations
  18. 18 Background: Lines of Code (LOC) PostgreSQL 0.3 M LOC

    3.6 M LOC 1.4 M LOC 1.1 M LOC
  19. 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
  20. 20 Non-optimizing Reference Engine Construction (NoREC) NoREC

  21. 21 Idea Query Generator Optimizer Optimized Query {}

  22. 22 Idea Query Generator Optimizer Optimizer Optimized Query Translation Step

    Unoptimized Query {}
  23. 23 Idea Query Generator Optimizer Optimizer Optimized Query Translation Step

    Unoptimized Query  -1 ≠ {}
  24. 24 Given Query Consider the following format for the optimized

    query: SELECT * FROM t0 WHERE φ;
  25. 25 Given Query Consider the following format for the optimized

    query: SELECT * FROM t0 WHERE φ; t0.c0 GLOB '-*'
  26. 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
  27. 27 Insight First Insight: The predicate φ must always evaluate

    to the same value, irrespective of its context
  28. 28 Translation Step (Correct Case) SELECT * FROM t0 WHERE

    φ; -1
  29. 29 Translation Step (Correct Case) SELECT * FROM t0 WHERE

    φ; -1
  30. 30 SELECT φ FROM t0; Translation Step (Correct Case) SELECT

    * FROM t0 WHERE φ; -1
  31. 31 SELECT φ FROM t0; Translation Step (Correct Case) SELECT

    * FROM t0 WHERE φ; -1 TRUE
  32. 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
  33. 33 Insights Second Insight: DBMSs focus their optimizations on reducing

    the amount of data that is processed
  34. 34 Translation Step SELECT * FROM t0 WHERE φ;

  35. 35 Translation Step SELECT * FROM t0 WHERE φ; QUERY

    PLAN `--SEARCH TABLE t0 USING COVERING INDEX sqlite_autoindex_t0_1 (c0>? AND c0<?) Optimizer
  36. 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
  37. 37 SELECT φ FROM t0; Translation Step SELECT * FROM

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

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

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

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

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

    • ORDER BYs • GROUP BYs
  43. 43 Scope • Multiple tables and rows • Join clauses

    • ORDER BYs • GROUP BYs
  44. 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;
  45. 45 Scope • Multiple tables and rows • Join clauses

    • ORDER BYs • GROUP BYs ORDER BY does not affect which rows are fetched
  46. 46 Scope • Multiple tables and rows • Join clauses

    • ORDER BYs • GROUP BYs
  47. 47 Implementation https://github.com/sqlancer

  48. 48 SQLancer Generate a Database Generate a Query Validate the

    Query’s Result
  49. 49 SQLancer Generate a Database Generate a Query Validate the

    Query’s Result Heuristic database and query generators for CockroachDB and MariaDB
  50. 50 SQLancer Generate a Database Generate a Query Validate the

    Query’s Result NoREC test oracle
  51. 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!
  52. 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
  53. 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
  54. 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!
  55. 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
  56. 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
  57. 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 {} 
  58. 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
  59. 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' ✓
  60. 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
  61. 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
  62. 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
  63. 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 ✓ {}
  64. 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
  65. 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
  66. 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
  67. 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
  68. 68 Limitations • Aggregate functions • Ordering of the result

    • Nondeterministic functions • Ambiguous queries (in SQLite)
  69. 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;
  70. 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
  71. 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;
  72. 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
  73. 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
  74. 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;
  75. 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
  76. 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! ≠
  77. 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
  78. 78 Bug Importance https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/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);
  79. 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/sqlite-users@mailinglists.sqlite.org/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);
  80. 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/sqlite-users@mailinglists.sqlite.org/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);
  81. 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/sqlite-users@mailinglists.sqlite.org/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);
  82. 82 @RiggerManuel manuel.rigger@inf.ethz.ch Summary Goal: detect optimization bugs Differential testing

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