With clause materialization is not working in Vertica 8.0.1

prasshant

I am having big analytic query where i amusing one with clause repeatedly , i want to make use of ENABLE_WITH_CLAUSE_MATERIALIZATION.

i am using JDBC Driver v8.0.1-0

I have tried following first by giving hint into query, as well as enabling with clause materialization at session level.

1. WITH /*+ ENABLE_WITH_CLAUSE_MATERIALIZATION  */ with-query...
2. ALTER SESSION SET PARAMETER EnableWithClauseMaterialization=1;

I am getting following error

[Error Code: 2656, SQL State: 42703] [Vertica]VJDBC ERROR: Column ad_line_agg_$1108$.ad_id does not exist

Please help

[Deleted User]

Can you run query without hint or session parameter ? If hint you have provided is incorrect it will be skipped but you should not get above error .

prasshant

We have ran query without that hint, as well as not setting up the materialization param, it works fine.

[Deleted User]

If you have support contract with support please open support ticket. If not please message me design for tables used and query .

bhanu_kishore

can you keep the query in a .sql file and execute .sql file from sql prompt instead of executing over jdbc connection. Just to make sure that the problem is not with client connenction.

prasshant

@bhanu_kishore
I have used vsql console and directly fired sql query from file, and error is not going away (same error), so it is definitely Vertica problem and not JDBC.