"Sean Carmody" <sean@categoricalsolutions.com.au> writes:
> CREATE VIEW last_date AS SELECT max(date) AS last_date FROM test
> SELECT test.* FROM test,last_date WHERE date=last_date
> [crashes]
Yeah, it's a bug all right. The rule rewriter has a lot of problems
with views that involve grouping or aggregation --- until it's fixed,
you need to be very wary of that combination of features. A workaround
is to write the query without a rule:
select * from test where date = (select max(date) from test);
which is pretty grotty but I think it works reliably in 6.5.
BTW, in current sources the rule-using query doesn't crash, but it does
deliver the wrong answer :-(. You get multiple copies of the desired
tuple. Apparently the rewriter adds an extra RTE for table 'test' to
the top-level query:
CREATE VIEW last_date AS SELECT max(date) AS last_date FROM test vt;
explain SELECT t.* FROM test t,last_date l WHERE date=last_date;
Nested Loop (cost=473.00 rows=10000 width=12)
InitPlan
-> Aggregate (cost=43.00 rows=1000 width=8)
-> Seq Scan on test vt (cost=43.00 rows=1000 width=8)
-> Seq Scan on test t (cost=43.00 rows=10 width=8)
-> Seq Scan on test vt (cost=43.00 rows=1000 width=4)
EXPLAIN
> Now back to the thread...
>>>>>> SELECT test.* FROM test,last_date WHERE date=last_date
>>>> ^^^^^^
>>>> Ah, your using an alias, but I don't see you declaring an alias
> anywhere.
Nonsense, that's a perfectly valid way of referring to a table.
regards, tom lane