-
Bug
-
Resolution: Done
-
Major
-
8.12.14.6_4
-
None
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
