[ https://issues.apache.org/jira/browse/IGNITE-16991?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17538244#comment-17538244 ]
Nikolay Izhikov commented on IGNITE-16991: ------------------------------------------ Just copy of my investigation report from user list: It seems, current behavior is correct, in general. Looks like passing list of values as a parameter is common issue for JDBC SQL queries [1] [2] [3]. Tried to find formal requirements in JDBC spec but didn’t find clear description :) Anyway, If you are interested in details: H2 engine optimize IN clause of your query to Equals query, because it contains only one item [4]. So in time Ignite receive filter it looks like «find string equals to array» - this leads to the exception after [5]. Calcite engine behave a little bit different and returns empty results for the same query. Didn’t look deeper to explain this. I will close ticket [6] as «Won’t fix». Feel free to reopen it. [1] https://www.baeldung.com/spring-jdbctemplate-in-list [2] https://stackoverflow.com/questions/45696465/how-to-pass-list-of-values-as-a-parameter-to-in-clause-using-jdbc-template [3] https://stackoverflow.com/questions/1327074/how-to-execute-in-sql-queries-with-springs-jdbctemplate-effectively [4] https://github.com/h2database/h2database/blob/master/h2/src/main/org/h2/expression/condition/ConditionIn.java#L129 [5] https://issues.apache.org/jira/browse/IGNITE-13056 [6] https://issues.apache.org/jira/browse/IGNITE-16991 > SQL in clause with array arg fails since ignite 2.11 > ---------------------------------------------------- > > Key: IGNITE-16991 > URL: https://issues.apache.org/jira/browse/IGNITE-16991 > Project: Ignite > Issue Type: Bug > Reporter: Nikolay Izhikov > Priority: Major > > Reproducer. > Test passes on ignite-2.10 and fails on ignite-2.11 > {code:java} > /** */ > public class SqlInQueryTest extends GridCommonAbstractTest { > /** */ > private static IgniteEx server; > /** {@inheritDoc} */ > @Override protected void beforeTestsStarted() throws Exception { > super.beforeTestsStarted(); > server = startGrid(0); > } > /** */ > @Test > public void testSelectWithInClause() throws Exception { > sql("CREATE TABLE T1(ID VARCHAR, IID VARCHAR, DATA VARCHAR, PRIMARY > KEY(ID, IID))", null); > for (int i = 0; i < 10; i++) > sql( > "INSERT INTO T1(ID, IID, DATA) VALUES(?, ?, ?)", > new Object[] {String.valueOf(i), String.valueOf(i % 3), "just > data"} > ); > assertEquals(10, sql("SELECT ID, IID FROM T1", null).size()); > // One ID inside IN clause. > assertEquals(1, sql( > "SELECT IID FROM T1 WHERE ID IN (?) AND IID = ?", > new Object[] {Arrays.asList("1").toArray(), "1"} > ).size()); > // Two ID inside IN clause. > assertEquals(2, sql( > "SELECT IID FROM T1 WHERE ID IN ('1', '4') AND IID = ?", > new Object[] {"1"} > ).size()); > // Two ID inside IN clause. Pass as patameters. > //assertEquals(2, sql( > // "SELECT IID FROM T1 WHERE ID IN (?) AND IID = ?", > // new Object[] {Arrays.asList("1", "4").toArray(), "1"}).size() > //); > } > /** */ > private List<List<?>> sql(String sqlText, Object[] args) { > return server.context().query().querySqlFields( > new > SqlFieldsQuery(sqlText).setDistributedJoins(true).setArgs(args), true > ).getAll(); > } > } > {code} -- This message was sent by Atlassian Jira (v8.20.7#820007)