SQLite Forum

Should SQLite be able to optimize this query?
Login
I have that same hunch, for the same reason. And, even before you suggested that group_concat() enhancement, I was thinking, "Oh nuts. I may have to fix that usage sin committed years ago by writing a smarter concatenator() aggregate function."

It is true that there is no good, ready-to-go substitute for the aggregated items being aggregated in a user-determined order. I have relied on the present behavior myself<sup>a</sup>, even knowing that it was not strictly predictable, taking the calculated risk that the present SQLite treatment would continue.

Based on the problem I was solving, and the extra work necessitated by more "pure" alternatives, I expect my reliance<sup>b</sup> is not rare among users.

----

a. The application was to take data from a schematic database, (at which point it is organized as Dr. Codd would approve), and from that producing a report in a format which is very commonly used by organizations that build electronic assemblies. The data in the report is badly non-normalized. In particular, each part type has an associated designator list, naming the individual usages of that part type in the assembly. People like to see these in a very predictable order, mainly because people reading the report want to find "C273" or whatever.

Using a set of views and queries, I could produce that report in one fell swoop from the properly normalized database. Without the ability to order the input to group_concat() to put that designator glom in the expected order, a separate processing step would be needed, losing real convenience. Now, the BoM (Bill of Materials) view output can be simply plopped in a spreadsheet, which is the preferred form of the wants-to-be-data.

b. If the designators are misordered, the "data" is still there and subject to later fixup. It's an annoyannce problem, not a missile launch failure issue.