[JBoss JIRA] (TEIID-4121) Enhancing the Teiid Materialization
by Kylin Soong (JIRA)
[ https://issues.jboss.org/browse/TEIID-4121?page=com.atlassian.jira.plugin... ]
Kylin Soong updated TEIID-4121:
-------------------------------
Description:
The intention of move "status" table to physical database is to increase durable and fully control refresh and loading, but it increase the complexity.
The "status" table by design should unique for whole VDB, but current one VDB may have multiple "status" table, each view may have a "status" table
was:After some investigation
> Enhancing the Teiid Materialization
> -----------------------------------
>
> Key: TEIID-4121
> URL: https://issues.jboss.org/browse/TEIID-4121
> Project: Teiid
> Issue Type: Sub-task
> Components: Query Engine
> Affects Versions: 9.x
> Reporter: Kylin Soong
> Assignee: Steven Hawkins
> Fix For: 9.0
>
>
> The intention of move "status" table to physical database is to increase durable and fully control refresh and loading, but it increase the complexity.
> The "status" table by design should unique for whole VDB, but current one VDB may have multiple "status" table, each view may have a "status" table
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 12 months
[JBoss JIRA] (TEIID-3582) Add the query requestID to all lines for that query in the logs
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3582?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-3582:
---------------------------------------
Did you update the logging pattern? This issue applies to the requestid, not the atomic requestid. The requestid will be shown if you update the logging pattern to reference the value from the MDC.
> Add the query requestID to all lines for that query in the logs
> ---------------------------------------------------------------
>
> Key: TEIID-3582
> URL: https://issues.jboss.org/browse/TEIID-3582
> Project: Teiid
> Issue Type: Enhancement
> Components: Server
> Affects Versions: 8.12.5
> Reporter: Van Halbert
> Assignee: Steven Hawkins
> Fix For: 8.12.2, 8.13
>
>
> Currently when running a query, the logs show the requestId on most of the lines but not all, especially CONNECTOR.
> for example, the following line doesn't have it:
> 14:13:34,071 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue13) Source-specific command: ....
> That's the problem. When lots of queries are running, user wants to grep through the log for that requestid and get just that one query's stuff.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 12 months
[JBoss JIRA] (TEIID-3582) Add the query requestID to all lines for that query in the logs
by Jan Stastny (JIRA)
[ https://issues.jboss.org/browse/TEIID-3582?page=com.atlassian.jira.plugin... ]
Jan Stastny reassigned TEIID-3582:
----------------------------------
Assignee: Steven Hawkins
> Add the query requestID to all lines for that query in the logs
> ---------------------------------------------------------------
>
> Key: TEIID-3582
> URL: https://issues.jboss.org/browse/TEIID-3582
> Project: Teiid
> Issue Type: Enhancement
> Components: Server
> Affects Versions: 8.12.5
> Reporter: Van Halbert
> Assignee: Steven Hawkins
> Fix For: 8.12.2, 8.13
>
>
> Currently when running a query, the logs show the requestId on most of the lines but not all, especially CONNECTOR.
> for example, the following line doesn't have it:
> 14:13:34,071 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue13) Source-specific command: ....
> That's the problem. When lots of queries are running, user wants to grep through the log for that requestid and get just that one query's stuff.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 12 months
[JBoss JIRA] (TEIID-3582) Add the query requestID to all lines for that query in the logs
by Jan Stastny (JIRA)
[ https://issues.jboss.org/browse/TEIID-3582?page=com.atlassian.jira.plugin... ]
Jan Stastny edited comment on TEIID-3582 at 4/6/16 1:15 PM:
------------------------------------------------------------
The requestId is not present in the log mentioned in the description.
I get:
{code:plain}
17:33:12,227 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue1) Source-specific command: SELECT g_0.`id`, g_0.`name`, g_0.`address` FROM `dballo19`.`customers` AS g_0
{code}
Logs for whole query processing:
{code:plain}
17:33:12,046 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) Request Thread GZ80TLU8xKRP.0 with state NEW
17:33:12,047 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) GZ80TLU8xKRP.0 Command has no cache hint and result set cache mode is not on.
17:33:12,047 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) GZ80TLU8xKRP.0 executing SELECT * FROM customers
17:33:12,141 DEBUG [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue0) Initializing the capabilities for mysql5
17:33:12,142 DEBUG [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue0) Setting the database version to 5.5.47
17:33:12,164 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) ProcessTree for GZ80TLU8xKRP.0 AccessNode(0) output=[MySQLSource.dballo19.customers.id, MySQLSource.dballo19.customers.name, MySQLSource.dballo19.customers.address] SELECT g_0.id, g_0.name, g_0.address FROM MySQLSource.dballo19.customers AS g_0
17:33:12,165 DEBUG [org.teiid.TXN_LOG] (Worker0_QueryProcessorQueue0) before getOrCreateTransactionContext:org.teiid.dqp.internal.process.TransactionServerImpl@cdfd35f(GZ80TLU8xKRP)
17:33:12,166 DEBUG [org.teiid.TXN_LOG] (Worker0_QueryProcessorQueue0) after getOrCreateTransactionContext : GZ80TLU8xKRP NONE ID:NONE
17:33:12,176 DEBUG [org.teiid.BUFFER_MGR] (Worker0_QueryProcessorQueue0) Creating TupleBuffer: 0 [MySQLSource.dballo19.customers.id, MySQLSource.dballo19.customers.name, MySQLSource.dballo19.customers.address] [class java.lang.Long, class java.lang.String, class java.lang.String] batch size 512 of type PROCESSOR
17:33:12,183 DEBUG [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue0) GZ80TLU8xKRP.0.0.0 Create State
17:33:12,207 DEBUG [org.teiid.BUFFER_MGR] (Worker0_QueryProcessorQueue0) GZ80TLU8xKRP.0.0.0 Blocking on source query GZ80TLU8xKRP.0.0.0
17:33:12,207 DEBUG [org.teiid.BUFFER_MGR] (Worker0_QueryProcessorQueue0) GZ80TLU8xKRP.0 Blocking on source request(s).
17:33:12,207 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) Request Thread GZ80TLU8xKRP.0 - processor blocked
17:33:12,214 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1) Running task for parent thread Worker0_QueryProcessorQueue0
17:33:12,215 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue1) GZ80TLU8xKRP.0.0.0 Processing NEW request: SELECT g_0.id, g_0.name, g_0.address FROM MySQLSource.dballo19.customers AS g_0
17:33:12,218 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue1) GZ80TLU8xKRP.0.0.0 Obtained execution
17:33:12,227 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue1) Source-specific command: SELECT g_0.`id`, g_0.`name`, g_0.`address` FROM `dballo19`.`customers` AS g_0
17:33:12,571 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue1) GZ80TLU8xKRP.0.0.0 Executed command
17:33:12,571 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue1) GZ80TLU8xKRP.0.0.0 Processing MORE request
17:33:12,571 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue1) GZ80TLU8xKRP.0.0.0 Getting results from connector
17:33:12,573 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue1) GZ80TLU8xKRP.0.0.0 Obtained last batch, total row count: 10
17:33:12,574 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue2) Request Thread GZ80TLU8xKRP.0 with state PROCESSING
17:33:12,575 DEBUG [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue2) GZ80TLU8xKRP.0.0.0 Remove State
17:33:12,575 DEBUG [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue2) GZ80TLU8xKRP.0.0.0 Processing Close : SELECT g_0.id, g_0.name, g_0.address FROM MySQLSource.dballo19.customers AS g_0
17:33:12,576 DEBUG [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue2) GZ80TLU8xKRP.0.0.0 Closed execution
17:33:12,576 DEBUG [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue2) GZ80TLU8xKRP.0.0.0 Closed connection
17:33:12,577 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue2) QueryProcessor: closing processor
17:33:12,577 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue2) GZ80TLU8xKRP.0 Finished Processing
{code}
was (Author: jstastny):
The requestId is not present in the log mentioned in description:
{code:plain}
17:33:12,046 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) Request Thread GZ80TLU8xKRP.0 with state NEW
17:33:12,047 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) GZ80TLU8xKRP.0 Command has no cache hint and result set cache mode is not on.
17:33:12,047 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) GZ80TLU8xKRP.0 executing SELECT * FROM customers
17:33:12,141 DEBUG [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue0) Initializing the capabilities for mysql5
17:33:12,142 DEBUG [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue0) Setting the database version to 5.5.47
17:33:12,164 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) ProcessTree for GZ80TLU8xKRP.0 AccessNode(0) output=[MySQLSource.dballo19.customers.id, MySQLSource.dballo19.customers.name, MySQLSource.dballo19.customers.address] SELECT g_0.id, g_0.name, g_0.address FROM MySQLSource.dballo19.customers AS g_0
17:33:12,165 DEBUG [org.teiid.TXN_LOG] (Worker0_QueryProcessorQueue0) before getOrCreateTransactionContext:org.teiid.dqp.internal.process.TransactionServerImpl@cdfd35f(GZ80TLU8xKRP)
17:33:12,166 DEBUG [org.teiid.TXN_LOG] (Worker0_QueryProcessorQueue0) after getOrCreateTransactionContext : GZ80TLU8xKRP NONE ID:NONE
17:33:12,176 DEBUG [org.teiid.BUFFER_MGR] (Worker0_QueryProcessorQueue0) Creating TupleBuffer: 0 [MySQLSource.dballo19.customers.id, MySQLSource.dballo19.customers.name, MySQLSource.dballo19.customers.address] [class java.lang.Long, class java.lang.String, class java.lang.String] batch size 512 of type PROCESSOR
17:33:12,183 DEBUG [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue0) GZ80TLU8xKRP.0.0.0 Create State
17:33:12,207 DEBUG [org.teiid.BUFFER_MGR] (Worker0_QueryProcessorQueue0) GZ80TLU8xKRP.0.0.0 Blocking on source query GZ80TLU8xKRP.0.0.0
17:33:12,207 DEBUG [org.teiid.BUFFER_MGR] (Worker0_QueryProcessorQueue0) GZ80TLU8xKRP.0 Blocking on source request(s).
17:33:12,207 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) Request Thread GZ80TLU8xKRP.0 - processor blocked
17:33:12,214 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1) Running task for parent thread Worker0_QueryProcessorQueue0
17:33:12,215 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue1) GZ80TLU8xKRP.0.0.0 Processing NEW request: SELECT g_0.id, g_0.name, g_0.address FROM MySQLSource.dballo19.customers AS g_0
17:33:12,218 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue1) GZ80TLU8xKRP.0.0.0 Obtained execution
17:33:12,227 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue1) Source-specific command: SELECT g_0.`id`, g_0.`name`, g_0.`address` FROM `dballo19`.`customers` AS g_0
17:33:12,571 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue1) GZ80TLU8xKRP.0.0.0 Executed command
17:33:12,571 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue1) GZ80TLU8xKRP.0.0.0 Processing MORE request
17:33:12,571 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue1) GZ80TLU8xKRP.0.0.0 Getting results from connector
17:33:12,573 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue1) GZ80TLU8xKRP.0.0.0 Obtained last batch, total row count: 10
17:33:12,574 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue2) Request Thread GZ80TLU8xKRP.0 with state PROCESSING
17:33:12,575 DEBUG [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue2) GZ80TLU8xKRP.0.0.0 Remove State
17:33:12,575 DEBUG [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue2) GZ80TLU8xKRP.0.0.0 Processing Close : SELECT g_0.id, g_0.name, g_0.address FROM MySQLSource.dballo19.customers AS g_0
17:33:12,576 DEBUG [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue2) GZ80TLU8xKRP.0.0.0 Closed execution
17:33:12,576 DEBUG [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue2) GZ80TLU8xKRP.0.0.0 Closed connection
17:33:12,577 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue2) QueryProcessor: closing processor
17:33:12,577 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue2) GZ80TLU8xKRP.0 Finished Processing
{code}
> Add the query requestID to all lines for that query in the logs
> ---------------------------------------------------------------
>
> Key: TEIID-3582
> URL: https://issues.jboss.org/browse/TEIID-3582
> Project: Teiid
> Issue Type: Enhancement
> Components: Server
> Affects Versions: 8.12.5
> Reporter: Van Halbert
> Fix For: 8.12.2, 8.13
>
>
> Currently when running a query, the logs show the requestId on most of the lines but not all, especially CONNECTOR.
> for example, the following line doesn't have it:
> 14:13:34,071 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue13) Source-specific command: ....
> That's the problem. When lots of queries are running, user wants to grep through the log for that requestid and get just that one query's stuff.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 12 months
[JBoss JIRA] (TEIID-3582) Add the query requestID to all lines for that query in the logs
by Jan Stastny (JIRA)
[ https://issues.jboss.org/browse/TEIID-3582?page=com.atlassian.jira.plugin... ]
Jan Stastny updated TEIID-3582:
-------------------------------
Affects Version/s: 8.12.5
> Add the query requestID to all lines for that query in the logs
> ---------------------------------------------------------------
>
> Key: TEIID-3582
> URL: https://issues.jboss.org/browse/TEIID-3582
> Project: Teiid
> Issue Type: Enhancement
> Components: Server
> Affects Versions: 8.12.5
> Reporter: Van Halbert
> Fix For: 8.12.2, 8.13
>
>
> Currently when running a query, the logs show the requestId on most of the lines but not all, especially CONNECTOR.
> for example, the following line doesn't have it:
> 14:13:34,071 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue13) Source-specific command: ....
> That's the problem. When lots of queries are running, user wants to grep through the log for that requestid and get just that one query's stuff.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 12 months
[JBoss JIRA] (TEIID-3582) Add the query requestID to all lines for that query in the logs
by Jan Stastny (JIRA)
[ https://issues.jboss.org/browse/TEIID-3582?page=com.atlassian.jira.plugin... ]
Jan Stastny reopened TEIID-3582:
--------------------------------
The requestId is not present in the log mentioned in description:
{code:plain}
17:33:12,046 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) Request Thread GZ80TLU8xKRP.0 with state NEW
17:33:12,047 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) GZ80TLU8xKRP.0 Command has no cache hint and result set cache mode is not on.
17:33:12,047 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) GZ80TLU8xKRP.0 executing SELECT * FROM customers
17:33:12,141 DEBUG [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue0) Initializing the capabilities for mysql5
17:33:12,142 DEBUG [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue0) Setting the database version to 5.5.47
17:33:12,164 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) ProcessTree for GZ80TLU8xKRP.0 AccessNode(0) output=[MySQLSource.dballo19.customers.id, MySQLSource.dballo19.customers.name, MySQLSource.dballo19.customers.address] SELECT g_0.id, g_0.name, g_0.address FROM MySQLSource.dballo19.customers AS g_0
17:33:12,165 DEBUG [org.teiid.TXN_LOG] (Worker0_QueryProcessorQueue0) before getOrCreateTransactionContext:org.teiid.dqp.internal.process.TransactionServerImpl@cdfd35f(GZ80TLU8xKRP)
17:33:12,166 DEBUG [org.teiid.TXN_LOG] (Worker0_QueryProcessorQueue0) after getOrCreateTransactionContext : GZ80TLU8xKRP NONE ID:NONE
17:33:12,176 DEBUG [org.teiid.BUFFER_MGR] (Worker0_QueryProcessorQueue0) Creating TupleBuffer: 0 [MySQLSource.dballo19.customers.id, MySQLSource.dballo19.customers.name, MySQLSource.dballo19.customers.address] [class java.lang.Long, class java.lang.String, class java.lang.String] batch size 512 of type PROCESSOR
17:33:12,183 DEBUG [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue0) GZ80TLU8xKRP.0.0.0 Create State
17:33:12,207 DEBUG [org.teiid.BUFFER_MGR] (Worker0_QueryProcessorQueue0) GZ80TLU8xKRP.0.0.0 Blocking on source query GZ80TLU8xKRP.0.0.0
17:33:12,207 DEBUG [org.teiid.BUFFER_MGR] (Worker0_QueryProcessorQueue0) GZ80TLU8xKRP.0 Blocking on source request(s).
17:33:12,207 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) Request Thread GZ80TLU8xKRP.0 - processor blocked
17:33:12,214 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1) Running task for parent thread Worker0_QueryProcessorQueue0
17:33:12,215 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue1) GZ80TLU8xKRP.0.0.0 Processing NEW request: SELECT g_0.id, g_0.name, g_0.address FROM MySQLSource.dballo19.customers AS g_0
17:33:12,218 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue1) GZ80TLU8xKRP.0.0.0 Obtained execution
17:33:12,227 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue1) Source-specific command: SELECT g_0.`id`, g_0.`name`, g_0.`address` FROM `dballo19`.`customers` AS g_0
17:33:12,571 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue1) GZ80TLU8xKRP.0.0.0 Executed command
17:33:12,571 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue1) GZ80TLU8xKRP.0.0.0 Processing MORE request
17:33:12,571 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue1) GZ80TLU8xKRP.0.0.0 Getting results from connector
17:33:12,573 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue1) GZ80TLU8xKRP.0.0.0 Obtained last batch, total row count: 10
17:33:12,574 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue2) Request Thread GZ80TLU8xKRP.0 with state PROCESSING
17:33:12,575 DEBUG [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue2) GZ80TLU8xKRP.0.0.0 Remove State
17:33:12,575 DEBUG [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue2) GZ80TLU8xKRP.0.0.0 Processing Close : SELECT g_0.id, g_0.name, g_0.address FROM MySQLSource.dballo19.customers AS g_0
17:33:12,576 DEBUG [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue2) GZ80TLU8xKRP.0.0.0 Closed execution
17:33:12,576 DEBUG [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue2) GZ80TLU8xKRP.0.0.0 Closed connection
17:33:12,577 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue2) QueryProcessor: closing processor
17:33:12,577 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue2) GZ80TLU8xKRP.0 Finished Processing
{code}
> Add the query requestID to all lines for that query in the logs
> ---------------------------------------------------------------
>
> Key: TEIID-3582
> URL: https://issues.jboss.org/browse/TEIID-3582
> Project: Teiid
> Issue Type: Enhancement
> Components: Server
> Affects Versions: 8.12.5
> Reporter: Van Halbert
> Fix For: 8.12.2, 8.13
>
>
> Currently when running a query, the logs show the requestId on most of the lines but not all, especially CONNECTOR.
> for example, the following line doesn't have it:
> 14:13:34,071 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue13) Source-specific command: ....
> That's the problem. When lots of queries are running, user wants to grep through the log for that requestid and get just that one query's stuff.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 12 months
[JBoss JIRA] (TEIID-4120) Teiid on Oracle Spatial generates incorrect SQL
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4120?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-4120:
----------------------------------
Fix Version/s: 8.13.4
(was: 8.13.3)
> Teiid on Oracle Spatial generates incorrect SQL
> -----------------------------------------------
>
> Key: TEIID-4120
> URL: https://issues.jboss.org/browse/TEIID-4120
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Driver
> Affects Versions: 8.13.3
> Reporter: Mike Houwers
> Assignee: Steven Hawkins
> Fix For: 9.0, 8.12.5, 8.13.4
>
>
> Attempting to run a spatial query using SDO_RELATE in Teiid against an Oracle database seems to generate incorrect SQL. The query in "Steps to Reproduce" results in the following SQL being generated by Teiid:
> {code}
> SELECT c_0 FROM (SELECT g_0."ID" AS c_0 FROM "FOO" g_0 WHERE SDO_RELATE(g_0."FOOTPRINT", g_0."FOOTPRINT", 'mask=contains') = 1) WHERE ROWNUM <= 100
> {code}
> Note the test for the return from SDO_RELATE = 1. This should be a boolean function, so the test for =1 is incorrect, and should be ='TRUE'. The following warning is in the Teiid log:
> {code}
> 14:37:11,934 WARN [org.teiid.CONNECTOR] (Worker5_QueryProcessorQueue17) YRwvNQVeMEzW Connector worker process failed for atomic-request=YRwvNQVeMEzW.5.0.3: org.teiid.translator.jdbc.JDBCExecutionException: 1722 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT c_0 FROM (SELECT g_0."ID" AS c_0 FROM "FOO" g_0 WHERE SDO_RELATE(g_0."FOOTPRINT", g_0."FOOTPRINT", 'mask=contains') = 1) WHERE ROWNUM <= 100]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:359)
> 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.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> at com.sun.proxy.$Proxy56.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> at java.util.concurrent.FutureTask.run(FutureTask.java:262)
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
> 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: java.sql.SQLSyntaxErrorException: ORA-01722: invalid number
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
> at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
> at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
> at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
> at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
> at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
> at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:863)
> at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153)
> at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275)
> at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)
> at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3620)
> at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:504)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123)
> ... 18 more
> {code}
> Running the generated SQL in SQLDeveloper results in the error:
> {code}
> ORA-01722: invalid number
> 01722. 00000 - "invalid number"
> *Cause:
> *Action:
> {code}
> Altering the generated SQL in SQLDeveloper to be:
> {code}
> SELECT g_0."ID" AS c_0 FROM "FOO" g_0 WHERE SDO_RELATE(g_0."FOOTPRINT", g_0.footprint, 'mask=contains') = 'TRUE';
> {code}
> returns the expected results.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 12 months
[JBoss JIRA] (TEIID-4120) Teiid on Oracle Spatial generates incorrect SQL
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4120?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-4120.
-----------------------------------
Resolution: Done
Added logic to look specifically for the spatial function comparisons and use the string rather than numeric boolean.
> Teiid on Oracle Spatial generates incorrect SQL
> -----------------------------------------------
>
> Key: TEIID-4120
> URL: https://issues.jboss.org/browse/TEIID-4120
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Driver
> Affects Versions: 8.13.3
> Reporter: Mike Houwers
> Assignee: Steven Hawkins
> Fix For: 9.0, 8.12.5, 8.13.3
>
>
> Attempting to run a spatial query using SDO_RELATE in Teiid against an Oracle database seems to generate incorrect SQL. The query in "Steps to Reproduce" results in the following SQL being generated by Teiid:
> {code}
> SELECT c_0 FROM (SELECT g_0."ID" AS c_0 FROM "FOO" g_0 WHERE SDO_RELATE(g_0."FOOTPRINT", g_0."FOOTPRINT", 'mask=contains') = 1) WHERE ROWNUM <= 100
> {code}
> Note the test for the return from SDO_RELATE = 1. This should be a boolean function, so the test for =1 is incorrect, and should be ='TRUE'. The following warning is in the Teiid log:
> {code}
> 14:37:11,934 WARN [org.teiid.CONNECTOR] (Worker5_QueryProcessorQueue17) YRwvNQVeMEzW Connector worker process failed for atomic-request=YRwvNQVeMEzW.5.0.3: org.teiid.translator.jdbc.JDBCExecutionException: 1722 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT c_0 FROM (SELECT g_0."ID" AS c_0 FROM "FOO" g_0 WHERE SDO_RELATE(g_0."FOOTPRINT", g_0."FOOTPRINT", 'mask=contains') = 1) WHERE ROWNUM <= 100]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:359)
> 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.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> at com.sun.proxy.$Proxy56.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> at java.util.concurrent.FutureTask.run(FutureTask.java:262)
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
> 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: java.sql.SQLSyntaxErrorException: ORA-01722: invalid number
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
> at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
> at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
> at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
> at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
> at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
> at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:863)
> at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153)
> at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275)
> at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)
> at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3620)
> at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:504)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123)
> ... 18 more
> {code}
> Running the generated SQL in SQLDeveloper results in the error:
> {code}
> ORA-01722: invalid number
> 01722. 00000 - "invalid number"
> *Cause:
> *Action:
> {code}
> Altering the generated SQL in SQLDeveloper to be:
> {code}
> SELECT g_0."ID" AS c_0 FROM "FOO" g_0 WHERE SDO_RELATE(g_0."FOOTPRINT", g_0.footprint, 'mask=contains') = 'TRUE';
> {code}
> returns the expected results.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 12 months