Personally, I think the column names given to a result set from a SELECT by
itself should be the same column names given to a table created through
CREATE TABLE AS.
For example, the following sql:
.headers on
create table a(col1 text, col text);
create table b(col2 text, col text);
insert into a(col1, col) values ('a1', 'a');
insert into b(col2, col) values ('b1', 'b');
select a.col1, b.col2 from a,b;
select a.col, b.col from a,b;
create table c as select a.col1, b.col2 from a,b;
create table d as select a.col, b.col from a,b;
.schema c
.schema d
Produce the following output:
col1|col2
a1|b1
col|col
a|b
CREATE TABLE c("a.col1" text,"b.col2" text);
CREATE TABLE d("a.col" text,"b.col" text);
Note that the column names produced by SELECT are different when SELECT is
run by itself as compared to when SELECT is run as part of a CREATE TABLE
AS. I don't agree with this inconsistency.
Of course we can't create a table with two columns named "col" as in the
second example so when there is a duplicate column name (and only then) full
"table.column" names should be used. Perhaps we need a third pragma to
support this type of naming conventions such as PRAGMA
short_no_conflict_column_names which would be short as long as there are no
conflicts.
My $0.02.
Sam
-------------------------------------------
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
-----Original Message-----
From: Igor Tandetnik [mailto:[EMAIL PROTECTED]
Sent: Wednesday, February 14, 2007 10:24 AM
To: SQLite
Subject: [sqlite] Re: another
Anderson, James H (IT)
<Jim.Anderson-/PgpppG8B+R7qynMiXIxWgC/[EMAIL PROTECTED]> wrote:
> If I run the following sql
>
> create table table_C as
> select
> A.col_1,
> B.col_2
>
> from table_A A,
> table_B B
> where A.col_3 = B.col_4
>
> The table_C is created with the following column names:
>
> "A.col_1",
> "B.col_2"
>
> It seems to me sqlite should strip off the alias qualifier and create
> the table as
>
> col_1,
> col_2
What do you think it should do for something like
create table C as
select A.col, B.col
from A, B;
Igor Tandetnik
----------------------------------------------------------------------------
-
To unsubscribe, send email to [EMAIL PROTECTED]
----------------------------------------------------------------------------
-
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
