The SQL standard has always been a moving feast, chasing the field implementations, perfectly capable of going back on it's earlier mistakes, the main purpose of which, on a good day, is to promote standardisation of SQL implementations and try and keep to the Relational Theory model where practical considerations allow.
So, if the SQL standard has drifted toward requiring "… in the order in which they are defined in the table definition…" to be meaningful, then this is an oversight that would likely be corrected when somebody has an in the field SQL database which, correctly, enforces no such concept. People should not be encouraged to become more dependent on the use of such temporary misfeatures. In context, the particular focus of your objection to the relational approach, seems irrelevant. >> "sort by *" would imply that the order of the columns returned by '*' is >> meaningful, which it is not. "sort by the arbitrary order produced by >> 'select *'" isn't even deterministic. > > In SQL column order *is* deterministic, so the sort order would also > be deterministic. Likely meaningless, but still deterministic. Sort order isn't necessarily deterministic even if we know the column order. So the possibility that we may not know it, makes life no worse. On 2 Jul 2013, at 05:30, Jay A. Kreibich <j...@kreibi.ch> wrote: > On Mon, Jul 01, 2013 at 10:52:20PM -0400, James K. Lowden scratched on the > wall: > >> "select *" is shorthand for "all columns". You'll note that what's >> returned isn't some kind of special '*' column, but all columns. The >> order in which the columns are returned isn't meaningful because the >> colums have labels -- names -- to tell you which is which. Rearranging >> the column order doesn't change the answer. > > That's not quite true. > > What you say is more or less true in pure Relational Theory. Under > Relational Theory, relational attributes (columns) are a proper set. > The columns have no defined order (just as rows have no defined > order), and can only be definitively reference by name. > > In SQL, columns are *not* a set. The order of the columns in any SQL > query or operation is strictly defined. Columns cannot be referenced > by name, because SQL allows name-less columns (SELECT 1, 1, 1;) and > multiple columns with the same name (SELECT 1 A, 1 A, 1 A;). SQL > doesn't even strictly define the column name for a calculated column > (SELECT avg( 1 )) and allows the DB to make up its own names. SQLite > used to have several PRAGMAs to control short and long column names. > > Rather, in SQL, a column is definitively defined by its positional > index in the table or result set. This is also why so many SQL APIs > allow you to fetch column values by index, rather than by name (which > would be a totally broken and dangerous API if columns could move > around). It gets pretty messy... The SQL standard goes to some > length to define a specific column order for stuff like JOIN operations, > including edge-case details like NATURAL JOINs where the number of > columns is reduced and somewhat ambiguously named. > > While rearranging the column order may not functionally change the > answer, a database is not given that flexibility in SQL. For > example, "SELECT *" *must* return the columns in the order they are > defined in the table definition. It isn't that most databases just > happen to do this-- the column order is actually predicated by the > standard. > >> "sort by *" would imply that the order of the columns returned by '*' is >> meaningful, which it is not. "sort by the arbitrary order produced by >> 'select *'" isn't even deterministic. > > In SQL column order *is* deterministic, so the sort order would also > be deterministic. Likely meaningless, but still deterministic. > > > -j > > -- > Jay A. Kreibich < J A Y @ K R E I B I.C H > > > "Intelligence is like underwear: it is important that you have it, > but showing it to the wrong people has the tendency to make them > feel uncomfortable." -- Angela Johnson > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
