[teiid-issues] [JBoss JIRA] (TEIID-1778) Procedure returning incorrect result set; right number of rows, wrong data

[ https://issues.jboss.org/browse/TEIID-1778?page=com.atlassian.jira.plugin... ] Paul Nittel updated TEIID-1778: ------------------------------- Description: Testing virtual procedures uses the PartsProject, and its PartsVirtual.SupplierInfo table, as a starting point. This transformation is defined as: SELECT PartsSourceA.SUPPLIER_PARTS.SUPPLIER_ID, PartsSourceA.SUPPLIER_PARTS.PART_ID, PartsSourceA.SUPPLIER_PARTS.QUANTITY, PartsSourceA.SUPPLIER_PARTS.SHIPPER_ID, PartsSourceB.SUPPLIER.SUPPLIER_NAME, PartsSourceB.SUPPLIER.SUPPLIER_STATUS, PartsSourceB.SUPPLIER.SUPPLIER_CITY, PartsSourceB.SUPPLIER.SUPPLIER_STATE FROM PartsSourceA.SUPPLIER_PARTS, PartsSourceB.SUPPLIER WHERE PartsSourceA.SUPPLIER_PARTS.SUPPLIER_ID = PartsSourceB.SUPPLIER.SUPPLIER_ID (Fundamentally, this is a SELECT *, but excluding duplicate columns makes it appear more complicated.) One procedure (MMSP07), is defined by this transformation: CREATE VIRTUAL PROCEDURE BEGIN DECLARE string VARIABLES.x; SELECT PartsVirtual.SupplierInfo.SUPPLIER_ID INTO #tempSupplierInfo FROM PartsVirtual.SupplierInfo; LOOP ON (SELECT #tempSupplierInfo.SUPPLIER_ID FROM #tempSupplierInfo) AS supplier_idCursor BEGIN VARIABLES.x = supplier_idCursor.SUPPLIER_ID; END SELECT PartsVirtual.SupplierInfo.SUPPLIER_ID FROM PartsVirtual.SupplierInfo WHERE PartsVirtual.SupplierInfo.SUPPLIER_ID = VARIABLES.x; END The virtual procedure is executed by: Exec PartsVirtual.MMSP07( ) The results should be 16 rows of "S115", but instead it returns 16 rows of the number 1. Attached are the query plan and model project set. was: Testing virtual procedures uses the PartsProject, and its PartsVirtual.SupplierInfo table, as a starting point. This transformation is defined as: SELECT PartsSourceA.SUPPLIER_PARTS.SUPPLIER_ID, PartsSourceA.SUPPLIER_PARTS.PART_ID, PartsSourceA.SUPPLIER_PARTS.QUANTITY, PartsSourceA.SUPPLIER_PARTS.SHIPPER_ID, PartsSourceB.SUPPLIER.SUPPLIER_NAME, PartsSourceB.SUPPLIER.SUPPLIER_STATUS, PartsSourceB.SUPPLIER.SUPPLIER_CITY, PartsSourceB.SUPPLIER.SUPPLIER_STATE FROM PartsSourceA.SUPPLIER_PARTS, PartsSourceB.SUPPLIER WHERE PartsSourceA.SUPPLIER_PARTS.SUPPLIER_ID = PartsSourceB.SUPPLIER.SUPPLIER_ID (Fundamentally, this is a SELECT *, but excluding duplicate columns makes it appear more complicated.) One procedure (MMSP07), is defined by this transformation: CREATE VIRTUAL PROCEDURE BEGIN DECLARE string VARIABLES.x; SELECT PartsVirtual.SupplierInfo.SUPPLIER_ID INTO #tempSupplierInfo FROM PartsVirtual.SupplierInfo; LOOP ON (SELECT #tempSupplierInfo.SUPPLIER_ID FROM #tempSupplierInfo) AS supplier_idCursor BEGIN VARIABLES.x = supplier_idCursor.SUPPLIER_ID; END SELECT PartsVirtual.SupplierInfo.SUPPLIER_ID FROM PartsVirtual.SupplierInfo WHERE PartsVirtual.SupplierInfo.SUPPLIER_ID = VARIABLES.x; END The results should be 16 rows of "S115", but instead it returns 16 rows of the number 1. Attached are the query plan and model project set. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.jboss.org/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira