Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-4022

Materialization issue dynamic SQL command can't be executed due ambiguous group name

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Blocker
    • Resolution: Done
    • Affects Version/s: 8.12.x
    • Fix Version/s: 9.0, 8.12.5, 8.13.2
    • Component/s: Server
    • Labels:
      None

      Description

      The defined model for materialization:

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

      causes the following exception:

      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
      

      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'

       

      returns: Person

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                van.halbert Van Halbert
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: