Re: Problem with BETWEEN and a view.

> Looks like a bug to me, but I'd like not to have to reverse-engineer the
> test case before I can look at it.  Could you provide some sample data,
> as well as the missing "Types" table declaration?  Ideally a psql script
> file to load everything up from scratch and trigger the error ;-)

Well, I dont blame you!! I am going insane with this database over here...
arguably the 'bug' is that I'm doing something which shouldn't be done by a
normal sane person ;-)


Here's a simpler script which reproduces the bug:


CREATE TABLE Happy (
  x   int4 PRIMARY KEY,
  y   text
);
CREATE FUNCTION get_happyx(text) RETURNS int4 AS
  'SELECT x FROM Happy WHERE y = $1' LANGUAGE 'sql' WITH (iscachable);

CREATE TABLE Joy (
  happyx  int4 REFERENCES Happy,
  z       text
);

INSERT INTO Happy (x,y) VALUES (1, 'One');
INSERT INTO Happy (x,y) VALUES (2, 'Two');
INSERT INTO Happy (x,y) VALUES (3, 'Three');
INSERT INTO Happy (x,y) VALUES (4, 'Four');

INSERT INTO Joy (happyx,z) VALUES (1, 'i love postgresql');
INSERT INTO Joy (happyx,z) VALUES (2, CURRENT_TIMESTAMP - '5 days'::interval);
INSERT INTO Joy (happyx,z) VALUES (3, CURRENT_TIMESTAMP + '5 days'::interval);
INSERT INTO Joy (happyx,z) VALUES (4, 'Tom Lane r0x0rs');


-- This view is the centre of the problem:

CREATE VIEW Depressed AS
  SELECT j1.z AS Text1, j2.z::timestamp AS Start,
         j3.z::timestamp AS Stop, j4.z AS Text2
  FROM   Joy j1, Joy j2, Joy j3, Joy j4
  WHERE  j1.happyx = get_happyx('One')
     AND j2.happyx = get_happyx('Two')
     AND j3.happyx = get_happyx('Three')
     AND j4.happyx = get_happyx('Four');

----------------------------------------------------------------------------------------


Now to test it:

foo=# SELECT * FROM Depressed;
       text1       |         start          |          stop          |      text2
-------------------+------------------------+------------------------+-----------------
 i love postgresql | 2000-11-10 17:25:45+11 | 2000-11-20 17:25:45+11 | Tom Lane r0x0rs
(1 row)

foo=# SELECT * FROM Depressed WHERE Start < CURRENT_TIMESTAMP AND Stop > CURRENT_TIMESTAMP;
ERROR:  Bad timestamp external representation 'i love postgresql'


Bingo!


Hope that helps,
Andrew.