[jira] [Commented] (IGNITE-4163) Wrong SQL generated by org.apache.ignite.cache.store.jdbc.dialect.BasicJdbcDialect#loadCacheSelectRangeQuery
[ 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)
[jira] [Commented] (IGNITE-4163) Wrong SQL generated by org.apache.ignite.cache.store.jdbc.dialect.BasicJdbcDialect#loadCacheSelectRangeQuery
[ https://issues.apache.org/jira/browse/IGNITE-4163?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15635499#comment-15635499 ] Anghel Botos commented on IGNITE-4163: -- Additional to my above comment, since having a select that is so nested I also looked at Oracle's {{ROW_NUMBER()}} function. I can get the same results using a query like this: {{SELECT KC1, KC2 FROM (SELECT KC1, KC2, ROW_NUMBER() OVER (ORDER BY KC1, KC2) RN FROM MY_TABLE) WHERE mod(RN, 2)=0;}} (I used {{parallelLoadCacheMinThreshold=2}} just as an example). Regarding which of the two approaches is better (i.e. more performant) I cannot really say, as I'm not an Oracle expert, but I found some articles dealing with the topic (e.g. https://explainextended.com/2009/05/06/oracle-row_number-vs-rownum/). In case you have an Oracle expert in amongst your contributors, it might be a good idea to check with him. > 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)
[jira] [Commented] (IGNITE-4163) Wrong SQL generated by org.apache.ignite.cache.store.jdbc.dialect.BasicJdbcDialect#loadCacheSelectRangeQuery
[ https://issues.apache.org/jira/browse/IGNITE-4163?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15644193#comment-15644193 ] Vasiliy Sisko commented on IGNITE-4163: --- Fixed using of row number function for Oracle, IBM DB2 and PostgreSQL databases. Left: Fix for Microsoft SQL Server. > 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: Vasiliy Sisko > Attachments: > IGNITE_4163_Fixed_load_range_queries_for_different_databases.patch, > 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)
[jira] [Commented] (IGNITE-4163) Wrong SQL generated by org.apache.ignite.cache.store.jdbc.dialect.BasicJdbcDialect#loadCacheSelectRangeQuery
[ https://issues.apache.org/jira/browse/IGNITE-4163?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15656745#comment-15656745 ] Pavel Konstantinov commented on IGNITE-4163: I've faced with the following issue while I'm tried to test with MS SQL Server {code} Caused by: java.lang.IllegalStateException: Failed to find column index for database field: id1 at org.apache.ignite.cache.store.jdbc.CacheAbstractJdbcStore.columnIndex(CacheAbstractJdbcStore.java:796) at org.apache.ignite.cache.store.jdbc.CacheJdbcPojoStore.buildBinaryObject(CacheJdbcPojoStore.java:250) at org.apache.ignite.cache.store.jdbc.CacheJdbcPojoStore.buildObject(CacheJdbcPojoStore.java:136) at org.apache.ignite.cache.store.jdbc.CacheAbstractJdbcStore$LoadCacheCustomQueryWorker.call(CacheAbstractJdbcStore.java:2047) at org.apache.ignite.cache.store.jdbc.CacheAbstractJdbcStore$LoadCacheCustomQueryWorker.call(CacheAbstractJdbcStore.java:2003) {code} > 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: Pavel Konstantinov > Attachments: IGNITE_4163_Fixed_load_range_queries_Review.patch, > IGNITE_4163_Fixed_load_range_queries_for_MS_SQL_Server_.patch, > IGNITE_4163_Fixed_load_range_queries_for_different_databases.patch, > 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)
[jira] [Commented] (IGNITE-4163) Wrong SQL generated by org.apache.ignite.cache.store.jdbc.dialect.BasicJdbcDialect#loadCacheSelectRangeQuery
[ https://issues.apache.org/jira/browse/IGNITE-4163?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15662691#comment-15662691 ] Vasiliy Sisko commented on IGNITE-4163: --- Fixed loading of cache in case when a query is specified and JDBC driver metadata contain columns in lower case. > 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: Vasiliy Sisko > Attachments: IGNITE_4163_Fixed_load_range_queries_Review.patch, > IGNITE_4163_Fixed_load_range_queries_for_MS_SQL_Server_.patch, > IGNITE_4163_Fixed_load_range_queries_for_different_databases.patch, > IGNITE_4163_Index_column_in_upper_case.patch, > IGNITE_4163_Oracle_specific_for_load_range_query_.patch, test4163-project.zip > > > 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)
[jira] [Commented] (IGNITE-4163) Wrong SQL generated by org.apache.ignite.cache.store.jdbc.dialect.BasicJdbcDialect#loadCacheSelectRangeQuery
[ https://issues.apache.org/jira/browse/IGNITE-4163?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=1522#comment-1522 ] Pavel Konstantinov commented on IGNITE-4163: Successfully tested with major DBs: Oracle, SQL Server, MySql, PostgreSQL, IBM DB2, H2 > 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: Pavel Konstantinov > Attachments: IGNITE_4163_Fixed_load_range_queries_Review.patch, > IGNITE_4163_Fixed_load_range_queries_for_MS_SQL_Server_.patch, > IGNITE_4163_Fixed_load_range_queries_for_different_databases.patch, > IGNITE_4163_Index_column_in_upper_case.patch, > IGNITE_4163_Oracle_specific_for_load_range_query_.patch, test4163-project.zip > > > 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)
[jira] [Commented] (IGNITE-4163) Wrong SQL generated by org.apache.ignite.cache.store.jdbc.dialect.BasicJdbcDialect#loadCacheSelectRangeQuery
[ https://issues.apache.org/jira/browse/IGNITE-4163?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15672718#comment-15672718 ] Andrey Novikov commented on IGNITE-4163: Merged. > 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: Pavel Konstantinov > Fix For: 1.8 > > Attachments: IGNITE_4163_Fixed_load_range_queries_Review.patch, > IGNITE_4163_Fixed_load_range_queries_for_MS_SQL_Server_.patch, > IGNITE_4163_Fixed_load_range_queries_for_different_databases.patch, > IGNITE_4163_Index_column_in_upper_case.patch, > IGNITE_4163_Oracle_specific_for_load_range_query_.patch, test4163-project.zip > > > 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)
[jira] [Commented] (IGNITE-4163) Wrong SQL generated by org.apache.ignite.cache.store.jdbc.dialect.BasicJdbcDialect#loadCacheSelectRangeQuery
[ https://issues.apache.org/jira/browse/IGNITE-4163?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15692655#comment-15692655 ] Pavel Konstantinov commented on IGNITE-4163: Re-tested in target branch. > 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: Pavel Konstantinov > Fix For: 1.8 > > Attachments: IGNITE_4163_Fixed_load_range_queries_Review.patch, > IGNITE_4163_Fixed_load_range_queries_for_MS_SQL_Server_.patch, > IGNITE_4163_Fixed_load_range_queries_for_different_databases.patch, > IGNITE_4163_Index_column_in_upper_case.patch, > IGNITE_4163_Oracle_specific_for_load_range_query_.patch, test4163-project.zip > > > 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)