Subquery uses ungrouped column

On Thu, May 26, 2016 at 12:02 PM, Alex Ignatov <a.ignatov@postgrespro.ru>
wrote:

Hello!

Why the following query:

SELECT (select msc_id
from collectors
where id = substring(fileid from -1)
) msc_id
from ip_data_records
group by substring(fileid from -1)

gives me:

ERROR: subquery uses ungrouped column "ip_data_records.fileid" from outer
query
LINE 3: where id = substring(fileid from -1)

but the following query:

SELECT (select msc_id
from collectors
where id = fileid
) msc_id
from ip_data_records
group by fileid
is working ok

From observation ​PostgreSQL​

​doesn't recognize the equivalency of the outer "group by substring(fileid
from -1)" and the subquery expression. What PostgreSQL does is push the
column ip_data_records.fieldid​ into the subquery where it just happens to
be used in the expression "substring(fileid from -1)". For all PostgreSQL
cares the subquery could have the expression "where id = lower(fileid)" and
the execution mechanics, and error, would be identical.

IOW, columns are the unit of interchange between a parent query and its
correlated subqueries.​

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

From observation PostgreSQL doesn't recognize the equivalency of the
outer "group by substring(fileid from -1)" and the subquery expression.

Well, it would without the subselect in between. There's a code comment
in parse_agg.c about this:

* NOTE: we recognize grouping expressions in the main query, but only
* grouping Vars in subqueries. For example, this will be rejected,
* although it could be allowed:
* SELECT
* (SELECT x FROM bar where y = (foo.a + foo.b))
* FROM foo
* GROUP BY a + b;
* The difficulty is the need to account for different sublevels_up.
* This appears to require a whole custom version of equal(), which is
* way more pain than the feature seems worth.

It'd probably be possible to fix parse_agg.c if you didn't mind expending
lots of cycles on such cases. I'm not sure offhand whether there would
be implications in the planner, or what it would take to fix them if so.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general