From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Sean Carmody" <sean(at)categoricalsolutions(dot)com(dot)au> |
Cc: | pgsql-bugs(at)postgreSQL(dot)org |
Subject: | Re: [BUGS] simple query triggers abnormal termination |
Date: | 1999-11-05 06:20:46 |
Message-ID: | 29255.941782846@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
"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
From | Date | Subject | |
---|---|---|---|
Next Message | Todd Vierling | 1999-11-07 19:43:10 | No index support for date/time types? |
Previous Message | Sean Carmody | 1999-11-05 05:22:48 | simple query triggers abnormal termination |