Thread

Re: [BUGS] simple query triggers abnormal termination

Tom Lane <tgl@sss.pgh.pa.us> — 1999-11-05T06:20:46Z

"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