Abstract
Logic bugs in Database Management Systems (DBMSs) are bugs that cause an incorrect result for a given query, for example, by omitting a row that should be fetched. These bugs are critical, since they are likely to go unnoticed by users. We propose Query Partitioning, a general and effective approach for finding logic bugs in DBMSs. The core idea of Query Partitioning is to, starting from a given original query, derive multiple, more complex queries (called partitioning queries), each of which computes a partition of the result. The individual partitions are then composed to compute a result set that must be equivalent to the original query's result set. A bug in the DBMS is detected when these result sets differ. Our intuition is that due to the increased complexity, the partitioning queries are more likely to stress the DBMS and trigger a logic bug than the original query. As a concrete instance of a partitioning strategy, we propose Ternary Logic Partitioning (TLP), which is based on the observation that a boolean predicate p can either evaluate to TRUE, FALSE, or NULL. Accordingly, a query can be decomposed into three partitioning queries, each of which computes its result on rows or intermediate results for which p, NOT p, and p IS NULL hold. This technique is versatile, and can be used to test WHERE, GROUP BY, as well as HAVING clauses, aggregate functions, and DISTINCT queries. As part of an extensive testing campaign, we found 175 bugs in widely-used DBMSs such as MySQL, TiDB, SQLite, and CockroachDB, 125 of which have been fixed. Notably, 77 of these were logic bugs, while the remaining were error and crash bugs. We expect that the effectiveness and wide applicability of Query Partitioning will lead to its broad adoption in practice, and the formulation of additional partitioning strategies.
Supplemental Material
- Shadi Abdul Khalek and Sarfraz Khurshid. 2010. Automated SQL Query Generation for Systematic Testing of Database Engines. In Proceedings of the IEEE/ACM International Conference on Automated Software Engineering (Antwerp, Belgium) (ASE '10). ACM, New York, NY, USA, 329-332. https://doi.org/10.1145/1858996.1859063 Google Scholar
Digital Library
- M. M. Astrahan, M. W. Blasgen, D. D. Chamberlin, K. P. Eswaran, J. N. Gray, P. P. Grifiths, W. F. King, R. A. Lorie, P. R. McJones, J. W. Mehl, G. R. Putzolu, I. L. Traiger, B. W. Wade, and V. Watson. 1976. System R : Relational Approach to Database Management. ACM Trans. Database Syst. 1, 2 ( June 1976 ), 97-137. https://doi.org/10.1145/320455.320457 Google Scholar
Digital Library
- Hardik Bati, Leo Giakoumakis, Steve Herbert, and Aleksandras Surna. 2007. A Genetic Approach for Random Testing of Database Systems. In Proceedings of the 33rd International Conference on Very Large Data Bases (Vienna, Austria) ( VLDB '07). VLDB Endowment, 1243-1251.Google Scholar
- Carsten Binnig, Donald Kossmann, and Eric Lo. 2007a. Reverse Query Processing. Proceedings-International Conference on Data Engineering, 506-515. https://doi.org/10.1109/ICDE. 2007.367896 Google Scholar
Cross Ref
- Carsten Binnig, Donald Kossmann, Eric Lo, and M. Tamer Özsu. 2007b. QAGen: Generating Query-Aware Test Databases. In Proceedings of the 2007 ACM SIGMOD International Conference on Management of Data (Beijing, China) ( SIGMOD '07). Association for Computing Machinery, New York, NY, USA, 341-352. https://doi.org/10.1145/1247480.1247520 Google Scholar
Digital Library
- Robert Brummayer and Armin Biere. 2009. Fuzzing and Delta-Debugging SMT Solvers. In Proceedings of the 7th International Workshop on Satisfiability Modulo Theories (Montreal, Canada) ( SMT '09). Association for Computing Machinery, New York, NY, USA, 1-5. https://doi.org/10.1145/1670412.1670413 Google Scholar
Digital Library
- Nicolas Bruno and Surajit Chaudhuri. 2005. Flexible Database Generators. In Proceedings of the 31st International Conference on Very Large Data Bases (Trondheim, Norway) (VLDB '05). VLDB Endowment, 1097-1107.Google Scholar
Digital Library
- Nicolas Bruno, Surajit Chaudhuri, and Dilys Thomas. 2006. Generating Queries with Cardinality Constraints for DBMS Testing. IEEE Trans. on Knowl. and Data Eng. 18, 12 (Dec. 2006 ), 1721-1725. https://doi.org/10.1109/TKDE. 2006.190 Google Scholar
Digital Library
- Surajit Chaudhuri. 1998. An Overview of Query Optimization in Relational Systems. In Proceedings of the Seventeenth ACM SIGACT-SIGMOD-SIGART Symposium on Principles of Database Systems (Seatle, Washington, USA) ( PODS '98). Association for Computing Machinery, New York, NY, USA, 34-43. https://doi.org/10.1145/275487.275492 Google Scholar
Digital Library
- Tsong Y Chen, Shing C Cheung, and Shiu Ming Yiu. 1998. Metamorphic testing: a new approach for generating next test cases. Technical Report. Technical Report HKUST-CS98-01, Department of Computer Science, Hong Kong.Google Scholar
- Tsong Yueh Chen, Fei-Ching Kuo, Huai Liu, Pak-Lok Poon, Dave Towey, T. H. Tse, and Zhi Quan Zhou. 2018. Metamorphic Testing: A Review of Challenges and Opportunities. ACM Comput. Surv. 51, 1, Article 4 ( Jan. 2018 ), 27 pages. https: //doi.org/10.1145/3143561 Google Scholar
Digital Library
- Yang Chen, Alex Groce, Chaoqiang Zhang, Weng-Keen Wong, Xiaoli Fern, Eric Eide, and John Regehr. 2013. Taming Compiler Fuzzers. In Proceedings of the 34th ACM SIGPLAN Conference on Programming Language Design and Implementation (Seatle, Washington, USA) ( PLDI '13). Association for Computing Machinery, New York, NY, USA, 197-208. https://doi.org/10.1145/2491956.2462173 Google Scholar
Digital Library
- E. F. Codd. 1970. A Relational Model of Data for Large Shared Data Banks. Commun. ACM 13, 6 ( June 1970 ), 377-387. https://doi.org/10.1145/362384.362685 Google Scholar
Digital Library
- Sara Cohen. 2006. User-Defined Aggregate Functions: Bridging Theory and Practice. In Proceedings of the 2006 ACM SIGMOD International Conference on Management of Data (Chicago, IL, USA) ( SIGMOD '06). Association for Computing Machinery, New York, NY, USA, 49-60. https://doi.org/10.1145/1142473.1142480 Google Scholar
Digital Library
- Will Dietz, Peng Li, John Regehr, and Vikram Adve. 2012. Understanding Integer Overflow in C/C++. In Proceedings of the 34th International Conference on Software Engineering (Zurich, Switzerland) (ICSE '12). IEEE Press, 760-770.Google Scholar
Digital Library
- Alastair F. Donaldson, Hugues Evrard, Andrei Lascu, and Paul Thomson. 2017. Automated Testing of Graphics Shader Compilers. Proc. ACM Program. Lang. 1, OOPSLA, Article 93 (Oct. 2017 ), 29 pages. https://doi.org/10.1145/3133917 Google Scholar
Digital Library
- Goetz Graefe. 1993. Query Evaluation Techniques for Large Databases. ACM Comput. Surv. 25, 2 ( June 1993 ), 73-169. https://doi.org/10.1145/152610.152611 Google Scholar
Digital Library
- Goetz Graefe. 2011. Modern B-Tree Techniques. Found. Trends Databases 3, 4 (April 2011 ), 203-402. https://doi.org/10. 1561/1900000028 Google Scholar
Digital Library
- Jim Gray, Prakash Sundaresan, Susanne Englert, Ken Baclawski, and Peter J. Weinberger. 1994. Quickly Generating BillionRecord Synthetic Databases. SIGMOD Rec. 23, 2 (May 1994 ), 243-252. https://doi.org/10.1145/191843.191886 Google Scholar
Digital Library
- Zhongxian Gu, Mohamed A. Soliman, and Florian M. Waas. 2012. Testing the Accuracy of Query Optimizers. In Proceedings of the Fifth International Workshop on Testing Database Systems (Scotsdale, Arizona) ( DBTest '12). ACM, New York, NY, USA, Article 11, 6 pages. https://doi.org/10.1145/2304510.2304525 Google Scholar
Digital Library
- Paolo Guagliardo and Leonid Libkin. 2017. A Formal Semantics of SQL Queries, Its Validation, and Applications. Proc. VLDB Endow. 11, 1 (Sept. 2017 ), 27-39. https://doi.org/10.14778/3151113.3151116 Google Scholar
Digital Library
- L. M. Haas, J. C. Freytag, G. M. Lohman, and H. Pirahesh. 1989. Extensible Query Processing in Starburst. In Proceedings of the 1989 ACM SIGMOD International Conference on Management of Data (Portland, Oregon, USA) ( SIGMOD '89). Association for Computing Machinery, New York, NY, USA, 377-388. https://doi.org/10.1145/67544.66962 Google Scholar
Digital Library
- Pinjia He, Clara Meister, and Zhendong Su. 2020. Structure-Invariant Testing for Machine Translation. In Proceedings of the ACM/IEEE 42nd International Conference on Software Engineering (Seoul, South Korea) (ICSE '20). Association for Computing Machinery, New York, NY, USA, 961-973. https://doi.org/10.1145/3377811.3380339 Google Scholar
Digital Library
- Kenneth Houkjaer, Kristian Torp, and Rico Wind. 2006. Simple and Realistic Data Generation. In Proceedings of the 32nd International Conference on Very Large Data Bases (Seoul, Korea) ( VLDB '06). VLDB Endowment, 1243-1246.Google Scholar
- Dongxu Huang, Qi Liu, Qiu Cui, Zhuhe Fang, Xiaoyu Ma, Fei Xu, Li Shen, Liu Tang, Yuxing Zhou, Menglong Huang, Wan Wei, Cong Liu, Jian Zhang, Jianjun Li, Xuelian Wu, Lingyu Song, Ruoxi Sun, Shuaipeng Yu, Lei Zhao, Nicholas Cameron, Liquan Pei, and Xin Tang. 2020. TiDB: A Raft-Based HTAP Database. Proc. VLDB Endow. 13, 12 (Aug. 2020 ), 3072-3084. https://doi.org/10.14778/3415478.3415535 Google Scholar
Digital Library
- P. Jesus, C. Baquero, and P. S. Almeida. 2015. A Survey of Distributed Data Aggregation Algorithms. IEEE Communications Surveys Tutorials 17, 1 ( 2015 ), 381-404.Google Scholar
- Jinho Jung, Hong Hu, Joy Arulraj, Taesoo Kim, and Woonhak Kang. 2019. APOLLO: Automatic Detection and Diagnosis of Performance Regressions in Database Systems. Proc. VLDB Endow. 13, 1 (Sept. 2019 ), 57-70. https://doi.org/10.14778/ 3357377.3357382 Google Scholar
Digital Library
- Timotej Kapus and Cristian Cadar. 2017. Automatic Testing of Symbolic Execution Engines via Program Generation and Diferential Testing. In Proceedings of the 32Nd IEEE/ACM International Conference on Automated Software Engineering (Urbana-Champaign, IL, USA) ( ASE 2017). IEEE Press, Piscataway, NJ, USA, 590-600.Google Scholar
- S. A. Khalek, B. Elkarablieh, Y. O. Laleye, and S. Khurshid. 2008. Query-Aware Test Generation Using a Relational Constraint Solver. In Proceedings of the 2008 23rd IEEE/ACM International Conference on Automated Software Engineering (ASE '08). IEEE Computer Society, Washington, DC, USA, 238-247. https://doi.org/10.1109/ASE. 2008.34 Google Scholar
Digital Library
- Vu Le, Mehrdad Afshari, and Zhendong Su. 2014. Compiler Validation via Equivalence Modulo Inputs. In Proceedings of the 35th ACM SIGPLAN Conference on Programming Language Design and Implementation (Edinburgh, United Kingdom) (PLDI '14). ACM, New York, NY, USA, 216-226. https://doi.org/10.1145/2594291.2594334 Google Scholar
Digital Library
- Eric Lo, Carsten Binnig, Donald Kossmann, M. Tamer Özsu, and Wing-Kai Hon. 2010. A framework for testing DBMS features. The VLDB Journal 19, 2 ( 01 Apr 2010 ), 203-230. https://doi.org/10.1007/s00778-009-0157-y Google Scholar
Digital Library
- Michaël Marcozzi, Qiyi Tang, Alastair F. Donaldson, and Cristian Cadar. 2019. Compiler Fuzzing: How Much Does It Mater? Proc. ACM Program. Lang. 3, OOPSLA, Article 155 (Oct. 2019 ), 29 pages. https://doi.org/10.1145/3360581 Google Scholar
Digital Library
- William M McKeeman. 1998. Diferential testing for software. Digital Technical Journal 10, 1 ( 1998 ), 100-107.Google Scholar
- Chaitanya Mishra, Nick Koudas, and Calisto Zuzarte. 2008. Generating Targeted Queries for Database Testing. In Proceedings of the 2008 ACM SIGMOD International Conference on Management of Data (Vancouver, Canada) ( SIGMOD '08). ACM, New York, NY, USA, 499-510. https://doi.org/10.1145/1376616.1376668 Google Scholar
Digital Library
- MySQL. 2020. MySQL Homepage. https://www.mysql.com/Google Scholar
- Andrea Neufeld, Guido Moerkote, and Peter C. Lockemann. 1993. Generating Consistent Test Data: Restricting the Search Space by a Generator Formula. The VLDB Journal 2, 2 (April 1993 ), 173-214.Google Scholar
Cross Ref
- Andrew Pavlo and Mathew Aslet. 2016. What's Really New with NewSQL? SIGMOD Rec. 45, 2 (Sept. 2016 ), 45-55. https://doi.org/10.1145/3003665.3003674 Google Scholar
Digital Library
- Meikel Poess and John M. Stephens. 2004. Generating Thousand Benchmark Queries in Seconds. In Proceedings of the hTirtieth International Conference on Very Large Data Bases-Volume 30 (Toronto, Canada) ( VLDB '04). VLDB Endowment, 1045-1053.Google Scholar
- PostgreSQL. 2020. PostgreSQL Homepage. https://www.postgresql.org/Google Scholar
- Mark Raasveldt and Hannes Mühleisen. 2020. Data Management for Data Science-Towards Embedded Analytics. In CIDR.Google Scholar
- John Regehr. 2010. A Guide to Undefined Behavior in C and C++. https://blog.regehr.org/archives/213Google Scholar
- John Regehr, Yang Chen, Pascal Cuoq, Eric Eide, Chucky Ellison, and Xuejun Yang. 2012. Test-Case Reduction for C Compiler Bugs. In Proceedings of the 33rd ACM SIGPLAN Conference on Programming Language Design and Implementation (Beijing, China) ( PLDI '12). Association for Computing Machinery, New York, NY, USA, 335-346. https: //doi.org/10.1145/2254064.2254104 Google Scholar
Digital Library
- Manuel Rigger and Zhendong Su. 2020a. Detecting Optimization Bugs in Database Engines via Non-Optimizing Reference Engine Construction. In Proceedings of the 2020 28th ACM Joint Meeting on European Software Engineering Conference and Symposium on the Foundations of Software Engineering (Sacramento, California, United States) (ESEC/FSE 2020 ).Google Scholar
Digital Library
- Manuel Rigger and Zhendong Su. 2020b. OOPSLA 20 Artifact for “Finding Bugs in Database Systems via Query Partitioning”. https://doi.org/10.5281/zenodo.4032401 Google Scholar
Digital Library
- Manuel Rigger and Zhendong Su. 2020c. Testing Database Engines via Pivoted Query Synthesis. In 14th USENIX Symposium on Operating Systems Design and Implementation (OSDI 20). USENIX Association, Banf, Alberta. https://www.usenix. org/conference/osdi20/presentation/riggerGoogle Scholar
- Sergio Segura and Zhi Quan Zhou. 2018. Metamorphic Testing 20 Years Later: A Hands-on Introduction. In Proceedings of the 40th International Conference on Software Engineering: Companion Proceeedings (Gothenburg, Sweden) (ICSE '18). Association for Computing Machinery, New York, NY, USA, 538-539. https://doi.org/10.1145/3183440.3183468 Google Scholar
Digital Library
- Andreas Seltenreich. 2019. SQLSmith. https://github.com/anse1/sqlsmithGoogle Scholar
- Konstantin Serebryany, Alexander Potapenko, Timur Iskhodzhanov, and Dmitriy Vyukov. 2011. Dynamic Race Detection with LLVM Compiler. In Proceedings of the Second International Conference on Runtime Verification (San Francisco, CA) ( RV'11). Springer-Verlag, Berlin, Heidelberg, 110-114. https://doi.org/10.1007/978-3-642-29860-8_9 Google Scholar
Digital Library
- Donald R Slutz. 1998. Massive stochastic testing of SQL. In VLDB, Vol. 98. 618-622.Google Scholar
- SQLite. 2020. SQLite Homepage. https://www.sqlite.org/Google Scholar
- Evgeniy Stepanov and Konstantin Serebryany. 2015. MemorySanitizer: Fast Detector of Uninitialized Memory Use in C++. In Proceedings of the 13th Annual IEEE/ACM International Symposium on Code Generation and Optimization (San Francisco, California) ( CGO '15). IEEE Computer Society, USA, 46-55.Google Scholar
- Rebecca Taft, Irfan Sharif, Andrei Matei, Nathan VanBenschoten, Jordan Lewis, Tobias Grieger, Kai Niemi, Andy Woods, Anne Birzin, Raphael Poss, Paul Bardea, Amruta Ranade, Ben Darnell, Bram Gruneir, Justin Jafray, Lucy Zhang, and Peter Matis. 2020. CockroachDB: The Resilient Geo-Distributed SQL Database. In Proceedings of the 2020 ACM SIGMOD International Conference on Management of Data (Portland, OR, USA) ( SIGMOD '20). International Foundation for Autonomous Agents and Multiagent Systems, Richland, SC, 1493-1509. https://doi.org/10.1145/3318464.3386134 Google Scholar
Digital Library
- Veronika Thost and Julian Dolby. 2019. QED: Out-of-the-Box Datasets for SPARQL Query Evaluation. In hTe Semantic Web, Pascal Hitzler, Miriam Fernández, Krzysztof Janowicz, Amrapali Zaveri, Alasdair J.G. Gray, Vanessa Lopez, Armin Haller, and Karl Hammar (Eds.). Springer International Publishing, Cham, 491-506.Google Scholar
- Manasi Vartak, Venkatesh Raghavan, and Elke A. Rundensteiner. 2010. QRelX: Generating Meaningful Queries That Provide Cardinality Assurance. In Proceedings of the 2010 ACM SIGMOD International Conference on Management of Data (Indianapolis, Indiana, USA) ( SIGMOD '10). Association for Computing Machinery, New York, NY, USA, 1215-1218. https://doi.org/10.1145/1807167.1807323 Google Scholar
Digital Library
- Dominik Winterer, Chengyu Zhang, and Zhendong Su. 2020. Validating SMT Solvers via Semantic Fusion. In Proceedings of the 41st ACM SIGPLAN Conference on Programming Language Design and Implementation (London, UK) ( PLDI 2020 ). Association for Computing Machinery, New York, NY, USA, 718-730. https://doi.org/10.1145/3385412.3385985 Google Scholar
Digital Library
- Xuejun Yang, Yang Chen, Eric Eide, and John Regehr. 2011. Finding and Understanding Bugs in C Compilers. In Proceedings of the 32Nd ACM SIGPLAN Conference on Programming Language Design and Implementation (San Jose, California, USA) ( PLDI '11). ACM, New York, NY, USA, 283-294. https://doi.org/10.1145/1993498.1993532 Google Scholar
Digital Library
- Yuan Yu, Pradeep Kumar Gunda, and Michael Isard. 2009. Distributed Aggregation for Data-Parallel Computing: Interfaces and Implementations. In Proceedings of the ACM SIGOPS 22nd Symposium on Operating Systems Principles (Big Sky, Montana, USA) ( SOSP '09). Association for Computing Machinery, New York, NY, USA, 247-260. https://doi.org/10. 1145/1629575.1629600 Google Scholar
Digital Library
- Rui Zhong, Yongheng Chen, Hong Hu, Hangfan Zhang, Wenke Lee, and Dinghao Wu. 2020. SQUIRREL: Testing Database Management Systems with Language Validity and Coverage Feedback. In Proceedings of the 27th ACM Conference on Computer and Communications Security (CCS).Google Scholar
Digital Library
Index Terms
Finding bugs in database systems via query partitioning
Recommendations
Detecting optimization bugs in database engines via non-optimizing reference engine construction
ESEC/FSE 2020: Proceedings of the 28th ACM Joint Meeting on European Software Engineering Conference and Symposium on the Foundations of Software EngineeringDatabase Management Systems (DBMS) are used ubiquitously. To efficiently access data, they apply sophisticated optimizations. Incorrect optimizations can result in logic bugs, which cause a query to compute an incorrect result set. We propose Non-...
Testing Graph Database Engines via Query Partitioning
ISSTA 2023: Proceedings of the 32nd ACM SIGSOFT International Symposium on Software Testing and AnalysisGraph Database Management Systems (GDBMSs) store data as graphs and allow the efficient querying of nodes and their relationships. Logic bugs are bugs that cause a GDBMS to return an incorrect result for a given query (e.g., by returning incorrect ...
Testing Database Systems via Differential Query Execution
ICSE '23: Proceedings of the 45th International Conference on Software EngineeringDatabase Management Systems (DBMSs) provide efficient data retrieval and manipulation for many applications through Structured Query Language (SQL). Incorrect implementations of DBMSs can result in logic bugs, which cause SELECT queries to fetch ...






Comments