Loading...

Type: Bug
Resolution: Done
Priority: Blocker
Fix Version/s: 11.2, 11.0.3, 10.3.5, 8.12.16.6_4, 11.1.2
Affects Version/s: 11.1
Component/s: Query Engine
Labels:
None

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 References:
https://bugzilla.redhat.com/show_bug.cgi?id=1633663

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

Details

Description

Attachments

Easy Agile Planning Poker

Activity

People

Dates