[teiid-issues] [JBoss JIRA] (TEIID-4022) Materialization issue dynamic SQL command can't be executed due ambiguous group name

[Van Halbert (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-4022?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13172235#comment-13172235 ] 

Van Halbert edited comment on TEIID-4022 at 3/4/16 1:40 PM:
------------------------------------------------------------

There was no error being seen when this was occurring.   The status table was updated to indicate the load had failed.     Only after trying to query the cache was the following exception seen:
{code}Caused by: org.teiid.api.exception.query.FunctionExecutionException: TEIID31147 The materialized view PersonMatModel.PersonMatView materialization table is currently not in valid state. 
{code}

And the above exception:  org.teiid.api.exception.query.QueryProcessingException

was not being seen in the server log, only thru debug did I see the issue found in the ProcedurePlan code (line 394).




was (Author: van.halbert):
Also, to note, the above exception:  org.teiid.api.exception.query.QueryProcessingException

was not being seen in the server log, only thru debug did I see the issue found in the ProcedurePlan code (line 394).

> Materialization issue dynamic SQL command can't be executed due ambiguous group name  
> --------------------------------------------------------------------------------------
>
>                 Key: TEIID-4022
>                 URL: https://issues.jboss.org/browse/TEIID-4022
>             Project: Teiid
>          Issue Type: Bug
>          Components: Server
>    Affects Versions: 8.12.x
>            Reporter: Van Halbert
>            Assignee: Steven Hawkins
>            Priority: Blocker
>
> The defined model for materialization:
> {code}
> 	<model name="PersonMatModel" type="VIRTUAL">
> 		<metadata type="DDL"><![CDATA[
> 	CREATE view PersonMatView
> 		(
>             name string,
>             id integer,
>             email string
>  		) OPTIONS (MATERIALIZED 'TRUE', UPDATABLE 'TRUE',         
>                MATERIALIZED_TABLE 'PersonMatCache.Person', 
>                "teiid_rel:MATVIEW_TTL" 60000,
>                "teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'execute PersonMatCache.native(''truncate cache'');',
>                "teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT"  'execute PersonMatCache.native(''swap cache names'');', 
>                 "teiid_rel:MATERIALIZED_STAGE_TABLE" 'PersonMatCache.Person',
>               "teiid_rel:MATVIEW_SHARE_SCOPE" 'NONE',
>                "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true', 
>                "teiid_rel:MATVIEW_STATUS_TABLE" 'Accounts.status',                
>                "teiid_rel:MATVIEW_ONERROR_ACTION" 'THROW_EXCEPTION')
>  		AS SELECT  name, id, email from PersonInfoModel.Person;
>     	]]>
>    		</metadata>
> 	</model>   
> {code}
> causes the following exception:
> {code}
> org.teiid.api.exception.query.QueryProcessingException: TEIID30168 Couldn't 
> execute the dynamic SQL command "EXECUTE IMMEDIATE ('SELECT count(*) as 
> rowCount FROM ' || matViewTable) AS rowCount integer INTO #load_count" with 
> the SQL statement "('SELECT count(*) as rowCount FROM ' || matViewTable)" due 
> to: Group specified is ambiguous, resubmit the query by fully qualifying the group 
> name: Person
> {code}
> And executing the following internal call that Teiid uses does produce the non-qualifed name
> {ccode}
> SELECT TargetName from SYSADMIN.MatViews WHERE VDBName = 'PeopleMat' AND SchemaName = 'PersonMatModel' AND Name = 'PersonMatView'
> {code}
> returns:  Person



--
This message was sent by Atlassian JIRA
(v6.4.11#64026)