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

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

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/

4ae30d49c8cc07e42d5a871efb9bcfba?s=128

Gregory Kapfhammer

October 02, 2014
Tweet

Transcript

  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
  7. None
  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