skip to main content

Finding bugs in database systems via query partitioning

Published:13 November 2020Publication History
Skip Abstract Section

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.

Skip Supplemental Material Section

Supplemental Material

Auxiliary Presentation Video

Main Video

References

  1. 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 ScholarGoogle ScholarDigital LibraryDigital Library
  2. 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 ScholarGoogle ScholarDigital LibraryDigital Library
  3. 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 ScholarGoogle Scholar
  4. 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 ScholarGoogle ScholarCross RefCross Ref
  5. 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 ScholarGoogle ScholarDigital LibraryDigital Library
  6. 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 ScholarGoogle ScholarDigital LibraryDigital Library
  7. 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 ScholarGoogle ScholarDigital LibraryDigital Library
  8. 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 ScholarGoogle ScholarDigital LibraryDigital Library
  9. 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 ScholarGoogle ScholarDigital LibraryDigital Library
  10. 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 ScholarGoogle Scholar
  11. 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 ScholarGoogle ScholarDigital LibraryDigital Library
  12. 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 ScholarGoogle ScholarDigital LibraryDigital Library
  13. 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 ScholarGoogle ScholarDigital LibraryDigital Library
  14. 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 ScholarGoogle ScholarDigital LibraryDigital Library
  15. 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 ScholarGoogle ScholarDigital LibraryDigital Library
  16. 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 ScholarGoogle ScholarDigital LibraryDigital Library
  17. 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 ScholarGoogle ScholarDigital LibraryDigital Library
  18. Goetz Graefe. 2011. Modern B-Tree Techniques. Found. Trends Databases 3, 4 (April 2011 ), 203-402. https://doi.org/10. 1561/1900000028 Google ScholarGoogle ScholarDigital LibraryDigital Library
  19. 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 ScholarGoogle ScholarDigital LibraryDigital Library
  20. 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 ScholarGoogle ScholarDigital LibraryDigital Library
  21. 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 ScholarGoogle ScholarDigital LibraryDigital Library
  22. 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 ScholarGoogle ScholarDigital LibraryDigital Library
  23. 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 ScholarGoogle ScholarDigital LibraryDigital Library
  24. 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 ScholarGoogle Scholar
  25. 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 ScholarGoogle ScholarDigital LibraryDigital Library
  26. 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 ScholarGoogle Scholar
  27. 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 ScholarGoogle ScholarDigital LibraryDigital Library
  28. 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 ScholarGoogle Scholar
  29. 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 ScholarGoogle ScholarDigital LibraryDigital Library
  30. 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 ScholarGoogle ScholarDigital LibraryDigital Library
  31. 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 ScholarGoogle ScholarDigital LibraryDigital Library
  32. 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 ScholarGoogle ScholarDigital LibraryDigital Library
  33. William M McKeeman. 1998. Diferential testing for software. Digital Technical Journal 10, 1 ( 1998 ), 100-107.Google ScholarGoogle Scholar
  34. 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 ScholarGoogle ScholarDigital LibraryDigital Library
  35. MySQL. 2020. MySQL Homepage. https://www.mysql.com/Google ScholarGoogle Scholar
  36. 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 ScholarGoogle ScholarCross RefCross Ref
  37. 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 ScholarGoogle ScholarDigital LibraryDigital Library
  38. 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 ScholarGoogle Scholar
  39. PostgreSQL. 2020. PostgreSQL Homepage. https://www.postgresql.org/Google ScholarGoogle Scholar
  40. Mark Raasveldt and Hannes Mühleisen. 2020. Data Management for Data Science-Towards Embedded Analytics. In CIDR.Google ScholarGoogle Scholar
  41. John Regehr. 2010. A Guide to Undefined Behavior in C and C++. https://blog.regehr.org/archives/213Google ScholarGoogle Scholar
  42. 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 ScholarGoogle ScholarDigital LibraryDigital Library
  43. 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 ScholarGoogle ScholarDigital LibraryDigital Library
  44. 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 ScholarGoogle ScholarDigital LibraryDigital Library
  45. 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 ScholarGoogle Scholar
  46. 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 ScholarGoogle ScholarDigital LibraryDigital Library
  47. Andreas Seltenreich. 2019. SQLSmith. https://github.com/anse1/sqlsmithGoogle ScholarGoogle Scholar
  48. 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 ScholarGoogle ScholarDigital LibraryDigital Library
  49. Donald R Slutz. 1998. Massive stochastic testing of SQL. In VLDB, Vol. 98. 618-622.Google ScholarGoogle Scholar
  50. SQLite. 2020. SQLite Homepage. https://www.sqlite.org/Google ScholarGoogle Scholar
  51. 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 ScholarGoogle Scholar
  52. 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 ScholarGoogle ScholarDigital LibraryDigital Library
  53. 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 ScholarGoogle Scholar
  54. 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 ScholarGoogle ScholarDigital LibraryDigital Library
  55. 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 ScholarGoogle ScholarDigital LibraryDigital Library
  56. 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 ScholarGoogle ScholarDigital LibraryDigital Library
  57. 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 ScholarGoogle ScholarDigital LibraryDigital Library
  58. 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 ScholarGoogle ScholarDigital LibraryDigital Library

Index Terms

  1. Finding bugs in database systems via query partitioning

      Recommendations

      Comments

      Login options

      Check if you have access through your login credentials or your institution to get full access on this article.

      Sign in

      Full Access

      PDF Format

      View or Download as a PDF file.

      PDF

      eReader

      View online with eReader.

      eReader
      About Cookies On This Site

      We use cookies to ensure that we give you the best experience on our website.

      Learn more

      Got it!