Natural Join Bug in 11.2.0.1 ??

From: Hemant K Chitale <hemantkchitale_at_gmail.com>
Date: Sun, 24 Feb 2013 23:15:34 +0800
Message-ID: <CAMNBsZu1fY4cOjbRT+-3qT36T9sz0nkyc9a6R_dk9LQNbyge0w_at_mail.gmail.com>

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 Received on Sun Feb 24 2013 - 16:15:34 CET