[jira] [Assigned] (IGNITE-4518) Wrong WHERE clause generated by org.apache.ignite.cache.store.jdbc.dialect.BasicJdbcDialect#loadCacheRangeQuery for upper bounds
[ https://issues.apache.org/jira/browse/IGNITE-4518?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Alexey Kuznetsov reassigned IGNITE-4518: Assignee: Andrey Novikov (was: Alexey Kuznetsov) Looks good for me. See my comment in GitHub PR review. > Wrong WHERE clause generated by > org.apache.ignite.cache.store.jdbc.dialect.BasicJdbcDialect#loadCacheRangeQuery > for upper bounds > > > Key: IGNITE-4518 > URL: https://issues.apache.org/jira/browse/IGNITE-4518 > Project: Ignite > Issue Type: Bug > Components: SQL >Affects Versions: 1.8 >Reporter: Anghel Botos >Assignee: Andrey Novikov > Fix For: 1.9 > > > Scenario: > * Cache entity with 3 key fields mapped to database as columns {{KC1}}, > {{KC2}}, {{KC3}} > * The following data is in the database table {{MY_TABLE}} (the values 0 to 4 > encoded as binary): > ||KC1||KC2||KC3|| > |0|0|0| > |0|0|1| > |0|1|0| > |0|1|1| > |1|0|0| > When running {{org.apache.ignite.IgniteCache#loadCache(null)}} (i.e. with no > custom SQL query arguments) the following happens: > * In {{org.apache.ignite.cache.store.jdbc.CacheAbstractJdbcStore#loadCache}}, > the query produced by > {{org.apache.ignite.cache.store.jdbc.dialect.JdbcDialect#loadCacheSelectRangeQuery}} > is used to determine the range boundaries for the load. In our case the > query would be: > {{SELECT KC1,KC2,KC3 FROM (SELECT KC1,KC2,KC3, ROWNUM AS rn FROM (SELECT > KC1,KC2,KC3 FROM MY_TABLE ORDER BY KC1,KC2,KC3)) WHERE mod(rn, 2) = 0}} (I > used {{parallelLoadCacheMinThreshold=2}} just as an example so that the > problem is visible for this small data set). > The results of this query are: > ||KC1||KC2||KC3|| > |0|0|1| > |0|1|1| > (which is correct, due to https://issues.apache.org/jira/browse/IGNITE-4163 > being fixed now) > These results will be used by > {{org.apache.ignite.cache.store.jdbc.CacheAbstractJdbcStore#loadCacheRange}} > to generate queries using > {{org.apache.ignite.cache.store.jdbc.dialect.JdbcDialect#loadCacheRangeQuery}} > which will be executed in parallel. Since there were 2 rows in the above > result, this means that the load will occur using 3 ranges/queries: > # No lower bound, upper bound using first row of the selectRangeQuery result > # Lower bound using the first row of the selectRangeQuery result, upper bound > using the second row of the selectRangeQuery result > # Lower bound using the second row of the selectRangeQuery result, no upper > bound > The queries are the following: > # {{SELECT KC1,KC2,KC3 FROM MY_TABLE WHERE (KC1 = ? AND KC2 = ? AND KC3 <= ? > OR KC1 = ? AND KC2 <= ? OR KC1 <= ? )}} > # {{SELECT KC1,KC2,KC3 FROM MY_TABLE WHERE (KC1 = ? AND KC2 = ? AND KC3 > ? > OR KC1 = ? AND KC2 > ? OR KC1 > ? ) AND (KC1 = ? AND KC2 = ? AND KC3 <= ? OR > KC1 = ? AND KC2 <= ? OR KC1 <= ? )}} > # {{SELECT KC1,KC2,KC3 FROM MY_TABLE WHERE (KC1 = ? AND KC2 = ? AND KC3 > ? > OR KC1 = ? AND KC2 > ? OR KC1 > ? )}} > Replacing the first bound in the first query yields the following query: > {{SELECT KC1,KC2,KC3 FROM MY_TABLE WHERE (KC1 = 0 AND KC2 = 0 AND KC3 <= 1 > OR KC1 = 0 AND KC2 <= 0 OR KC1 <= 0 )}}, which executed, yields the > following results: > ||KC1||KC2||KC3|| > |0|0|0| > |0|0|1| > |0|1|0| > |0|1|1| > which is not correct, as the result set should have contained only 2 rows > (due to {{parallelLoadCacheMinThreshold=2}}). Something similar will happen > also to the second query (the one with both lower and upper bound). > The problem arises due to how the upper bound part of the WHERE clause is > being generated, namely the conditions when NOT ALL of the key columns are > constrained. These conditions should use *<* (strictly less), not *<=* (less > or equal). > The correct queries should be (please note the {{OR KC1 = ? AND KC2 < ? OR > KC1 < ?}} part which makes the difference): > # {{SELECT KC1, KC2, KC3 FROM MY_TABLE WHERE (KC1 = ? AND KC2 = ? AND KC3 <= > ? OR KC1 = ? AND KC2 < ? OR KC1 < ? )}} > # {{SELECT KC1, KC2, KC3 FROM MY_TABLE WHERE (KC1 = ? AND KC2 = ? AND KC3 > ? > OR KC1 = ? AND KC2 > ? OR KC1 > ? ) AND (KC1 = ? AND KC2 = ? AND KC3 <= ? OR > KC1 = ? AND KC2 < ? OR KC1 < ? )}} > # {{SELECT KC1, KC2, KC3 FROM MY_TABLE WHERE (KC1 = ? AND KC2 = ? AND KC3 > ? > OR KC1 = ? AND KC2 > ? OR KC1 > ? )}} > My fix to generate the correct queries was to use the following code instead > of > {{org.apache.ignite.cache.store.jdbc.dialect.BasicJdbcDialect#loadCacheRangeQuery:193-205}}: > {code} > sb.append("("); > for (int cnt = keyCols.size(); cnt > 0; cnt--) { > for (int j = 0; j < cnt; j++) { > if (j == cnt - 1) { > if (cnt == keyCols.size()) { > s
[jira] [Assigned] (IGNITE-4518) Wrong WHERE clause generated by org.apache.ignite.cache.store.jdbc.dialect.BasicJdbcDialect#loadCacheRangeQuery for upper bounds
[ https://issues.apache.org/jira/browse/IGNITE-4518?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Andrey Novikov reassigned IGNITE-4518: -- Assignee: Andrey Novikov > Wrong WHERE clause generated by > org.apache.ignite.cache.store.jdbc.dialect.BasicJdbcDialect#loadCacheRangeQuery > for upper bounds > > > Key: IGNITE-4518 > URL: https://issues.apache.org/jira/browse/IGNITE-4518 > Project: Ignite > Issue Type: Bug > Components: SQL >Affects Versions: 1.8 >Reporter: Anghel Botos >Assignee: Andrey Novikov > > Scenario: > * Cache entity with 3 key fields mapped to database as columns {{KC1}}, > {{KC2}}, {{KC3}} > * The following data is in the database table {{MY_TABLE}} (the values 0 to 4 > encoded as binary): > ||KC1||KC2||KC3|| > |0|0|0| > |0|0|1| > |0|1|0| > |0|1|1| > |1|0|0| > When running {{org.apache.ignite.IgniteCache#loadCache(null)}} (i.e. with no > custom SQL query arguments) the following happens: > * In {{org.apache.ignite.cache.store.jdbc.CacheAbstractJdbcStore#loadCache}}, > the query produced by > {{org.apache.ignite.cache.store.jdbc.dialect.JdbcDialect#loadCacheSelectRangeQuery}} > is used to determine the range boundaries for the load. In our case the > query would be: > {{SELECT KC1,KC2,KC3 FROM (SELECT KC1,KC2,KC3, ROWNUM AS rn FROM (SELECT > KC1,KC2,KC3 FROM MY_TABLE ORDER BY KC1,KC2,KC3)) WHERE mod(rn, 2) = 0}} (I > used {{parallelLoadCacheMinThreshold=2}} just as an example so that the > problem is visible for this small data set). > The results of this query are: > ||KC1||KC2||KC3|| > |0|0|1| > |0|1|1| > (which is correct, due to https://issues.apache.org/jira/browse/IGNITE-4163 > being fixed now) > These results will be used by > {{org.apache.ignite.cache.store.jdbc.CacheAbstractJdbcStore#loadCacheRange}} > to generate queries using > {{org.apache.ignite.cache.store.jdbc.dialect.JdbcDialect#loadCacheRangeQuery}} > which will be executed in parallel. Since there were 2 rows in the above > result, this means that the load will occur using 3 ranges/queries: > # No lower bound, upper bound using first row of the selectRangeQuery result > # Lower bound using the first row of the selectRangeQuery result, upper bound > using the second row of the selectRangeQuery result > # Lower bound using the second row of the selectRangeQuery result, no upper > bound > The queries are the following: > # {{SELECT KC1,KC2,KC3 FROM MY_TABLE WHERE (KC1 = ? AND KC2 = ? AND KC3 <= ? > OR KC1 = ? AND KC2 <= ? OR KC1 <= ? )}} > # {{SELECT KC1,KC2,KC3 FROM MY_TABLE WHERE (KC1 = ? AND KC2 = ? AND KC3 > ? > OR KC1 = ? AND KC2 > ? OR KC1 > ? ) AND (KC1 = ? AND KC2 = ? AND KC3 <= ? OR > KC1 = ? AND KC2 <= ? OR KC1 <= ? )}} > # {{SELECT KC1,KC2,KC3 FROM MY_TABLE WHERE (KC1 = ? AND KC2 = ? AND KC3 > ? > OR KC1 = ? AND KC2 > ? OR KC1 > ? )}} > Replacing the first bound in the first query yields the following query: > {{SELECT KC1,KC2,KC3 FROM MY_TABLE WHERE (KC1 = 0 AND KC2 = 0 AND KC3 <= 1 > OR KC1 = 0 AND KC2 <= 0 OR KC1 <= 0 )}}, which executed, yields the > following results: > ||KC1||KC2||KC3|| > |0|0|0| > |0|0|1| > |0|1|0| > |0|1|1| > which is not correct, as the result set should have contained only 2 rows > (due to {{parallelLoadCacheMinThreshold=2}}). Something similar will happen > also to the second query (the one with both lower and upper bound). > The problem arises due to how the upper bound part of the WHERE clause is > being generated, namely the conditions when NOT ALL of the key columns are > constrained. These conditions should use *<* (strictly less), not *<=* (less > or equal). > The correct queries should be (please note the {{OR KC1 = ? AND KC2 < ? OR > KC1 < ?}} part which makes the difference): > # {{SELECT KC1, KC2, KC3 FROM MY_TABLE WHERE (KC1 = ? AND KC2 = ? AND KC3 <= > ? OR KC1 = ? AND KC2 < ? OR KC1 < ? )}} > # {{SELECT KC1, KC2, KC3 FROM MY_TABLE WHERE (KC1 = ? AND KC2 = ? AND KC3 > ? > OR KC1 = ? AND KC2 > ? OR KC1 > ? ) AND (KC1 = ? AND KC2 = ? AND KC3 <= ? OR > KC1 = ? AND KC2 < ? OR KC1 < ? )}} > # {{SELECT KC1, KC2, KC3 FROM MY_TABLE WHERE (KC1 = ? AND KC2 = ? AND KC3 > ? > OR KC1 = ? AND KC2 > ? OR KC1 > ? )}} > My fix to generate the correct queries was to use the following code instead > of > {{org.apache.ignite.cache.store.jdbc.dialect.BasicJdbcDialect#loadCacheRangeQuery:193-205}}: > {code} > sb.append("("); > for (int cnt = keyCols.size(); cnt > 0; cnt--) { > for (int j = 0; j < cnt; j++) { > if (j == cnt - 1) { > if (cnt == keyCols.size()) { > sb.append(cols[j]).append(" <= ? "); > } else { > sb.append(cols[j]).ap