Class: PgCanary::Rules::CorrelatedSubqueryInSelect

Inherits:
Base
  • Object
show all
Defined in:
lib/pg_canary/rules/definitions/correlated_subquery_in_select.rb

Overview

A scalar subquery in the SELECT list that references the outer table runs once per result row (N+1 inside a single query).

Instance Method Summary collapse

Methods inherited from Base

all, check, default_enabled, enabled?, #initialize, option, options, rule_name

Constructor Details

This class inherits a constructor from PgCanary::Rules::Base

Instance Method Details

#check ⇒ Object 



12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
 
# File 'lib/pg_canary/rules/definitions/correlated_subquery_in_select.rb', line 12

def check
  detections = []
  each_scope do |scope|
    scope.stmt.target_list.each do |target|
      res_target = target.unwrap
      next unless res_target.is_a?(PgQuery::ResTarget)

      res_target.val.walk_scope do |node|
        next unless node.is_a?(PgQuery::SubLink) && node.sub_link_type == :EXPR_SUBLINK

        table, column = correlated_reference(node, scope)
        next unless table && column
        next unless applicable_table?(table)

        detections << detection(
          table: table,
          columns: column,
          message: "Scalar subquery in the SELECT list references #{table}.#{column} from the " \
                   "outer query, so it executes once per returned row.",
          suggestion: "Rewrite as a JOIN + GROUP BY (or a LATERAL join) so the lookup runs " \
                      "once as a set operation."
        )
      end
    end
  end
  detections
end