Bug in check constraint?

From: gonzaga(at)pbh(dot)gov(dot)br
To: pgsql-bugs(at)postgresql(dot)org
Subject: Bug in check constraint?
Date: 2005-01-15 20:08:23
Message-ID: 1322.201.19.83.219.1105819703.squirrel@www.pbh.gov.br
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hi,

Forgive me my poor English for writing.

Sees, below, that it seems to have one bug in set transform_null_equals
or, then, in <> NULL.

This fact occurs in Versions: 7.4.5 and 8.0.0-rc2.

-- Creation with transform_null_equals set to off

set transform_null_equals to OFF;

--drop table cntpagit1;

Create table cntpagit1 (VALORPG numeric(10,2), DTPAGTO dates);

ALTER TABLE CNTPAGIT1

ADD CONSTRAINT TTT

CHECK ((VALORPG > 0 AND DTPAGTO <> NULL) OR

(VALORPG = 0 AND DTPAGTO = NULL));

-- They see as it was in the Catalog

-- Table: CNTPAGIT1

-- DROP TABLE CNTPAGIT1;

CREATE TABLE cntpagit1

(
valorpg numeric(10,2), dtpagto date,

CONSTRAINT ttt

CHECK (valorpg > 0::numeric AND dtpagto <> NULL::date OR

valorpg = 0::numeric AND dtpagto = NULL::date)

) WITH OIDS;

ALTER TABLE cntpagit1 OWNER postgres;

-- Result of sql.

insert into cntpagit1 values(1, NULL);

Query returned successfully: one row 20540 with OID inserted, 60 ms
execution times.

insert into cntpagit1 values(0, '20050115 ');

Query returned successfully: one row 20541 with OID inserted, 60 ms
execution times.

-- Creation with transform_null_equals set to on

set transform_null_equals to ON;

-- drop table cntpagit1;

Create table cntpagit1 ( VALORPG numeric(10,2), DTPAGTO dates);

ALTER TABLE CNTPAGIT1

ADD CONSTRAINT TTT

CHECK ((VALORPG > 0 AND DTPAGTO <> NULL) OR

(VALORPG = 0 AND DTPAGTO = NULL));

-- They see as it was in the Catalog.

-- Table: CNTPAGIT1

-- DROP TABLE CNTPAGIT1;

CREATE TABLE cntpagit1 (valorpg numeric(10,2), dtpagto dates,

--- *** Has one bug in the transformation of <> NULL for IS NOT NULL? ***

CONSTRAINT ttt CHECK (valorpg > 0::numeric AND dtpagto <> NULL::date OR
valorpg = 0::numeric AND dtpagto IS NULL)

) WITH OIDS;

ALTER TABLE cntpagit1 OWNER postgres;

-- Result of sql.

insert into cntpagit1 values(1, NULL);

Query returned successfully: one row 20545 with OID inserted, 70 ms
execution times.

insert into cntpagit1 values(0, '20050115 ');

ERROR: new row will be relation "cntpagit1" violates check constraint "ttt"

-- Creating the check with IS NOT NULL and IS NULL funcionou correctly.

Regards,

Luiz Gonzaga da Mata.

Brasil.

Browse pgsql-bugs by date

  From Date Subject
Next Message Fahad G. 2005-01-16 01:36:40 Re: BUG #1393: Adding 'LIMIT 1' to the query halts forever
Previous Message Magnus Hagander 2005-01-15 17:44:13 Re: BUG #1400: libeay32.dll error