research-article

Observing SQL queries in their natural habitat

Authors:

Jan RittingerAuthors Info & Claims

ACM Transactions on Database Systems (TODS), Volume 38, Issue 1

Article No.: 3, Pages 1 - 33

https://doi.org/10.1145/2445583.2445586

Published: 26 April 2013 Publication History

Abstract

We describe Habitat, a declarative observational debugger for SQL. Habitat facilitates true language-level (not: plan-level) debugging of, probably flawed, SQL queries that yield unexpected results. Users mark SQL subexpressions of arbitrary size and then observe whether these evaluate as expected. Habitat understands query nesting and free row variables in correlated subqueries, and generally aims to not constrain users while suspect subexpressions are marked for observation.

From the marked SQL text, Habitat's algebraic compiler derives a new query whose result represents the values of the desired observations. These observations are generated by the target SQL database host itself and are derived from the original data: Habitat does not require prior data extraction or extra debugging middleware. Experiments with TPC-H database instances indicate that observations impose a runtime overhead sufficiently low to allow for interactive debugging sessions.

References

[1]

ANSI/ISO. Database Language SQL—Part 2: Foundation (SQL/Foundation). ANSI/ISO. IEC 9075.

[2]

Bati, H., Giakoumakis, L., Herbert, S., and Suma, A. 2007. A genetic approach for random testing of database systems. In Proceedings of the 33rd International Conference on Very Large Data Bases. VLDB Endowment, 243--1251.

Digital Library

[3]

Binnig, C. and Kossmann, D. 2007. Reverse query processing. In Proceedings of the 23rd International Conference on Data Engineering. IEEE, 506--515.

[4]

Bruno, N. 2010. Minimizing database repros using language grammars. In Proceedings of the 13th International Conference on Extending Database Technology. ACM, 382--393.

Digital Library

[5]

Bruno, N., Chauduri, S., and Ramamurthy, R. 2009. Interactive plan hints for query optimization. In Proceedings of the 35th ACM SIGMOD International Conference on Management of Data. ACM, 1043--1046.

Digital Library

[6]

Caballero, R., Garcia-Ruiz, Y., and Sáenz-Pérez, F. 2012. Declarative debugging of wrong and missing answers for SQL views. In Proceedings of the 11th International Symposium on Functional and Logic Programming. Springer.

Digital Library

[7]

Chapman, A. and Jagadish, H. 2009. Why not&quest; In Proceedings of the 35th ACM SIGMOD International Conference on Management of Data. ACM, 523--534.

Digital Library

[8]

Cheney, J., Chiticariu, L., and Tan, W. 2009. Provenance in databases: Why, how, and where. Found. Trends Datab. 1, 4, 379--474.

Digital Library

[9]

Chopra, S., Olston, C., and Srivastava, U. 2009. Generating example data for dataflow programs. In Proceedings of the 35th ACM SIGMOD International Conference on Management of Data. ACM, 245--256.

Digital Library

[10]

Embarcadero Technologies. The Embarcadero SQL Debugger. http://www.embarcadero.com/products/debugger.

[11]

Ganski, R. and Wong, H. 1987. Optimization of nested SQL queries revisited. SIGMOD Record 16, 3, 23--33.

Digital Library

[12]

Gogolla, M. 1990. A note on the translation of SQL to the tuple calculus. SIGMOD Record 19, 1, 18--22.

Digital Library

[13]

Grust, T., Kliebhan, F., Rittinger, J., and Schreiber, T. 2011. True language-level SQL debugging. In Proceedings of the 14th International Conference on Extending Database Technology. ACM, 562--565.

Digital Library

[14]

Grust, T., Mayr, M., Rittinger, J., Sakr, S., and Teubner, J. 2007a. A SQL:1999 code generator for the Pathfinder XQuery compiler. In Proceedings of the 33rd ACM SIGMOD International Conference on Management of Data. ACM, 1162--1164.

Digital Library

[15]

Grust, T., Rittinger, J., and Teubner, J. 2007b. Data-intensive XQuery debugging with instant replay. In Proceedings of the 4th International Workshop on XQuery Implementation, Experience, and Perspective. ACM, 4:1--4:6.

Digital Library

[16]

Haritsa, J. and Reddy, N. 2005. Analyzing plan diagrams of database query optimizers. In Proceedings of the 31st International Conference on Very Large Data Bases. VLDB Endowment. 1228--1239.

Digital Library

[17]

Herschel, M. and Hernández, M. 2010. Explaining missing answers to SPJUA queries. Proc. VLDB Endow. 3, 1--2, 185--196.

Digital Library

[18]

Jagadish, H., Chapman, A., Elkiss, A., Jayapandian, M., Li, Y., Nandi, A., and Yu, C. 2007. Making database systems usable. In Proceedings of the 33rd ACM SIGMOD International Conference on Management of Data. ACM, 13--24.

Digital Library

[19]

Johnsson, T. 1985. Lambda lifting: Transforming programs to recursive equations. In Proceedings of the Conference on Functional Programming Languages and Computer Architecture. Springer, 190--203.

Digital Library

[20]

Katz, I. and Anderson, J. 1987. Debugging: An analysis of bug-location strategies. Hum.-Comput. Interact. 3, 4, 351--399.

Digital Library

[21]

Khoussainova, N., Balazinska, M., and Suciu, D. 2012. PerfXplain: Debugging MapReduce job performance. Proc. VLDB Endow. 5, 7, 598--609.

Digital Library

[22]

Kim, W. 1982. On optimizing an SQL-like nested query. ACM Trans. Datab. Syst. 7, 3, 443--469.

Digital Library

[23]

Landin, P. 1964. The mechanical evaluation of expressions. Comput. J. 6, 4, 308--320.

[24]

Liang, R. 2009. What's Changed between my New Query Plan and the Old One&quest; Plan Comparison (built-in function diff_plan_outline()) in Oracle 11gR2. Insights into the working of the Oracle Optimizer, https://blogs.oracle.com/optimizer/entry/whats_changed_between_my_new_query_plan_and_the_old_one.

[25]

Marlow, S., Iborra, J., Pope, B., and Gill, A. 2007. A lightweight interactive debugger for Haskell. In Proceedings of the ACM SIGPLAN Workshop on Haskell. ACM, 13--24.

Digital Library

[26]

Microsoft Corporation. Transact-SQL (T-SQL) Debugger in Microsoft SQL Server 2008. http://msdn. microsoft.com/en-us/library/cc645997.aspx.

[27]

Motro, A. 1986. Query generalization: A method for interpreting null answers. In Proceedings of the 1st International Workshop on Expert Database Systems. Benjamin/Cummings, 597--616.

Digital Library

[28]

Murphy, L., Lewandowski, G., McCauley, R., Simon, B., Thomas, L., and Zander, C. 2008. Debugging: The good, the bad, and the quirky—A quantitative analysis of novices' strategies. SIGCSE Bull. 40, 1, 163--167.

Digital Library

[29]

Naish, L. 1997. A declarative debugging scheme. J. Funct. Logic Program., 3.

[30]

Olston, C. and Reed, B. 2011. Inspector Gadget: A framework for custom monitoring and debugging of distributed dataflows. Proc. VLDB Endow. 4, 12, 1237--1248.

[31]

Pope, B. and Naish, L. 2003. Practical aspects of declarative debugging in Haskell 98. In Proceedings of the 5th ACM SIGPLAN International Conference on Principles and Practice of Declarative Programming. ACM, 230--244.

Digital Library

[32]

Shapiro, E. 1983. Algorithmic Program Debugging. MIT Press, Cambridge, MA, USA.

Digital Library

[33]

Silva, J. 2011. A survey on algorithmic debugging strategies. Adv. Eng. Software 42, 11, 976--991.

Digital Library

[34]

Tran, Q. and Jagadish, H. 2010. How to ConQueR why-not questions. In Proceedings of the 36th ACM SIGMOD International Conference on Management of Data. ACM, 15--26.

Digital Library

[35]

Transaction Processing Performance Council. TPC-H, a Decision-Support Benchmark. http://tpc.org/tpch/.

[36]

Victor, B. 2012. Inventing on Principle. In Proceedings of the Canadian University Software Engineering Conference (CUSEC). Montreal, Canada.

Cited By

Hu YGilad AStephens-Martinez KRoy SYang J(2024)Qr-Hint: Actionable Hints Towards Correcting Wrong SQL QueriesProceedings of the ACM on Management of Data10.1145/36549952:3(1-27)Online publication date: 30-May-2024
https://dl.acm.org/doi/10.1145/3654995
Hu YJiang HTang HLin XHu Z(2023)SQL#: A Language for Maintainable and Debuggable Database QueriesInternational Journal of Software Engineering and Knowledge Engineering10.1142/S021819402350010933:05(619-649)Online publication date: 13-Apr-2023
https://doi.org/10.1142/S0218194023500109
Deep SGruenheid ANagaraj KNaito HNaughton JViglas S(2022)DIAMETRICSCommunications of the ACM10.1145/356746465:12(105-112)Online publication date: 22-Nov-2022
https://dl.acm.org/doi/10.1145/3567464
Show More Cited By

Index Terms

Observing SQL queries in their natural habitat
1. Information systems
  1. Data management systems
    1. Query languages
2. Software and its engineering
  1. Software creation and management
    1. Software verification and validation
      1. Software defect analysis
        Software testing and debugging

Recommendations

On Workload Characterization of Relational Database Environments

A relational database workload analyzer (REDWAR) is developed to characterize the workload in a DB2 environment. This is applied to study a production DB2 system where a structured query language (SQL) trace for a two-hour interval and an image copy of ...
Extensions to SQL for Historical Databases

A historical management system (HDBMS) is described which uses an extended relational data model with state-oriented, instead of 'cubic', conceptualization. Two types of historical relations, called state and event relations, are provided for modeling ...
Providing Quality Responses with Natural Language Interfaces: The Null Value Problem

An underlying relational database model and the database query language SQL are assumed, and methods are presented for responding with appropriate answers to null value responses. This is done by using a knowledge base based on RM/T, an extended ...

Comments

Information & Contributors

Information

Published In

cover image ACM Transactions on Database Systems

ACM Transactions on Database Systems Volume 38, Issue 1

April 2013

290 pages

ISSN:0362-5915

EISSN:1557-4644

DOI:10.1145/2445583

Issue’s Table of Contents

Copyright © 2013 ACM.

Permission to make digital or hard copies of all or part of this work for personal or classroom use is granted without fee provided that copies are not made or distributed for profit or commercial advantage and that copies bear this notice and the full citation on the first page. Copyrights for components of this work owned by others than ACM must be honored. Abstracting with credit is permitted. To copy otherwise, or republish, to post on servers or to redistribute to lists, requires prior specific permission and/or a fee. Request permissions from [email protected]

Publisher

Association for Computing Machinery

New York, NY, United States

Publication History

Published: 26 April 2013

Accepted: 01 September 2012

Received: 01 April 2012

Published in TODS Volume 38, Issue 1

Permissions

Request permissions for this article.

Request Permissions

Check for updates

Author Tags

Qualifiers

Research-article
Research
Refereed

Funding Sources

Deutsche Forschungsgemeinschaft

Contributors

Other Metrics

View Article Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

14
Total Citations
View Citations
707
Total Downloads

Downloads (Last 12 months)21
Downloads (Last 6 weeks)4

Reflects downloads up to 22 Sep 2024

Other Metrics

View Author Metrics

Citations

Cited By

Hu YGilad AStephens-Martinez KRoy SYang J(2024)Qr-Hint: Actionable Hints Towards Correcting Wrong SQL QueriesProceedings of the ACM on Management of Data10.1145/36549952:3(1-27)Online publication date: 30-May-2024
https://dl.acm.org/doi/10.1145/3654995
Hu YJiang HTang HLin XHu Z(2023)SQL#: A Language for Maintainable and Debuggable Database QueriesInternational Journal of Software Engineering and Knowledge Engineering10.1142/S021819402350010933:05(619-649)Online publication date: 13-Apr-2023
https://doi.org/10.1142/S0218194023500109
Deep SGruenheid ANagaraj KNaito HNaughton JViglas S(2022)DIAMETRICSCommunications of the ACM10.1145/356746465:12(105-112)Online publication date: 22-Nov-2022
https://dl.acm.org/doi/10.1145/3567464
Taipalus TGrahn HGhanbari H(2022)Error messages in relational database management systemsJournal of Systems and Software10.1016/j.jss.2021.111034181:COnline publication date: 22-Apr-2022
https://dl.acm.org/doi/10.1016/j.jss.2021.111034
Deep SGruenheid ANagaraj KNaito HNaughton JViglas S(2021)DIAMetricsACM SIGMOD Record10.1145/3471485.347149250:1(24-31)Online publication date: 15-Jun-2021
https://dl.acm.org/doi/10.1145/3471485.3471492
Deep SGruenheid ANagaraj KNaito HNaughton JViglas S(2020)DIAMetricsProceedings of the VLDB Endowment10.14778/3415478.341555113:12(3285-3298)Online publication date: 14-Sep-2020
https://dl.acm.org/doi/10.14778/3415478.3415551
O'Grady DMüller TGrust THerschel M(2018)How how explains what what computesProceedings of the 10th USENIX Conference on Theory and Practice of Provenance10.5555/3319379.3319391(8-8)Online publication date: 11-Jul-2018
https://dl.acm.org/doi/10.5555/3319379.3319391
Müller TDietrich BGrust T(2018)You say 'what', i hear 'where' and 'why'Proceedings of the VLDB Endowment10.14778/3236187.323620411:11(1536-1549)Online publication date: 1-Jul-2018
https://dl.acm.org/doi/10.14778/3236187.3236204
Niu XArab BLee SFeng SZou XGawlick DKrishnaswamy VLiu ZGlavic B(2017)Debugging transactions and tracking their provenance with reenactmentProceedings of the VLDB Endowment10.14778/3137765.313779310:12(1857-1860)Online publication date: 1-Aug-2017
https://dl.acm.org/doi/10.14778/3137765.3137793
Herschel MDiestelkämper RBen Lahmar H(2017)A survey on provenanceThe VLDB Journal — The International Journal on Very Large Data Bases10.1007/s00778-017-0486-126:6(881-906)Online publication date: 1-Dec-2017
https://dl.acm.org/doi/10.1007/s00778-017-0486-1
Show More Cited By

View Options

Get Access

Login options

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

Full Access

Get this Article

View options

PDF

View or Download as a PDF file.

eReader

View online with eReader.

Media

Figures

Other

Tables

View Issue’s Table of Contents