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

INNER and LEFT joins of CTEs fail or return incorrect results

    Details

      Description

      I defined a table "test_a" with the same data and structure in PostgreSQL and MySQL:

      CREATE TABLE test_a(a integer, b integer);
      INSERT INTO test_a VALUES (1, 1);
      INSERT INTO test_a VALUES (1, 2);
      INSERT INTO test_a VALUES (2, 1);
      INSERT INTO test_a VALUES (2, 2);
      INSERT INTO test_a VALUES (3, 2);
      INSERT INTO test_a VALUES (3, 10);
      

      The following query, based on the table in PostgreSQL, fails:

      with
      CTE1 as (
              WITH 
                  CTE11 as (SELECT a from pg.test_a),
                  CTE21 as (select t1.a from CTE11 t1 join CTE11 t2 on t1.a=t2.a),
                  CTE31 as (select a from CTE21)
              SELECT CTE31.a FROM CTE21 join CTE31 on CTE31.a=CTE21.a
          )  
      select * from CTE1
      

      with this exception:

      16:36:28,752 WARN  [org.teiid.CONNECTOR] (Worker13_QueryProcessorQueue114) eYLiZgMIChSF Connector worker process failed for atomic-request=eYLiZgMIChSF.11.2.24: org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: WITH CTE11 (a) AS (SELECT NULL FROM "public"."test_a" AS g_0), CTE21 (a) AS (SELECT g_0.a FROM CTE11 AS g_0, CTE11 AS g_1 WHERE g_0.a = g_1.a) SELECT g_1.a FROM CTE21 AS g_0, CTE21 AS g_1 WHERE g_1.a = g_0.a]
              at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
              at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:365)
              at sun.reflect.GeneratedMethodAccessor94.invoke(Unknown Source)
              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: org.postgresql.util.PSQLException: ERROR: failed to find conversion function from unknown to text
              at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
              at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
              at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
              at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
              at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
              at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
              at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:504)
              at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123)
              ... 17 more
      

      The same query based on the table in MySQL wrongly returns an empty result.
      The main differences is that the query is fully pushed down to PostgreSQL but it isn't in MySQL.

      Regarding wrong results, I am also experiencing a similar problem with the following query:

      with
      CTE1 as (
              WITH 
                  alias as (SELECT a from pg.test_a),
                  alias2 as (select t2.a as a1, t1.a from alias t1 join (SELECT 1 as a) t2 on t1.a=t2.a),
                  CTE31 as (select t2.a as a1 from alias2 t2)
              SELECT CTE31.a1 FROM alias2 join CTE31 on CTE31.a1=alias2.a
          ),
      CTE2 as (
              WITH 
                  alias as (SELECT 1 as a),
                  alias2 as (select t2.a a1, t1.a from alias t1 join (SELECT 1 as a) t2 on t1.a=t2.a),
                  CTE32 as (select t2.a from alias2 t2)
              SELECT CTE32.a FROM alias2 join CTE32 on CTE32.a=alias2.a
          )  
      select * from CTE1 as T1 join CTE2 as T2 on T1.a1=T2.a
      

      It returns 4 rows (as expected) if based on MySQL table but it returns 16 rows if pushed down to PostgreSQL.
      I don't know if the two behaviors are related or not, but I can create a different ticket for the second issue, if needed.

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                fox123 Salvatore R
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: