The impact of equivalent, redundant, and quasi mutants on database schema mutation analysis

Interested in learning more about this topic? Visit this web site to read the paper: https://www.gregorykapfhammer.com/research/papers/Wright2014/

1. The Impact of Equivalent, Redundant and Quasi Mutants on Database Schema Mutation Analysis Chris J. Wright Gregory M. Kapfhammer Phil McMinn
2. The Impact of Equivalent, Redundant and Quasi Mutants on Database Schema Mutation Analysis Chris J. Wright Gregory M. Kapfhammer Phil McMinn
3. The Impact of Equivalent, Redundant and Quasi Mutants on Database Schema Mutation Analysis Chris J. Wright Gregory M. Kapfhammer Phil McMinn
4. The Impact of Equivalent, Redundant and Quasi Mutants on Database Schema Mutation Analysis Chris J. Wright Gregory M. Kapfhammer Phil McMinn
5. The Impact of Equivalent, Redundant and Quasi Mutants on Database Schema Mutation Analysis Chris J. Wright Gregory M. Kapfhammer Phil McMinn
6. The Impact of Equivalent, Redundant and Quasi Mutants on Database Schema Mutation Analysis Chris J. Wright Gregory M. Kapfhammer Phil McMinn
8. Database Schema
9. Database Schema 1 CREATE TABLE T ( 2 A CHAR, B CHAR, C CHAR, 3 CONSTRAINT UniqueOnColsAandB UNIQUE (A, B) 4 ); 5 6 CREATE TABLE S ( 7 X CHAR, Y CHAR, Z CHAR, 8 CONSTRAINT RefToColsAandB FOREIGN KEY (X, Y) 9 REFERENCES T (A, B) 10 );
10. Database Schema 1 CREATE TABLE T ( 2 A CHAR, B CHAR, C CHAR, 3 CONSTRAINT UniqueOnColsAandB UNIQUE (A, B) 4 ); 5 6 CREATE TABLE S ( 7 X CHAR, Y CHAR, Z CHAR, 8 CONSTRAINT RefToColsAandB FOREIGN KEY (X, Y) 9 REFERENCES T (A, B) 10 ); Tables
11. Database Schema 1 CREATE TABLE T ( 2 A CHAR, B CHAR, C CHAR, 3 CONSTRAINT UniqueOnColsAandB UNIQUE (A, B) 4 ); 5 6 CREATE TABLE S ( 7 X CHAR, Y CHAR, Z CHAR, 8 CONSTRAINT RefToColsAandB FOREIGN KEY (X, Y) 9 REFERENCES T (A, B) 10 ); Columns
12. Database Schema 1 CREATE TABLE T ( 2 A CHAR, B CHAR, C CHAR, 3 CONSTRAINT UniqueOnColsAandB UNIQUE (A, B) 4 ); 5 6 CREATE TABLE S ( 7 X CHAR, Y CHAR, Z CHAR, 8 CONSTRAINT RefToColsAandB FOREIGN KEY (X, Y) 9 REFERENCES T (A, B) 10 ); Constraints
13. Why Test a Database Schema?
14. Why Test a Database Schema? Database Schema
15. Why Test a Database Schema? DBMS Database Schema
16. Why Test a Database Schema? DBMS Database Schema
17. Why Test a Database Schema? DBMS Application Web Server Third Party Database Schema
18. Why Test a Database Schema? DBMS Application Web Server Third Party Database Schema ✗ ✗ ✗ ✗
19. How to Test a Database Schema
20. How to Test a Database Schema • Generate test data – SQL INSERT statements
21. How to Test a Database Schema • Generate test data – SQL INSERT statements INSERT INTO T(a, b) VALUES('a', 'b');
22. How to Test a Database Schema • Generate test data – SQL INSERT statements INSERT INTO T(a, b) VALUES('a', 'b');
23. How to Test a Database Schema • Generate test data – SQL • Execute the data against the database INSERT INTO T(a, b) VALUES('a', 'b'); DBMS
24. How to Test a Database Schema • Generate test data – SQL • Execute the data against the database • Examine the acceptance of statements INSERT INTO T(a, b) VALUES('a', 'b'); DBMS ✗ ✓/
25. Mutation Analysis
26. Mutation Analysis Application
27. Mutation Analysis Application Mutation Operators
28. Mutation Analysis Application Mutants Mutation Operators
29. Mutation Analysis Application Mutants Mutation Operators Test suite
30. Mutation Analysis Application Mutants Mutation Operators Test suite Test results
31. Mutation Analysis Application Mutants Mutation Operators Test suite Test suite Test results
32. Mutation Analysis Application Mutants Mutation Operators Test suite Test suite Test results Test results
33. Mutation Analysis Application Mutants Mutation Operators Test suite Test suite Test results Test results Comparison
34. Mutation Analysis Application Mutants Mutation Operators Test suite Test suite Test results Test results Comparison Mutation Score
35. Mutation Analysis Application Mutants Mutation Operators Test suite Test suite Test results Test results Comparison Mutation Score
36. Database Schema Mutation Operators
37. Database Schema Mutation Operators Primary Key Foreign Key Unique Not Null Check
38. Database Schema Mutation Operators Primary Key Foreign Key Unique Not Null Check ×
39. Database Schema Mutation Operators Primary Key Foreign Key Unique Not Null Check Column Addition ×
40. Database Schema Mutation Operators Primary Key Foreign Key Unique Not Null Check Column Addition Column Removal ×
41. Database Schema Mutation Operators Primary Key Foreign Key Unique Not Null Check Column Addition Column Removal Column Exchange ×
42. Database Schema Mutation Operators
43. Database Schema Mutation Operators Primary Key Column Addition
44. Database Schema Mutation Operators Primary Key Column Addition 1 CREATE TABLE T ( 2 A CHAR, B CHAR, 3 PRIMARY KEY (A) 4 );
45. Database Schema Mutation Operators Primary Key Column Addition 1 CREATE TABLE T ( 2 A CHAR, B CHAR, 3 PRIMARY KEY (A) 4 ); 1 CREATE TABLE T ( 2 A CHAR, B CHAR, 3 PRIMARY KEY (A, B) 4 );
46. Database Schema Mutation Operators Primary Key Column Exchange 1 CREATE TABLE T ( 2 A CHAR, B CHAR, 3 PRIMARY KEY (A) 4 ); 1 CREATE TABLE T ( 2 A CHAR, B CHAR, 3 PRIMARY KEY (B) 4 );
47. Database Schema Mutation Operators Primary Key Column Removal 1 CREATE TABLE T ( 2 A CHAR, B CHAR, 3 PRIMARY KEY (A) 4 ); 1 CREATE TABLE T ( 2 A CHAR, B CHAR, 3 4 );
48. Mutation Analysis – Challenges
49. Mutation Analysis – Challenges • Special classes of mutants
50. Mutation Analysis – Challenges • Special classes of mutants • Equivalent
51. Mutation Analysis – Challenges • Special classes of mutants • Equivalent • Redundant
52. Mutation Analysis – Challenges • Special classes of mutants • Equivalent • Redundant • Quasi-mutants
53. Mutation Analysis – Challenges • Special classes of mutants • Equivalent • Redundant • Quasi-mutants •
54. Equivalent Mutants
55. Equivalent Mutants • Functionally identical to non-mutant
56. Equivalent Mutants • Functionally identical to non-mutant • …but syntactically different
57. Equivalent Mutants • Functionally identical to non-mutant • …but syntactically different • Cannot be ‘killed’
58. Equivalent Mutants • Functionally identical to non-mutant • …but syntactically different • Cannot be ‘killed’ • Artificially decrease mutation score
59. Equivalent Mutants • Functionally identical to non-mutant • …but syntactically different • Cannot be ‘killed’ • Artificially decrease mutation score •
60. Equivalent Mutants
61. Equivalent Mutants 1 CREATE TABLE T ( 2 A CHAR, 3 PRIMARY KEY (A) 4 ); Original:
62. Equivalent Mutants 1 CREATE TABLE T ( 2 A CHAR, 3 PRIMARY KEY (A) 4 ); 1 CREATE TABLE T ( 2 A CHAR NOT NULL, 3 PRIMARY KEY (A) 4 ); Original: Mutant:
63. Redundant Mutants
64. Redundant Mutants • Functionally identical to another mutant
65. Redundant Mutants • Functionally identical to • …but syntactically different
66. Redundant Mutants • Functionally identical to • …but syntactically different • May be ‘killed’
67. Redundant Mutants • Functionally identical to • …but syntactically different • May be ‘killed’ • Artificially alters mutation score
68. Redundant Mutants • Functionally identical to • …but syntactically different • May be ‘killed’ • Artificially alters mutation score • Reduces efficiency
69. Redundant Mutants • Functionally identical to • …but syntactically different • May be ‘killed’ • Artificially alters mutation score • Reduces efficiency •
70. Types of Equivalence
71. Types of Equivalence • Structural
72. Types of Equivalence • Structural • Functionally irrelevant syntactic differences
73. Types of Equivalence • Structural • Functionally irrelevant syntactic differences
74. Types of Equivalence • Structural • Functionally irrelevant syntactic differences • Behavioural
75. Types of Equivalence • Structural • Functionally irrelevant syntactic differences • Behavioural • Overlap within SQL features
76. Types of Equivalence • Structural • Functionally irrelevant syntactic differences • Behavioural • Overlap within SQL features •
77. Behavioural Equivalence Patterns
78. Behavioural Equivalence Patterns • NOT NULL in CHECK constraints • NOT NULL ≅ CHECK(… IS NOT NULL)
79. Behavioural Equivalence Patterns • NOT NULL in CHECK constraints • NOT NULL ≅ CHECK(… IS NOT NULL) 1 CREATE TABLE T ( 2 A CHAR NOT NULL, 3 ); 1 CREATE TABLE T ( 2 A CHAR, 3 CHECK(A IS NOT NULL) 4 );
80. Behavioural Equivalence Patterns
81. Behavioural Equivalence Patterns • NOT NULL on PRIMARY KEY columns
82. Behavioural Equivalence Patterns • NOT NULL on PRIMARY KEY columns • Implicit NOT NULL on PRIMARY KEY
83. Behavioural Equivalence Patterns • NOT NULL on PRIMARY KEY columns • Implicit NOT NULL on PRIMARY KEY • (Only PostgreSQL and HyperSQL)
84. Behavioural Equivalence Patterns • NOT NULL on PRIMARY KEY columns
85. Behavioural Equivalence Patterns • NOT NULL on PRIMARY KEY columns 1 CREATE TABLE T ( 2 A CHAR, 3 PRIMARY KEY (A) 4 ); 1 CREATE TABLE T ( 2 A CHAR NOT NULL, 3 PRIMARY KEY (A) 4 );
86. Behavioural Equivalence Patterns
87. Behavioural Equivalence Patterns • UNIQUE and PRIMARY KEY with shared columns
88. Behavioural Equivalence Patterns • UNIQUE and PRIMARY KEY with shared columns 1 CREATE TABLE T ( 2 A CHAR, 3 PRIMARY KEY (A) 4 ); 1 CREATE TABLE T ( 2 A CHAR, 3 PRIMARY KEY (A), 4 UNIQUE (A) 5 );
89. Quasi-mutants
90. Quasi-mutants • Operators produce DBMS-agnostic mutants
91. Quasi-mutants • Operators produce DBMS-agnostic mutants • Some DBMSs have implicit constraints
92. Quasi-mutants • Operators produce DBMS-agnostic mutants • Some DBMSs have implicit constraints • Valid for some DBMSs, invalid for others HyperSQL PostgreSQL SQLite ✓ ✗ ✗
93. Quasi-mutants • Operators produce DBMS-agnostic mutants • Some DBMSs have implicit constraints • Valid for some DBMSs, invalid for others • HyperSQL PostgreSQL SQLite ✓ ✗ ✗
94. Quasi-mutants SQLite ✓ PostgreSQL ✗ HyperSQL ✗
95. Quasi-mutants • Cannot adversely affect mutation score SQLite ✓ PostgreSQL ✗ HyperSQL ✗
96. Quasi-mutants • Cannot adversely affect mutation score • …but may preclude some optimisations SQLite ✓ PostgreSQL ✗ HyperSQL ✗
97. Quasi-mutants • Cannot adversely affect mutation score • …but may preclude some optimisations • Remove when DBMS will ‘reject’ them SQLite ✓ PostgreSQL ✗ HyperSQL ✗
98. Types of Quasi-mutants
99. Types of Quasi-mutants • Representative example
100. Types of Quasi-mutants • Representative example • DBMS: PostgreSQL, HyperSQL
101. Types of Quasi-mutants • Representative example • DBMS: PostgreSQL, HyperSQL • ∀ FK(reference columns) ∃ 
 (PK(reference columns) ∨ 
 Unique(reference columns))
102. Types of Quasi-mutants • Representative example • DBMS: PostgreSQL, HyperSQL • ∀ FK(reference columns) ∃ 
 (PK(reference columns) ∨ 
 Unique(reference columns))
103. Types of Quasi-mutants • Representative example • DBMS: PostgreSQL, HyperSQL • ∀ FK(reference columns) ∃ 
 (PK(reference columns) ∨ 
 Unique(reference columns))
104. Types of Quasi-mutants • Representative example • DBMS: PostgreSQL, HyperSQL • ∀ FK(reference columns) ∃ 
 (PK(reference columns) ∨ 
 Unique(reference columns))
105. Types of Quasi-mutants • Representative example • DBMS: PostgreSQL, HyperSQL • ∀ FK(reference columns) ∃ 
 (PK(reference columns) ∨ 
 Unique(reference columns))
106. Types of Quasi-mutants • Representative example • DBMS: PostgreSQL, HyperSQL • ∀ (PK(reference columns) Unique(reference •
107. Detecting Quasi-mutants
108. Detecting Quasi-mutants • Submit to DBMS
109. Detecting Quasi-mutants • Submit to DBMS • 100% accurate
110. Detecting Quasi-mutants • Submit to DBMS • 100% accurate • Convert representation to SQL, submit to database, inspect response
111. Detecting Quasi-mutants • Submit to DBMS • 100% accurate • Convert representation to SQL, submit to database, inspect response • Analyse statically
112. Detecting Quasi-mutants • Submit to DBMS • 100% accurate • Convert representation to SQL, submit to database, inspect response • Analyse statically • Operates directly on representation
113. Detecting Quasi-mutants • Submit to DBMS • 100% accurate • Convert representation to SQL, submit to database, inspect response • Analyse statically • Operates directly on representation • DBMS-specific implementation
114. Empirical Study
115. Empirical Study 1. Quasi-mutant detection – DBMS v Static Analysis
116. Empirical Study 1. Quasi-mutant detection – DBMS v Static Analysis 2. Equivalent, Redundant and Quasi-mutant removal – Efficiency?
117. Empirical Study 1. Quasi-mutant detection – DBMS v Static Analysis 2. Equivalent, Redundant and Quasi-mutant removal – Efficiency? 3. Equivalent, Redundant and Quasi-mutant removal – Effectiveness?
118. Empirical Study
119. Empirical Study • 16 schemas
120. Empirical Study • 16 schemas • 2 DBMSs – PostgreSQL, HyperSQL
121. Empirical Study • 16 schemas • 2 DBMSs – PostgreSQL, HyperSQL • 15 repeat trials
122. Empirical Study • 16 schemas • 2 DBMSs – PostgreSQL, HyperSQL • 15 repeat trials •
123. Empirical Study – Quasi-mutants
124. Empirical Study – Quasi-mutants • 5 conditions:
125. Empirical Study – Quasi-mutants • 5 conditions: • Postgres (with/without transactions)
126. Empirical Study – Quasi-mutants • 5 conditions: • Postgres (with/without transactions) • HyperSQL (with/without transactions)
127. Empirical Study – Quasi-mutants • 5 conditions: • Postgres (with/without transactions) • HyperSQL (with/without transactions) • Static analysis
128. Empirical Study – Quasi-mutants • 5 conditions: • Postgres (with/without transactions) • HyperSQL (with/without transactions) • Static analysis •
129. Empirical Study – Quasi-mutants • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • 0.00 0.25 0.50 0.75 1.00 1.25 HyperSQL HyperSQL Trans. Postgres Postgres Trans. Static Approach Scaled Time Taken
130. Empirical Study – Mutant Removal
131. Empirical Study – Mutant Removal • 2 conditions – with and without removal
132. Empirical Study – Mutant Removal • 2 conditions – with and without removal • 2 metrics –
133. Empirical Study – Mutant Removal • 2 conditions – with and without removal • 2 metrics – • Time taken for mutation analysis
134. Empirical Study – Mutant Removal • 2 conditions – with and without removal • 2 metrics – • Time taken for mutation analysis • Mutation score
135. Empirical Study – Mutant Removal
136. • HyperSQL – Time saved • Best case: 718ms (23.05%) • Worst case: -824ms (-9.71%) Empirical Study – Mutant Removal
137. Empirical Study – Mutant Removal
138. • HyperSQL – Time saved Empirical Study – Mutant Removal
139. • HyperSQL – Time saved • 9/16 mean time decrease (p < 0.05) Empirical Study – Mutant Removal
140. • HyperSQL – Time saved • 9/16 mean time decrease ( • 7/16 mean time increase (p < 0.05) Empirical Study – Mutant Removal
141. • HyperSQL – Time saved • 9/16 mean time decrease ( • 7/16 mean time increase ( • Overall, decrease (1.6% mean, 1.4% median) Empirical Study – Mutant Removal
142. Empirical Study – Mutant Removal
143. • PostgreSQL – Time saved • Best case: 317,208ms (33.71%) • Worst case: -3,086ms, (-0.33%) Empirical Study – Mutant Removal
144. Empirical Study – Mutant Removal
145. • PostgreSQL – Time saved Empirical Study – Mutant Removal
146. • PostgreSQL – Time saved • 14/16 mean time decrease (p < 0.05) Empirical Study – Mutant Removal
147. • PostgreSQL – Time saved • 14/16 mean time decrease ( • 2/16 mean time increase (p < 0.05) Empirical Study – Mutant Removal
148. • PostgreSQL – Time saved • 14/16 mean time decrease ( • 2/16 mean time increase ( • Overall, decrease (12.7% mean, 11.8% median) Empirical Study – Mutant Removal
149. Empirical Study – Mutant Removal DBMS Time saved (ms) Median Mean
150. Empirical Study – Mutant Removal DBMS Time saved (ms) Median Mean HyperSQL 36.2 7.5
151. Empirical Study – Mutant Removal DBMS Time saved (ms) Median Mean HyperSQL 36.2 7.5 Postgres 8,071 50,880
152. Empirical Study – Mutant Removal DBMS Time saved (ms) Median Mean HyperSQL 36.2 7.5 Postgres 8,071 50,880 Both 229.9 25,450
153. Empirical Study – Mutant Removal DBMS Time saved (%) Median Mean HyperSQL 1.4 1.6 Postgres 12.7 11.8 Both 4.7 6.7
154. Empirical Study – Mutant Removal
155. • HyperSQL – Mutation score • 75% Increased • 44% Adequate • 25% No change Empirical Study – Mutant Removal
156. • PostgreSQL – Mutation score • 75% Increased • 44% Adequate • 25% No change Empirical Study – Mutant Removal
157. Empirical Study – Mutant Removal DBMS Scores changed (%) Increased (adequate) No change HyperSQL 75 (44) 25 Postgres 75 (44) 25 Both 75 (44) 25
158. Conclusion 1. Quasi-mutant detection – DBMS v Static Analysis 2. Equivalent, Redundant and Quasi-mutant removal – Efficiency? 3. Equivalent, Redundant and Quasi-mutant removal – Effectiveness?
159. Conclusion 1. Quasi-mutant detection – DBMS v Static Analysis 2. Equivalent, Redundant and Quasi-mutant removal – Efficiency? 3. Equivalent, Redundant and Quasi-mutant removal – Effectiveness?
160. Conclusion 1. Quasi-mutant detection – DBMS v Static Analysis 2. Equivalent, Redundant and Quasi-mutant removal – Efficiency? 3. Equivalent, Redundant and Quasi-mutant removal – Effectiveness?
161. Conclusion 1. Quasi-mutant detection – Improved efficiency 2. Equivalent, Redundant and Quasi-mutant removal – Improved efficiency 3. Equivalent, Redundant and Quasi-mutant removal – Improved effectiveness
162. Conclusion 1. Quasi-mutant detection – Improved efficiency 2. Equivalent, Redundant and Quasi-mutant removal – Improved efficiency 3. Equivalent, Redundant and Quasi-mutant removal – Improved effectiveness Chris J. Wright – aca08cw@sheffield.ac.uk