Обсуждение: Syntax bug? Group by?
Shouldn't this work? select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15; ERROR: column "y.ycis_id" must appear in the GROUP BY clause or be used in an aggregate function If I am asking for a specific column value, should I, technically speaking, need to group by that column?
* Mark Woodward (pgsql@mohawksoft.com) wrote:
> If I am asking for a specific column value, should I, technically
> speaking, need to group by that column?
Technically speaking, if you're asking for a specific tuple, should you
be allowed to request an aggregation?
Thanks,
Stephen
On Tue, 17 Oct 2006 12:08:07 -0400 Stephen Frost <sfrost@snowman.net> wrote: > * Mark Woodward (pgsql@mohawksoft.com) wrote: > > If I am asking for a specific column value, should I, technically > > speaking, need to group by that column? > > Technically speaking, if you're asking for a specific tuple, should you > be allowed to request an aggregation? One column value doesn't necessarily mean one tuple unless it has a unique index on that column. SELECT COUNT(*) FROM table WHERE field = 'value'; That's perfectly reasonable. You don't need the GROUP BY clause. However, this doesn't sound like a hackers question. Next time, please ask on another list such as pgsql-sql or even pgsql-novice. You can review the mailing lists and their purpose at http://www.postgresql.org/community/lists/ -- D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
Stephen Frost wrote: > * Mark Woodward (pgsql@mohawksoft.com) wrote: >> If I am asking for a specific column value, should I, technically >> speaking, need to group by that column? > > Technically speaking, if you're asking for a specific tuple, should you > be allowed to request an aggregation? > Only with the assumption that the value in the where clause is for a unique column. If you want min(col2) and avg(col2) where col1=x you can get it without a group by, the same as if you put col1<x - if you want an aggregate of all records returned not the aggregate based on each value of col1. > select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15; But back to the query the issue comes in that the ycis_id value is included with the return values requested (a single row value with aggregate values that isn't grouped) - if ycis_id is not unique you will get x number of returned tuples with ycis_id=15 and the same min() and avg() values for each row. Removing the ycis_id after the select will return the aggregate values you want without the group by. -- Shane Ambler Postgres@007Marketing.com Get Sheeky @ http://Sheeky.Biz
> Stephen Frost wrote: > >> select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15; > > But back to the query the issue comes in that the ycis_id value is > included with the return values requested (a single row value with > aggregate values that isn't grouped) - if ycis_id is not unique you will > get x number of returned tuples with ycis_id=15 and the same min() and > avg() values for each row. > Removing the ycis_id after the select will return the aggregate values > you want without the group by. I still assert that there will always only be one row to this query. This is an aggregate query, so all the rows with ycis_id = 15, will be aggregated. Since ycis_id is the identifying part of the query, it should not need to be grouped. My question, is it a syntactic technicality that PostgreSQL asks for a "group by," or a bug in the parser?
Hi, Mark, Mark Woodward wrote: > Shouldn't this work? > > select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15; > > ERROR: column "y.ycis_id" must appear in the GROUP BY clause or be used > in an aggregate function > > If I am asking for a specific column value, should I, technically > speaking, need to group by that column? Try: SELECT 15 as ycis_id, min(tindex), avt(tindex) from y where ycis_id = 15; HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org
Mark Woodward wrote: >>> select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15; >>> > > I still assert that there will always only be one row to this query. This > is an aggregate query, so all the rows with ycis_id = 15, will be > aggregated. Since ycis_id is the identifying part of the query, it should > not need to be grouped. > > My question, is it a syntactic technicality that PostgreSQL asks for a > "group by," or a bug in the parser? > I think your point is that every non-aggregate column in the results of the query also appears in the where clause and is given a single value there, so conceivably, an all-knowing, all-powerful postgres could recognize this and do the implied GROUP by on these columns. I'm not in a position to give a definitive answer on this, but I suspect that adjusting the query parser/planner to allow an implied GROUP BY either gets prohibitively complicated, or fits too much of a special case to be worth implementing. selectycis_id,some_other_id,min(tindex),avg(tindex)from ywhere ycis_id = 15group by some_other_id; Here, postgres would have to use the group by you specified, and also recognize the single-valued constant assigned to ycis_id. Maybe not too bad, but: selectycis_id,some_other_id,min(tindex),avg(tindex)from ywhere ycis_id = some_single_valued_constant(foo, bar)groupby some_other_id; In this case, postgres doesn't know whether some_single_valued_constant() will really return the same single value for every tuple. Ultimately, as more complex queries are introduced, it would become a lot simpler for the query writer to just specify the group by columns instead of trying to guess it from the where clause. Final note: I could also see situations where an implied group by would silently allow a poorly written query to execute, instead of throwing an error that suggests to the query writer that they did something wrong. -- Nolan Cafferky Software Developer IT Department RBS Interactive nolan.cafferky@rbsinteractive.com
Mark Woodward wrote: >> Stephen Frost wrote: >> >> >>> select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15; >>> >> But back to the query the issue comes in that the ycis_id value is >> included with the return values requested (a single row value with >> aggregate values that isn't grouped) - if ycis_id is not unique you will >> get x number of returned tuples with ycis_id=15 and the same min() and >> avg() values for each row. >> Removing the ycis_id after the select will return the aggregate values >> you want without the group by. >> > > I still assert that there will always only be one row to this query. This > is an aggregate query, so all the rows with ycis_id = 15, will be > aggregated. Since ycis_id is the identifying part of the query, it should > not need to be grouped. > > My question, is it a syntactic technicality that PostgreSQL asks for a > "group by," or a bug in the parser? > > AFAIK what you want is not per sql spec. What if you had instead written select ycis_id, min(tindex), avg(tindex) from y where frobnitz(ycis_id) = 15; ? I think you are expecting too much reasoning from the engine. cheers andrew
Hi, Mark, Mark Woodward wrote: >> Stephen Frost wrote: >> >>> select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15; >> But back to the query the issue comes in that the ycis_id value is >> included with the return values requested (a single row value with >> aggregate values that isn't grouped) - if ycis_id is not unique you will >> get x number of returned tuples with ycis_id=15 and the same min() and >> avg() values for each row. >> Removing the ycis_id after the select will return the aggregate values >> you want without the group by. > > I still assert that there will always only be one row to this query. This > is an aggregate query, so all the rows with ycis_id = 15, will be > aggregated. Since ycis_id is the identifying part of the query, it should > not need to be grouped. > > My question, is it a syntactic technicality that PostgreSQL asks for a > "group by," or a bug in the parser? I think that it's a lack of special-casing the = operator. Imagine "where ycis_id>15" or "where ycis_id @|< $RECTANGLE" or other (probably user defined) operators on (probably user defined) datatypes. The parser has no real knowledge what the operators do, it simply requests one that returns a bool. One could make the parser to special case the = operator, and maybe some others, however I doubt it's worth the effort. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org
> Hi, Mark, > > Mark Woodward wrote: >>> Stephen Frost wrote: >>> >>>> select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15; >>> But back to the query the issue comes in that the ycis_id value is >>> included with the return values requested (a single row value with >>> aggregate values that isn't grouped) - if ycis_id is not unique you >>> will >>> get x number of returned tuples with ycis_id=15 and the same min() and >>> avg() values for each row. >>> Removing the ycis_id after the select will return the aggregate values >>> you want without the group by. >> >> I still assert that there will always only be one row to this query. >> This >> is an aggregate query, so all the rows with ycis_id = 15, will be >> aggregated. Since ycis_id is the identifying part of the query, it >> should >> not need to be grouped. >> >> My question, is it a syntactic technicality that PostgreSQL asks for a >> "group by," or a bug in the parser? > > I think that it's a lack of special-casing the = operator. Imagine > "where ycis_id>15" or "where ycis_id @|< $RECTANGLE" or other (probably > user defined) operators on (probably user defined) datatypes. > > The parser has no real knowledge what the operators do, it simply > requests one that returns a bool. > > One could make the parser to special case the = operator, and maybe some > others, however I doubt it's worth the effort. I understand the SQL, and this isn't a "sql" question else it would be on a different list, it is a PostgreSQL internals question and IMHO potential bug. The original query: select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15; Should NOT require a "group by" to get ycis_id in the results.
> Hi, Mark, > > Mark Woodward wrote: >> Shouldn't this work? >> >> select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15; >> >> ERROR: column "y.ycis_id" must appear in the GROUP BY clause or be used >> in an aggregate function >> >> If I am asking for a specific column value, should I, technically >> speaking, need to group by that column? > > Try: > > SELECT 15 as ycis_id, min(tindex), avt(tindex) from y where ycis_id = 15; > > This isn't a "SQL" question!!! This is a question of whether or not PostgreSQL is correct in requiring a "group by" in the query. I assert that since it is unabiguous as to what "ycis_id" should be, PostgreSQL should not require a grouping.
> Mark Woodward wrote: >>> Stephen Frost wrote: >>> >>> >>>> select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15; >>>> >>> But back to the query the issue comes in that the ycis_id value is >>> included with the return values requested (a single row value with >>> aggregate values that isn't grouped) - if ycis_id is not unique you >>> will >>> get x number of returned tuples with ycis_id=15 and the same min() and >>> avg() values for each row. >>> Removing the ycis_id after the select will return the aggregate values >>> you want without the group by. >>> >> >> I still assert that there will always only be one row to this query. >> This >> is an aggregate query, so all the rows with ycis_id = 15, will be >> aggregated. Since ycis_id is the identifying part of the query, it >> should >> not need to be grouped. >> >> My question, is it a syntactic technicality that PostgreSQL asks for a >> "group by," or a bug in the parser? >> >> > > AFAIK what you want is not per sql spec. What if you had instead written > > > select ycis_id, min(tindex), avg(tindex) from y where frobnitz(ycis_id) > = 15; > > > ? I think you are expecting too much reasoning from the engine. > Regardless, I can get the results I need and have already worked around this. The reason why I posted the question to hackers was that I think it is a bug. The output column "ycis_id" is unabiguously a single value with regards to the query. Shouldn't PostgreSQL "know" this? AFAIR, I think I've used this exact type of query before either on PostgreSQL or another system, maybe Oracle, and it did work.
> Mark Woodward wrote: >>>> select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15; >>>> >> >> I still assert that there will always only be one row to this query. >> This >> is an aggregate query, so all the rows with ycis_id = 15, will be >> aggregated. Since ycis_id is the identifying part of the query, it >> should >> not need to be grouped. >> >> My question, is it a syntactic technicality that PostgreSQL asks for a >> "group by," or a bug in the parser? >> > I think your point is that every non-aggregate column in the results of > the query also appears in the where clause and is given a single value > there, so conceivably, an all-knowing, all-powerful postgres could > recognize this and do the implied GROUP by on these columns. Not exactly. > > I'm not in a position to give a definitive answer on this, but I suspect > that adjusting the query parser/planner to allow an implied GROUP BY > either gets prohibitively complicated, or fits too much of a special > case to be worth implementing. > > select > ycis_id, > some_other_id, > min(tindex), > avg(tindex) > from > y > where > ycis_id = 15 > group by > some_other_id; This is not, in fact, like the example I gave and confuses the point I am trying to make. The original query: select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15; ycis_id is unambiguous and MUST be only one value, there should be no requirement of grouping. In fact, a "group by" implies multiple result rows in an aggregate query. As I said in other branches of this thread, this isn't a SQL question, it is a question of whether or not the PostgreSQL parser is correct or not, and I do not believe that it is working correctly.
Mark Woodward wrote: >> Hi, Mark, >> >> Mark Woodward wrote: >> >>> Shouldn't this work? >>> >>> select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15; >>> >>> ERROR: column "y.ycis_id" must appear in the GROUP BY clause or be used >>> in an aggregate function >>> >>> If I am asking for a specific column value, should I, technically >>> speaking, need to group by that column? >>> >> Try: >> >> SELECT 15 as ycis_id, min(tindex), avt(tindex) from y where ycis_id = 15; >> >> >> > This isn't a "SQL" question!!! This is a question of whether or not > PostgreSQL is correct in requiring a "group by" in the query. I assert > that since it is unabiguous as to what "ycis_id" should be, PostgreSQL > should not require a grouping. > > Of course it's an SQL question. How can you ask about the correctness of a piece of text which purports to be SQL and then say it isn't an SQL question? If you can point to a place in the spec or our docs that sanctions the usage you expect, then please do so, Until then I (and I suspect everyone else) will persist in saying it's not a bug. cheers andrew
Mark Woodward wrote: >> Stephen Frost wrote: >> >>> select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15; >> But back to the query the issue comes in that the ycis_id value is >> included with the return values requested (a single row value with >> aggregate values that isn't grouped) - if ycis_id is not unique you will >> get x number of returned tuples with ycis_id=15 and the same min() and >> avg() values for each row. >> Removing the ycis_id after the select will return the aggregate values >> you want without the group by. > > I still assert that there will always only be one row to this query. This > is an aggregate query, so all the rows with ycis_id = 15, will be > aggregated. Since ycis_id is the identifying part of the query, it should > not need to be grouped. SELECT ycis_id FROM table WHERE ycis_id=15; returns a single tuple when ycis_id is unique otherwise multiple tuples which means that SELECT ycis_id is technically defined as returning a multiple row tuple even if ycis_id is unique - the data in the tuple returned is data directly from one table row SELECT max(col2) FROM table WHERE ycis_id=15; returns an aggregate tuple SELECT ycis_id FROM table WHERE ycis_id=15 GROUP BY ycis_id; returns an aggregate tuple (aggregated with the GROUP BY clause making the ycis_id after the SELECT an aggregate as well) You can't have both a single tuple and an aggregate tuple returned in the one statement. If you want the column value of ycis_id in the results you need the group by to unify all returned results as being aggregates. -- Shane Ambler Postgres@007Marketing.com Get Sheeky @ http://Sheeky.Biz
Mark Woodward wrote: > Shouldn't this work? > > select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15; > > ERROR: column "y.ycis_id" must appear in the GROUP BY clause or be > used in an aggregate function This would require a great deal of special-casing, in particular knowledge of the = operator, and then the restriction to a particular form of the WHERE clause. For overall consistency, I don't think this should be allowed. -- Peter Eisentraut http://developer.postgresql.org/~petere/
On Oct 17, 2006, at 15:19, Peter Eisentraut wrote:
> Mark Woodward wrote:
>> Shouldn't this work?
>>
>> select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15;
>>
>> ERROR: column "y.ycis_id" must appear in the GROUP BY clause or be
>> used in an aggregate function
>
> This would require a great deal of special-casing, in particular
> knowledge of the = operator, and then the restriction to a particular
> form of the WHERE clause. For overall consistency, I don't think this
> should be allowed.
In this particular case, the client constructing the query *knows*
the value of ycis_id (since the client is generating the "ycis_id =
15" clause). It's technically just a waste of bandwidth and server
resources to recalculate it. If you really want to replicate the
output of the query you proposed, you could rewrite it on the client as:
select 15 as ycis_id, min(tindex), avg(tindex) from y where
ycis_id = 15;
You could argue that the server should do this for you, but it seems
ugly to do in the general case. And, like Peter points out, would
need a lot of special-casing. I guess the parser could do it for
expressions in the SELECT clause that exactly match expressions in
the WHERE clause.
Thanks!
- Chris
> On Tue, Oct 17, 2006 at 02:41:25PM -0400, Mark Woodward wrote: > >> The output column "ycis_id" is unabiguously a single value with regards >> to >> the query. Shouldn't PostgreSQL "know" this? AFAIR, I think I've used >> this >> exact type of query before either on PostgreSQL or another system, maybe >> Oracle, and it did work. > > Doesn't work in Oracle 10g: > > SELECT ycis_id, tindex from x where ycis_id = 15; > YCIS_ID TINDEX > ======= ====== > 15 10 > 15 20 > > SELECT ycis_id, min(tindex), avg(tindex) from x where ycis_id = 15; > ORA-00937: not a single-group group function > > SELECT ycis_id, min(tindex), avg(tindex) from x where ycis_id = 15 GROUP > BY ycis_id; > YCIS_ID MIN(TINDEX) AVG(TINDEX) > ======= =========== =========== > 15 10 15 That's interesting. I am digging through the SQL99 spec, and am trying to find a definitive answer.
> Mark Woodward wrote: >> Shouldn't this work? >> >> select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15; >> >> ERROR: column "y.ycis_id" must appear in the GROUP BY clause or be >> used in an aggregate function > > This would require a great deal of special-casing, in particular > knowledge of the = operator, and then the restriction to a particular > form of the WHERE clause. For overall consistency, I don't think this > should be allowed. > Well, this started out as a "huh, that's funny, that should work, is that a bug?" and is turning into a search through the SQL99 spec for a clear answer. I've already worked around it, but to me, at least, it seems it should work.
> On Oct 17, 2006, at 15:19, Peter Eisentraut wrote: > >> Mark Woodward wrote: >>> Shouldn't this work? >>> >>> select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15; >>> >>> ERROR: column "y.ycis_id" must appear in the GROUP BY clause or be >>> used in an aggregate function >> >> This would require a great deal of special-casing, in particular >> knowledge of the = operator, and then the restriction to a particular >> form of the WHERE clause. For overall consistency, I don't think this >> should be allowed. > > In this particular case, the client constructing the query *knows* > the value of ycis_id (since the client is generating the "ycis_id = > 15" clause). It's technically just a waste of bandwidth and server > resources to recalculate it. If you really want to replicate the > output of the query you proposed, you could rewrite it on the client as: > > select 15 as ycis_id, min(tindex), avg(tindex) from y where > ycis_id = 15; > > You could argue that the server should do this for you, but it seems > ugly to do in the general case. And, like Peter points out, would > need a lot of special-casing. I guess the parser could do it for > expressions in the SELECT clause that exactly match expressions in > the WHERE clause. > But, and here's the rub, which is the "correct" way to handle it? I'm looking through the SQL99 spec to see if I can find an answer.
On Tue, Oct 17, 2006 at 02:41:25PM -0400, Mark Woodward wrote: > The output column "ycis_id" is unabiguously a single value with regards to > the query. Shouldn't PostgreSQL "know" this? AFAIR, I think I've used this > exact type of query before either on PostgreSQL or another system, maybe > Oracle, and it did work. Doesn't work in Oracle 10g: SELECT ycis_id, tindex from x where ycis_id = 15; YCIS_ID TINDEX ======= ====== 15 10 15 20 SELECT ycis_id, min(tindex), avg(tindex) from x where ycis_id = 15; ORA-00937: not a single-group group function SELECT ycis_id, min(tindex), avg(tindex) from x where ycis_id = 15 GROUP BY ycis_id; YCIS_ID MIN(TINDEX) AVG(TINDEX) ======= =========== =========== 15 10 15 --Joe -- Joe Sunday <sunday@csh.rit.edu> http://www.csh.rit.edu/~sunday/ Computer Science House, Rochester Inst. Of Technology
On Tue, Oct 17, 2006 at 04:45:49PM -0400, Mark Woodward wrote: > Well, this started out as a "huh, that's funny, that should work, is that > a bug?" and is turning into a search through the SQL99 spec for a clear > answer. I've already worked around it, but to me, at least, it seems it > should work. What you're asking for is difficult, not done by anyone else (so far demostrated) and not mandated by the spec, so I don't see how it could be construed a bug. As for the spec, this is what I have from SQL2003: 7.12.15) If T is a grouped table, then let G be the set of grouping columns of T. In each <value expression> contained in<select list>, each column reference that references a column of T shall reference some column C that is functionally dependenton G or shall be contained in an aggregated argument of a <set function specification> whose aggregation query isQS. Which to me says that everything in the output is either grouped by or part of an aggregate. That together with a statement elsewhere saying that if no group by clause is present, GROUP BY () is implied seems to seal it for me. (BTW, the "functionally dependent" is new and postgresql only supports the older SQL standards where C has to actually be a grouping column). Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Andrew Dunstan <andrew@dunslane.net> writes:
> Mark Woodward wrote:
>> My question, is it a syntactic technicality that PostgreSQL asks for a
>> "group by," or a bug in the parser?
> AFAIK what you want is not per sql spec.
It would in fact be a violation of spec. Consider the case where there
are no rows matching 15. In this case
select min(tindex), avg(tindex) from y where ycis_id = 15;
will yield one row containing NULLs, whereas
select min(tindex), avg(tindex) from y where ycis_id = 15 group by ycis_id;
will yield no rows (because there are no groups). Therefore, if
Postgres were to implicitly insert a GROUP BY to make it legal to
reference ycis_id directly, we'd be changing the query behavior
and breaking spec.
regards, tom lane
Joe Sunday wrote: > On Tue, Oct 17, 2006 at 02:41:25PM -0400, Mark Woodward wrote: > >> The output column "ycis_id" is unabiguously a single value with regards to >> the query. Shouldn't PostgreSQL "know" this? AFAIR, I think I've used this >> exact type of query before either on PostgreSQL or another system, maybe >> Oracle, and it did work. > > Doesn't work in Oracle 10g: > > SELECT ycis_id, tindex from x where ycis_id = 15; > YCIS_ID TINDEX > ======= ====== > 15 10 > 15 20 > > SELECT ycis_id, min(tindex), avg(tindex) from x where ycis_id = 15; > ORA-00937: not a single-group group function > > SELECT ycis_id, min(tindex), avg(tindex) from x where ycis_id = 15 GROUP BY ycis_id; > YCIS_ID MIN(TINDEX) AVG(TINDEX) > ======= =========== =========== > 15 10 15 > > --Joe > MySQL reports - Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause I found one that actually returns the desired result - SQLite3. sqlite> select * from test; 15|20 15|10 sqlite> select ycis_id,min(tindex),avg(tindex) from test where ycis_id=15; 15|10|15 sqlite> -- Shane Ambler Postgres@007Marketing.com Get Sheeky @ http://Sheeky.Biz
Dnia 17-10-2006 o godz. 23:21 Tom Lane napisał(a): > Andrew Dunstan <andrew@dunslane.net> writes: > > Mark Woodward wrote: > >> My question, is it a syntactic technicality that PostgreSQL asks for a > >> "group by," or a bug in the parser? > > > AFAIK what you want is not per sql spec. > > It would in fact be a violation of spec. Consider the case where there > are no rows matching 15. In this case > > select min(tindex), avg(tindex) from y where ycis_id = 15; > > will yield one row containing NULLs, whereas > > select min(tindex), avg(tindex) from y where ycis_id = 15 group by > ycis_id; > > will yield no rows (because there are no groups). Therefore, if > Postgres were to implicitly insert a GROUP BY to make it legal to > reference ycis_id directly, we'd be changing the query behavior > and breaking spec. > > regards, tom lane > Hello IMHO: Also take into consider that - what should be expected behavior of this: select ycis_id, min(tindex), avg(tindex) from y where ycis_id = truncate(random()*100); Since result of comparission is not known on parsing phase it couldn't be done here if there is no other requirements onargum
Hi, Mark, Mark Woodward wrote: >>> My question, is it a syntactic technicality that PostgreSQL asks for a >>> "group by," or a bug in the parser? >> I think that it's a lack of special-casing the = operator. Imagine >> "where ycis_id>15" or "where ycis_id @|< $RECTANGLE" or other (probably >> user defined) operators on (probably user defined) datatypes. >> >> The parser has no real knowledge what the operators do, it simply >> requests one that returns a bool. >> >> One could make the parser to special case the = operator, and maybe some >> others, however I doubt it's worth the effort. > > I understand the SQL, and this isn't a "sql" question else it would be on > a different list, it is a PostgreSQL internals question and IMHO potential > bug. And that's why I talked about PostgreSQL internals. > The original query: > select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15; > > Should NOT require a "group by" to get ycis_id in the results. And, as I wrote, this is only possible when the query parser special-cases the = operator (compared to all other operators). HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org