4 Replies Latest reply on Mar 22, 2013 1:32 PM by Steven Hawkins

    WITH Clause is processed incorrectly

    Alex K. Newbie

      When I issue a query on Teiid 8.1 that uses a with clause like following:

       

      with  t as (select * from address)  select * from t

       

      I get an error message "reached max source concurrency of  6"  in logs if the table has some data in it.

      If the table is empty, the empty result is returned after ca 80 seconds.

       

      However this equivalent query returns results in milliseconds:

       

      select * from (select * from address) as t

       

      It seems to be a bug in processing the "with" clause. How can I get it working?`

        • 1. Re: WITH Clause is processed incorrectly
          Steven Hawkins Master

          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
            Alex K. Newbie

            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&amp;defaultFetchSize=1000&amp;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
              Steven Hawkins Master

              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

              • 4. Re: WITH Clause is processed incorrectly
                Steven Hawkins Master

                I went ahead and logged/worked this under https://issues.jboss.org/browse/TEIID-2442

                 

                Steve