[ 
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]).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)

Reply via email to