Skip to content

Lint rule: function on a join key (ON LOWER(a.id) = LOWER(b.id)) is not flagged, although the same function in WHERE is (GV005) #131

Open

Description

@laaielichter

GV005 function-on-filter-column flags WHERE LOWER(event_type) = 'x' because the function hides the raw column from partition pruning, clustering and min/max statistics. The same wrapper inside a join condition does the same harm (the engine cannot use clustering, indexes or key statistics on the wrapped side, and often cannot prune), but only WHERE is looked at:

gulliver lint "SELECT e.user_id FROM events e JOIN users u ON LOWER(e.user_id) = LOWER(u.id) LIMIT 10"
# No scale problems found.

gulliver lint "SELECT e.user_id FROM events e JOIN users u ON CAST(e.user_id AS VARCHAR) = u.id LIMIT 10"
# No scale problems found.

gulliver lint "SELECT user_id FROM events WHERE LOWER(event_type) = 'purchase' LIMIT 10"
# warn  GV005 function-on-filter-column: The filter wraps event_type in a function or calculation, ...

The CAST(int AS VARCHAR) = varchar_key case is the most common real one: a type mismatch that someone papered over with a cast on the big table's key.

Before and after (the fix is usually to clean the key once, upstream, or to cast the small side):

-- before: the big table's key is wrapped
SELECT e.user_id FROM events e JOIN users u ON CAST(e.user_id AS VARCHAR) = u.id_text

-- after: cast the small side, or store the key with the right type
SELECT e.user_id FROM events e JOIN users u ON e.user_id = CAST(u.id_text AS BIGINT)

This one cannot be rewritten automatically (casts can fail or change which rows match), so it is a finding without an autofix.

Checked on current main (c48adf8): the 18 rules (gulliver rules) have nothing for this and no open or closed issue asks for it. (Lint rule GV019 is taken by the open issue #125 and the issue for = NULL is open too, so take the next free id when you start.)

Suggested direction

  1. New rule function-on-join-key, severity info (engines differ: some hash on expressions without trouble, and the lint cannot see the table sizes), message The join key e.user_id is wrapped in a function or cast, so clustering, indexes and statistics on that side cannot be used. and the fix Compare the raw columns, cast the smaller side, or fix the column type upstream.
  2. Look at every equality in a JOIN ... ON where one side is a function or cast of columns from one table (exp.Func, exp.Cast, ::) and the other side refers to a column of another table. Do not flag: ON a.x = b.y (no function), USING (...), a function that wraps only a literal (b.d = DATE '2024-01-01'), and non-equality or OR conditions (GV008 owns those)
  3. Report the node position like the other rules. One finding per join condition is enough
  4. Reuse as much of GV005's column and function detection as possible; if it needs a small refactor to share, do that first in its own commit

Where to start

  • src/gulliver/lint.py: the join checks (GV003 / GV008, around line 247) and GV005 (around line 309) are the two blocks to combine; _join_condition(join) (around line 93) and _aliases_in(expr) (around line 97) already give the pieces for "which tables does this side use"
  • src/gulliver/lint_report.py (around line 73): RULE_DOCS for gulliver rules; README.md (the lint rule table around line 747) and skills/gulliver/SKILL.md (rule table around line 83)
  • Tests: tests/test_lint.py

How to test

gulliver lint "SELECT e.user_id FROM events e JOIN users u ON LOWER(e.user_id) = LOWER(u.id) LIMIT 10"   # new finding
gulliver lint "SELECT e.user_id FROM events e JOIN users u ON e.user_id = u.id LIMIT 10"                  # clean
gulliver lint "SELECT e.user_id FROM events e JOIN users u USING (user_id) LIMIT 10"                      # clean
gulliver rules
pytest tests/test_lint.py

Done when

  • The rule fires for LOWER(a.x) = b.y, CAST(a.x AS VARCHAR) = b.y, a.x::text = b.y and DATE(a.ts) = b.d in a JOIN ... ON (a test for each, and one that parses in each of the four dialects where the syntax exists)
  • It does not fire for a plain a.x = b.y, USING, a function that only wraps a literal, or a function in WHERE (GV005 owns that, no double report)
  • It is in gulliver rules, rule_docs(), the README table and skills/gulliver/SKILL.md, and works with -- gulliver: ignore GVxxx and [lint] disable / severity
  • Leave CHANGELOG.md alone; the maintainer adds the entry with credit to you when it is merged
  • pytest and ruff check . pass

Please comment with your plan before writing a lot of code, as CONTRIBUTING.md asks for bigger changes.

Comment here to claim it and you'll be assigned.

Activity

  1. dianatofficial commented on Nov 15, 2026

    @dianatofficial
    Contributor

    Hi @laaielichter! I would love to work on this issue.

    Here is my implementation plan:

    1. Rule Definition & ID:

    2. Detection in src/gulliver/lint.py:

      • Inspect join conditions in JOIN ... ON.
      • For each exp.EQ in the ON clause, check if either operand wraps columns from the joined table (or the base table) in a function or cast (exp.Func, exp.Cast, ::), while the other operand references a column of another table.
      • Ignore plain key joins (�.x = b.y), USING (...), NATURAL JOIN, non-equality/OR conditions (handled by GV008), and functions wrapping only literal constants (�.d = DATE '2024-01-01').
      • Emit at most one finding per join condition with proper node line/col mapping.
    3. Documentation & Registry:

      • Register in RULE_DOCS in src/gulliver/lint_report.py.
      • Update skills/gulliver/SKILL.md and regenerate the README Scale lint rules table via gulliver rules --format markdown.
      • Ensure -- gulliver: ignore GV021 and [lint.severity] overrides work out of the box.
    4. Testing & Verification:

      • Add unit tests in ests/test_lint.py covering:
        • LOWER(a.id) = LOWER(b.id) and LOWER(a.id) = b.id
        • CAST(a.id AS VARCHAR) = b.id and �.id::text = b.id
        • DATE(a.ts) = b.d across DuckDB, Postgres, BigQuery, and Snowflake
        • Clean passes: �.id = b.id, USING (id), literal function wrapping, and WHERE clause functions (ensuring no duplicate reporting with GV005).
      • Update CHANGELOG.md under ## Unreleased -> ### Added.
      • Verify pytest and
        uff check . 100% clean.

    Could you please assign #131 to me so I can proceed with the PR?

  2. laaielichter commented on Nov 15, 2026

    @laaielichter
    OwnerAuthor

    Thanks @dianatofficial, that is a thoughtful plan. We keep a limit of 3 open assigned issues per person, and you already hold three: #119 (PR #121), #112 (PR #115) and #152 (PR #168). So I can't assign #131 to you right now.

    As soon as one of those is merged you can claim another, and #131 stays free until then. The quickest way forward is to finish and merge the PRs you already have open; that frees a slot right away.


    Generated by Claude Code

  3. AzalHoseinian commented on Nov 15, 2026

    @AzalHoseinian
    Contributor

    Hi! I'd like to claim this one. My first PR (#163, GV003) was just merged, so I have no other open issues. Plan: extend the GV005 check in lint.py to also look at JOIN ... ON conditions (a function or CAST wrapping a column on either side of the comparison), report it as a finding with advice (no auto-rewrite), and add tests for the LOWER/CAST examples from the issue plus a plain ON a.id = b.id that stays clean. I'll read CONTRIBUTING.md first and ask here if anything about the rule ID or severity is unclear.

  4. AzalHoseinian commented on Nov 15, 2026

    @AzalHoseinian
    Contributor

    Small correction to my plan above: I'm implementing this as a new rule, as the issue describes, not by extending GV005. Details: rule id GV021 function-on-join-key (GV019 is taken by #125), severity info, message and fix text as suggested in the issue. It looks at each equality in a JOIN ... ON where one side wraps columns of one table in a function or cast and the other side uses a column of another table. It does not flag plain keys, USING/NATURAL, literal-only functions, OR/non-equality conditions, or functions in WHERE (GV005 keeps those, no double report). Also updates gulliver rules, README, SKILL.md, CHANGELOG and tests. I have it working locally (full pytest and ruff pass) and will open the PR once I've double-checked it.

  5. laaielichter commented on Nov 15, 2026

    @laaielichter
    OwnerAuthor

    Thanks @AzalHoseinian, it's yours, assigned. Your revised plan (a separate GV021 function-on-join-key rule, info, no double report with GV005) is exactly the direction we want.

    One small change: please leave CHANGELOG.md out of the PR. The maintainer adds the credit line under ## Unreleased after merging, which also avoids merge conflicts.


    Generated by Claude Code

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions