|
Simple Query select * from c_master join e_master; [No Problem] BUT select * from c_master c join e_master e; [Could not execute statement. There is more than one way to join 'c' to 'e' SQLCODE=-147, ODBC 3 State="42000"] Why is this? |
|
This doc page might give a clue: As you are using JOIN without an explicit ON condition and without a further JOIN specifier, you are using a KEY JOIN. And this is somewhat dependent on the role name of the FK relationship, which seems to depend on the name of the table. I could imagine (but that's a guess) that using an ALIAS somewhat "disturbs" that search here - but that would only hold if there are more than one FKs between c_master and e_master, and one of them is named after the parent table - that would be the one used when not using an ALIAS). FWIW, I regurlary use KEY JOINs with tables with ALIASed names and have never run into that issue. IMHO, KEY JOINs only fail (as expected) when there are more than 2 FK relationships (and in our cases, if so, they are both named differently than the parent table itself). |
Can you please tell the version you are using, I can't reproduce your problem in 12.0.1.3769 so maybe it is an old bug?
Martin, I'm on 12.0.1.3750
I acknowledge Volker's comment below, but if the principle of name aliasing for the sake of readability introduces other complexities, it's not aliasing anymore is it?
There should be no reason why the plan execution cannot de-alias the statement to get it to the original form before building the relevant plans, is there?
Just to clarify: Are there 2 FKs between these tables - and if so, how are they named?
Volker, they are named according to a standard naming convention, i.e. fk_EMASTER_CMASTER, so there is no real correlation. I presume a NATURAL JOIN with same-named columns would not have the same problem. Will try it.
But my point still stands that aliasing should not compel you to rewrite your query.
I agree on the "should not compel" point - however, I still have the impression there's something particular to your sample - feel free to show the FK relationships and their role name.
FWIW, as stated, I have never ever run into this issue:
As specified, using a fitting correlation name ensures even a working KEY JOIN when there are more than one FK between both tables (what I have never made use of personally).
So I still would think that in your sample, aliasing just leads to the fact that a previous "correlation name fits role name" situation is no longer true... - and here aliasing surely will make a difference, as docuemtned.
Volker, you're right. There are in fact two foreign keys from table B to table A, both referring to the same PK in table A.
Thanks for the clarification.