Hello Dennis Cote, > SELECT c1, c2, c3 FROM a NATURAL JOIN b; > SELECT c1, c2, c3 FROM a JOIN b USING(c1); > >There is no way to know which table the c1 column value is associated with >(technically the selected columns in such joins don't belong to either table >according to the SQL standard) so no unique rowid can be returned.
I think as long as SQLite can return a value for a single row, it should be able to identify the RowID from which it took that value. How else should it know where to take the value from? If there are ambiguities, SQLite does in fact detect them when preparing the query (see your 2nd example, discussed below). For your example queries, SQLite returns: SELECT c1, c2, c3 FROM a NATURAL JOIN b; sqlite_column_table_name(0) returns 'a' sqlite_column_origin_name(0) returns 'c1' sqlite_column_table_name(1) returns 'a' sqlite_column_origin_name(1) returns 'c2' sqlite_column_table_name(2) returns 'a' sqlite_column_origin_name(2) returns 'c3' SELECT c1, c2, c3 FROM a JOIN b USING(c1); Preparing this returns SQLITE_ERROR with message "ambiguous column name: c2". I understand this to back my initial assumption that sqlite3_column_rowid should basically be able to return a valid RowID whenever sqlite_column_table_name and sqlite_column_origin_name do not return NULLs. >This may seem a little unnatural at first, but you would not usually be >editing such [calculated] columns in queries where they were used to join >multiple tables. If these columns are to be edited, it would have to be >through a query on one table or the other. I agree. sqlite3_column_rowid would return -1 indicating that those columns can not be directly updated. Your solution suggest the usual way to update such queries. Btw: My sqlite3_column_rowid proposal does not attempt make available a mechanism to update calculated values: these can not be updated because their origin is unknown by design. Instead, I am trying to to handle reliably the the majority of cases where non-calculated values can currently not be updated because their corresponding RowID is unavailable through the API. Regards, Ralf
