Re: [BUGS] simple query triggers abnormal termination

"Sean Carmody" <sean(at)categoricalsolutions(dot)com(dot)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