[teiid-issues] [JBoss JIRA] (TEIID-2071) Getting IndexOutOfBoundsException while querying to a view that has a select statement with multiple union statements
Rajasekhar Kota (JIRA)
jira-events at lists.jboss.org
Mon Jun 11 14:55:04 EDT 2012
Rajasekhar Kota created TEIID-2071:
--------------------------------------
Summary: 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.
--
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
More information about the teiid-issues
mailing list