Thread: bug in 8.4 and resolved

On 04/21/2013 10:12 PM, Abhinav Dwivedi wrote:
> Dear Member,
>
> We found a bug in postgresql 8.4  details are follows.
>
>   If a syntactically wrong query is made inner query of a nested query
> then the whole query wrongly returns a dataset instead of an error
> message. For example:
>
> select * from district where statecode in (Select districtcode from state)
>
> Please note that the attribute districtcode is not existent in the table
> state and if this query i.e. Select districtcode from state is executed
> in isolation then it correctly throws an error message. But when this
> incorrect query is made inner query of a nested query then the whole
> query wrongly returns a dataset.
>
> This issue is observed on Postgres8.4 and is found resolved on
> Postgres9.1. But our issue is that our application is using postgres8.4.
> Where to upgrade is not possible in quick time .so is there a possible
> fix (patch etc.. ) for this on Postgres8.4 itself.

What minor version of 8.4 are you using?

>
> Thanks and Regards..
> Abhinav


--
Adrian Klaver
adrian.klaver@gmail.com

Abhinav Dwivedi wrote on 22.04.2013 07:12:
> select * from district where statecode in (Select districtcode from state)
>
> Please note that the attribute districtcode is not existent in the table state and
>if this query i.e. Select districtcode from state is executed in isolation then it
> correctly throws an error message. But when this incorrect query is made inner query
> of a nested query then the whole query wrongly returns a dataset.

I assume the column districtcode is present in the table district. In that case this is not a bug,
this is required by the SQL standard. The sub-query references the column from the outer query.

And I don't think this has been changed with 9.x (nor will it ever).

On 4/22/2013 3:13 PM, Thomas Kellerer wrote:
> Abhinav Dwivedi wrote on 22.04.2013 07:12:
>> select * from district where statecode in (Select districtcode from
>> state)
>>
>> Please note that the attribute districtcode is not existent in the
>> table state and
>> if this query i.e. Select districtcode from state is executed in
>> isolation then it
>> correctly throws an error message. But when this incorrect query is
>> made inner query
>> of a nested query then the whole query wrongly returns a dataset.
>
> I assume the column districtcode is present in the table district. In
> that case this is not a bug,
> this is required by the SQL standard. The sub-query references the
> column from the outer query.
>
> And I don't think this has been changed with 9.x (nor will it ever).
>

if that had been written as an explicit join, there would be less ambiguity.

     select district.* from district join state using(statecode);

assuming that's even what you wanted.


--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

On 04/23/2013 12:29 AM, John R Pierce wrote:
> On 4/22/2013 3:13 PM, Thomas Kellerer wrote:
>> Abhinav Dwivedi wrote on 22.04.2013 07:12:
>>> select * from district where statecode in (Select districtcode from
>>> state)
>>>
>>> Please note that the attribute districtcode is not existent in the
>>> table state and
>>> if this query i.e. Select districtcode from state is executed in
>>> isolation then it
>>> correctly throws an error message. But when this incorrect query is
>>> made inner query
>>> of a nested query then the whole query wrongly returns a dataset.
>>
>> I assume the column districtcode is present in the table district. In
>> that case this is not a bug,
>> this is required by the SQL standard. The sub-query references the
>> column from the outer query.
>>
>> And I don't think this has been changed with 9.x (nor will it ever).
>>
>
> if that had been written as an explicit join, there would be less
> ambiguity.
>
>     select district.* from district join state using(statecode);
>
> assuming that's even what you wanted.

That's not an equivalent query.  Abhinav is doing a semi-join.