No NULL checks for fields from joined tables (FieldsFromJoinsWithoutIsNull)¶

Type	Scope	Severity	Activated by default	Minutes to fix	Tags
`Error`	`BSL` `OS`	`Critical`	`No`	`2`	`sql` `suspicious` `unpredictable`

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¶

Snippets¶

Diagnostic ignorance in code¶

// BSLLS:FieldsFromJoinsWithoutIsNull-off
// BSLLS:FieldsFromJoinsWithoutIsNull-on

Parameter for config¶

"FieldsFromJoinsWithoutIsNull": false