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

Procedure returning incorrect result set; right number of rows, wrong data -------------------------------------------------------------------------- Key: TEIID-1778 URL: https://issues.jboss.org/browse/TEIID-1778 Project: Teiid Issue Type: Bug Components: Query Engine Affects Versions: 7.4.1 Environment: JBDS 4.1.1 M2, SOA-P 5.2 ER4 Reporter: Paul Nittel Assignee: Steven Hawkins Attachments: MMSP07_plan.txt, VirtualProcedures_MPS.zip 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.