|
From: Philip W. (JIRA) <tr...@fi...> - 2008-09-30 17:01:46
|
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
|