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

[Paul Nittel (Updated) (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-1778?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Paul Nittel updated TEIID-1778:
-------------------------------

    Attachment: MMSP07_plan.txt
                VirtualProcedures_MPS.zip


Query plan and Model Project Set
                
> 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.

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