Automatically Repairing SQL Faults

Abstract

SQL is the standard database language, yet SQL statements can be complex and expensive to debug by hand. Automatic program repair techniques have the potential to reduce cost significantly. A previous attempt to repair SQL faults automatically used a decision tree (DT) algorithm that succeeded in some cases, but also generated many patches that passed the automated tests but that were not acceptable to the engineers. This paper proposes a novel fault localization and repair technique to repair faulty SQL statements. It targets faults in two common SQL constructs, JOIN and WHERE. It identifies the fault location and type precisely, and then creates a patch to fix the fault. We implemented this technique in a tool, and evaluated it on five medium to large-scale databases using 825 faulty queries with various complexity and faulty types. Experimental results showed that this technique can identify and repair JOIN faults when the DT approach is infeasible, and repair WHERE faults at about the same rate as the DT approach. Moreover, patches generated by our approach are more acceptable to engineers, and the tool is much faster.