Обсуждение: BUG #11130: Case condition error
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. :(
mozsar.laszlo wrote > The following bug has been logged on the website: > > Bug reference: 11130 > Logged by: Laszlo Mozsar > Email address: > mozsar.laszlo@ > 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.) Cannot test myself at the moment but is it that your attempt at a test case didn't fail or that you didn't attempt a self-contained test case? If, on the same database, you cannot reproduce the error with a minimal test case then something about the specific tables and queries is wrong and you (since you cannot share) need to figure out what that is. Even if you cannot share the data sharing the schema will let others try to see whatever it is you may be missing. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-11130-Case-condition-error-tp5814246p5814306.html Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
David G Johnston wrote > > mozsar.laszlo wrote >> The following bug has been logged on the website: >> >> Bug reference: 11130 >> Logged by: Laszlo Mozsar >> Email address: >> mozsar.laszlo@ >> 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.) > Cannot test myself at the moment but is it that your attempt at a test > case didn't fail or that you didn't attempt a self-contained test case? > > If, on the same database, you cannot reproduce the error with a minimal > test case then something about the specific tables and queries is wrong > and you (since you cannot share) need to figure out what that is. Even if > you cannot share the data sharing the schema will let others try to see > whatever it is you may be missing. > > David J. And yes I see you shared a schema but nothing about it looks wrong so with additional info is needed or you are showing what you think is the schema while what is actually in production is different. There are only a couple of relevant fields so if you can even so a subset of the data that would probably help. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-11130-Case-condition-error-tp5814246p5814307.html Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
David G Johnston wrote > > David G Johnston wrote >> >> mozsar.laszlo wrote >>> The following bug has been logged on the website: >>> >>> Bug reference: 11130 >>> Logged by: Laszlo Mozsar >>> Email address: >>> mozsar.laszlo@ >>> 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.) >> Cannot test myself at the moment but is it that your attempt at a test >> case didn't fail or that you didn't attempt a self-contained test case? >> >> If, on the same database, you cannot reproduce the error with a minimal >> test case then something about the specific tables and queries is wrong >> and you (since you cannot share) need to figure out what that is. Even >> if you cannot share the data sharing the schema will let others try to >> see whatever it is you may be missing. >> >> David J. > And yes I see you shared a schema but nothing about it looks wrong so with > additional info is needed or you are showing what you think is the schema > while what is actually in production is different. There are only a > couple of relevant fields so if you can even so a subset of the data that > would probably help. > > David J. Actually, upon further review... I suspect your problem is with "lajarat_tipus" This is a char(1) field that you likely are interpreting as null what is actually a blank/space and so the first when is false as is the second (not L) and so the else matches. I do not use char so am not that knowledgable as to its nuances with respect to padding spaces. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-11130-Case-condition-error-tp5814246p5814308.html Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
On Fri, 8 Aug 2014, David G Johnston wrote: > David G Johnston wrote >> >> David G Johnston wrote >>> >>> mozsar.laszlo wrote >>>> The following bug has been logged on the website: >>>> >>>> Bug reference: 11130 >>>> Logged by: Laszlo Mozsar >>>> Email address: > >>>> mozsar.laszlo@ > >>>> 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.) >>> Cannot test myself at the moment but is it that your attempt at a test >>> case didn't fail or that you didn't attempt a self-contained test case? >>> >>> If, on the same database, you cannot reproduce the error with a minimal >>> test case then something about the specific tables and queries is wrong >>> and you (since you cannot share) need to figure out what that is. Even >>> if you cannot share the data sharing the schema will let others try to >>> see whatever it is you may be missing. >>> >>> David J. >> And yes I see you shared a schema but nothing about it looks wrong so with >> additional info is needed or you are showing what you think is the schema >> while what is actually in production is different. There are only a >> couple of relevant fields so if you can even so a subset of the data that >> would probably help. >> >> David J. > > Actually, upon further review... > > I suspect your problem is with "lajarat_tipus" > > This is a char(1) field that you likely are interpreting as null what is > actually a blank/space and so the first when is false as is the second (not > L) and so the else matches. > > I do not use char so am not that knowledgable as to its nuances with respect > to padding spaces. I checked this with some temp tables and could not reproduce the error. I also checked char of '' and ' ' for lajarat_tipus. Appropriate results were returned. > > David J. > > > > -- > View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-11130-Case-condition-error-tp5814246p5814308.html > Sent from the PostgreSQL - bugs mailing list archive at Nabble.com. > > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs > - Mike Porter PGP Fingerprint: F4 AE E1 9F 67 F7 DA EA 2F D2 37 F3 99 ED D1 C2