[teiid-issues] [JBoss JIRA] (TEIID-2071) Getting IndexOutOfBoundsException while querying to a view that has a select statement with multiple union statements

Getting IndexOutOfBoundsException while querying to a view that has a select statement with multiple union statements ---------------------------------------------------------------------------------------------------------------------- Key: TEIID-2071 URL: https://issues.jboss.org/browse/TEIID-2071 Project: Teiid Issue Type: Bug Components: Query Engine Affects Versions: 7.7 Environment: Windows 7, 64 bit, JBoss 5.1.0.GA, Java 32 bit - version "1.7.0_02" , Dynamic VDB Reporter: Rajasekhar Kota Assignee: Steven Hawkins Hi, I have a view (SCH.MYVIEW) with the below definition: <view name="SCH.MYVIEW"> <columns> <column name="__objecttype__" type="varchar"/> <column name="__objecttypexmlPK__" type="varchar"/> <column name="tocde" type="integer"/> <column name="version" type="integer"/> <column name="ctcode" type="integer"/> <column name="sid" type="varchar"/> <column name="splx" type="varchar"/> <column name="ctod" type="timestamp"/> <column name="stod" type="timestamp"/> <column name="mcode" type="varchar"/> <column name="uuid" type="varchar"/> <column name="uid" type="varchar"/> <column name="uname" type="varchar"/> <column name="job" type="varchar"/> <column name="src" type="varchar"/> <column name="adm" type="varchar"/> <column name="Commandstr" type="varchar"/> <column name="oprcode" type="integer"/> <column name="vio1code" type="integer"/> <column name="vio2code" type="integer"/> <column name="desc" type="varchar"/> <column name="cdesc" type="varchar"/> </columns> <definition> SELECT "g_0"."sid" AS c_0, "g_0"."ctod" AS c_1, "g_0"."stod" AS c_2, "g_0"."job" AS c_3, "g_0"."COMMANDSTR" AS c_4, "g_0"."splx" AS c_5, "g_0"."__objecttype__" AS c_6, "g_0"."adm" AS c_7, "g_0"."desc" AS c_8, "g_0"."uname" AS c_9, "g_0"."cdesc" AS c_10, "g_0"."mcode" AS c_11, "g_0"."src" AS c_12, "g_0"."uid" AS c_13 FROM ( select "__objecttype__","__objecttypexmlPK__", tcode, version , ctcode , sid , splx , ctod , stod , mcode , evtpuuid , uid , uname, job , src , adm , Commandstr, oprcode , vio1code , vio2code , desc , cdesc FROM ( select u."__objecttype__", u."__objecttypexmlPK__", u.tcode, u.version , u.ctcode , u.sid , u.splx , u.ctod , u.stod , u.mcode , u.evtpuuid , u.uid , u.uname, u.job , u.src , u.adm , u.Commandstr, u.oprcode , u.vio1code , u.vio2code, tp.desc, ct.cdesc FROM ( select 'VADMTAB' as "__objecttype__",getPKColumnsFor('VADMTAB','sid',a.sid, 'stod', a.stod,'ctod',a.ctod) as "__objecttypexmlPK__", a.tcode , a.version , a.ctcode , a.sid , a.splx , a.ctod , a.stod , a.mcode , a.evtpuuid , a.uid , a.uname, a.job , a.src , a.adm , a.admacctstr as Commandstr, a.oprcode , a.vio1code , a.vio2code from VADMTAB a UNION ALL select 'VAPLYTAB' as "__objecttype__",getPKColumnsFor('VAPLYTAB','sid',p.sid, 'stod', p.stod,'ctod',p.ctod) as "__objecttypexmlPK__", p.tcode , p.version , p.ctcode , p.sid , p.splx , p.ctod , p.stod , p.mcode , p.evtpuuid , p.uid , p.uname, p.job , p.src , p.adm , p.admpolicystr as Commandstr, p.oprcode , p.vio1code , p.vio2code from VAPLYTAB p UNION ALL select 'VOACSTAB' as "__objecttype__",getPKColumnsFor('VOACSTAB','sid',o.sid, 'stod', o.stod,'ctod',o.ctod) as "__objecttypexmlPK__", o.tcode , o.version , o.ctcode , o.sid , o.splx , o.ctod , o.stod , o.mcode , o.evtpuuid , o.uid , o.uname, o.job , o.src , o.usrprogram as adm , ' ' as Commandstr, o.acccode as oprcode , o.vio1code , o.vio2code from VOACSTAB o UNION ALL select 'VSCNTTAB' as "__objecttype__", getPKColumnsFor('VSCNTTAB','sid',c.sid,'stod', c.stod,'ctod',c.ctod) as "__objecttypexmlPK__",c.tcode , c.version , c.ctcode , c.sid , c.splx , c.ctod , c.stod , c.mcode , c.evtpuuid , c.uid , c.uname, c.job , c.src , ' ' as adm , c.admsecstr as Commandstr, 0 as oprcode , 0 as vio1code , 0 as vio2code from VSCNTTAB c UNION ALL select 'VAMISTAB' as "__objecttype__",getPKColumnsFor('VAMISTAB','sid',m.sid, 'stod', m.stod,'ctod',m.ctod) as "__objecttypexmlPK__", m.tcode , m.version , m.ctcode , m.sid , m.splx , m.ctod , m.stod , m.mcode , m.evtpuuid , m.uid , m.uname, m.job , m.src , ' ' as adm , m.admmiscstr as Commandstr, m.oprcode , m.vio1code , m.vio2code from VAMISTAB m UNION ALL select 'VSACSTAB' as "__objecttype__",getPKColumnsFor('VSACSTAB','sid',s.sid, 'stod', s.stod,'ctod',s.ctod) as "__objecttypexmlPK__", s.tcode , s.version , s.ctcode , s.sid , s.splx , s.ctod , s.stod , s.mcode , s.evtpuuid , s.uid , s.uname, s.job , s.src , s.usrprogram as adm , ' ' as Commandstr, 0 as oprcode , s.inf1code as vio1code , s.inf2code as vio2code from VSACSTAB s UNION ALL select 'VUFLTAB' as "__objecttype__",getPKColumnsFor('VUFLTAB','sid',f.sid, 'stod', f.stod,'ctod',f.ctod) as "__objecttypexmlPK__", f.tcode , f.version , f.ctcode , f.sid , f.splx , f.ctod , f.stod , f.mcode , f.evtpuuid , f.uid , f.uname, f.job , f.src , f.usrgroup as adm , ' ' as Commandstr, f.acccode as oprcode , f.esmrc as vio1code, f.esmreason as vio2code from VUFLTAB f UNION ALL select 'VUSSUSTAB' as "__objecttype__",getPKColumnsFor('VUSSUSTAB','sid',r.sid, 'stod', r.stod,'ctod',r.ctod) as "__objecttypexmlPK__", r.tcode , r.version , r.ctcode , r.sid , r.splx , r.ctod , r.stod , r.mcode , r.evtpuuid , r.uid , r.uname, r.job , r.src , r.usruser as adm , ' ' as Commandstr, r.ussfunction as oprcode , r.esmrc as vio1code , r.esmreason as vio2code from VUSSUSTAB r ) as u left join TAB1 tp on (tp.tcode=u.tcode) left join TAB2 ct on (ct.ctcode=u.ctcode) ) AS t ) AS "g_0" WHERE "g_0"."adm" LIKE '%HOGWA%' AND "g_0"."job" LIKE '%FTP%' AND convert("g_0"."ctod", string) >= '2009-06-01 21:00:00.0' AND convert("g_0"."ctod", string) <= '2012-06-11 21:30:00.0' LIMIT 10] </definition> </view> And if I query the above view with the statement: SELECT TABNAME."sid", TABNAME."ctod", TABNAME."stod", TABNAME."job", TABNAME."COMMANDSTR", TABNAME."splx", TABNAME."__objecttype__", TABNAME."adm", TABNAME."desc", TABNAME."uname", (select count(*) from (select * from notes.GETNOTES where OBJECT_PKEY = xmlserialize(xmlelement("SCH.MYVIEW", XMLATTRIBUTES(convert(TABNAME."sid", String) as "sid",convert(TABNAME."ctod", String) as "ctod",convert(TABNAME."stod", String) as "stod")) as String)) as foo) as cnt, TABNAME."cdesc", TABNAME."mcode", TABNAME."src", TABNAME."uid" FROM "SCH.MYVIEW" as TABNAME WHERE ((TABNAME."adm" like '%TXT01%' AND TABNAME."job" like '%FTP4%') AND (TABNAME."ctod" Between '2009-06-01 21:00:00.0' And '2012-06-11 21:30:00.0')) LIMIT 10 I am getting IndexOutOfBoundsException in the logs: [22:56:52.058][info][talledLocalContainer] 11 Jun 2012 22:56:52,058 IST INFO [CONNECTOR] MyExecutionFactory Commit=true;DatabaseProductName=MyDBName;DatabaseProductVersion=09015;DriverMajorVersion=4;DriverMajorVersion=12;DriverName=Server Driver for JDBC and SQLJ;DriverVersion=4.12.55;IsolationLevel=2 [22:59:03.940][info][talledLocalContainer] 11 Jun 2012 22:59:03,920 IST ERROR [PROCESSOR] Unexpected exception for request d51bqOSYXDYA.4 [22:59:03.940][info][talledLocalContainer] java.lang.IndexOutOfBoundsException: Index: 0, Size: 0 [22:59:03.940][info][talledLocalContainer] at java.util.ArrayList.rangeCheck(ArrayList.java:604) [22:59:03.940][info][talledLocalContainer] at java.util.ArrayList.get(ArrayList.java:382) [22:59:03.940][info][talledLocalContainer] at org.teiid.query.optimizer.relational.rules.RuleCollapseSource.simplifyFromClause(RuleCollapseSource.java:506) [22:59:03.940][info][talledLocalContainer] at org.teiid.query.optimizer.relational.rules.RuleCollapseSource.createQuery(RuleCollapseSource.java:234) [22:59:03.940][info][talledLocalContainer] at org.teiid.query.optimizer.relational.rules.RuleCollapseSource.createQuery(RuleCollapseSource.java:209) [22:59:03.940][info][talledLocalContainer] at org.teiid.query.optimizer.relational.rules.RuleCollapseSource.createQuery(RuleCollapseSource.java:209) [22:59:03.940][info][talledLocalContainer] at org.teiid.query.optimizer.relational.rules.RuleCollapseSource.createQuery(RuleCollapseSource.java:209) [22:59:03.940][info][talledLocalContainer] at org.teiid.query.optimizer.relational.rules.RuleCollapseSource.createQuery(RuleCollapseSource.java:209) [22:59:03.940][info][talledLocalContainer] at org.teiid.query.optimizer.relational.rules.RuleCollapseSource.createQuery(RuleCollapseSource.java:209) [22:59:03.940][info][talledLocalContainer] at org.teiid.query.optimizer.relational.rules.RuleCollapseSource.buildQuery(RuleCollapseSource.java:359) [22:59:03.940][info][talledLocalContainer] at org.teiid.query.optimizer.relational.rules.RuleCollapseSource.buildQuery(RuleCollapseSource.java:323) [22:59:03.940][info][talledLocalContainer] at org.teiid.query.optimizer.relational.rules.RuleCollapseSource.buildQuery(RuleCollapseSource.java:323) [22:59:03.940][info][talledLocalContainer] at org.teiid.query.optimizer.relational.rules.RuleCollapseSource.buildQuery(RuleCollapseSource.java:373) [22:59:03.941][info][talledLocalContainer] at org.teiid.query.optimizer.relational.rules.RuleCollapseSource.buildQuery(RuleCollapseSource.java:373) [22:59:03.941][info][talledLocalContainer] at org.teiid.query.optimizer.relational.rules.RuleCollapseSource.buildQuery(RuleCollapseSource.java:373) [22:59:03.941][info][talledLocalContainer] at org.teiid.query.optimizer.relational.rules.RuleCollapseSource.createQuery(RuleCollapseSource.java:228) [22:59:03.941][info][talledLocalContainer] at org.teiid.query.optimizer.relational.rules.RuleCollapseSource.execute(RuleCollapseSource.java:99) [22:59:03.941][info][talledLocalContainer] at org.teiid.query.optimizer.relational.RelationalPlanner.executeRules(RelationalPlanner.java:455) [22:59:03.941][info][talledLocalContainer] at org.teiid.query.optimizer.relational.RelationalPlanner.optimize(RelationalPlanner.java:199) [22:59:03.941][info][talledLocalContainer] at org.teiid.query.optimizer.QueryOptimizer.optimizePlan(QueryOptimizer.java:190) [22:59:03.941][info][talledLocalContainer] at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:436) [22:59:03.941][info][talledLocalContainer] at org.teiid.dqp.internal.process.PreparedStatementRequest.generatePlan(PreparedStatementRequest.java:138) [22:59:03.941][info][talledLocalContainer] at org.teiid.dqp.internal.process.Request.processRequest(Request.java:459) [22:59:03.941][info][talledLocalContainer] at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:516) [22:59:03.941][info][talledLocalContainer] at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:276) [22:59:03.941][info][talledLocalContainer] at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:49) [22:59:03.941][info][talledLocalContainer] at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:215) [22:59:03.941][info][talledLocalContainer] at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:232) [22:59:03.941][info][talledLocalContainer] at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:118) [22:59:03.941][info][talledLocalContainer] at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:288) [22:59:03.941][info][talledLocalContainer] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110) [22:59:03.941][info][talledLocalContainer] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603) [22:59:03.941][info][talledLocalContainer] at java.lang.Thread.run(Thread.java:722) [22:59:03.964][info][talledLocalContainer] 11 Jun 2012 22:59:03,961 IST WARN [CONNECTOR] Connector worker process failed for atomic-request=bPMKtejy9HxW.0.7.59 [22:59:03.964][info][talledLocalContainer] [JDBCExecutionException] 0: Error Code:0 Message:'Index: 0, Size: 0' error executing statement(s): [Prepared Values: [] SQL: SELECT "g_0"."sid" AS c_0, "g_0"."ctod" AS c_1, "g_0"."stod" AS c_2, "g_0"."job" AS c_3, "g_0"."COMMANDSTR" AS c_4, "g_0"."splex" AS c_5, "g_0"."__objecttype__" AS c_6, "g_0"."adm" AS c_7, "g_0"."desc" AS c_8, "g_0"."uname" AS c_9, "g_0"."cdesc" AS c_10, "g_0"."mcode" AS c_11, "g_0"."src" AS c_12, "g_0"."uid" AS c_13 FROM ( select "__objecttype__","__objecttypexmlPK__", tcode, version , ccode , sid , splex , ctod , stod , -------------- etc.. Please have a look into this issue.