1 2 Previous Next 18 Replies Latest reply on Jun 4, 2013 1:49 PM by 656310

    ROWID mismatch

    656310
      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
        • 1. Re: ROWID mismatch
          908002
          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..
          • 2. Re: ROWID mismatch
            656310
            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
            • 3. Re: ROWID mismatch
              RogerT
              Alread tried to add a column alias to the b.rowid? Something like b.rowid as row_id ?

              hth
              • 4. Re: ROWID mismatch
                Karthick2003
                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> 
                1 person found this helpful
                • 5. Re: ROWID mismatch
                  656310
                  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 ;)
                  • 6. Re: ROWID mismatch
                    656310
                    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
                    • 7. Re: ROWID mismatch
                      RogerT
                      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
                      • 8. Re: ROWID mismatch
                        656310
                        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.
                        • 9. Re: ROWID mismatch
                          Karthick2003
                          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
                          • 10. Re: ROWID mismatch
                            656310
                            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
                            • 11. Re: ROWID mismatch
                              Thomas Morgan
                              When I rewrite the query to old fashion outer join it works:
                              select '1'
                              from   dual a,
                                     (
                                            select c.dummy 
                                               , b.rowid
                                            from   dual b,
                                                   dual c
                                            where  b.dummy = c.dummy
                                     ) d
                              where  a.dummy = d.dummy(+);
                              Then, this would make me think "bug in Oracle's implementation of outer join".

                              Thanks,

                              Tom
                              1 person found this helpful
                              • 12. Re: ROWID mismatch
                                Jonathan Lewis
                                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
                                • 13. Re: ROWID mismatch
                                  >
                                  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.
                                  >
                                  Can you tell us how to conduct a test that shows that alias being used?
                                  • 14. Re: ROWID mismatch
                                    onedbguru
                                    ROWID is a HASH of FILE|BLOCK|ROW - the EXACT location in the database. Dual only has one row, therefore has only one ROWID.

                                    You can see this by determining the file and block by
                                    SQL> select dbms_rowid.rowid_to_absolute_fno(rowid, 'SYS', 'DUAL') FILE_NO,
                                    dbms_rowid.rowid_block_number(rowid) BLOCK_NO
                                    from dual
                                    /

                                    FILE_NO BLOCK_NO
                                    ---------- ----------
                                    1 44

                                    Yours will be different.

                                    Next you can use "alter system dump ... " to actually view the formatted block and see the row number where your data lives.
                                    1 2 Previous Next