Clinical Data Counts Issue (Patients are incorrectly counted because case insensitivity)

[haynescd]

Study where issue was found
https://www.cbioportal.org/study/summary?id=prad_organoids_msk_2022

Specifically the issue was found while looking at the Pie Chart Ethnicity

Patients Related to issue

• VCAP
• VCaP
• LNCAP
• LNCaP
• 22RV1
• 22Rv1
• PC3
• PC-3

The Patients above seem to be merged together when counting clinical-data-counts even though they have unique patient internal ids.

The Ethnicity chart shows 5 Caucasians, but there are a total of 6

Found in the RFC80 Effort

SQL used

SELECT ''                                                   AS sample_unique_id,
       concat(cs.cancer_study_identifier, '_', p.stable_id) AS patient_unique_id,
       p.internal_id,
       cam.attr_id                                          AS attribute_name,
       ifNull(clinpat.attr_value, '')                                   AS attribute_value,
       cs.cancer_study_identifier                           AS cancer_study_identifier,
       'patient'                                            AS type
FROM patient AS p
    INNER JOIN cancer_study AS cs ON p.cancer_study_id = cs.cancer_study_id
    FULL OUTER JOIN clinical_attribute_meta AS cam ON cs.cancer_study_id = cam.cancer_study_id
    FULL OUTER JOIN clinical_patient AS clinpat ON (p.internal_id = clinpat.internal_id) AND (clinpat.attr_id = cam.attr_id)
WHERE cam.patient_attribute = 1 and cs.cancer_study_identifier = 'prad_organoids_msk_2022' and attribute_name = 'ETHNICITY';

[onursumer]

The unique internal ids do not matter to legacy implementation because we are using stable patient ids in the SQL.

cbioportal/src/main/java/org/cbioportal/persistence/mybatis/ClinicalDataMyBatisRepository.java

Lines 186 to 187 in f7fdc1a

	return clinicalDataMapper.fetchPatientClinicalDataCounts(patientStudyIds,
	patients.stream().map(Patient::getStableId).collect(Collectors.toList()), attributeIds, projection);

The below SQL works (except for PC-3) because mysql is case insensitive

cbioportal/src/main/resources/org/cbioportal/persistence/mybatis/ClinicalDataMapper.xml

Lines 101 to 104 in f7fdc1a

	patient.STABLE_ID IN
	<foreach item="item" collection="patientIds" open="(" separator="," close=")">
	#{item}
	</foreach>