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