Ralf Junker wrote:
Even if sqlite3_column_rowid can sometimes not exactly identify a data
field, it can still do so reliably with the majority of queries IMHO.
It could at least do so much better than any other work-around I can
think of. This would finally enable applications to allow users to
edit most of their custom queries and views which are not already
build into the application.
I agree. This is why your proposal would be very useful, even if there
are a few corner cases to watch out for.
Select statement 1:
SELECT c1, c2, c3 FROM a NATURAL JOIN b;
Results:
sqlite_column_int(0) returns 1
sqlite_column_table_name(0) returns 'a'
sqlite_column_origin_name(0) returns 'c1'
----------
Select statement 2:
SELECT c1, c2, c3 FROM a JOIN b USING(c1);
Results:
sqlite_column_int(0) returns 1
sqlite_column_table_name(0) returns 'a'
sqlite_column_origin_name(0) returns 'c1'
Technically SQLite gets both of these results wrong. The value for c1
returned by these select statements are not supposed to be taken from
table a. The sqlite_column_table_name should return a null value. The
origin name could still be returned, because the column name is still c1.
The SQL standard says that a USING clause provides a list of common
column names that must compare equal to implement the join. Only one
copy of the common column data is returned in the result, and the common
column names (the names that appears in the USING clause) cannot be
qualified in the SELECT statement that contains that USING clause. The
same thing applies to a NATURAL join, since that is really just a
shortcut for a USING clause with all the names that are common between
the two tables.
SQLite should generate an error for the qualified c1 names in all the
queries below. The c1 value is not from either table, and so qualifying
the common name with a table name is illegal.
SQLite version 3.3.5
Enter ".help" for instructions
sqlite> CREATE TABLE a (c1, c2);
sqlite> INSERT INTO a VALUES (1, 2);
sqlite>
sqlite> CREATE TABLE b (c1, c3);
sqlite> INSERT INTO b VALUES (1, 3);
sqlite>
sqlite> SELECT a.c1, c2, c3 FROM a NATURAL JOIN b;
1|2|3
sqlite> SELECT b.c1, c2, c3 FROM a NATURAL JOIN b;
1|2|3
sqlite> SELECT a.c1, c2, c3 FROM a JOIN b USING(c1);
1|2|3
sqlite> SELECT b.c1, c2, c3 FROM a JOIN b USING(c1);
1|2|3
sqlite>
This should probably be fixed when your proposal is implemented.
Dennis Cote
