Обсуждение: Problem with BETWEEN and a view.
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 | andrew@modulus.org | 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);
Further to this, I wish you to note the following works correctly:
# select start, stop from mailredirs where start < CURRENT_TIMESTAMP;
start | stop
------------------------+------------------------
2000-10-31 17:26:52+11 | 2000-11-25 16:29:01+11
(1 row)
# select start, stop from mailredirs where stop > CURRENT_TIMESTAMP;
start | stop
------------------------+------------------------
2000-10-31 17:26:52+11 | 2000-11-25 16:29:01+11
(1 row)
Also, there is definitely only one row in the entire "Values" table that
contains a value of "Just because."
# select * from values where value LIKE 'Just because.';
id | attributeid | thingid | value
-------+-------------+---------+---------------
13525 | 46 | 1246 | Just because.
(1 row)
Regards,
Andrew.
On Wed, 15 Nov 2000, I wrote:
>
> 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 | andrew@modulus.org | 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??
Andrew Snow <als@fl.net.au> writes:
> # 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??
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 ;-)
regards, tom lane
> 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.
Andrew Snow <als@fl.net.au> writes:
> Here's a simpler script which reproduces the bug:
OK, I can reproduce the bug in 7.0.2 and 7.0.3, but not in current
development sources. More tomorrow ... I'm about out of steam
for tonight ...
regards, tom lane
Andrew Snow <als@fl.net.au> writes:
> Here's a simpler script which reproduces the bug:
Well, after looking at this some more, I'm not convinced it's a bug;
or at least, if it's a bug it's one that can't be fixed without a
fundamental redefinition of rules/views. Boiled down, here's what
you are doing:
CREATE TABLE Joy (x int4, z text);
INSERT INTO Joy VALUES (1, 'i love postgresql');
INSERT INTO Joy VALUES (2, CURRENT_TIMESTAMP - '5 days'::interval);
INSERT INTO Joy VALUES (3, CURRENT_TIMESTAMP + '5 days'::interval);
CREATE VIEW foo AS SELECT z::timestamp AS Start FROM Joy WHERE x = 2;
SELECT * FROM foo; start
------------------------2000-11-10 01:45:09-05
(1 row)
SELECT * FROM foo WHERE Start < CURRENT_TIMESTAMP;
ERROR: Bad timestamp external representation 'i love postgresql'
Now the first select is OK because the WHERE clause filters out the
rows that don't have timestamp-looking values of z before the SELECT's
output values get evaluated. But the second SELECT gets expanded
by the rule rewriter into
SELECT z::timestamp AS Start FROM JoyWHERE x = 2 AND z::timestamp < CURRENT_TIMESTAMP;
Then it's all a matter of what order the WHERE clauses happen to get
applied in --- if the timestamp check gets applied first, the query
fails with exactly the result you see. There is no guarantee that the
clauses that came from inside the view will be applied before those that
came from outside. Indeed, the planner would be very foolish to make
such a guarantee, since the clauses coming from outside the view might
be far more selective and/or might enable use of an index.
In 7.1 it would be possible to force the view to be evaluated as an
independently-planned subplan, and have the outer WHERE clauses be
applied only as filters on the result. However, the performance
implications of doing that are painful enough that I don't really
want to do it. Essentially, we'd be lobotomizing the planner so that
it would not be able to make any choices about when to evaluate WHERE
clauses. This would cause it to miss a lot of good plans for queries
involving views and sub-selects.
The relevant part of SQL92 seems to be section 3.3.4.4 "Rule evaluation
order". As far as I can tell, this leaves implementations a great deal
of freedom to determine evaluation order of elements of a WHERE
expression. Does anyone want to argue that the spec requires us to
be stupid about evaluation order?
regards, tom lane