Testing Database Management Systems via Pivoted Query Synthesis - PowerPoint PPT Presentation

Intuition • Simpler conceptually and implementation-wise • Same effectiveness as checking all rows • Precise oracle for a single row 43

Approach

Database Generation Randomly Generate Database 45

Database Generation Randomly To explore “ all possible database states ” Generate we randomly create databases Database 46

Pivot Row Selection Randomly Select Generate Pivot Row Database 47

Query Generation Generate Randomly Select Query for the Generate Pivot Row Pivot Row Database SELECT picture, description FROM animal_pictures WHERE animal = 'Cat' AND description LIKE '%cute%' animal description picture Cat plants Cat (cute!) 48

Verifying the Result Generate Verify that Randomly Select Query for the the Pivot Row Generate Pivot Row Pivot Row is contained Database result set SELECT picture, description FROM animal_pictures DBMS WHERE animal = 'Cat' AND description LIKE '%cute%' pivot row 49

Verifying the Result Generate Verify that Randomly Select Query for the the Pivot Row Generate Pivot Row Pivot Row is contained Database result set SELECT picture, description FROM animal_pictures DBMS WHERE animal = 'Cat' AND description LIKE '%cute%' pivot row ✓ pivot row ∈ result set 50

Verifying the Result Generate Verify that Randomly Select Query for the the Pivot Row Generate Pivot Row Pivot Row is contained Database result set SELECT picture, description FROM animal_pictures DBMS WHERE animal = 'Cat' AND description LIKE '%cute%' pivot row 51

Verifying the Result Generate Verify that Randomly Select Query for the the Pivot Row Generate Pivot Row Pivot Row is contained Database result set SELECT picture, description FROM animal_pictures DBMS WHERE animal = 'Cat' AND description LIKE '%cute%' pivot row  pivot row ∉ result set 52

Verifying the Result Generate Verify that Randomly Select Query for the the Pivot Row Generate Pivot Row Pivot Row is contained Database  pivot row ∉ result set The “ containment oracle ” is PQS’ primary oracle 53

Approach Generate Verify that Randomly Select Query for the the Pivot Row Generate Pivot Row Pivot Row is contained Database 54

Approach Generate Verify that Randomly Select Query for the the Pivot Row Generate Pivot Row Pivot Row is contained Database 55

Approach Generate Verify that Randomly Select Query for the the Pivot Row Generate Pivot Row Pivot Row is contained Database 56

Approach How do we generate this query? Generate Verify that Randomly Select Query for the the Pivot Row Generate Pivot Row Pivot Row is contained Database 57

How do we Generate Queries? SELECT picture, description FROM animal_pictures WHERE Generate an expression that yields TRUE for the pivot row 58

How do we Generate Queries? Modify Use in Randomly Evaluate expression to WHERE Generate Expression on yield TRUE clause Expression Pivot Row 59

Random Expression Generation animal_pictures animal description picture We first generate a random expression https://www.sqlite.org/syntax/expr.html 60

Random Expression Generation animal = 'Cat' AND description LIKE '%cute%' AND = LIKE descrip 'Cat' animal '%cute%' tion 61

Random Expression Generation animal = 'Cat' AND description LIKE '%cute%' AND Evaluate the tree based on the pivot row = LIKE descrip 'Cat' animal '%cute%' tion 62

Random Expression Evaluation Constant nodes return their assigned literal AND values = LIKE 'Cat' '%cute%' descrip 'Cat' animal '%cute%' tion 63

Random Expression Evaluation Column references return the values from the pivot row AND 'Cat = LIKE plants (cute!)' 'Cat' 'Cat' '%cute%' descrip 'Cat' animal '%cute%' tion 64

Random Expression Evaluation Compound nodes compute their result AND based on their children TRUE TRUE 'Cat = LIKE plants (cute!)' 'Cat' 'Cat' '%cute%' descrip 'Cat' animal '%cute%' tion 65

Random Expression Evaluation TRUE AND TRUE TRUE 'Cat = LIKE plants (cute!)' 'Cat' 'Cat' '%cute%' descrip 'Cat' animal '%cute%' tion 66

Query Synthesis SELECT picture, description FROM animal_pictures WHERE animal = 'Cat' AND description LIKE '%cute%' 67

Random Expression Evaluation TRUE What about when the expression does not evaluate to TRUE ? AND TRUE TRUE 'Cat = LIKE plants (cute!)' 'Cat' 'Cat' '%cute%' descrip 'Cat' animal '%cute%' tion 68

Random Expression Evaluation What about when the expression FALSE does not evaluate to TRUE ? animal = 'Dog' = 'Dog' 'Cat' 'Dog' animal 69

Random Expression Rectification switch (result) { case TRUE: result = randexpr; case FALSE: result = NOT randexpr; case NULL: result = randexpr ISNULL; } 70

Random Expression Rectification switch (result) { case TRUE: FALSE result = randexpr; animal = 'Dog' case FALSE: result = NOT randexpr; case NULL: result = randexpr ISNULL; } 71

Random Expression Rectification switch (result) { case TRUE: TRUE result = randexpr; NOT(animal = 'Dog') case FALSE: result = NOT randexpr; case NULL: result = randexpr ISNULL; } 72

How do we Generate Queries? SELECT picture, description FROM animal_pictures WHERE NOT(animal = 'Dog') 73

Evaluation

Tested DBMS PostgreSQL 75

Tested DBMS PostgreSQL We tested these (and other DBMS) in a period of 3-4 months 76

DBMS 77

DBMS 78

DBMS 79

Bugs Overview Real Bugs DBMS Fixed Verified SQLite 65 0 MySQL 15 10 PostgreSQL 5 4 Sum 14 85 80

Bugs Overview Real Bugs DBMS Fixed Verified SQLite 65 0 MySQL 15 10 PostgreSQL 5 4 Sum 14 85 99 real bugs : addressed by code or documentation fixes, or verified as bugs 81

Bugs Overview Real Bugs DBMS Fixed Verified SQLite 65 0 MySQL 15 10 PostgreSQL 5 4 Sum 14 85 The SQLite developers quickly responded to all our bug reports → we focused on this DBMS 82

Bugs Overview Real Bugs DBMS Fixed Verified SQLite 65 0 MySQL 15 10 PostgreSQL 5 4 Sum 14 85 All MySQL bug reports were verified quickly 83

Bugs Overview Real Bugs DBMS Fixed Verified SQLite 65 0 MySQL 15 10 PostgreSQL 5 4 Sum 14 85 MySQL’s trunk is not available , and it has a long release cycle 84

Bugs Overview Real Bugs DBMS Fixed Verified SQLite 65 0 MySQL 15 10 PostgreSQL 5 4 Sum 14 85 We found the fewest bugs in PostgreSQL and not all could be easily addressed 85

Oracles Real Bugs DBMS Containment Error SEGFAULT SQLite 46 17 2 MySQL 14 10 1 PostgreSQL 1 7 1 Sum 61 34 4 86

Oracles Real Bugs Containment Oracle DBMS Containment Error SEGFAULT SQLite 46 17 2 MySQL 14 10 1 PostgreSQL 1 7 1 Sum 61 34 4 Our Containment oracle allowed us to detect most errors 87

Result: Bug in SQLite3 Real Bugs Containment CREATE TABLE t0(c1 TEXT PRIMARY KEY) WITHOUT ROWID; Oracle CREATE INDEX i0 ON t0(c1 COLLATE NOCASE); INSERT INTO t0(c1) VALUES ('A'); INSERT INTO t0(c1) VALUES ('a'); 88

Result: Bug in SQLite3 Real Bugs Containment CREATE TABLE t0(c1 TEXT PRIMARY KEY) WITHOUT ROWID; Oracle CREATE INDEX i0 ON t0(c1 COLLATE NOCASE); INSERT INTO t0(c1) VALUES ('A'); INSERT INTO t0(c1) VALUES ('a'); An index is an auxiliary data structure that should not affect the query’s result 89

Result: Bug in SQLite3 Real Bugs Containment CREATE TABLE t0(c1 TEXT PRIMARY KEY) WITHOUT ROWID; Oracle CREATE INDEX i0 ON t0(c1 COLLATE NOCASE); c1 INSERT INTO t0(c1) VALUES ('A'); INSERT INTO t0(c1) VALUES ('a'); 'A' 'a' 90

Result: Bug in SQLite3 Real Bugs Containment CREATE TABLE t0(c1 TEXT PRIMARY KEY) WITHOUT ROWID; Oracle CREATE INDEX i0 ON t0(c1 COLLATE NOCASE); c1 INSERT INTO t0(c1) VALUES ('A'); INSERT INTO t0(c1) VALUES ('a'); 'A' 'a' c1 SELECT * FROM t0; 'A' 91

Result: Bug in SQLite3 Real Bugs Containment CREATE TABLE t0(c1 TEXT PRIMARY KEY) WITHOUT ROWID; Oracle CREATE INDEX i0 ON t0(c1 COLLATE NOCASE); c1 INSERT INTO t0(c1) VALUES ('A'); INSERT INTO t0(c1) VALUES ('a'); 'A' 'a' c1 'A'  SELECT * FROM t0; SQLite failed to fetch 'a' ! 92

Result: Bug in PostgreSQL Real Bugs t0 CREATE TABLE t0(c0 INT PRIMARY KEY, c1 INT); c0 c1 CREATE TABLE t1(c0 INT) INHERITS (t0); Containment Oracle t1 c0 c1 93

Result: Bug in PostgreSQL Real Bugs t0 CREATE TABLE t0(c0 INT PRIMARY KEY, c1 INT); c0 c1 CREATE TABLE t1(c0 INT) INHERITS (t0); Containment Oracle INSERT INTO t0(c0, c1) VALUES(0, 0); 0 0 t1 c0 c1 94

Result: Bug in PostgreSQL Real Bugs t0 CREATE TABLE t0(c0 INT PRIMARY KEY, c1 INT); c0 c1 CREATE TABLE t1(c0 INT) INHERITS (t0); Containment Oracle INSERT INTO t0(c0, c1) VALUES(0, 0); 0 0 INSERT INTO t1(c0, c1) VALUES(0, 1); 0 1 t1 c0 c1 0 1 95

Result: Bug in PostgreSQL Real Bugs t0 CREATE TABLE t0(c0 INT PRIMARY KEY, c1 INT); c0 c1 CREATE TABLE t1(c0 INT) INHERITS (t0); Containment Oracle INSERT INTO t0(c0, c1) VALUES(0, 0); 0 0 INSERT INTO t1(c0, c1) VALUES(0, 1); 0 1 t1 The inheritance relationship c0 c1 causes the row to be inserted 0 1 both in t0 and t1 96

Result: Bug in PostgreSQL Real Bugs t0 CREATE TABLE t0(c0 INT PRIMARY KEY, c1 INT); c0 c1 CREATE TABLE t1(c0 INT) INHERITS (t0); Containment Oracle INSERT INTO t0(c0, c1) VALUES(0, 0); 0 0 INSERT INTO t1(c0, c1) VALUES(0, 1); 0 1 t1 c0 c1 c0 c1 SELECT c0, c1 FROM t0 0 1 GROUP BY c0, c1; 0 0 97

Result: Bug in PostgreSQL Real Bugs t0 CREATE TABLE t0(c0 INT PRIMARY KEY, c1 INT); c0 c1 CREATE TABLE t1(c0 INT) INHERITS (t0); Containment Oracle INSERT INTO t0(c0, c1) VALUES(0, 0); 0 0 INSERT INTO t1(c0, c1) VALUES(0, 1); 0 1 t1 c0 c1 c0 c1 SELECT c0, c1 FROM t0 0 1 GROUP BY c0, c1; 0 0  PostgreSQL failed to fetch the row 0 | 1 98

Result: Bug in MySQL Real Bugs t0 CREATE TABLE t0(c0 TINYINT); Containment c0 INSERT INTO t0(c0) VALUES(NULL); Oracle NULL 99

Result: Bug in MySQL Real Bugs t0 CREATE TABLE t0(c0 TINYINT); Containment c0 INSERT INTO t0(c0) VALUES(NULL); Oracle NULL SELECT * FROM t0 WHERE c0 NOT(t0.c0 <=> 2035382037); FALSE 100