Ошибка запроса null - Исправление ошибок и поиск оптимальных решений проблем

Время прочтения
8 мин

Просмотры 12K

Навскидку многим кажется, что они знакомы с поведением NULL-значений в PostgreSQL, однако иногда неопределённые значения преподносят сюрпризы. Мы с коллегами написали статью на основе моего доклада с PGConf.Russia 2022 — он был полностью посвящён особенностям обработки NULL-значений в Postgres.

NULL простыми словами

Что такое SQL база данных? Согласно одному из определений, это просто набор взаимосвязанных таблиц. А что такое NULL? Обратимся к простому бытовому примеру: все мы задаём друг другу дежурный вопрос: «Как дела?». Часто мы получаем в ответ: «Да ничего…» Вот это «ничего» нам и нужно положить в базу данных — NULL: неопределённое, некорректное или неизвестное значение.

Допустим, вы суммируете две колонки, и в сотой по счёту записи наткнулись на NULL. Что тогда делать? Или возвращать ошибку, потому что так нельзя, или всё-таки как-то выполнить сложение и идти дальше. Сообщество решило в пользу второго варианта и закрепило это в стандартах языка SQL. Также договорились, что данные любого типа могут оказаться NULL, написали специальные функции и операции для обработки NULL-значений.

NULL может оказаться в столбце с любым типом данных и попасть на вход к любому оператору или функции. Соответственно, все операторы и функции как-то обрабатывают NULL, но результат обработки иногда оказывается неожиданным.

Какие значения не являются NULL?

Давайте теперь посмотрим, что не есть NULL. Ноль — это просто ноль, не NULL. Пустая строка — это пустая строка в Postgres, в отличие от Oracle. Пустой массив, пустой JSON, массив NULL-значений, пустой диапазон — это не NULL. Сложные типы, включающие NULL, уже не являются NULL.

Есть, правда, одно исключение: запись, собранная из NULL-значений, является NULL. Это сделано для совместимости со стандартом языка SQL. Однако, «под капотом» Postgres функции и операторы считают запись, состоящую из NULL-значений, NOT NULL. Ниже приведены результаты обработки такой записи для некоторых из них:

сount(row(NULL)) посчитает такую запись;

num_nulls(row(NULL)) выдаст ноль;

row(NULL) IS DISTINCT FROM NULL выдаст TRUE.

Ещё удивительнее пример с записями, содержащими NULL:

row(NULL::int, ‘Bob’::TEXT) IS NULL ожидаемо выдаст FALSE, но

row(NULL::int, ‘Bob’::TEXT) IS NOT NULL тоже выдаст FALSE!

Тем не менее, это поведение не является багом и описано в документации.

Операции с NULL

Почти все бинарные операции с NULL — сложить, вычесть, умножить, конкатенировать — дают на выходе NULL. С этим стоит быть осторожнее. Если вы к строке или к JSON конкатенируете что-то, оказавшееся NULL, то получаете на выходе NULL. А если вы ещё и сделали UPDATE в базу данных, выйдет совсем нехорошо.

Тем не менее, логическая операция TRUE OR NULL на выходе даёт TRUE. FALSE AND NULL даёт в результате FALSE. То есть существуют некоторые исключения из общего правила.

Операции сравнения

Операции сравнения — больше, меньше, больше или равно — c NULL на выходе дают NULL. При этом и сам NULL не равен самому себе. Впрочем, в PostgreSQL есть параметр transform_null_equals, который по умолчанию выключен. Если его включить, то NULL будет равен NULL.

Для проверки любого значения на NULL в Postgres предусмотрен специальный оператор — … IS NULL, … IS NOT NULL. Также может быть непривычно, что при сравнении булевых переменных с NULL или при применении оператора равенства помимо значений TRUE и FALSE возможно ещё и неизвестное значение. При этом оператор IS (NOT) UNKNOWN — это аналог IS (NOT) NULL для булевых переменных.

Операторы IS TRUE или IS FALSE для булевых переменных дают или TRUE, или FALSE. NULL в результате их применения получиться не может. Использование оператора IS TRUE позволяет писать более надёжный код, чем обычное сравнение = TRUE, которое может выдать не учтённое программистом NULL-значение и пойти «не туда».

Что если нам нужно сравнить два значения X и Y, считая, что NULL-значения равны друг другу? Можно самому написать конструкцию из логических операторов, но существует уже готовый оператор X IS (NOT) DISTINCT FROM Y. Правда, планировщик PostgreSQL плохо понимает этот оператор и может выдавать долгие планы выполнения для запросов с ним.

Cпециальные функции для работы с NULL

Обратимся к специальным функциям для работы с NULL. Всем известная coalesce возвращает первый NOT NULL аргумент. Есть nullif, есть num_nulls — этой функции можно дать сколько угодно аргументов, она посчитает количество NULL-значений. С помощью функции num_nonnulls можно посчитать NOT NULL значения.

Как правило, функции с произвольным числом аргументов игнорируют NULL. Такие функции, как greatest, concat его просто проигнорируют. При этом функция создания массивов включит NULL-значение во вновь образованный массив, за этим надо следить.

NULL и агрегатные функции

Что касается агрегатных функций, то array_agg, json_agg включают NULL в агрегат, а конкатенация строки не может вставить NULL-значение в середину строки, и поэтому она NULL игнорирует.

Статистические функции min, max, sum игнорируют NULL, а вот с выражением Count всё хитро. Count по конкретному полю посчитает только строки, где выражение NOT NULL, а вот Count со звёздочкой посчитает всё, включая NULL-значения.

Что со всем этим делать? Можно почитать в справке или потестировать, как функция обрабатывает NULL-значения. А лучше использовать выражение FILTER и в явном виде исключить все NULL-значения.

NULL и пользовательские функции

Теперь о пользовательских функциях. При создании пользовательской функции по умолчанию включен режим CALLED ON NULL INPUT, то есть при наличии NULL среди аргументов функция вызовется и будет обрабатывать это значение. Если вам это не нужно, можно использовать RETURNS NULL ON NULL INPUT либо STRICT — в этом случае функция, обнаружив NULL хотя бы в одном аргументе, сразу возвращает NULL и дальше вообще не думает — для экономии времени.

Многие системные функции в PostgreSQL определены именно как STRICT, поэтому стали возможны некоторые математические казусы. Например, NULL можно разделить на ноль, и в результате вы получите NULL — вместо ошибки деления на ноль. NULL в степени ноль тоже является NULL, хотя в математике любое число в нулевой степени, даже если это сам ноль, даёт единицу. Непонятно, правильно ли такое поведение с философской точки зрения, но вроде пока никто не жаловался.

Группировка и сортировка

Если говорить о группировке, то она считает все NULL-значения одинаковыми, так как это делает оператор IS NOT DISTINCT FROM. При сортировке есть специальные подвыражения, в которых можно указать NULLS FIRST или NULLS LAST. По умолчанию выбирается NULLS LAST, то есть считается, что неопределённые значения больше всех остальных чисел.

Сортировка работает так при создании выборки, индекса, в агрегатных функциях и оконных функциях.

NULL и записи

Когда мы формируем запись из нескольких значений, то сравниваются все NOT NULL значения. Если найдётся различие, то результат будет FALSE. Если все NOT NULL значения совпадают, и нашёлся NULL, то будет NULL.

Сравнение на больше/меньше выполняется по другим правилам. Как только попадётся не совпадающее значение, тогда оно будет больше или меньше, а если обнаружится NULL, то будет NULL.

NULL и диапазоны

С бинарными операциями разобрались, но что если у нас тернарная операция? Например, SELECT NOW BETWEEN NULL AND NULL. Получится, ожидаемо, NULL.

Однако, точно такое же выражение, сформулированное через диапазоны, неожиданно даёт TRUE. Да, с точки зрения Postgres здесь и сейчас мы находимся в неопределённом промежутке времени!

Согласно стандарту SQL, все диапазонные типы — не только временные, а все вообще —воспринимают границу NULL как бесконечность.

Я полюбопытствовал и выяснил, что промежуток от минус бесконечности до плюс бесконечности входит в промежуток от NULL до NULL, а обратное — неверно.

Выходит, что NULL здесь даже несколько больше, чем бесконечность.

Также я попытался проверить, входит ли NULL в промежуток от минус бесконечности до плюс бесконечности. Оказалось, что это неизвестно. Это контринтуитивный для меня момент: мне казалось, что полный диапазон значений от минус до плюс бесконечности должен включать в себя любое значение, в том числе и неопределённое. Но нет, в PostgreSQL это не так.

Откуда в запросах появляются NULL-значения?

Во-первых, они попадают в базу данных при вставке или обновлении и продолжают храниться в столбцах таблиц.

Во-вторых, они записываются в базу как результат подзапроса не нашедшего ни одной строки — в этом случае подзапрос вернёт вам NULL.

В-третьих, NULL-значения могут появляться в результате операции объединения LEFT JOIN.

В-четвёртых, NULL-значения появлются как результат некоторых функций при некоторых условиях.

В-пятых, их можно создать вручную, например, при использовании конструкции CASE. В каком-то хитром запросе вы можете указать, что при определённых условиях получится неизвестное значение.

Структура базы данных и NULL

Во-первых, можно запретить хранение NULL-значений в столбце. Есть специальное ограничение (constraint) NOT NULL. Крайне рекомендую так и поступать — всегда запрещать хранение NULL-значений, если только вы не планируете хранить и обрабатывать NULL именно в этом столбце.

При определении ограничения (constraint) тоже есть одна особенность: если условие возвращает NULL, это считается допустимым, и такая запись может быть вставлена.

Например, ограничение Foreign key позволяет в дочерней таблице вставить запись со ссылкой, которая является NULL. Это будет допустимо.

Ограничение CHECK (price > 0) даст вам вставить в таблицу поле для Price со значением, равным NULL.

Ограничение unique позволяет создать несколько записей со значением NULL. Правда, в PostgreSQL 14 уже появилось специальное «заклинание», которое может запретить несколько записей с NULL.

Как NULL хранится внутри записи БД?

NULL вообще не хранится среди полей записи, но если там есть хотя бы одно NULL-значение, то создаётся битовая карта неопределённых значений, которая называется t_bits. Стоит запомнить, что самое первое NULL-значение влечёт за собой создание такой карты и расход некоторого количества места.

Правда, все дополнительные NULL-значения в этой записи вы уже храните бесплатно и достаточно компактно. Если вы действительно экономите каждый байт в своей базе данных, хранение NULL — правильное занятие.

NULL и индексы

Postgres хранит NULL-значения в btree-индексах. Этим он отличается от Oracle. Также Postgres может использовать такой индекс при поиске записей по NULL-значению.

Тем не менее, хранение NULL-значений в индексе для вас бесполезно, если у вас нет такого типа запросов (они довольно редки, и их можно проверить в представлении pg_stats_statements).

Также пользы не будет при большом количестве NULL-значений в индексе, и следовательно, плохой селективности. В этом случае будет дешевле сделать последовательное сканирование таблицы, а не возиться с индексом.

Вот пример случая с большим числом NULL-значений в таблице. У вас есть внешний ключ (foreign key) на какую-то родительскую таблицу, но реальная ссылка используется редко, и в основном в дочерней таблице NULL-значения.

Или же у вас может быть какой-то хитрый функциональный индекс, который часто возвращает NULL. Здесь у нас пример по JSONB-ключу key1, а если у вас в JSON этот ключ встречается нечасто, то и большинство значений будет NULL.

Если у вас NULL-значений много, то вам поможет перестроение индекса на частичный с условием WHERE <ваше поле или выражение> IS NOT NULL. То есть мы просто выкидываем такие значения из нашего индекса. Это принесёт ряд улучшений:

во-первых, сокращается размер индекса на дисках, в том числе на репликах и бэкапах;
во-вторых, уменьшится количество записей в журнал предзаписи (WAL);
в-третьих, освободится место в оперативной памяти и улучшится кэширование.

В моей практике были случаи, когда реально удавалось сократить размер индекса на порядок.

В поиске таких индексов поможет представление pg_stats. В нём есть поле null_frac, которое показывает долю NULL-значений в столбцах таблиц. То есть с помощью этого представления можно определить, есть ли у вас кандидаты на оптимизацию.

Сценарий аккуратного переезда вполне очевиден:

создаёте новый частичный индекс;
по представлению pg_stat_user_indexes убеждаетесь, что запросы переехали на новый индекс;
удаляете старый индекс.

Выводы

Значение NULL может преподнести некоторые сюрпризы, если вы к нему не готовы.
Стоит проверить, как работают с NULL вызываемые вами функции и ваш код.
Запрещайте NULL там, где вы не планируете его использовать явным образом.
Проверяйте ваши индексы на наличие NULL-значений — возможно, за счёт оптимизаций удастся сэкономить некоторое количество памяти и ресурсов процессора.

Полезные ссылки

В статье Хаки Бенита рассматриваются как раз такие переполненные NULL-значениями индексы, есть SQL запрос для их поиска в вашей базе данных и практический результат перестроения.

Классическая статья Брюса Момжиана (Bruce Momjian) под названием «NULLs Make Things Easier?» доступна здесь.

Также рекомендуем ознакомиться с книгой Егора Рогова «PostgreSQL 14 изнутри».

Источник

Автор Allen Browne, ноябрь 1999 г.
http://allenbrowne.com/casu-12.html
Перевод: А. Артамонов, октябрь 2011г.

Вот некоторые распространенные ошибки обращения с Null -ами, которые допускают новички. Если у вас нет ясности насчет Null’ов, сначала прочитайте Nulls: Do I need them?

Ошибка № 1: Null-ы в критериях

Если вы вводите условии отбора под полем в конструкторе запросов, он возвращает только совпадающие с образцом записи. Null-ы при этом исключаются из отбора.
Например, скажем, у вас есть таблица компаний и адресов. Вам нужны два запроса: один, который выдает местные компании, другой ― все остальные. В строке условий отбора в первом запросе под полем Город вы вписываете:
«Бобруйск»
и во втором запросе:
Not «Бобруйск»
Неправильно! Ни один запрос не включит записи, у которых город Null.

Решение

Используйте Is Null. Во втором запросе, чтобы достичь желаемого результата, условия отбора должны выглядеть как:
Is Null Or Not «Бобруйск»
Примечание: Запросы DDL (языка определения данных) обращаются с Null-ами иначе. Например, Null-ы учитываются в таком типе запроса:
ALTER TABLE Table1 ADD CONSTRAINT chk1 CHECK (99 < (SELECT Count(*) FROM Table2 WHERE Table2.State <> ‘TX’));

Ошибка № 2: Null-ы в выражениях

Результатом вычислений, включающих Null, обычно является Null. Например, новички иногда вводят выражение в источник данных текстового поля, чтобы вывести остаток к оплате:
=[СуммаКОплате] — [СуммаУплаченная]
Проблема в том, что если не было оплачено ничего, СуммаУплаченная является Null-ом, и в текстовом поле ничего не отображается.

Решение

Используйте функцию Nz(), чтобы указать значение для Null-а:
= Nz([СуммаКОплате], 0) — Nz([СуммаУплаченная], 0)

Ошибка № 3: Null-ы во внешних ключах

В то время, как Аксесс запрещает Null-ы в первичных ключах, он разрешает Null-ы во внешних. В большинстве случаев стоит явно запрещать эту возможность, чтобы избежать висящих ссылок.
В типичной таблице для накладных, строки накладной хранятся в таблице НакладнаяСтроки, соединенной с таблицей Накладные по НакладнаяКод. Вы создаете связь между Накладные.НакладнаяКод и НакладнаяСтроки.НакладнаяКод с поддержкой ссылочной целостности. Этого недостаточно!
Если вы не установили свойство Обязательное поля НакладнаяКод в таблице НакладнаяСтроки на Да, Аксесс разрешает Null-ы. Чаще всего это случается, когда пользователь начинает добавлять строки в подформе, не создав сперва саму накладную в основной форме. Так как у этих записей нет соответствующей им записи в основной форме, эти висящие записи больше нигде не показываются снова. Пользователь уверен, что программа их удалила, хотя на самом деле они находятся в таблице.

Решение

Всегда выставляйте значение свойства Обязательное на Да в конструкторе таблиц, если только вам не нужна возможность иметь Null-ы во внешних ключах.

Ошибка № 4: Null-ы и типы, отличные от Variant.

В Visual Basic единственным типом данных, который может содержать Null является Variant. Когда бы вы ни присваивали значение поля в переменную с типом не-Variant, всегда нужно учитывать возможность, что в поле может содержаться Null. Посмотрите, что в этом коде модуля формы может пойти не так:
       Dim strName as String
       Dim lngID As Long
       strName = Me.Отчество
       lngID = Me.КодКлиента
Когда поле Отчество содержит Null, попытка присвоить Null переменной строкового типа породит ошибку.
Аналогично, присвоение значения КодКлиента числовой переменной может вызвать ошибку. Даже если КодКлиента является первичным ключом, код небезопасен: первичный ключ содержит Null в новой записи.

Решения

(a) Используйте тип Variant, если вам необходимо работать с Null-ами.
(b) Используйте функцию Nz(), чтобы указать значение вместо Null. Например::
strName = Nz(Me.Отчество, «»)
lngID = Nz(Me.КодКлиента, 0)

Error 5: Сравнение с Null

Выражение:
If [Фамилия] = Null Then
будет бессмысленно, так как никогда не может быть истинно. Даже если фамилия является Null-ом, VBA полагает, что вы спросили::
Является ли Неизвестное равным Неизвестному?
и всегда отвечает: “Откуда мне знать, равны ли ваши неизвестные друг другу? Здесь мы опять наблюдаем распространение Null: результат не является ни истинным ни ложным.

Решение

Используйте функцию IsNull():
If IsNull([Фамилия]) Then

Ошибка № 6: Забыли, что Null ни Истина ни Ложь.

Выполняют ли эти две конструкции одну и ту же работу?
(a)     If [Фамилия] = «Иванов» Then
           MsgBox «Это Иванов”
       Else
           MsgBox «Это не Иванов»
       End If

(b)     If [Фамилия] <> «Иванов» Then
           MsgBox «Это не Иванов»
       Else
           MsgBox «Это Иванов”
       End If
Когда фамилия является Null-ом, эти два куска кода противоречат друг другу. В обоих случаях, первая часть If не срабатывает, и начинается выполнение Else, что приводит к противоречащим друг другу сообщениям.

Решения

(a) Учитывайте все три возможных результата сравнения — True, False, и Null:
       If [Фамилия] = «Иванов» Then
           MsgBox «Это Иванов”
       ElseIf [Фамилия] <> «Иванов» Then
           MsgBox «Это не Иванов”
       Else
           MsgBox «Мы не знаем, Иванов это или нет»
       End If
(b) В некоторых случаях, функция Nz() позволит обработать два случая разом. Например, рассматривать Null и нулевую строку одинаково:
       If Len(Nz([Фамилия],»»)) = 0 Then

Источник

Dmitri446

30.11.17 — 22:15

Помогите добавить проверки на null, не могу допереть как ее сделать, точнее что вместо null выводить если он есть

hhhh

1 — 30.11.17 — 22:22

(0) используй ЕСТЬNULL

jsmith82

2 — 30.11.17 — 22:26

ЕСТЬNULL(КонтактныеЛица.РабМесто, Сотрудники.РабочееМесто)

Мисти

3 — 30.11.17 — 23:19

» Лефмихалыч

4 — 30.11.17 — 23:21

во-первых, «ПОЛНОЕ» замени на «ВНУТРЕННЕЕ»

во-вторых, в данном случае не может у тебя запрос NULL вернуть как раз потому, что соединение в любом случае внутреннее

Лефмихалыч

5 — 30.11.17 — 23:22

» vi0

6 — 01.12.17 — 05:46

(0) а что ты хочешь в результате?

какую задачу решаешь?

pasha_d

7 — 01.12.17 — 10:16

хочешь Null — используй левое соединение

dezss

8 — 01.12.17 — 11:02

(7) с фига ли?

при полном как раз будут null`ы.

HEKPOH

9 — 01.12.17 — 11:03

(8) и при полном, и при левом, и при правом

Ненавижу 1С

10 — 01.12.17 — 11:04

(9) иногда NULL бывает вообще в одной таблице без соединений

Ц_У

11 — 01.12.17 — 11:07

(3) переведу (5)

у вас профиль скучный

hhhh

12 — 01.12.17 — 11:09

(8) имеется в виду, что при условии

|ГДЕ

| (Сотрудники.ФизЛицо.Ссылка = &Ссылка

| ИЛИ КонтактныеЛица.ФизЛицо.Ссылка = &Ссылка)

не будет null

dezss

13 — 01.12.17 — 11:13

(12) а если полное переписать на левое, то типа будут?)))

При таком условии будут null`ы, если в одном из справочников нет ссылки на это физ. лицо.

dezss

14 — 01.12.17 — 11:15

(13) + кстати, КонтактныеЛица.Владелец тоже может быть null`ом)

hhhh

15 — 01.12.17 — 11:17

(13) null-ов не будет, они отбросятся при выполнении условия в ГДЕ. Об этом и говорит (4)

Buster007

16 — 01.12.17 — 11:19

(3) это потому, что у тебя фотки нет )

dezss

17 — 01.12.17 — 11:19

(15) и куда ж они отбросятся при соединении?

что будет в КонтактныеЛица.Владелец, если КонтактныеЛица.ФизЛицо.Ссылка = &Ссылка будет ложью?

Dmitrii

18 — 01.12.17 — 11:21

(15) >> null-ов не будет, они отбросятся

Перечитай еще раз запрос.

Ничего там не отбросится

hhhh

19 — 01.12.17 — 11:21

(17) этой строчки вообще не будет, она не пройдет через ГДЕ

hhhh

20 — 01.12.17 — 11:22

(18) всё отбросится, это следует из аксиомы

Х ИЛИ NULL = ЛОЖЬ

dezss

21 — 01.12.17 — 11:23

(19) т.е. ты хочешь сказать, что КонтактныеЛица.Владелец, который описан после ВЫБРАТЬ не будет присутствовать в результате запроса? О_о

мда…пятница, кому-то пора отдохнуть)))

dezss

22 — 01.12.17 — 11:23

(20) е-мое….

т.е. ты хочешь сказать, что условие КонтактныеЛица.ФизЛицо.Ссылка = &Ссылка вернет Null? О_О

undertaker

23 — 01.12.17 — 11:24

(11) а у Dmitri446 он не скучный? о_О

dezss

24 — 01.12.17 — 11:26

(23) ОФФ: он мужик…кто будет просить фотку у мужика)))

Dmitrii

25 — 01.12.17 — 11:27

(4) >> во-первых, «ПОЛНОЕ» замени на «ВНУТРЕННЕЕ»

Сильно сомневаюсь, что тут нужно ВНУТРЕННЕЕ соединение.

Скорее всего, ЛЕВОЕ. Но оно не избавит от NULL.

dezss

26 — 01.12.17 — 11:28

(25) а разве в постановке вопроса была задача избавиться от null`а?

вроде ж спросили только про проверку и в (1) и (2) получили ответ на вопрос

Dmitri446

27 — 01.12.17 — 11:28

Переделал под левое, но с NULL до сих пор чет не врублюсь

dezss

28 — 01.12.17 — 11:30

(27) в чем конкретно косяк?

какие тебя не устраивают данные?

Dmitrii

29 — 01.12.17 — 11:32

(27) >> Переделал под левое

Где? В запросе везде ПОЛНОЕ.

У тебя каша в голове.

Скажу уже наконец — что тебе именно надо.

hhhh

30 — 01.12.17 — 11:32

(27) так может

   |ГДЕ

    |    Сотрудники.ФизЛицо.Ссылка = &Ссылка

    |    И НЕ КонтактныеЛица.ФизЛицо ЕСТЬ NULL

Ненавижу 1С

31 — 01.12.17 — 11:33

(27)

Сотрудники.ФизЛицо.Ссылка = &Ссылка

И НЕ Сотрудники.ФизЛицо ЕСТЬ NULL

это ПЯТНИЦА!

Dmitri446

32 — 01.12.17 — 11:33

Не тот код скинул…

В общем мне нужно при выборе физ лица если у него есть контактное лицо выбрать раб место контактного лица, если контактного лица нету то выбрать рабочее место сотрудника и наоборот

Ц_У

33 — 01.12.17 — 11:34

(23) от пола зависит

Dmitri446

34 — 01.12.17 — 11:34

ВЫБРАТЬ

        КонтактныеЛица.Владелец,

        ВЫБОР

            КОГДА КонтактныеЛица.РабМесто <> ЗНАЧЕНИЕ(Справочник.ИТ_РабочиеМеста.ПустаяСсылка)

                ТОГДА КонтактныеЛица.РабМесто

            ИНАЧЕ Сотрудники.РабочееМесто

        КОНЕЦ КАК РабочееМесто

    ИЗ

        Справочник.ИТ_КонтактныеЛица КАК КонтактныеЛица

            ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Сотрудники КАК Сотрудники

            ПО КонтактныеЛица.ФизЛицо = Сотрудники.ФизЛицо

    ГДЕ

        Сотрудники.ФизЛицо.Ссылка = &Ссылка

        И НЕ Сотрудники.ФизЛицо ЕСТЬ NULL

ОБЪЕДИНИТЬ

    ВЫБРАТЬ

        КонтактныеЛица.Владелец,

        ВЫБОР

            КОГДА КонтактныеЛица.РабМесто <> ЗНАЧЕНИЕ(Справочник.ИТ_РабочиеМеста.ПустаяСсылка)

                ТОГДА КонтактныеЛица.РабМесто

            ИНАЧЕ Сотрудники.РабочееМесто

        КОНЕЦ

    ИЗ

        Справочник.ИТ_КонтактныеЛица КАК КонтактныеЛица

            ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Сотрудники КАК Сотрудники

            ПО КонтактныеЛица.ФизЛицо = Сотрудники.ФизЛицо

    ГДЕ

        КонтактныеЛица.ФизЛицо.Ссылка = &Ссылка

        И НЕ Сотрудники.ФизЛицо ЕСТЬ NULL

hhhh

35 — 01.12.17 — 11:35

(29) условие в ГДЕ

|ГДЕ

| (Сотрудники.ФизЛицо.Ссылка = &Ссылка

вревращает ПОЛНОЕ СОЕДИНЕНИЕ в ЛЕВОЕ

идем дальше, вторая строчка

| ИЛИ КонтактныеЛица.ФизЛицо.Ссылка = &Ссылка)

превращает ЛЕВОЕ СОЕДИНЕНИЕ во ВНУТРЕННЕЕ.

то есть в запросе (0) фактически внутреннее соединение ????

dezss

36 — 01.12.17 — 11:42

(35) Не совсем так. Не забывай, тут ИЛИ, а не И. Если было бы И, то ты был бы прав.

Вместо

       ВЫБОР

            КОГДА КонтактныеЛица.РабМесто <> ЗНАЧЕНИЕ(Справочник.ИТ_РабочиеМеста.ПустаяСсылка)

                ТОГДА КонтактныеЛица.РабМесто

            ИНАЧЕ Сотрудники.РабочееМесто

        КОНЕЦ

Напиши

ЕстьNull(КонтактныеЛица.РабМесто,Сотрудники.РабочееМесто).

Но если оба будут null, то тут тоже будет null.

И, кстати ЗНАЧЕНИЕ(Справочник.ИТ_РабочиеМеста.ПустаяСсылка) <> Null, запомни это очень хорошо!!!

dezss

37 — 01.12.17 — 11:44

(36) Вторая часть, которая после «Вместо…» для (34)

Dmitrii

38 — 01.12.17 — 11:45

(35) Если бы тыл прав, то проблемы в (0) не было бы.

А если вникнуть в текст условия ГДЕ, то ты увидишь там «ИЛИ». То есть никакого превращение во ВНУТРЕННЕЕ соединение не произойдёт.

dezss

39 — 01.12.17 — 11:47

(0) Блин, проще написать, чем объяснить ТС-у…может сам разберется)))

    |ВЫБРАТЬ

    |    ЕСТЬNULL(КонтактныеЛица.Владелец, ЗНАЧЕНИЕ(Справочник.ИТ_КонтактныеЛица.ПустаяСсылка)) КАК Владелец,

    |    ЕстьNull(КонтактныеЛица.РабМесто,Сотрудники.РабочееМесто) КАК РабочееМесто

    |ИЗ

    |    Справочник.ИТ_КонтактныеЛица КАК КонтактныеЛица

    |        ПОЛНОЕ СОЕДИНЕНИЕ Справочник.Сотрудники КАК Сотрудники

    |        ПО КонтактныеЛица.ФизЛицо = Сотрудники.ФизЛицо

    |ГДЕ

    |    Сотрудники.ФизЛицо.Ссылка = &Ссылка

    |    ИЛИ КонтактныеЛица.ФизЛицо.Ссылка = &Ссылка

MaxS

40 — 01.12.17 — 12:09

Зачем нужны эти ФизЛицо.Ссылка = &Ссылка ?

почему не ФизЛицо = &Ссылка ?

dezss

41 — 01.12.17 — 12:13

(40) нафиг не нужны…я просто копипастил из запроса тс

МимохожийОднако

42 — 01.12.17 — 12:20

(3) ОФФ: Правда матку режет.

Источник

Собственно, вот. Задача 2.2 1с специалист. Виновник проблемы вот этот код. В результате запроса Null при любом варианте. Вдруг кто-нибудь сможет помочь.

Процедура ОбработкаПроведения(Отказ, РежимПроведения)
Движения.Хозрасчеты.Записывать = Истина;

   //Блокировки
   Блокировка = Новый БлокировкаДанных;
   ЭлементБлокировки = Блокировка.Добавить(«РегистрБухгалтерии.Хозрасчеты»);
   ЭлементБлокировки.УстановитьЗначение(«Счет»,ПланыСчетов.Хозрасчеты.Покупатели);
   ЭлементБлокировки.УстановитьЗначение(«Субконто1»,Контрагент);
   ЭлементБлокировки.Режим = РежимБлокировкиДанных.Исключительный;
   Блокировка.Заблокировать();

Движения.Хозрасчеты.Записать();

//Запрос к регистру бухгалтерии
   Запрос = Новый Запрос;
   Запрос.Текст =
      «ВЫБРАТЬ
      |   ХозрасчетыОстатки.Субконто2 КАК Договор,
      |   ХозрасчетыОстатки.Субконто3 КАК РасходнаяНакладная,
      |   ЕСТЬNULL(ХозрасчетыОстатки.ОтгрузкаОстатокДт, 0) КАК ДолгКонтрагента,
      |   ЕСТЬNULL(ХозрасчетыОстатки.ОплатаОстатокДт, 0) КАК ОплаченоКонтрагентом
      |ИЗ
      |   РегистрБухгалтерии.Хозрасчеты.Остатки(&МоментВремени, Счет = ЗНАЧЕНИЕ(ПланСчетов.Хозрасчеты.Покупатели), &ВидыСубконто, Субконто1 = &Контрагент) КАК ХозрасчетыОстатки
      |
      |УПОРЯДОЧИТЬ ПО
      |   ХозрасчетыОстатки.Субконто2.ДатаОкончания,
      |   ХозрасчетыОстатки.Субконто3.МоментВремени»;
   Запрос.УстановитьПараметр(«ВидыСубконто», ПланыСчетов.Хозрасчеты.Покупатели.ВидыСубконто.ВыгрузитьКолонку(«ВидСубконто»));
   Запрос.УстановитьПараметр(«Контрагент», Контрагент);
   Запрос.УстановитьПараметр(«МоментВремени», МоментВремени());
//Какая то ошибка в запросе, во всех полях null
   Результат = Запрос.Выполнить();

      Выборка = Результат.Выбрать(ОбходРезультатаЗапроса.ПоГруппировкам);
      //Проверка не больше ли сумма оплаты, чем долг
      //Запрос возвращает null, поэтому я не уверен можно ли использовать Итог
   Если Сумма > (Выборка.ДолгКонтрагента.Итог — Выборка.ОплаченоКонтрагентом.Итог) Тогда
      Сообщить(«Сумма оплаты » + Строка(Сумма)+ » превышает долг конрагента «+Строка(Выборка.ДолгКонтрагента.Итог — Выборка.ОплаченоКонтрагентом.Итог));
      Отказ = Истина;
   КонецЕсли;

        //Из реквизита документа
         СуммаПлатежа = Сумма;
   //В цикле для выборки
   Пока Выборка.Следующий() Цикл
        //Если долг больше чем уже оплачено
         Если Выборка.ДолгКонтрагента > Выборка.ОплаченоКонтрагентом Тогда
            //выясняем сколько надо оплатить
            Оплатить = Выборка.ДолгКонтрагента — Выборка.ОплаченоКонтрагентом;
            //Оплачиваем
           Оплачено = Выборка.ОплаченоКонтрагентом + Оплатить;
            //уменьшаем сумму платежа
            СуммаПлатежа = СуммаПлатежа — Оплатить;
            //добавляем движения
            Движение = Движения.Хозрасчеты.Добавить();
            Движение.СчетДт = ПланыСчетов.Хозрасчеты.Касса;
            Движение.СчетКт = ПланыСчетов.Хозрасчеты.Покупатели;
            Движение.Период = Дата;
            Движение.Оплата = Оплачено;
            Движение.СубконтоКт[ПланыВидовХарактеристик.ВидыСубконто.Контрагенты] = Выборка.Контрагент;
            Движение.СубконтоКт[ПланыВидовХарактеристик.ВидыСубконто.Договоры] = Выборка.Договор;
            Движение.СубконтоКт[ПланыВидовХарактеристик.ВидыСубконто.РасходныеНакладные] = Выборка.РасходнаяНакладная;
            Иначе Продолжить;//Иначе переходим к следующей записи цикла
         КонецЕсли;
   КонецЦикла;

КонецПроцедуры

Источник

Содержание:

1. Тип значения Null

2. Методы Есть Null и ЕстьNull

1. Тип значения Null

Тип значения Null – это переменная или поле с отсутствующим значением. Если реквизит объекта имеет один определенный тип, например тип «Справочник.Договоры», то незаполненное значение будет равно пустой ссылке в запросе «Справочник.Договоры.ПустаяСсылка». Если реквизит имеет мультитип, то незаполненное значение данного реквизита будет равно значению «Неопределено», так как используемые в этом реквизите типы известны, но неизвестно, какой из пустых типов нужно использовать в данном случае. Для примера, в документе «ЗаказКлиента» реквизит «ДокументОснование» может быть и «ЗаданиеТорговомуПредставителю» и «КоммерческоеПредложениеКлиенту». В случаях же, когда вообще неизвестно, какие типы мы хотим получить, значением свойства является Null. В качестве примера, в котором в запросе есть Null, можно рассмотреть левое соединение таблиц, когда в присоединяемой таблице отсутствуют значения, удовлетворяющие условию соединения.

Пример кода: присоединение к таблице контрагентов таблицы договоров

Рис. 1 Левое соединение таблиц

Если у контрагента отсутствует договор, то при выполнении запроса получим следующие значения:

Как видим в запросе в 1С есть Null и это отображается пустой строкой.

В запросе сравнение любого значения с типом значения Null дает результат Ложь. Даже условие Null = Null является ложным.

2. Методы Есть Null и ЕстьNull

Для правильной обработки данных ситуаций созданы такие методы, как:

· Есть Null – это выражение условия, проверяющее, является ли выбранное значение типом значения Null. При проверке следует применять его. Сравнение вида (ДоговорКонтрагента.Ссылка = Null) не даст корректного результата.

Пример кода:

Рис. 2 Пример кода для проверки типа значения Null

Для корректной работы запроса обязательно следует отслеживать места, где есть Null в запросе 1С, и заменять данное значение на какой-то определенный тип.

· ЕстьNull
Этот метод позволяет менять значение Null на нужное нам значение.

Пример кода для запроса в 1С с ЕстьNull

Рис. 3 Код для ЕстьNull в запросе

Отсутствующую строку с помощью метода ЕстьNull в 1С можно заменить на пустую строку, отсутствующую цифру на ноль, ссылку на пустую ссылку, дату на пустую дату. Например ЕстьNull(“ДоговорыКонтрагентов.Номер”, “”), ЕстьNull(“ДоговорыКонтрагентов.СуммаДоговора”, 0), ЕстьNull(“ДоговорыКонтрагентов.Партнер”, Значение(Справочник.Партнеры.ПустаяСсылка)), ЕстьNull(“ДоговорыКонтрагентов.Дата”, ДАТАВРЕМЯ(1,1,1,0,0,0)).

Тип значения Null также может уже содержаться в используемом в запросе реквизите, попав туда в результате некорректной записи программным кодом, смене типа реквизита в конфигураторе либо при неправильном обмене данными между базами. В этих случаях тоже можно воспользоваться выражением ЕстьNull в запросе, но лучше исправлять такие реквизиты.

Специалист компании «Кодерлайн»

Александр Суворов

Источник

NULL – отсутствующие значения.
Не путать с нулевым значением! NULL – это не число, не равно пробелу, пустой ссылке, Неопределено.

NULL – типообразующее значение, т.е. есть тип NULL и единственное значение этого типа.

NULL значения появляются в запросе в следующих ситуациях:
а) Внешнее соединение, при котором не было найдено соответствующей записи в другой таблице (при левом – во второй, при правом – в первой, при полном – в обоих)
б) Обращение к реквизитам элементов для группы и наоборот.
в) NULL в списке полей выборки (ВЫБРАТЬ)
г) Обращение к реквизитам для битой ссылки

ЕСТЬ NULL используется в операторе ВЫБРАТЬ (как бы проверя, что значение это есть пустое ( Значение ЕСТЬ NULL )):

Код 1C v 8.х

 	

ВЫБОР 

 КОГДА Значение ЕСТЬ NULL ТОГДА РезультатЕслиNULL 

 ИНАЧЕ Значение 

КОНЕЦ

еще пример:

Код 1C v 8.х

  ВЫБРАТЬ

    СправочникНоменклатуры.Наименование,

    ВЫБОР КОГДА УчетНоменклатурыОстатки.КоличествоОстаток ЕСТЬ NULL ТОГДА 0 

    ИНАЧЕ УчетНоменклатурыОстатки.КоличествоОстаток КАК КоличествоОстаток

ИЗ

    Справочник.Номенклатура КАК СправочникНоменклатуры

        ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.УчетНоменклатуры.Остатки КАК УчетНоменклатурыОстатки

        ПО УчетНоменклатурыОстатки.Номенклатура = СправочникНоменклатуры.Ссылка

ГДЕ

    СправочникНоменклатуры.ЭтоГруппа = ЛОЖЬ

Функция ЕСТЬNULL (значение, РезультатЕслиNULL) возвращает значение своего первого параметра, в случае если он не равен NULL, и значение второго параметра в противном случае
Является свернутым ВЫБОР…КОНЕЦ, но ЕСТЬNULL предпочтительнее.

Код 1C v 8.х

 

ВЫБРАТЬ

	ЕСТЬNULL(Справочник.Номенклатура.Артикул, "---") КАК Артикул,

	Справочник.Номенклатура.Представление КАК Номенклатура

еще пример:

Код 1C v 8.х

 

ВЫБРАТЬ

    СправочникНоменклатуры.Наименование,

    ЕСТЬNULL(УчетНоменклатурыОстатки.КоличествоОстаток, 0) КАК КоличествоОстаток

ИЗ

    Справочник.Номенклатура КАК СправочникНоменклатуры

        ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.УчетНоменклатуры.Остатки КАК УчетНоменклатурыОстатки

        ПО УчетНоменклатурыОстатки.Номенклатура = СправочникНоменклатуры.Ссылка

ГДЕ

    СправочникНоменклатуры.ЭтоГруппа = ЛОЖЬ

В данном примере получаются все элементы справочника номенклатуры, после чего, для каждой номенклатуры из регистра накопления получаются текущие остатки. Т.к. для номенклатуры, по которой отсутствуют остатки, виртуальная таблица остатков не запись вернет, то в результате соединения в поле «УчетНоменклатурыОстатки.КоличествоОстаток» будут значения NULL для номенклатуры, по которой не было остатков. Для того чтобы вместо значения NULL в результате запроса присутствовало значение 0, мы использовали функцию ЕСТЬNULL(), которая осуществит желаемую замену.

ЕСТЬNULL отличается от ВЫБОР по следующим причинам:
а) При ЕСТЬNULL лучше читается запрос (проще)
б) При ЕСТЬNULL, если проверяется сложное выражение, то работает быстрее, поскольку вычисляется один раз
в) При ЕСТЬNULL выражение замены приводится к типу проверяемого выражения, если оно имеет тип Строка (длина) или Число (разрядность).

Нельзя проверять значения на NULL обычным равенством, потому что в SQL действует трехзначная логика – Истина, Ложь, NULL, и результатом такого сравнения будет UNKNOWN, что в 1С 8.0 аналогично ЛОЖЬ.
NULL <> 0, поэтому при левых внешних соединениях спр. Номенклатура с таблицами остатков, цен, Контрагентов со взаиморасчетами при отсутствии таких записей там будет NULL, который не равен 0. Лучшее решение – ЕСТЬNULL

Источник

Я не могу ничего больше добавить в этом введении. Короче говоря, вы должны использовать естественные выражения типа IS NULL или IS NOT NULL, а не какие-либо встроенные функции, доступные вам в SQL Server, например, ISNULL, COALESCE и т.п., которые являются функциями слоя представления и не имеют реляционного смысла.

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

Мастер настройки

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

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

Вторая проблема неестественных выражений связана с неявным преобразованием.

Возьмем, например.

DECLARE 

    @i int = 0;

SELECT 

    c = 

        CASE ISNULL(@i, '') 

             WHEN '' 

             THEN 1 

             ELSE 0 

        END;

Будет возвращена 1, поскольку 0 и » могут быть неявно конвертированы.

Вот менее очевидный и более редкий пример:

DECLARE 

    @d datetime = '19000101';

SELECT 

    c = 

        CASE ISNULL(@d, '') 

             WHEN '' 

             THEN 1 

             ELSE 0 

        END;

Который тоже вернет 1.

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

Если этого вам недостаточно, чтобы избавиться от такой идеи, давайте посмотрим, как все это происходит в реальном мире.

Давайте сначала создадим индекс. Без этого не будет фундаментальной разницы в производительности.

CREATE INDEX v ON dbo.Votes

    (BountyAmount);

Нашим золотым стандартом будут эти два запроса:

SELECT

    c = COUNT_BIG(*)

FROM dbo.Votes AS v

WHERE v.BountyAmount IS NULL;

SELECT

    c = COUNT_BIG(*)

FROM dbo.Votes AS v

WHERE v.BountyAmount IS NOT NULL;

Первый запрос, который проверяет на значения NULL возвращает число 182,348,084.

Второй, который проверяет значения NOT NULL возвращает число 344,070.

Запомните это.

Планы обоих запросов выглядят так:

Запросы выполняются соответственно за 846мс и 26мс. Очевидно, что запрос с более селективным предикатом будет здесь иметь преимущество по времени.

Неправильно

Здесь начинаются ошибки.

Этот запрос возвращает неправильные результаты, но вы, вероятно, привыкли к этому из-за всех этих хинтов NOLOCK в ваших запросах.

SELECT

    c = COUNT_BIG(*)

FROM dbo.Votes AS v

WHERE ISNULL(v.BountyAmount, '') = '';

Возвращаемое количество 182349088, а не 182348084, поскольку для 1004 строк bounty равно 0.

Хотя мы использовали пустую строку в наших запросах, она неявно конвертировалась в 0.

И ты думал, что такой умный.

Плохо

В бесполезных упражнениях, которые выполняют люди, я часто наблюдаю использование выражений ISNULL, COALESCE и CASE.

Здесь стоит заметить, что COALESCE — это всего лишь стоящее за кадром выражение CASE.

Для нахождения NULL люди извернутся и сделают это:

SELECT

    c = COUNT_BIG(*)

FROM dbo.Votes AS v

WHERE ISNULL(v.BountyAmount, -1) = -1;

SELECT

    c = COUNT_BIG(*)

FROM dbo.Votes AS v

WHERE COALESCE(v.BountyAmount, -1) = -1;

Мы можем использовать здесь значение -1, поскольку оно не встречается в естественных данных. Результаты корректны в обоих случаях, но сравнительная производительность ужасна.

Мы видим 2,5 секунды против 900мс. Ситуация также становится хуже при более селективных предикатах.

Они оба занимают примерно одинаковое время, что и другие неестественные формы этого запроса, но вызов естественной версии этого запроса завершится менее чем за 30мс.

Я надеюсь, что мне не придется об этом больше писать, но сейчас я вижу, как люди делают это, и уже начинаю в этом сомневаться.

Я не знаю, почему некоторые думают, что это хорошая идея. Ходят слухи, что это исходит от разработчиков приложений, которые привыкли к значениям NULL, вызывающим ошибки при написании SQL-запросов, в которых они не представляют такой же угрозы.

Кто знает. Может быть, людям просто нравится праздничный розовый цвет текста, которым окрашиваются функции в SSMS.

Источник

NULL – это не что иное, как отсутствие значения. Многие путают его со значением «0» типа число, пустой ссылкой на какой-либо объект или же с пустой строкой. Из-за этого заблуждения возникает много ошибок.

Значение NULL будет появляться в том случае, если в запросе будет обращение к несуществующему полю, свойству или к битой ссылке.

Язык запросов в 1С основан на SQL, который не позволяет проверять на значение NULL обычным равенством. Ниже описаны два способа проверки на NULL в 1С 8.3.

Функция ЕСТЬNULL()

Функция языка запросов 1С 8.3 ЕСТЬNULL() имеет два входных параметра:

проверяемое выражение;
выражение замены.

Наша команда предоставляет услуги по консультированию, настройке и внедрению 1С.

Связаться с нами можно по телефону +7 499 350 29 00.

Услуги и цены можно увидеть по ссылке.

Будем рады помочь Вам!

Если проверяемое значение будет NULL, то эта функция вернет значение выражения замены. Если же значение будет отлично от NULL, то вернется само проверяемое выражение.

Ниже рассмотрен пример. В нем выбираются все номенклатурные позиции табличной части товара из документа «Поступление товаров и услуг». При помощи левого соединения каждой номенклатуре проставляется последняя цена из регистра сведений «Цены номенклатуры».

В данном случае может возникнуть такая ситуация, что для какой-либо позиции может просто не быть цены в регистре. В таком случае функция ЕСТЬNULL вернет нам привычный ноль. Если ей не воспользоваться, то при попытке произвести арифметические операции над полем «Цена» со значением NULL мы получим ошибку.

ВЫБРАТЬ
Товары.Номенклатура КАК Товар,
ЕСТЬNULL(Цены.Цена, 0) КАК АктуальнаяЦена
ИЗ
Документ.ПоступлениеТоваровУслуг.Товары КАК Товары
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних КАК Цены
ПО Товары.Номенклатура = Цены.Номенклатура
ГДЕ
Товары.Ссылка = &СсылкаНаДокумент

ЕСТЬ NULL в операторе ВЫБОР

Аналогом функции ЕСТЬNULL() является «ЕСТЬ NULL», которая используется в операторе ВЫБОР и проверяет, является ли значение NULL. «ЕСТЬ» в данном случае подразумевает равенство и запрос предыдущего примера будет выглядеть следующим образом:

ВЫБРАТЬ
Товары.Номенклатура КАК Товар,
ВЫБОР
КОГДА Цены.Цена ЕСТЬ NULL
ТОГДА 0
ИНАЧЕ Цены.Цена
КОНЕЦ КАК АктуальнаяЦена
ИЗ
Документ.ПоступлениеТоваровУслуг.Товары КАК Товары
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних КАК Цены
ПО Товары.Номенклатура = Цены.Номенклатура
ГДЕ
Товары.Ссылка = &СсылкаНаДокумент

Отличия функции ЕСТЬNULL() от ЕСТЬ NULL

Как вы могли увидеть из предыдущих примеров, в обоих случаях запрос возвращает одни и те же данные. Функция ЕСТЬNULL() является сокращенным вариантом ВЫБОР КОГДА … ЕСТЬ NULL … КОНЕЦ, но она все же будет предпочтительнее по следующим причинам:

Функция EСТЬNULL() оптимизирует запрос. Она считывается один раз, поэтому при проверке сложного выражения, запрос отработает быстрее.
Функция EСТЬNULL() сокращает конструкцию, за счет чего запрос становится более читабельным.
При выполнении функции EСТЬNULL() выражение замены приводится к типу проверяемого выражения для типов строковых типов (к длине строки) и числовых (к разрядности).

Источник