- j
Jack
4 weeks agoHey, qq, does sorting by a timestamp column work? Seeing some odd results when doing an "order by ts desc" where ts is a Timestamp column. 
Pei
4 weeks agoCould you explain more on “odd results” you get? I have tried sorting by timestamps on our demo and it seems to work.- j
Jack
4 weeks agoThe results were unordered. It only seems to be an issue when we have a WHERE clause Pei
4 weeks agoCould you share the query you have?- j
Jack
4 weeks agoselect * from 'table_name' where venue = 'venue1' and type = 'type1' order by ts desc; Pei
4 weeks agoare
&venue
strings or symbols?type- j
Jack
4 weeks agoThey are both symbols Pei
4 weeks agoI have tried a similar query
on symbols in console with a small dataset (1M) and it seems to be working on my side. If possible, could you try again with something likeWHERE
on yours? And it’d be nice to perhaps create a GitHub issue with some info like, table schema, data volume, your environments, questdb version, etc. so our team can try to reproduce.LIMIT- this is on my local, the one above was on our demo box.
- j
Jack
4 weeks agoI've just had another play with this - and it seems to be a pagination issue in the UI. When I limit to 5000 - the result is as expected. But if I limit to say, 5010, the last 10 records are unsorted. Pei
4 weeks agoThanks for reporting!- j
Jack
4 weeks agono worries Pei
4 weeks agowhich version are you on?- j
Jack
4 weeks agomessage has been deleted Pei
4 weeks agoThanks. Do you mind also try it on 6.4.2? Just tried it on our demo box and it’s also working.- j
Jack
4 weeks agoYep I will try and give it a go today Pei
4 weeks agoThanks!- Hi @Jack have you tested it and does it work?
- j
Jack
3 weeks ago - So it turns out its nothing to do with the version. When running that same query on the original table, it worked as expected. When taking a copy of that table, the sort was wrong.
- They key difference is the copy did not have the indexes from the original table.
- After adding the indexes, it then works as expected.
Pei
3 weeks agoyes symbols require indexes to work properly.- When you say copy of that table, do you copy using SQL statement?
- j
Jack
3 weeks agoyes Pei
3 weeks agogotcha, thank you! I’ll try to reproduce this on my side. 🙂- j
Jack
3 weeks ago👍 Pei
3 weeks agohttps://questdb.io/docs/reference/sql/create-table/#create-table-as This would be more recommended 🙂- j
Jack
3 weeks agoah and that copies over indexes etc? Pei
3 weeks agoyes (at least if it doesn’t I’d see it as a bug…) 😅- j
Jack
3 weeks agoJust gave that a try, but the indexes didnt seem to carry - I ran create table table_test as ( select * from 'table' )
- where table has indexes on 4 columns
Pei
3 weeks agolet me discuss with our engineers a bit will get back to you 🙂
Jaromir Hamala
3 weeks agolooking now- ok, I can confirm
will pick up the designated timestamp from the source table, but it won’t pick up indexes and it won’t pick up even partitioning. I’ll check whether it’s intentional and why. quick thoughts: the inner select might be more complicated than just a simplecreate table as select …
. It can have an arbitrary projection, predicates, it can also include functions, aggregations, etc. So there is not always 1:1 mapping between source and target columns. I reckon the case with 1:1 mapping (=table cloning) is frequent enough to be treated as a special case. But this has to be properly evaluated. The last thing we’d want is to create additional confusion when someselect * from ...
queries do clone indexes & partitioning, others don’t and it’s not immediately obvious why they behave differently.create from select - j
Jack
3 weeks agoUnderstood Jaromir Hamala
3 weeks agoupdate: it’s a feature, not a bug 🙂 you can use something like this:create table clone as ( select * from 'blueprint' ), index(symb) timestamp(ts) PARTITION BY DAY;
Join thread in Slack
View count: 1