Home » SQL & PL/SQL » SQL & PL/SQL » Nested table join order - weird behaviour (Oracle 10.2.0.4, Sun Solaris)
Nested table join order - weird behaviour [message #422447] Wed, 16 September 2009 09:24 Go to next message
ptaku
Messages: 3
Registered: September 2009
Location: Wroclaw/Poland
Junior Member
Hello,
I encountered a weird problem with nested table today - the following code reproduces it.

Can anyone explain, why the JOIN 1 in this example doesn't work? It works fine with the dual table (I know it's not a standard table but it should behave like one Wink).

drop table nt_bug;

drop type nt_bug_type;
create type nt_bug_type as table of number;
/

create table nt_bug (id number, nt nt_bug_type) 
nested table nt store as nt_bug2;
insert into nt_bug values (1, null);

drop table nt_bug3;
create table nt_bug3 (dummy char);
insert into nt_bug3 values ('X');

commit;

set serveroutput on;

declare

   nt_row nt_bug%rowtype;

begin

   select * into nt_row from nt_bug where id = 1;

   for c_log in (select 
                    'JOIN 1' as output
                 from 
                    nt_bug3,
                    table(nt_row.nt)(+))
   loop
      dbms_output.put_line(c_log.output);
   end loop;
   
   for c_log in (select 
                    'JOIN 2' as output
                 from 
                    table(nt_row.nt)(+),
                    nt_bug3)
   loop
      dbms_output.put_line(c_log.output);
   end loop;
   
      for c_log in (select 
                    'JOIN 3' as output
                 from 
                    dual,
                    table(nt_row.nt)(+))
   loop
      dbms_output.put_line(c_log.output);
   end loop;
   
   for c_log in (select 
                    'JOIN 4' as output
                 from 
                    table(nt_row.nt)(+),
                    dual)
   loop
      dbms_output.put_line(c_log.output);
   end loop;
   
end;
/


I guess it's a Oracle bug, but maybe there's an explanation for it ?

Regards,
I.Ptak
Re: Nested table join order - weird behaviour [message #422449 is a reply to message #422447] Wed, 16 September 2009 09:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
A few points:

1) What results are you getting that cause you to believe that you've found a bug

2) Where did you get that outer join syntax from? In Oracle, the (+) syntax is applied at a column level when specifying join conditions.

3) What version of Oracle are you on?
Re: Nested table join order - weird behaviour [message #422450 is a reply to message #422449] Wed, 16 September 2009 09:42 Go to previous messageGo to next message
ptaku
Messages: 3
Registered: September 2009
Location: Wroclaw/Poland
Junior Member
JRowbottom wrote on Wed, 16 September 2009 09:34

1) What results are you getting that cause you to believe that you've found a bug

2) Where did you get that outer join syntax from? In Oracle, the (+) syntax is applied at a column level when specifying join conditions.

3) What version of Oracle are you on?


1. The script output is:

JOIN 2
JOIN 3
JOIN 4

The JOIN 1 and JOIN 2 differs only in the table order (in the CROSS JOIN so it shouldn't matter).
The same difference is between JOIN 3 and JOIN 4 (but it uses the DUAL table instead of my own nt_bug3 table, which is exactly the same as DUAL).

I believe there should be no difference in output between all of the versions above.

2. It's the only way to use the LEFT JOIN with the nested table.

3. 10.2.0.4
Re: Nested table join order - weird behaviour [message #422452 is a reply to message #422450] Wed, 16 September 2009 10:25 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It works fine for me, with 10.2.0.4 on Windows, and 10.2.0.3 on Linux - returns all 4 values.

I think this might be one for Metalink.
Re: Nested table join order - weird behaviour [message #422520 is a reply to message #422452] Thu, 17 September 2009 01:45 Go to previous messageGo to next message
ptaku
Messages: 3
Registered: September 2009
Location: Wroclaw/Poland
Junior Member
Now, that's unexpected Smile
I reproduced it on Oracle 9i (also Solaris). Metalink might be a good idea.
Re: Nested table join order - weird behaviour [message #422533 is a reply to message #422520] Thu, 17 September 2009 03:54 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Hmmm - 9.2.0.8 on Linux, I get only results 2 and 4.
Previous Topic: ULT_FILE.GET_LINE raise NO_DATA_FOUND without a reason
Next Topic: Procedure utl_smtp.write_raw_data
Goto Forum:
  


Current Time: Sat Oct 01 05:56:43 CDT 2016

Total time taken to generate the page: 0.11187 seconds