We currently have no concat function or any other ability to concatenate string.
I am still not clear on your use case. I understand you have millions of records (we deal with many datasets with billions of records)
I do not understand under what conditions you need to send back a list of 1 million sorted results.
As a fast Interactive analytical database we have not seen any really good use cases for this.
What we would normally recommend would be to return back some kind of binned
group by in your example something around date say like:
Assume cstart is your time stamp. This table has about 300m records
mapdql> select date_trunc (day,cstart),count(*) from mydata group by date_trunc (day, cstart) order by date_trunc (day, cstart) desc limit 20;
20 rows returned.
Execution time: 107 ms, Total time: 107 ms
I am returning 20 here but we may want to return hunderds of days (or whatever granularity, whatever is relevant
Then we would hone in on the data we are interested in with a series of finer filter queries until we saw the data that was relevant to the analyst. Rather than drowning them in the delays of delivering 1 Million records… really what are they going to do with those records next?
This is not to say we wont lift the sorting constraints over time but we are targeting an interactive experience for our end users and that does not normally include delivering 1 million results ordered or not. We are trying to lift people out of the REPORT style mentality and allow them to EXPLORE the real data.
Have you reviewed the dashboards at https://www.mapd.com/demos/
You will see many of them have time charts that span many billions of records, that work effectively on this binning approach as you drill into the data.
i hope this helps explain our approach