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