Bug #22049 Odd behaviour with different orders of when-clauses in a case

• Submitted: 6 Sep 2006 11:09
• Modified: 7 Jan 2008 11:09
• Reporter: Valeriy Kravchuk
• Status: Closed
• Category: MaxDB
• Severity: S1 (Critical)
• Version: 7.5.00.34
• OS: Windows (Windows)
• CPU Architecture: Any

[6 Sep 2006 11:09] Valeriy Kravchuk

Description:
Expressions

case
when kunbranb=14000 then 'privat'
when kunbranb!=14000 and kt.geschart='GI' and kt.kontogru='KK' and kt.kontoart='01' then 'kommerz'
else 'HuluHulu'
end

and

case
when kunbranb!=14000 and kt.geschart='GI' and kt.kontogru='KK' and kt.kontoart='01' then 'kommerz'
when kunbranb=14000 then 'privat'
else 'HuluHulu'

may give different results on same rows (when expression "kunbranb!=14000 and kt.geschart='GI' and kt.kontogru='KK' and kt.kontoart='01'" is true).

How to repeat:
CREATE TABLE table1
(
"BANK" Fixed (5,0),
"KONTO" Fixed (13,0),
"GESCHART" Char (2) ASCII,
"KONTOART" Char (2) ASCII,
"KUNDNR" Fixed (13,0),
"KONTOGRU" Char (2) ASCII,
PRIMARY KEY (BANK,KONTO)
)
//
INSERT INTO table1 VALUES (1,1,'GA','01',1,'GA')
//
INSERT INTO table1 VALUES (1,2,'GI','01',1,'KK')
//
INSERT INTO table1 VALUES (1,3,'SP','01',1,'SP')
//
CREATE TABLE table2
(
"BANK" Fixed (5,0),
"KUNDNR" Fixed (13,0),
"KUNBRANB" Smallint,
PRIMARY KEY (BANK,KUNDNR)
)
//
INSERT INTO table2 VALUES (1,1,1060)
//
select distinct kd.kundnr,
case
when kunbranb=14000 then 'privat'
when kunbranb!=14000 and kt.geschart='GI' and kt.kontogru='KK' and kt.kontoart='01' then 'kommerz'
else 'HuluHulu'
end,
case
when kunbranb!=14000 and kt.geschart='GI' and kt.kontogru='KK' and kt.kontoart='01' then 'kommerz'
when kunbranb=14000 then 'privat'
else 'HuluHulu'
end kuart
from table2 kd inner join table1 kt on kd.bank=kt.bank and kd.kundnr=kt.kundnr
where kd.bank=1
order by kd.kundnr

The result is two rows:

1  kommerz  HuluHulu
1  HuluHulu HuluHulu

While only one row

1  kommerz  HuluHulu

is expected.

Suggested fix:
Don't know. But this is really weird bug.