skip to main content
US FlagAn official website of the United States government
dot gov icon
Official websites use .gov
A .gov website belongs to an official government organization in the United States.
https lock icon
Secure .gov websites use HTTPS
A lock ( lock ) or https:// means you've safely connected to the .gov website. Share sensitive information only on official, secure websites.

Explore Research Products in the PAR
It may take a few hours for recently added research products to appear in PAR search results.


Title: Qr-Hint: Actionable Hints Towards Correcting Wrong SQL Queries
We describe a system called Qr-Hint that, given a (correct) target query Q* and a (wrong) working query Q, both expressed in SQL, provides actionable hints for the user to fix the working query so that it becomes semantically equivalent to the target. It is particularly useful in an educational setting, where novices can receive help from Qr-Hint without requiring extensive personal tutoring. Since there are many different ways to write a correct query, we do not want to base our hints completely on how Q* is written; instead, starting with the user's own working query, Qr-Hint purposefully guides the user through a sequence of steps that provably lead to a correct query, which will be equivalent to Q* but may still look quite different from it. Ideally, we would like Qr-Hint's hints to lead to the smallest possible corrections to Q. However, optimality is not always achievable in this case due to some foundational hurdles such as the undecidability of SQL query equivalence and the complexity of logic minimization. Nonetheless, by carefully decomposing and formulating the problems and developing principled solutions, we are able to provide provably correct and locally optimal hints through Qr-Hint. We show the effectiveness of Qr-Hint through quality and performance experiments as well as a user study in an educational setting.  more » « less
Award ID(s):
2008107
PAR ID:
10559210
Author(s) / Creator(s):
; ; ; ;
Publisher / Repository:
ACM
Date Published:
Journal Name:
Proceedings of the ACM on Management of Data
Volume:
2
Issue:
3
ISSN:
2836-6573
Page Range / eLocation ID:
1 to 27
Format(s):
Medium: X
Sponsoring Org:
National Science Foundation
More Like this
  1. ; ; (, Proceedings of the ACM on Software Engineering)
    SQL is the most commonly used front-end language for data-intensive scalable computing (DISC) applications due to its broad presence in new and legacy workflows and shallow learning curve. However, DISC-backed SQL introduces several layers of abstraction that significantly reduce the visibility and transparency of workflows, making it challenging for developers to find and fix errors in a query. When a query returns incorrect outputs, it takes a non-trivial effort to comprehend every stage of the query execution and find the root cause among the input data and complex SQL query. We aim to bring the benefits of step-through interactive debugging to DISC-powered SQL with DeSQL. Due to the declarative nature of SQL, there are no ordered atomic statements to place a breakpoint to monitor the flow of data. DeSQL’s automated query decomposition breaks a SQL query into its constituent sub queries, offering natural locations for setting breakpoints and monitoring intermediate data. However, due to advanced query optimization and translation in DISC systems, a user query rarely matches the physical execution, making it challenging to associate subqueries with their intermediate data. DeSQL performs fine-grained taint analysis to dynamically map the subqueries to their intermediate data, while also recognizing subqueries removed by the optimizers. For such subqueries, DeSQL efficiently regenerates the intermediate data from a nearby subquery’s data. On the popular TPC-DC benchmark, DeSQL provides a complete debugging view in 13% less time than the original job time while incurring an average overhead of 10% in addition to retaining Apache Spark’s scalability. In a user study comprising 15 participants engaged in two debugging tasks, we find that participants utilizing DeSQL identify the root cause behind a wrong query output in 74% less time than the de-facto, manual debugging. 
    more » « less
  2. ; (, Journal of computing sciences in colleges)
    Many people are learning programming on their own using various online resources. Unfortunately, learners using these resources often be- come disengaged or even quit when encountering an obstacle they cannot overcome without additional help. Teachers in a classroom can provide this type of help, but this may be impractical or impossible to implement in online educational settings. To address this issue, we added a visually- oriented hint system into an existing online educational game designed to teach novices introductory programming concepts. We implemented three versions of the hint system, providing equivalent information for each level of the game, adjusting the amount of interactivity between versions. The first version consisted of a static image with text showing how to solve a level in a single panel. The second version included a series of images that allowing users to scroll through hints step-by-step. The final version showed a short video allowing users to play, pause, and seek through animated hint(s). In total, we had 150 people play the game, randomly assigned to one of these three versions of the hint system. We found that users had a strong preference for the video version of the hint system, completing more levels. Based on these findings, we propose suggestions for designers of online educational tools to better support their users. 
    more » « less
  3. ; ; ; ; (, Proceedings of the 2019 International Conference on Management of Data)
    In this demonstration, we present SpeakQL, a speech-driven query system and interface for structured data. SpeakQL supports a tractable and practically useful subset of regular SQL, allowing users to query in any domain with unbounded vocabulary with the help of speech/touch based user-in-the-loop mechanisms for correction. When querying in such domains, automatic speech recognition introduces countless forms of errors in transcriptions, presenting us with a technical challenge. We characterize such errors and leverage our observations along with SQL's unambiguous context-free grammar to first correct the query structure. We then exploit phonetic representation of the queried database to identify the correct Literals, hence delivering the corrected transcribed query. In this demo, we show that SpeakQL helps users reduce time and effort in specifying SQL queries significantly. In addition, we show that SpeakQL, unlike Natural Language Interfaces and conversational assistants, allows users to query over any arbitrary database schema. We allow the audience to explore SpeakQL using an easy-to-use web-based interface to compose SQL queries. 
    more » « less
  4. ; ; ; (, Advances in Neural Information Processing Systems 34 (NeurIPS 2021))
    We consider the online linear optimization problem, where at every step the algorithm plays a point x_t in the unit ball, and suffers loss for some cost vector c_t that is then revealed to the algorithm. Recent work showed that if an algorithm receives a "hint" h_t that has non-trivial correlation with c_t before it plays x_t, then it can achieve a logarithmic regret guarantee, improving on the classical sqrt(T) bound. In this work, we study the question of whether an algorithm really requires a hint at every time step. Somewhat surprisingly, we show that an algorithm can obtain logarithmic regret with just O(sqrt(T)) hints under a natural query model. We give two applications of our result, to the well-studied setting of optimistic regret bounds and to the problem of online learning with abstention. 
    more » « less
  5. ; ; ; (, DataEd '23: Proceedings of the 2nd International Workshop on Data Systems Education: Bridging education practice with education research)
    As the need for database management skills continues to grow, there is an increasing demand for education on database models and their corresponding query languages. However, the body of research addressing the difficulties encountered by novice learners when working with query languages in database systems is still limited. In this study, we examined over 357215 submissions from 462 students’ homework problems during the Fall 2022 semester covering concepts in SQL, MongoDB, and Neo4j query languages. Our analysis through breaking down the most common syntax errors by concept confirms previous research and demonstrates that certain data operations pose challenges to students across different database systems. Specifically, we found that aggregation operations and Join operations were particularly difficult for students, which aligns with prior SQL education research. Therefore, we suggest that instructors consider incorporating visuals and assignments that enable students to build mental models for different database models. 
    more » « less
  • Citation Formats
  • MLA
  • APA
  • Chicago
  • BibTeX
  • Save / Share this Record
  • Send to Email