Nested table join order - weird behaviour [message #422447] |
Wed, 16 September 2009 09:24  |
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 ).
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   |
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   |
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
|
|
|
|
|
|