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

INNER and LEFT joins of CTEs fail or return incorrect results



      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:

      CTE1 as (
                  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:

      CTE1 as (
                  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 (
                  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




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


                • Created: