Bug in check constraint?
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.
"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
Import Notes
Reply to msg id not found: 1133.201.19.109.189.1105905897.squirrel@www.pbh.gov.brReference msg id not found: 1133.201.19.109.189.1105905897.squirrel@www.pbh.gov.br | Resolved by subject fallback
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.