Jose Martinez Poblete created HIVE-27098:
--------------------------------------------
Summary: Incorrect results using CASE with filter on ORC table
Key: HIVE-27098
URL: https://issues.apache.org/jira/browse/HIVE-27098
Project: Hive
Issue Type: Bug
Components: CBO
Affects Versions: 3.1.3, 4.0.0
Environment: apache-hive-4.0.0-SNAPSHOT
https://github.com/jpoblete/Hive
Reporter: Jose Martinez Poblete
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
{noformat}
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{noformat}
The results are:
*_WITHOUT_* {{AND EG.id_subcontrato = 11968431}}
{noformat}
| id_contrato | id_subcontrato | estado_registro |
| 4678406 | 11968431 | MODIF_I |{noformat}
*_WITH_* {{AND EG.id_subcontrato = 11968431}}
{noformat}
| id_contrato | id_subcontrato | estado_registro |
| 4678406 | 11968431 | NULL |{noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
