flatten table with TIMESERIES

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.

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

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.

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!

@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