The Meaning of Null in Databases and Programming Languages

Kenneth Baclawski

DOI: https://doi.org/10.48550/arXiv.1606.00740

2016-06-02

Databases

Abstract:The meaning of null in relational databases is a major source of confusion not only among database users but also among database textbook writers. The purpose of this article is to examine what database nulls could mean and to make some modest suggestions about how to reduce the confusion.

What problem does this paper attempt to address?

A Formalization of SQL with Nulls

Wilmer Ricciotti,James Cheney

DOI: https://doi.org/10.48550/arXiv.2003.11331

2022-06-20

Abstract:SQL is the world's most popular declarative language, forming the basis of the multi-billion-dollar database industry. Although SQL has been standardized, the full standard is based on ambiguous natural language rather than formal specification. Commercial SQL implementations interpret the standard in different ways, so that, given the same input data, the same query can yield different results depending on the SQL system it is run on. Even for a particular system, mechanically checked formalization of all widely-used features of SQL remains an open problem. The lack of a well-understood formal semantics makes it very difficult to validate the soundness of database implementations. Although formal semantics for fragments of SQL were designed in the past, they usually did not support set and bag operations, lateral joins, nested subqueries, and, crucially, null values. Null values complicate SQL's semantics in profound ways analogous to null pointers or side-effects in other programming languages. Since certain SQL queries are equivalent in the absence of null values, but produce different results when applied to tables containing incomplete data, semantics which ignore null values are able to prove query equivalences that are unsound in realistic databases. A formal semantics of SQL supporting all the aforementioned features was only proposed recently. In this paper, we report about our mechanization of SQL semantics covering set/bag operations, lateral joins, nested subqueries, and nulls, written in the Coq proof assistant, and describe the validation of key metatheoretic properties. Additionally, we are able to use the same framework to formalize the semantics of a flat relational calculus (with null values), and show a certified translation of its normal forms into SQL.

Logic in Computer Science
Handling SQL Nulls with Two-Valued Logic

Leonid Libkin,Liat Peterfreund

DOI: https://doi.org/10.1145/3584372.3588661

2024-07-02

Abstract:The design of SQL is based on a three-valued logic (3VL), rather than the familiar Boolean logic. 3VL adds a truth value unknown to true and false to handle nulls. Viewed as indispensable for SQL expressiveness, it is at the same time much criticized for unintuitive behavior of queries and being a source of programmer mistakes. We show that, contrary to the widely held view, SQL could have been designed based on the standard Boolean logic, without any loss of expressiveness and without giving up nulls. The approach itself follows SQL's evaluation, which only retains tuples for which conditions in WHERE evaluate to true. We show that conflating unknown with false leads to an equally expressive version of SQL that does not use the third truth value. Queries written under the two-valued semantics can be efficiently translated into the standard SQL and thus executed on any existing RDBMS. These results cover the core of the SQL 1999 Standard: SELECT-FROM-WHERE-GROUP BY-HAVING queries extended with subqueries and IN/EXISTS/ANY/ALL conditions, and recursive queries. We also investigate new optimization rules enabled by the two-valued SQL, and show that for many queries, including most of those found in benchmarks such as TPC-H and TPC-DS, there is no difference between three- and two-valued versions.

Databases
Semantically Correct Query Answers in the Presence of Null Values

Loreto Bravo,Leopoldo Bertossi

DOI: https://doi.org/10.48550/arXiv.cs/0604076

2006-04-19

Databases

Abstract:For several reasons a database may not satisfy a given set of integrity constraints(ICs), but most likely most of the information in it is still consistent with those ICs; and could be retrieved when queries are answered. Consistent answers to queries wrt a set of ICs have been characterized as answers that can be obtained from every possible minimally repaired consistent version of the original database. In this paper we consider databases that contain null values and are also repaired, if necessary, using null values. For this purpose, we propose first a precise semantics for IC satisfaction in a database with null values that is compatible with the way null values are treated in commercial database management systems. Next, a precise notion of repair is introduced that privileges the introduction of null values when repairing foreign key constraints, in such a way that these new values do not create an infinite cycle of new inconsistencies. Finally, we analyze how to specify this kind of repairs of a database that contains null values using disjunctive logic programs with stable model semantics.
Belnap-Dunn Logic and Query Answering in Inconsistent Databases with Null Values

Cornelis A. Middelburg,

DOI: https://doi.org/10.7561/sacs.2023.2.159

2023-12-20

Scientific Annals of Computer Science

Abstract:This paper concerns an expansion of first-order Belnap-Dunn logic, called BD^{\supset,F}_{\bot}, and an application of this logic in the area of relational database theory. The notion of a relational database, the notion of a query applicable to a relational database, and several notions of an answer to a query with respect to a relational database are considered from the perspective of this logic, taking into account that a database may be an inconsistent database and/or a database with null values. The chosen perspective enables among other things the definition of a notion of a consistent answer to a query with respect to a possibly inconsistent database without resort to database repairs. For each of the notions of an answer considered, being an answer to a query with respect to a database of the kind considered is decidable.
Doctor of Technical Sciences, Professor, Chief Researcher at ISP RAS, Professor at the Departments of System Programming of MSU, MIPT, and HSE

Sergey Dmitrievich Kuznetsov

DOI: https://doi.org/10.15514/ispras-2023-35(2)-6

2023-01-01

Abstract:The state of affairs in the area of missing information management in relational databases leaves much to be desired. The SQL standard uses the universal null value to represent missing data, and the control is based on three-valued logic, in which the null value is identified with a third boolean value. This solution is conceptually inconsistent and often results in DBMS behavior that is not intuitive. An alternative approach using typed special values leaves all handling of missing data to users. In this article, we analyze the long history of research and development that led to this situation. We come to the conclusion that no other solution could have appeared in the SQL standard due to the choice of the mechanism of the universal null value more than 50 years ago, and the alternative mechanism cannot provide system support for special values due to the use of two-valued logic. We propose a combined approach using typed special values based on three-valued logic. This approach allows you to use the semantics of data types when processing queries with conditions that include unknown data. In addition, our approach allows us to define a full-fledged three-valued logic in which a special value of a Boolean type is the third boolean value.
Null Messages, Information and Coordination

Raïssa Nataf,Guy Goren,Yoram Moses

DOI: https://doi.org/10.48550/arXiv.2208.10866

2023-08-09

Abstract:This paper investigates the role that null messages play in synchronous systems with and without failures, and provides necessary and sufficient conditions on the structure of protocols for information transfer and coordination there. We start by introducing a new and more refined definition of null messages. A generalization of message chains that allow these null messages is provided, and is shown to be necessary and sufficient for information transfer in reliable systems. Coping with crash failures requires a much richer structure, since not receiving a message may be the result of the sender's failure. We introduce a class of communication patterns called {\em resilient message blocks}, which impose a stricter condition on protocols than the {\em silent choirs} of Goren and Moses (2020). Such blocks are shown to be necessary for information transfer in crash-prone systems. Moreover, they are sufficient in several cases of interest, in which silent choirs are not. Finally, a particular combination of resilient message blocks is shown to be necessary and sufficient for solving the Ordered Response coordination problem.

Distributed, Parallel, and Cluster Computing
NULLS!: Revisiting Null Representation in Modern Columnar Formats.

Xinyu Zeng,Ruijun Meng,Andrew Pavlo,Wes McKinney,Huanchen Zhang

DOI: https://doi.org/10.1145/3662010.3663452

2024-01-01

Abstract:Nulls are common in real-world data sets, yet recent research on columnar formats and encodings rarely address Null representations. Popular file formats like Parquet and ORC follow the same design as C-Store from nearly 20 years ago that only stores non-Null values contiguously. But recent formats store both non-Null and Null values, with Nulls being set to a placeholder value. In this work, we analyze each approach's pros and cons under different data distributions, encoding schemes (with different best SIMD ISA), and implementations. We optimize the bottlenecks in the traditional approach using AVX512. We also propose a Null-filling strategy called SmartNull, which can determine the Null values best for compression ratio at encoding time. From our micro-benchmarks, we argue that the optimal Null compression depends on several factors: decoding speed, data distribution, and Null ratio. Our analysis shows that the Compact layout performs better when Null ratio is high and the Placeholder layout is better when the Null ratio is low or the data is serial-correlated.
Querying Incomplete Numerical Data: Between Certain and Possible Answers

Marco Console,Leonid Libkin,Liat Peterfreund

DOI: https://doi.org/10.48550/arXiv.2210.15395

2022-11-01

Abstract:Queries with aggregation and arithmetic operations, as well as incomplete data, are common in real-world database, but we lack a good understanding of how they should interact. On the one hand, systems based on SQL provide ad-hoc rules for numerical nulls, on the other, theoretical research largely concentrates on the standard notions of certain and possible answers. In the presence of numerical attributes and aggregates, however, these answers are often meaningless, returning either too little or too much. Our goal is to define a principled framework for databases with numerical nulls and answering queries with arithmetic and aggregations over them. Towards this goal, we assume that missing values in numerical attributes are given by probability distributions associated with marked nulls. This yields a model of probabilistic bag databases in which tuples are not necessarily independent, since nulls can repeat. We provide a general compositional framework for query answering, and then concentrate on queries that resemble standard SQL with arithmetic and aggregation. We show that these queries are measurable, and that their outputs have a finite representation. Moreover, since the classical forms of answers provide little information in the numerical setting, we look at the probability that numerical values in output tuples belong to specific intervals. Even though their exact computation is intractable, we show efficient approximation algorithms to compute such probabilities.

Databases
NULLS!: Revisiting Null Representation in Modern Columnar Formats

Xianmin Zeng,Rui-jun Meng,Andrew Pavlo,Wes McKinney,Huanchen Zhang

DOI: https://doi.org/10.1145/3662010.3663452

2024-01-01

Abstract:Nulls are common in real-world data sets, yet recent research on columnar formats and encodings rarely address Null representations. Popular file formats like Parquet and ORC follow the same design as C-Store from nearly 20 years ago that only stores non-Null values contiguously. But recent formats store both non-Null and Null values, with Nulls being set to a placeholder value. In this work, we analyze each approach's pros and cons under different data distributions, encoding schemes (with different best SIMD ISA), and implementations. We optimize the bottlenecks in the traditional approach using AVX512. We also propose a Null-filling strategy called SmartNull, which can determine the Null values best for compression ratio at encoding time. From our micro-benchmarks, we argue that the optimal Null compression depends on several factors: decoding speed, data distribution, and Null ratio. Our analysis shows that the Compact layout performs better when Null ratio is high and the Placeholder layout is better when the Null ratio is low or the data is serial-correlated.
Database Semantics

Robert E. Kent

DOI: https://doi.org/10.48550/arXiv.1209.3054

2012-09-14

Abstract:This paper, the first step to connect relational databases with systems consequence (Kent: "System Consequence" 2009), is concerned with the semantics of relational databases. It aims to to study system consequence in the logical/semantic system of relational databases. The paper, which was inspired by and which extends a recent set of papers on the theory of relational database systems (Spivak: "Functorial Data Migration" 2012), is linked with work on the Information Flow Framework (IFF) [<a class="link-external link-http" href="http://suo.ieee.org/IFF/" rel="external noopener nofollow">this http URL</a>] connected with the ontology standards effort (SUO), since relational databases naturally embed into first order logic. The database semantics discussed here is concerned with the conceptual level of database architecture. We offer both an intuitive and technical discussion. Corresponding to the notions of primary and foreign keys, relational database semantics takes two forms: a distinguished form where entities are distinguished from relations, and a unified form where relations and entities coincide. The distinguished form corresponds to the theory presented in (Spivak: "Simplicial databases" 2009)[<a class="link-https" data-arxiv-id="0904.2012" href="https://arxiv.org/abs/0904.2012">arXiv:0904.2012</a>]. The unified form, a special case of the distinguished form, corresponds to the theory presented in (Spivak: "Functorial Data Migration" 2012). A later paper will discuss various formalisms of relational databases, such as relational algebra and first order logic, and will complete the description of the relational database logical environment.

Databases,Category Theory
NoSQL Databases: Yearning for Disambiguation

Chaimae Asaad,Karim Baïna,Mounir Ghogho

DOI: https://doi.org/10.48550/arXiv.2003.04074

2020-03-16

Abstract:The demanding requirements of the new Big Data intensive era raised the need for flexible storage systems capable of handling huge volumes of unstructured data and of tackling the challenges that traditional databases were facing. NoSQL Databases, in their heterogeneity, are a powerful and diverse set of databases tailored to specific industrial and business needs. However, the lack of theoretical background creates a lack of consensus even among experts about many NoSQL concepts, leading to ambiguity and confusion. In this paper, we present a survey of NoSQL databases and their classification by data model type. We also conduct a benchmark in order to compare different NoSQL databases and distinguish their characteristics. Additionally, we present the major areas of ambiguity and confusion around NoSQL databases and their related concepts, and attempt to disambiguate them.

Databases
Null models for network data

Patrick O. Perry,Patrick J. Wolfe

DOI: https://doi.org/10.48550/arXiv.1201.5871

2012-01-28

Abstract:The analysis of datasets taking the form of simple, undirected graphs continues to gain in importance across a variety of disciplines. Two choices of null model, the logistic-linear model and the implicit log-linear model, have come into common use for analyzing such network data, in part because each accounts for the heterogeneity of network node degrees typically observed in practice. Here we show how these both may be viewed as instances of a broader class of null models, with the property that all members of this class give rise to essentially the same likelihood-based estimates of link probabilities in sparse graph regimes. This facilitates likelihood-based computation and inference, and enables practitioners to choose the most appropriate null model from this family based on application context. Comparative model fits for a variety of network datasets demonstrate the practical implications of our results.

Statistics Theory,Social and Information Networks,Methodology
Inconsistency Measures for Relational Databases

Francesco Parisi,John Grant

DOI: https://doi.org/10.48550/arXiv.1904.03403

2019-04-06

Databases

Abstract:In this paper, building on work done on measuring inconsistency in knowledge bases, we introduce inconsistency measures for databases. In particular, focusing on databases with denial constraints, we first consider the natural approach of virtually transforming a database into a propositional knowledge base and then applying well-known measures. However, using this method, tuples and constraints are equally considered in charge of inconsistencies. Then, we introduce a version of inconsistency measures blaming database tuples only, i.e., treating integrity constraints as irrefutable statements. We analyze the compliance of database inconsistency measures with standard rationality postulates and find interesting relationships between measures. Finally, we investigate the complexity of the inconsistency measurement problem as well as of the problems of deciding whether the inconsistency is lower than, greater than, or equal to a given threshold.
When is Nothing Something? Editorial for the Null Results Special Issue of Journal of Business and Psychology

Ronald S. Landis,Lawrence R. James,Charles E. Lance,Charles A. Pierce,Steven G. Rogelberg

DOI: https://doi.org/10.1007/s10869-014-9347-8

IF: 6.604

2014-02-25

Journal of Business and Psychology

Abstract:This special issue is focused on how null results can meaningfully advance science and practice. This editorial describes some of the unique aspects of creating a special issue devoted to null results, offers opinions regarding why and under what conditions null results should be valued, and offers recommendations for key stakeholders (i.e., editors, reviewers, and authors) in the publication enterprise with respect to submitting and evaluating research with null findings. This editorial also provides summaries of the papers included in this special issue with emphasis on how they serve as example for what constitutes meaningful null results.

psychology, applied,business
On Statistical Non-Significance

Alberto Abadie

DOI: https://doi.org/10.48550/arXiv.1803.00609

2018-03-02

Abstract:Significance tests are probably the most extended form of inference in empirical research, and significance is often interpreted as providing greater informational content than non-significance. In this article we show, however, that rejection of a point null often carries very little information, while failure to reject may be highly informative. This is particularly true in empirical contexts where data sets are large and where there are rarely reasons to put substantial prior probability on a point null. Our results challenge the usual practice of conferring point null rejections a higher level of scientific significance than non-rejections. In consequence, we advocate a visible reporting and discussion of non-significant results in empirical practice.

Other Statistics,Applications,Methodology
Subsums of Null Sequences: A Survey and Open Questions

Justin Jacob

DOI: https://doi.org/10.48550/arXiv.2012.00813

2020-12-02

Abstract:We give a survey on the different results involving the topological structure of subsums of null sequences.

Dynamical Systems
A Unifying Framework for Incompleteness, Inconsistency, and Uncertainty in Databases

Benny Kimelfeld,Phokion G. Kolaitis

DOI: https://doi.org/10.1145/3624717

IF: 22.7

2024-02-24

Communications of the ACM

Abstract:Deploying possible world semantics and the challenge of computing the certain answers to queries.

computer science, theory & methods, software engineering, hardware & architecture
Negative Database for Data Security

Anup Patel,Niveeta Sharma,Magdalini Eirinaki

DOI: https://doi.org/10.48550/arXiv.1105.0049

2011-04-30

Abstract:Data Security is a major issue in any web-based application. There have been approaches to handle intruders in any system, however, these approaches are not fully trustable; evidently data is not totally protected. Real world databases have information that needs to be securely stored. The approach of generating negative database could help solve such problem. A Negative Database can be defined as a database that contains huge amount of data consisting of counterfeit data along with the real data. Intruders may be able to get access to such databases, but, as they try to extract information, they will retrieve data sets that would include both the actual and the negative data. In this paper we present our approach towards implementing the concept of negative database to help prevent data theft from malicious users and provide efficient data retrieval for all valid users.

Databases
Dimension-free matricial Nullstellensätze for noncommutative polynomials

Jurij Volčič

2024-03-11

Abstract:Hilbert's Nullstellensatz is one of the most fundamental correspondences between algebra and geometry, and has inspired a plethora of noncommutative analogs. In last two decades, there has been an increased interest in understanding vanishing sets of polynomials in several matrix variables without restricting the matrix size, prompted by developments in noncommutative function theory, control systems, operator algebras, and quantum information theory. The emerging results vary according to the interpretation of what vanishing means. For example, given a collection of noncommutative polynomials, one can consider all matrix tuples at which the values of these polynomials are all zero, singular, have common kernel, or have zero trace. This survey reviews Nullstellensätze for the above types of vanishing sets, and identifies their structural counterparts in the free algebra.

Rings and Algebras,Operator Algebras
Coo: Rethink Data Anomalies In Databases

Haixiang Li,Xiaoyan Li,Yuxing Chen,Yuean Zhu,Xiaoyong Du,Wei Lu,Chang Liu,Anqun Pan

DOI: https://doi.org/10.48550/arXiv.2109.06485

2021-11-01

Abstract:Transaction processing technology has three important contents: data anomalies, isolation levels, and concurrent control algorithms. Concurrent control algorithms are used to eliminate some or all data anomalies at different isolation levels to ensure data consistency. Isolation levels in the current ANSI standard are defined by disallowing certain kinds of data anomalies. Yet, the definitions of data anomalies in the ANSI standard are controversial. On one hand, the definitions lack a mathematical formalization and cause ambiguous interpretations. On the other hand, the definitions are made in a case-by-case manner and lead to a situation that even a senior DBA could not have infallible knowledge of data anomalies, due to a lack of a full understanding of its nature. While revised definitions in existing literature propose various mathematical formalizations to correct the former argument, how to address the latter argument still remains an open problem. In this paper, we present a general framework called Coo with the capability to systematically define data anomalies. Under this framework, we show that existing reported data anomalies are only a small portion. While we theoretically prove that Coo is complete to mathematically formalize data anomalies, we employ a novel method to classify infinite data anomalies. In addition, we use this framework to define new isolation levels and quantitatively describe the concurrency and rollback rate of mainstream concurrency control algorithms. These works show that the C and I of ACID can be quantitatively analyzed based on all data anomalies.

Databases

The Meaning of Null in Databases and Programming Languages

A Formalization of SQL with Nulls

Handling SQL Nulls with Two-Valued Logic

Semantically Correct Query Answers in the Presence of Null Values

Belnap-Dunn Logic and Query Answering in Inconsistent Databases with Null Values

Doctor of Technical Sciences, Professor, Chief Researcher at ISP RAS, Professor at the Departments of System Programming of MSU, MIPT, and HSE

Null Messages, Information and Coordination

NULLS!: Revisiting Null Representation in Modern Columnar Formats.

Querying Incomplete Numerical Data: Between Certain and Possible Answers

NULLS!: Revisiting Null Representation in Modern Columnar Formats

Database Semantics

NoSQL Databases: Yearning for Disambiguation

Null models for network data

Inconsistency Measures for Relational Databases

When is Nothing Something? Editorial for the Null Results Special Issue of Journal of Business and Psychology

On Statistical Non-Significance

Subsums of Null Sequences: A Survey and Open Questions

A Unifying Framework for Incompleteness, Inconsistency, and Uncertainty in Databases

Negative Database for Data Security

Dimension-free matricial Nullstellensätze for noncommutative polynomials

Coo: Rethink Data Anomalies In Databases