Horton

Thread

  1. Problem with BETWEEN and a view.

    Andrew Snow <als@fl.net.au> — 2000-11-15T05:42:47Z

    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);

  2. Re: Problem with BETWEEN and a view.

    Andrew Snow <als@fl.net.au> — 2000-11-15T05:48:32Z

    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??

  3. Re: Problem with BETWEEN and a view.

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-11-15T06:07:06Z

    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

  4. Re: Problem with BETWEEN and a view.

    Andrew Snow <als@fl.net.au> — 2000-11-15T06:35:45Z

    > 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.

  5. Re: Problem with BETWEEN and a view.

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-11-15T06:47:47Z

    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

  6. WHERE-clause evaluation order (was Problem with BETWEEN and a view)

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-11-15T17:45:08Z

    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 Joy
	WHERE 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