Error when genomic data filter is set

[alisman]

curl 'https://genie-public-green.cbioportal.org/api/column-store/clinical-data-counts/fetch' \
  -H 'accept: application/json' \
  -H 'accept-language: en-US,en;q=0.9' \
  -H 'cache-control: no-cache' \
  -H 'content-type: application/json' \
  -H 'cookie: _gid=GA1.2.628642096.1733691232; _ga_5260NDGD6Z=GS1.1.1733702774.5.1.1733702775.0.0.0; _ga=GA1.1.458296050.1733264799; _ga_CKJ2CEEFD8=GS1.1.1733773705.245.1.1733773816.0.0.0; SESSION=NWUzNWI0OTMtOTBjMS00NmEzLTliM2UtZWJiZjBlMzM5MWFk; _ga_ET18FDC3P1=GS1.1.1733774682.5.1.1733774878.0.0.0; _dd_s=logs=1&id=288f33dc-62dd-4e71-8961-67ab6824b8ed&created=1733774682210&expire=1733776224193' \
  -H 'origin: https://genie-public-green.cbioportal.org' \
  -H 'pragma: no-cache' \
  -H 'priority: u=1, i' \
  -H 'referer: https://genie-public-green.cbioportal.org/study/summary?id=genie_public' \
  -H 'sec-ch-ua: "Google Chrome";v="131", "Chromium";v="131", "Not_A Brand";v="24"' \
  -H 'sec-ch-ua-mobile: ?0' \
  -H 'sec-ch-ua-platform: "macOS"' \
  -H 'sec-fetch-dest: empty' \
  -H 'sec-fetch-mode: cors' \
  -H 'sec-fetch-site: same-origin' \
  -H 'user-agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/131.0.0.0 Safari/537.36' \
  --data-raw '{"attributes":[{"attributeId":"CENTER"},{"attributeId":"SEQ_ASSAY_ID"},{"attributeId":"SAMPLE_TYPE_DETAILED"},{"attributeId":"SAMPLE_COUNT"},{"attributeId":"CANCER_TYPE_DETAILED"},{"attributeId":"SEX"},{"attributeId":"DEAD"},{"attributeId":"ETHNICITY"},{"attributeId":"ONCOTREE_CODE"},{"attributeId":"CANCER_TYPE"}],"studyViewFilter":{"genomicDataFilters":[{"hugoGeneSymbol":"KRAS","profileType":"cna","values":[{"value":"NA"}]}],"clinicalDataFilters":[{"attributeId":"SAMPLE_TYPE","values":[{"value":"PRIMARY"}]},{"attributeId":"PRIMARY_RACE","values":[{"value":"WHITE"}]}],"geneFilters":[{"molecularProfileIds":["genie_public_cna"],"geneQueries":[[{"hugoGeneSymbol":"EGFR","entrezGeneId":0,"alterations":["AMP"],"includeDriver":true,"includeVUS":true,"includeUnknownOncogenicity":true,"tiersBooleanMap":{},"includeUnknownTier":true,"includeGermline":true,"includeSomatic":true,"includeUnknownStatus":true}]]}],"studyIds":["genie_public"],"alterationFilter":{"copyNumberAlterationEventTypes":{"AMP":true,"HOMDEL":true},"mutationEventTypes":{"any":true},"structuralVariants":null,"includeDriver":true,"includeVUS":true,"includeUnknownOncogenicity":true,"includeUnknownTier":true,"includeGermline":true,"includeSomatic":true,"includeUnknownStatus":true,"tiersBooleanMap":{}}}}'

[onursumer]

Not sure what's exactly going on but the SQL below fails on cgds_genie_green but works fine on sling_db_2024_11_14.
The inner SELECT statement (SELECT count(distinct sample_unique_id) as count ...) works fine without an error though.

SELECT
    'TEST' AS value,
    (
        SELECT
            count(distinct sample_unique_id) as count
        FROM
            sample_derived
        WHERE
            sample_unique_id IN (
                SELECT
                    sample_unique_id
                FROM
                    sample_derived
                WHERE
                    cancer_study_identifier IN ('genie_public')
                INTERSECT
                (
                    WITH cna_query AS (
                        SELECT
                            sample_unique_id, alteration_value
                        FROM
                            genetic_alteration_derived
                        WHERE
                            profile_type = 'cna'
                          AND
                            hugo_gene_symbol = 'EGFR'
                          AND
                            cancer_study_identifier IN ('genie_public')
                    )
                    SELECT
                        DISTINCT sd.sample_unique_id
                    FROM
                        sample_derived sd
                            LEFT JOIN cna_query ON sd.sample_unique_id = cna_query.sample_unique_id
                    WHERE
                        cancer_study_identifier IN ('genie_public')
                      AND
                        (alteration_value IS null)
                )
            )
    ) AS count

This is the error message we are getting:

Code: 403. DB::Exception: Cannot determine join keys in ALL LEFT JOIN ... ON sd.sample_unique_id = sample_unique_id. (INVALID_JOIN_ON_EXPRESSION) (version 24.8.1.10472 (official build))

[onursumer]

Here is the corresponding mybatis code for reference

cbioportal/src/main/resources/org/cbioportal/persistence/mybatisclickhouse/StudyViewMapper.xml

Lines 226 to 238 in b31fe9e

	SELECT attributeId,
	'NA' AS value,
	((
	<choose>
	<when test="'${type}' == 'sample'">
	<include refid="getTotalSampleCount"/>
	</when>
	<otherwise>
	<include refid="getTotalPatientCount"/>
	</otherwise>
	</choose>
	) - clinical_data_sum.sum) AS count
	FROM clinical_data_sum

And we are getting the same error for other endpoints wherever we basically do

    (<include refid="getTotalSampleCount"/> - non_na_count) AS count

[alisman]

@onursumer for some reason this works. if these results are correct, it seems like just another Clickhouse rough edge ; )

[onursumer]

Thanks @alisman 🙏

[onursumer]

PR #11265 partially fixes this but after adding more charts and filters I got a different SQL error. Here is the corresponding curl:

curl 'http://localhost:8082/api/column-store/genomic-data-counts/fetch' \
  -H 'Accept: application/json' \
  -H 'Accept-Language: en-US,en;q=0.9,tr;q=0.8,fi;q=0.7' \
  -H 'Connection: keep-alive' \
  -H 'Content-Type: application/json' \
  -H 'Cookie: _ga=GA1.1.10135226.1672424265; _ga_5260NDGD6Z=GS1.1.1731356725.9.0.1731356725.0.0.0; _ga_334HHWHCPJ=GS1.1.1733184309.2.0.1733184312.0.0.0; _ga_N6EV8C84KY=GS1.1.1733518128.271.0.1733518128.0.0.0; _dd_s=logs=1&id=e775ee62-f308-4bbe-8d04-f353c39104d1&created=1733847439275&expire=1733848339275' \
  -H 'Origin: http://localhost:8082' \
  -H 'Referer: http://localhost:8082/study/summary?id=genie_public' \
  -H 'Sec-Fetch-Dest: empty' \
  -H 'Sec-Fetch-Mode: cors' \
  -H 'Sec-Fetch-Site: same-origin' \
  -H 'User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/131.0.0.0 Safari/537.36' \
  -H 'sec-ch-ua: "Google Chrome";v="131", "Chromium";v="131", "Not_A Brand";v="24"' \
  -H 'sec-ch-ua-mobile: ?0' \
  -H 'sec-ch-ua-platform: "Linux"' \
  --data-raw '{"genomicDataFilters":[{"hugoGeneSymbol":"TP53","profileType":"cna"}],"studyViewFilter":{"genomicDataFilters":[{"hugoGeneSymbol":"EGFR","profileType":"cna","values":[{"value":"NA"}]}],"studyIds":["genie_public"],"alterationFilter":{"copyNumberAlterationEventTypes":{"AMP":true,"HOMDEL":true},"mutationEventTypes":{"any":true},"structuralVariants":null,"includeDriver":true,"includeVUS":true,"includeUnknownOncogenicity":true,"includeUnknownTier":true,"includeGermline":true,"includeSomatic":true,"includeUnknownStatus":true,"tiersBooleanMap":{}}}}'

This is the error message:

DB::Exception: Unknown table expression identifier 'cna_query' in scope WITH cna_query AS (SELECT sample_unique_id AS sampleUniqueId, alteration_value FROM genetic_alteration_derived WHERE (profile_type = 'cna') AND (hugo_gene_symbol = 'EGFR') AND (cancer_study_identifier IN ('genie_public'))) SELECT DISTINCT sd.sample_unique_id FROM sample_derived AS sd LEFT JOIN cna_query ON sd.sample_unique_id = cna_query.sampleUniqueId WHERE (cancer_study_identifier IN ('genie_public')) AND (alteration_value IS NULL). (UNKNOWN_TABLE) (version 24.8.1.10472 (official build))