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
130

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

Manuel Rigger

October 29, 2020
Tweet

More Decks by Manuel Rigger

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

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

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

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

    View Slide

  82. 82
    @RiggerManuel [email protected]
    Summary
    Goal: detect optimization bugs Differential testing is inapplicable
    NoREC derives an unoptimized query Evaluation: Over 150 bugs in DBMSs

    View Slide