Long presentation for "Testing Database Engines via Pivoted Query Synthesis" at OSDI '20

Slide 1

Slide 1 text

Testing Database Engines via Pivoted Query Synthesis Manuel Rigger Zhendong Su ETH Zurich, Switzerland 11/05/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 “it is seems likely that there are over one trillion (1e12) SQLite databases in active use” https://www.sqlite.org/mostdeployed.html

Slide 4

Slide 4 text

4 Database Management Systems (DBMSs) PostgreSQL We found 96 unique bugs in these DBMSs, 78 of which were fixed!

Slide 5

Slide 5 text

5 Goal: Find Logic Bugs Logic bugs: DBMS returns an incorrect result set

Slide 6

Slide 6 text

6 Example: SQLite3 Bug c0 0 1 2 NULL t0 CREATE TABLE t0(c0); CREATE INDEX i0 ON t0(1) WHERE c0 NOT NULL; INSERT INTO t0 (c0) VALUES (0), (1), (2), (NULL); SELECT c0 FROM t0 WHERE t0.c0 IS NOT 1; https://sqlite.org/src/tktview/80256748471a01

Slide 7

Slide 7 text

7 Example: SQLite3 Bug c0 0 1 2 NULL t0 CREATE TABLE t0(c0); CREATE INDEX i0 ON t0(1) WHERE c0 NOT NULL; INSERT INTO t0 (c0) VALUES (0), (1), (2), (NULL); SELECT c0 FROM t0 WHERE t0.c0 IS NOT 1; IS NOT is a “null-safe” comparison operator https://sqlite.org/src/tktview/80256748471a01

Slide 8

Slide 8 text

8 Example: SQLite3 Bug c0 0 1 2 NULL t0 CREATE TABLE t0(c0); CREATE INDEX i0 ON t0(1) WHERE c0 NOT NULL; INSERT INTO t0 (c0) VALUES (0), (1), (2), (NULL); SELECT c0 FROM t0 WHERE t0.c0 IS NOT 1; https://sqlite.org/src/tktview/80256748471a01

Slide 9

Slide 9 text

9 Example: SQLite3 Bug c0 0 1 2 NULL t0 0 CREATE TABLE t0(c0); CREATE INDEX i0 ON t0(1) WHERE c0 NOT NULL; INSERT INTO t0 (c0) VALUES (0), (1), (2), (NULL); SELECT c0 FROM t0 WHERE t0.c0 IS NOT 1; TRUE https://sqlite.org/src/tktview/80256748471a01

Slide 10

Slide 10 text

10 Example: SQLite3 Bug c0 0 1 2 NULL t0 0 CREATE TABLE t0(c0); CREATE INDEX i0 ON t0(1) WHERE c0 NOT NULL; INSERT INTO t0 (c0) VALUES (0), (1), (2), (NULL); SELECT c0 FROM t0 WHERE t0.c0 IS NOT 1; TRUE 0 https://sqlite.org/src/tktview/80256748471a01

Slide 11

Slide 11 text

11 Example: SQLite3 Bug c0 0 1 2 NULL t0 1 CREATE TABLE t0(c0); CREATE INDEX i0 ON t0(1) WHERE c0 NOT NULL; INSERT INTO t0 (c0) VALUES (0), (1), (2), (NULL); SELECT c0 FROM t0 WHERE t0.c0 IS NOT 1; 0 FALSE https://sqlite.org/src/tktview/80256748471a01

Slide 12

Slide 12 text

12 Example: SQLite3 Bug c0 0 1 2 NULL t0 0 2 2 CREATE TABLE t0(c0); CREATE INDEX i0 ON t0(1) WHERE c0 NOT NULL; INSERT INTO t0 (c0) VALUES (0), (1), (2), (NULL); SELECT c0 FROM t0 WHERE t0.c0 IS NOT 1; TRUE https://sqlite.org/src/tktview/80256748471a01

Slide 13

Slide 13 text

13 Example: SQLite3 Bug c0 0 1 2 NULL t0 0 2 NULL NULL CREATE TABLE t0(c0); CREATE INDEX i0 ON t0(1) WHERE c0 NOT NULL; INSERT INTO t0 (c0) VALUES (0), (1), (2), (NULL); SELECT c0 FROM t0 WHERE t0.c0 IS NOT 1; TRUE https://sqlite.org/src/tktview/80256748471a01

Slide 14

Slide 14 text

14 Example: SQLite3 Bug c0 0 1 2 NULL t0 NULL CREATE TABLE t0(c0); CREATE INDEX i0 ON t0(1) WHERE c0 NOT NULL; INSERT INTO t0 (c0) VALUES (0), (1), (2), (NULL); SELECT c0 FROM t0 WHERE t0.c0 IS NOT 1; TRUE 0 2 NULL https://sqlite.org/src/tktview/80256748471a01

Slide 15

Slide 15 text

15 Example: SQLite3 Bug c0 0 1 2 NULL t0 NULL CREATE TABLE t0(c0); CREATE INDEX i0 ON t0(1) WHERE c0 NOT NULL; INSERT INTO t0 (c0) VALUES (0), (1), (2), (NULL); SELECT c0 FROM t0 WHERE t0.c0 IS NOT 1; TRUE 0 2 NULL 0 2 https://sqlite.org/src/tktview/80256748471a01

Slide 16

Slide 16 text

16 Example: SQLite3 Bug c0 0 1 2 NULL t0 NULL CREATE TABLE t0(c0); CREATE INDEX i0 ON t0(1) WHERE c0 NOT NULL; INSERT INTO t0 (c0) VALUES (0), (1), (2), (NULL); SELECT c0 FROM t0 WHERE t0.c0 IS NOT 1; TRUE  NULL was not contained in the result set! 0 2 NULL 0 2 https://sqlite.org/src/tktview/80256748471a01

Slide 17

Slide 17 text

17 Background: Differential Testing PostgreSQL SELECT c0 FROM t0 WHERE t0.c0 IS NOT 1; Massive Stochastic Testing of SQL by Slutz, 1998.

Slide 18

Slide 18 text

18 Background: Differential Testing PostgreSQL RS1 RS2 RS3 SELECT c0 FROM t0 WHERE t0.c0 IS NOT 1; Massive Stochastic Testing of SQL by Slutz, 1998.

Slide 19

Slide 19 text

19 Background: Differential Testing PostgreSQL RS1 RS2 RS3 SELECT c0 FROM t0 WHERE t0.c0 IS NOT 1; Check that all DBMSs compute the same result (RS1 = RS2 = RS3 ) Massive Stochastic Testing of SQL by Slutz, 1998.

Slide 20

Slide 20 text

20 Background: Differential Testing PostgreSQL RS1 RS2 RS3

Slide 21

Slide 21 text

21 Background: Differential Testing PostgreSQL RS1 RS2 RS3

Slide 22

Slide 22 text

22 Background: Differential Testing {0, 2} Syntax error Syntax error PostgreSQL

Slide 23

Slide 23 text

23 Background: Differential Testing CREATE TABLE t0(c0); CREATE INDEX i0 ON t0(1) WHERE c0 NOT NULL; INSERT INTO t0 (c0) VALUES (0), (1), (2), (3), (NULL); SELECT c0 FROM t0 WHERE t0.c0 IS NOT 1;

Slide 24

Slide 24 text

24 Background: Differential Testing CREATE TABLE t0(c0); CREATE INDEX i0 ON t0(1) WHERE c0 NOT NULL; INSERT INTO t0 (c0) VALUES (0), (1), (2), (3), (NULL); SELECT c0 FROM t0 WHERE t0.c0 IS NOT 1; MySQL and PostgreSQL require a data type definition

Slide 25

Slide 25 text

25 Background: Differential Testing CREATE TABLE t0(c0); CREATE INDEX i0 ON t0(1) WHERE c0 NOT NULL; INSERT INTO t0 (c0) VALUES (0), (1), (2), (3), (NULL); SELECT c0 FROM t0 WHERE t0.c0 IS NOT 1; PostgreSQL provides an IS DISTINCT FROM operator, and MySQL a <=> null-safe comparison operator

Slide 26

Slide 26 text

26 Idea: PQS Pivoted Query Synthesis (PQS): Divide-and-conquer approach for testing DBMSs

Slide 27

Slide 27 text

27 PQS Idea c0 0 1 2 NULL t0 CREATE TABLE t0(c0); CREATE INDEX i0 ON t0(1) WHERE c0 NOT NULL; INSERT INTO t0 (c0) VALUES (0), (1), (2), (3), (NULL); SELECT c0 FROM t0 WHERE t0.c0 IS NOT 1; Validate the result set based on one randomly-selected row

Slide 28

Slide 28 text

28 PQS Idea c0 0 1 2 NULL t0 CREATE TABLE t0(c0); CREATE INDEX i0 ON t0(1) WHERE c0 NOT NULL; INSERT INTO t0 (c0) VALUES (0), (1), (2), (3), (NULL); SELECT c0 FROM t0 WHERE t0.c0 IS NOT 1; Pivot row Validate the result set based on one randomly-selected row

Slide 29

Slide 29 text

29 PQS Idea c0 0 1 2 NULL t0 CREATE TABLE t0(c0); CREATE INDEX i0 ON t0(1) WHERE c0 NOT NULL; INSERT INTO t0 (c0) VALUES (0), (1), (2), (3), (NULL); SELECT c0 FROM t0 WHERE t0.c0 IS NOT 1; Generate a query that is guaranteed to at least fetch the pivot row NULL TRUE

Slide 30

Slide 30 text

30 PQS Idea c0 0 1 2 NULL t0 CREATE TABLE t0(c0); CREATE INDEX i0 ON t0(1) WHERE c0 NOT NULL; INSERT INTO t0 (c0) VALUES (0), (1), (2), (3), (NULL); SELECT c0 FROM t0 WHERE t0.c0 IS NOT 1;  If the pivot row is missing from the result set a bug has been detected 0 2

Slide 31

Slide 31 text

31 Approach Randomly generate database Select pivot row Generate query for the pivot row Validate that the pivot row is contained

Slide 32

Slide 32 text

32 Approach Randomly generate database Select pivot row Generate query for the pivot row Validate that the pivot row is contained CREATE TABLE t0(c0); CREATE INDEX i0 ON t0(1) WHERE c0 NOT NULL; INSERT INTO t0 (c0) VALUES (0), (1), (2), (3), (NULL); Statements are heuristically generated based on the DBMS’ SQL dialect

Slide 33

Slide 33 text

33 Approach Randomly generate database Select pivot row Generate query for the pivot row Validate that the pivot row is contained One random row from multiple tables and views

Slide 34

Slide 34 text

34 Approach Randomly generate database Select pivot row Generate query for the pivot row Validate that the pivot row is contained Generate predicatesthat evaluate to TRUE for the pivot row and use them in JOIN and WHERE clauses SELECT c0 FROM t0 WHERE

Slide 35

Slide 35 text

35 Random Expression Generation t0.c0 IS NOT 1; Randomly generate database Select pivot row Generate query for the pivot row Validate that the pivot row is contained IS NOT t0.c0 1

Slide 36

Slide 36 text

36 Random Expression Generation t0.c0 IS NOT 1; We implemented an expression evaluator for each node Randomly generate database Select pivot row Generate query for the pivot row Validate that the pivot row is contained IS NOT t0.c0 1

Slide 37

Slide 37 text

37 Random Expression Generation c0 0 1 2 NULL t0 Evaluate the tree based on the pivot row Randomly generate database Select pivot row Generate query for the pivot row Validate that the pivot row is contained IS NOT t0.c0 1

Slide 38

Slide 38 text

38 Random Expression Generation Column references return the values from the pivot row c0 0 1 2 NULL t0 Randomly generate database Select pivot row Generate query for the pivot row Validate that the pivot row is contained IS NOT t0.c0 1

Slide 39

Slide 39 text

39 Random Expression Generation Column references return the values from the pivot row c0 0 1 2 NULL t0 Randomly generate database Select pivot row Generate query for the pivot row Validate that the pivot row is contained IS NOT t0.c0 1 NULL

Slide 40

Slide 40 text

40 Random Expression Generation Constant nodes return their assigned literal values c0 0 1 2 NULL t0 Randomly generate database Select pivot row Generate query for the pivot row Validate that the pivot row is contained IS NOT t0.c0 1 NULL

Slide 41

Slide 41 text

41 Random Expression Generation Constant nodes return their assigned literal values c0 0 1 2 NULL t0 Randomly generate database Select pivot row Generate query for the pivot row Validate that the pivot row is contained IS NOT t0.c0 1 NULL 1

Slide 42

Slide 42 text

42 Random Expression Generation Compound nodes compute their result based on their children TRUE c0 0 1 2 NULL t0 Randomly generate database Select pivot row Generate query for the pivot row Validate that the pivot row is contained IS NOT t0.c0 1 NULL 1

Slide 43

Slide 43 text

43 Random Expression Generation Compound nodes compute their result based on their children TRUE c0 0 1 2 NULL t0 Randomly generate database Select pivot row Generate query for the pivot row Validate that the pivot row is contained IS NOT t0.c0 1 NULL 1 TRUE

Slide 44

Slide 44 text

44 t0.c0 IS NOT 1; Query Synthesis SELECT c0 c0 FROM t0 WHERE Randomly generate database Select pivot row Generate query for the pivot row Validate that the pivot row is contained

Slide 45

Slide 45 text

45 t0.c0 IS NOT 1; Query Synthesis SELECT c0 c0 FROM t0 WHERE What if the expression does not evaluate to TRUE? Randomly generate database Select pivot row Generate query for the pivot row Validate that the pivot row is contained

Slide 46

Slide 46 text

46 Random Expression Rectification switch (result) { case TRUE: result = randexpr; case FALSE: result = NOT randexpr; case NULL: result = randexpr IS NULL; } Randomly generate database Select pivot row Generate query for the pivot row Validate that the pivot row is contained

Slide 47

Slide 47 text

47 Random Expression Rectification switch (result) { case TRUE: result = randexpr; case FALSE: result = NOT randexpr; case NULL: result = randexpr IS NULL; } Alternatively, we could validate that the pivot row is expectedly not fetched Randomly generate database Select pivot row Generate query for the pivot row Validate that the pivot row is contained

Slide 48

Slide 48 text

48 Random Expression Rectification Randomly generate database Select pivot row Generate query for the pivot row Validate that the pivot row is contained • DISTINCT clauses • ORDER BY clauses • DBMS-specific clauses (e.g., FOR UPDATE)

Slide 49

Slide 49 text

49 Approach SELECT (NULL) INTERSECT SELECT c0 FROM t0 WHERE NULL IS NOT 1; Rely on the DBMS to check whether the row is contained Randomly generate database Select pivot row Generate query for the pivot row Validate that the pivot row is contained

Slide 50

Slide 50 text

50 Approach Randomly generate database Select pivot row Generate query for the pivot row Validate that the pivot row is contained

Slide 51

Slide 51 text

51 Approach Randomly generate database Select pivot row Generate query for the pivot row Validate that the pivot row is contained

Slide 52

Slide 52 text

52 Approach Randomly generate database Select pivot row Generate query for the pivot row Validate that the pivot row is contained We generate 100,000 queries for each generated database

Slide 53

Slide 53 text

53 Implementation https://github.com/sqlancer

Slide 54

Slide 54 text

54 Bugs Overview DBMS Fixed Verified SQLite 64 0 MySQL 17 7 PostgreSQL 5 3

Slide 55

Slide 55 text

55 Bugs Overview DBMS Fixed Verified SQLite 64 0 MySQL 17 7 PostgreSQL 5 3 96 bugs were unique, previously unknown ones

Slide 56

Slide 56 text

56 Bugs Overview The SQLite developers quickly responded to all our bug reports → we focused on this DBMS DBMS Fixed Verified SQLite 64 0 MySQL 17 7 PostgreSQL 5 3

Slide 57

Slide 57 text

57 Oracles DBMS Logic Error Crash SQLite 46 17 2 MySQL 14 10 1 PostgreSQL 1 7 1 61 were logic bugs

Slide 58

Slide 58 text

58 Example: SQLite CREATE TABLE t1(c1, c2, c3, c4, PRIMARY KEY (c4, c3)); INSERT INTO t1(c3) VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (NULL), (1), (0); UPDATE t1 SET c2 = 0; INSERT INTO t1(c1) VALUES (0), (0), (NULL), (0), (0); ANALYZE t1; UPDATE t1 SET c3 = 1; SELECT DISTINCT * FROM t1 WHERE t1.c3 = 1; https://www.sqlite.org/src/tktview?name=ced41c7c7d

Slide 59

Slide 59 text

59 Example: SQLite CREATE TABLE t1(c1, c2, c3, c4, PRIMARY KEY (c4, c3)); INSERT INTO t1(c3) VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (NULL), (1), (0); UPDATE t1 SET c2 = 0; INSERT INTO t1(c1) VALUES (0), (0), (NULL), (0), (0); ANALYZE t1; UPDATE t1 SET c3 = 1; SELECT DISTINCT * FROM t1 WHERE t1.c3 = 1; ANALYZE gathers statistics about tables, which are then used for query planning https://www.sqlite.org/src/tktview?name=ced41c7c7d

Slide 60

Slide 60 text

60 Example: SQLite CREATE TABLE t1(c1, c2, c3, c4, PRIMARY KEY (c4, c3)); INSERT INTO t1(c3) VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (NULL), (1), (0); UPDATE t1 SET c2 = 0; INSERT INTO t1(c1) VALUES (0), (0), (NULL), (0), (0); ANALYZE t1; UPDATE t1 SET c3 = 1; SELECT DISTINCT * FROM t1 WHERE t1.c3 = 1; NULL 0 1 NULL  https://www.sqlite.org/src/tktview?name=ced41c7c7d

Slide 61

Slide 61 text

61 Example: SQLite CREATE TABLE t1(c1, c2, c3, c4, PRIMARY KEY (c4, c3)); INSERT INTO t1(c3) VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (NULL), (1), (0); UPDATE t1 SET c2 = 0; INSERT INTO t1(c1) VALUES (0), (0), (NULL), (0), (0); ANALYZE t1; UPDATE t1 SET c3 = 1; SELECT DISTINCT * FROM t1 WHERE t1.c3 = 1; NULL 0 1 NULL  A bug in the skip-scan optimization caused this logic bug https://www.sqlite.org/src/tktview?name=ced41c7c7d

Slide 62

Slide 62 text

62 Example: SQLite CREATE TABLE t1(c1, c2, c3, c4, PRIMARY KEY (c4, c3)); INSERT INTO t1(c3) VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (NULL), (1), (0); UPDATE t1 SET c2 = 0; INSERT INTO t1(c1) VALUES (0), (0), (NULL), (0), (0); ANALYZE t1; UPDATE t1 SET c3 = 1; SELECT DISTINCT * FROM t1 WHERE t1.c3 = 1; NULL 0 1 NULL 0 NULL 1 NULL NULL NULL 1 NULL https://www.sqlite.org/src/tktview?name=ced41c7c7d ✓

Slide 63

Slide 63 text

63 Example: SQLite CREATE TABLE t1(c1, c2, c3, c4, PRIMARY KEY (c4, c3)); INSERT INTO t1(c3) VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (NULL), (1), (0); UPDATE t1 SET c2 = 0; INSERT INTO t1(c1) VALUES (0), (0), (NULL), (0), (0); ANALYZE t1; UPDATE t1 SET c3 = 1; SELECT DISTINCT * FROM t1 WHERE t1.c3 = 1; NULL 0 1 NULL 0 NULL 1 NULL NULL NULL 1 NULL https://www.sqlite.org/src/tktview?name=ced41c7c7d ✓ The bug was classified as “Severe” and quickly fixed

Slide 64

Slide 64 text

64 Result: Bug in PostgreSQL CREATE TABLE t0(c0 INT PRIMARY KEY, c1 INT); CREATE TABLE t1(c0 INT) INHERITS (t0); INSERT INTO t0(c0, c1) VALUES(0, 0); c0 c1 0 0 c0 c1 t0 t1 https://www.postgresql.org/message-id/CA%2Bu7OA7VLKf_vEr6kLF3MnWSA9LToJYncgpNX2tQ-oWzYCBQAw%40mail.gmail.com

Slide 65

Slide 65 text

65 Result: Bug in PostgreSQL CREATE TABLE t0(c0 INT PRIMARY KEY, c1 INT); CREATE TABLE t1(c0 INT) INHERITS (t0); INSERT INTO t0(c0, c1) VALUES(0, 0); INSERT INTO t1(c0, c1) VALUES(0, 1); c0 c1 0 0 0 1 c0 c1 0 1 t0 t1 https://www.postgresql.org/message-id/CA%2Bu7OA7VLKf_vEr6kLF3MnWSA9LToJYncgpNX2tQ-oWzYCBQAw%40mail.gmail.com

Slide 66

Slide 66 text

66 Result: Bug in PostgreSQL CREATE TABLE t0(c0 INT PRIMARY KEY, c1 INT); CREATE TABLE t1(c0 INT) INHERITS (t0); INSERT INTO t0(c0, c1) VALUES(0, 0); INSERT INTO t1(c0, c1) VALUES(0, 1); c0 c1 0 0 0 1 c0 c1 0 1 t0 t1 The inheritance relationship causes the row to be inserted both in t0 and t1 https://www.postgresql.org/message-id/CA%2Bu7OA7VLKf_vEr6kLF3MnWSA9LToJYncgpNX2tQ-oWzYCBQAw%40mail.gmail.com

Slide 67

Slide 67 text

67 Result: Bug in PostgreSQL CREATE TABLE t0(c0 INT PRIMARY KEY, c1 INT); CREATE TABLE t1(c0 INT) INHERITS (t0); INSERT INTO t0(c0, c1) VALUES(0, 0); INSERT INTO t1(c0, c1) VALUES(0, 1); c0 c1 0 0 0 1 c0 c1 0 1 t0 t1 SELECT c0, c1 FROM t0 GROUP BY c0, c1; 0 0  https://www.postgresql.org/message-id/CA%2Bu7OA7VLKf_vEr6kLF3MnWSA9LToJYncgpNX2tQ-oWzYCBQAw%40mail.gmail.com

Slide 68

Slide 68 text

68 Result: Bug in PostgreSQL CREATE TABLE t0(c0 INT PRIMARY KEY, c1 INT); CREATE TABLE t1(c0 INT) INHERITS (t0); INSERT INTO t0(c0, c1) VALUES(0, 0); INSERT INTO t1(c0, c1) VALUES(0, 1); c0 c1 0 0 0 1 c0 c1 0 1 t0 t1 SELECT c0, c1 FROM t0 GROUP BY c0, c1; 0 0 An optimization incorrectly simplified the GROUP BY clause  https://www.postgresql.org/message-id/CA%2Bu7OA7VLKf_vEr6kLF3MnWSA9LToJYncgpNX2tQ-oWzYCBQAw%40mail.gmail.com

Slide 69

Slide 69 text

69 Result: Bug in PostgreSQL CREATE TABLE t0(c0 INT PRIMARY KEY, c1 INT); CREATE TABLE t1(c0 INT) INHERITS (t0); INSERT INTO t0(c0, c1) VALUES(0, 0); INSERT INTO t1(c0, c1) VALUES(0, 1); c0 c1 0 0 0 1 c0 c1 0 1 t0 t1 SELECT c0, c1 FROM t0 GROUP BY c0, c1; 0 0 0 1 ✓ https://www.postgresql.org/message-id/CA%2Bu7OA7VLKf_vEr6kLF3MnWSA9LToJYncgpNX2tQ-oWzYCBQAw%40mail.gmail.com

Slide 70

Slide 70 text

70 Result: Bug in MySQL c0 1 t0 CREATE TABLE t0(c0 INT); INSERT INTO t0(c0) VALUES (1); SELECT * FROM t0 WHERE 123 != (NOT (NOT 123));  {} https://bugs.mysql.com/bug.php?id=95900

Slide 71

Slide 71 text

71 Result: Bug in MySQL c0 1 t0 CREATE TABLE t0(c0 INT); INSERT INTO t0(c0) VALUES (1); SELECT * FROM t0 WHERE 123 != (NOT (NOT 123));  {} The double negation cannot be removed due to MySQL’s flexible type system https://bugs.mysql.com/bug.php?id=95900

Slide 72

Slide 72 text

72 Result: Bug in MySQL c0 1 t0 CREATE TABLE t0(c0 INT); INSERT INTO t0(c0) VALUES (1); SELECT * FROM t0 WHERE 123 != (NOT (NOT 123)); 0 ✓ https://bugs.mysql.com/bug.php?id=95900

Slide 73

Slide 73 text

73 Oracles DBMS Logic Error Crash SQLite 46 17 2 MySQL 14 10 1 PostgreSQL 1 7 1 Error bugs are due to unexpected (internal) errors

Slide 74

Slide 74 text

74 Example: SQLite3 Bug CREATE TABLE t0(c0, c1 REAL PRIMARY KEY); INSERT INTO t0(c0, c1) VALUES (TRUE, 9223372036854775807), (TRUE, 0); UPDATE t0 SET c0 = NULL; UPDATE OR REPLACE t0 SET c1 = 1; SELECT DISTINCT * FROM t0 WHERE (t0.c0 IS NULL); Database disk image is malformed

Slide 75

Slide 75 text

75 Discussion: Implementation Effort • Literal evaluator • Simpler than PL AST Interpreters → No mutable state • Simpler than query engines → only a single row needs to be considered

Slide 76

Slide 76 text

76 Discussion: Implementation Effort • Literal evaluator • Simpler than PL AST Interpreters → No mutable state • Simpler than query engines → only a single row needs to be considered • Operators are implemented naively • The performance of the DBMS is the bottleneck

Slide 77

Slide 77 text

77 Discussion: Implementation Effort • Literal evaluator • Simpler than PL AST Interpreters → No mutable state • Simpler than query engines → only a single row needs to be considered • Operators are implemented naively • The performance of the DBMS is the bottleneck • Higher implementation effort for functions (e.g. printf) and complex operators

Slide 78

Slide 78 text

78 Discussion: Limitations • Requires understanding of the SQL semantics • Aggregate and window functions • Ordering • Duplicate rows

Slide 79

Slide 79 text

79 Discussion: 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);

Slide 80

Slide 80 text

80 Discussion: 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);

Slide 81

Slide 81 text

81 Discussion: 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);

Slide 82

Slide 82 text

82 Discussion: 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);

Slide 83

Slide 83 text

83 Overview Pivoted Query Synthesis (PQS) Non-optimizing Reference Engine Construction (NoREC) Ternary Logic Query Partitioning (TLP)

Slide 84

Slide 84 text

84 Overview Pivoted Query Synthesis (PQS) Non-optimizing Reference Engine Construction (NoREC) Ternary Logic Query Partitioning (TLP) Detecting optimization bugs by rewriting the query so that it cannot be optimized >150 bugs

Slide 85

Slide 85 text

85 Overview Pivoted Query Synthesis (PQS) Non-optimizing Reference Engine Construction (NoREC) Ternary Logic Query Partitioning (TLP) Partition the query into several partitioning queries, which is applicable to test various features >150 bugs

Slide 86

Slide 86 text

86 SQLancer: Supported DBMSs PostgreSQL

Slide 87

Slide 87 text

87 @RiggerManuel [email protected] Summary Goal: Detect logic bugs PQS randomly selects a pivot row Rectify a random expression Evaluation: Close to 100 bugs in DBMSs