6

I am running Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.8.0.0.0.

Consider the following example:

create table tab_a as
          select 1 as id from dual
union all select 2 as id from dual
union all select 3 as id from dual
;

create table tab_b as
          select 1 as id, 'b1' as val, 'bbb1' as val_b from dual
union all select 2 as id, 'b2' as val, 'bbb2' as val_b from dual
;

create table tab_c as
          select 1 as id, 'c1' as val, 'ccc1' as val_c from dual
union all select 3 as id, 'c3' as val, 'ccc1' as val_c from dual
;

select
  a.id
 ,b.val
 ,b.val_b
 ,b.val_c
from
  tab_a a
  left join tab_b b on b.id = a.id
  left join tab_c b on b.id = a.id
order by
  a.id
;

As you can see, I reused the "b" alias on another table (duplicate). This is the result:

        ID VAL        VAL_2      VAL_3
---------- ---------- ---------- ----------
         1 b1         bbb1       ccc1
         2 b2         bbb2       (null)
         3 (null)     (null)     ccc1

So:

  • the column "val", present in both tables, is taken from tab_b, which was declared first
  • the column "val_b" is taken from tab_b
  • the column "val_c" is taken from tab_c

It appears that Oracle assigns the alias to both tables with a priority and it does not report it as incorrect syntax. But this is something I would like to avoid. I was surprised to have discovered this Bug in my query. Is this behavior known? Can anything be done to enforce unique aliases?

Arnoldo
  • 73
  • 4
  • 2
    I don't see much point in the question: You shouldn't be using the same alias for two different tables...or the same alias more than once. – Tim Biegeleisen May 20 '21 at 07:45
  • 7
    My guess would be that this is a bug. You could open a support ticket and/or search Metalink to see if there is a patch. But it's probably not a high priority bug to be fixed when you can just fix your alias. – Justin Cave May 20 '21 at 07:54
  • I generally avoid using table aliases at all, and only use them when the same table is used more than once in the query. they are not needed in this query – Jad May 20 '21 at 07:55
  • As info MSSQL does report an error `The correlation name 'b' is specified multiple times in a FROM clause. ` - I would say that the query designer should be aware enough to not make this mistake in the first place. First of all, try to avoid using single character alias unless it's obvious what you are referring to. – Michael Z. May 20 '21 at 08:01
  • 1
    Moreover, if you add `b.id` to the SELECT list, the query is still valid. `b.id` will refer to the first joined table. Can't find a proper doc descrbing this behaviour. – Serg May 20 '21 at 08:19
  • 1
    @TimBiegeleisen the point is: if you write an incorrect query, you should get a compile error, or at least a runtime error. There are many things you shouldn't do, but guess what: people make errors, all the time. This bug has been in my code for years and nobody noticed – Arnoldo May 20 '21 at 08:21
  • I agree that it is a curious error +1. – Tim Biegeleisen May 20 '21 at 08:21
  • 1
    @jad the guy who coded the core of my banking system told me to **ALWAYS** use aliases. Otherwise, if you make a typo, it's much easier to miss it. Also, if you deleta a column from the table in a subquery, oracle will try to match this column name with a column from a different table, potentially leading to a nasty bug. – Arnoldo May 20 '21 at 08:25
  • 1
    To everyone saying _just don't use same alias twice_: the problem is, I have hundreds of queries, many of them hundreds of lines of code long. They are expanded over the years and they join 10+ tables. It is quite easy to duplicate an alias at some point. – Arnoldo May 20 '21 at 08:31
  • [Related DBA question](https://dba.stackexchange.com/q/211046/847); it refers to a different (possibly fixed) bug, but "I don't understand why this is allowed" still applies, and I don't think the referenced bug addresses that. – Alex Poole May 20 '21 at 08:57
  • @Przemator, I think you misunderstand me. you always use full table names on the joins, rather than table aliases. that way everything is very explicit, and very debuggable – Jad May 20 '21 at 09:02
  • @AlexPoole: yes I saw this question before posting my own, but I didn't like how the example is structured. In my example I check what happens in each case. I think when you refer to an alias, Oracle just loops over all tables attached to this alias in the order of declaration, and if it finds a matching column, it goes with it. – Arnoldo May 20 '21 at 09:04
  • @jad: ok I see. Well it works with short tables names, but consider that in my db the table names are e.g. `obj_person_det_hist`, `obj_person_name_hist`, `obj_person_crs_attr`. Joining it all together without aliases is an eyesore. – Arnoldo May 20 '21 at 09:09
  • @Przemator I prefer to add in white space (new lines), and consider it readable/maintainable ... – Jad May 20 '21 at 09:17
  • 1
    If anyone is curious, this bug is in other versions as well: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=27d0c92f8b97bea4325798eb3bfdf3b2. – Gordon Linoff May 20 '21 at 11:51
  • @GordonLinoff this bug was in my code since version 12c – Arnoldo May 21 '21 at 13:54

1 Answers1

0

I cannot comment due to my reputation, but had this issue recently and wanted to mention is fixed on 23c.

SQL> select
  2    a.id
  3   ,b.val
  4   ,b.val_b
  5   ,b.val_c
  6  from
  7    tab_a a
  8    left join tab_b b on b.id = a.id
  9    left join tab_c b on b.id = a.id
 10  order by
 11    a.id
 12  ;
 ,b.val
  *
ERROR at line 3:
ORA-00918: column ambiguously defined
kLorD
  • 41
  • 2