flatten table with TIMESERIES

BHIFP

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
        )
;

Vertica_Curtis

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.

BHIFP

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

Vertica_Curtis

So, prices_sync.ticks doesn't exist?

create table bla as select bla bla

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

BHIFP

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_Knicely

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

BHIFP

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_Knicely

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?

BHIFP

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_Knicely

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!

SruthiA

@BHIFP : VER-78767 has been fixed. please upgrade your cluster to 10.1.1-9 or 11.0.0-3 to avail fix for this issue.

https://www.vertica.com/docs/ReleaseNotes/11.0.x/Vertica_11.0.x_Release_Notes.htm#11.0.0-3

https://www.vertica.com/docs/ReleaseNotes/10.1.x/Vertica_10.1.x_Release_Notes.htm#10.1.1-9