FYI, we decided we didn't want this additional capability.
---
Florian G. Pflug wrote:
> Kevin Grittner wrote:
> On Thu, Aug 23, 2007 at 3:01 PM, in message <[EMAIL PROTECTED]>,
> > Tom Lane <[EMAIL PROTECTED]> wrote:
This has been saved for the 8.4 release:
http://momjian.postgresql.org/cgi-bin/pgpatches_hold
---
Andrew Dunstan wrote:
>
>
> Chuck McDevitt wrote:
> > PostgreSQL already has a huge amount of
> > "non-standard" sy
> > I still don't see it as terrifically useful functionality, given
that it's
> > just saving you 4 keystrokes ...
>
> Less than that, because the AS is optional. The only argument I've
> heard that carries much weight with me is that it eases porting from
> other DBMS's that allow this. Are
Ron Mayer <[EMAIL PROTECTED]> writes:
> Warning: Use of frivolous nonstandard behavior XXX.
> Hint: Use the standard YYY instead.
If these behaviors were really "frivolous" this might fly, but I don't
think the majority of users will hold still for the removal of either
GROUP BY 1 or ORDER BY
Tom Lane wrote:
> Part of the reason for being conservative about changing here
> is that we've got a mix of standard and nonstandard behaviors
>
> A lot of this is legacy behavior that would never have passed muster
> if it had been newly proposed in the last few years --- we have gotten
> *far*
"Tom Lane" <[EMAIL PROTECTED]> writes:
> Gregory Stark <[EMAIL PROTECTED]> writes:
>
>> Note that if you use something like fetchrow_hashref it will actually
>> condense
>> out duplicate column names since it loads the row into a hash. So if you
>> you're writing a program which just wants to dum
Gregory Stark <[EMAIL PROTECTED]> writes:
> "Tom Lane" <[EMAIL PROTECTED]> writes:
>> ... note that we fail to meet (c)
>> exactly, since we don't bother to generate names that are distinct ---
>> but in practice no one seems to care about that.)
> Actually I suspect there are people who get annoy
On Thu, Aug 23, 2007 at 02:06:16PM -0400, Chuck McDevitt wrote:
> In general, we wouldn't want to support any de facto standard that:
>
> 1. Is supported only by one vendor
> 2. Causes any standard SQL statement to fail, or return a different
> answer from the standard.
>
> The proposed cha
"Tom Lane" <[EMAIL PROTECTED]> writes:
> c) Otherwise, the of the i-th column of thespecification> is implementation-dependent and different
> from the of any column, other than itself, of
> a table referenced by any contained in the
>
Peter Eisentraut <[EMAIL PROTECTED]> writes:
> Florian G. Pflug wrote:
>> knowing *why* the standard committee disallows that syntax -
>> and why everybody except oracle chose to agree with it would be quite
>> interesting.
> ...
> If you believe that, then a table source without any name at all,
"Florian G. Pflug" <[EMAIL PROTECTED]> writes:
> The really funny thing is that pgsql, mysql and at least sybase
> *explicitly* dissallow the no-alias case.
I knew that pgsql does that, because I made sure it did ;-). But it is
pretty interesting that these other DBMSes also go out of their way t
Florian G. Pflug wrote:
> knowing *why* the standard committee disallows that syntax -
> and why everybody except oracle chose to agree with it would be quite
> interesting.
Consider that we rightfully disallow
SELECT * FROM tab1, tab1;
because there are two tables with the same name involved.
On 8/23/07, Florian G. Pflug <[EMAIL PROTECTED]> wrote:
> Ben Tilly wrote:
> > On 8/22/07, Michael Glaesemann <[EMAIL PROTECTED]> wrote:
> >> On Aug 22, 2007, at 20:49 , Ben Tilly wrote:
> >>
> >>> If your implementation accepts:
> >>>
> >>> group by case when true then 'foo' end
> >> What would
Kevin Grittner wrote:
On Thu, Aug 23, 2007 at 3:01 PM, in message <[EMAIL PROTECTED]>,
Tom Lane <[EMAIL PROTECTED]> wrote:
The only argument I've
heard that carries much weight with me is that it eases porting from
other DBMS's that allow this. Are there any others besides Oracle?
select *
Chuck McDevitt wrote:
Tom, it isn't just a case of "convenience". When we are trying to
convert users from another database (say Oracle for example) to
PostgeSQL, one of the big stumbling blocks that slows down the work is
all the little query changes that people have to make (people who might
>>> On Thu, Aug 23, 2007 at 3:01 PM, in message <[EMAIL PROTECTED]>,
Tom Lane <[EMAIL PROTECTED]> wrote:
> The only argument I've
> heard that carries much weight with me is that it eases porting from
> other DBMS's that allow this. Are there any others besides Oracle?
> select * from (select
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Michael Glaesemann wrote:
>> How about something like gensym?
> It is dangerous to provide a synthetic name; if the standard ever gets
> modified to support alias-less subqueries, they would likely choose a
> different name-generating algorithm, and we
Tom,
> so you don't get to point to them as one that we'd improve
> compatibility with.
Derby & DB2 are also strict.
--
Josh Berkus
PostgreSQL @ Sun
San Francisco
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your
Josh Berkus <[EMAIL PROTECTED]> writes:
> I still don't see it as terrifically useful functionality, given that it's
> just saving you 4 keystrokes ...
Less than that, because the AS is optional. The only argument I've
heard that carries much weight with me is that it eases porting from
other DB
Michael Glaesemann wrote:
>
> On Aug 23, 2007, at 14:25 , Tom Lane wrote:
>
>> Josh Berkus <[EMAIL PROTECTED]> writes:
>>> I just don't see the ability to omit the alias in a query with only one
>>> subquery (the only circumstances under which it would be safe to do so)
>>> as
>>> any significant
"Chuck McDevitt" <[EMAIL PROTECTED]> writes:
> Except "group by 1" meaning "group by column 1" is a PostgreSQL extension,
> not a SQL standard feature, if I recall.
Correct. Part of the reason for being conservative about changing here
is that we've got a mix of standard and nonstandard behavior
Tom,
> As long as you take care that the subquery's column names don't match
> any other ones in the query, you don't *need* an alias for it ---
> there'll be no need to qualify the column names. This extends just
> fine to multiple subqueries.
Oh, hmmm. I was thinking this would somehow be mor
On Aug 23, 2007, at 14:25 , Tom Lane wrote:
Josh Berkus <[EMAIL PROTECTED]> writes:
I just don't see the ability to omit the alias in a query with
only one
subquery (the only circumstances under which it would be safe to
do so) as
any significant gain in fuctionality.
Why do you think it
Josh Berkus <[EMAIL PROTECTED]> writes:
> I just don't see the ability to omit the alias in a query with only one
> subquery (the only circumstances under which it would be safe to do so) as
> any significant gain in fuctionality.
Why do you think it'd be restricted to only one subquery?
As lon
Chuck McDevitt wrote:
PostgreSQL already has a huge amount of
"non-standard" syntax and semantics (perhaps "extensions" is a better
word?).
Everything from non-standard cast operator, non-standard substr,
non-standard trim, non standard group by semantics (allowing simple ints
to mean column nu
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-hackers-
> [EMAIL PROTECTED] On Behalf Of Florian G. Pflug
> Sent: Thursday, August 23, 2007 11:04 AM
> To: Ben Tilly
> Cc: Michael Glaesemann; Gregory Stark; pgsql-hackers@postgresql.org
> Subject: Re:
On Aug 23, 2007, at 12:25 , Ben Tilly wrote:
It is already extended in postgres. For pretty good reasons.
Yes, this particular point is moot. Whether or not the reasons are
good is another thing and a difference of opinion.
I'm not surprised that the window clause section is impenetrabl
> -Original Message-
> From: Andrew Dunstan [mailto:[EMAIL PROTECTED]
> Sent: Thursday, August 23, 2007 10:26 AM
> To: Chuck McDevitt
> Cc: Tom Lane; Gregory Stark; Michael Glaesemann; Ben Tilly; pgsql-
> [EMAIL PROTECTED]
> Subject: Re: [HACKERS] SQL feature requ
On Thursday 23 August 2007 09:55, Alvaro Herrera wrote:
> > I think the compromise is to instead of generating aliases at all just
> > use an alias like "*Anonymous Subquery*" and add a boolean flag
> > indicating that that range table is anonymous and not a valid target for
> > references. I start
Ben Tilly wrote:
On 8/22/07, Michael Glaesemann <[EMAIL PROTECTED]> wrote:
On Aug 22, 2007, at 20:49 , Ben Tilly wrote:
If your implementation accepts:
group by case when true then 'foo' end
What would that mean? Regardless of whether or not it's accepted, it
should have *some* meaning.
On 8/23/07, Michael Glaesemann <[EMAIL PROTECTED]> wrote:
> On Aug 23, 2007, at 10:47 , Ben Tilly wrote:
[...]
> > Why does it seem like a bug to you?
> >
> > Turn it around, and tell me in what way is its behaviour surprising to
> > someone who knows SQL. You asked to group on something that is t
On 8/23/07, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Chuck McDevitt" <[EMAIL PROTECTED]> writes:
> > Tom, it isn't just a case of "convenience". When we are trying to
> > convert users from another database (say Oracle for example) to
> > PostgeSQL, one of the big stumbling blocks that slows down th
Gregory Stark escribió:
> I get this about once a day because I frequently type this ad-hoc query and
> always forget to put the alias in the first time:
>
> postgres=# select count(*),n from (select count(*) as n from test group by i)
> group by n;
> ERROR: subquery in FROM must have an alias
"Chuck McDevitt" <[EMAIL PROTECTED]> writes:
> Tom, it isn't just a case of "convenience". When we are trying to
> convert users from another database (say Oracle for example) to
> PostgeSQL, one of the big stumbling blocks that slows down the work is
> all the little query changes that people hav
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Chuck McDevitt wrote:
> Tom, it isn't just a case of "convenience". When we are trying to
> convert users from another database (say Oracle for example) to
> PostgeSQL, one of the big stumbling blocks that slows down the work is
> all the little quer
On 8/22/07, Michael Glaesemann <[EMAIL PROTECTED]> wrote:
>
> On Aug 22, 2007, at 20:36 , Ben Tilly wrote:
[...]
> > I can well believe that the standard says that you must accept
> > subqueries with aliases. But does it say you must reject subqueries
> > without aliases? I strongly doubt that.
>
Chuck McDevitt wrote:
Sometimes supporting "de-facto" standards as well as official standards
makes sense.
On that basis we would support huge pieces of stuff that emulates MySQL
too. Or perhaps you'd like us to follow Oracle's treatment of NULL. Who
gets to choose what is the de facto
"Tom Lane" <[EMAIL PROTECTED]> writes:
> Gregory Stark <[EMAIL PROTECTED]> writes:
>> This seems like a particularly petty case compared to a lot of other
>> extensions we do allow.
>
> That's exactly the problem. Most of our other extensions are justified
> by some significant capability gain.
>
> > This seems like a particularly petty case compared to a lot of other
> > extensions we do allow.
>
> That's exactly the problem. Most of our other extensions are
justified
> by some significant capability gain. This isn't --- it provides zero
> new functionality, and the "convenience" fac
On 8/23/07, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Ben Tilly" <[EMAIL PROTECTED]> writes:
> > ... But then I need this (possibly
> > empty) list to have a valid group by statement at the end. In Oracle
> > I used to just write it like this:
>
> > SELECT ...
> > GROUP BY $possible_term_1
> >
Gregory Stark escribió:
> "Alvaro Herrera" <[EMAIL PROTECTED]> writes:
>
> > Gregory Stark escribió:
> >
> >> The upside is the convenience which after all is the same upside as most of
> >> our spec grammar extensions. Many many programmers are accustomed to
> >> entering
> >> ad-hoc queries of
Gregory Stark <[EMAIL PROTECTED]> writes:
> I highly doubt the spec would ever conflict with allowing the user to elide
> the aliases given that Oracle (and others?) have always allowed this. Moreover
> if it's been 15 years without them adding it surely that argues we can be
> pretty sure they won
On Aug 23, 2007, at 10:47 , Ben Tilly wrote:
On 8/22/07, Michael Glaesemann <[EMAIL PROTECTED]> wrote:
*This* seems like a bug:
test=# select record_id
, count(observation_id) as bar
from observation
group by record_id
, case when true
th
"Alvaro Herrera" <[EMAIL PROTECTED]> writes:
> Gregory Stark escribió:
>
>> The upside is the convenience which after all is the same upside as most of
>> our spec grammar extensions. Many many programmers are accustomed to entering
>> ad-hoc queries of this form and forcing them to enter an alias
"Ben Tilly" <[EMAIL PROTECTED]> writes:
> ... But then I need this (possibly
> empty) list to have a valid group by statement at the end. In Oracle
> I used to just write it like this:
> SELECT ...
> GROUP BY $possible_term_1
> $possible_term_2
> $possible_term_3
> 'end of possibl
On 8/22/07, Michael Glaesemann <[EMAIL PROTECTED]> wrote:
>
> On Aug 22, 2007, at 20:49 , Ben Tilly wrote:
>
> > If your implementation accepts:
> >
> > group by case when true then 'foo' end
>
> What would that mean? Regardless of whether or not it's accepted, it
> should have *some* meaning.
T
Gregory Stark escribió:
> The upside is the convenience which after all is the same upside as most of
> our spec grammar extensions. Many many programmers are accustomed to entering
> ad-hoc queries of this form and forcing them to enter an alias for no purpose
> is just silly pedanticism from the
"Tom Lane" <[EMAIL PROTECTED]> writes:
> In short, lots of downsides here, and not a whole lot of upside.
I highly doubt the spec would ever conflict with allowing the user to elide
the aliases given that Oracle (and others?) have always allowed this. Moreover
if it's been 15 years without them
"Zeugswetter Andreas ADI SD" <[EMAIL PROTECTED]> writes:
>> > how much harder can it be to accept:
>> >
>> > group by 'foo'
>
> Presumably you meant group by "foo".
No that's the whole point. He meant the constant string 'foo' not the column
identifier "foo" which certainly should work now.
-
> > how much harder can it be to accept:
> >
> > group by 'foo'
Presumably you meant group by "foo".
Imho pg should accept group by "foo". It could be part of a constant
removal, that also takes burden off the sort.
e.g. in "select x, count(*) from bar where x=5 group by x", x could be
remov
> > If your implementation accepts:
> >
> > group by case when true then 'foo' end
I think he meant: group by case when true then "foo" end
>
> What would that mean? Regardless of whether or not it's accepted, it
> should have *some* meaning.
>
> It's not equivalent to GROUP BY "foo"
Yea,
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-hackers-
> [EMAIL PROTECTED] On Behalf Of Chuck McDevitt
> Sent: Wednesday, August 22, 2007 11:53 PM
> To: Michael Glaesemann; Ben Tilly
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS]
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-hackers-
> [EMAIL PROTECTED] On Behalf Of Michael Glaesemann
> Sent: Wednesday, August 22, 2007 5:17 PM
> To: Ben Tilly
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] SQL feature requests
>
Michael Glaesemann <[EMAIL PROTECTED]> writes:
> On Aug 22, 2007, at 20:36 , Ben Tilly wrote:
>> I can well believe that the standard says that you must accept
>> subqueries with aliases. But does it say you must reject subqueries
>> without aliases? I strongly doubt that.
> If I'm reading my dr
On Aug 22, 2007, at 20:49 , Ben Tilly wrote:
If your implementation accepts:
group by case when true then 'foo' end
What would that mean? Regardless of whether or not it's accepted, it
should have *some* meaning.
It's not equivalent to GROUP BY "foo"
test=# select record_id as foo
On Aug 22, 2007, at 20:36 , Ben Tilly wrote:
On 8/22/07, Michael Glaesemann <[EMAIL PROTECTED]> wrote:
On Aug 22, 2007, at 18:45 , Ben Tilly wrote:
1. Just a minor annoyance, but why must subqueries in FROM clauses
have an alias?
It's required by the SQL standard, AIUI. I wonder what Ente
Ben Tilly wrote:
On 8/22/07, Gregory Stark <[EMAIL PROTECTED]> wrote:
"Ben Tilly" <[EMAIL PROTECTED]> writes:
2. Why is 'non-integer constant in GROUP BY' an error?
Hm... I was a bit surprised by this warning myself. IIRC there was an
implementation convenience issue.
If your implementation
On 8/22/07, Gregory Stark <[EMAIL PROTECTED]> wrote:
>
> "Ben Tilly" <[EMAIL PROTECTED]> writes:
>
> > Hopefully this is the right place for a few feature requests that
> > would address some of the things that I've noticed in postgres.
> >
> > 1. Just a minor annoyance, but why must subqueries in
On 8/22/07, Michael Glaesemann <[EMAIL PROTECTED]> wrote:
>
> On Aug 22, 2007, at 18:45 , Ben Tilly wrote:
>
> > 1. Just a minor annoyance, but why must subqueries in FROM clauses
> > have an alias?
>
> It's required by the SQL standard, AIUI. I wonder what EnterpriseDB
> does?
I can well believe
"Ben Tilly" <[EMAIL PROTECTED]> writes:
> Hopefully this is the right place for a few feature requests that
> would address some of the things that I've noticed in postgres.
>
> 1. Just a minor annoyance, but why must subqueries in FROM clauses
> have an alias? For instance suppose that I have a
On Aug 22, 2007, at 18:45 , Ben Tilly wrote:
1. Just a minor annoyance, but why must subqueries in FROM clauses
have an alias?
It's required by the SQL standard, AIUI. I wonder what EnterpriseDB
does?
2. Why is 'non-integer constant in GROUP BY' an error?
This works for now:
case
Ben,
pgsql-sql is probably the appropriate list for future queries of this
nature.
Note that the below is my personal opinion; each PG developer has their
own.
> 1. Just a minor annoyance, but why must subqueries in FROM clauses
> have an alias? For instance suppose that I have an orders tabl
Hopefully this is the right place for a few feature requests that
would address some of the things that I've noticed in postgres.
1. Just a minor annoyance, but why must subqueries in FROM clauses
have an alias? For instance suppose that I have an orders table, and
one of the fields is userid. T
63 matches
Mail list logo
