[JBoss JIRA] (TEIID-3501) IN clause - teradata does not support "CAST( ... AS ...)" statement in "IN(...)"
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-3501?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration updated TEIID-3501:
-------------------------------------------
Bugzilla Update: Perform
Bugzilla References: https://bugzilla.redhat.com/show_bug.cgi?id=1226824
> IN clause - teradata does not support "CAST( ... AS ...)" statement in "IN(...)"
> --------------------------------------------------------------------------------
>
> Key: TEIID-3501
> URL: https://issues.jboss.org/browse/TEIID-3501
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 8.7.1.6_2
> Environment: teradata version: 15
> Reporter: Juraj Duráni
> Assignee: Steven Hawkins
>
> The teradata DB does not support CAST statement in IN. Query (1), teradata exception (2), source specific command (3), query that teradata accepts (4).
> (1) SELECT BQT1.SmallA.IntKey FROM BQT1.SmallA WHERE BQT1.SmallA.TimeStampValue IN (convert('2000-01-01 00:00:12.0', timestamp), convert('2000-01-01 00:00:45.0', timestamp)) ORDER BY IntKey
> (2) SELECT g_0.intkey AS c_0 FROM smalla AS g_0 WHERE g_0.timestampvalue IN (cast('2000-01-01 00:00:12.0' AS TIMESTAMP(6)), cast('2000-01-01 00:00:45.0' AS TIMESTAMP(6))) ORDER BY 1
> (3) SELECT g_0.intkey AS c_0 FROM smalla AS g_0 WHERE g_0.timestampvalue IN ('2000-01-01 00:00:12.0', '2000-01-01 00:00:45.0') ORDER BY 1
> (4) Caused by: java.sql.SQLException: [Teradata Database] [TeraJDBC 15.10.00.05] [Error 3706] [SQLState 42000] Syntax error: expected something between ')' and ','.
> at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDatabaseSQLException(ErrorFactory.java:308)
> ...
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
8 years, 11 months
- 1
- 0
[JBoss JIRA] (TEIID-3501) IN clause - teradata does not support "CAST( ... AS ...)" statement in "IN(...)"
by Juraj Duráni (JIRA)
Juraj Duráni created TEIID-3501:
-----------------------------------
Summary: IN clause - teradata does not support "CAST( ... AS ...)" statement in "IN(...)"
Key: TEIID-3501
URL: https://issues.jboss.org/browse/TEIID-3501
Project: Teiid
Issue Type: Bug
Affects Versions: 8.7.1.6_2
Environment: teradata version: 15
Reporter: Juraj Duráni
Assignee: Steven Hawkins
The teradata DB does not support CAST statement in IN. Query (1), teradata exception (2), source specific command (3), query that teradata accepts (4).
(1) SELECT BQT1.SmallA.IntKey FROM BQT1.SmallA WHERE BQT1.SmallA.TimeStampValue IN (convert('2000-01-01 00:00:12.0', timestamp), convert('2000-01-01 00:00:45.0', timestamp)) ORDER BY IntKey
(2) SELECT g_0.intkey AS c_0 FROM smalla AS g_0 WHERE g_0.timestampvalue IN (cast('2000-01-01 00:00:12.0' AS TIMESTAMP(6)), cast('2000-01-01 00:00:45.0' AS TIMESTAMP(6))) ORDER BY 1
(3) SELECT g_0.intkey AS c_0 FROM smalla AS g_0 WHERE g_0.timestampvalue IN ('2000-01-01 00:00:12.0', '2000-01-01 00:00:45.0') ORDER BY 1
(4) Caused by: java.sql.SQLException: [Teradata Database] [TeraJDBC 15.10.00.05] [Error 3706] [SQLState 42000] Syntax error: expected something between ')' and ','.
at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDatabaseSQLException(ErrorFactory.java:308)
...
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
8 years, 11 months
- 1
- 0
[JBoss JIRA] (TEIID-3500) execute SYSADMIN.matViewStatus failed cause infinite loop in MaterializationManager JobSchedular
by Kylin Soong (JIRA)
[ https://issues.jboss.org/browse/TEIID-3500?page=com.atlassian.jira.plugin... ]
Kylin Soong commented on TEIID-3500:
------------------------------------
SYSADMIN.loadMatView also execute failed in mysql, due to
TEIID30168 Couldn't execute the dynamic SQL command "EXECUTE IMMEDIATE (('SELECT Name, TargetSchemaName, TargetName, Valid, LoadState, Updated, Cardinality, LoadNumber FROM ' || VARIABLES.statusTable) || crit) AS Name string, TargetSchemaName string, TargetName string, Valid boolean, LoadState string, Updated timestamp, Cardinality long, LoadNumber long INTO #load USING vdbName = VARIABLES.vdbName, vdbVersion = VARIABLES.vdbVersion, schemaName = schemaName, viewName = loadMatView.viewName" with the SQL statement "(('SELECT Name, TargetSchemaName, TargetName, Valid, LoadState, Updated, Cardinality, LoadNumber FROM ' || VARIABLES.statusTable) || crit)" due to: The datatype 'clob' for element 'Cardinality' in the dynamic SQL cannot be implicitly converted to 'long'.
If execute against H2 , it works fine.
> execute SYSADMIN.matViewStatus failed cause infinite loop in MaterializationManager JobSchedular
> ------------------------------------------------------------------------------------------------
>
> Key: TEIID-3500
> URL: https://issues.jboss.org/browse/TEIID-3500
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.11
> Reporter: Kylin Soong
> Attachments: ExternalMaterializationMysql.java, mat-mysql-vdb-debug.xml, mat-mysql-vdb.xml, schema.sql
>
>
> While using Mysql, SYSADMIN.matViewStatus throw a SQLExecption
> ~~~
> java.sql.SQLException: org.teiid.api.exception.query.QueryProcessingException: TEIID30168 Couldn't execute the dynamic SQL command "EXECUTE IMMEDIATE (('SELECT TargetSchemaName, TargetName, Valid, LoadState, Updated, Cardinality, LoadNumber, VARIABLES.action FROM ' || VARIABLES.statusTable) || crit) AS TargetSchemaName string, TargetName string, Valid boolean, LoadState string, Updated timestamp, Cardinality long, LoadNumber long, OnErrorAction string USING vdbName = VARIABLES.vdbName, vdbVersion = VARIABLES.vdbVersion, schemaName = matViewStatus.schemaName, viewName = matViewStatus.viewName" with the SQL statement "(('SELECT TargetSchemaName, TargetName, Valid, LoadState, Updated, Cardinality, LoadNumber, VARIABLES.action FROM ' || VARIABLES.statusTable) || crit)" due to: The datatype 'clob' for element 'Cardinality' in the dynamic SQL cannot be implicitly converted to 'long'.
> ~~~
> this cause scheduleJob infinite loop, 'SYSADMIN.loadMatView' never be executed.
> If we didn't configure 'teiid_rel:MATVIEW_TTL', execute SYSADMIN.matViewStatus manually , full stack trace as [1]
> If test with H2 database, SYSADMIN.matViewStatus works fine.
> [1] stack trace
> ~~~
> 2015-06-01 11:35 088 WARNING [org.teiid.PROCESSOR] (main) TEIID30020 Processing exception for request gQ25UW2DqodQ.7 'TEIID30168 Couldn't execute the dynamic SQL command "EXECUTE IMMEDIATE (('SELECT TargetSchemaName, TargetName, Valid, LoadState, Updated, Cardinality, LoadNumber, VARIABLES.action FROM ' || VARIABLES.statusTable) || crit) AS TargetSchemaName string, TargetName string, Valid boolean, LoadState string, Updated timestamp, Cardinality long, LoadNumber long, OnErrorAction string USING vdbName = VARIABLES.vdbName, vdbVersion = VARIABLES.vdbVersion, schemaName = matViewStatus.schemaName, viewName = matViewStatus.viewName" with the SQL statement "(('SELECT TargetSchemaName, TargetName, Valid, LoadState, Updated, Cardinality, LoadNumber, VARIABLES.action FROM ' || VARIABLES.statusTable) || crit)" due to: The datatype 'clob' for element 'Cardinality' in the dynamic SQL cannot be implicitly converted to 'long'.'. Originally QueryProcessingException ExecDynamicSqlInstruction.java:341. Enable more detailed logging to see the entire stacktrace.
> org.teiid.jdbc.TeiidSQLException: TEIID30168 Couldn't execute the dynamic SQL command "EXECUTE IMMEDIATE (('SELECT TargetSchemaName, TargetName, Valid, LoadState, Updated, Cardinality, LoadNumber, VARIABLES.action FROM ' || VARIABLES.statusTable) || crit) AS TargetSchemaName string, TargetName string, Valid boolean, LoadState string, Updated timestamp, Cardinality long, LoadNumber long, OnErrorAction string USING vdbName = VARIABLES.vdbName, vdbVersion = VARIABLES.vdbVersion, schemaName = matViewStatus.schemaName, viewName = matViewStatus.viewName" with the SQL statement "(('SELECT TargetSchemaName, TargetName, Valid, LoadState, Updated, Cardinality, LoadNumber, VARIABLES.action FROM ' || VARIABLES.statusTable) || crit)" due to: The datatype 'clob' for element 'Cardinality' in the dynamic SQL cannot be implicitly converted to 'long'.
> at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:135)
> at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:71)
> at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:694)
> at org.teiid.jdbc.StatementImpl.access$100(StatementImpl.java:64)
> at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:533)
> at org.teiid.client.util.ResultsFuture.addCompletionListener(ResultsFuture.java:148)
> at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:529)
> at org.teiid.jdbc.StatementImpl.execute(StatementImpl.java:1062)
> at org.teiid.jdbc.StatementImpl.execute(StatementImpl.java:314)
> at org.teiid.example.util.JDBCUtils.execute(JDBCUtils.java:86)
> at org.teiid.test.perf.ExternalMaterializationMysqlDebug.executeMatViewStatus(ExternalMaterializationMysqlDebug.java:83)
> at org.teiid.test.perf.ExternalMaterializationMysqlDebug.main(ExternalMaterializationMysqlDebug.java:65)
> Caused by: org.teiid.api.exception.query.QueryProcessingException: TEIID30168 Couldn't execute the dynamic SQL command "EXECUTE IMMEDIATE (('SELECT TargetSchemaName, TargetName, Valid, LoadState, Updated, Cardinality, LoadNumber, VARIABLES.action FROM ' || VARIABLES.statusTable) || crit) AS TargetSchemaName string, TargetName string, Valid boolean, LoadState string, Updated timestamp, Cardinality long, LoadNumber long, OnErrorAction string USING vdbName = VARIABLES.vdbName, vdbVersion = VARIABLES.vdbVersion, schemaName = matViewStatus.schemaName, viewName = matViewStatus.viewName" with the SQL statement "(('SELECT TargetSchemaName, TargetName, Valid, LoadState, Updated, Cardinality, LoadNumber, VARIABLES.action FROM ' || VARIABLES.statusTable) || crit)" due to: The datatype 'clob' for element 'Cardinality' in the dynamic SQL cannot be implicitly converted to 'long'.
> at org.teiid.query.processor.proc.ExecDynamicSqlInstruction.process(ExecDynamicSqlInstruction.java:249)
> at org.teiid.query.processor.proc.ProcedurePlan.processProcedure(ProcedurePlan.java:384)
> at org.teiid.query.processor.proc.ProcedurePlan.nextBatchDirect(ProcedurePlan.java:293)
> at org.teiid.query.processor.proc.ProcedurePlan.nextBatch(ProcedurePlan.java:267)
> at org.teiid.query.processor.relational.PlanExecutionNode.nextBatchDirect(PlanExecutionNode.java:118)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278)
> at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:148)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278)
> 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:457)
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:339)
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:267)
> at org.teiid.dqp.internal.process.DQPCore.executeRequest(DQPCore.java:298)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:606)
> at org.teiid.transport.LocalServerConnection$1$1.call(LocalServerConnection.java:175)
> at java.util.concurrent.FutureTask.run(FutureTask.java:262)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:260)
> at org.teiid.transport.LocalServerConnection$1.invoke(LocalServerConnection.java:173)
> at com.sun.proxy.$Proxy11.executeRequest(Unknown Source)
> at org.teiid.jdbc.StatementImpl.execute(StatementImpl.java:661)
> at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:527)
> ... 5 more
> Caused by: org.teiid.api.exception.query.QueryProcessingException: The datatype 'clob' for element 'Cardinality' in the dynamic SQL cannot be implicitly converted to 'long'.
> at org.teiid.query.processor.proc.ExecDynamicSqlInstruction.validateDynamicCommand(ExecDynamicSqlInstruction.java:341)
> at org.teiid.query.processor.proc.ExecDynamicSqlInstruction.process(ExecDynamicSqlInstruction.java:168)
> ... 34 more
> ~~~
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
8 years, 11 months
- 1
- 0
[JBoss JIRA] (TEIID-3500) execute SYSADMIN.matViewStatus failed cause infinite loop in MaterializationManager JobSchedular
by Kylin Soong (JIRA)
[ https://issues.jboss.org/browse/TEIID-3500?page=com.atlassian.jira.plugin... ]
Kylin Soong updated TEIID-3500:
-------------------------------
Attachment: mat-mysql-vdb-debug.xml
mat-mysql-vdb.xml
schema.sql
ExternalMaterializationMysql.java
Steps to reproduce:
1. execute attached schema.sql against a Mysql database
2. with attached vdb and java will reproduce the issue.
> execute SYSADMIN.matViewStatus failed cause infinite loop in MaterializationManager JobSchedular
> ------------------------------------------------------------------------------------------------
>
> Key: TEIID-3500
> URL: https://issues.jboss.org/browse/TEIID-3500
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.11
> Reporter: Kylin Soong
> Attachments: ExternalMaterializationMysql.java, mat-mysql-vdb-debug.xml, mat-mysql-vdb.xml, schema.sql
>
>
> While using Mysql, SYSADMIN.matViewStatus throw a SQLExecption
> ~~~
> java.sql.SQLException: org.teiid.api.exception.query.QueryProcessingException: TEIID30168 Couldn't execute the dynamic SQL command "EXECUTE IMMEDIATE (('SELECT TargetSchemaName, TargetName, Valid, LoadState, Updated, Cardinality, LoadNumber, VARIABLES.action FROM ' || VARIABLES.statusTable) || crit) AS TargetSchemaName string, TargetName string, Valid boolean, LoadState string, Updated timestamp, Cardinality long, LoadNumber long, OnErrorAction string USING vdbName = VARIABLES.vdbName, vdbVersion = VARIABLES.vdbVersion, schemaName = matViewStatus.schemaName, viewName = matViewStatus.viewName" with the SQL statement "(('SELECT TargetSchemaName, TargetName, Valid, LoadState, Updated, Cardinality, LoadNumber, VARIABLES.action FROM ' || VARIABLES.statusTable) || crit)" due to: The datatype 'clob' for element 'Cardinality' in the dynamic SQL cannot be implicitly converted to 'long'.
> ~~~
> this cause scheduleJob infinite loop, 'SYSADMIN.loadMatView' never be executed.
> If we didn't configure 'teiid_rel:MATVIEW_TTL', execute SYSADMIN.matViewStatus manually , full stack trace as [1]
> If test with H2 database, SYSADMIN.matViewStatus works fine.
> [1] stack trace
> ~~~
> 2015-06-01 11:35 088 WARNING [org.teiid.PROCESSOR] (main) TEIID30020 Processing exception for request gQ25UW2DqodQ.7 'TEIID30168 Couldn't execute the dynamic SQL command "EXECUTE IMMEDIATE (('SELECT TargetSchemaName, TargetName, Valid, LoadState, Updated, Cardinality, LoadNumber, VARIABLES.action FROM ' || VARIABLES.statusTable) || crit) AS TargetSchemaName string, TargetName string, Valid boolean, LoadState string, Updated timestamp, Cardinality long, LoadNumber long, OnErrorAction string USING vdbName = VARIABLES.vdbName, vdbVersion = VARIABLES.vdbVersion, schemaName = matViewStatus.schemaName, viewName = matViewStatus.viewName" with the SQL statement "(('SELECT TargetSchemaName, TargetName, Valid, LoadState, Updated, Cardinality, LoadNumber, VARIABLES.action FROM ' || VARIABLES.statusTable) || crit)" due to: The datatype 'clob' for element 'Cardinality' in the dynamic SQL cannot be implicitly converted to 'long'.'. Originally QueryProcessingException ExecDynamicSqlInstruction.java:341. Enable more detailed logging to see the entire stacktrace.
> org.teiid.jdbc.TeiidSQLException: TEIID30168 Couldn't execute the dynamic SQL command "EXECUTE IMMEDIATE (('SELECT TargetSchemaName, TargetName, Valid, LoadState, Updated, Cardinality, LoadNumber, VARIABLES.action FROM ' || VARIABLES.statusTable) || crit) AS TargetSchemaName string, TargetName string, Valid boolean, LoadState string, Updated timestamp, Cardinality long, LoadNumber long, OnErrorAction string USING vdbName = VARIABLES.vdbName, vdbVersion = VARIABLES.vdbVersion, schemaName = matViewStatus.schemaName, viewName = matViewStatus.viewName" with the SQL statement "(('SELECT TargetSchemaName, TargetName, Valid, LoadState, Updated, Cardinality, LoadNumber, VARIABLES.action FROM ' || VARIABLES.statusTable) || crit)" due to: The datatype 'clob' for element 'Cardinality' in the dynamic SQL cannot be implicitly converted to 'long'.
> at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:135)
> at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:71)
> at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:694)
> at org.teiid.jdbc.StatementImpl.access$100(StatementImpl.java:64)
> at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:533)
> at org.teiid.client.util.ResultsFuture.addCompletionListener(ResultsFuture.java:148)
> at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:529)
> at org.teiid.jdbc.StatementImpl.execute(StatementImpl.java:1062)
> at org.teiid.jdbc.StatementImpl.execute(StatementImpl.java:314)
> at org.teiid.example.util.JDBCUtils.execute(JDBCUtils.java:86)
> at org.teiid.test.perf.ExternalMaterializationMysqlDebug.executeMatViewStatus(ExternalMaterializationMysqlDebug.java:83)
> at org.teiid.test.perf.ExternalMaterializationMysqlDebug.main(ExternalMaterializationMysqlDebug.java:65)
> Caused by: org.teiid.api.exception.query.QueryProcessingException: TEIID30168 Couldn't execute the dynamic SQL command "EXECUTE IMMEDIATE (('SELECT TargetSchemaName, TargetName, Valid, LoadState, Updated, Cardinality, LoadNumber, VARIABLES.action FROM ' || VARIABLES.statusTable) || crit) AS TargetSchemaName string, TargetName string, Valid boolean, LoadState string, Updated timestamp, Cardinality long, LoadNumber long, OnErrorAction string USING vdbName = VARIABLES.vdbName, vdbVersion = VARIABLES.vdbVersion, schemaName = matViewStatus.schemaName, viewName = matViewStatus.viewName" with the SQL statement "(('SELECT TargetSchemaName, TargetName, Valid, LoadState, Updated, Cardinality, LoadNumber, VARIABLES.action FROM ' || VARIABLES.statusTable) || crit)" due to: The datatype 'clob' for element 'Cardinality' in the dynamic SQL cannot be implicitly converted to 'long'.
> at org.teiid.query.processor.proc.ExecDynamicSqlInstruction.process(ExecDynamicSqlInstruction.java:249)
> at org.teiid.query.processor.proc.ProcedurePlan.processProcedure(ProcedurePlan.java:384)
> at org.teiid.query.processor.proc.ProcedurePlan.nextBatchDirect(ProcedurePlan.java:293)
> at org.teiid.query.processor.proc.ProcedurePlan.nextBatch(ProcedurePlan.java:267)
> at org.teiid.query.processor.relational.PlanExecutionNode.nextBatchDirect(PlanExecutionNode.java:118)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278)
> at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:148)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278)
> 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:457)
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:339)
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:267)
> at org.teiid.dqp.internal.process.DQPCore.executeRequest(DQPCore.java:298)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:606)
> at org.teiid.transport.LocalServerConnection$1$1.call(LocalServerConnection.java:175)
> at java.util.concurrent.FutureTask.run(FutureTask.java:262)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:260)
> at org.teiid.transport.LocalServerConnection$1.invoke(LocalServerConnection.java:173)
> at com.sun.proxy.$Proxy11.executeRequest(Unknown Source)
> at org.teiid.jdbc.StatementImpl.execute(StatementImpl.java:661)
> at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:527)
> ... 5 more
> Caused by: org.teiid.api.exception.query.QueryProcessingException: The datatype 'clob' for element 'Cardinality' in the dynamic SQL cannot be implicitly converted to 'long'.
> at org.teiid.query.processor.proc.ExecDynamicSqlInstruction.validateDynamicCommand(ExecDynamicSqlInstruction.java:341)
> at org.teiid.query.processor.proc.ExecDynamicSqlInstruction.process(ExecDynamicSqlInstruction.java:168)
> ... 34 more
> ~~~
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
8 years, 11 months
- 1
- 0
[JBoss JIRA] (TEIID-3500) execute SYSADMIN.matViewStatus failed cause infinite loop in MaterializationManager JobSchedular
by Kylin Soong (JIRA)
[ https://issues.jboss.org/browse/TEIID-3500?page=com.atlassian.jira.plugin... ]
Kylin Soong reassigned TEIID-3500:
----------------------------------
Issue Type: Bug (was: Feature Request)
Assignee: (was: Steven Hawkins)
> execute SYSADMIN.matViewStatus failed cause infinite loop in MaterializationManager JobSchedular
> ------------------------------------------------------------------------------------------------
>
> Key: TEIID-3500
> URL: https://issues.jboss.org/browse/TEIID-3500
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.11
> Reporter: Kylin Soong
>
> While using Mysql, SYSADMIN.matViewStatus throw a SQLExecption
> ~~~
> java.sql.SQLException: org.teiid.api.exception.query.QueryProcessingException: TEIID30168 Couldn't execute the dynamic SQL command "EXECUTE IMMEDIATE (('SELECT TargetSchemaName, TargetName, Valid, LoadState, Updated, Cardinality, LoadNumber, VARIABLES.action FROM ' || VARIABLES.statusTable) || crit) AS TargetSchemaName string, TargetName string, Valid boolean, LoadState string, Updated timestamp, Cardinality long, LoadNumber long, OnErrorAction string USING vdbName = VARIABLES.vdbName, vdbVersion = VARIABLES.vdbVersion, schemaName = matViewStatus.schemaName, viewName = matViewStatus.viewName" with the SQL statement "(('SELECT TargetSchemaName, TargetName, Valid, LoadState, Updated, Cardinality, LoadNumber, VARIABLES.action FROM ' || VARIABLES.statusTable) || crit)" due to: The datatype 'clob' for element 'Cardinality' in the dynamic SQL cannot be implicitly converted to 'long'.
> ~~~
> this cause scheduleJob infinite loop, 'SYSADMIN.loadMatView' never be executed.
> If we didn't configure 'teiid_rel:MATVIEW_TTL', execute SYSADMIN.matViewStatus manually , full stack trace as [1]
> If test with H2 database, SYSADMIN.matViewStatus works fine.
> [1] stack trace
> ~~~
> 2015-06-01 11:35 088 WARNING [org.teiid.PROCESSOR] (main) TEIID30020 Processing exception for request gQ25UW2DqodQ.7 'TEIID30168 Couldn't execute the dynamic SQL command "EXECUTE IMMEDIATE (('SELECT TargetSchemaName, TargetName, Valid, LoadState, Updated, Cardinality, LoadNumber, VARIABLES.action FROM ' || VARIABLES.statusTable) || crit) AS TargetSchemaName string, TargetName string, Valid boolean, LoadState string, Updated timestamp, Cardinality long, LoadNumber long, OnErrorAction string USING vdbName = VARIABLES.vdbName, vdbVersion = VARIABLES.vdbVersion, schemaName = matViewStatus.schemaName, viewName = matViewStatus.viewName" with the SQL statement "(('SELECT TargetSchemaName, TargetName, Valid, LoadState, Updated, Cardinality, LoadNumber, VARIABLES.action FROM ' || VARIABLES.statusTable) || crit)" due to: The datatype 'clob' for element 'Cardinality' in the dynamic SQL cannot be implicitly converted to 'long'.'. Originally QueryProcessingException ExecDynamicSqlInstruction.java:341. Enable more detailed logging to see the entire stacktrace.
> org.teiid.jdbc.TeiidSQLException: TEIID30168 Couldn't execute the dynamic SQL command "EXECUTE IMMEDIATE (('SELECT TargetSchemaName, TargetName, Valid, LoadState, Updated, Cardinality, LoadNumber, VARIABLES.action FROM ' || VARIABLES.statusTable) || crit) AS TargetSchemaName string, TargetName string, Valid boolean, LoadState string, Updated timestamp, Cardinality long, LoadNumber long, OnErrorAction string USING vdbName = VARIABLES.vdbName, vdbVersion = VARIABLES.vdbVersion, schemaName = matViewStatus.schemaName, viewName = matViewStatus.viewName" with the SQL statement "(('SELECT TargetSchemaName, TargetName, Valid, LoadState, Updated, Cardinality, LoadNumber, VARIABLES.action FROM ' || VARIABLES.statusTable) || crit)" due to: The datatype 'clob' for element 'Cardinality' in the dynamic SQL cannot be implicitly converted to 'long'.
> at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:135)
> at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:71)
> at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:694)
> at org.teiid.jdbc.StatementImpl.access$100(StatementImpl.java:64)
> at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:533)
> at org.teiid.client.util.ResultsFuture.addCompletionListener(ResultsFuture.java:148)
> at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:529)
> at org.teiid.jdbc.StatementImpl.execute(StatementImpl.java:1062)
> at org.teiid.jdbc.StatementImpl.execute(StatementImpl.java:314)
> at org.teiid.example.util.JDBCUtils.execute(JDBCUtils.java:86)
> at org.teiid.test.perf.ExternalMaterializationMysqlDebug.executeMatViewStatus(ExternalMaterializationMysqlDebug.java:83)
> at org.teiid.test.perf.ExternalMaterializationMysqlDebug.main(ExternalMaterializationMysqlDebug.java:65)
> Caused by: org.teiid.api.exception.query.QueryProcessingException: TEIID30168 Couldn't execute the dynamic SQL command "EXECUTE IMMEDIATE (('SELECT TargetSchemaName, TargetName, Valid, LoadState, Updated, Cardinality, LoadNumber, VARIABLES.action FROM ' || VARIABLES.statusTable) || crit) AS TargetSchemaName string, TargetName string, Valid boolean, LoadState string, Updated timestamp, Cardinality long, LoadNumber long, OnErrorAction string USING vdbName = VARIABLES.vdbName, vdbVersion = VARIABLES.vdbVersion, schemaName = matViewStatus.schemaName, viewName = matViewStatus.viewName" with the SQL statement "(('SELECT TargetSchemaName, TargetName, Valid, LoadState, Updated, Cardinality, LoadNumber, VARIABLES.action FROM ' || VARIABLES.statusTable) || crit)" due to: The datatype 'clob' for element 'Cardinality' in the dynamic SQL cannot be implicitly converted to 'long'.
> at org.teiid.query.processor.proc.ExecDynamicSqlInstruction.process(ExecDynamicSqlInstruction.java:249)
> at org.teiid.query.processor.proc.ProcedurePlan.processProcedure(ProcedurePlan.java:384)
> at org.teiid.query.processor.proc.ProcedurePlan.nextBatchDirect(ProcedurePlan.java:293)
> at org.teiid.query.processor.proc.ProcedurePlan.nextBatch(ProcedurePlan.java:267)
> at org.teiid.query.processor.relational.PlanExecutionNode.nextBatchDirect(PlanExecutionNode.java:118)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278)
> at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:148)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278)
> 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:457)
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:339)
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:267)
> at org.teiid.dqp.internal.process.DQPCore.executeRequest(DQPCore.java:298)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:606)
> at org.teiid.transport.LocalServerConnection$1$1.call(LocalServerConnection.java:175)
> at java.util.concurrent.FutureTask.run(FutureTask.java:262)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:260)
> at org.teiid.transport.LocalServerConnection$1.invoke(LocalServerConnection.java:173)
> at com.sun.proxy.$Proxy11.executeRequest(Unknown Source)
> at org.teiid.jdbc.StatementImpl.execute(StatementImpl.java:661)
> at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:527)
> ... 5 more
> Caused by: org.teiid.api.exception.query.QueryProcessingException: The datatype 'clob' for element 'Cardinality' in the dynamic SQL cannot be implicitly converted to 'long'.
> at org.teiid.query.processor.proc.ExecDynamicSqlInstruction.validateDynamicCommand(ExecDynamicSqlInstruction.java:341)
> at org.teiid.query.processor.proc.ExecDynamicSqlInstruction.process(ExecDynamicSqlInstruction.java:168)
> ... 34 more
> ~~~
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
8 years, 11 months
- 1
- 0
[JBoss JIRA] (TEIID-3500) execute SYSADMIN.matViewStatus failed cause infinite loop in MaterializationManager JobSchedular
by Kylin Soong (JIRA)
Kylin Soong created TEIID-3500:
----------------------------------
Summary: execute SYSADMIN.matViewStatus failed cause infinite loop in MaterializationManager JobSchedular
Key: TEIID-3500
URL: https://issues.jboss.org/browse/TEIID-3500
Project: Teiid
Issue Type: Feature Request
Components: Query Engine
Affects Versions: 8.11
Reporter: Kylin Soong
Assignee: Steven Hawkins
While using Mysql, SYSADMIN.matViewStatus throw a SQLExecption
~~~
java.sql.SQLException: org.teiid.api.exception.query.QueryProcessingException: TEIID30168 Couldn't execute the dynamic SQL command "EXECUTE IMMEDIATE (('SELECT TargetSchemaName, TargetName, Valid, LoadState, Updated, Cardinality, LoadNumber, VARIABLES.action FROM ' || VARIABLES.statusTable) || crit) AS TargetSchemaName string, TargetName string, Valid boolean, LoadState string, Updated timestamp, Cardinality long, LoadNumber long, OnErrorAction string USING vdbName = VARIABLES.vdbName, vdbVersion = VARIABLES.vdbVersion, schemaName = matViewStatus.schemaName, viewName = matViewStatus.viewName" with the SQL statement "(('SELECT TargetSchemaName, TargetName, Valid, LoadState, Updated, Cardinality, LoadNumber, VARIABLES.action FROM ' || VARIABLES.statusTable) || crit)" due to: The datatype 'clob' for element 'Cardinality' in the dynamic SQL cannot be implicitly converted to 'long'.
~~~
this cause scheduleJob infinite loop, 'SYSADMIN.loadMatView' never be executed.
If we didn't configure 'teiid_rel:MATVIEW_TTL', execute SYSADMIN.matViewStatus manually , full stack trace as [1]
If test with H2 database, SYSADMIN.matViewStatus works fine.
[1] stack trace
~~~
2015-06-01 11:35 088 WARNING [org.teiid.PROCESSOR] (main) TEIID30020 Processing exception for request gQ25UW2DqodQ.7 'TEIID30168 Couldn't execute the dynamic SQL command "EXECUTE IMMEDIATE (('SELECT TargetSchemaName, TargetName, Valid, LoadState, Updated, Cardinality, LoadNumber, VARIABLES.action FROM ' || VARIABLES.statusTable) || crit) AS TargetSchemaName string, TargetName string, Valid boolean, LoadState string, Updated timestamp, Cardinality long, LoadNumber long, OnErrorAction string USING vdbName = VARIABLES.vdbName, vdbVersion = VARIABLES.vdbVersion, schemaName = matViewStatus.schemaName, viewName = matViewStatus.viewName" with the SQL statement "(('SELECT TargetSchemaName, TargetName, Valid, LoadState, Updated, Cardinality, LoadNumber, VARIABLES.action FROM ' || VARIABLES.statusTable) || crit)" due to: The datatype 'clob' for element 'Cardinality' in the dynamic SQL cannot be implicitly converted to 'long'.'. Originally QueryProcessingException ExecDynamicSqlInstruction.java:341. Enable more detailed logging to see the entire stacktrace.
org.teiid.jdbc.TeiidSQLException: TEIID30168 Couldn't execute the dynamic SQL command "EXECUTE IMMEDIATE (('SELECT TargetSchemaName, TargetName, Valid, LoadState, Updated, Cardinality, LoadNumber, VARIABLES.action FROM ' || VARIABLES.statusTable) || crit) AS TargetSchemaName string, TargetName string, Valid boolean, LoadState string, Updated timestamp, Cardinality long, LoadNumber long, OnErrorAction string USING vdbName = VARIABLES.vdbName, vdbVersion = VARIABLES.vdbVersion, schemaName = matViewStatus.schemaName, viewName = matViewStatus.viewName" with the SQL statement "(('SELECT TargetSchemaName, TargetName, Valid, LoadState, Updated, Cardinality, LoadNumber, VARIABLES.action FROM ' || VARIABLES.statusTable) || crit)" due to: The datatype 'clob' for element 'Cardinality' in the dynamic SQL cannot be implicitly converted to 'long'.
at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:135)
at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:71)
at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:694)
at org.teiid.jdbc.StatementImpl.access$100(StatementImpl.java:64)
at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:533)
at org.teiid.client.util.ResultsFuture.addCompletionListener(ResultsFuture.java:148)
at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:529)
at org.teiid.jdbc.StatementImpl.execute(StatementImpl.java:1062)
at org.teiid.jdbc.StatementImpl.execute(StatementImpl.java:314)
at org.teiid.example.util.JDBCUtils.execute(JDBCUtils.java:86)
at org.teiid.test.perf.ExternalMaterializationMysqlDebug.executeMatViewStatus(ExternalMaterializationMysqlDebug.java:83)
at org.teiid.test.perf.ExternalMaterializationMysqlDebug.main(ExternalMaterializationMysqlDebug.java:65)
Caused by: org.teiid.api.exception.query.QueryProcessingException: TEIID30168 Couldn't execute the dynamic SQL command "EXECUTE IMMEDIATE (('SELECT TargetSchemaName, TargetName, Valid, LoadState, Updated, Cardinality, LoadNumber, VARIABLES.action FROM ' || VARIABLES.statusTable) || crit) AS TargetSchemaName string, TargetName string, Valid boolean, LoadState string, Updated timestamp, Cardinality long, LoadNumber long, OnErrorAction string USING vdbName = VARIABLES.vdbName, vdbVersion = VARIABLES.vdbVersion, schemaName = matViewStatus.schemaName, viewName = matViewStatus.viewName" with the SQL statement "(('SELECT TargetSchemaName, TargetName, Valid, LoadState, Updated, Cardinality, LoadNumber, VARIABLES.action FROM ' || VARIABLES.statusTable) || crit)" due to: The datatype 'clob' for element 'Cardinality' in the dynamic SQL cannot be implicitly converted to 'long'.
at org.teiid.query.processor.proc.ExecDynamicSqlInstruction.process(ExecDynamicSqlInstruction.java:249)
at org.teiid.query.processor.proc.ProcedurePlan.processProcedure(ProcedurePlan.java:384)
at org.teiid.query.processor.proc.ProcedurePlan.nextBatchDirect(ProcedurePlan.java:293)
at org.teiid.query.processor.proc.ProcedurePlan.nextBatch(ProcedurePlan.java:267)
at org.teiid.query.processor.relational.PlanExecutionNode.nextBatchDirect(PlanExecutionNode.java:118)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278)
at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:148)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278)
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:457)
at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:339)
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:267)
at org.teiid.dqp.internal.process.DQPCore.executeRequest(DQPCore.java:298)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.teiid.transport.LocalServerConnection$1$1.call(LocalServerConnection.java:175)
at java.util.concurrent.FutureTask.run(FutureTask.java:262)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:260)
at org.teiid.transport.LocalServerConnection$1.invoke(LocalServerConnection.java:173)
at com.sun.proxy.$Proxy11.executeRequest(Unknown Source)
at org.teiid.jdbc.StatementImpl.execute(StatementImpl.java:661)
at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:527)
... 5 more
Caused by: org.teiid.api.exception.query.QueryProcessingException: The datatype 'clob' for element 'Cardinality' in the dynamic SQL cannot be implicitly converted to 'long'.
at org.teiid.query.processor.proc.ExecDynamicSqlInstruction.validateDynamicCommand(ExecDynamicSqlInstruction.java:341)
at org.teiid.query.processor.proc.ExecDynamicSqlInstruction.process(ExecDynamicSqlInstruction.java:168)
... 34 more
~~~
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
8 years, 11 months
- 1
- 0