From: | christopher(dot)hamel(at)zimmer(dot)com |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #10254: Joined Constraints not invoked on date ranges |
Date: | 2014-05-07 15:24:36 |
Message-ID: | 20140507152436.1397.689@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 10254
Logged by: Christopher Hamel
Email address: christopher(dot)hamel(at)zimmer(dot)com
PostgreSQL version: 9.3.0
Operating system: RedHat 6.4
Description:
If you have this theoretical structure:
create table stage.header (
id int not null,
transaction_date date not null
);
create table stage.line (
header_id int not null,
transaction_date date not null,
line_id int not null
);
create table stage.header_2013 (
constraint header_2013_ck1 check (transaction_date >= '2013-01-01' and
transaction_date < '2014-01-01')
) inherits (stage.header);
create table stage.header_2014 (
constraint header_2014_ck1 check (transaction_date >= '2014-01-01' and
transaction_date < '2015-01-01')
) inherits (stage.header);
create table stage.line_2013 (
constraint line_2013_ck1 check (transaction_date >= '2013-01-01' and
transaction_date < '2014-01-01')
) inherits (stage.line);
create table stage.line_2014 (
constraint line_2014_ck1 check (transaction_date >= '2014-01-01' and
transaction_date < '2015-01-01')
) inherits (stage.line);
If I run an explain plan on the following query:
select *
from
stage.header h
join stage.line l on
h.id = l.header_id and
h.transaction_date = l.transaction_date
where
h.transaction_date = '2014-03-01'
It correctly invokes the check constraint on both h and l and only reads the
"2014" tables.
However, if I change the "= 2014-03-01" to "> 2014-03-01" the check
constraint is ignored.
If I specifically invoke the range on both the h and l tables, it will work
fine, but since the join specifies those fields have to be the same, can
that condition be propagated automatically?
From | Date | Subject | |
---|---|---|---|
Next Message | Stephen Frost | 2014-05-07 15:44:31 | Re: BUG #10250: pgAdmin III 1.16.1 stores unescaped plaintext password |
Previous Message | Tom Lane | 2014-05-07 14:15:24 | Re: Server process crash - Segmentation fault |