[sqlite] 'order by' doesn't work with 'group_concat()'

[Keith Medcalf]

NB:  This format of the select only works co-incidentally.  Presently, the 
SQLite query planner "believes" that the order by is significant to the result 
and preserves it at some expense.  This may not always be the case.  Thus using 
this method to obtain the desired result is relying on an "implementation 
detail".

select a.id,
       (select group_concat(c)
          from (select chr as c
                  from b
                 where oid = a.id
              order by chr)
       )
  from a;
--EQP-- 0,0,0,SCAN TABLE a
--EQP-- 0,0,0,EXECUTE CORRELATED SCALAR SUBQUERY 1
--EQP-- 2,0,0,SCAN TABLE b
--EQP-- 2,0,0,USE TEMP B-TREE FOR ORDER BY
--EQP-- 1,0,0,SCAN SUBQUERY 2
1|x,y
2|x,y

Using a covering index, however, is more likely to always work (as long as the 
solution method remains recursive row-wise descent):

create index ob on b (oid, chr);

select a.id,
       (select group_concat(chr)
          from b
         where oid = a.id
       )
  from a;
--EQP-- 0,0,0,SCAN TABLE a
--EQP-- 0,0,0,EXECUTE CORRELATED SCALAR SUBQUERY 1
--EQP-- 1,0,0,SEARCH TABLE b USING COVERING INDEX ob (oid=?)
1|x,y
2|x,y