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/
Slide 2
Slide 2 text
2
Database Management Systems (DBMSs)
PostgreSQL
Slide 3
Slide 3 text
3
Database Management Systems (DBMSs)
PostgreSQL
SQLite is used in mobile phones,
web browsers, OSs, TVs, …
Slide 4
Slide 4 text
4
PostgreSQL
Database Management Systems (DBMSs)
We found 159 unique bugs in these
systems, 141 of which have been fixed
Slide 5
Slide 5 text
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
Slide 6
Slide 6 text
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
Slide 7
Slide 7 text
7
Non-optimizing
Reference Engine
Construction
(NoREC)
NoREC
Idea: Rewrite the query so that
the DBMS cannot optimize it
Slide 8
Slide 8 text
8
NoREC
SELECT * FROM t0
WHERE t0.c0 GLOB '-*'; {}
Optimizer
Slide 9
Slide 9 text
9
SELECT t0.c0 GLOB '-*'
FROM t0;
NoREC
SELECT * FROM t0
WHERE t0.c0 GLOB '-*'; {}
Optimizer
Slide 10
Slide 10 text
10
SELECT t0.c0 GLOB '-*'
FROM t0;
NoREC
SELECT * FROM t0
WHERE t0.c0 GLOB '-*';
TRUE
{}
Optimizer
Optimizer
Slide 11
Slide 11 text
11
SELECT t0.c0 GLOB '-*'
FROM t0;
NoREC
SELECT * FROM t0
WHERE t0.c0 GLOB '-*';
TRUE
{}
Result
should
contain
one row
Optimizer
Optimizer
Slide 12
Slide 12 text
12
SELECT t0.c0 GLOB '-*'
FROM t0;
NoREC
SELECT * FROM t0
WHERE t0.c0 GLOB '-*';
TRUE
{}
Result
should
contain
one row
Optimizer
Optimizer