3 Replies Latest reply on Nov 5, 2013 12:03 PM by Brandon Tweed

    Certain Types of CTE Queries Cause Looping?

    Brandon Tweed Newbie

      I'm working with a translator that has support for common table expressions (CTEs) turned off. When I issue a query containing WITH, the translator appears to repeatedly execute part of the query indefinitely. For example, I can use the following query in my development environment to reproduce the issue:

       

      WITH PIZZA AS ( SELECT KEY_STACKNAME FROM performance_netmaster.TCPPORT)  SELECT KEY_STACKNAME FROM PIZZA

       

      Issuing this query gives the following plan information:

       

      ----------------------------------------------------------------------------

      OPTIMIZATION COMPLETE:

      PROCESSOR PLAN:

      WITH

      AccessNode(0) output=[KEY_STACKNAME] SELECT performance_netmaster.TCPPORT.KEY_STACKNAME FROM performance_netmaster.TCPPORT

      AccessNode(1) output=[KEY_STACKNAME] SELECT PIZZA.KEY_STACKNAME FROM PIZZA

       

      I receive statements like the following in the log that tell me the same query is being executed repeatedly:

       

      28 Oct 2013 09:42:22,714 EDT INFO  [com.ca.chorus.teiid.safety.SafetyHarnessExecution] (Worker12_QueryProcessorQueue895) Execute SELECT TCPPORT.KEY_STACKNAME FROM TCPPORT against com.ca.chorus.teiid.translator.netmaster.NMExecution@15809892 with userid: twebr01

       

       

      Could this behavior be the result of a bug? Is there something specific I could look at to help find the cause of the repeated execution?