[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:all-tabpanel ]

Steven Hawkins updated TEIID-1912:
----------------------------------

             Fix Version/s: 7.7
                  Priority: Minor  (was: Major)
                  Assignee:     (was: Steven Hawkins)
    Workaround Description: Change the value for setMaxDependentInPredicates to a smaller value, such as 30.
                Workaround: Workaround Exists


Can you provide the link from your search?  With the 30 setting Teiid will split the values into 30 in predicates of 1000 (also a default) values each.  Beyond that multiple queries will be used.  

If there is an actual hard limit we can change the default in the PostgreSQLExecutionFactory.  If you are not using PreparedStatements then this may be dependent on the length of the literal values.  Otherwise any combination of max in and max dependent predicates can be used, but it would be best to know the actual limitations.
                
> 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