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

[Paul Nittel (Created) (JIRA)]

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


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