Thread: BUG #8830: Query with a subquery failes to execute if this subquery does not contain references to own table

The following bug has been logged on the website:

Bug reference:      8830
Logged by:          Query with a subquery failes to execute if a subquery does not contain
references to inner table
Email address:      valgog@gmail.com
PostgreSQL version: 9.3.2
Operating system:   Linux
Description:

select 'key.' || md5( s.i::text ) as key,
       'value.' || ( select string_agg( md5( (s.i + g.j)::text), '' )
                       from generate_series(1, 10) as g(j)
                   ) as value
  from generate_series(1, 100) as s(i);

works fine.

select 'key.' || md5( s.i::text ) as key,
       'value.' || ( select string_agg( md5( (s.i)::text), '' )
                       from generate_series(1, 20) as g(j)
                   ) as value
  from generate_series(1, 100) as s(i);

fails with:

ERROR:  column "s.i" must appear in the GROUP BY clause or be used in an
aggregate function
LINE 2: select 'key.' || md5( s.i::text ) as key,

Is it a bug or a feature?

valgog@gmail.com writes:
> select 'key.' || md5( s.i::text ) as key,
>        'value.' || ( select string_agg( md5( (s.i)::text), '' )
>                        from generate_series(1, 20) as g(j)
>                    ) as value
>   from generate_series(1, 100) as s(i);

> fails with:

> ERROR:  column "s.i" must appear in the GROUP BY clause or be used in an
> aggregate function
> LINE 2: select 'key.' || md5( s.i::text ) as key,


That's per SQL standard, AFAICS.  The string_agg() call is an aggregate of
the outer query, *not* the sub-select, because the lowest-level variable
in the aggregate's argument is of the outer query.  See SQL:2011
6.9 <set function specification> syntax rule 6:

    6) The aggregation query of a <set function specification> SFS is determined as follows. Case:
      a) If SFS has no aggregated column reference, then the aggregation query of SFS is the innermost <query
specification>that contains SFS. 
      b) Otherwise, the innermost qualifying query of the aggregated column references of SFS is the aggregation query
ofSFS. 

So the outer query has an aggregate, and that means that any variables
that aren't within the aggregate call have to be grouped columns.

            regards, tom lane