[teiid-issues] [JBoss JIRA] (TEIID-1912) Issuing and inner merge join query produces IOException against PostgreSQL database

[Steven Hawkins (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-1912?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12661628#comment-12661628 ] 

Steven Hawkins commented on TEIID-1912:
---------------------------------------

Ah, then yes there is a hard limitation of 1^15 - 1 parameters in the query.  Can you try maxDependentInPredicates = 1 with maxInCriteriaSize = 32767?
                
> Issuing and inner merge join query produces IOException against PostgreSQL database
> -----------------------------------------------------------------------------------
>
>                 Key: TEIID-1912
>                 URL: https://issues.jboss.org/browse/TEIID-1912
>             Project: Teiid
>          Issue Type: Bug
>          Components: JDBC Connector
>    Affects Versions: 7.7
>         Environment: Teiid 7.7.0 (built with latest today) in JBoss AS 5.1 against Oracle 10 and PostgreSQL 9.4 databases
>            Reporter: Van Halbert
>            Priority: Minor
>             Fix For: 7.7
>
>         Attachments: oracle11-ds.xml, postgres-8.4-tpcr01-ds.xml, TPC.vdb
>
>
> Running the following inner merge query:
> SELECT ORDERS.O_ORDERKEY, ORDERS.O_ORDERDATE, ORDERS.O_CLERK, CUSTOMER.C_CUSTKEY, CUSTOMER.C_NAME, CUSTOMER.C_ACCTBAL 
> FROM TPCR01_PostgreSQLVirt.ORDERS, TPCR01_OracleVirt.CUSTOMER WHERE (CUSTOMER.C_CUSTKEY = ORDERS.O_CUSTKEY) 
> AND (ORDERS.O_ORDERDATE < '1992-02-06 00:00:00') AND (CUSTOMER.C_ACCTBAL > 5100) 
> produces exception:
> 1 [PSQLException]An I/O error occured while sending to the backend.
> 2 [IOException]Tried to send an out-of-range integer as a 2-byte value: 50000
> 	at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:90)
> 	at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:272)
> 	at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:342)
> 	at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:138)
> 	at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:135)
> 	at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
> 	at java.util.concurrent.FutureTask.run(FutureTask.java:138)
> 	at org.teiid.dqp.internal.process.DQPCore$FutureWork.run(DQPCore.java:118)
> 	at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:232)
> 	at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:118)
> 	at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:288)
> 	at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
> 	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
> 	at java.lang.Thread.run(Thread.java:680)
> From googling the exception, I found the suggestion (i've yet to find where Postgres specifically states the max allowable) to split the parameters into 30,000 chunks.   So I changed the PostgreSQLExecutionFactory by adding the following the CTOR:  setMaxDependentInPredicates(30);    
> and this allowed the query to finish.    Not to say this is the final solution, but it's an option that worked.  

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira