[jira] [Closed] (IGNITE-4518) Wrong WHERE clause generated by org.apache.ignite.cache.store.jdbc.dialect.BasicJdbcDialect#loadCacheRangeQuery for upper bounds

["Andrey Novikov (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/IGNITE-4518?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Andrey Novikov closed IGNITE-4518.
----------------------------------
    Assignee:     (was: 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
>             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()) {
>                         sb.append(cols[j]).append(" <= ? ");
>                     } else {
>                         sb.append(cols[j]).append(" < ? ");
>                     }
>                 } else {
>                     sb.append(cols[j]).append(" = ? AND ");
>                 }
>             }
>             if (cnt != 1) {
>                 sb.append(" OR ");
>             }
>         }
>         sb.append(")");
> {code}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)