column

Correctness of SQL Queries on Databases with Nulls

Authors:

Paolo Guagliardo,

Leonid LibkinAuthors Info & Claims

ACM SIGMOD Record, Volume 46, Issue 3

Pages 5 - 16

https://doi.org/10.1145/3156655.3156657

Published: 31 October 2017 Publication History

Abstract

Multiple issues with SQL's handling of nulls have been well documented. Having efficiency as its main goal, SQL disregards the standard notion of correctness on incomplete databases -- certain answers -- due to its high complexity. As a result, the evaluation of SQL queries on databases with nulls may produce answers that are just plain wrong. However, SQL evaluation can be modified, at least for relational algebra queries, to approximate certain answers, i.e., return only correct answers. We examine recently proposed approximation schemes for certain answers and analyze their complexity, both theoretical bounds and real-life behavior

References

[1]

S. Abiteboul, R. Hull, and V. Vianu. Foundations of Databases. Addison-Wesley, 1995.

Digital Library

[2]

S. Abiteboul, P. C. Kanellakis, and G. Grahne. On the representation and querying of sets of possible worlds. Theoretical Computer Science, 78(1):158-187, 1991.

Digital Library

[3]

M. Arenas, P. Barceló, L. Libkin, and F. Murlak. Foundations of Data Exchange. Cambridge University Press, 2014.

Digital Library

[4]

J. Celko. SQL for Smarties: Advanced SQL Programming. Morgan Kaufmann, 1995.

Digital Library

[5]

J. Claußen, A. Kemper, G. Moerkotte, K. Peithner, and M. Steinbrunn. Optimization and evaluation of disjunctive queries. IEEE Trans. Knowl. Data Eng., 12(2):238-260, 2000.

Digital Library

[6]

E. F. Codd and C. J. Date. Much ado about nothing. In C. J. Date, editor, Relational Database Writings 1991-1994. 1995.

[7]

F. Coelho. DataFiller - generate random data from database schema. https://www.cri.ensmp.fr/ people/coelho/datafiller.html.

[8]

M. Console, P. Guagliardo, and L. Libkin. On querying incomplete information in databases under bag semantics. In IJCAI, pages 993-999. ijcai.org, 2017.

[9]

C. Date and H. Darwen. A Guide to the SQL Standard. Addison-Wesley, 1996.

Digital Library

[10]

C. J. Date. An Introduction to Database Systems. Pearson, 2003.

[11]

G. H. Gessert. Four valued logic for relational database systems. SIGMOD Record, 19(1):29-35, 1990.

Digital Library

[12]

A. Gheerbrant, L. Libkin, and C. Sirangelo. Naïve evaluation of queries over incomplete databases. ACM Trans. Database Syst., 39(4):31:1-31:42, 2014.

Digital Library

[13]

J. Grant. Null values in a relational data base. Inf. Process. Lett., 6(5):156-157, 1977.

[14]

S. Grumbach, L. Libkin, T. Milo, and L. Wong. Query languages for bags: expressive power and complexity. SIGACT News, 27(2):30-44, 1996.

Digital Library

[15]

P. Guagliardo and L. Libkin. Making SQL queries correct on incomplete databases: A feasibility study. In PODS, pages 211-223. ACM, 2016.

Digital Library

[16]

P. Guagliardo and L. Libkin. A formal semantics of SQL queries, its validation, and applications. PVLDB, 11(1), 2017.

[17]

P. Guagliardo and L. Libkin. On the Codd semantics of SQL nulls. In AMW, 2017.

[18]

T. Imielinski and W. Lipski. Incomplete information in relational databases. J. ACM, 31(4):761-791, 1984.

Digital Library

[19]

H. Klein. How to modify SQL queries in order to guarantee sure answers. SIGMOD Record, 23(3):14-20, 1994.

Digital Library

[20]

W. Lang, R. V. Nehme, E. Robinson, and J. F. Naughton. Partial results in database systems. In SIGMOD, pages 1275-1286, 2014.

Digital Library

[21]

V. Leis, A. Gubichev, A. Mirchev, P. A. Boncz, A. Kemper, and T. Neumann. How good are query optimizers, really? PVLDB, 9(3):204-215, 2015.

Digital Library

[22]

M. Lenzerini. Data integration: A theoretical perspective. In PODS, pages 233-246, 2002.

Digital Library

[23]

N. Lerat and W. Lipski. Nonapplicable nulls. Theor. Comput. Sci., 46(3):67-82, 1986.

Digital Library

[24]

L. Libkin. Certain answers as objects and knowledge. Artificial Intelligence, 232:1-19, 2016.

Digital Library

[25]

L. Libkin. SQL's three-valued logic and certain answers. ACM TODS, 41(1):1:1-1:28, 2016.

Digital Library

[26]

W. Lipski. On semantic issues connected with incomplete information databases. ACM Transactions on Database Systems, 4(3):262-296, 1979.

Digital Library

[27]

W. Lipski. On relational algebra with marked nulls. In PODS, pages 201-203, 1984.

Digital Library

[28]

R. Reiter. On closed world data bases. In Logic and Data Bases, pages 55-76, 1977.

[29]

R. Reiter. A sound and sometimes complete query evaluation algorithm for relational databases with null values. Journal of the ACM, 33(2):349-347, 1986.

Digital Library

[30]

Transaction Processing Performance Council. TPC Benchmark™ H Standard Specification, Nov. 2014. Revision 2.17.1.

[31]

R. van der Meyden. Logical approaches to incomplete information: A survey. In Logics for Databases and Information Systems, pages 307-356, 1998.

Digital Library

[32]

M. Vardi. Querying logical databases. Journal of Computer and System Sciences, 33(2):142-160, 1986.

Digital Library

[33]

K. Yue. A more general model for handling missing information in relational databases using a 3-valued logic. SIGMOD Record, 20(3):43-49, 1991.

Digital Library

[34]

C. Zaniolo. Database relations with null values. J. Comput. Syst. Sci., 28(1):142-166, 1984.

Cited By

Huber AKennedy ORudra AZhao ZFeng SGlavic B(2025)FastPDB: Towards Bag-Probabilistic Queries at Interactive SpeedsProceedings of the ACM on Management of Data10.1145/37096913:1(1-25)Online publication date: 11-Feb-2025
https://dl.acm.org/doi/10.1145/3709691
Me’Ndez MMerayo MChittayasothorn S(2024)Handling Null Values in SQL Queries on Relational Databases2024 10th International Conference on Engineering, Applied Sciences, and Technology (ICEAST)10.1109/ICEAST61342.2024.10553913(65-68)Online publication date: 1-May-2024
https://doi.org/10.1109/ICEAST61342.2024.10553913
Feng SGlavic BKennedy O(2023)Efficient Approximation of Certain and Possible Answers for Ranking and Window Queries over Uncertain DataProceedings of the VLDB Endowment10.14778/3583140.358315116:6(1346-1358)Online publication date: 20-Apr-2023
https://dl.acm.org/doi/10.14778/3583140.3583151
Show More Cited By

Correctness of SQL Queries on Databases with Nulls
1. Information systems
  1. Data management systems
    1. Database management system engines
    2. Query languages
2. Theory of computation
  1. Theory and algorithms for application domains
    1. Database theory

Recommendations

Making SQL Queries Correct on Incomplete Databases: A Feasibility Study
PODS '16: Proceedings of the 35th ACM SIGMOD-SIGACT-SIGAI Symposium on Principles of Database Systems

Multiple issues with SQL's handling of nulls have been well documented. Having efficiency as its key goal, evaluation of SQL queries disregards the standard notion of correctness on incomplete databases - certain answers - due to its high complexity. As ...
On the Codd semantics of SQL nulls
Abstract
Theoretical models used in database research often have subtle differences with those occurring in practice. One particular mismatch that is usually neglected concerns the use of marked nulls to represent missing values in theoretical ...
Highlights
- SQL nulls are commonly interpreted as non-repeating marked nulls, but even simple queries may produce answers that break this interpretation.
Sql: Learn Basics of Queries and Implement Easily (sql programming, SQL 2016, sql database programming, sql for beginners, sql beginners guide, sql ... sql workbook, sql guide, MSSQL) (Volume 1)

Comments

Information & Contributors

Information

Published In

cover image ACM SIGMOD Record

ACM SIGMOD Record Volume 46, Issue 3

September 2017

37 pages

ISSN:0163-5808

DOI:10.1145/3156655

Editors:
Yanlei Diao
University of Massachusetts Amherst
,
Vanessa Braganholo
Universidade Federal Fluminense
,
Marco Brambilla
Politecnico di Milano
,
Chee Yong Chan
National University of Singapore
,
Rada Chirkova
North Carolina State University
,
Zackary Ives
University of Pennsylvania
,
Anastasios Kementsietsidis
Google Research
,
Jeffrey Naughton
University of Wisconsin-Madison
,
Frank Neven
Hasselt University
,
Olga Papaemmanoui
Brandeis Univesity
,
Aditya Parameswaran
University of Illinois
,
Anish Das Sarma
Google Research
,
Alkis Simitsis
HP Labs
,
Wang-Chiew Tan
University of California Santa Cruz
,
Nesime Tatbul
MIT CSAIL
,
Marianne Winslett
University of Illinois
,
Jun Yang
Duke University

Issue’s Table of Contents

Copyright © 2017 Copyright is held by the owner/author(s).

Permission to make digital or hard copies of part or all 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 third-party components of this work must be honored. For all other uses, contact the Owner/Author.

Publisher

Association for Computing Machinery

New York, NY, United States

Publication History

Published: 31 October 2017

Published in SIGMOD Volume 46, Issue 3

Check for updates

Qualifiers

Column

Contributors

Other Metrics

View Article Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

13
Total Citations
View Citations
158
Total Downloads

Downloads (Last 12 months)27
Downloads (Last 6 weeks)0

Reflects downloads up to 20 Feb 2025

Other Metrics

View Author Metrics

Citations

Cited By

Huber AKennedy ORudra AZhao ZFeng SGlavic B(2025)FastPDB: Towards Bag-Probabilistic Queries at Interactive SpeedsProceedings of the ACM on Management of Data10.1145/37096913:1(1-25)Online publication date: 11-Feb-2025
https://dl.acm.org/doi/10.1145/3709691
Me’Ndez MMerayo MChittayasothorn S(2024)Handling Null Values in SQL Queries on Relational Databases2024 10th International Conference on Engineering, Applied Sciences, and Technology (ICEAST)10.1109/ICEAST61342.2024.10553913(65-68)Online publication date: 1-May-2024
https://doi.org/10.1109/ICEAST61342.2024.10553913
Feng SGlavic BKennedy O(2023)Efficient Approximation of Certain and Possible Answers for Ranking and Window Queries over Uncertain DataProceedings of the VLDB Endowment10.14778/3583140.358315116:6(1346-1358)Online publication date: 20-Apr-2023
https://dl.acm.org/doi/10.14778/3583140.3583151
Sauvayre R(2022)Types of Errors Hiding in Google Scholar DataJournal of Medical Internet Research10.2196/2835424:5(e28354)Online publication date: 27-May-2022
https://doi.org/10.2196/28354
Wei ZLink S(2021)Embedded Functional Dependencies and Data-completeness Tailored Database DesignACM Transactions on Database Systems10.1145/345051846:2(1-46)Online publication date: 29-May-2021
https://dl.acm.org/doi/10.1145/3450518
Feng SGlavic BHuber AKennedy OLi GLi ZIdreos SSrivastava D(2021)Efficient Uncertainty Tracking for Complex Queries with Attribute-level BoundsProceedings of the 2021 International Conference on Management of Data10.1145/3448016.3452791(528-540)Online publication date: 9-Jun-2021
https://dl.acm.org/doi/10.1145/3448016.3452791
Koehler HLe VLink S(2021)Possible Keys and Functional DependenciesJournal on Data Semantics10.1007/s13740-021-00135-w10:3-4(327-366)Online publication date: 14-Aug-2021
https://doi.org/10.1007/s13740-021-00135-w
Console MGuagliardo PLibkin LToussaint ESuciu DTao YWei Z(2020)Coping with Incomplete Data: Recent AdvancesProceedings of the 39th ACM SIGMOD-SIGACT-SIGAI Symposium on Principles of Database Systems10.1145/3375395.3387970(33-47)Online publication date: 14-Jun-2020
https://dl.acm.org/doi/10.1145/3375395.3387970
Feng SHuber AGlavic BKennedy OBoncz PManegold SAilamaki ADeshpande AKraska T(2019)Uncertainty Annotated Databases - A Lightweight Approach for Approximating Certain AnswersProceedings of the 2019 International Conference on Management of Data10.1145/3299869.3319887(1313-1330)Online publication date: 25-Jun-2019
https://dl.acm.org/doi/10.1145/3299869.3319887
Bertossi LSuciu DSkritek SKoch C(2019)Database Repairs and Consistent Query AnsweringProceedings of the 38th ACM SIGMOD-SIGACT-SIGAI Symposium on Principles of Database Systems10.1145/3294052.3322190(48-58)Online publication date: 25-Jun-2019
https://dl.acm.org/doi/10.1145/3294052.3322190
Show More Cited By

View Options

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 Publication

View options

PDF

View or Download as a PDF file.

eReader

View online with eReader.

Figures

Tables

Media

View Issue’s Table of Contents