Skip to content

BSL187 — No NULL checks for fields from joined tables

← All rules

Summary

No NULL checks for fields from joined tables

Identifiers

Field Value
Rule code BSL187
Compatible alias FieldsFromJoinsWithoutIsNull
Severity WARNING
Enabled by default Yes
Implemented Yes
Tags query, correctness

Behavior

  • The public identifier BSL187 and alias FieldsFromJoinsWithoutIsNull are stable.
  • The rule reports the cases documented on this page.
  • Suppressions and project configuration are applied before publication.
  • The rule requires neither an external analyzer nor network access.

Configuration and suppression

BSL### is the primary stable identifier. The compatible alias is accepted in select, ignore, and compatible block suppression comments.

[tool.onec-hbk-bsl]
select = ["BSL187"]
ignore = ["FieldsFromJoinsWithoutIsNull"]

All three suppression families support both a current line and a range. When an opening comment follows code, it affects only that line. Use any one form:

  • noqa:
Value = "example";  // noqa: BSL187
  • bsl-disable:
Value = "example";  // bsl-disable: BSL187
  • compatible BSLLS form:
Value = "example";  // BSLLS:FieldsFromJoinsWithoutIsNull-off

When the same opening comment is on a line by itself, it starts a range. Close it with the matching marker from the same family:

// noqa: BSL187
// code without this diagnostic
// noqa-enable: BSL187

// bsl-disable: BSL187
// code without this diagnostic
// bsl-enable: BSL187

// BSLLS:FieldsFromJoinsWithoutIsNull-off
// code without this diagnostic
// BSLLS:FieldsFromJoinsWithoutIsNull-on

To disable the rule until the end of the file, omit the closing noqa-enable, bsl-enable, or BSLLS:…-on marker.

Opening and closing markers must belong to the same family.

Description

Diagnostics checks fields from left, right, full joins that are not checked with ISNULL() or IS NOT NULL or NOT IS NULL .

Queries cannot use attributes from left-join or right-join tables without checking the values for NULL. Such a call can lead to errors if the join condition is not met and there are no matching records in the left or right table. As a result, as a result of executing the query, you may receive unexpected data and the system may behave in an incorrect way.

It is important to remember that any comparison of the value NULL with any other expression is always false, even the comparison of NULL and NULL is always false. The following are examples of such incorrect comparisons. It is correct to compare with NULL - operator IS NULL or function ISNULL().

Left \ right joins are often used, although the data allows an inner join without checking for NULL.

Additional checks of field values can be performed in the 1C code, and not in the query text. This makes it difficult to read the code and refactor the code, because the context of the access to the field has to be considered in several places. It should be remembered that simple checks in a query are performed a little faster and easier than in interpreted 1C code.

These problems are the most common mistakes made by 1C developers of all skill levels.

Examples

Example showing NULL comparison problems - joining 2 tables incorrectly and showing different comparison methods 

SELECT
  CASE
    WHEN LeftTable.Fld2 = 0 THEN "Equals 0 - does not work"
    WHEN LeftTable.Fld2 <> 0 THEN "NOT Equals 0 - does not work"
    WHEN LeftTable.Fld2 = NULL THEN "Equals NULL - does not work"
    WHEN LeftTable.Fld2 IS NULL THEN "IS NULL - it works"
    WHEN ISNULL(LeftTable.Fld2, 0) = 0  THEN "ISNULL() - and this works too"
    ELSE "else"
  END
ИЗ
  First AS First
  LEFT JOIN LeftTable AS LeftTable
  ON FALSE

Suspicious code for accessing an joined table attribute 

ВЫБРАТЬ
  ДокументыПродажи.Ссылка КАК ДокПродажи,
  РегистрПродажи.Сумма КАК Сумма // здесь ошибка
ИЗ Документ.РеализацияТоваровУслуг КАК ДокументыПродажи
ЛЕВОЕ СОЕДИНЕНИЕ  РегистрНакопления.Продажи КАК РегистрПродажи
ПО ДокументыПродажи.Ссылка = РегистрПродажи.Документ
Correct 
ВЫБРАТЬ
  ДокументыПродажи.Ссылка КАК ДокПродажи,
  ЕстьNULL(РегистрПродажи.Сумма, 0) КАК Сумма
ИЗ Документ.РеализацияТоваровУслуг КАК ДокументыПродажи
ЛЕВОЕ СОЕДИНЕНИЕ  РегистрНакопления.Продажи КАК РегистрПродажи
ПО ДокументыПродажи.Ссылка = РегистрПродажи.Документ
Also correct: 
ВЫБРАТЬ
  ДокументыПродажи.Ссылка КАК ДокПродажи,
  ВЫБОР КОГДА РегистрПродажи.Сумма Есть NULL ТОГДА 0
  ИНАЧЕ  РегистрПродажи.Сумма
  КОНЕЦ КАК Сумма
ИЗ Документ.РеализацияТоваровУслуг КАК ДокументыПродажи
ЛЕВОЕ СОЕДИНЕНИЕ  РегистрНакопления.Продажи КАК РегистрПродажи
ПО ДокументыПродажи.Ссылка = РегистрПродажи.Документ
Possible variant 
ВЫБРАТЬ
  ДокументыПродажи.Ссылка КАК ДокПродажи,
  РегистрПродажи.Сумма КАК Сумма
ИЗ Документ.РеализацияТоваровУслуг КАК ДокументыПродажи
ЛЕВОЕ СОЕДИНЕНИЕ  РегистрНакопления.Продажи КАК РегистрПродажи
ПО ДокументыПродажи.Ссылка = РегистрПродажи.Документ
ГДЕ
    РегистрПродажи.Документ ЕСТЬ НЕ NULL
    //или НЕ РегистрПродажи.Документ ЕСТЬ NULL
The last one is not the best, because it actually emulates an inner join. It is more correct to explicitly specify INNER JOIN instead of using a left join with a IS NOT NULL or NOT IS NULL check

Sources