Обсуждение: BUG #15783: Fail to select with a function in FROM clause plus another table
BUG #15783: Fail to select with a function in FROM clause plus another table
От
PG Bug reporting form
Дата:
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