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

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}