[JBoss JIRA] (TEIID-3477) NPE when trying to use OData service with void functions
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-3477?page=com.atlassian.jira.plugin... ]
Ramesh Reddy resolved TEIID-3477.
---------------------------------
Labels: Beta2 (was: )
Fix Version/s: 8.11
Resolution: Done
Fixed. Now meta processor should allow void functions.
> NPE when trying to use OData service with void functions
> --------------------------------------------------------
>
> Key: TEIID-3477
> URL: https://issues.jboss.org/browse/TEIID-3477
> Project: Teiid
> Issue Type: Bug
> Components: OData
> Affects Versions: 8.10.1
> Reporter: Jaime Meritt
> Assignee: Ramesh Reddy
> Priority: Minor
> Labels: Beta2
> Fix For: 8.11
>
>
> I have Teiid configured to work with OData sources and tested it successfully with the Northwind v2 endpoint. However, when trying to use my custom OData service I get an NPE during the metadata import process. After some debugging I have tracked this down to a null returnType coming back from the following code in the ODataMetadataProcessor which will see a null for void functions. I looked for a translator property to suppress import of procedures to work around it but have yet to find anything that will help so I think this requires a code change to workaround.
>
> // add return type
> EdmType returnType = function.getReturnType();
> if (returnType.isSimple()) {
> mf.addProcedureParameter(...
> }
>
> Appreciate any help you can provide. Thanks, Jaime
> See stack trace below
> Caused by: java.lang.NullPointerException
> at org.teiid.translator.odata.ODataMetadataProcessor.addFunctionImportAsProcedure(ODataMetadataProcessor.java:396)
> at org.teiid.translator.odata.ODataMetadataProcessor.getMetadata(ODataMetadataProcessor.java:119)
> at org.teiid.translator.odata.ODataMetadataProcessor.process(ODataMetadataProcessor.java:92)
> at org.teiid.translator.odata.ODataExecutionFactory.getMetadata(ODataExecutionFactory.java:112)
> at org.teiid.translator.odata.ODataExecutionFactory.getMetadata(ODataExecutionFactory.java:53)
> at org.teiid.query.metadata.NativeMetadataRepository.getMetadata(NativeMetadataRepository.java:83)
> at org.teiid.query.metadata.NativeMetadataRepository.loadMetadata(NativeMetadataRepository.java:60)
> at org.teiid.query.metadata.ChainingMetadataRepository.loadMetadata(ChainingMetadataRepository.java:55)
> at org.teiid.runtime.EmbeddedServer.loadMetadata(EmbeddedServer.java:762)
> at org.teiid.runtime.AbstractVDBDeployer.loadMetadata(AbstractVDBDeployer.java:168)
> at org.teiid.runtime.EmbeddedServer.deployVDB(EmbeddedServer.java:712)
> at org.teiid.runtime.EmbeddedServer.deployVDB(EmbeddedServer.java:653)
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
10 years, 11 months
[JBoss JIRA] (TEIID-3476) Order by on query with union or union all orders parts independently
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3476?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-3476:
----------------------------------
Fix Version/s: 8.11
Affects Version/s: 8.4
(was: 8.9.1)
Component/s: Query Engine
This is actually a problem with the creation of the canonical plan. An ordering over a set query with a group by does not have it's symbols modified when a view is inserted for the affect of the grouping.
> Order by on query with union or union all orders parts independently
> --------------------------------------------------------------------
>
> Key: TEIID-3476
> URL: https://issues.jboss.org/browse/TEIID-3476
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.4
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Fix For: 8.11
>
> Attachments: debugplan_orderbyproblem.txt
>
>
> I have this query that consists of a UNION ALL of 2 select queries that have a group by statement. I'd like to sort the whole result on the column aliased admtime. However this results in an IndexOutOfBoundsException.
> select gd.patientid as admissionid,gd.PatGroup as patgroup,wr.Abbreviation as ward,gd.AdmissionTime as admtime, dd.DischargeTime as distime, 'wh' as origin
> from wh_P_GeneralData gd
> join wh_P_DischargeData dd on gd.patientid = dd.patientid
> join prod_S_WardRef wr on wr.WardID = gd.WardID
> where gd.patientid in (
> select patientid
> from wh_P_PharmaRec pr
> where pr.PharmaID = 1002463 and
> bitand(pr.Status, 2) <> 2
> group by patientid
> )
> group by gd.patientid,gd.PatGroup,wr.Abbreviation,gd.AdmissionTime, dd.DischargeTime
> UNION ALL
> select gd.patientid,gd.PatGroup,wr.Abbreviation,gd.AdmissionTime, dd.DischargeTime , 'prod'
> from prod_P_GeneralData gd
> join prod_P_DischargeData dd on gd.patientid = dd.patientid
> join prod_S_WardRef wr on wr.WardID = gd.WardID
> where gd.patientid in (
> select patientid
> from prod_P_PharmaRec pr
> where pr.PharmaID = 1002463 and
> bitand(pr.Status, 2) <> 2
> group by patientid
> )
> and gd.Status >= 4 and gd.status <> 5
> group by gd.patientid,gd.PatGroup,wr.Abbreviation,gd.AdmissionTime, dd.DischargeTime
> order by admtime
> java.lang.IndexOutOfBoundsException: Index: 6, Size: 6
> at java.util.ArrayList.rangeCheck(ArrayList.java:604) [rt.jar:1.7.0_25]
> at java.util.ArrayList.get(ArrayList.java:382) [rt.jar:1.7.0_25]
> at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.setColStatEstimates(NewCalculateCostUtil.java:411) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.setCardinalityEstimate(NewCalculateCostUtil.java:313) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.estimateSetOpCost(NewCalculateCostUtil.java:252) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.computeNodeCost(NewCalculateCostUtil.java:204) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.updateCardinality(NewCalculateCostUtil.java:136) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.updateCardinality(NewCalculateCostUtil.java:133) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.updateCardinality(NewCalculateCostUtil.java:133) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.computeCostForTree(NewCalculateCostUtil.java:122) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.query.optimizer.relational.rules.RuleCalculateCost.execute(RuleCalculateCost.java:50) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.query.optimizer.relational.RelationalPlanner.executeRules(RelationalPlanner.java:739) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.query.optimizer.relational.RelationalPlanner.optimize(RelationalPlanner.java:221) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.query.optimizer.QueryOptimizer.optimizePlan(QueryOptimizer.java:159) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:411) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.dqp.internal.process.Request.processRequest(Request.java:438) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:614) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:320) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:259) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) [teiid-engine-8.9.1.jar:8.9.1]
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_25]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_25]
> at java.lang.Thread.run(Thread.java:724) [rt.jar:1.7.0_25]
> I then changed the query to this form:
> select *
> from (
> previous query
> ) v
> order by admtime
> In this case there is no error but the ordering seemed to have happened for each part of the select statement independently. So the collection of columns with origin 'prod' is sorted and the collection of rows with 'wh' as origin are sorted independently and then unioned toghether.
> I've attached the debug log
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
10 years, 11 months
[JBoss JIRA] (TEIID-3476) Order by on query with union or union all orders parts independently
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3476?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-3476.
-----------------------------------
Resolution: Done
Updated the plan creation to modify the parent order by as well.
> Order by on query with union or union all orders parts independently
> --------------------------------------------------------------------
>
> Key: TEIID-3476
> URL: https://issues.jboss.org/browse/TEIID-3476
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.4
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Fix For: 8.11
>
> Attachments: debugplan_orderbyproblem.txt
>
>
> I have this query that consists of a UNION ALL of 2 select queries that have a group by statement. I'd like to sort the whole result on the column aliased admtime. However this results in an IndexOutOfBoundsException.
> select gd.patientid as admissionid,gd.PatGroup as patgroup,wr.Abbreviation as ward,gd.AdmissionTime as admtime, dd.DischargeTime as distime, 'wh' as origin
> from wh_P_GeneralData gd
> join wh_P_DischargeData dd on gd.patientid = dd.patientid
> join prod_S_WardRef wr on wr.WardID = gd.WardID
> where gd.patientid in (
> select patientid
> from wh_P_PharmaRec pr
> where pr.PharmaID = 1002463 and
> bitand(pr.Status, 2) <> 2
> group by patientid
> )
> group by gd.patientid,gd.PatGroup,wr.Abbreviation,gd.AdmissionTime, dd.DischargeTime
> UNION ALL
> select gd.patientid,gd.PatGroup,wr.Abbreviation,gd.AdmissionTime, dd.DischargeTime , 'prod'
> from prod_P_GeneralData gd
> join prod_P_DischargeData dd on gd.patientid = dd.patientid
> join prod_S_WardRef wr on wr.WardID = gd.WardID
> where gd.patientid in (
> select patientid
> from prod_P_PharmaRec pr
> where pr.PharmaID = 1002463 and
> bitand(pr.Status, 2) <> 2
> group by patientid
> )
> and gd.Status >= 4 and gd.status <> 5
> group by gd.patientid,gd.PatGroup,wr.Abbreviation,gd.AdmissionTime, dd.DischargeTime
> order by admtime
> java.lang.IndexOutOfBoundsException: Index: 6, Size: 6
> at java.util.ArrayList.rangeCheck(ArrayList.java:604) [rt.jar:1.7.0_25]
> at java.util.ArrayList.get(ArrayList.java:382) [rt.jar:1.7.0_25]
> at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.setColStatEstimates(NewCalculateCostUtil.java:411) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.setCardinalityEstimate(NewCalculateCostUtil.java:313) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.estimateSetOpCost(NewCalculateCostUtil.java:252) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.computeNodeCost(NewCalculateCostUtil.java:204) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.updateCardinality(NewCalculateCostUtil.java:136) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.updateCardinality(NewCalculateCostUtil.java:133) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.updateCardinality(NewCalculateCostUtil.java:133) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.computeCostForTree(NewCalculateCostUtil.java:122) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.query.optimizer.relational.rules.RuleCalculateCost.execute(RuleCalculateCost.java:50) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.query.optimizer.relational.RelationalPlanner.executeRules(RelationalPlanner.java:739) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.query.optimizer.relational.RelationalPlanner.optimize(RelationalPlanner.java:221) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.query.optimizer.QueryOptimizer.optimizePlan(QueryOptimizer.java:159) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:411) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.dqp.internal.process.Request.processRequest(Request.java:438) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:614) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:320) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:259) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) [teiid-engine-8.9.1.jar:8.9.1]
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_25]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_25]
> at java.lang.Thread.run(Thread.java:724) [rt.jar:1.7.0_25]
> I then changed the query to this form:
> select *
> from (
> previous query
> ) v
> order by admtime
> In this case there is no error but the ordering seemed to have happened for each part of the select statement independently. So the collection of columns with origin 'prod' is sorted and the collection of rows with 'wh' as origin are sorted independently and then unioned toghether.
> I've attached the debug log
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
10 years, 11 months
[JBoss JIRA] (TEIID-3477) NPE when trying to use OData service with void functions
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-3477?page=com.atlassian.jira.plugin... ]
Ramesh Reddy reassigned TEIID-3477:
-----------------------------------
Assignee: Ramesh Reddy (was: Steven Hawkins)
> NPE when trying to use OData service with void functions
> --------------------------------------------------------
>
> Key: TEIID-3477
> URL: https://issues.jboss.org/browse/TEIID-3477
> Project: Teiid
> Issue Type: Bug
> Components: OData
> Affects Versions: 8.10.1
> Reporter: Jaime Meritt
> Assignee: Ramesh Reddy
> Priority: Minor
>
> I have Teiid configured to work with OData sources and tested it successfully with the Northwind v2 endpoint. However, when trying to use my custom OData service I get an NPE during the metadata import process. After some debugging I have tracked this down to a null returnType coming back from the following code in the ODataMetadataProcessor which will see a null for void functions. I looked for a translator property to suppress import of procedures to work around it but have yet to find anything that will help so I think this requires a code change to workaround.
>
> // add return type
> EdmType returnType = function.getReturnType();
> if (returnType.isSimple()) {
> mf.addProcedureParameter(...
> }
>
> Appreciate any help you can provide. Thanks, Jaime
> See stack trace below
> Caused by: java.lang.NullPointerException
> at org.teiid.translator.odata.ODataMetadataProcessor.addFunctionImportAsProcedure(ODataMetadataProcessor.java:396)
> at org.teiid.translator.odata.ODataMetadataProcessor.getMetadata(ODataMetadataProcessor.java:119)
> at org.teiid.translator.odata.ODataMetadataProcessor.process(ODataMetadataProcessor.java:92)
> at org.teiid.translator.odata.ODataExecutionFactory.getMetadata(ODataExecutionFactory.java:112)
> at org.teiid.translator.odata.ODataExecutionFactory.getMetadata(ODataExecutionFactory.java:53)
> at org.teiid.query.metadata.NativeMetadataRepository.getMetadata(NativeMetadataRepository.java:83)
> at org.teiid.query.metadata.NativeMetadataRepository.loadMetadata(NativeMetadataRepository.java:60)
> at org.teiid.query.metadata.ChainingMetadataRepository.loadMetadata(ChainingMetadataRepository.java:55)
> at org.teiid.runtime.EmbeddedServer.loadMetadata(EmbeddedServer.java:762)
> at org.teiid.runtime.AbstractVDBDeployer.loadMetadata(AbstractVDBDeployer.java:168)
> at org.teiid.runtime.EmbeddedServer.deployVDB(EmbeddedServer.java:712)
> at org.teiid.runtime.EmbeddedServer.deployVDB(EmbeddedServer.java:653)
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
10 years, 11 months
[JBoss JIRA] (TEIID-2973) OData 4 support with Olingo
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-2973?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-2973:
-------------------------------------
Added most features of OData V4 support based on Olingo version 4.0.0-beta-03. Some of omissions are
1) $expand is supported, but nested $expand and $expand of multiple entities is not supported currently
2) No $search support as it is not applicable in JDBC, but may be in general service sense, need to take this as separate topic
3) No Geo type support yet, as Olingo library does not support it
4) No delta data support
5) No deep insert yet
6) $batch is supported, but references using $1, $2 not yet supported
7) Edm.Stream support is available (spec does not call for multi-part for this). The REST procedures created in Teiid will now behave same way over OData !
8) Functions and Actions are supported. When "updateCount = 0" and has return type is defined, a procedure is treated as Function Import, otherwise it is a Action Import. There are no entity based actions and functions in Teiid, thus no composable functions and actions either
9) No Async support (will be a while for this)
10) No annotation support
11) No ATOM payload support due to lack of the feature in the Olingo library (hopefully by the next revision)
12) No "metadata=full" support yet due to lack of feature in the Olingo library. This is work in progress, will be there in next revision.
13) No $crossjoin support yet
14) no $root support yet
15) The library is also designed to work as one can create service for each individual VDB file, however there is no support structure yet. i.e scripts or gui to build one with different security options etc.
I will be working on some of the above for 8.11, rest may get pushed to next releases.
> OData 4 support with Olingo
> ---------------------------
>
> Key: TEIID-2973
> URL: https://issues.jboss.org/browse/TEIID-2973
> Project: Teiid
> Issue Type: Task
> Components: Misc. Connectors, OData
> Reporter: Steven Hawkins
> Assignee: Ramesh Reddy
> Fix For: 8.11
>
>
> Whenever an olingo final is available for odata 3/4 we'll want to utilize that. The best guess now is that this will happen in the 8.9 timeframe. Additional support can then be added for later odata features, which will have subtasks as necessary.
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
10 years, 11 months
[JBoss JIRA] (TEIID-3477) NPE when trying to use OData service with void functions
by Jaime Meritt (JIRA)
Jaime Meritt created TEIID-3477:
-----------------------------------
Summary: NPE when trying to use OData service with void functions
Key: TEIID-3477
URL: https://issues.jboss.org/browse/TEIID-3477
Project: Teiid
Issue Type: Bug
Components: OData
Affects Versions: 8.10.1
Reporter: Jaime Meritt
Assignee: Steven Hawkins
Priority: Minor
I have Teiid configured to work with OData sources and tested it successfully with the Northwind v2 endpoint. However, when trying to use my custom OData service I get an NPE during the metadata import process. After some debugging I have tracked this down to a null returnType coming back from the following code in the ODataMetadataProcessor which will see a null for void functions. I looked for a translator property to suppress import of procedures to work around it but have yet to find anything that will help so I think this requires a code change to workaround.
// add return type
EdmType returnType = function.getReturnType();
if (returnType.isSimple()) {
mf.addProcedureParameter(...
}
Appreciate any help you can provide. Thanks, Jaime
See stack trace below
Caused by: java.lang.NullPointerException
at org.teiid.translator.odata.ODataMetadataProcessor.addFunctionImportAsProcedure(ODataMetadataProcessor.java:396)
at org.teiid.translator.odata.ODataMetadataProcessor.getMetadata(ODataMetadataProcessor.java:119)
at org.teiid.translator.odata.ODataMetadataProcessor.process(ODataMetadataProcessor.java:92)
at org.teiid.translator.odata.ODataExecutionFactory.getMetadata(ODataExecutionFactory.java:112)
at org.teiid.translator.odata.ODataExecutionFactory.getMetadata(ODataExecutionFactory.java:53)
at org.teiid.query.metadata.NativeMetadataRepository.getMetadata(NativeMetadataRepository.java:83)
at org.teiid.query.metadata.NativeMetadataRepository.loadMetadata(NativeMetadataRepository.java:60)
at org.teiid.query.metadata.ChainingMetadataRepository.loadMetadata(ChainingMetadataRepository.java:55)
at org.teiid.runtime.EmbeddedServer.loadMetadata(EmbeddedServer.java:762)
at org.teiid.runtime.AbstractVDBDeployer.loadMetadata(AbstractVDBDeployer.java:168)
at org.teiid.runtime.EmbeddedServer.deployVDB(EmbeddedServer.java:712)
at org.teiid.runtime.EmbeddedServer.deployVDB(EmbeddedServer.java:653)
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
10 years, 11 months
[JBoss JIRA] (TEIID-3476) Order by on query with union or union all orders parts independently
by Bram Gadeyne (JIRA)
[ https://issues.jboss.org/browse/TEIID-3476?page=com.atlassian.jira.plugin... ]
Bram Gadeyne updated TEIID-3476:
--------------------------------
Attachment: debugplan_orderbyproblem.txt
debugplan
> Order by on query with union or union all orders parts independently
> --------------------------------------------------------------------
>
> Key: TEIID-3476
> URL: https://issues.jboss.org/browse/TEIID-3476
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 8.9.1
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Attachments: debugplan_orderbyproblem.txt
>
>
> I have this query that consists of a UNION ALL of 2 select queries that have a group by statement. I'd like to sort the whole result on the column aliased admtime. However this results in an IndexOutOfBoundsException.
> select gd.patientid as admissionid,gd.PatGroup as patgroup,wr.Abbreviation as ward,gd.AdmissionTime as admtime, dd.DischargeTime as distime, 'wh' as origin
> from wh_P_GeneralData gd
> join wh_P_DischargeData dd on gd.patientid = dd.patientid
> join prod_S_WardRef wr on wr.WardID = gd.WardID
> where gd.patientid in (
> select patientid
> from wh_P_PharmaRec pr
> where pr.PharmaID = 1002463 and
> bitand(pr.Status, 2) <> 2
> group by patientid
> )
> group by gd.patientid,gd.PatGroup,wr.Abbreviation,gd.AdmissionTime, dd.DischargeTime
> UNION ALL
> select gd.patientid,gd.PatGroup,wr.Abbreviation,gd.AdmissionTime, dd.DischargeTime , 'prod'
> from prod_P_GeneralData gd
> join prod_P_DischargeData dd on gd.patientid = dd.patientid
> join prod_S_WardRef wr on wr.WardID = gd.WardID
> where gd.patientid in (
> select patientid
> from prod_P_PharmaRec pr
> where pr.PharmaID = 1002463 and
> bitand(pr.Status, 2) <> 2
> group by patientid
> )
> and gd.Status >= 4 and gd.status <> 5
> group by gd.patientid,gd.PatGroup,wr.Abbreviation,gd.AdmissionTime, dd.DischargeTime
> order by admtime
> java.lang.IndexOutOfBoundsException: Index: 6, Size: 6
> at java.util.ArrayList.rangeCheck(ArrayList.java:604) [rt.jar:1.7.0_25]
> at java.util.ArrayList.get(ArrayList.java:382) [rt.jar:1.7.0_25]
> at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.setColStatEstimates(NewCalculateCostUtil.java:411) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.setCardinalityEstimate(NewCalculateCostUtil.java:313) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.estimateSetOpCost(NewCalculateCostUtil.java:252) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.computeNodeCost(NewCalculateCostUtil.java:204) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.updateCardinality(NewCalculateCostUtil.java:136) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.updateCardinality(NewCalculateCostUtil.java:133) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.updateCardinality(NewCalculateCostUtil.java:133) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.computeCostForTree(NewCalculateCostUtil.java:122) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.query.optimizer.relational.rules.RuleCalculateCost.execute(RuleCalculateCost.java:50) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.query.optimizer.relational.RelationalPlanner.executeRules(RelationalPlanner.java:739) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.query.optimizer.relational.RelationalPlanner.optimize(RelationalPlanner.java:221) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.query.optimizer.QueryOptimizer.optimizePlan(QueryOptimizer.java:159) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:411) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.dqp.internal.process.Request.processRequest(Request.java:438) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:614) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:320) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:259) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.9.1.jar:8.9.1]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) [teiid-engine-8.9.1.jar:8.9.1]
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_25]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_25]
> at java.lang.Thread.run(Thread.java:724) [rt.jar:1.7.0_25]
> I then changed the query to this form:
> select *
> from (
> previous query
> ) v
> order by admtime
> In this case there is no error but the ordering seemed to have happened for each part of the select statement independently. So the collection of columns with origin 'prod' is sorted and the collection of rows with 'wh' as origin are sorted independently and then unioned toghether.
> I've attached the debug log
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
10 years, 11 months
[JBoss JIRA] (TEIID-3476) Order by on query with union or union all orders parts independently
by Bram Gadeyne (JIRA)
Bram Gadeyne created TEIID-3476:
-----------------------------------
Summary: Order by on query with union or union all orders parts independently
Key: TEIID-3476
URL: https://issues.jboss.org/browse/TEIID-3476
Project: Teiid
Issue Type: Bug
Affects Versions: 8.9.1
Reporter: Bram Gadeyne
Assignee: Steven Hawkins
I have this query that consists of a UNION ALL of 2 select queries that have a group by statement. I'd like to sort the whole result on the column aliased admtime. However this results in an IndexOutOfBoundsException.
select gd.patientid as admissionid,gd.PatGroup as patgroup,wr.Abbreviation as ward,gd.AdmissionTime as admtime, dd.DischargeTime as distime, 'wh' as origin
from wh_P_GeneralData gd
join wh_P_DischargeData dd on gd.patientid = dd.patientid
join prod_S_WardRef wr on wr.WardID = gd.WardID
where gd.patientid in (
select patientid
from wh_P_PharmaRec pr
where pr.PharmaID = 1002463 and
bitand(pr.Status, 2) <> 2
group by patientid
)
group by gd.patientid,gd.PatGroup,wr.Abbreviation,gd.AdmissionTime, dd.DischargeTime
UNION ALL
select gd.patientid,gd.PatGroup,wr.Abbreviation,gd.AdmissionTime, dd.DischargeTime , 'prod'
from prod_P_GeneralData gd
join prod_P_DischargeData dd on gd.patientid = dd.patientid
join prod_S_WardRef wr on wr.WardID = gd.WardID
where gd.patientid in (
select patientid
from prod_P_PharmaRec pr
where pr.PharmaID = 1002463 and
bitand(pr.Status, 2) <> 2
group by patientid
)
and gd.Status >= 4 and gd.status <> 5
group by gd.patientid,gd.PatGroup,wr.Abbreviation,gd.AdmissionTime, dd.DischargeTime
order by admtime
java.lang.IndexOutOfBoundsException: Index: 6, Size: 6
at java.util.ArrayList.rangeCheck(ArrayList.java:604) [rt.jar:1.7.0_25]
at java.util.ArrayList.get(ArrayList.java:382) [rt.jar:1.7.0_25]
at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.setColStatEstimates(NewCalculateCostUtil.java:411) [teiid-engine-8.9.1.jar:8.9.1]
at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.setCardinalityEstimate(NewCalculateCostUtil.java:313) [teiid-engine-8.9.1.jar:8.9.1]
at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.estimateSetOpCost(NewCalculateCostUtil.java:252) [teiid-engine-8.9.1.jar:8.9.1]
at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.computeNodeCost(NewCalculateCostUtil.java:204) [teiid-engine-8.9.1.jar:8.9.1]
at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.updateCardinality(NewCalculateCostUtil.java:136) [teiid-engine-8.9.1.jar:8.9.1]
at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.updateCardinality(NewCalculateCostUtil.java:133) [teiid-engine-8.9.1.jar:8.9.1]
at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.updateCardinality(NewCalculateCostUtil.java:133) [teiid-engine-8.9.1.jar:8.9.1]
at org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil.computeCostForTree(NewCalculateCostUtil.java:122) [teiid-engine-8.9.1.jar:8.9.1]
at org.teiid.query.optimizer.relational.rules.RuleCalculateCost.execute(RuleCalculateCost.java:50) [teiid-engine-8.9.1.jar:8.9.1]
at org.teiid.query.optimizer.relational.RelationalPlanner.executeRules(RelationalPlanner.java:739) [teiid-engine-8.9.1.jar:8.9.1]
at org.teiid.query.optimizer.relational.RelationalPlanner.optimize(RelationalPlanner.java:221) [teiid-engine-8.9.1.jar:8.9.1]
at org.teiid.query.optimizer.QueryOptimizer.optimizePlan(QueryOptimizer.java:159) [teiid-engine-8.9.1.jar:8.9.1]
at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:411) [teiid-engine-8.9.1.jar:8.9.1]
at org.teiid.dqp.internal.process.Request.processRequest(Request.java:438) [teiid-engine-8.9.1.jar:8.9.1]
at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:614) [teiid-engine-8.9.1.jar:8.9.1]
at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:320) [teiid-engine-8.9.1.jar:8.9.1]
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) [teiid-engine-8.9.1.jar:8.9.1]
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:259) [teiid-engine-8.9.1.jar:8.9.1]
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274) [teiid-engine-8.9.1.jar:8.9.1]
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.9.1.jar:8.9.1]
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) [teiid-engine-8.9.1.jar:8.9.1]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_25]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_25]
at java.lang.Thread.run(Thread.java:724) [rt.jar:1.7.0_25]
I then changed the query to this form:
select *
from (
previous query
) v
order by admtime
In this case there is no error but the ordering seemed to have happened for each part of the select statement independently. So the collection of columns with origin 'prod' is sorted and the collection of rows with 'wh' as origin are sorted independently and then unioned toghether.
I've attached the debug log
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
10 years, 11 months
[JBoss JIRA] (TEIID-3474) Inconsistent results of RIGHT function for different datasources
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3474?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-3474.
-----------------------------------
Resolution: Done
To ensure consistent results pulled right support from oracle (we can workaround further if needed) and older pg. Also updated the logic for db2.
> Inconsistent results of RIGHT function for different datasources
> ----------------------------------------------------------------
>
> Key: TEIID-3474
> URL: https://issues.jboss.org/browse/TEIID-3474
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 7.7
> Environment: OS: Fedora 20
> arch: x86_64
> java: oracle 1.8
> Reporter: Juraj Duráni
> Assignee: Steven Hawkins
> Fix For: 8.11
>
>
> The RIGHT function returns different results for different datasources. Some of those results are inconsistent with definition of the RIGHT(x,y) function.
> =======
> +*Query: SELECT intkey, RIGHT(intkey, 1) FROM table ORDER BY intkey*+
> *Teradata, SQLServer, Sybase, Ingres, MySQL, Oracle (this result is OK):*
> 0 0
> 1 1
> 2 2
> ...
> 10 0
> 11 1
> ...
> 100 0
> 101 1
> *Postgres:*
> 0 0
> 1 1
> 2 2
> ...
> 10 10
> 11 11
> ...
> 100 100
> 101 101
> *DB2:*
> 0 " "
> 1 " "
> 2 " "
> ...
> 10 " "
> 11 " "
> ...
> 100 " "
> 101 " "
> =====
> +*Query:SELECT intkey, RIGHT(intkey, 2) FROM table ORDER BY intkey*+
> *SQLServer, Sybase, Ingress, MySQL, Teradata (this result is OK):*
> 0 0
> 1 1
> ...
> 10 10
> 11 11
> ...
> 100 00
> 101 01
> *Oracle:*
> 0 <null>
> 1 <null>
> 2 <null>
> ...
> 10 10
> 11 11
> ...
> 100 00
> 101 01
> *Postgres:*
> 0 0
> 1 1
> 2 2
> ...
> 10 10
> 11 11
> ...
> 100 100
> 101 101
> *DB2:*
> 0 " "
> 1 " "
> 2 " "
> ...
> 10 " "
> 11 " "
> ...
> 100 " "
> 101 " "
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
10 years, 11 months