-
1. Re: WITH Clause is processed incorrectly
shawkins Mar 20, 2013 8:24 AM (in response to blaxell)What is the structure of the address table? Can you provide the query plan? And have you tried this in 8.3?
Steve
-
2. Re: WITH Clause is processed incorrectly
blaxell Mar 21, 2013 10:27 AM (in response to shawkins)Hi Steve,
below is the exec plan of the query.
LimitNode
+ Output Columns:
0: AddressID (long)
1: AddressLine1 (string)
2: AddressLine2 (string)
3: City (string)
4: PostalCode (string)
5: Phone (string)
6: StateProvinceID (long)
7: CountryRegionCode (string)
8: ModifiedDate (timestamp)
9: rowguid (string)
+ Statistics:
0: Node Output Rows: 0
1: Node Process Time: 0
2: Node Cumulative Process Time: 0
3: Node Cumulative Next Batch Process Time: 0
4: Node Next Batch Calls: 0
5: Node Blocks: 0
+ Cost Estimates:Estimated Node Cardinality: 100.0
+ Child 0:
AccessNode
+ Output Columns:
0: AddressID (long)
1: AddressLine1 (string)
2: AddressLine2 (string)
3: City (string)
4: PostalCode (string)
5: Phone (string)
6: StateProvinceID (long)
7: CountryRegionCode (string)
8: ModifiedDate (timestamp)
9: rowguid (string)
+ Statistics:
0: Node Output Rows: 0
1: Node Process Time: 0
2: Node Cumulative Process Time: 0
3: Node Cumulative Next Batch Process Time: 0
4: Node Next Batch Calls: 0
5: Node Blocks: 0
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Query:SELECT t.AddressID, t.AddressLine1, t.AddressLine2, t.City, t.PostalCode, t.Phone, t.StateProvinceID, t.CountryRegionCode, t.ModifiedDate, t.rowguid FROM t
+ Model Name:__TEMP__
+ Row Offset:null
+ Row Limit:100
+ With Subplan 0:
AccessNode
+ Output Columns:
0: AddressID (long)
1: AddressLine1 (string)
2: AddressLine2 (string)
3: City (string)
4: PostalCode (string)
5: Phone (string)
6: StateProvinceID (long)
7: CountryRegionCode (string)
8: ModifiedDate (timestamp)
9: rowguid (string)
+ Statistics:
0: Node Output Rows: 0
1: Node Process Time: 0
2: Node Cumulative Process Time: 0
3: Node Cumulative Next Batch Process Time: 0
4: Node Next Batch Calls: 0
5: Node Blocks: 0
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Query:SELECT g_0.AddressID, g_0.AddressLine1, g_0.AddressLine2, g_0.City, g_0.PostalCode, g_0.Phone, g_0.StateProvinceID, g_0.CountryRegionCode, g_0.ModifiedDate, g_0.rowguid FROM crm.address AS g_0
+ Model Name:crm
+ With:t (t.AddressID, t.AddressLine1, t.AddressLine2, t.City, t.PostalCode, t.Phone, t.StateProvinceID, t.CountryRegionCode, t.ModifiedDate, t.rowguid) AS (SELECT * FROM crm.address)
+ DV_EXT0
I also get sometimes the following exception insetad of "reached max source concurrency of 6":
15:18:57,353 WARN [org.teiid.CONNECTOR] (Worker2_QueryProcessorQueue310) Connector worker process failed for atomic-request=5GomusEPLJ3e.6.0.71:
org.teiid.translator.TranslatorException: TEIID11009 java.sql.SQLException: javax.resource.ResourceException: IJ000453: Unable to get managed conn
ection for java:/mysql-ds
at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:231)
at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:57)
at org.teiid.translator.ExecutionFactory.getConnection(ExecutionFactory.java:188) [teiid-api-8.1.0.Final.jar:8.1.0.Final]
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:223) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:432) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:170) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:167) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303) [rt.jar:1.6.0_33]
at java.util.concurrent.FutureTask.run(FutureTask.java:138) [rt.jar:1.6.0_33]
at org.teiid.dqp.internal.process.DQPCore$FutureWork.run(DQPCore.java:125) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:249) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:123) [teiid-engine-8.1.0.Final.jar:8.1.
0.Final]
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:298) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) [rt.jar:1.6.0_33]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) [rt.jar:1.6.0_33]
at java.lang.Thread.run(Thread.java:662) [rt.jar:1.6.0_33]
Caused by: java.sql.SQLException: javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:/mysql-ds
at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:137)
at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:229)
... 15 more
Caused by: javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:/mysql-ds
at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractConnectionManager.java:390) [ironjacamar-co
re-impl-1.0.9.Final.jar:1.0.9.Final]
at org.jboss.jca.core.connectionmanager.tx.TxConnectionManagerImpl.getManagedConnection(TxConnectionManagerImpl.java:368) [ironjacamar-cor
e-impl-1.0.9.Final.jar:1.0.9.Final]
at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.allocateConnection(AbstractConnectionManager.java:464) [ironjacamar-core
-impl-1.0.9.Final.jar:1.0.9.Final]
at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:129)
... 16 more
Caused by: javax.resource.ResourceException: IJ000655: No managed connections available within configured blocking timeout (120000 [ms])
at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool.getConnection(SemaphoreArrayListManagedConnection
Pool.java:377) [ironjacamar-core-impl-1.0.9.Final.jar:1.0.9.Final]
at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getSimpleConnection(AbstractPool.java:397) [ironjacamar-core-impl-1.0.9.Final.ja
r:1.0.9.Final]
at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getConnection(AbstractPool.java:365) [ironjacamar-core-impl-1.0.9.Final.jar:1.0.
9.Final]
at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractConnectionManager.java:329) [ironjacamar-co
re-impl-1.0.9.Final.jar:1.0.9.Final]
... 19 more
the connection itself is defined as:
<datasource jndi-name="java:/mysql-ds" pool-name="mysqlDS" use-java-context="true">
<connection-url>jdbc:mysql://${dv.mysql.host}:${dv.mysql.port}/crm?useCursorFetch=true&defaultFetchSize=1000&zeroDateTimeBehavior=convertToNull</connection-url>
<driver>mysql</driver>
<pool>
<min-pool-size>2</min-pool-size>
<max-pool-size>70</max-pool-size>
</pool>
<security>
<user-name>${dv.mysql.user}</user-name>
<password>${dv.mysql.pwd}</password>
</security>
<validation>
<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/>
<exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>
</validation>
<timeout>
<blocking-timeout-millis>120000</blocking-timeout-millis>
<idle-timeout-minutes>5</idle-timeout-minutes>
</timeout>
</datasource>
-
3. Re: WITH Clause is processed incorrectly
shawkins Mar 22, 2013 10:55 AM (in response to blaxell)I do see an issue with the blocked evaluations of the with clause. This is causing the the initial query to be executed multiple times and likely is leading to the source concurrency exception. Can you log an issue?
Steve
-