Submitted by: Philip Williams (unordained)

Is related to CORE1235

Example:
create table a
(
id integer not null primary key,
x varchar(250)
);

create table b
(
a_id integer not null,
constraint b_fk_a foreign key (a_id) references a (id),
blah timestamp
);

create view c as
select http://a.id id, count(*) b_times from a left join b on b.a_id = http://a.id group by http://a.id;

create view d as
select a.*, c.*
from a natural join c;

That last select statement, by itself, works just fine; but it will have two columns named 'id', of same datatype and value, and the attempt to create the view will fail:

unsuccessful metadata update
STORE RDB$RELATION_FIELDS failed
attempt to store duplicate value (visible to active transactions) in unique index "RDB$INDEX_15"

The Joneses:
I'm not absolutely sure, but I vaguely remember that Oracle automatically drops these duplicate columns when using either USING or NATURAL JOIN syntaxes. Regardless, it would be beneficial for generating unambiguous select * results, particularly for view creation. (I want to later be able to add columns to "a" or "c", and regenerate my view from a script, without having to name every column I added.)

Considerations:
I don't know if "picking" a column to keep would affect automation down-stream; the view resulting from the join probably wouldn't be updatable by default anyway, but there are some (unused?) mechanisms in JDBC for identifying the source table of a given column, and I could see that getting confusing. Also, if NATURAL JOIN and USING support joins across columns of similar but different types (different varchar lengths?), I could see that being weird too.