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

D. Richard Hipp
sqlite-users mailing list

Reply via email to