[teiid-issues] [JBoss JIRA] (TEIID-5401) MaxDependentInPredicates prevents IN criteria pushdown in Prepared Statement

[Jan Stastny (JIRA)]

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

Jan Stastny commented on TEIID-5401:
------------------------------------

It seems like TEIID-4901 is not applied in case of Prepared Statement.

I can reproduce the issue even without setting MaxDependentInPredicates property, just by issuing a long enough IN criteria. The query is never processed in parallel.

> MaxDependentInPredicates prevents IN criteria pushdown in Prepared Statement
> ----------------------------------------------------------------------------
>
>                 Key: TEIID-5401
>                 URL: https://issues.jboss.org/browse/TEIID-5401
>             Project: Teiid
>          Issue Type: Bug
>          Components: Query Engine
>    Affects Versions: 8.12.14.6_4
>            Reporter: Jan Stastny
>            Assignee: Steven Hawkins
>
> When user sets translator override property MaxDependentInPredicates, then IN criteria is not pushed into underlying datasource, but only when using Prepared Statement.
> The pushdown is not dependent on the limit vs IN criteria size. Simply, if the property is set, then the pushdown is not performed.
> {code:xml|title=vdb}
> <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
> <vdb name="vdb-name" version="1">
>     <model name="pm1" type="PHYSICAL">
>         <source name="pm1" connection-jndi-name="java:/ds"
>             translator-name="mysql5-override" />
>         <metadata type="DDL">
>         <![CDATA[
>             CREATE FOREIGN TABLE g1(e1 integer, e2 string);
>         ]]>
>         </metadata>
>     </model>
>     <translator name="mysql5-override" type="mysql5">
>         <property name="MaxInCriteriaSize" value="2" />
>         <property name="MaxDependentInPredicates" value="2" />
>     </translator>
> </vdb>
> {code}
> {code:sql|title=query}
> SELECT e2 FROM g1 WHERE e2 IN ('a', 'b', 'c', 'd', 'e', 'f') AND e1<5 ORDER BY e2
> {code}
> pushed as 2 src commands:
> {code:sql}
> SELECT g_0.e2 AS c_0 FROM g1 AS g_0 WHERE g_0.e1 < 5 AND (g_0.e2 IN ('a', 'b') OR g_0.e2 IN ('c', 'd'));
> SELECT g_0.e2 AS c_0 FROM g1 AS g_0 WHERE g_0.e1 < 5 AND g_0.e2 IN ('e', 'f');
> {code}
> {code:java|title=preparedstatement}
> PreparedStatement ps = con
>                     .prepareStatement("SELECT e2 FROM g1 WHERE e2 IN (?, ?, ?, ?, ?, ?) AND e1<? ORDER BY e2");
> ps.setString(1, "a");
> ps.setString(2, "b");
> ps.setString(3, "c");
> ps.setString(4, "d");
> ps.setString(5, "e");
> ps.setString(6, "f");
> ps.setInt(7, 5);
> ResultSet rs2 = ps.executeQuery();
> {code}
> pushed as single SRC Command:
> {code:sql}
> SELECT g_0.e2 FROM g1 AS g_0 WHERE g_0.e1 < ?
> {code}
> when I remove the 'MaxDependentInPredicates' property, the ps gets pushed as:
> {code:sql}
> SELECT g_0.e2 AS c_0 FROM g1 AS g_0 WHERE (g_0.e2 IN (?, ?) OR g_0.e2 IN (?, ?) OR g_0.e2 IN (?, ?)) AND g_0.e1 < ? ORDER BY c_0
> {code}



--
This message was sent by Atlassian JIRA
(v7.5.0#75005)