[HIVE-27098] Incorrect results using CASE with filter on ORC table

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 3.1.3, 4.0.0
• Component/s: CBO

Environment

apache-hive-4.0.0-SNAPSHOT

https://github.com/jpoblete/Hive

 

• Assignee: Unassigned
• Reporter: Jose Martinez Poblete
• Created: 23/Feb/23 10:49
• Updated: 23/Feb/23 10:49

Description

Consider the statement below where table_1 / table_2 are ORC
The result for the CASE WHEN column ESTADO_REGISTRO is DIFFERENT when the closing LAST filter: 'AND EG.id_subcontrato = 11968431' is not specified

SELECT
EG.id_contrato,
EG.id_subcontrato,
CASE 
    WHEN CRP.id_contrato IS NULL 
    AND  CRP.id_subcontrato IS NULL 
    THEN 'NUEVO' 
    WHEN CRP.id_contrato IS NOT NULL 
    AND  CRP.id_subcontrato IS NOT NULL 
    AND  (
          -NVL CONDITION SET-
         ) 
    THEN 'MODIF_I' END ESTADO_REGISTRO
FROM
table_1 EG
LEFT JOIN (
SELECT
*
FROM
table_2
WHERE
date_format(from_unixtime(unix_timestamp(cast(aud_fecha_datos as string),'yyyyMMdd')),'yyyy-MM-dd') = date_sub(date_format(from_unixtime(unix_timestamp('20230101', 'yyyyMMdd')),'yyyy-MM-dd'),1)
AND
FECHA_FIN_VIG = '9999-12-31 00:00:00.0'
) as CRP 
ON  CRP.cod_aplicacion              = EG.cod_aplicacion
AND CRP.cod_empresa                 = EG.cod_empresa
AND CRP.cod_centro                  = EG.cod_centro
AND CRP.cod_afijo                   = EG.cod_afijo
AND CRP.id_interno_contrato         = EG.id_interno_contrato
AND trim(CRP.id_interno_subcontrato)= trim(EG.id_interno_subcontrato)
WHERE
EG.aud_fecha_datos = 20230101
AND 
EG.id_subcontrato = 11968431; – DIFFERENT RESULT IF THIS IS NOT SPECIFIED

The results are:

WITHOUT  AND EG.id_subcontrato = 11968431

| id_contrato | id_subcontrato | estado_registro | 
| 4678406     | 11968431       | MODIF_I         |

WITH        AND EG.id_subcontrato = 11968431

| id_contrato | id_subcontrato | estado_registro | 
| 4678406     | 11968431       | NULL            |