From: | Andrew Snow <als(at)fl(dot)net(dot)au> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: Problem with BETWEEN and a view. |
Date: | 2000-11-15 06:35:45 |
Message-ID: | Pine.BSF.4.21.0011151727510.83404-100000@jander.fl.net.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs pgsql-hackers |
> 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2000-11-15 06:47:47 | Re: Problem with BETWEEN and a view. |
Previous Message | Tom Lane | 2000-11-15 06:07:06 | Re: Problem with BETWEEN and a view. |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2000-11-15 06:47:47 | Re: Problem with BETWEEN and a view. |
Previous Message | Tom Lane | 2000-11-15 06:07:06 | Re: Problem with BETWEEN and a view. |