Hi Sam, On 2/14/07, Samuel R. Neff <[EMAIL PROTECTED]> wrote:
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
I puzzled over "short as long" for a bit, and then smiled. You make a good point, and this can be confusing. I don't know the internals and intricacies of SQLite, and this behavior bit me a while back. Since then I have learned to simply alias all my columns explicitly for all operations (other than the simplest... single table, or multiple tables with all different column names). Once I got in the habit of aliasing all the time, there never has been a problem. It is always nice if SQLite can do "one more thing" but for me, as long as it keeps on doing what it does very well, standards-based, and allows me to add my own capabilities (which it does, via its capability to add functions), I'd rather do some extra work myself than complicate the core program.
-----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] -----------------------------------------------------------------------------
-- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ --------------------------------------------------------------------- collaborate, communicate, compete ===================================================================== ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
