Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-5283

Recursive WITH query results in "java.lang.ClassCastException: org.teiid.query.sql.lang.Query cannot be cast to org.teiid.query.sql.lang.SetQuery"

XMLWordPrintable

    • Hide

      Deploy VDB:
      <?xml version="1.0" encoding="UTF-8" standalone="no"?>
      <vdb name="recursiveWith" version="1">
      <description/>
      <property name="validationDateTime" value="Mon Mar 12 08:21:08 CDT 2018"/>
      <property name="validationVersion" value="8.12.5"/>
      <model name="testSource">
      <source name="testSource" translator-name="loopback"/>
      <metadata type="DDL"><![CDATA[

      CREATE FOREIGN TABLE table1 (
      column1 string(4000),
      column2 string(4000)
      ) OPTIONS(NAMEINSOURCE 'table1', UPDATABLE 'TRUE');

      CREATE FOREIGN TABLE table2 (
      column3 string(4000),
      column4 string(4000)
      ) OPTIONS(NAMEINSOURCE 'table2', UPDATABLE 'TRUE');

      ]]></metadata>
      </model>
      </vdb>

      Execute query:
      WITH table3 (column5,column6) AS (
      SELECT column1, column2 FROM table1
      UNION ALL
      SELECT column1,column2 FROM table1 INNER JOIN table3 ON table1.column1 = table3.column5
      )
      SELECT column3, column4 FROM table2 WHERE column3 IN (SELECT column5 FROM table3)

      Show
      Deploy VDB: <?xml version="1.0" encoding="UTF-8" standalone="no"?> <vdb name="recursiveWith" version="1"> <description/> <property name="validationDateTime" value="Mon Mar 12 08:21:08 CDT 2018"/> <property name="validationVersion" value="8.12.5"/> <model name="testSource"> <source name="testSource" translator-name="loopback"/> <metadata type="DDL"><![CDATA[ CREATE FOREIGN TABLE table1 ( column1 string(4000), column2 string(4000) ) OPTIONS(NAMEINSOURCE 'table1', UPDATABLE 'TRUE'); CREATE FOREIGN TABLE table2 ( column3 string(4000), column4 string(4000) ) OPTIONS(NAMEINSOURCE 'table2', UPDATABLE 'TRUE'); ]]></metadata> </model> </vdb> Execute query: WITH table3 (column5,column6) AS ( SELECT column1, column2 FROM table1 UNION ALL SELECT column1,column2 FROM table1 INNER JOIN table3 ON table1.column1 = table3.column5 ) SELECT column3, column4 FROM table2 WHERE column3 IN (SELECT column5 FROM table3)

      Executing a query using a recursive WITH clause results in the error:
      ERROR [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue29) TEIID30019 Unexpected exception for request juY1MrNVs8vD.7: java.lang.ClassCastException: org.teiid.query.sql.lang.Query cannot be cast to org.teiid.query.sql.lang.SetQuery
      at org.teiid.query.optimizer.relational.RelationalPlanner.planWith(RelationalPlanner.java:327) [teiid-engine-8.12.11.6_4-redhat-64-12.jar:8.12.11.6_4-redhat-64-12]
      at org.teiid.query.optimizer.relational.RelationalPlanner.optimize(RelationalPlanner.java:208) [teiid-engine-8.12.11.6_4-redhat-64-12.jar:8.12.11.6_4-redhat-64-12]
      at org.teiid.query.optimizer.QueryOptimizer.optimizePlan(QueryOptimizer.java:159) [teiid-engine-8.12.11.6_4-redhat-64-12.jar:8.12.11.6_4-redhat-64-12]
      at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:448) [teiid-engine-8.12.11.6_4-redhat-64-12.jar:8.12.11.6_4-redhat-64-12]
      at org.teiid.dqp.internal.process.Request.processRequest(Request.java:476) [teiid-engine-8.12.11.6_4-redhat-64-12.jar:8.12.11.6_4-redhat-64-12]
      at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:642) [teiid-engine-8.12.11.6_4-redhat-64-12.jar:8.12.11.6_4-redhat-64-12]
      at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:337) [teiid-engine-8.12.11.6_4-redhat-64-12.jar:8.12.11.6_4-redhat-64-12]
      at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) [teiid-engine-8.12.11.6_4-redhat-64-12.jar:8.12.11.6_4-redhat-64-12]
      at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:274) [teiid-engine-8.12.11.6_4-redhat-64-12.jar:8.12.11.6_4-redhat-64-12]
      at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:284) [teiid-engine-8.12.11.6_4-redhat-64-12.jar:8.12.11.6_4-redhat-64-12]
      at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.12.11.6_4-redhat-64-12.jar:8.12.11.6_4-redhat-64-12]
      at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) [teiid-engine-8.12.11.6_4-redhat-64-12.jar:8.12.11.6_4-redhat-64-12]
      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [rt.jar:1.8.0_151]
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [rt.jar:1.8.0_151]
      at java.lang.Thread.run(Thread.java:748) [rt.jar:1.8.0_151]

              rhn-engineering-shawkins Steven Hawkins
              rhn-support-mshirley Marc Shirley (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Created:
                Updated:
                Resolved: