The following bug has been logged on the website:
Bug reference: 11130
Logged by: Laszlo Mozsar
Email address: mozsar.laszlo@mgmail.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. :(