On Sun, 25 Jan 2015 23:18:05 +0200 RSmith <rsm...@rsweb.co.za> wrote:
> There is no documentation in either SQLite or the SQL standard > that would lead anyone to believe that behavior is expected - in fact > it is very clear about the returned column names being > non-deterministic if not explicitly requested by aliasing. I no longer think that is entirely true. In SQLite's documentation, it's not in the description of the SELECT statement that I can find (http://www.sqlite.org/lang_select.html). It is mentioned in the sqlite3_column_name description, so, OK, it's not a bug. But I'm going to say that a function that "returns the name" of a column really should return the column's *name*. Especially if that name would suffice to address the column syntactically if the form statement were wrapped one more level, i.e.: select * from (<select statement>) as T Otherwise there's weird mismatch between the names SQL itself uses and the ones presented to the user. My reading of what I can find of the standard makes me think that if this is left up to the implementation, it's a molehill we can't use to shout from. Definitely it's a POLA pain point from the user's perspective. The standard is proprietary, but the old SQL-92 draft is online at http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt. Part 6.4 describes a "column reference", and distinguishes a column *reference* from its name: <column reference> ::= [ <qualifier> <period> ] <column name> <qualifier> ::= <table name> | <correlation name> So there's no such thing (in case anyone thinks there is) as a "fully qualified name" in SQL. There are names and references. Sometimes a reference is letter-for-letter identical to the name; sometimes the reference is longer to distinguish one name from another belonging to a differerent entity. What should the "name" be that is returned to the user? Going back to my example, select <columns> from (<select statement>) as T each name in <columns> should be the shortest possible syntactically correct column reference. A <select statement> that designates a unique set of names (by whatever means) thus yields an unambigiuous, reasonable result. One that doesn't is more problematic to the user, but then he's playing a little fast and loose in the first place. If there is no syntactically correct reference for a particular column then, sure, make one up. No one can complain about that. Implementation of such a policy would not break any existing applications. It might even yield improvements to SQLite internally by making names more dependable, and by distinguishing more strongly between the column's name and its syntactic reference. Humbly submitted. --jkl P.S. I tried testing this with sqlfiddle to get a vote, too. But they must have a problem with their output column logic; afaict same-named columns are ignored after the first one (as it were). If you cross-join a table to itself, you get only one set of columns, not two. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
