Hi,

forgive me the poor English for the writing, for technique reading is a
little better..

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.


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

> transform_null_equals only catches the exact syntax "something = NULL". It
> does not touch "something <> NULL". The latter is always going to yield
> NULL, by definition.

Yes, I saw this in parser_expr.c and the documentation.

1)In code "dtpagto IS NULL" is not the same thing that !(dtpagto IS NULL),
or either, a condition bolean.

2) if "dtpagto <> NULL" is not a valid codification and not checked, he
would not be correct that a message of error while creating constraint.
The fact not to occur the error message, can delude the programmer of the
SGBD with in sample of test.


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"

regards,

Luiz Gonzaga da Mata.

Brasil.




---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

"Luiz Gonzaga da Mata" <gonzaga@pbh.gov.br> writes:
> Sees, below, that it seems to have one bug in set transform_null_equals
> or, then, in <> NULL.

transform_null_equals only catches the exact syntax "something = NULL".
It does not touch "something <> NULL". The latter is always going to
yield NULL, by definition.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend