pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG in 6.5 - GROUP BY inheritance
5+ messages / 3 participants
[nested] [flat]

* BUG in 6.5 - GROUP BY inheritance
@ 1999-06-09 14:43 chris@tech.com.au
  0 siblings, 0 replies; 5+ messages in thread

From: chris@tech.com.au @ 1999-06-09 14:43 UTC (permalink / raw)
  To: pgsql-hackers@hub.org; +Cc: chris@tech.com.au


The following query produces the following error

BUT...

if I take out the "*" from category* it starts to work. Unfortunately I need the "*" for my purposes. Bug?

This is from CVS of about a week ago.

SELECT question.title, count(comment.oid) FROM question, category*,comment WHERE comment.webobject = question.oid and question.category = category.oid GROUP BY question.title;
ERROR:  replace_vars_with_subplan_refs: variable not in target list

-- 
Chris Bitmead
http://www.bigfoot.com/~chris.bitmead
mailto:chris.bitmead@bigfoot.com



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* BUG in 6.5 - GROUP BY inheritance
@ 1999-06-10 03:48 Chris Bitmead <chris.bitmead@bigfoot.com>
  1999-06-10 15:06 ` Re: [HACKERS] BUG in 6.5 - GROUP BY inheritance Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 5+ messages in thread

From: Chris Bitmead @ 1999-06-10 03:48 UTC (permalink / raw)
  To: pgsql-hackers@hub.org


The following query produces the following error

BUT...

if I take out the "*" from category* it starts to work. Unfortunately I
need the "*" for my purposes. Bug?

This is from CVS of about a week ago.

SELECT question.title, count(comment.oid) FROM question,
category*,comment WHERE comment.webobject = question.oid and
question.category = category.oid GROUP BY question.title;
ERROR:  replace_vars_with_subplan_refs: variable not in target list

-- 
Chris Bitmead
http://www.bigfoot.com/~chris.bitmead
mailto:chris.bitmead@bigfoot.com



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [HACKERS] BUG in 6.5 - GROUP BY inheritance
  1999-06-10 03:48 BUG in 6.5 - GROUP BY inheritance Chris Bitmead <chris.bitmead@bigfoot.com>
@ 1999-06-10 15:06 ` Tom Lane <tgl@sss.pgh.pa.us>
  1999-06-11 13:12   ` Re: [HACKERS] BUG in 6.5 - GROUP BY inheritance Chris Bitmead <chris.bitmead@bigfoot.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Tom Lane @ 1999-06-10 15:06 UTC (permalink / raw)
  To: Chris Bitmead <chris.bitmead@bigfoot.com>; +Cc: pgsql-hackers@hub.org

Chris Bitmead <chris.bitmead@bigfoot.com> writes:
> This is from CVS of about a week ago.

> SELECT question.title, count(comment.oid) FROM question,
> category*,comment WHERE comment.webobject = question.oid and
> question.category = category.oid GROUP BY question.title;

See my message from Sunday, "inherited GROUP BY is busted":

: It turns out that pretty much *anything* involving grouping or
: aggregation would fail if the query used inheritance, because the

This query seems to work with current sources (although EXPLAIN
fails for it --- I think that explain.c is out of sync with the
executor).

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [HACKERS] BUG in 6.5 - GROUP BY inheritance
  1999-06-10 03:48 BUG in 6.5 - GROUP BY inheritance Chris Bitmead <chris.bitmead@bigfoot.com>
  1999-06-10 15:06 ` Re: [HACKERS] BUG in 6.5 - GROUP BY inheritance Tom Lane <tgl@sss.pgh.pa.us>
@ 1999-06-11 13:12   ` Chris Bitmead <chris.bitmead@bigfoot.com>
  1999-06-11 14:02     ` Re: [HACKERS] BUG in 6.5 - GROUP BY inheritance Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 5+ messages in thread

From: Chris Bitmead @ 1999-06-11 13:12 UTC (permalink / raw)
  To: pgsql-hackers@hub.org


Ok, I upgraded my CVS sources yesterday, and simple inherited/GROUP BY
queries are working again. But a more complex one still seems broken.
This is the query...




httpd=> SELECT question.oid, question.title, question.summary,
question.datetime, category.oid, category.title, category.image, email,
webuser.name, count(comment.oid) FROM question, webuser,
category*,comment WHERE question.webuser = webuser.oid and
question.category = category.oid AND comment.webobject = question.oid
GROUP BY question.oid, question.title, question.summary,
question.datetime, category.oid, category.title, category.image, email,
webuser.name UNION SELECT question.oid, question.title,
question.summary, question.datetime, category.oid, category.title,
category.image, webuser.email, webuser.name, 0 FROM question, webuser,
category* WHERE question.webuser = webuser.oid and question.category =
category.oid;
ERROR:  replace_vars_with_subplan_refs: variable not in target list


Tom Lane wrote:
> 
> Chris Bitmead <chris.bitmead@bigfoot.com> writes:
> > This is from CVS of about a week ago.
> 
> > SELECT question.title, count(comment.oid) FROM question,
> > category*,comment WHERE comment.webobject = question.oid and
> > question.category = category.oid GROUP BY question.title;
> 
> See my message from Sunday, "inherited GROUP BY is busted":
> 
> : It turns out that pretty much *anything* involving grouping or
> : aggregation would fail if the query used inheritance, because the
> 
> This query seems to work with current sources (although EXPLAIN
> fails for it --- I think that explain.c is out of sync with the
> executor).
> 
>                         regards, tom lane

-- 
Chris Bitmead
mailto:chris@tech.com.au
http://www.techphoto.org - Photography News, Stuff that Matters



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [HACKERS] BUG in 6.5 - GROUP BY inheritance
  1999-06-10 03:48 BUG in 6.5 - GROUP BY inheritance Chris Bitmead <chris.bitmead@bigfoot.com>
  1999-06-10 15:06 ` Re: [HACKERS] BUG in 6.5 - GROUP BY inheritance Tom Lane <tgl@sss.pgh.pa.us>
  1999-06-11 13:12   ` Re: [HACKERS] BUG in 6.5 - GROUP BY inheritance Chris Bitmead <chris.bitmead@bigfoot.com>
@ 1999-06-11 14:02     ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 5+ messages in thread

From: Tom Lane @ 1999-06-11 14:02 UTC (permalink / raw)
  To: Chris Bitmead <chris.bitmead@bigfoot.com>; +Cc: pgsql-hackers@hub.org

Chris Bitmead <chris.bitmead@bigfoot.com> writes:
> httpd=> SELECT question.oid, question.title, question.summary,
> question.datetime, category.oid, category.title, category.image, email,
> webuser.name, count(comment.oid) FROM question, webuser,
> category*,comment WHERE question.webuser = webuser.oid and
> question.category = category.oid AND comment.webobject = question.oid
> GROUP BY question.oid, question.title, question.summary,
> question.datetime, category.oid, category.title, category.image, email,
> webuser.name UNION SELECT question.oid, question.title,
> question.summary, question.datetime, category.oid, category.title,
> category.image, webuser.email, webuser.name, 0 FROM question, webuser,
> category* WHERE question.webuser = webuser.oid and question.category =
> category.oid;
> ERROR:  replace_vars_with_subplan_refs: variable not in target list

Would it be possible to have some CREATE TABLE statements for these
tables, so that the problem can be reproduced without so much
reverse-engineering?

BTW, I do not know when "yesterday" your time might have been,
but I committed a critical fix for UNION + GROUP BY in
backend/optimizer/prep/prepunion.c at 10 Jun 02:55 EDT (-0400).
If you don't have that version please update and try again.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~1999-06-11 14:02 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-06-09 14:43 BUG in 6.5 - GROUP BY inheritance chris@tech.com.au
1999-06-10 03:48 BUG in 6.5 - GROUP BY inheritance Chris Bitmead <chris.bitmead@bigfoot.com>
1999-06-10 15:06 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-06-11 13:12   ` Chris Bitmead <chris.bitmead@bigfoot.com>
1999-06-11 14:02     ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox