Forum Stats

  • 3,873,027 Users
  • 2,266,497 Discussions
  • 7,911,407 Comments

Discussions

ROWID mismatch

656310
656310 Member Posts: 28
edited Jun 4, 2013 9:49AM in SQL & PL/SQL
Hallo,

please could someone explain to me, what is the REASON for following Oracle behaviour?

SQL:
select '1' from dual a
left join (
select c.dummy
--, b.rowid
from dual b
join dual c
on b.dummy = c.dummy) d
on a.dummy = d.dummy;

If I uncomment column b.rowid Oracle returns error : ORA-00918: column ambiguously defined.
If I put alias ( b.rowid x) it works, but anyway I would like to know the cause.

Note: SQL above is simplified and reproducible version of our application SQL code.

Thank you. Filip

Best Answer

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,116 Blue Diamond
    Answer ✓
    Filip Oliva wrote:
    Hallo,

    please could someone explain to me, what is the REASON for following Oracle behaviour?

    SQL:
    select '1' from dual a
    left join (
    select c.dummy
    --, b.rowid
    from dual b
    join dual c
    on b.dummy = c.dummy) d
    on a.dummy = d.dummy;

    If I uncomment column b.rowid Oracle returns error : ORA-00918: column ambiguously defined.
    If I put alias ( b.rowid x) it works, but anyway I would like to know the cause.

    Note: SQL above is simplified and reproducible version of our application SQL code.
    It's a typical sort of Oracle bug with the internal transformation from ANSI outer join form to Oracle outer join form.
    Because of the reference to b.rowid the transformation uses the name ROWID as the alias for a column in a generated in-line view - and that happens to be illegal. The error message is not appropriate to the error, but that's probably a side effect of the codepath that handles the transformation.

    Regards
    Jonathan Lewis
«1

Answers

  • 908002
    908002 Member Posts: 1,202
    ROWID is the pseudo column which is common to all tables.. When you specify rowid.. how would oracle know, which tables rowid it need to get among the joined tables... when you specify alias Oracle aware of which tables' rowid it need to get..
  • 656310
    656310 Member Posts: 28
    how would oracle know, which tables rowid it need to get among the joined tables
    Ok, but look at the query, there is : b.rowid
    So I specified that I would like to take ROWID from table b
  • RogerT
    RogerT Member Posts: 1,860 Gold Trophy
    Alread tried to add a column alias to the b.rowid? Something like b.rowid as row_id ?

    hth
  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    edited Jun 3, 2013 7:33AM
    What is the DB Version and Show the entire error message. In my version i am getting
    SQL> select * 
      2    from v$version
      3   where rownum = 1;
     
    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
    
    SQL> select '1'
      2    from dual a
      3    left
      4    join (
      5           select c.dummy
      6                , b.rowid
      7             from dual b
      8             join dual c
      9               on b.dummy = c.dummy
     10         ) d
     11      on a.dummy = d.dummy
     12  /
    select '1'
    *
    ERROR at line 1:
    ORA-01445: cannot select ROWID from, or sample, a join view without a
    key-preserved table
     
     
    SQL> 
    And when i specify an alias name its working fine
    SQL> select '1'
      2    from dual a
      3    left
      4    join (
      5           select c.dummy
      6                , b.rowid as rid
      7             from dual b
      8             join dual c
      9               on b.dummy = c.dummy
     10         ) d
     11      on a.dummy = d.dummy
     12  /
     
    '
    -
    1
     
    SQL> 
    Karthick2003
  • 656310
    656310 Member Posts: 28
    Thanks for suggestion.

    But in my post i wrote: > If I put alias ( b.rowid x) it works, but anyway I would like to know the cause.

    Well, I know that I can solve it by column alias .. Now I just would like to hear why it doesn't work without column alias ;)
  • 656310
    656310 Member Posts: 28
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit

    1) ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table
    I got this error when I change LEFT JOIN to INNER JOIN

    2) About COLUMN ALIAS I already wroted above
  • RogerT
    RogerT Member Posts: 1,860 Gold Trophy
    Sorry...haven't seen that.

    Now:

    If I write a inline view like that
    select '1' from dual a
    left join (
    select c.dummy 
    --, b.rowid
    from dual b
    join dual c
    on b.dummy = c.dummy) d
    on a.dummy = d.dummy;
    Then I I guess oracle gets messed up with the rowid (automatically ?) selected from the main table (dual (a))? Thats just my guess... or in other words, if I added rowid to the first-line (SELECT '1',rowid) oracle has no chance to know which ROWID you mean.

    hth
  • 656310
    656310 Member Posts: 28
    I found another interesting thing on this issue.

    When I change DUAL table -> to some real TABLE (for example DW_COUNTRY in our DB) and if there is unique INDEX on joining columns (COUNTRY_CODE), then error message disappears.

    select '1' from dw_country a
    left join (
    select c.*
    , b.rowid
    from dw_country b
    join dw_country c
    on b.country_code = c.country_code) d
    on a.COUNTRY_CODE = d.COUNTRY_CODE;

    When I drop index on COUNTRY_CODE, then "ORA-00918: column ambiguously defined" come again.

    And to your reply:
    if I added rowid to the first-line (SELECT '1',rowid) oracle has no chance to know which ROWID you mean.
    OK, but error is fired even when there is no ROWID on the first-line and that is strange to me.
  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    Filip Oliva wrote:
    I found another interesting thing on this issue.

    When I change DUAL table -> to some real TABLE (for example DW_COUNTRY in our DB) and if there is unique INDEX on joining columns (COUNTRY_CODE), then error message disappears.

    select '1' from dw_country a
    left join (
    select c.*
    , b.rowid
    from dw_country b
    join dw_country c
    on b.country_code = c.country_code) d
    on a.COUNTRY_CODE = d.COUNTRY_CODE;

    When I drop index on COUNTRY_CODE, then "ORA-00918: column ambiguously defined" come again.

    And to your reply:
    if I added rowid to the first-line (SELECT '1',rowid) oracle has no chance to know which ROWID you mean.
    OK, but error is fired even when there is no ROWID on the first-line and that is strange to me.
    Are you getting
    ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table
    Or
    ORA-00918: column ambiguously defined
    Because i only get ORA-01445
  • 656310
    656310 Member Posts: 28
    When I use LEFT JOIN I got:
    ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table

    When I use JOIN I got:
    ORA-00918: column ambiguously defined

    I thing difference is cause by our different Oracle versions 10g vs 11g
This discussion has been closed.