Re: Natural Join Bug in 11.2.0.1 ??

From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Sun, 24 Feb 2013 19:53:33 +0400
Message-ID: <CAOVevU6KTC4Mv6mn4cQL6YXf0mrVGBahrY518VYhPN3oZvshTg_at_mail.gmail.com>

Hi,
You forgot about MANAGER_ID column. Your query with natural join equal to:

select employee_id, e.department_id, department_name from employees e

   , departments d
where

     d.department_id = e.department_id
 and d.manager_id    = e.manager_id
 and d.department_id in (10,20,30,40,50)

order by e.department_id, e.employee_id
/
 or
select employee_id, e.department_id, department_name from employees e

     join departments d
       on  d.department_id = e.department_id
       and d.manager_id    = e.manager_id

where
  d.department_id in (10,20,30,40,50)
order by e.department_id, e.employee_id
/

On Sun, Feb 24, 2013 at 7:15 PM, Hemant K Chitale <hemantkchitale_at_gmail.com>wrote:

> While creating some sample SQLs for my OCA 11g students, I found what seems
> to be a bug in
> 11.2.0.1 Oracle OTN Developer Days VM
> select employee_id, department_id, department_name
> from employees natural join departments
> where department_id in (10,20,30,40,50)
> order by department_id, employee_id
> /
>
> EMPLOYEE_ID DEPARTMENT_ID DEPARTMENT_NAME
> ----------- ------------- ------------------------------
> 202 20 Marketing
> 115 30 Purchasing
> 116 30 Purchasing
> 117 30 Purchasing
> 118 30 Purchasing
> 119 30 Purchasing
> 129 50 Shipping
> 130 50 Shipping
> 131 50 Shipping
> 132 50 Shipping
> 184 50 Shipping
> 185 50 Shipping
> 186 50 Shipping
> 187 50 Shipping
>
> 14 rows selected.
>
> Strangely, these two are not reported with my data : (This looks like a
> Bug)
> EMPLOYEE_ID DEPARTMENT_ID DEPARTMENT_NAME
> ----------- ------------- ------------------------------
> 200 10 Administration
> 203 40 Human Resources
>
> SQL> l
> 1 select e.employee_id, e.department_id, d.department_name
> 2 from employees e, departments d
> 3 where e.department_id = d.department_id
> 4* and d.department_id in (10,40)
> SQL> /
>
> EMPLOYEE_ID DEPARTMENT_ID DEPARTMENT_NAME
> ----------- ------------- ------------------------------
> 200 10 Administration
> 203 40 Human Resources
>
> SQL>
> SQL> l
> 1 select e.employee_id, e.department_id, d.department_name
> 2 from employees e, departments d
> 3 where e.department_id = d.department_id
> 4* and e.department_id in (10,40)
> SQL> /
>
> EMPLOYEE_ID DEPARTMENT_ID DEPARTMENT_NAME
> ----------- ------------- ------------------------------
> 200 10 Administration
> 203 40 Human Resources
>
> SQL>
>
>
>
> --
>
> Hemant K Chitale
> http://hemantoracledba.blogspot.com
> http://hemantscribbles.blogspot.com
> http://web.singnet.com.sg/~hkchital
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Best regards,
Sayan Malakshinov
Senior performance tuning engineer
PSBank
http://orasql.org


--
http://www.freelists.org/webpage/oracle-l

Received on Sun Feb 24 2013 - 16:53:33 CET