I must disagree. SQL is based on sets. Sets do not have any order, even if the elements of the set (e.g. cardinal numbers) suggest a "natural" order (which may not be the same for all jurisdictions). An ordered set is called a permutation. Operations on sets (should) yield identical results, regardless of the permutation used. In real implementations, computing a result set may be made very much easier (faster) if an index (i.e. a permutation of elements=rows based on a specific permutation of fields within the row) can be used. As a side effect, the results will tend to be produced in the order suggested by the index used.
Selecting a specific permutation of the result set is specified with the ORDER BY clause and this also tells the database engine that the additional work that may be required is authorized by the writer of the query. The telephone directory is an example of a (seemingly) natural order (surname, firstname) only for societies with the tribalistic concept of a basically immutable "family name". This would be totally useless in iceland, where the "surname" is composed of the first name of the father and the extension -son or -dottir (for male and female descendants respectively) and the natural order becomes (firstname, surname). -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Jean-Christophe Deschamps Gesendet: Donnerstag, 12. Jänner 2017 09:11 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: Re: [sqlite] Why this query plan? Richard, At 02:00 12/01/2017, you wrote: >The "PRAGMA reverse_unordered_selects=ON" statement has long been >available to do this. But it is an optional feature that has to be >turned on. And I don't think anybody ever turns it on. My proposal is >to make it random. > >Maybe it would be sufficient to initialize the >reverse_unordered_selects setting to a random value (on or off) inside >of sqlite3_open(). I read this as a provocative joke. While I agree with you that way too many users and applications blindly (naively?) rely on the current behavior, willfully making the order more or less random by default would be similar, say for a spreadsheet app, to choose random font, size, centering, coloring and formatting of any cell where those attributes have not been explicitely set. Ask yourself, but I for one wouldn't make much use of such a spreadsheet app, even if some standard says it's legitimate behavior. If you ask somebody to enumerate strictly positive integers less than 6, 99.999% of people expect the answer to be 1, 2, 3, 4, 5. While 2, 5, 4, 1, 3 is a perfectly valid answer, anyone would ask "Why this funny order?". _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
