Ralf Junker wrote:
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'
Ralf,
This example has several issues. First the column c3 does not exist in
table a so the table name returned for c3, 'a', is simply wrong if that
is the actual value returned by SQLite.
More importantly, the column c1 exists in both table a and table b. With
a natural join SQLite (or any other database engine) has no way to know
which of the two tables you would intend to update using the returned
rowid. While it is practical to return the value from either table as
the result of the query, since they are the same, it can't just pick a
table and return the rowid for that table. How does SQLite know I want
to update table a and not table b. To do the later, I need the rowid of
the row in table b, the rowid from table a is useless.
This is different than a normal join which returns the columns from both
tables.
SQLite version 3.3.5
Enter ".help" for instructions
sqlite> CREATE TABLE a (c1, c2);
sqlite> CREATE TABLE b (c1, c3);
sqlite> insert into a values (1, 2);
sqlite> insert into b values (1, 3);
sqlite> .header on
sqlite> .mode column
sqlite> SELECT c1, c2, c3 FROM a NATURAL JOIN b;
c1 c2 c3
---------- ---------- ----------
1 2 3
sqlite> SELECT c1, c2, c3 FROM a JOIN b USING(c1);
c1 c2 c3
---------- ---------- ----------
1 2 3
sqlite> SELECT * FROM a JOIN b on a.c1 = b.c1;
c1 c2 c1 c3
---------- ---------- ---------- ----------
1 2 1 3
sqlite> SELECT c1, c2, c3 FROM a JOIN b on a.c1 = b.c1;
SQL error: ambiguous column name: c1
sqlite> SELECT b.c1, c2, c3 FROM a JOIN b on a.c1 = b.c1;
c1 c2 c3
---------- ---------- ----------
1 2 3
As you can see when I select all columns for a normal join, I get a
column c1 from each table. If I try to select a single column c1, I get
an ambiguous column error. If I supply a table name to resolve the
ambiguity then I get that column only.
With a natural join or a using clause I can't resolve the ambiguity.
This concept is addressed in the SQL standard for this very reason. The
returned column is defined to be different from both of the joined table
columns. It is effectively a column from a temporary table used to
implement the join.
SELECT c1, c2, c3 FROM a JOIN b USING(c1);
Preparing this returns SQLITE_ERROR with message "ambiguous column name: c2".
This is wrong. Column c2 only exists in table b and so it can't be
ambiguous. I suspect you are using different table definitions than I gave.
I agree. sqlite3_column_rowid would return -1 indicating that those
columns can not be directly updated.
I like your proposal, and I understand what you want it for (at least I
think I do :-) ). I just think that the cases where SQLite can't
determine a unique source for the column data, even though it may be
taking the data directly from a particular table column, should be
treated the same as a calculated value and return a sentinel rowid value
of -1. The two cases that came to mind were the natural and using joins,
there may be others.
I'm just saying that you can't update the columns used to join tables by
editing the results of a query that joins two tables using either of
these mechanisms. You can edit the other columns that result from the
join, but to edit the joined columns you would need to use a different
query.
Dennis Cote
