• j

    Jack

    4 weeks ago
    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

    Pei

    4 weeks ago
    Could 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 ago
    The results were unordered. It only seems to be an issue when we have a WHERE clause
  • Pei

    Pei

    4 weeks ago
    Could you share the query you have?
  • j

    Jack

    4 weeks ago
    select * from 'table_name' where venue = 'venue1' and type = 'type1' order by ts desc;
  • Pei

    Pei

    4 weeks ago
    are
    venue
    &
    type
    strings or symbols?
  • j

    Jack

    4 weeks ago
    They are both symbols
  • Pei

    Pei

    4 weeks ago
    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.
  • this is on my local, the one above was on our demo box.
  • j

    Jack

    4 weeks ago
    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

    Pei

    4 weeks ago
    Thanks for reporting!
  • j

    Jack

    4 weeks ago
    no worries
  • Pei

    Pei

    4 weeks ago
    which version are you on?
  • j

    Jack

    4 weeks ago
    message has been deleted
  • Pei

    Pei

    4 weeks ago
    Thanks. 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 ago
    Yep I will try and give it a go today
  • Pei

    Pei

    4 weeks ago
    Thanks!
  • Hi @Jack have you tested it and does it work?
  • j

    Jack

    3 weeks ago
    Hey @Pei - sorry not yet, I'll update our test instance now and take a look.
  • 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

    Pei

    3 weeks ago
    yes symbols require indexes to work properly.
  • When you say copy of that table, do you copy using SQL statement?
  • j

    Jack

    3 weeks ago
    yes
  • Pei

    Pei

    3 weeks ago
    gotcha, thank you! I’ll try to reproduce this on my side. 🙂
  • j

    Jack

    3 weeks ago
    👍
  • Pei

    Pei

    3 weeks ago
  • j

    Jack

    3 weeks ago
    ah and that copies over indexes etc?
  • Pei

    Pei

    3 weeks ago
    yes (at least if it doesn’t I’d see it as a bug…) 😅
  • j

    Jack

    3 weeks ago
    Just 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

    Pei

    3 weeks ago
    let me discuss with our engineers a bit will get back to you 🙂
  • Hi @Jaromir Hamala do you mind take a look at what might went wrong here? Thank you 🙏
  • Jaromir Hamala

    Jaromir Hamala

    3 weeks ago
    looking now
  • 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.
  • j

    Jack

    3 weeks ago
    Understood
  • Jaromir Hamala

    Jaromir Hamala

    3 weeks ago
    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.