join_path rule implementation

[sshenzha]

In OMOP CDM, clinical tables store concept IDs (e.g. condition_occurrence.condition_concept_id), but the human-readable metadata for those concepts lives in the concept table. To get anything useful out of a query, you need to JOIN them. This rule catches the case where you reference concept or concept_relationship, but the JOIN wiring is missing or wrong.

[sshenzha]

The code works as follows:

1. Parses the SQL into one or more ASTs.
2. Builds the set of STANDARD concept fields from semantic_chema.py. These are the columns that must map to concept.concept_id.
3. Extracts which of those fields are actually referenced in the query via _extract_concept_references().

If none of the known STANDARD fields appear in the query, the rule exists.

The validation rule logic runs in three stages:

• Are vocabulary tables even present? -> It looks for the tables concept and concept_relationship; if neither table is in the query, there is nothing to validate. The rule only fires when you have both a clinical STANDARD field and a vocabulary table. That combination is what implies a JOIN should exist.
• CTE-based indirect joins -> A JOIN doesn't have to be a literal JOIN keyword between two base tables, instead it can use CTE.

WITH drug_concepts AS (
    SELECT concept_id          -- outputs concept_id
    FROM concept               -- uses a vocabulary table
    WHERE vocabulary_id = 'RxNorm'
)
SELECT de.*
FROM drug_exposure de
WHERE de.drug_concept_id IN (SELECT concept_id FROM drug_concepts)  -- joins via CTE

• Direct join path check -> It checks whether the vocabulary tables are directly wired to clinical tables via JOIN conditions.

WRONG EXAMPLES

-- missing JOIN condition
SELECT co.condition_concept_id, c.concept_name
FROM condition_occurrence co, concept c        -- comma join, no ON clause
WHERE c.standard_concept = 'S'
-- WARNING: concept table is not properly joined to clinical tables

-- concept table used but never connected
SELECT co.condition_concept_id
FROM condition_occurrence co
JOIN concept c ON 1 = 1                        -- no real key link
-- WARNING: same as above

CORRECT EXAMPLES

-- direct JOIN
SELECT co.condition_concept_id, c.concept_name
FROM condition_occurrence co
JOIN concept c ON co.condition_concept_id = c.concept_id
WHERE c.standard_concept = 'S'
-- OK: condition_occurrence.condition_concept_id → concept.concept_id

-- indirect JOIN via CTE
WITH valid_drugs AS (
    SELECT concept_id FROM concept WHERE vocabulary_id = 'RxNorm'
)
SELECT de.*
FROM drug_exposure de
JOIN valid_drugs vd ON de.drug_concept_id = vd.concept_id
-- OK: CTE bridges concept → drug_exposure via concept_id

-- indirect via concept_ancestor
SELECT ca.descendant_concept_id
FROM concept_ancestor ca
JOIN concept c ON c.concept_id = ca.descendant_concept_id
-- OK: concept is linked to concept_ancestor on descendant_concept_id