On 10/4/17, Doug Nebeker <ad...@poweradmin.com> wrote: > Is it just a matter of using sqlite3_create_function to register a function > that guarantees it will concatenate in the order rows are received? Would > that guarantee that your example works, or is order no longer guaranteed > once they leave the inner select? > > SELECT group_concat(LineText, '\n') FROM > (SELECT LineText FROM DocLines > WHERE DocID = 10 > ORDER BY LineIndex)
The group_concat() function has always concatenated rows in the order they are received. The problem is that the order they are received by the function is not necessarily obvious from the input SQL, because SQLite is prone doing some serious reorganizations of the input SQL in its quest to come up with the fastest execution plan. But, as it happens, we long ago added constraint 16 to the query flattener (https://www.sqlite.org/draft/optoverview.html#flattening) to prevent the flattener from running on queries like the one you show above. This restriction on the query flattener causes your example query above to do what you want. SQLite version 3.21.0 adds new restrictions on the query flattener which allows the application to control whether expensive functions (or subqueries) are run before or after sorting. See https://www.sqlite.org/draft/optoverview.html#deferred_work for further information. These new flattener restrictions, together with the increased preference for using co-routines, are found in the latest "Pre-release Snapshot". Please try them out if you are able to. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
