BUG #11130: Case condition error

From: mozsar(dot)laszlo(at)mgmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #11130: Case condition error
Date: 2014-08-08 12:22:49
Message-ID: 20140808122249.2597.46833@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 11130
Logged by: Laszlo Mozsar
Email address: mozsar(dot)laszlo(at)mgmail(dot)com
PostgreSQL version: 9.3.4
Operating system: Ubuntu 14.04 LTS
Description:

The "is null" contition on a date field returns wrong result. I can't
reproduce this with a test table. My tables contains personal and business
informations, so I can't send a full log with datas.
(I used reindex and vacuum before submitting this report.)

The structure:
CREATE TABLE public.hitel (
kod INTEGER NOT NULL,
szerzodes_szam VARCHAR(40) NOT NULL,
szerzodes_datum DATE NOT NULL,
lejarat_datum DATE,
lejarat_tipus CHAR(1),
szerzodeskod_merkint VARCHAR(20),
szerzodeskod_bag VARCHAR(20),
szerzodeskod_elementa VARCHAR(20),
szerzodeskod_eurobank VARCHAR(40),
ugyfelkod_merkint VARCHAR(20),
ugyfelkod_bag VARCHAR(20),
ugyfelkod_elementa VARCHAR(20),
ugyfelkod_eurobank VARCHAR(20),
szamlaszam public.szamlaszam,
folyoszamlaszam public.szamlaszam,
deviza public.deviza,
indulo_toke public.osszeg NOT NULL,
kulonbozet public.osszeg,
elszamolas_modja CHAR(1),
tamogatott BOOLEAN NOT NULL,
folyoszamla BOOLEAN NOT NULL,
bapo BOOLEAN NOT NULL,
konvertalt_errol INTEGER,
konvertalt_erre INTEGER,
hirdetmeny INTEGER,
hirdetmeny_forras VARCHAR(20),
szerzodeskod_flex VARCHAR(20),
ugyfelkod_flex VARCHAR(20),
ugyfelnev VARCHAR(80),
koveteles_huf public.osszeg,
koveteles public.osszeg,
CONSTRAINT hitel_pkey PRIMARY KEY(kod),
CONSTRAINT hitel_fk_hirdetmeny FOREIGN KEY (hirdetmeny)
REFERENCES public.hirdetmeny(kod)
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE,
CONSTRAINT hitel_fk_konvertalt_erre FOREIGN KEY (konvertalt_erre)
REFERENCES public.hitel(kod)
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE,
CONSTRAINT hitel_fk_konvertalt_errol FOREIGN KEY (konvertalt_errol)
REFERENCES public.hitel(kod)
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE
)
WITH (oids = false);

The SQL select:
select
h.szerzodes_szam,
case
when h.lejarat_datum is null and h.lejarat_tipus is null then 'Elo'
when h.lejarat_tipus = 'L' then 'Felmondott'
else 'Lezart'
end as statusz,
h.lejarat_datum,
h.lejarat_tipus,
h.deviza,
coalesce (h.koveteles, 0.00) as koveteles,
coalesce (h.koveteles_huf, 0.00) as koveteles_huf,
'E' as jelleg
from hitel h

The result contains many rows with statusz='Lezart', but lejarat_tipus and
lejarat_datum are nulls. The result not contains any record with
statusz='Elo' and lejarat_datum is not null.

If I use this, the result is correct:
when coalesce (h.lejarat_datum, '1899-12-30') = '1899-12-30' and
h.lejarat_tipus is null then 'Elo'

PS: Sorry, my engllish is poor. :(

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Fujii Masao 2014-08-08 12:44:48 Re: BUG #10675: alter database set tablespace and unlogged table
Previous Message Sandeep Thakkar 2014-08-08 11:54:41 Re: Re: BUG #11039: installation fails when trying to install C++ redistributable