On Saturday, 7 December, 2019 16:05, Tim Streater <t...@clothears.org.uk> wrote:
>At various times in various threads on this list it has been stated that >the column name in a result set is not guaranteed unless one uses AS. >IOW, one should say > > select abc as abc from mytable where i=23; > >rather than just: > > select abc from mytable where i=23; > >I'm trying to find where on the SQLite website that is documented, if it >is, so I can point someone at it. The Xojo documentation doesn't mwention >this so either that is a potential problem for all us Xojo users or >they've worked around it somehow in their SQLite wrapper. Currently the default is that pragma short_column_names=1; pragma full_column_names=0; and the rules as described for the pragma full_column_names is in effect. The use of the pragma's is deprecated. https://sqlite.org/pragma.html#pragma_full_column_names Anonymous columns are named "columnX" where x is the column number. For example, "values (1,2),(3,4);" generates a two column table with columns named column1 and column2 and two rows (1,2) and (3,4). You may have duplicate column names in a result set: create table a(id); create table b(id); create table c(id); insert into a values (1); insert into b values (1); insert into c values (1); select * from a, b, c; you will get a table that is the result of the join and the first column will be named "id", the second "id", and the third "id". Which one of these represents which id from which table depends on the nesting order that the optimizer selects when the query is prepared. If you care, then you need to explicitly select the columns by name and give them an output column name using AS: select a.id as a_id, b.id as b_id, c.id as c_id from a,b,c; You may also use the result column metadata function described at https://sqlite.org/c3ref/column_database_name.html to get metadata about the underlying database, table, and column provided that you compiled the version of the SQLite3 library you are using with the appropropriate SQLITE_ENABLE_COLUMN_METADATA C-preprocessor symbol defined. The naming of columns in the result set is NOT GUARANTEED unless you use the AS clause to provide a name. If you do not provide as AS clause to give a result column a name, then each implementation is free to provide whatever names it feels like (so sayeth the standard). In other words, in *ALL* SQL implementations the only time you have guaranteed result column names is if you use the AS clause to give the result set columns names. IN ALL OTHER CASES AND FOR ALL IMPLEMENTATIONS OF ALL SQL INTERFACES the name given the result set column is a non-specified implementation detail. However, there is a general reliance on the rules as outlined above so they are unlikely to change. It is possible that a "pragma random_result_names" might be introduced at some point in the future to assist in finding code that relies on "implementation details" much as "pragma reverse_unordered_selects" assists in finding where reliance is placed on the ordering of result sets when no order by is used. -- 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
