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

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