[teiid-issues] [JBoss JIRA] (TEIID-1893) TeiidException when executing SELECT subquery with more than 2 UNIONs

[Steven Hawkins (Resolved) (JIRA)]

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

Steven Hawkins resolved TEIID-1893.
-----------------------------------

    Fix Version/s: 7.6.1
       Resolution: Done


The issue was invalid output columns on the access node after it had been raised.  This happens with a specific set of circumstances, but to ensure we aren't making invalid assumptions the fix reuses RuleCleanCriteria remove output columns (which was the case before the change to run RuleAssignOutput initially in planning) prior to costing.

A more refined fix, just as with updates to costing, would be to incrementally invalidate/update nodes that have been modified, rather than removing all output columns.
                
> TeiidException when executing SELECT subquery with more than 2 UNIONs
> ---------------------------------------------------------------------
>
>                 Key: TEIID-1893
>                 URL: https://issues.jboss.org/browse/TEIID-1893
>             Project: Teiid
>          Issue Type: Bug
>          Components: Query Engine
>    Affects Versions: 7.6
>         Environment: Windows 7, JBoss 5.1.0.GA, java version "1.6.0_22" 64 bit
>            Reporter: Andriy Rokhmanov
>            Assignee: Steven Hawkins
>             Fix For: 7.6.1
>
>         Attachments: plan.txt
>
>
> Exception: org.teiid.core.TeiidException happens after executing select statement #1, but works fine on #2 and #3.
> The execution plan for problematic statement attached.
> 1. Problematic SQL Statement:
> 	SELECT T.ROLE_NAME as ROLE_NAME, T.SCHEMA_NAME as SCHEMA_NAME, T.INSTALL_STATUS as INSTALL_STATUS
> 		FROM (
> 				SELECT ROLE_NAME, 'demodata_chorus_metadata' as SCHEMA_NAME, INSTALL_STATUS from demodata_chorus_metadata.chorus_role 
> 				UNION
> 				SELECT ROLE_NAME, 'security_cia_chorus_metadata' as SCHEMA_NAME, INSTALL_STATUS from security_cia_chorus_metadata.chorus_role 
> 				UNION
> 				SELECT ROLE_NAME, 'vantage_chorus_metadata' as SCHEMA_NAME, INSTALL_STATUS from vantage_chorus_metadata.chorus_role 
> 			) AS T, chorus_license.RL_LICENSE as L 
> 		WHERE T.ROLE_NAME = L.ROLE_NAME 
> 		AND T.install_status = true;
> 2. This SQL statement works fine (the difference between #1 and #2 is in final 'AND...' clause):
> 	SELECT T.ROLE_NAME as ROLE_NAME, T.SCHEMA_NAME as SCHEMA_NAME, T.INSTALL_STATUS as INSTALL_STATUS
> 		FROM (
> 				SELECT ROLE_NAME, 'demodata_chorus_metadata' as SCHEMA_NAME, INSTALL_STATUS from demodata_chorus_metadata.chorus_role 
> 				UNION
> 				SELECT ROLE_NAME, 'security_cia_chorus_metadata' as SCHEMA_NAME, INSTALL_STATUS from security_cia_chorus_metadata.chorus_role 
> 				UNION
> 				SELECT ROLE_NAME, 'vantage_chorus_metadata' as SCHEMA_NAME, INSTALL_STATUS from vantage_chorus_metadata.chorus_role 
> 			) AS T, chorus_license.RL_LICENSE as L 
> 		WHERE T.ROLE_NAME = L.ROLE_NAME;
> 3. This SQL statement also works fine (the difference between #1 and #3 is only two UNIONs included out of three, does not matter which ones)
> 	SELECT T.ROLE_NAME as ROLE_NAME, T.SCHEMA_NAME as SCHEMA_NAME, T.INSTALL_STATUS as INSTALL_STATUS
> 		FROM (
> 				SELECT ROLE_NAME, 'security_cia_chorus_metadata' as SCHEMA_NAME, INSTALL_STATUS from security_cia_chorus_metadata.chorus_role 
> 				UNION
> 				SELECT ROLE_NAME, 'vantage_chorus_metadata' as SCHEMA_NAME, INSTALL_STATUS from vantage_chorus_metadata.chorus_role 
> 			) AS T, chorus_license.RL_LICENSE as L 
> 		WHERE T.ROLE_NAME = L.ROLE_NAME 
> 		AND T.install_status = true;

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