Re: bug in 8.4 and resolved

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.