[BUGS] Problem with BETWEEN and a view.

[Andrew Snow]

I just installed v7.0.3 release on a FreeBSD 4.x system.  (Problem still happened in 
7.0.2 too).

This is the problem I noticed:

# select * from mailredirs;
 username |    destination     |         start          |          stop          |    
reason
----------+--------------------+------------------------+------------------------+---------------
 als      | [EMAIL PROTECTED] | 2000-10-31 17:26:52+11 | 2000-11-25 16:29:01+11 | 
Just because.
(1 row)

# select * from mailredirs where start < CURRENT_TIMESTAMP and stop > 
CURRENT_TIMESTAMP;
ERROR:  Bad timestamp external representation 'Just because.'

Why is it even looking at the 'reason' field??



Unfortunately it gets more complicated here, as I am going to dump you with a load of 
table
and view definitions.  

CREATE VIEW MailRedirs AS
 SELECT u.Name AS Username, v1.Value AS Destination,     v2.Value::timestamp AS Start,
                            v3.Value::timestamp AS Stop, v4.Value AS Reason
 FROM Values v1, Values v2, Values v3, Values v4, Users u
 WHERE v1.AttributeID = get_attributeid('MailRedir', 'Dest')
   AND v2.AttributeID = get_attributeid('MailRedir','Start')
   AND v3.AttributeID = get_attributeid('MailRedir','End')
   AND v4.AttributeID = get_attributeid('MailRedir','Reason')
   AND u.ID=v1.ThingID AND u.ID=v2.ThingID AND u.ID=v3.ThingID AND u.ID=v4.ThingID;

The table "Values" joins an Attribute to a Thing with a text value.  "Users" is a
view on "Things", pulling out only "Things" of type User...

CREATE TABLE Values (
  ID           serial PRIMARY KEY,
  AttributeID  int4 NOT NULL REFERENCES Attributes,
  ThingID      int4 NOT NULL REFERENCES Things ON DELETE CASCADE,
  Value        text NOT NULL
);
CREATE TABLE Attributes (
  ID       serial PRIMARY KEY,
  Name     text NOT NULL,
  Subname  text NOT NULL,
  Format   text NOT NULL,
  UNIQUE(Name, Subname)
);
CREATE TABLE Things (
  ID        serial PRIMARY KEY,
  Name      text NOT NULL,
  TypeID    int4 NOT NULL REFERENCES Types,
  ParentID  int4 REFERENCES Things DEFAULT NULL
);
CREATE VIEW Users AS
 SELECT th.ID, th.Name, th2.Name AS ParentName, th2.ID AS ParentID
    FROM Things th, Things th2
    WHERE th2.ID=th.ParentID AND Types.Name='User' AND th.TypeID=Types.ID;
CREATE FUNCTION get_attributeid(text, text) returns int4 AS 'SELECT ID FROM Attributes 
WHERE (Name,Subname)=($1,$2)'
 LANGUAGE 'sql' WITH (iscachable);