[Firebird-devel] [FB-Tracker] Created: (CORE-2096) Remove duplicate columns when selecting * in natural joins

[Philip W. (JIRA) <tr...@fi...>]

Remove duplicate columns when selecting * in natural joins
----------------------------------------------------------

                 Key: CORE-2096
                 URL: http://tracker.firebirdsql.org/browse/CORE-2096
             Project: Firebird Core
          Issue Type: Improvement
          Components: Engine
    Affects Versions: 2.1.0
            Reporter: Philip Williams
            Priority: Minor


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 a.id id, count(*) b_times from a left join b on b.a_id = a.id group by 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira