[jira] [Commented] (IGNITE-4163) Wrong SQL generated by org.apache.ignite.cache.store.jdbc.dialect.BasicJdbcDialect#loadCacheSelectRangeQuery

["Anghel Botos (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/IGNITE-4163?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15635385#comment-15635385 ] 

Anghel Botos commented on IGNITE-4163:
--------------------------------------

Hi,

After trying out the patched dialect and playing around with it, I'm back with some more information on this. While the query is correct now from a syntactic point of view, it does not produce the expected results. As explained on http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html , *A ROWNUM value is assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation*, so having ROWNUM as part of the sorted sub-query will produce the wrong values in the {{rn}} column (because it's values are assigned before the ordering).

Assume we have a table with two key columns, with the following values:
||KC1|| KC2||
|2|2|
|2|1|
|1|3|
|1|2|
|1|1|
Then {{SELECT KC1, KC2, ROWNUM AS rn FROM MY_TABLE}} yields:
||KC1|| KC2||RN||
|2|2|1|
|2|1|2|
|1|3|3|
|1|2|4|
|1|1|5|
The sub-query as it would be produced by the patched dialect would be {{SELECT KC1, KC2, ROWNUM AS rn FROM MY_TABLE ORDER BY KC1, KC2}}. Executing this query, one obtains:
||KC1|| KC2||RN||
|1|1|5|
|1|2|4|
|1|3|3|
|2|1|2|
|2|2|1|
This is wrong, we need to have {{ROWNUM}} added to the result set after the ordering has been performed, so it needs to go in the outer query to have something like this: {{SELECT KC1, KC2, ROWNUM AS rn FROM (SELECT KC1, KC2 FROM MY_TABLE ORDER BY KC1, KC2)}}. Executing the query one gets the following results:
||KC1|| KC2||RN||
|1|1|1|
|1|2|2|
|1|3|3|
|2|1|4|
|2|2|5|
Now we need to apply the condition {{WHERE mod(RN, parallelLoadCacheMinThreshold)=0}}, but for some odd reason this results in  {{ORA-00904: "RN": invalid identifier}}. Since we anyway want to not have the {{RN}} column in the output result set, we can wrap the entire query from above into another {{SELECT}}, having a final query that looks like this: {{SELECT KC1, KC2 FROM (SELECT KC1, KC2, ROWNUM AS rn FROM (SELECT KC1, KC2 FROM MY_TABLE ORDER BY KC1, KC2)) WHERE mod(rn, 2)=0}} (I used {{parallelLoadCacheMinThreshold=2}} just as an example, as in reality it should be larger in order to actually benefit from the parallel loading). Executing the query one obtains the following results:
||KC1|| KC2||
|1|2|
|2|1|
Which are *correct*.



> Wrong SQL generated by org.apache.ignite.cache.store.jdbc.dialect.BasicJdbcDialect#loadCacheSelectRangeQuery
> ------------------------------------------------------------------------------------------------------------
>
>                 Key: IGNITE-4163
>                 URL: https://issues.apache.org/jira/browse/IGNITE-4163
>             Project: Ignite
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 1.7
>            Reporter: Anghel Botos
>            Assignee: Alexey Kuznetsov
>         Attachments: IGNITE_4163_Oracle_specific_for_load_range_query_.patch
>
>
> The SQL statement generated by org.apache.ignite.cache.store.jdbc.dialect.BasicJdbcDialect#loadCacheSelectRangeQuery looks like this:
> {{SELECT KEY_COLUMN_1,KEY_COLUMN_2 FROM (SELECT KEY_COLUMN_1,KEY_COLUMN_2, ROWNUM() AS rn FROM SOME_TABLE ORDER BY KEY_COLUMN_1,KEY_COLUMN_2) WHERE mod(rn, ?) = 0}}
> For Oracle this is incorrect, as Oracle does not have a {{ROWNUM()}} function. For the above query the following error is thrown: {{ORA-00923: FROM keyword not found where expected}}
> Regarding row numbering Oracle has:
> * a {{ROWNUM}} pseudocolumn, in which case the query should have {{ROWNUM AS rn}}
> * a {{ROW_NUMBER()}} function, in which case the query would become more complicated. See https://docs.oracle.com/database/121/SQLRF/functions170.htm#SQLRF06100 for more details about {{ROW_NUMBER()}}
> Please make the neccessary adjustments to either {{BasicJdbcDialect}} or {{OracleDialect}} so that a correct query is produced.



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