Ansi Joins and ORA-25154

For one of my projects I needed to create all kind of dynamic queries and execute them against different tables. During this step I decided to use ansi joins even though I don’t like it so much.

In one part of the code I had to join tables using a dynamic SQL while some of the tables had multi-column join condition. Since the entire thing is completely dynamic (I don’t know the table names and column names in advance) I had to dynamically create a SQL like this one (for single column join):

SELECT *
FROM t1,
     t2
WHERE t1.f1 = t2.f1;

Or this one (for multi column join):

SELECT *
FROM t1,
     t2
WHERE t1.f1 = t2.f1
  AND t1.f2 = t2.f2;

But because I don’t know the names of the tables and columns in advance, this quickly became pretty ugly.

It was easy for me to get a comma separated string containing the list of the join column(s), so instead of splitting the string to add the table aliases and the equal sign, I decided to use ansi joins, where I can use the string as is for the join:

SELECT *
FROM t1
     JOIN t2 using (<join_col_string>);

And that worked perfectly! But then I needed to add a predicate on one of the columns, so I got this query:

SQL> SELECT *
  2  FROM t1
  3       JOIN t2 USING (f1,f2)
  4  WHERE t2.f1='X'
WHERE t2.f1='X'
      *
ERROR at line 4:
ORA-25154: column part of USING clause cannot have qualifier

Apparently, when you use ansi join you cannot specify the qualifier for the column that is in the USING clause. The fix is easy, simply remove the “t2.” before “f1”:

SQL> SELECT *
  2  FROM t1
  3       JOIN t2 USING (f1,f2)
  4  WHERE f1='X'

no rows selected

This might make sense, but I find it troublesome. First, it’s easy to understand the query in this case, but what if we have a long query, with quite a few tables? I always add a qualifier to columns, even if the column name is unique. It makes it easier to follow the code and understand the query. If I’m not allowed to add qualifier, it might make it confusing when trying to understand which table this column is coming from.

Besides that, qualifiers are sometime critical, so I thought “there must be some problematic scenario here”. This is a simplified example I found: What would happen if I have another table with the same columns, but the join would be using a different column? Complicated? Let’s see an example:

SQL> create table t1 (f1 number, f2 number);

Table created.

SQL> create table t2 (f1 number, f2 number, f3 number);

Table created.

SQL> create table t3 (f1 number, f2 number, f3 number);

Table created.

And this is how I join them (f1 and f2 for the t1-t2 join, and f3 for joining t3):

SQL> SELECT *
  2  FROM
  3     t1
  4     JOIN t2 USING (f1,f2)
  5     JOIN t3 USING (f3);

no rows selected

And now to the tricky part, I’d like to add a predicate on f2:

SQL> SELECT *
  2  FROM
  3     t1
  4     JOIN t2 USING (f1,f2)
  5     JOIN t3 USING (f3)
  6  WHERE f2='x'
WHERE f2='x'
      *
ERROR at line 6:
ORA-00918: column ambiguously defined

Okay, that makes sense, which f2? The one from the t3 table or the one from t1/t2 (that’s the same f2 because of the join). The one from t3? Not a problem:

SQL> SELECT *
  2  FROM
  3     t1
  4     JOIN t2 USING (f1,f2)
  5     JOIN t3 USING (f3)
  6  WHERE t3.f2='x'

no rows selected

Cool, but the one from t1/t2? A little bit of a problem:

SQL> SELECT *
  2  FROM
  3     t1
  4     JOIN t2 USING (f1,f2)
  5     JOIN t3 USING (f3)
  6  WHERE t1.f2='x'
WHERE t1.f2='x'
      *
ERROR at line 6:
ORA-25154: column part of USING clause cannot have qualifier

Oh, right, I can’t do that. So how do I filter on t1.f2 or t2.f2? I can’t! The only way I found is to rewrite the query that way:

SQL> SELECT *
  2  FROM
  3     t1
  4     JOIN t2 ON t1.f1=t2.f1
  5                AND t1.f2=t2.f2
  6                AND t1.f2='x'
  7     JOIN t3 USING (f3)

no rows selected

(I can also change the t1/t2 join to an inline view, but that will make the query more complex)

I find this annoying. And for me it also doesn’t make any sense. the “t1.f2=’x'” is not part of the join, so it doesn’t make sense that it has to be in the JOIN clause and not in the WHERE clause.

I have to say that I don’t understand why I can’t specify a qualifier for the column that is in the USING clause. It’s a syntax issue, not a logical one. The database is smart enough to handle that (it knows that “t1.f2=’x'” also means “t2.f2=’x'”) and just execute it. That would have solved this strange scenario.

Update – 03-Dec-2019

After a twitter comment from Sven Weller, I had to check outer joins as well, and this is even stranger than I expected. As Sven said, if I cannot qualify the column, with outer join the result might be wrong. I can check if the column is null, and it makes a huge difference which column I use. So first let’s just use the query as is but this time as an outer join:

SQL> select *
  2  from t1
  3       outer join t2 using (f1)
  4* where f1 is null

no rows selected

It works, but as I said, might be problematic as the result is really undefined.

Now let’s see if I can qualify f1:

SQL> select *
  2  from t1
  3       outer join t2 using (f1)
  4* where t1.f1 is null
where t1.f1 is null
      *
ERROR at line 4:
ORA-00904: "T1"."F1": invalid identifier

SQL> select *
  2  from t1
  3       outer join t2 using (f1)
  4* where t2.f1 is null
where t2.f1 is null
      *
ERROR at line 4:
ORA-25154: column part of USING clause cannot have qualifier

The second query was quite expected, but what’s up with the first one? T1.F1 is definitely there! Will try to look into it and update.

Update – 04-Dec-2019

A couple of updates:

A twitter comment from Jonathan Lewis might explain this strange behavior. It seems that the standard says that when you use Natural join or the USING keyword, there is only one copy of the join columns. So you change the join syntax and the number of columns in the result set changes, really crazy:

SQL> select *
  2  from t1 join t2 on t1.f1=t2.f1
  3  where t1.f1=1;

        F1         F2         F1         F2         F3
---------- ---------- ---------- ---------- ----------
         1          1          1          1          1

SQL> select *
  2  from t1 join t2 using (f1)
  3  where f1=1;

        F1         F2         F2         F3
---------- ---------- ---------- ----------
         1          1          1          1

Also, you can see a comment on this post by Iudith Mentzel about the outer join syntax. This looks like a bug, so I’m going to open an SR about that and we’ll see what happens.