ambiguous field name error

searain

table1 has the fields "code" and "name"

table2 has the fields "code" and "office"

select table1.code as code, table1.name as name, table2.office as office from table1, table2 where table1.code=table2.code

This sql statement is working in interbase. but when i update the database to firebird, i got the error of "ambiguous field name error about the field name 'code'".

i think in interbase, when i specify table1.code as code, it is clear enough that "code" is for table1.code. but in firebird, it seems not clear enough to see if "code" means table1.code or table2.code.

what is the sql standard in this case? is it interbase not enforcing sql standard strictly, or is it firebird enforcing it too much? what is the mysql's rule about this sql statement?

Thanks!

Sxooter

I'm pretty sure the problem here is in firebird. I'd report it as a bug. Since you never refer to the code column unqualified, the code alias shouldn't be bumping into it.

searain

sxooter, thanks.

i double check the codes, when i post the error codes above here, i have simplfied it. and i left the "order by" out. actually the codes with error is in the "order by"

actually the sql statement is

"select table1.code as code, table1.name as name, table2.office as office from table1, table2 where table1.code=table2.code order by code"

there is "order by code"

I thought when I use "order by code", it is the same as "order by table1.code", because i already select table1.code as code.

This is working in interbase. But the firebird, it creates errors.

i have to use

select table1.code as code, table1.name as name, table2.office as office from table1, table2 where table1.code=table2.code order by table1.code

"order by table1.code".

So my questions stays.

"select table1.code as code, table1.name as name, table2.office as office from table1, table2 where table1.code=table2.code order by code"

this sql statement works in Interbase not firebird, what the sql standard says here? it is still an bug or it is the sql standard that firebird follows and interbase didn't? thanks!

Sxooter

Ok, that IS ambiguous. code could be any of the previous code fields.

searain

So the old interbase (open source version) let me pass that ambiguous field sql statement before is a bug it had, or the interbase had twisted the rule here different from the general sql standard?

Sxooter

It's probably more a difference of implementation. The old interbase guessed that you meant the alias code, while the new firebird can't tell the difference between the assign alias code, and the on implied by the other field in the other table.

Which is one of the reasons I always name fk->pk fields differently, so the problem just doesn't ever show up.

searain

Yes. It seems the old interbase assumes that I am using the alias. But the new firebird, didn't recognize the alias in the "order by" at all.

select table1.code as table1_code, table1.name as name, table2.office as office from table1, table2 where table1.code=table2.code order by table1_code

I used the alias table1_code in the order by. And the new firebird told me there was an error that "table1_code" is not a column name.

Sxooter

I wonder if Firebird is incapable of seeing the alias, or if it can't tell the difference. Does this work:

select code as codealias from table1 order by codealias

???

IF that works, then it can see aliases, but your alias having the same name as a field in another table (table2) is the issue.

If it doesn't work, then it's that firebird can't see aliases in order by.

The more common problem is that people expect something like this to work:

select avg(field1) as myavg where myavg>75

The problem being that where clauses fire before select lists, so there's no such thing as myavg when the where clause fires off.

OTOH, since order by fires AFTER the select list and where clause, it should be able to see aliases.