Ambiguous Oracle Result Set

Question

I am working with some legacy SQL that is not really well written. There are ambiguous selects that seem to produce the results we expect for the most part, but not always.

Here's a simplified example of what I am talking about:

create table T1(
  A                 VARCHAR2(10),
  B                 VARCHAR2(10),
  C                 VARCHAR2(10),
  D                 VARCHAR2(10)
  )

create table T2(
  A                 VARCHAR2(10),
  B                 VARCHAR2(10)
  )

insert into T1(A, B, C, D)
values ('AA', 'BB', 'CC', 'DD' )

insert into T2(A, B)
values ('Y', 'N' )

So far nothing unusual, except the queries that are being used...

  SELECT * FROM T1, T2 WHERE T2.A ='Y'

    A B   C   D   A_1 B_1
    AA    BB  CC  DD  Y   N


  SELECT * FROM T2, T1 WHERE T2.A ='Y'

    A B   A_1 B_1 C   D
    Y N   AA  BB  CC  DD

This is clearly ambiguous and Oracle tries to help us by respecifying columns with the suffix _1 where needed. Are there circumstances where Oracle would handle this differently? Eg, Different versions, etc.

For example in the first query the value of Field A is 'AA', but might there be circumstances where the same query would set Field A to 'Y'?

The existing code base has this sort of thing all over the place and it will need to be fixed, for now I am just trying to assess how serious a problem this is? I am mainly concerned that the same SQL, run against different Oracle versions could provide different results.

Thank you!

EDIT: To be clear the plan is to disambiguate the queries. For now the question is how will different versions of Oracle handle this?

This should not be a problem: if you write SQL queries in REAL SQL applications you want never to use `SELECT *`, and rather use `SELECT t1.col1, [...]` — Benoit, Jan 17 '12 at 20:58
The names of columns aren't really part of the result-set, and they depend as much on the programming-language and database-driver as on the underlying database. Also, appending `_1` doesn't really "help" (since it doesn't help you look up a column), unless you're using a language or framework that automatically converts result-set rows to associative arrays. You should add any relevant such information to your question. — ruakh, Jan 17 '12 at 22:16
I agree Select * is a terrible way to do this. This is legacy code that I have inherited. I am hoping to find out if this code will work consistently across different versions of Oracle until I can sort this out correctly. — sse, Jan 18 '12 at 22:23

score 2 · Accepted Answer · edited May 23 '17 at 12:29

2

If possible, you should probably go through the code and explicitly set column names and aliases, and also do the same to any code that accesses your resultsets by column index. The answers in the link below speak to how uncomfortable you should be with your current situation:

select * vs select column

Of course, I know some dev leads that would suggest, "if it ain't broke, don't fix it" but you're not that guy, right?

edited May 23 '17 at 12:29

Community

1
1

answered Jan 17 '12 at 22:26

Tim Lehner

14,813
4
59
76

No I am not that guy, it needs to be fixed. But the code base is huge and this will take some time. Do you know if different versions of Oracle will handle the query differently? – sse Jan 18 '12 at 22:25
I think ruakh's comment stated it correctly about depending upon several layers of tech to get those column names. I wouldn't trust anything to always return column names appended with "_1" and such when that's not part of any standard that I can find. Thanks for the accept, though! – Tim Lehner Jan 19 '12 at 19:36

Ambiguous Oracle Result Set

1 Answers1