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

[Jan Stastny (JIRA)]

Jan Stastny created TEIID-5401:
----------------------------------

             Summary: 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)