Pinolo: Detecting Logical Bugs in Database Management Systems with Approximate Query Synthesis


Zongyin Hao and Quanfeng Huang, School of Informatics, Xiamen University; Chengpeng Wang, The Hong Kong University of Science and Technology; Jianfeng Wang, University of Southern California; Yushan Zhang, Tencent Inc.; Rongxin Wu, School of Informatics, Xiamen University; Charles Zhang, The Hong Kong University of Science and Technology


DBMSs (Database Management Systems) are essential in modern enterprise software. Thus, ensuring the correctness of DBMSs is critical for enterprise applications. Among various kinds of bugs, logical bugs, which make a DBMS return an incorrect result set for a given SQL query, are the most challenging for detection since they typically do not result in apparent manifestations (e.g., crashes) and are likely to go unnoticed by users. The key challenge of detecting logical bugs is the test oracle problem, i.e., how to automatically characterize the expected results for a given query. The state-of-theart approaches focus on generating the equivalent forms of queries via the customized rules, which rewrite a seed query to achieve the equivalent transformation. This dramatically limits the forms of SQL queries fed to the DBMS and thus leads to the under-reporting of many deeply-hidden logical bugs. In this paper, we propose a novel approach, PINOLO, to constructing a test oracle for logical bugs. Instead of generating the equivalent mutants of a seed query, our idea is to synthesize the queries that theoretically should return a superset or a subset of the result set of the seed query, forming the over-approximations or under-approximations of the seed query. A logical bug is detected if the result set returned by our synthesized query does not follow the expected approximation relation. We implemented our idea as a DBMS testing system and evaluated it on four widely-used DBMSs: MySQL, MariaDB, TiDB, and OceanBase. By the time of writing, PINOLO has found 41 unique logical bugs in these DBMSs, 39 of which have been confirmed by developers.

Open Access Media

USENIX is committed to Open Access to the research presented at our events. Papers and proceedings are freely available to everyone once the event begins. Any video, audio, and/or slides that are posted after the event are also free and open to everyone. Support USENIX and our commitment to Open Access.

This content is available to:

@inproceedings {288764,
author = {Zongyin Hao and Quanfeng Huang and Chengpeng Wang and Jianfeng Wang and Yushan Zhang and Rongxin Wu and Charles Zhang},
title = {Pinolo: Detecting Logical Bugs in Database Management Systems with Approximate Query Synthesis},
booktitle = {2023 USENIX Annual Technical Conference (USENIX ATC 23)},
year = {2023},
isbn = {978-1-939133-35-9},
address = {Boston, MA},
pages = {345--358},
url = {},
publisher = {USENIX Association},
month = jul