Re: Subquery uses ungrouped column
| От | David G. Johnston |
|---|---|
| Тема | Re: Subquery uses ungrouped column |
| Дата | |
| Msg-id | CAKFQuwZAZ4C8F1=2tsLJ4ut=t8KyFGFaV5yh5ZcQzr8xHjGZpA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Subquery uses ungrouped column (Alex Ignatov <a.ignatov@postgrespro.ru>) |
| Ответы |
Re: Subquery uses ungrouped column
|
| Список | pgsql-general |
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.
В списке pgsql-general по дате отправления: