Обсуждение: joined tables with USING and GROUPBY on the USING() column
I'll start by saying that I'm sure I'm missing something obvious... I have a query that is working fine on all my servers except one. The only obvious difference is that the failing one is running 9.5.3, while most are running 9.5.4, but since the query works on a 9.5.1 box I also have I can't imagine that's the cause. I can simplify the query (and still retain the error) down to: SELECT results.token FROM results INNER JOIN tokens USING(token) GROUP BY token; ERROR: column "results.token" must appear in the GROUP BY clause or be used in an aggregate function All well and good, and I'm happy enough to change the query's GROUP BY to include the table name, but it's confusing me how it works OK on all servers except one. Is there some configuration option I'm missing? Thanks! Geoff
On 9/7/2016 12:53 PM, Geoff Winkless wrote: > I'll start by saying that I'm sure I'm missing something obvious... > > I have a query that is working fine on all my servers except one. The > only obvious difference is that the failing one is running 9.5.3, > while most are running 9.5.4, but since the query works on a 9.5.1 box > I also have I can't imagine that's the cause. > > I can simplify the query (and still retain the error) down to: > > SELECT results.token FROM results INNER JOIN tokens USING(token) GROUP BY token; > > ERROR: column "results.token" must appear in the GROUP BY clause or be > used in an aggregate function > > All well and good, and I'm happy enough to change the query's GROUP BY > to include the table name, but it's confusing me how it works OK on > all servers except one. > > Is there some configuration option I'm missing? the only thing I can think of is that 'group by token' is ambiguous, if that was group by results.token, it would be specific. yes, I know, your join conditions using(token) are equivalent to saying on results.token=tokens.token, so either token should be the same thing... -- john r pierce, recycling bits in santa cruz
On 09/07/2016 12:53 PM, Geoff Winkless wrote: > I'll start by saying that I'm sure I'm missing something obvious... > > I have a query that is working fine on all my servers except one. The > only obvious difference is that the failing one is running 9.5.3, > while most are running 9.5.4, but since the query works on a 9.5.1 box > I also have I can't imagine that's the cause. > > I can simplify the query (and still retain the error) down to: > > SELECT results.token FROM results INNER JOIN tokens USING(token) GROUP BY token; > > ERROR: column "results.token" must appear in the GROUP BY clause or be > used in an aggregate function Seems to me Postgres is not seeing results.token as the same as token. What are schema for the tables results and tokens? What happens if you table qualify all the references to token? > > All well and good, and I'm happy enough to change the query's GROUP BY > to include the table name, but it's confusing me how it works OK on > all servers except one. > > Is there some configuration option I'm missing? > > Thanks! > > Geoff > > -- Adrian Klaver adrian.klaver@aklaver.com
On 7 Sep 2016 9:01 p.m., "Adrian Klaver" <adrian.klaver@aklaver.com> wrote:
> What happens if you table qualify all the references to token?
Oh it definitely fixes it; I was more confused why it works on one server and not another. I thought perhaps there was a config option to allow more lax naming in this way. If not I'll have to look more closely at the table defs, although I thought I had checked they were the same.
Geoff
On 09/07/2016 01:11 PM, Geoff Winkless wrote: > On 7 Sep 2016 9:01 p.m., "Adrian Klaver" <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> wrote: >> What happens if you table qualify all the references to token? > > Oh it definitely fixes it; I was more confused why it works on one > server and not another. I thought perhaps there was a config option to > allow more lax naming in this way. If not I'll have to look more closely > at the table defs, although I thought I had checked they were the same. I would check search_path to see if you have another table of same name in the path on the problem machine versus on the other machines. > > Geoff > -- Adrian Klaver adrian.klaver@aklaver.com
Thanks for the suggestions. Turns out I was right: I _was_ missing something obvious - results had token as char(4), tokens had token as varchar(4). Because the columns aren't the same they aren't treated as identical so the query builder won't accept the unqualified name. Next task is to work out how on earth the server ended up with its results table different to all the others... Geoff