On Sunday, 17 March, 2019 12:30, Yannick Duchêne <yannick_duch...@yahoo.fr> wrote:
>Hi people, it’s a long time I did not get there. >I’m currently to create and use an SQLite DB with the Python biding >APSW. For each row returned, I always retrieve the description with >`Cursor.getdescription()`. Surprisingly, during a test, I noticed the >name of a column as returned in the description, is not the same as >in the query. That column, is an alias of the ROWID. >Here is an example to test: > -- `id` is `integer primary key`, so that it is an alias of rowid, > -- which is necessary to use it in `using (…)` clauses. > create table test (id integer primary key, a int, b int); > insert into test (a, b) values (1, 2); > insert into test (a, b) values (3, 4); > insert into test (a, b) values (5, 6); > insert into test (a, b) values (7, 8); > select rowid, id, a, b from test; >The description of the SELECT query result, returns these column >names: ID, ID, A, B, instead of ROWID, ID, A, B. Well, that makes perfect sense since you get the column name metadata by calling the sqlite3_column_name function with a pointer to the statement and the column number. That function returns the column name. In other words, the call sqlite3_column_name(..., 0) is returning the name of the first column in the query, and that column happens to be named "id". (There is no column named "rowid", that column is named "id"). If you want a specific column name then you must specify the column name that you want, otherwise the column name is not guaranteed to relate to anything at all (thus sayeth the standard) -- although there is an attempt to return the underlying column name if it exists (and the underlying column name is "id"). >That’s not annoying to me, since this was just to check ID is indeed >an alias of ROWID (it’s a long time I did not use SQLite), but may be >that’s a bug, although not a nasty one, so I wanted to tell. Unless >I’m wrong and that’s the expected result? select rowid as rowid, id, a, b from test; specifies the name you want to appear for the first column. The other names for the other four columns are free to be whatever they want ... that is, without an AS clause, the column names are not guaranteed to be anything other than a random basket of characters though SQLite will attempt to return the underlying table and column name (from the table declaration). >Aside, what also surprised me just a moment ago and I never noticed >before, is that although I can refer to ROWID (not aliased) anywhere >in a query, it seems to not work properly in `using(rowid)`, if the >ROWID is automatically generated in one of the table (ex. an ordinary >table with no “without rowid”) and it is not in the other table (ex. >a column named ROWID in a VIEW). You will have to provide an example. t1 JOIN t2 USING (ROWID) works perfectly fine for me when ROWID is an explicitly defined rowid (integer primary key in a rowid table). Are you trying to perform a join using the ephemeral ROWID? --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
