Skip site navigation (1) Skip section navigation (2)

Re: [BUGS] simple query triggers abnormal termination

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 (view raw or flat)
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

pgsql-bugs by date

Next:From: Todd VierlingDate: 1999-11-07 19:43:10
Subject: No index support for date/time types?
Previous:From: Sean CarmodyDate: 1999-11-05 05:22:48
Subject: simple query triggers abnormal termination

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group