Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-5401

MaxDependentInPredicates prevents IN criteria pushdown in Prepared Statement

    Details

      Description

      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.

      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>
      
      query
      SELECT e2 FROM g1 WHERE e2 IN ('a', 'b', 'c', 'd', 'e', 'f') AND e1<5 ORDER BY e2
      

      pushed as 2 src commands:

      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');
      
      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();
      

      pushed as single SRC Command:

      SELECT g_0.e2 FROM g1 AS g_0 WHERE g_0.e1 < ?
      

      when I remove the 'MaxDependentInPredicates' property, the ps gets pushed as:

      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
      

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                jstastny Jan Stastny
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: