The TEIID31153 log is not generally correct.  It should be checking if the load was successful and is not.

For accesses to the view after the ttl I believe what is happening is that the nesting parens are throwing off the hint detection.  With ... AS (...) the parser is just looking at the first token and seeing ( and not seeing comment.  If you remove the parens it should work.  Feel free to log an issue to have us handle this more gracefully.

The documentation says to use parens here Hints and Options - Teiid 8.9 (draft) - Project Documentation Editor, I know used them with parens before. I am confused as how AS (...) parser is different, it uses same QueryParser

Are you executing the query again after the ttl has expired? From what I recall jdv only refreshes the materialization when the a query is executed after ttl.

Jason

> The documentation says to use parens here Hints and Options - Teiid 8.9 (draft) - Project Documentation Editor

Where does it say that?

> I know used them with parens before. I am confused as how AS (...) parser is different, it uses same QueryParser

The parser is looking at specific tokens with regards to hints.  The assumption being that the cache hint appears before the first token.  Since the parens are not part of the create view, they are parsed as part of the table definition query expression.  This means that it would have to look like: AS /*+ cache(ttl:300000) */ (...)

> Are you executing the query again after the ttl has expired? From what I recall jdv only refreshes the materialization when the a query is executed after ttl.

That is correct for internal materialized views.  There is an intention at some point to proactively schedule using the refresh logic associated with external materialization management.  But in this specific case it's the parens that are the issue.

I should add there is [TEIID-2194] Allow the mat view cache hint to be specified via a property - JBoss Issue Tracker which overlaps somewhat with extension properties added for external materialization management.  This would make things a little clearer as the cache hint would not be part of the query expression.

I misread the nesting parens as the ones that are defining the cache hint, not one surround it, nevermind