SQLite Forum

Should SQLite be able to optimize this query?
I'm finding test cases like this:

> ~~~
SELECT group_concat(word,',')
  FROM (SELECT word FROM dictionary ORDER BY 1);

The idea here is that you want to feed the values into group_concat()
in a specific order.  The ORDER BY clause in the subquery is used to
impose that order.

Now, I don't think the SQL language ever guarantees an order in this
particular circumstance.  So, technically, we should be free to ignore
the ORDER BY clause in the subquery above.  But I'm wondering how often
this kind of thing occurs in the wild, and how many applications will
break if I add an optimization to ignore (technically) useless ORDER BY
clauses in subqueries?

If you had an application that has been working great for 10 years and
then starts giving incorrect answers when you upgrade to SQLite 3.37.0,
would you be pleased?

In order to move forward with this optimization, I need to be convinced
that it will be a net positive.  I need evidence that
the benefit of increased performance will outweigh the cost of fixing
applications that break because they were depending on undocumented
behavior.  I'm not yet convinced that the benefits exceed the inconvenience
in this case, but I'm open to arguments to the contrary.