Thread: subquery abnormal behavior

subquery abnormal behavior

From
"Shoaib Mir"
Date:
I just noticed an abnormal behavior for the subquery:<br /><br /> create table myt1 (a numeric);<br /> create table
myt2(b numeric);<br /><br /> select a from myt1 where a in (select a from myt2);<br /><br /> This should be giving an
errorthat column 'a' does not exist in myt2 but it runs with any error...<br /><br /> I had been trying it on 8.2!<br
/><br/>Can someone please comment?<br /><br />Thank you,<br />-------------<br />Shoaib Mir<br />EnterpriseDB (<a
href="http://www.enterprisedb.com">www.enterprisedb.com</a>)<br/> 

Re: subquery abnormal behavior

From
"Rajesh Kumar Mallah"
Date:
On 12/11/06, Shoaib Mir <shoaibmir@gmail.com> wrote:
> I just noticed an abnormal behavior for the subquery:
>
>  create table myt1 (a numeric);
>  create table myt2 (b numeric);
>
>  select a from myt1 where a in (select a from myt2);
>
>  This should be giving an error that column 'a' does not exist in myt2 but
> it runs with any error...
>
>  I had been trying it on 8.2!
Even in 8.1.5 it does not complain. (its not 8.2 specific at least)

Regds
mallah.
>
> Can someone please comment?
>
> Thank you,
> -------------
> Shoaib Mir
> EnterpriseDB (www.enterprisedb.com)
>


Re: subquery abnormal behavior

From
"Shoaib Mir"
Date:
I just noticed the same behavior in Oracle and SQL Server as well :)

Regards,
---------
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

On 12/11/06, Shoaib Mir <shoaibmir@gmail.com> wrote:
I just noticed an abnormal behavior for the subquery:

create table myt1 (a numeric);
create table myt2 (b numeric);

select a from myt1 where a in (select a from myt2);

This should be giving an error that column 'a' does not exist in myt2 but it runs with any error...

I had been trying it on 8.2!

Can someone please comment?

Thank you,
-------------
Shoaib Mir
EnterpriseDB ( www.enterprisedb.com)

Re: subquery abnormal behavior

From
Michael Glaesemann
Date:
On Dec 11, 2006, at 15:48 , Shoaib Mir wrote:

> create table myt1 (a numeric);
> create table myt2 (b numeric);
>
> select a from myt1 where a in (select a from myt2);
>
> This should be giving an error that column 'a' does not exist in  
> myt2 but it runs with any error...

The a in the IN clause is the same a in outer expression. This is in  
effect:

select a from myt1 where a = a;

Now, if you were to say

select a from myt1 where a in (select myt2.a from myt2);
ERROR:  column myt2.a does not exist
LINE 1: select a from myt1 where a in (select myt2.a from myt2);

And if you were to instead have
create table myt1 (a numeric);
CREATE TABLE
create table myt2 (b numeric);
CREATE TABLE
insert into myt1(a) values (1), (2);
INSERT 0 2
insert into myt2 (b) values (3), (4), (2);
INSERT 0 3
create table myt3 (a numeric);
CREATE TABLE
insert into myt3 (a) values (2), (3),(4);
INSERT 0 3
test=# select a from myt1 where a in (select a from myt3);
a
---
2
(1 row)

It looks like PostgreSQL treats it as a natural join like

select a from myt1 natural join myt3;

Hope this helps.

Michael Glaesemann
grzm seespotcode net




Re: subquery abnormal behavior

From
"Shoaib Mir"
Date:
Oh that explains a lot...

Thank you,
-------------
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)


On 12/11/06, Michael Glaesemann <grzm@seespotcode.net> wrote:

On Dec 11, 2006, at 15:48 , Shoaib Mir wrote:

> create table myt1 (a numeric);
> create table myt2 (b numeric);
>
> select a from myt1 where a in (select a from myt2);
>
> This should be giving an error that column 'a' does not exist in
> myt2 but it runs with any error...

The a in the IN clause is the same a in outer expression. This is in
effect:

select a from myt1 where a = a;

Now, if you were to say

select a from myt1 where a in (select myt2.a from myt2);
ERROR:  column myt2.a does not exist
LINE 1: select a from myt1 where a in (select myt2.a from myt2);

And if you were to instead have
create table myt1 (a numeric);
CREATE TABLE
create table myt2 (b numeric);
CREATE TABLE
insert into myt1(a) values (1), (2);
INSERT 0 2
insert into myt2 (b) values (3), (4), (2);
INSERT 0 3
create table myt3 (a numeric);
CREATE TABLE
insert into myt3 (a) values (2), (3),(4);
INSERT 0 3
test=# select a from myt1 where a in (select a from myt3);
a
---
2
(1 row)

It looks like PostgreSQL treats it as a natural join like

select a from myt1 natural join myt3;

Hope this helps.

Michael Glaesemann
grzm seespotcode net



Re: subquery abnormal behavior

From
"Aaron Bono"
Date:
On 12/11/06, Shoaib Mir <shoaibmir@gmail.com> wrote:
Oh that explains a lot...

Thank you,
-------------
Shoaib Mir
EnterpriseDB (www.enterprisedb.com )


On 12/11/06, Michael Glaesemann <grzm@seespotcode.net> wrote:

On Dec 11, 2006, at 15:48 , Shoaib Mir wrote:

> create table myt1 (a numeric);
> create table myt2 (b numeric);
>
> select a from myt1 where a in (select a from myt2);
>
> This should be giving an error that column 'a' does not exist in
> myt2 but it runs with any error...

The a in the IN clause is the same a in outer expression. This is in
effect:

select a from myt1 where a = a;

Now, if you were to say

select a from myt1 where a in (select myt2.a from myt2);
ERROR:  column myt2.a does not exist
LINE 1: select a from myt1 where a in (select myt2.a from myt2);

And if you were to instead have
create table myt1 (a numeric);
CREATE TABLE
create table myt2 (b numeric);
CREATE TABLE
insert into myt1(a) values (1), (2);
INSERT 0 2
insert into myt2 (b) values (3), (4), (2);
INSERT 0 3
create table myt3 (a numeric);
CREATE TABLE
insert into myt3 (a) values (2), (3),(4);
INSERT 0 3
test=# select a from myt1 where a in (select a from myt3);
a
---
2
(1 row)

It looks like PostgreSQL treats it as a natural join like

select a from myt1 natural join myt3;

Hope this helps.

Michael Glaesemann
grzm seespotcode net




If you want to know more about this, check into how Correlated Subqueries work.  I would never recommend using Correlated Subqueries but knowledge of them and how/why they work helps you understand what is going on here much better.

--
==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
   http://codeelixir.com
==================================================================