[JBoss JIRA] (TEIID-4822) predicate in on clause is not pushed down to view's underlying select statement
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4822?page=com.atlassian.jira.plugin... ]
Steven Hawkins closed TEIID-4822.
---------------------------------
> predicate in on clause is not pushed down to view's underlying select statement
> -------------------------------------------------------------------------------
>
> Key: TEIID-4822
> URL: https://issues.jboss.org/browse/TEIID-4822
> Project: Teiid
> Issue Type: Enhancement
> Affects Versions: 9.1.4
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
>
> Hi,
> I have a view called tv_retrieve_labres that contains the following select statement
> {code:sql}
> SELECT
> f.PatientID AS admissionid, f.Labogroep, f.SampleTime, f.EnterTime, f.varvalue, f.StringValue
> FROM (
> SELECT v.PatientID, v.Labogroep, v.SampleTime, v.EnterTime, v.varvalue, v.StringValue,
> ROW_NUMBER() OVER (PARTITION BY v.PatientID, v.Labogroep, v.SampleTime ORDER BY v.EnterTime DESC) AS rang
> FROM (
> SELECT lcv.PatientID, lm.Labogroep, lcv.SampleTime, lcv.EnterTime, lcv.varvalue, lcv.StringValue,
> ROW_NUMBER() OVER (PARTITION BY lcv.PatientID, lcv.variableid_oud, lcv.ResultID ORDER BY lcv.ResultNo DESC) AS rang
> FROM izisprod.prod_U_P_LabMapping AS lm
> INNER JOIN /*+ MAKEDEP */ izisprod.prod_U_PV_LabCorrectValues AS lcv ON
> lcv.variableid_oud = lm.VariableID AND
> bitand(convert(lcv.Status, integer), 8) = 8 AND
> bitand(convert(lcv.Status, integer), 2) <> 2
> UNION
> SELECT lcv.PatientID, lm.Labogroep, lcv.SampleTime, lcv.EnterTime, lcv.varvalue, lcv.StringValue,
> ROW_NUMBER() OVER (PARTITION BY lcv.PatientID, lcv.variableid_oud, lcv.ResultID ORDER BY lcv.ResultNo DESC) AS rang
> FROM iziswh.wh_U_P_LabMapping AS lm
> INNER JOIN /*+ MAKEDEP */ iziswh.wh_U_PV_LabCorrectValues AS lcv ON
> lcv.variableid_oud = lm.VariableID AND
> bitand(convert(lcv.Status, integer), 8) = 8 AND
> bitand(convert(lcv.Status, integer), 2) <> 2
> ) AS v
> WHERE v.rang = 1
> ) AS f
> WHERE
> f.rang = 1
> {code}
> When I execute the following select statement the value for lrs.Labogroep is not pushed down.
> {code:sql}
> select gd.patientid, lrs.varvalue, lrs.entertime, lrs.sampletime
> from prod_P_GeneralData gd
> join tv_retrieve_labres lrs on
> lrs.admissionid = gd.PatientID
> and lrs.Labogroep = 'pH-type'
> and lrs.varvalue = 1
> where gd.status = 1
> {code}
> Should this normally be working? I'm wondering if it doesn't work because of the union statement in the view.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
8 years, 4 months
[JBoss JIRA] (TEIID-4966) Infinite loop when deploying a malformed vdb
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4966?page=com.atlassian.jira.plugin... ]
Steven Hawkins closed TEIID-4966.
---------------------------------
> Infinite loop when deploying a malformed vdb
> ---------------------------------------------
>
> Key: TEIID-4966
> URL: https://issues.jboss.org/browse/TEIID-4966
> Project: Teiid
> Issue Type: Bug
> Environment: * WildFly 10
> * Teiid Server 9.3.0
> Reporter: Pedro Inácio
> Assignee: Steven Hawkins
>
> Deploy a malformed vdb. Example with closing comment (--) but without a open comment (!--).
> The following error start to appear in logs without end.
> Deploying a correctly formed VDB after does not fix the problem.
> Log extract;
> 2017-06-22 17:46:48,971 INFO [org.jboss.as.server.deployment.scanner] (DeploymentScanner-threads - 1) WFLYDS0035: Scan found countryServiceList-vdb.xml which is not well-formed at lineNumber: 3591, columnNumber: 9. Either the file was incompletely copied at the time of the scanning or it is just wrong.
> .....
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
8 years, 4 months
[JBoss JIRA] (TEIID-4813) Basic support for joins in OData v2
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4813?page=com.atlassian.jira.plugin... ]
Steven Hawkins closed TEIID-4813.
---------------------------------
> Basic support for joins in OData v2
> -----------------------------------
>
> Key: TEIID-4813
> URL: https://issues.jboss.org/browse/TEIID-4813
> Project: Teiid
> Issue Type: Bug
> Components: OData
> Reporter: Damien B
> Assignee: Steven Hawkins
>
> It's half a bug, half a feature request :-)
> We deal with a perticular OData almost v2 server that uses system functions disguised as entitities. Basically, "/A('b')/C" works but not "/C?$filter=foo eq 'b'". In order to have that, we've enabled the joins support on the ODataTranslator, but we still haven't the right query generated. We've traced back the origin to the optimizer:
> * SELECT * FROM a join b ON a.x=b.y WHERE a.x='v'
> * is optimized as SELECT <columns> FROM a join b ON a.x=b.y WHERE a.x='v' AND b.y='v'
> The extra induced condition "b.y = 'v'" messes up with the URL construction. Any idea about how to proceed from there?
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
8 years, 4 months
[JBoss JIRA] (TEIID-4949) Using UNION ALL with NULL in views results in unintended cast to string and errors
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4949?page=com.atlassian.jira.plugin... ]
Steven Hawkins closed TEIID-4949.
---------------------------------
> Using UNION ALL with NULL in views results in unintended cast to string and errors
> ----------------------------------------------------------------------------------
>
> Key: TEIID-4949
> URL: https://issues.jboss.org/browse/TEIID-4949
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 9.0.3
> Environment: teiid-9.0.3 on WildFly Full 9.0.2.Final (WildFly Core 1.0.2.Final)
> Reporter: dalex dalex
> Assignee: Steven Hawkins
> Priority: Blocker
>
> Running the script:
> {code:sql}
> select * from views.v2;;
> {code}
> leads to the following errors:
> {code}
> 2017-06-06 15:27:38,670 ERROR [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue19) JeWUXFUzqsu3 TEIID30019 Unexpected exception for request JeWUXFUzqsu3.7: org.teiid.core.TeiidRuntimeException: TEIID30447 org.teiid.api.exception.query.QueryResolverException: TEIID30082 Expected value of type 'null' but 'null' is of type 'string' and no implicit conversion is available.
> at org.teiid.query.sql.lang.SetQuery.getTypedProjectedSymbols(SetQuery.java:145)
> at org.teiid.query.rewriter.QueryRewriter.correctProjectedTypes(QueryRewriter.java:819)
> at org.teiid.query.rewriter.QueryRewriter.rewriteSetQuery(QueryRewriter.java:832)
> at org.teiid.query.rewriter.QueryRewriter.rewriteCommand(QueryRewriter.java:270)
> at org.teiid.query.rewriter.QueryRewriter.rewrite(QueryRewriter.java:171)
> at org.teiid.query.rewriter.QueryRewriter.rewrite(QueryRewriter.java:175)
> at org.teiid.query.optimizer.relational.RelationalPlanner.resolveVirtualGroup(RelationalPlanner.java:1992)
> at org.teiid.query.optimizer.relational.RelationalPlanner.buildTree(RelationalPlanner.java:1383)
> at org.teiid.query.optimizer.relational.RelationalPlanner.createQueryPlan(RelationalPlanner.java:1270)
> at org.teiid.query.optimizer.relational.RelationalPlanner.createQueryPlan(RelationalPlanner.java:1221)
> at org.teiid.query.optimizer.relational.RelationalPlanner.generatePlan(RelationalPlanner.java:954)
> at org.teiid.query.optimizer.relational.RelationalPlanner.optimize(RelationalPlanner.java:202)
> at org.teiid.query.optimizer.QueryOptimizer.optimizePlan(QueryOptimizer.java:159)
> at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:445)
> at org.teiid.dqp.internal.process.Request.processRequest(Request.java:473)
> at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:642)
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:337)
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:274)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
> at java.lang.Thread.run(Thread.java:745)
> Caused by: org.teiid.api.exception.query.QueryResolverException: TEIID30082 Expected value of type 'null' but 'null' is of type 'string' and no implicit conversion is available.
> at org.teiid.query.resolver.util.ResolverUtil.convertExpression(ResolverUtil.java:232)
> at org.teiid.query.resolver.util.ResolverUtil.convertExpression(ResolverUtil.java:197)
> at org.teiid.query.sql.lang.SetQuery.getTypedProjectedSymbols(SetQuery.java:143)
> ... 24 more
> {code}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
8 years, 4 months
[JBoss JIRA] (TEIID-1328) API request: Programatically modify the VDB
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-1328?page=com.atlassian.jira.plugin... ]
Steven Hawkins closed TEIID-1328.
---------------------------------
> API request: Programatically modify the VDB
> --------------------------------------------
>
> Key: TEIID-1328
> URL: https://issues.jboss.org/browse/TEIID-1328
> Project: Teiid
> Issue Type: Feature Request
> Components: AdminApi
> Affects Versions: 7.1
> Reporter: Mark Addleman
> Priority: Minor
>
> I'd like an API to programatically modify the VDB. It would be ideal to modify the VDB using DDL through Teiid. Our primary use case is accessing a dynamic set of datasources through Teiid. When a datasource is added, we would INSERT the appropriate entries into MODEL, PROPERTIES, DATASOURCES tables. Teiid would reread the appropriate metadata from the associated translators.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
8 years, 4 months
[JBoss JIRA] (TEIID-5162) Google Spreadsheet translator after update unaffected columns are set to null
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5162?page=com.atlassian.jira.plugin... ]
Steven Hawkins closed TEIID-5162.
---------------------------------
> Google Spreadsheet translator after update unaffected columns are set to null
> -----------------------------------------------------------------------------
>
> Key: TEIID-5162
> URL: https://issues.jboss.org/browse/TEIID-5162
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.12.x-6.4
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
> Priority: Blocker
>
> There is loss of data when performing update on Google Spreadsheet.
> Initial data:
> {code:sql}
> INSERT INTO SmallA_Direct (IntKey, StringKey, IntNum, StringNum) VALUES (1, '1', 1, '1'), (2, '2', 2, '2'), (3, '3', 3, '3'), (4, '4', 4, '4'), (5, '5', 5, '5')
> {code}
> Then a {code}SELECT * FROM SmallA_Direct{code} results in:
> |1|1|1|1|
> |2|2|2|2|
> |3|3|3|3|
> |4|4|4|4|
> |5|5|5|5|
> Following update:
> {code:sql}
> UPDATE SmallA_Direct SET IntNum = NULL WHERE IntNum >= 3
> {code}
> Results in following data returned by {code}SELECT * FROM SmallA_Direct{code}:
> |1|<null>|1|<null>|
> |2|<null>|2|<null>|
> |3|3.0|<null>|3.0|
> |4|4.0|<null>|4.0|
> |5|5.0|<null>|5.0|
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
8 years, 4 months
[JBoss JIRA] (TEIID-5070) SYSADMIN.loadMatView have risk in xa datasource
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5070?page=com.atlassian.jira.plugin... ]
Steven Hawkins closed TEIID-5070.
---------------------------------
> SYSADMIN.loadMatView have risk in xa datasource
> -----------------------------------------------
>
> Key: TEIID-5070
> URL: https://issues.jboss.org/browse/TEIID-5070
> Project: Teiid
> Issue Type: Quality Risk
> Components: Query Engine
> Affects Versions: 8.12.10.6_3
> Environment: * JDV 6.3 - teiid-engine-8.12.5.redhat-8.jar
> Reporter: Kylin Soong
> Assignee: Steven Hawkins
>
> The main logic of *SYSADMIN.loadMatView* is insert data to stage table, then rename the stage table to mat table. However, this logic has risk in XA Transaction, If an XA transaction is in the ACTIVE state, you cannot issue any statements that cause an implicit commit, The RENAME TABLE statement will cause an implicit commit, so if MATVIEW_
> AFTER_LOAD_SCRIPT has these kinds of SQL will cause an error.
> Below is the error I have hit in a test with Customer's Data, the Mat View come from 7 Oracle databases, the mat table use mysql, the error looks
> {code}
> TEIID11008:TEIID11004 Error executing statement(s): RENAME TABLE CD_WELL TO CD_WELL_TEMP
> at org.teiid.translator.jdbc.JDBCDirectQueryExecution.execute(JDBCDirectQueryExecution.java:95) [translator-jdbc-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:364)
> at sun.reflect.GeneratedMethodAccessor162.invoke(Unknown Source) [:1.8.0_102]
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_102]
> at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_102]
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> at com.sun.proxy.$Proxy81.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
> at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:142)
> at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:391)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282)
> at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:150)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282)
> at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:145)
> at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151)
> at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114)
> at org.teiid.query.processor.BatchIterator.finalRow(BatchIterator.java:69)
> at org.teiid.common.buffer.AbstractTupleSource.getCurrentTuple(AbstractTupleSource.java:70)
> at org.teiid.query.processor.BatchIterator.getCurrentTuple(BatchIterator.java:84)
> at org.teiid.common.buffer.AbstractTupleSource.hasNext(AbstractTupleSource.java:92)
> at org.teiid.query.processor.proc.ProcedurePlan.executePlan(ProcedurePlan.java:608)
> at org.teiid.query.processor.proc.CreateCursorResultSetInstruction.process(CreateCursorResultSetInstruction.java:69)
> at org.teiid.query.processor.proc.ExecDynamicSqlInstruction$1.process(ExecDynamicSqlInstruction.java:218)
> at org.teiid.query.processor.proc.ProcedurePlan.processProcedure(ProcedurePlan.java:389)
> at org.teiid.query.processor.proc.ProcedurePlan.nextBatchDirect(ProcedurePlan.java:298)
> at org.teiid.query.processor.proc.ProcedurePlan.nextBatch(ProcedurePlan.java:270)
> at org.teiid.query.processor.relational.PlanExecutionNode.nextBatchDirect(PlanExecutionNode.java:118)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282)
> at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:150)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282)
> at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:145)
> at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151)
> at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:164)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146)
> at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:472)
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:348)
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:274)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [rt.jar:1.8.0_102]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [rt.jar:1.8.0_102]
> at java.lang.Thread.run(Thread.java:745) [rt.jar:1.8.0_102]
> Caused by: java.sql.SQLException: XAER_RMFAIL: The command cannot be executed when global transaction is in the ACTIVE state
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:998)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3835)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3771)
> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
> at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2531)
> at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2489)
> at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:848)
> at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:742)
> at com.mysql.jdbc.jdbc2.optional.StatementWrapper.execute(StatementWrapper.java:629)
> at org.jboss.jca.adapters.jdbc.WrappedStatement.execute(WrappedStatement.java:157)
> at org.teiid.translator.jdbc.JDBCDirectQueryExecution.execute(JDBCDirectQueryExecution.java:83) [translator-jdbc-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> ... 44 more
> {code}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
8 years, 4 months