Postgres SQL 7.1 a thank you and a possible bug

Started by Nic Ferrierabout 25 years ago2 messagesgeneral
Jump to latest
#1Nic Ferrier
nferrier@tapsellferrier.co.uk

First of all 7.1 is great! I'm using beta4 and it seems to be working
very well.

Thank's particularly for the SQL92 join syntax (though union joins
would be usefull).

I have a problem though and I'm not sure if it's a bug or not so I
thought I'd discuss it here first.

The synopsis of the problem is that date columns don't seem to be
working as dates when they're selected through a union view.

For example

create table A
( id INTEGER,
date DATE);

insert into table A values (1,'1 dec 1999');

create table B
( id INTEGER,
somedate DATE);

insert into table B values (2,'3 jan 2000');

create view C as
select id,date from A
union
select id,somedate from B;

select * from C where date<'1 jan 2000';

id date
----------------------------
1 '1999-12-01'
2 '2000-01-03'

select * from C where date<'1 jan 2001';

id date
----------------------------
1 '1999-12-01'
2 '2000-01-03'

Which seems strange especially as this:

\d C

produces the following:

Attribute | Type | Modifier
-----------+------+----------
id | integer |
date | date |

I've tried casting the date in various ways but none of it works.

Is this a bug or am I doing something gloriously stupid?

Nic Ferrier

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Nic Ferrier (#1)
Re: Postgres SQL 7.1 a thank you and a possible bug

"Nic Ferrier" <nferrier@tapsellferrier.co.uk> writes:

The synopsis of the problem is that date columns don't seem to be
working as dates when they're selected through a union view.

No, the problem is that restriction clauses applied to views that
contain unions are busted in beta4 :-(. Thanks for the bug report!
It's fixed in current CVS, or use tomorrow morning's snapshot.

regards, tom lane