Hey qq does sorting by a timestamp column work Seeing some o | Users-public

Channels

users-market-data

users-public

Feed

Channels

Title

#users-public

Jack

07/11/2022, 9:20 AM

Hey, 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

07/11/2022, 1:05 PM

Could you explain more on “odd results” you get? I have tried sorting by timestamps on our demo and it seems to work.

Jack

07/11/2022, 1:07 PM

The results were unordered. It only seems to be an issue when we have a WHERE clause

Pei

07/11/2022, 1:08 PM

Could you share the query you have?

Jack

07/11/2022, 1:09 PM

select * from 'table_name' where venue = 'venue1' and type = 'type1' order by ts desc;

Pei

07/11/2022, 1:25 PM

are

venue

type

strings or symbols?

Jack

07/11/2022, 7:56 PM

They are both symbols

Pei

07/12/2022, 7:46 AM

I have tried a similar query

WHERE

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 like

LIMIT

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.

7:49 AM

this is on my local, the one above was on our demo box.

Jack

07/12/2022, 7:53 AM

I'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

07/12/2022, 7:53 AM

Thanks for reporting!

Jack

07/12/2022, 7:53 AM

no worries

Pei

07/12/2022, 7:56 AM

which version are you on?

Jack

07/12/2022, 7:56 AM

Pei

07/12/2022, 8:01 AM

Thanks. Do you mind also try it on 6.4.2? Just tried it on our demo box and it’s also working.

Jack

07/12/2022, 8:01 AM

Yep I will try and give it a go today

Pei

07/12/2022, 8:01 AM

Thanks!

10:07 AM

Hi @Jack have you tested it and does it work?

Jack

07/14/2022, 4:15 PM

Hey @Pei - sorry not yet, I'll update our test instance now and take a look.

👍 1

4:24 PM

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.

4:24 PM

They key difference is the copy did not have the indexes from the original table.

4:24 PM

After adding the indexes, it then works as expected.

Pei

07/14/2022, 4:25 PM

yes symbols require indexes to work properly.

4:26 PM

When you say copy of that table, do you copy using SQL statement?

Jack

07/14/2022, 4:26 PM

yes

Pei

07/14/2022, 4:27 PM

gotcha, thank you! I’ll try to reproduce this on my side. 🙂

Jack

07/14/2022, 4:30 PM

👍

Pei

07/14/2022, 4:38 PM

https://questdb.io/docs/reference/sql/create-table/#create-table-as This would be more recommended 🙂

Jack

07/14/2022, 4:39 PM

ah and that copies over indexes etc?

Pei

07/14/2022, 4:40 PM

yes (at least if it doesn’t I’d see it as a bug…) 😅

Jack

07/14/2022, 4:43 PM

Just gave that a try, but the indexes didnt seem to carry

4:45 PM

I ran create table table_test as ( select * from 'table' )

4:45 PM

where table has indexes on 4 columns

Pei

07/14/2022, 4:48 PM

let me discuss with our engineers a bit will get back to you 🙂

9:00 AM

Hi @Jaromir Hamala do you mind take a look at what might went wrong here? Thank you 🙏

👀 1

Jaromir Hamala

07/15/2022, 9:05 AM

looking now

🙏 1

9:19 AM

ok, I can confirm

create table as select …

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 simple

select * from ...

. 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 some

create from select

queries do clone indexes & partitioning, others don’t and it’s not immediately obvious why they behave differently.

👍 1

Jack

07/15/2022, 9:20 AM

Understood

Jaromir Hamala

07/15/2022, 11:10 AM

update: 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;

there is currently no single command to clone a table including all options.

💜 1

View count: 1

Join thread in Slack