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

JOIN of a table and sub-query containing DISTINCT and table function of a procedural call with arguments fails

    Details

    • Type: Bug
    • Status: Resolved (View Workflow)
    • Priority: Blocker
    • Resolution: Done
    • Affects Version/s: 11.1
    • Fix Version/s: 11.2, 11.0.3, 10.3.5, 8.12.16.6_4, 11.1.2
    • Component/s: Query Engine
    • Labels:
      None
    • Environment:

      teiid-11.1.0 (from 01.09.2018) on WildFly Full 11.0.0.Final (WildFly Core 3.0.8.Final)

    • Steps to Reproduce:
      Hide

      1. Create "test_x" and "test_y" tables in a PostgreSQL DB:

      CREATE TABLE public.test_x
      (
        b boolean
      );
      INSERT INTO public.test_x(
                  b)
          VALUES (true);
      
      CREATE TABLE public.test_y
      (
        i integer
      );
      INSERT INTO public.test_y(
                  i)
          VALUES (2);
      

      2. Add postgresql (in my example I use "test" database) database configurations in standalone-teiid.xml:

                   <datasource jndi-name="java:/test_tables_pg" pool-name="test_tables_pg" enabled="true" use-java-context="true">
                          <connection-url>jdbc:postgresql://localhost:5432/test?charSet=utf8</connection-url>
                          <driver-class>org.postgresql.Driver</driver-class>
                          <driver>org.postgresql</driver>
                          <pool>
                              <min-pool-size>2</min-pool-size>
                              <max-pool-size>70</max-pool-size>
                              <prefill>false</prefill>
                              <use-strict-min>false</use-strict-min>
                              <flush-strategy>FailingConnectionOnly</flush-strategy>
                          </pool>
                          <security>
                              <user-name>postgres</user-name>
                              <password>xxxxxx</password>
                          </security>
                          <validation>
                              <check-valid-connection-sql>select 0</check-valid-connection-sql>
                          </validation>
                          <timeout>
                              <blocking-timeout-millis>120000</blocking-timeout-millis>
                              <idle-timeout-minutes>5</idle-timeout-minutes>
                          </timeout>
                      </datasource>
      

      3. Add in test-vdb.xml java:/test_tables_pg configured in previous step as data source:

          <model name="test_tables_pg">
              <property name="importer.useFullSchemaName" value="false"/>
              <property name="importer.tableTypes" value="TABLE,VIEW"/>
              <property name="importer.importKeys" value="false"/>
              <source name="test_tables_pg" translator-name="myPg" connection-jndi-name="java:/test_tables_pg"/>
          </model>
      
          <translator name="myPg" type="postgresql">
              <property name="SupportsNativeQueries" value="true"/>
          </translator>
      

      4. Add in test-vdb.xml the virtual "pr" procedure:

          <model visible = "true" type = "VIRTUAL" name = "views">
              <metadata type = "DDL"><![CDATA[
              CREATE PROCEDURE pr(arg string) RETURNS (res integer) AS
      	BEGIN
      		SELECT 2;
      	END
              ]]>
              </metadata>
          </model>
      

      5. Comment out

      Unable to find source-code formatter for language: noformat. Available languages are: actionscript, ada, applescript, bash, c, c#, c++, cpp, css, erlang, go, groovy, haskell, html, java, javascript, js, json, lua, none, nyan, objc, perl, php, python, r, rainbow, ruby, scala, sh, sql, swift, visualbasic, xml, yaml
      if (!prepared && requestMsg.getRowLimit() > 0 && command instanceof QueryCommand) {
      

      condition in Request.generatePlan method of engine module or set rowLimit to 0 in debug mode (when running the query from 6. point) via Statement.setMaxRows method not to add Limit clause automatically.

      6. Run the following query:

      SELECT t2.i
      FROM (
      	SELECT DISTINCT 1 a, b from test_tables_pg.test_x) t1,
      	table(CALL "views.pr"(arg => t1.a)
      	) sub
      JOIN test_tables_pg.test_y t2 ON true ;;
      

      A note: if you allow to add the Limit clause automatically in Request.generatePlan method then the query above won't fail.

      Show
      1. Create "test_x" and "test_y" tables in a PostgreSQL DB: CREATE TABLE public .test_x ( b boolean ); INSERT INTO public .test_x( b) VALUES ( true ); CREATE TABLE public .test_y ( i integer ); INSERT INTO public .test_y( i) VALUES (2); 2. Add postgresql (in my example I use "test" database) database configurations in standalone-teiid.xml: <datasource jndi-name= "java:/test_tables_pg" pool-name= "test_tables_pg" enabled= "true" use-java-context= "true" > <connection-url> jdbc:postgresql://localhost:5432/test?charSet=utf8 </connection-url> <driver-class> org.postgresql.Driver </driver-class> <driver> org.postgresql </driver> <pool> <min-pool-size> 2 </min-pool-size> <max-pool-size> 70 </max-pool-size> <prefill> false </prefill> <use-strict-min> false </use-strict-min> <flush-strategy> FailingConnectionOnly </flush-strategy> </pool> <security> <user-name> postgres </user-name> <password> xxxxxx </password> </security> <validation> <check-valid-connection-sql> select 0 </check-valid-connection-sql> </validation> <timeout> <blocking-timeout-millis> 120000 </blocking-timeout-millis> <idle-timeout-minutes> 5 </idle-timeout-minutes> </timeout> </datasource> 3. Add in test-vdb.xml java:/test_tables_pg configured in previous step as data source: <model name= "test_tables_pg" > <property name= "importer.useFullSchemaName" value= "false" /> <property name= "importer.tableTypes" value= "TABLE,VIEW" /> <property name= "importer.importKeys" value= "false" /> <source name= "test_tables_pg" translator-name= "myPg" connection-jndi-name= "java:/test_tables_pg" /> </model> <translator name= "myPg" type= "postgresql" > <property name= "SupportsNativeQueries" value= "true" /> </translator> 4. Add in test-vdb.xml the virtual "pr" procedure: <model visible = "true" type = "VIRTUAL" name = "views" > <metadata type = "DDL" > <![CDATA[ CREATE PROCEDURE pr(arg string) RETURNS (res integer) AS BEGIN SELECT 2; END ]]> </metadata> </model> 5. Comment out Unable to find source-code formatter for language: noformat. Available languages are: actionscript, ada, applescript, bash, c, c#, c++, cpp, css, erlang, go, groovy, haskell, html, java, javascript, js, json, lua, none, nyan, objc, perl, php, python, r, rainbow, ruby, scala, sh, sql, swift, visualbasic, xml, yaml if (!prepared && requestMsg.getRowLimit() > 0 && command instanceof QueryCommand) { condition in Request.generatePlan method of engine module or set rowLimit to 0 in debug mode (when running the query from 6. point) via Statement.setMaxRows method not to add Limit clause automatically. 6. Run the following query: SELECT t2.i FROM ( SELECT DISTINCT 1 a , b from test_tables_pg.test_x) t1, table ( CALL "views.pr" (arg => t1. a ) ) sub JOIN test_tables_pg.test_y t2 ON true ;; A note: if you allow to add the Limit clause automatically in Request.generatePlan method then the query above won't fail.
    • Bugzilla Update:
      Perform

      Description

      JOIN of a table and sub-query containing DISTINCT and table function of a procedural call with arguments, like:

      SELECT t2.i
      FROM (
      	SELECT DISTINCT 1 a, b from test_tables_pg.test_x) t1,
      	table(CALL "views.pr"(arg => t1.a)
      	) sub
      JOIN test_tables_pg.test_y t2 ON true ;;
      

      fails with the following stack trace:

      Unable to find source-code formatter for language: noformat. Available languages are: actionscript, ada, applescript, bash, c, c#, c++, cpp, css, erlang, go, groovy, haskell, html, java, javascript, js, json, lua, none, nyan, objc, perl, php, python, r, rainbow, ruby, scala, sh, sql, swift, visualbasic, xml, yaml
      2018-09-25 17:47:15,024 ERROR [org.teiid.TRANSPORT] (NIO1)  TEIID40113 Unhandled exception, aborting operation: org.teiid.transport.ObjectEncoder$FailedWriteException: org.teiid.core
      .TeiidRuntimeException: TEIID20001 The modeled datatype integer for column 0 doesn't match the runtime type "java.lang.Boolean". Please ensure that the column's modeled datatype matc
      hes the expected data.
              at org.teiid.transport.ObjectEncoder.write(ObjectEncoder.java:132)
              at io.netty.channel.AbstractChannelHandlerContext.invokeWrite0(AbstractChannelHandlerContext.java:738)
              at io.netty.channel.AbstractChannelHandlerContext.invokeWrite(AbstractChannelHandlerContext.java:730)
              at io.netty.channel.AbstractChannelHandlerContext.write(AbstractChannelHandlerContext.java:816)
              at io.netty.channel.AbstractChannelHandlerContext.write(AbstractChannelHandlerContext.java:723)
              at io.netty.channel.ChannelDuplexHandler.write(ChannelDuplexHandler.java:106)
              at io.netty.channel.AbstractChannelHandlerContext.invokeWrite0(AbstractChannelHandlerContext.java:738)
              at io.netty.channel.AbstractChannelHandlerContext.invokeWrite(AbstractChannelHandlerContext.java:730)
              at io.netty.channel.AbstractChannelHandlerContext.access$1900(AbstractChannelHandlerContext.java:38)
              at io.netty.channel.AbstractChannelHandlerContext$AbstractWriteTask.write(AbstractChannelHandlerContext.java:1089)
              at io.netty.channel.AbstractChannelHandlerContext$AbstractWriteTask.run(AbstractChannelHandlerContext.java:1078)
              at io.netty.util.concurrent.AbstractEventExecutor.safeExecute(AbstractEventExecutor.java:163)
              at io.netty.util.concurrent.SingleThreadEventExecutor.runAllTasks(SingleThreadEventExecutor.java:403)
              at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:442)
              at io.netty.util.concurrent.SingleThreadEventExecutor$5.run(SingleThreadEventExecutor.java:858)
              at java.lang.Thread.run(Thread.java:745)
      Caused by: org.teiid.core.TeiidRuntimeException: TEIID20001 The modeled datatype integer for column 0 doesn't match the runtime type "java.lang.Boolean". Please ensure that the colum
      n's modeled datatype matches the expected data.
              at org.teiid.client.BatchSerializer.writeBatch(BatchSerializer.java:874)
              at org.teiid.client.ResultsMessage.writeExternal(ResultsMessage.java:315)
              at java.io.ObjectOutputStream.writeExternalData(ObjectOutputStream.java:1459)
              at java.io.ObjectOutputStream.writeOrdinaryObject(ObjectOutputStream.java:1430)
              at java.io.ObjectOutputStream.writeObject0(ObjectOutputStream.java:1178)
              at java.io.ObjectOutputStream.writeObject(ObjectOutputStream.java:348)
              at org.teiid.net.socket.Message.writeExternal(Message.java:57)
              at java.io.ObjectOutputStream.writeExternalData(ObjectOutputStream.java:1459)
              at java.io.ObjectOutputStream.writeOrdinaryObject(ObjectOutputStream.java:1430)
              at java.io.ObjectOutputStream.writeObject0(ObjectOutputStream.java:1178)
              at java.io.ObjectOutputStream.writeObject(ObjectOutputStream.java:348)
              at org.teiid.transport.ObjectEncoder.write(ObjectEncoder.java:112)
              ... 15 more
      Caused by: java.lang.ClassCastException: java.lang.Boolean cannot be cast to java.lang.Integer
              at org.teiid.client.BatchSerializer$IntColumnSerializer.writeObject(BatchSerializer.java:559)
              at org.teiid.client.BatchSerializer$ColumnSerializer.writeColumn(BatchSerializer.java:530)
              at org.teiid.client.BatchSerializer.writeBatch(BatchSerializer.java:863)
              ... 26 more
      

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                dalex005 Dmitrii Pogorelov
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: