Обсуждение: group by
I have a problem whith group by and aliases select s1.channelname as channelname, s1.id as channelid, count(*) as anz from mgw_news n left join mgw_news_channels s1 on n.channelid = s1.id group by channelid, channelname order by channelid, channelname this runs under mysql and postgresql seems not to accept aliases in group by cause when i'm changing channelid to s1.id it works perfectly is anyone working on that? I didn't see this in the TODO list With whom can I talk about this problem? regards Ewald Geschwinde
Ewald, > select s1.channelname as channelname, s1.id as channelid, > count(*) as anz from mgw_news n left join mgw_news_channels s1 on > n.channelid = s1.id group by channelid, channelname order by channelid, > channelname > > this runs under mysql and postgresql seems not to accept aliases in group by > > cause when i'm changing channelid to s1.id it works perfectly I believe that the PostgreSQL behavior, i.e. not accepting the real names of columns that have been aliased, is the SQL92 standard behavior. Certainly it prevents a certain confusion in the query parser in the case of multiple references to an aliased column. In other words, I believe that the behavior you are observing is by design and is not a bug. Please keep in mind that MySQL does *not* uphold the SQL92 standard, so it cannot be used as an index of proper behavior. So, change your GROUP BY reference. However, the behavior you mention should certainly be mentioned in one or more of the porting articles at TechDocs ( http://techdocs.postgresql.org/ ). E-mail one of the authors if it's not. -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Ewald Geschwinde <webmaster@geschwinde.net> writes: > I have a problem whith group by and aliases > select s1.channelname as channelname, s1.id as channelid, > count(*) as anz from mgw_news n left join mgw_news_channels s1 on > n.channelid = s1.id group by channelid, channelname order by channelid, > channelname I would expect "group by channelid" to refer to n.channelid, which is the SQL-mandated interpretation. Using aliases in GROUP BY is a flat violation of the SQL92 standard, but a lot of implementations accept it anyway. However, when there is an ambiguous situation, as here, we must treat GROUP BY as referring to the underlying column name not the alias, else we do not conform to the standard. > this runs under mysql and postgresql seems not to accept aliases in > group by > cause when i'm changing channelid to s1.id it works perfectly > is anyone working on that? No, because it's not a bug. MySQL's behavior is not SQL92 compliant, if you've described it accurately. regards, tom lane