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

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

Manuel Rigger
October 29, 2020
68

Teaser for "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 full-size slide

  2. 2
    Database Management Systems (DBMSs)
    PostgreSQL

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  5. 5
    Optimization Bugs
    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 full-size slide

  6. 6
    Optimization Bugs
    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 full-size slide

  7. 7
    Non-optimizing
    Reference Engine
    Construction
    (NoREC)
    NoREC
    Idea: Rewrite the query so that
    the DBMS cannot optimize it

    View full-size slide

  8. 8
    NoREC
    SELECT * FROM t0
    WHERE t0.c0 GLOB '-*'; {}
    Optimizer

    View full-size slide

  9. 9
    SELECT t0.c0 GLOB '-*'
    FROM t0;
    NoREC
    SELECT * FROM t0
    WHERE t0.c0 GLOB '-*'; {}
    Optimizer

    View full-size slide

  10. 10
    SELECT t0.c0 GLOB '-*'
    FROM t0;
    NoREC
    SELECT * FROM t0
    WHERE t0.c0 GLOB '-*';
    TRUE
    {}
    Optimizer
    Optimizer

    View full-size slide

  11. 11
    SELECT t0.c0 GLOB '-*'
    FROM t0;
    NoREC
    SELECT * FROM t0
    WHERE t0.c0 GLOB '-*';
    TRUE
    {}
    Result
    should
    contain
    one row
    Optimizer
    Optimizer

    View full-size slide

  12. 12
    SELECT t0.c0 GLOB '-*'
    FROM t0;
    NoREC
    SELECT * FROM t0
    WHERE t0.c0 GLOB '-*';
    TRUE
    {}
    Result
    should
    contain
    one row

    Optimizer
    Optimizer

    View full-size slide

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

    View full-size slide