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

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/
   

   View Slide

2. 2
   Database Management Systems (DBMSs)
   PostgreSQL
   

   View Slide

3. 3
   Database Management Systems (DBMSs)
   PostgreSQL
   SQLite is used in mobile phones,
   web browsers, OSs, TVs, …
   

   View Slide

4. 4
   PostgreSQL
   Database Management Systems (DBMSs)
   CockroachDB is a highly-popular, commercially
   developed NewSQL DBMS
   

   View Slide

5. 5
   PostgreSQL
   Database Management Systems (DBMSs)
   It is crucial that these DBMSs
   work correctly!
   

   View Slide

6. 6
   PostgreSQL
   Database Management Systems (DBMSs)
   We found 159 unique bugs in these
   systems, 141 of which have been fixed
   

   View Slide

7. 7
   Goal: Find Logic Bugs
   Optimization bugs: logic bugs in the
   query optimizer that cause the DBMS
   to return an incorrect result set
   

   View Slide

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
   

   View Slide

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 “-”
   

   View Slide

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
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

15. 15
    Background: Differential Testing
    https://www.sqlite.org/pragma.html
    

    View Slide

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

    View Slide

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
    

    View Slide

18. 18
    Background: Lines of Code (LOC)
    PostgreSQL
    0.3 M LOC
    3.6 M LOC
    1.4 M LOC
    1.1 M LOC
    

    View Slide

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
    

    View Slide

20. 20
    Non-optimizing
    Reference Engine
    Construction
    (NoREC)
    NoREC
    

    View Slide

21. 21
    Idea
    Query
    Generator
    Optimizer
    Optimized
    Query
    {}
    

    View Slide

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

    View Slide

23. 23
    Idea
    Query
    Generator
    Optimizer
    Optimizer
    Optimized
    Query
    Translation
    Step
    Unoptimized
    Query
    
    -1
    ≠
    {}
    

    View Slide

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

    View Slide

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

    View Slide

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
    

    View Slide

27. 27
    Insight
    First Insight: The predicate φ must
    always evaluate to the same value,
    irrespective of its context
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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
    

    View Slide

33. 33
    Insights
    Second Insight: DBMSs focus their
    optimizations on reducing the
    amount of data that is processed
    

    View Slide

34. 34
    Translation Step
    SELECT * FROM t0
    WHERE φ;
    

    View Slide

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

    View Slide

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
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

40. 40
    Counting Implementation
    SELECT COUNT(*)
    FROM …
    WHERE φ
    SELECT SUM(count) FROM (
    SELECT φ IS TRUE
    as count
    FROM
    );
    Optimizer
    Optimizer
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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;
    

    View Slide

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

    View Slide

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

    View Slide

47. 47
    Implementation
    https://github.com/sqlancer
    

    View Slide

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

    View Slide

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

    View Slide

50. 50
    SQLancer
    Generate a
    Database
    Generate a
    Query
    Validate the
    Query’s Result
    NoREC test oracle
    

    View Slide

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!
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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!
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    {} 
    

    View Slide

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
    

    View Slide

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'
    ✓
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    ✓
    {}
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

68. 68
    Limitations
    • Aggregate functions
    • Ordering of the result
    • Nondeterministic functions
    • Ambiguous queries (in SQLite)
    

    View Slide

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;
    

    View Slide

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
    

    View Slide

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;
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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;
    

    View Slide

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
    

    View Slide

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!
    ≠
    

    View Slide

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
    

    View Slide

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);
    

    View Slide

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);
    

    View Slide

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);
    

    View Slide

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);
    

    View Slide

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
    

    View Slide