Understanding and Detecting SQL Function Bugs: Using Simple Boundary Arguments to Trigger Hundreds of DBMS Bugs

Built-in SQL functions are crucial in Database Management Systems (DBMSs), supporting various operations and computations across multiple data types. They are essential for querying, data transformation, and aggregation. Despite their importance, the bugs in SQL functions have caused widespread problems in the real world, from system failures to arbitrary code execution. However, the understanding of the bug characteristics is limited. More importantly, conventional function testing methods struggle to generate semantically correct SQL test cases, while DBMS testing efforts are hard to measure built-in SQL functions.

This paper presents a comprehensive study of 318 built-in SQL function bugs, shedding light on their characteristics and root causes. Our investigation reveals that 87.4% of these bugs were caused by improper handling of boundary values of arguments. The boundary values of arguments come from three sources: literal values, type castings, and nested functions. By studying the bugs from three sources, we summarized 10 SQL patterns of bug-inducing queries. Moreover, we designed Soft, a testing tool based on the patterns to test seven widely used DBMSs, including PostgreSQL, MySQL, and ClickHouse. Soft discovered and confirmed 132 previously unknown SQL function bugs. The DBMS vendors took these bugs seriously and fixed 97 bugs in three days. For example, the CTO of ClickHouse commented on one bug: "We must fix it immediately or get rid of this function."