Skip to content

BSL209 — Logical 'OR' in 'JOIN' query section

← All rules

Summary

Logical 'OR' in 'JOIN' query section

Identifiers

Field Value
Rule code BSL209
Compatible alias LogicalOrInJoinQuerySection
Severity WARNING
Enabled by default Yes
Implemented Yes
Tags query, performance

Behavior

  • The public identifier BSL209 and alias LogicalOrInJoinQuerySection 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 = ["BSL209"]
ignore = ["LogicalOrInJoinQuerySection"]

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: BSL209
  • bsl-disable:
Value = "example";  // bsl-disable: BSL209
  • compatible BSLLS form:
Value = "example";  // BSLLS:LogicalOrInJoinQuerySection-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: BSL209
// code without this diagnostic
// noqa-enable: BSL209

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

// BSLLS:LogicalOrInJoinQuerySection-off
// code without this diagnostic
// BSLLS:LogicalOrInJoinQuerySection-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 reveals the use of the OR operator in the conditions of table joins.

The presence of the OR operators in connection conditions may cause the DBMS to be unable to use table indexes and perform scans, which will increase query running time and the likelihood of locks.

The error can be solved by "spreading" the predicates of the condition with OR into different query packages with combining

IMPORTANT: Diagnostics monitors the presence of predicates in the condition OR, over various fields, since the use of the operator OR When executing a query on the SQL side, the control over the variants of one field is automatically converted to the IN condition.

Examples

1) The error will not be fixed when using OR over variants of a single field.

LEFT JOIN Catalog.NomenclatureTypes КАК NomenclatureTypes
    ON CatalogNomenclature.NomenclatureType = NomenclatureTypes.Reference
        AND (CatalogNomenclature.ExpirationDate > 1
     OR CatalogNomenclature.ExpirationDate < 10)
2) When using the OR operator over various fields, the error will be fixed for each occurrence of the operator.

INNER JOIN Document.GoodsServicesSaling КАК GoodsServicesSaling
ON GoodsServicesSalingGoods.Reference = GoodsServicesSaling.Reference
   AND (GoodsServicesSalingGoods.Amount  > 0
   OR GoodsServicesSalingGoods.AmountVAT > 0
   OR GoodsServicesSalingGoods.AmountWithVAT > 0)

It is proposed to correct such constructions by placing requests in separate packages with combining:

SELECT *
FROM
INNER JOIN Document.GoodsServicesSaling КАК GoodsServicesSaling
ON GoodsServicesSalingGoods.Reference = GoodsServicesSaling.Reference
   AND GoodsServicesSalingGoods.Amount  > 0

UNION ALL

SELECT *
FROM
INNER JOIN Document.GoodsServicesSaling КАК GoodsServicesSaling
ON GoodsServicesSalingGoods.Reference = GoodsServicesSaling.Reference
    AND GoodsServicesSalingGoods.AmountVAT > 0

UNION ALL

SELECT *
FROM
INNER JOIN Document.GoodsServicesSaling КАК GoodsServicesSaling
ON GoodsServicesSalingGoods.Reference = GoodsServicesSaling.Reference
    AND GoodsServicesSalingGoods.AmountWithVAT > 0

3) Diagnostics will also work for nested connections using OR in conditions.

Document.GoodsServicesSaling.Goods КАК GoodsServicesSalingGoods
INNER JOIN Document.GoodsServicesSaling КАК GoodsServicesSaling
ON GoodsServicesSalingGoods.Reference = GoodsServicesSaling.Reference
LEFT JOIN Catalog.Nomenclature КАК CatalogNomenclature
    LEFT JOIN Catalog.NomenclatureTypes КАК NomenclatureTypes
    ON CatalogNomenclature.NomenclatureType = NomenclatureTypes.Reference
        AND (CatalogNomenclature.ExpirationDate > 1
         OR NomenclatureTypes.SaleThroughAPatentIsProhibited = TRUE)
A fix similar to paragraph 2 is recommended by replacing the nested connection with a connection with the creation of an intermediate temporary table.

Sources