BUG #11130: Case condition error
От | mozsar.laszlo@mgmail.com |
---|---|
Тема | BUG #11130: Case condition error |
Дата | |
Msg-id | 20140808122249.2597.46833@wrigleys.postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #11130: Case condition error
|
Список | pgsql-bugs |
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. :(
В списке pgsql-bugs по дате отправления: