Thread: Re: [SQL] COUNT(*) to find records which have a certain number of dependencies ?
Re: [SQL] COUNT(*) to find records which have a certain number of dependencies ?
From
Greg Stark
Date:
T E Schmitz <mailreg@numerixtechnology.de> writes:
> There's a German saying "Go and find a parking-meter", i.e. suggesting to pop a
> coin in the parking-meter and talk to it as nobody else wants to listen. ;-)
Yes well I anticipated such a response. So I tried my hand at it myself.
Well I finally found a problem tractable enough for me to get all the way from
start to end in a single sitting. Here's a simple solution to my complaint.
This patch allows subqueries without aliases. This is SQL-non-spec-compliant
syntax that Oracle supports and many users expect to work. It's also just
damned convenient, especially for simple ad-hoc queries.
There was a comment saying an alias name would have to be constructed so I
took that approach. It seems like it would have been cleaner to just ensure
that the code doesn't fail when no alias is present. But I have no idea how
much work would be involved in that, so I just took advice from the anonymous
author of the comment.
Incidentally, It seems weird to me that the counter doesn't reset for every
query. Perhaps I should change that?
Index: src/backend/parser/gram.y
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/parser/gram.y,v
retrieving revision 2.475
diff -u -p -c -r2.475 gram.y
cvs diff: conflicting specifications of output style
*** src/backend/parser/gram.y 29 Aug 2004 04:12:35 -0000 2.475
--- src/backend/parser/gram.y 20 Sep 2004 21:34:13 -0000
*************** table_ref: relation_expr
*** 5158,5177 ****
{
/*
* The SQL spec does not permit a subselect
! * (<derived_table>) without an alias clause,
! * so we don't either. This avoids the problem
! * of needing to invent a unique refname for it.
! * That could be surmounted if there's sufficient
! * popular demand, but for now let's just implement
! * the spec and see if anyone complains.
! * However, it does seem like a good idea to emit
! * an error message that's better than "syntax error".
*/
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("subquery in FROM must have an alias"),
! errhint("For example, FROM (SELECT ...) [AS] foo.")));
! $$ = NULL;
}
| select_with_parens alias_clause
{
--- 5158,5172 ----
{
/*
* The SQL spec does not permit a subselect
! * (<derived_table>) without an alias clause, We surmount
! * this because of popular demand by gining up a fake name
! * in transformRangeSubselect
*/
!
! RangeSubselect *n = makeNode(RangeSubselect);
! n->subquery = $1;
! n->alias = NULL;
! $$ = (Node *) n;
}
| select_with_parens alias_clause
{
Index: src/backend/parser/parse_clause.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/parser/parse_clause.c,v
retrieving revision 1.136
diff -u -p -c -r1.136 parse_clause.c
cvs diff: conflicting specifications of output style
*** src/backend/parser/parse_clause.c 29 Aug 2004 05:06:44 -0000 1.136
--- src/backend/parser/parse_clause.c 20 Sep 2004 21:34:14 -0000
*************** transformRangeSubselect(ParseState *psta
*** 418,426 ****
* an unlabeled subselect.
*/
if (r->alias == NULL)
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("subquery in FROM must have an alias")));
/*
* Analyze and transform the subquery.
--- 418,434 ----
* an unlabeled subselect.
*/
if (r->alias == NULL)
! {
! static int subquery_counter = 1;
! static char buf[30];
!
! sprintf(buf, "*SUBQUERY*%d*", subquery_counter++);
!
! r->alias = makeNode(Alias);
! r->alias->aliasname = pstrdup(buf);
! r->alias->colnames = NULL;
! }
!
/*
* Analyze and transform the subquery.
Re: [SQL] COUNT(*) to find records which have a certain number of dependencies ?
From
Tom Lane
Date:
Greg Stark <gsstark@mit.edu> writes:
> This patch allows subqueries without aliases. This is SQL-non-spec-compliant
> syntax that Oracle supports and many users expect to work.
AFAIR you're the first to propose that we ignore the SQL spec here.
When I wrote "see if anyone complains", I had in mind waiting for
quite a few complaints before contemplating changing this...
regards, tom lane
Re: [SQL] COUNT(*) to find records which have a certain number of dependencies ?
From
Greg Stark
Date:
Tom Lane <tgl@sss.pgh.pa.us> writes: > Greg Stark <gsstark@mit.edu> writes: > > This patch allows subqueries without aliases. This is SQL-non-spec-compliant > > syntax that Oracle supports and many users expect to work. > > AFAIR you're the first to propose that we ignore the SQL spec here. > When I wrote "see if anyone complains", I had in mind waiting for > quite a few complaints before contemplating changing this... I understand. But I expect there are lots of users this annoys. It's just such an easy thing to work around that it would be a petty thing to complain about. That's the only reason I never complained about it all this time it was annoying me. Not until I felt ready to poke around and fix it myself. And I'm not suggesting ignoring the spec, just allowing the user to ignore it, and not having postgres go out of its way to enforce the spec on users. I doubt the spec says that the implementation cannot allow the syntax. ...Ok, well I wouldn't put it past the spec to do so. But it does so about lots of things that Postgres allows. The general attitude postgres has seems to be to extend the spec whenever it's nice for the user as long as doing so doesn't interfere with actually supporting any spec compliant code. It's not like postgres is a good platform for testing spec compliance of SQL code otherwise. It just seems excessively obnoxious to refuse queries because they don't match a grammar precisely when the missing piece is entirely not needed by the database. It doesn't cause any ambiguity or other problems with the SQL. It doesn't cost a single cycle in the normal case. The code doesn't impact anything else in the system. It's the database being intentionally nosy and picky about something just because it can. -- greg