Re: [BUGS] BUG #14514: Bug in Subquery

Поиск
Список
Период
Сортировка
От Pantelis Theodosiou
Тема Re: [BUGS] BUG #14514: Bug in Subquery
Дата
Msg-id CAE3TBxyr-TZXGKUL1aBHbcKszA=-iaGe64NT2QCXWwR-mR+rPg@mail.gmail.com
обсуждение исходный текст
Ответ на Re: [BUGS] BUG #14514: Bug in Subquery  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-bugs
Дерево обсуждения


On Wed, Jan 25, 2017 at 3:29 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
reva.d91@gmail.com writes:
> It is ignoring the invalid columns in subquery,Instead of throwing the error
> 'Column does not exist'.

> (eg):
> select * from emp where (empname,empid,empno) in(selec emname,emid,emno from
> em_details)

> Note:There is no emno in em_details table.

Maybe not, but if there is one in emp, then this query is legal per
SQL spec --- emno is an outer reference.

                        regards, tom lane
 
What Tom says above, the query is legal SQL if the column exists in either table.

You should use aliases (or just prefix the columns with their table name and dot):

select e.* from emp as e
where (e.empname, e.empid, e.empno) in
    (select ed.emname, ed.emid, ed.emno
     from em_details as ed)

This way the query will work as you expected, either give a result (if the 3 columns exist in em_details) or give you an error if not.

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: [BUGS] could not fork autovacuum worker process: No error
Следующее
От: Tomasz Szypowski
Дата:
Сообщение: Re: [BUGS] could not fork autovacuum worker process: No error