Strange behavior of CASE WHEN clause
sanjeev.gour Oct 2, 2014 7:22 AMWe are observing a case where the the results of an sql look strange and undesirable, we have not understood yet why this might be happening. Here are two queries and the respective plans, these query plans are identical but the results are not for some reasons. The strange thing is that in both cases the where condition is satisfied but the WHEN in CASE is satisfied in one case and not in the other.
Case 1:
Query:
SELECT "EVTUSERID", "EVTTYPEDESC", CASE WHEN ("EVTUSERID" = 'GOUSA01') THEN 'Match' ELSE 'No Match' END AS RULESTATE
FROM "SECCM.VIEWSYSACCESS" S1 where (("EVTUSERID" = 'GOUSA01' AND "EVTTYPEDESC" = 'SIGNOFF'))
Plan:
OPTIMIZATION COMPLETE:
PROCESSOR PLAN:
ProjectNode(0) output=[evtuserid AS evtuserid, evttypedesc AS evttypedesc, CASE WHEN evtuserid = 'GOUSA01' THEN 'Match' ELSE 'No Match' END AS RULESTATE] [evtuserid AS evtuserid, evttypedesc AS evttypedesc, CASE WHEN evtuserid = 'GOUSA01' THEN 'Match' ELSE 'No Match' END AS RULESTATE]
AccessNode(1) output=[evtuserid, evttypedesc] SELECT g_0.EVTUSERID, g_0.EVTTYPEDESC FROM CMGRQ1.VIEWSYSACCESS AS g_0 WHERE (g_0.EVTUSERID = 'GOUSA01') AND (g_0.EVTTYPEDESC = 'SIGNOFF') LIMIT 2
Results
evtuserid | evttypedesc | RULESTATE | |
GOUSA01 | SIGNOFF | No Match | |
GOUSA01 | SIGNOFF | No Match |
Case 2:
Query:
SELECT "EVTUSERID", "EVTTYPEDESC", CASE WHEN ("EVTTYPEDESC" = 'SIGNOFF') THEN 'Match' ELSE 'No Match' END AS RULESTATE
FROM "SECCM.VIEWSYSACCESS" S1 where (("EVTUSERID" = 'GOUSA01' AND "EVTTYPEDESC" = 'SIGNOFF'))
Plan:
OPTIMIZATION COMPLETE:
PROCESSOR PLAN:
ProjectNode(0) output=[evtuserid AS evtuserid, evttypedesc AS evttypedesc, CASE WHEN evttypedesc = 'SIGNOFF' THEN 'Match' ELSE 'No Match' END AS RULESTATE] [evtuserid AS evtuserid, evttypedesc AS evttypedesc, CASE WHEN evttypedesc = 'SIGNOFF' THEN 'Match' ELSE 'No Match' END AS RULESTATE]
AccessNode(1) output=[evtuserid, evttypedesc] SELECT g_0.EVTUSERID, g_0.EVTTYPEDESC FROM CMGRQ1.VIEWSYSACCESS AS g_0 WHERE (g_0.EVTUSERID = 'GOUSA01') AND (g_0.EVTTYPEDESC = 'SIGNOFF') LIMIT 2
Results:
evtuserid | evttypedesc | RULESTATE | |
GOUSA01 | SIGNOFF | Match | |
GOUSA01 | SIGNOFF | Match |
Help is appreciated.
Thanks.
Sanjeev.