In MSK data, some condition causes sample-list-counts to fail

[alisman]

The following sample_list query returns a couple counts for null list names. Need to figure out why that is.

SELECT
    name AS label,
    REPLACE(stable_id, CONCAT(cancer_study_identifier, '_'), '') AS value,
    count(sample_id) AS count
FROM sample_list_list sll
         LEFT JOIN sample_derived s ON sll.sample_id=s.internal_id
         LEFT JOIN sample_list sl on sll.list_id=sl.list_id
WHERE sample_unique_id IN (

    SELECT sample_unique_id
    FROM sample_derived
    WHERE cancer_study_identifier IN
          (
           'brca_tcga_pan_can_atlas_2018'
              ,
           'gbm_tcga_pan_can_atlas_2018'
              )
)
GROUP BY s.cancer_study_identifier, sl.stable_id, sl.name

here is api

fetch("http://localhost:8082/api/column-store/sample-lists-counts/fetch", {
  "headers": {
    "accept": "application/json",
    "accept-language": "en-US,en;q=0.9",
    "cache-control": "no-cache",
    "content-type": "application/json",
    "pragma": "no-cache",
    "sec-ch-ua": "\"Chromium\";v=\"128\", \"Not;A=Brand\";v=\"24\", \"Google Chrome\";v=\"128\"",
    "sec-ch-ua-mobile": "?0",
    "sec-ch-ua-platform": "\"macOS\"",
    "sec-fetch-dest": "empty",
    "sec-fetch-mode": "cors",
    "sec-fetch-site": "same-origin"
  },
  "referrer": "http://localhost:8082/study/summary?id=brca_tcga_pan_can_atlas_2018%2Cgbm_tcga_pan_can_atlas_2018",
  "referrerPolicy": "strict-origin-when-cross-origin",
  "body": "{\"studyIds\":[\"brca_tcga_pan_can_atlas_2018\",\"gbm_tcga_pan_can_atlas_2018\"],\"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\":{}}}",
  "method": "POST",
  "mode": "cors",
  "credentials": "include"
});

[alisman]

@gblaih once you get access to the VPN (and then the database i gave you credentials for), try to execute the above query against that database. You should see a result with null values:

The task is to analyze WHY that row appears. It has to do with the LEFT joins. Probably an issue with data.