Vertica Internal Optimzer error for a Query with distinct clause
Hi ,
We have a query (mentioned below) throwing this error
Vertica version : 7.2.0
[Error Code: 3594, SQL State: VX001] [Vertica][VJDBC](3594) INTERNAL: Internal Optimizer Error (11)
[Vertica][VJDBC]Detail: isInputTypeCompatibleWithExpected(intype, vartype)
we have a column access policy on age column which might return null depending on the user role.
the above error is thrown only for users who dont have access to that column.
And this issue is only for the table patient.
we tried recreating tables , still issue exists.
any help would be appreciated
SELECT DISTINCT
D.PatientKey,
P.AGE,
P.SRC_GNDR_DESC,
P.EXPIRATION_INDICATOR,
P.HOME_CENTER_DESC,
PATIENT_CLASS_CD AS Inpatient_or_Outpatient,
P.PATIENT_CLASSS_STATUS_CD AS Patient_Status,
Concept_Unique_Identifier,
Disease_Name,
Grade_Description,
M_Stage,
N_Stage,
T_Stage,
Progress_Cd Progression,
C.PROTOCOL_NAME,
C.PROTOCOL_RESPONSE,
CHEMO_ADV_EVENTS.DRUG,
CHEMO_ADV_EVENTS.TOT_CRSE_NUM,
CHEMO_ADV_EVENTS.AE_CUI_CD,
CHEMO_ADV_EVENTS.AE_NM,
CHEMO_ADV_EVENTS.RSLV_STAT_DESC
FROM
(
SELECT
PT_KEY AS PatientKey,
DX_CUI_CD Concept_Unique_Identifier,
MAX(DX_DESC) Disease_Name,
GRD_DESC Grade_Description,
GRD_SYS_DESC Grade_System_Description,
M_STG_DESC M_Stage,
N_STG_DESC N_Stage,
STG_DESC Stage,
T_STG_DESC T_Stage,
PRGRS_CD Progress_Cd
FROM
STAGE.CLNCL_DX_FNDG_RESLTS_V_ALL
WHERE
INDCT_CD = 'Y'
GROUP BY
PT_KEY,
DX_CUI_CD,
GRD_DESC,
GRD_SYS_DESC,
INDCT_CD,
M_STG_DESC,
N_STG_DESC,
STG_DESC,
T_STG_DESC,
PRGRS_CD
) AS D INNER JOIN STAGE.PATIENT_V_ALL P
ON P.PT_KEY = D.PatientKey LEFT JOIN STAGE.CARE_PLAN_V_ALL C
ON C.PT_KEY = P.PT_KEY
AND C.PT_KEY = D.PatientKey
LEFT JOIN
(
select CHEMO.PT_KEY, DRUG, TOT_CRSE_NUM, AE_CUI_CD, MAX(AE_NM) as AE_NM, RSLV_STAT_DESC from (
select distinct SUBQRY1.PT_KEY, DOC_SVC_DTM, SUBQRY1.DRUG, TOT_CRSE_NUM from
(select PT_KEY, DOC_SVC_DTM, UPPER(DRUG_1_NM) as DRUG, TOT_CRSE_NUM from STAGE.CHEMOTHERAPY_FNDG_RESLTS_V_ALL where DRUG_1_NM is NOT NULL
union
select PT_KEY, DOC_SVC_DTM, UPPER(DRUG_2_NM) as DRUG, TOT_CRSE_NUM from STAGE.CHEMOTHERAPY_FNDG_RESLTS_V_ALL where DRUG_2_NM is NOT NULL
union
select PT_KEY, DOC_SVC_DTM, UPPER(DRUG_3_NM) as DRUG, TOT_CRSE_NUM from STAGE.CHEMOTHERAPY_FNDG_RESLTS_V_ALL where DRUG_3_NM is NOT NULL
union
select PT_KEY, DOC_SVC_DTM, UPPER(DRUG_4_NM) as DRUG, TOT_CRSE_NUM from STAGE.CHEMOTHERAPY_FNDG_RESLTS_V_ALL where DRUG_4_NM is NOT NULL
union
select PT_KEY, DOC_SVC_DTM, UPPER(DRUG_5_NM) as DRUG, TOT_CRSE_NUM from STAGE.CHEMOTHERAPY_FNDG_RESLTS_V_ALL where DRUG_5_NM is NOT NULL
) SUBQRY1
) CHEMO
left outer join STAGE.ADVERSE_EVENT_FNDG_RESLTS_V_ALL ADV on ADV.PT_KEY = CHEMO.PT_KEY and ADV.DOC_SVC_DTM > CHEMO.DOC_SVC_DTM
group by CHEMO.PT_KEY, DRUG, TOT_CRSE_NUM,AE_CUI_CD, RSLV_STAT_DESC
) CHEMO_ADV_EVENTS
ON CHEMO_ADV_EVENTS.PT_KEY = P.PT_KEY
Comments
Hi Valaravaus,
Are you seeing below error.
INTERNAL 3594: Internal Optimizer Error (11)
DETAIL: isInputTypeCompatibleWithExpected(intype, vartype)
HINT: Please report this error to Vertica; try restating your query
If so then you may contact vertica support if you are a licenced customer of vertica to see what exactly is causing the error and gain information regarding the error and its fix. Alternatively, you may try a workaround using the following and run your query to see if it works.
vsql=> Alter session set DisableFlattenSubquery= 1;
There is a open bug in vertica for the issue and should be fixed in 7.2 SP2(shipped with 7.2.2-0). If you are a valid customer of vertica then you will receive update regarding when the version will be released.
Thanks.
Hi Valaravaus,
Are you seeing below error.
INTERNAL 3594: Internal Optimizer Error (11)
DETAIL: isInputTypeCompatibleWithExpected(intype, vartype)
HINT: Please report this error to Vertica; try restating your query
If so then you may contact vertica support if you are a licenced customer of vertica to see what exactly is causing the error and gain information regarding the error and its fix. Alternatively, you may try a workaround using the following and run your query to see if it works.
vsql=> Alter session set DisableFlattenSubquery= 1;
There is an open bug in vertica for the issue and should be fixed in 7.2 SP2(shipped with 7.2.2-0). If you are a valid customer of vertica then you will receive update regarding when the version will be released.
If you are running vertica version before v7.2.0 then you may not see this issue at all.
Thanks.