BUG #15783: Fail to select with a function in FROM clause plus another table
The following bug has been logged on the website:
Bug reference: 15783
Logged by: To Sites
Email address: tosites2019@gmail.com
PostgreSQL version: 10.7
Operating system: Windows 8.1
Description:
-- FAIL
select m.nome socio, p.tipo, sum(c.valor * m.percentual / 100) valor
from contas_pagas('2019-04-01', '2019-04-30', 'R') c, medico m
inner join planoconta p on (c.cod_planoconta = p.cod)
where (m.percentual > 0)
group by m.nome, p.tipo
-- ERROR
-- SQL Error [42P01]: ERROR: invalid reference to FROM-clause entry for
table "c"
-- Hint: There is an entry for table "c", but it cannot be referenced from
this part of the query.
-- Position: 169
-- WORKS
select m.nome socio, p.tipo, sum(c.valor * m.percentual / 100) valor
from contas_pagas('2019-04-01', '2019-04-30', 'R') c
inner join planoconta p on (c.cod_planoconta = p.cod)
cross join medico m
where (m.percentual > 0)
group by m.nome, p.tipo
In other words, we need to use cross join instead of put all tables in FROM
clause, if one of these tables was a function that returns a table.
PG Bug reporting form <noreply@postgresql.org> writes:
select m.nome socio, p.tipo, sum(c.valor * m.percentual / 100) valor
from contas_pagas('2019-04-01', '2019-04-30', 'R') c, medico m
inner join planoconta p on (c.cod_planoconta = p.cod)
where (m.percentual > 0)
group by m.nome, p.tipo
-- ERROR
-- SQL Error [42P01]: ERROR: invalid reference to FROM-clause entry for
table "c"
-- Hint: There is an entry for table "c", but it cannot be referenced from
this part of the query.
In other words, we need to use cross join instead of put all tables in FROM
clause, if one of these tables was a function that returns a table.
No, this has nothing to do with whether the relations are functions or
tables. You've forgotten that JOIN binds more tightly than comma in a
FROM-list, so that the relations available to that ON clause are only
m and p.
MySQL got this wrong for many years (maybe still does?), which has
encouraged a lot of confusion, but the SQL standard is entirely
clear on the point.
regards, tom lane