Thread

1. Postgres SQL 7.1 a thank you and a possible bug

   Nic Ferrier <nferrier@tapsellferrier.co.uk> — 2001-02-02T19:32:38Z

   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 
   
   

2. Re: Postgres SQL 7.1 a thank you and a possible bug

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-02-03T21:20:56Z

   "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