Неполные данные и null — различия между версиями

Материал из Викиконспекты
Перейти к: навигация, поиск
м (Скалярные операции, порождающие null)
м (rollbackEdits.php mass rollback)
 
(не показано 35 промежуточных версий 2 участников)
Строка 1: Строка 1:
{{В разработке}}
 
 
 
== Что означает null ==
 
== Что означает null ==
 
Представим себе таблицы в БД университета ИТМО  
 
Представим себе таблицы в БД университета ИТМО  
Строка 84: Строка 82:
  
 
=== Сравнение ===
 
=== Сравнение ===
==== = ====
+
==== Равенство ====
 
{| style="background-color:#CCC;margin:0.5px;text-align:center"
 
{| style="background-color:#CCC;margin:0.5px;text-align:center"
 
!style="background-color:#EEE;color:#00F"| <tex>\bf{=}</tex>
 
!style="background-color:#EEE;color:#00F"| <tex>\bf{=}</tex>
Строка 144: Строка 142:
 
|style="background-color:#FFF;padding:2px 30px"| <tex>false</tex>
 
|style="background-color:#FFF;padding:2px 30px"| <tex>false</tex>
 
|}
 
|}
 +
 
== Проблемы при работе с null ==
 
== Проблемы при работе с null ==
 
При работе с '''null''' в процессе разработки БД, во избежание непредвиденных ошибок, необъодимо заранее ознакомиться с тем, какие проблемы могут возникнуть.
 
При работе с '''null''' в процессе разработки БД, во избежание непредвиденных ошибок, необъодимо заранее ознакомиться с тем, какие проблемы могут возникнуть.
=== Неоднозначность импликации ===
+
=== Вывод логических выражений ===
TODO: уточнить
+
В новой тернарной логике работают не все правила преобразований, присущие двоичной.
* A → B = (not A) or B
+
Например, нельзя полагать, что <tex>(A\ \vee\ \neg\ A)</tex> всегда истинно, потому что теперь может получиться ''unknown''. <br>
{| style="background-color:#CCC;margin-left:2rem;text-align:center"
+
Поэтому при каждом преобразовании троичного логического выражения, лучше сверяться с таблицами истинности.
!style="background-color:#EEE;color:#00F"| <tex>\bf{(not\ A)\ or\ B}</tex>
 
!style="background-color:#EEE"| <tex>\bf{true}</tex>
 
!style="background-color:#EEE"| <tex>\bf{unknown}</tex>
 
!style="background-color:#EEE"| <tex>\bf{false}</tex>
 
|-
 
|style="background-color:#EEE;padding:2px 30px"| <tex>\bf{true}</tex>
 
|style="background-color:#FFF;padding:2px 30px"| <tex>true</tex>
 
|style="background-color:#FFF;padding:2px 30px"| <tex>unknown</tex>
 
|style="background-color:#FFF;padding:2px 30px"| <tex>false</tex>
 
|-
 
|style="background-color:#EEE;padding:2px 30px"| <tex>\bf{unknown}</tex>
 
|style="background-color:#FFF;padding:2px 30px"| <tex>true</tex>
 
|style="background-color:#FFF;padding:2px 30px"| <tex>unknown</tex>
 
|style="background-color:#FFF;padding:2px 30px"| <tex>unknown</tex>
 
|-
 
|style="background-color:#EEE;padding:2px 30px"| <tex>\bf{false}</tex>
 
|style="background-color:#FFF;padding:2px 30px"| <tex>true</tex>
 
|style="background-color:#FFF;padding:2px 30px"| <tex>true</tex>
 
|style="background-color:#FFF;padding:2px 30px"| <tex>true</tex>
 
|}
 
 
 
* A → B = not (A and not B)
 
{| style="background-color:#CCC;margin-left:2rem;text-align:center"
 
!style="background-color:#EEE;color:#00F"| <tex>\bf{not\ (A\ and\ not\ B)}</tex>
 
!style="background-color:#EEE"| <tex>\bf{true}</tex>
 
!style="background-color:#EEE"| <tex>\bf{unknown}</tex>
 
!style="background-color:#EEE"| <tex>\bf{false}</tex>
 
|-
 
|style="background-color:#EEE;padding:2px 30px"| <tex>\bf{true}</tex>
 
|style="background-color:#FFF;padding:2px 30px"| <tex>true</tex>
 
|style="background-color:#FFF;padding:2px 30px"| <tex>unknown</tex>
 
|style="background-color:#FFF;padding:2px 30px"| <tex>false</tex>
 
|-
 
|style="background-color:#EEE;padding:2px 30px"| <tex>\bf{unknown}</tex>
 
|style="background-color:#FFF;padding:2px 30px"| <tex>true</tex>
 
|style="background-color:#FFF;padding:2px 30px"| <tex>unknown</tex>
 
|style="background-color:#FFF;padding:2px 30px"| <tex>unknown</tex>
 
|-
 
|style="background-color:#EEE;padding:2px 30px"| <tex>\bf{false}</tex>
 
|style="background-color:#FFF;padding:2px 30px"| <tex>true</tex>
 
|style="background-color:#FFF;padding:2px 30px"| <tex>true</tex>
 
|style="background-color:#FFF;padding:2px 30px"| <tex>true</tex>
 
|}
 
Оказывается что законы Де Моргана в данном случае '''не работают'''.
 
  
 
=== Скалярные операции, порождающие null ===
 
=== Скалярные операции, порождающие null ===
 
Следующие операции с '''null''' порождают '''null''', и иногда это может сбивать с толку начинающих разработчиков.
 
Следующие операции с '''null''' порождают '''null''', и иногда это может сбивать с толку начинающих разработчиков.
* =, <>, <, <=, >, >=
+
* <tex>=</tex>, <tex><></tex>, <tex><</tex>, <tex><=</tex>, <tex>></tex>, <tex>>=</tex>
* +, −, *, /
+
* <tex>+</tex>, <tex></tex>, <tex>*</tex>,<tex>/</tex>
* ||
+
* <tex>\|</tex>
* in
+
* <tex>in</tex>
 
Рассмотрим несколько примеров.
 
Рассмотрим несколько примеров.
 
  <font color = blue>select</font> (1 + <font color = blue>null</font>) <font color = blue>from</font> Students;
 
  <font color = blue>select</font> (1 + <font color = blue>null</font>) <font color = blue>from</font> Students;
Не смотря на то, что этот запрос не несет большого смысла, на его примере можно убедиться, что в арифместических операциях '''null''' "заразен".<br>
+
Не смотря на то, что этот запрос не несет большого смысла, на его примере можно убедиться, что в арифметических операциях '''null''' "заразен".<br>
  
 
  <font color = blue>select</font> StudentId <font color = blue>from</font> Students where GroupId = null;
 
  <font color = blue>select</font> StudentId <font color = blue>from</font> Students where GroupId = null;
Это частая ошибка, сравнение с '''''null''''' дает ''unknown'', а значит запрос вернет пустую таблицу.
+
Это частая ошибка, сравнение с '''''null''''' дает ''unknown'', а значит запрос вернет пустую таблицу.<br>
 +
 
 +
Говоря об операции сравнения, стоит отметить, что она не транзитивна и не рефлексивна.
 +
* <tex>x\ =\ x</tex> - ''true'' или '''''null'''''
 +
* <tex>x\ <>\ x</tex> - ''true'' или '''''null'''''
 +
* <tex>x\ or\ x</tex> - ''true'' или '''''null'''''
 +
* <tex>x\ or\ not\ x</tex> - ''true'' или '''''null'''''
 +
* <tex>x\ and\ not\ x</tex> - ''false'' или '''''null'''''
  
 
=== Дубликаты и null ===
 
=== Дубликаты и null ===
Строка 214: Строка 176:
 
* <tex>R \bowtie R</tex> - не всегда <tex>R</tex>  
 
* <tex>R \bowtie R</tex> - не всегда <tex>R</tex>  
  
 +
=== Неинтуитивность null ===
 +
Рассмотрим запрос, для нахождения студентов не из группы 'M34391'.
 +
<font color = blue>select</font> * <font color = blue>from</font> Students <font color = blue>where</font> GroupId <> <font color = green>'M34391'</font>
 +
Корректность запроса зависит от смысла '''null'''. Неясно, надо ли возвращать в этом запросе студента, о котором нет информации, в какой группе он учится.
  
===== coalesce(v1, v2, ...) =====
+
Следующий запрос, хоть и выглядит странно, предполагает просто поиск всевозможных студентов
* Принимает произвольное число аргументов и возвращает первый не null
+
<font color = blue>select</font> * <font color = blue>from</font> Students <font color = blue>where</font> GroupId <> <font color = green>'M34391'</font>
* Если все аргументы null - возвращает '''null'''
+
<font color = blue>union</font>
 +
<font color = blue>select</font> * <font color = blue>from</font> Students <font color = blue>where</font> GroupId = <font color = green>'M34391'</font>
 +
Но из-за наличия '''null''', этот запрос не отработает так, как предполагалось. Если ''GroupId'' студента '''''null''''', то сравнение не вернет ''true'', а значит в результате это учтено не будет. <br>
 +
Подробнее у работе функции <font color = blue>where</font> будет рассказано в следующем разделе.
 +
 
 +
== Работа с null в SQL ==
 +
Несмотря на множество проблем, описанных выше, в SQL существуют механизмы, позволяющие корректно обработать '''null'''.
 +
=== Проверки значений ===
 +
Для сравнения с '''null''' используется <font color = blue>is null</font> (или <font color = blue>is not null</font>).
 +
Получить всех студентов с '''''null''''' в поле ''GroupId'' можно следующим образом:
 +
<font color = blue>select</font> StudentId <font color = blue>from</font> Students <font color = blue>where</font> GroupId <font color = blue>is null</font>;
 +
В общем виде синтаксис проверки значений выглядит следующим образом:
 +
<font color = red>значение</font> <font color = blue>is</font> <font color = red>[</font><font color = blue>not</font><font color = red>]</font> <font color = red>{</font><font color = blue>null</font>|true|false|unknown<font color = red>}</font>, например:
 +
* x <font color = blue>is not</font> true
 +
* x <font color = blue>or</font> x <font color = blue>is not null</font>
  
=== Спецэффекты ===
+
Так же в SQL существует функция '''''coalesce(v1, v2, ...)''''', которая принимает произвольное число аргументов и возвращает первый не '''не null'''. Если все аргументы '''null''', то возвращает '''null'''.
При детальном рассмотрении, оказывается, что сравнение не транзитивно и не рефлексивно.
 
* ''x = x'' - true или null
 
* ''x <> x'' - true или null
 
* ''x or x'' - true или null
 
* ''x or not x'' - true или null
 
* ''x and not x'' - false или null
 
  
 
=== Ключи и null ===
 
=== Ключи и null ===
Строка 235: Строка 209:
 
'''Первичные ключи не могут содержать null.'''
 
'''Первичные ключи не могут содержать null.'''
  
== Неинтуитивность null ==
 
=== Студенты, учащиеся в группе M34391 ===
 
<font color = blue>select</font> * <font color = blue>from</font> Students <font color = blue>where</font> GroupId <> <font color = green>'M34391'</font>
 
Корректность запроса зависит от смысла '''null'''. Неясно, надо ли возвращать в этом запросе студента, о котором нет информации, в какой группе он учится.
 
 
=== Поиск всех студентов ===
 
<font color = blue>select</font> * <font color = blue>from</font> Students <font color = blue>where</font> GroupId <> <font color = green>'M34391'</font>
 
<font color = blue>union</font>
 
<font color = blue>select</font> * <font color = blue>from</font> Students <font color = blue>where</font> GroupId <> <font color = green>'M34391'</font>
 
Из-за наличия '''null''', этот запрос не отработает так, как предполагалось.
 
 
== Работа с null в SQL ==
 
 
=== Предикаты ===
 
=== Предикаты ===
 
==== DML ====
 
==== DML ====
 
<font color = blue>where</font> и <font color = blue>having</font> считают истинным предикат '''только если он вернул ''true'''''
 
<font color = blue>where</font> и <font color = blue>having</font> считают истинным предикат '''только если он вернул ''true'''''
 +
Зная этот факт можно, например убедиться, что ''false'' <font color = blue>and</font> ''unknown'' дает ''false''. Следующий запрос вернет 1:
 +
<font color = blue>select</font> 1 <font color = blue>where not</font> (0 = 1 <font color = blue>and</font> 0 = <font color = blue>null</font>)
 +
 
==== DDL ====
 
==== DDL ====
 
C точки зрения <font color = blue>check</font> constraint-ов не подходит только ''false''. ''Unknown'' превращается в ''true''
 
C точки зрения <font color = blue>check</font> constraint-ов не подходит только ''false''. ''Unknown'' превращается в ''true''
Строка 258: Строка 223:
  
 
=== Типы столбцов ===
 
=== Типы столбцов ===
* <font color = blue>nullable</font> (по умолчанию)
+
В SQL столбцы могут быть <font color = blue>nullable</font> (по умолчанию) и не <font color = blue>nullable</font>
** <font color = gray>birthday date</font>
+
<font color>birthday date</font>
* <font color = blue>Не nullable</font>
+
<font color>birthday date</font> <font color = blue>not null</font>
** <font color = gray>birthday date</font> <font color = blue>not null</font>
 
  
=== Проверки значений ===
+
Перед созданием <font color = blue>nullable</font> столбца, рекомендуется дополнительно обдумать, какой конкретно смысл вкладывается в '''null''' в данном случае, не скажется ли это негативно на остальных запросах, в случае, если начать его использовать. Если есть возможность, во избежание дополнительных проблем, описанных выше, лучше объявлять столбцы '''not null'''.
==== Синтаксис ====
 
*значение is [not] {null|true|false|unknown}
 
==== Примеры ====
 
* x is null
 
* x is not true
 
* (x or x) is not null
 
  
 
=== Прочее ===
 
=== Прочее ===
exists, Агрегирующие функции, Order by
+
* <font color = blue>exists</font>
 +
** возвращает ''true'' или 'false'
 +
** если внутри получились только строки, состоящие из '''null''', то вернет так же ''false''
 +
* агрегирующие функции (<font color = blue>count</font>, <font color = blue>sum</font>, <font color = blue>avg</font> и т.д.)
 +
** пропускают '''''null''''', т.е. не учитывают его при подсчете
 +
** при отсутствии аргументов, отличных от '''''null''''', возвращают '''''null'''''
 +
** исключением является <font color = blue>count</font>(*), что просто считает количество строк
 +
* <font color = blue>order by</font>
 +
** Помимо указаний порядка сортировки (asc или desc), можно указывать, куда ставить '''''null'''-ы'' - в начало или в конец. Например <font color = blue>order by</font> year nulls first. По умолчанию '''''null'''-ы'' складываются либо в начало, либо в конец, это нужно уточнять в документации к конкретной СУБД.

Текущая версия на 19:27, 4 сентября 2022

Что означает null

Представим себе таблицы в БД университета ИТМО

  • Students(StudentId, StudentName, GroupId)
  • Groups(GroupId, GroupName)

Что может означать тот факт, что у студента null в столбце GroupId?

  • Значение неизвестно (нет информации, из какой группы студент)
  • Значение неверно (студент учится в какой-то группе, но эта группа не представлена в БД)
  • Значение еще/уже не существует (студент был зачислен, но еще не распределен в группу или уже отчислен)
  • Значение не имеет смысла (студент из другого университета, который пришел с какими-то целями в ИТМО)
  • Значение недоступно (недостаточно прав узнать группу)

На основе этих предположений можно сделать вывод, что значение null сильно зависит от контекста (какую предметную область мы моделируем итд.).
Вполне возможно, что возникнет необходимость различать разные виды того, что значение в том или ином смысле отсутствует.

Можно ли обойтись без null?

Как представить кортеж с неопределенными частями в нашем случае?

  • Разбить на 2 группы и сделать необязательную связь 1:1. В таком случае, в дополнительной таблице будет запись (StudentId, GroupId) тогда и только тогда, когда у студента определена группа

Где еще появляется null

  • Результаты внешних соединений
  • Результаты множественных операций

Оказывается, что в некоторых случаях без null не обойтись и надо уметь с ним работать.

Тернарная логика с использованием null

С точки зрения SQL, результат логического выражения может быть true, false или unknown.
С другой стороны есть тип boolean, и у него есть 3 значения: true, false и null
То есть формально unknown - это результат вычисления, а null - это конкретное значение, которое может быть записано в БД. На практике unknown представляется значением null, и это различие не будет иметь большого значения.

Конъюнкция

[math]\bf{and}[/math] [math]\bf{true}[/math] [math]\bf{unknown}[/math] [math]\bf{false}[/math]
[math]\bf{true}[/math] [math]true[/math] [math]unknown[/math] [math]false[/math]
[math]\bf{unknown}[/math] [math]unknown[/math] [math]unknown[/math] [math]false[/math]
[math]\bf{false}[/math] [math]false[/math] [math]false[/math] [math]false[/math]

Дизъюнкция

[math]\bf{or}[/math] [math]\bf{true}[/math] [math]\bf{unknown}[/math] [math]\bf{false}[/math]
[math]\bf{true}[/math] [math]true[/math] [math]true[/math] [math]true[/math]
[math]\bf{unknown}[/math] [math]true[/math] [math]unknown[/math] [math]unknown[/math]
[math]\bf{false}[/math] [math]true[/math] [math]unknown[/math] [math]false[/math]

Отрицание

[math][/math] [math]\bf{true}[/math] [math]\bf{unknown}[/math] [math]\bf{false}[/math]
[math]\bf{not}[/math] [math]false[/math] [math]unknown[/math] [math]true[/math]

Сравнение

Равенство

[math]\bf{=}[/math] [math]\bf{true}[/math] [math]\bf{unknown}[/math] [math]\bf{false}[/math]
[math]\bf{true}[/math] [math]true[/math] [math]unknown[/math] [math]false[/math]
[math]\bf{unknown}[/math] [math]unknown[/math] [math]unknown[/math] [math]unknown[/math]
[math]\bf{false}[/math] [math]false[/math] [math]unknown[/math] [math]true[/math]

is

[math][/math] [math]\bf{true}[/math] [math]\bf{unknown}[/math] [math]\bf{false}[/math]
[math]\bf{is\ true}[/math] [math]true[/math] [math]false[/math] [math]false[/math]
[math]\bf{is\ unknown}[/math] [math]false[/math] [math]true[/math] [math]false[/math]
[math]\bf{is\ false}[/math] [math]false[/math] [math]false[/math] [math]true[/math]
[math]\bf{is\ not\ true}[/math] [math]false[/math] [math]true[/math] [math]true[/math]
[math]\bf{is\ not\ unknown}[/math] [math]true[/math] [math]false[/math] [math]true[/math]
[math]\bf{is\ not\ false}[/math] [math]true[/math] [math]true[/math] [math]false[/math]

Проблемы при работе с null

При работе с null в процессе разработки БД, во избежание непредвиденных ошибок, необъодимо заранее ознакомиться с тем, какие проблемы могут возникнуть.

Вывод логических выражений

В новой тернарной логике работают не все правила преобразований, присущие двоичной. Например, нельзя полагать, что [math](A\ \vee\ \neg\ A)[/math] всегда истинно, потому что теперь может получиться unknown.
Поэтому при каждом преобразовании троичного логического выражения, лучше сверяться с таблицами истинности.

Скалярные операции, порождающие null

Следующие операции с null порождают null, и иногда это может сбивать с толку начинающих разработчиков.

  • [math]=[/math], [math]\lt \gt [/math], [math]\lt [/math], [math]\lt =[/math], [math]\gt [/math], [math]\gt =[/math]
  • [math]+[/math], [math]−[/math], [math]*[/math],[math]/[/math]
  • [math]\|[/math]
  • [math]in[/math]

Рассмотрим несколько примеров.

select (1 + null) from Students;

Не смотря на то, что этот запрос не несет большого смысла, на его примере можно убедиться, что в арифметических операциях null "заразен".

select StudentId from Students where GroupId = null;

Это частая ошибка, сравнение с null дает unknown, а значит запрос вернет пустую таблицу.

Говоря об операции сравнения, стоит отметить, что она не транзитивна и не рефлексивна.

  • [math]x\ =\ x[/math] - true или null
  • [math]x\ \lt \gt \ x[/math] - true или null
  • [math]x\ or\ x[/math] - true или null
  • [math]x\ or\ not\ x[/math] - true или null
  • [math]x\ and\ not\ x[/math] - false или null

Дубликаты и null

Так как null ≠ null, сравнения кортежей, содержащих null не обладают интуитивными свойствами, например:

  • [math]R \cup R[/math] - не всегда [math]R[/math]
  • [math]R \cap R[/math] - не всегда [math]R[/math]
  • [math]R \bowtie R[/math] - не всегда [math]R[/math]

Неинтуитивность null

Рассмотрим запрос, для нахождения студентов не из группы 'M34391'.

select * from Students where GroupId <> 'M34391'

Корректность запроса зависит от смысла null. Неясно, надо ли возвращать в этом запросе студента, о котором нет информации, в какой группе он учится.

Следующий запрос, хоть и выглядит странно, предполагает просто поиск всевозможных студентов

select * from Students where GroupId <> 'M34391'
union
select * from Students where GroupId = 'M34391'

Но из-за наличия null, этот запрос не отработает так, как предполагалось. Если GroupId студента null, то сравнение не вернет true, а значит в результате это учтено не будет.
Подробнее у работе функции where будет рассказано в следующем разделе.

Работа с null в SQL

Несмотря на множество проблем, описанных выше, в SQL существуют механизмы, позволяющие корректно обработать null.

Проверки значений

Для сравнения с null используется is null (или is not null). Получить всех студентов с null в поле GroupId можно следующим образом:

select StudentId from Students where GroupId is null;

В общем виде синтаксис проверки значений выглядит следующим образом: значение is [not] {null|true|false|unknown}, например:

  • x is not true
  • x or x is not null

Так же в SQL существует функция coalesce(v1, v2, ...), которая принимает произвольное число аргументов и возвращает первый не не null. Если все аргументы null, то возвращает null.

Ключи и null

Можно использовать null:

  • Альтернативные ключи
  • Внешние ключи
    • Простые
    • Составные, отсутствующие целиком

Первичные ключи не могут содержать null.

Предикаты

DML

where и having считают истинным предикат только если он вернул true Зная этот факт можно, например убедиться, что false and unknown дает false. Следующий запрос вернет 1:

select 1 where not (0 = 1 and 0 = null)

DDL

C точки зрения check constraint-ов не подходит только false. Unknown превращается в true

Различимость

Два null не равны и не различимы. Это важно для distinct и group by
Например, кортежи (1, null) и (1, null) склеятся в случае distinct и не породят разные группы в случае group by, т.к. не различимы

Типы столбцов

В SQL столбцы могут быть nullable (по умолчанию) и не nullable

birthday date
birthday date not null

Перед созданием nullable столбца, рекомендуется дополнительно обдумать, какой конкретно смысл вкладывается в null в данном случае, не скажется ли это негативно на остальных запросах, в случае, если начать его использовать. Если есть возможность, во избежание дополнительных проблем, описанных выше, лучше объявлять столбцы not null.

Прочее

  • exists
    • возвращает true или 'false'
    • если внутри получились только строки, состоящие из null, то вернет так же false
  • агрегирующие функции (count, sum, avg и т.д.)
    • пропускают null, т.е. не учитывают его при подсчете
    • при отсутствии аргументов, отличных от null, возвращают null
    • исключением является count(*), что просто считает количество строк
  • order by
    • Помимо указаний порядка сортировки (asc или desc), можно указывать, куда ставить null - в начало или в конец. Например order by year nulls first. По умолчанию null складываются либо в начало, либо в конец, это нужно уточнять в документации к конкретной СУБД.