Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

flatten table with TIMESERIES

I created the following flatten table statement, syntactically it is correct, but when I run it, I get:
[Vertica]VJDBC ROLLBACK: Unrecognized node type: 311
Any idea how to fix it? Thanks

ALTER TABLE public.std_trades
    ADD COLUMN ticks numeric(35,20)
        DEFAULT (
            SELECT price FROM (
                                  SELECT distribution_id, base_asset_id, counter_asset_id, business_time1, TS_LAST_VALUE(price IGNORE NULLS) AS price
                                  FROM prices_sync.ticks TIMESERIES business_time1 AS '1 minute'
                                      OVER(PARTITION BY distribution_id, base_asset_id, counter_asset_id ORDER BY business_time)
                              ) b
            WHERE base_asset_id=b.base_asset_id and counter_asset_id=b.counter_asset_id
              and DATE_TRUNC('MINUTE', external_trade_ts AT TIMEZONE 'UTC') = b.business_time1 and b.distribution_id=1000
        )
;

Answers

  • It's possible that the AT TIMEZONE part of that isn't immutable, and it's not liking it. You could try it without that to see if it makes it happy.

    Otherwise, break the query down into simpler bits and see which bit it starts complaining about. Hard to debug from this side of the screen.

  • It is not the AT TIMEZONE part, because this still complains:

    if I remove TIMESERIES part, it does querying:

    Does it mean I need to create a table or view for this first?
    SELECT distribution_id, base_asset_id, counter_asset_id, business_time1, TS_LAST_VALUE(price IGNORE NULLS) AS price
    FROM prices_sync.ticks TIMESERIES business_time1 AS '1 minute'
    OVER(PARTITION BY distribution_id, base_asset_id, counter_asset_id ORDER BY business_time)

    If so, I am not familiar with the syntax to create a table out of a select statement, do you mind writing something?

    Thanks

  • So, prices_sync.ticks doesn't exist?

    create table bla as select bla bla

    We call them "CTAS" statements - create table as select.

  • Prices_sync.ticks exists.
    I wonder if the gap filling statement is too complex to be combined with flatten table.
    If I wrap the gap filling statement as a new table, will it be “live”?
  • Jim_KnicelyJim_Knicely Administrator
    edited September 1

    @BHIFP - This appears to be a bug (TIMESERIES clause with a Flattened table).

    Simple reproducer:

    verticademos=> CREATE TABLE test (c INT);
    CREATE TABLE
    
    verticademos=> ALTER TABLE test ADD COLUMN c2 INT DEFAULT (SELECT 1 FROM (SELECT '19-MAR-2018'::TIMESTAMP as tm UNION SELECT '23-MAR-2018'::TIMESTAMP as tm) as t TIMESERIES ts as '1 DAY' OVER (ORDER BY tm) LIMIT 1);
    ROLLBACK 5238:  Unrecognized node type: 311
    

    I created the Jira (VER-78767) to track this issue.

    Thank you for letting us know about this, and I will let you know when the Jira has been fixed.

  • Thanks, Jim. This is my first discovery of a bug. What should be my expectation? ie, how long normally it takes for a bug to be addressed? Once fixed, what does the deployment cycle looks like?

  • Jim_KnicelyJim_Knicely Administrator

    Oh, we don't fix bugs. We just like to identify them. Ha Ha, just kidding!

    Are you a customer of Vertica with paid support? If so, please open a support case ASAP and add a reference to the Jira VER-78767. After opening the case, let me know the case number (via IM) and I can tag the Jira with the case and your company's name. This will help the dev team prioritize the Jira!

    Opening a support case:

    https://softwaresupport.softwaregrp.com/

    By the way, what version of Vertica are you using?

  • Case Number 01934129

    The issue is with EON 10.1.1.6

    Thanks Jim. You almost got me for not fixing bugs :). It also took me a while to figure out how to file a case.

  • Jim_KnicelyJim_Knicely Administrator

    Thanks! I added your account info and Case number to the Jira! I will keep an eye on the support case and Jira and let you know when a fix will be made avaiable!

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.