Fix Oracle slice fetch.

6 files changed, 37 insertions, 27 deletions

diff --git a/src/main/java/com/amazon/carbonado/repo/jdbc/H2SupportStrategy.java b/src/main/java/com/amazon/carbonado/repo/jdbc/H2SupportStrategy.java
index 6850fbc..30a8810 100644
--- a/src/main/java/com/amazon/carbonado/repo/jdbc/H2SupportStrategy.java
+++ b/src/main/java/com/amazon/carbonado/repo/jdbc/H2SupportStrategy.java
@@ -44,14 +44,14 @@ class H2SupportStrategy extends JDBCSupportStrategy {
     }

 

     @Override

-    String buildSelectWithSlice(String select, boolean limit, boolean offset) {

-        if (limit) {

-            if (offset) {

+    String buildSelectWithSlice(String select, boolean from, boolean to) {

+        if (to) {

+            if (from) {

                 return select.concat(" LIMIT ? OFFSET ?");

             } else {

                 return select.concat(" LIMIT ?");

             }

-        } else if (offset) {

+        } else if (from) {

             return select.concat(" LIMIT 2147483647 OFFSET ?");

         } else {

             return select;

diff --git a/src/main/java/com/amazon/carbonado/repo/jdbc/JDBCStorage.java b/src/main/java/com/amazon/carbonado/repo/jdbc/JDBCStorage.java
index 8810c09..f9bac1f 100644
--- a/src/main/java/com/amazon/carbonado/repo/jdbc/JDBCStorage.java
+++ b/src/main/java/com/amazon/carbonado/repo/jdbc/JDBCStorage.java
@@ -666,19 +666,20 @@ class JDBCStorage<S extends Storable> extends StandardQueryFactory<S>
                     return super.fetch(values, from, to);

                 }

                 select = prepareSelect(values, false);

-                select = mSupportStrategy.buildSelectWithSlice(select, true, false);

+                select = mSupportStrategy.buildSelectWithSlice(select, false, true);

                 break;

             case OFFSET_ONLY:

                 if (from <= 0) {

                     return super.fetch(values, from, to);

                 }

                 select = prepareSelect(values, false);

-                select = mSupportStrategy.buildSelectWithSlice(select, false, true);

+                select = mSupportStrategy.buildSelectWithSlice(select, true, false);

                 break;

             case LIMIT_AND_OFFSET:

             case OFFSET_AND_LIMIT:

+            case FROM_AND_TO:

                 select = prepareSelect(values, false);

-                select = mSupportStrategy.buildSelectWithSlice(select, to != null, from > 0);

+                select = mSupportStrategy.buildSelectWithSlice(select, from > 0, to != null);

                 break;

             }

 

@@ -711,6 +712,11 @@ class JDBCStorage<S extends Storable> extends StandardQueryFactory<S>
                             case OFFSET_AND_LIMIT:

                                 ps.setLong(psOrdinal, from);

                                 ps.setLong(psOrdinal + 1, to - from);

+                                break;

+                            case FROM_AND_TO:

+                                ps.setLong(psOrdinal, from);

+                                ps.setLong(psOrdinal + 1, to);

+                                break;

                             }

                         } else {

                             ps.setLong(psOrdinal, from);

diff --git a/src/main/java/com/amazon/carbonado/repo/jdbc/JDBCSupportStrategy.java b/src/main/java/com/amazon/carbonado/repo/jdbc/JDBCSupportStrategy.java
index f3b0db5..89756c5 100644
--- a/src/main/java/com/amazon/carbonado/repo/jdbc/JDBCSupportStrategy.java
+++ b/src/main/java/com/amazon/carbonado/repo/jdbc/JDBCSupportStrategy.java
@@ -302,6 +302,8 @@ class JDBCSupportStrategy {
         LIMIT_AND_OFFSET,

         // Slice is fully supported with offset parameter first

         OFFSET_AND_LIMIT,

+        // Slice is fully supported with from parameter first

+        FROM_AND_TO,

     }

 

     /**

@@ -313,13 +315,13 @@ class JDBCSupportStrategy {
 

     /**

      * @param select base select statement

-     * @param limit when true, select must support limit parameter

-     * @param offset when true, select must support offset parameter

+     * @param from when true, select must support slice from bound

+     * @param to when true, select must support slice to bound

      * @return revised select statement

      * @throws UnsupportedOperationException

      * @since 1.2

      */

-    String buildSelectWithSlice(String select, boolean limit, boolean offset) {

+    String buildSelectWithSlice(String select, boolean from, boolean to) {

         throw new UnsupportedOperationException();

     }

 }

diff --git a/src/main/java/com/amazon/carbonado/repo/jdbc/MysqlSupportStrategy.java b/src/main/java/com/amazon/carbonado/repo/jdbc/MysqlSupportStrategy.java
index bf15ec6..beaa69c 100644
--- a/src/main/java/com/amazon/carbonado/repo/jdbc/MysqlSupportStrategy.java
+++ b/src/main/java/com/amazon/carbonado/repo/jdbc/MysqlSupportStrategy.java
@@ -43,14 +43,14 @@ class MysqlSupportStrategy extends JDBCSupportStrategy {
     }

 

     @Override

-    String buildSelectWithSlice(String select, boolean limit, boolean offset) {

-        if (limit) {

-            if (offset) {

+    String buildSelectWithSlice(String select, boolean from, boolean to) {

+        if (to) {

+            if (from) {

                 return select.concat(" LIMIT ?,?");

             } else {

                 return select.concat(" LIMIT ?");

             }

-        } else if (offset) {

+        } else if (from) {

             return select.concat(" LIMIT ?,18446744073709551615");

         } else {

             return select;

diff --git a/src/main/java/com/amazon/carbonado/repo/jdbc/OracleSupportStrategy.java b/src/main/java/com/amazon/carbonado/repo/jdbc/OracleSupportStrategy.java
index 582b299..4098933 100644
--- a/src/main/java/com/amazon/carbonado/repo/jdbc/OracleSupportStrategy.java
+++ b/src/main/java/com/amazon/carbonado/repo/jdbc/OracleSupportStrategy.java
@@ -222,21 +222,23 @@ class OracleSupportStrategy extends JDBCSupportStrategy {
 

     @Override

     SliceOption getSliceOption() {

-        return SliceOption.OFFSET_AND_LIMIT;

+        return SliceOption.FROM_AND_TO;

     }

 

     @Override

-    String buildSelectWithSlice(String select, boolean limit, boolean offset) {

-        if (limit) {

-            if (offset) {

-                return "SELECT * FROM (SELECT ROW_.*, ROWNUM ROWNUM_ FROM(" +

-                    select + ") ROW_) WHERE ROWNUM_ > ? AND ROWNUM_ <= ?";

+    String buildSelectWithSlice(String select, boolean from, boolean to) {

+        if (to) {

+            if (from) {

+                // Use quoted identifier with space to prevent clash with

+                // Storable property name.

+                return "SELECT * FROM (SELECT \"A ROW\".*, ROWNUM \"A ROWNUM\" FROM (" +

+                    select + ") \"A ROW\") WHERE \"A ROWNUM\" > ? AND \"A ROWNUM\" <= ?";

             } else {

                 return "SELECT * FROM (" + select + ") WHERE ROWNUM <= ?";

             }

-        } else if (offset) {

-            return "SELECT * FROM (SELECT ROW_.*, ROWNUM ROWNUM_ FROM(" +

-                select + ") ROW_) WHERE ROWNUM_ > ?";

+        } else if (from) {

+            return "SELECT * FROM (SELECT \"A ROW\".*, ROWNUM \"A ROWNUM\" FROM (" +

+                select + ") \"A ROW\") WHERE \"A ROWNUM\" > ?";

         } else {

             return select;

         }

diff --git a/src/main/java/com/amazon/carbonado/repo/jdbc/PostgresqlSupportStrategy.java b/src/main/java/com/amazon/carbonado/repo/jdbc/PostgresqlSupportStrategy.java
index 43d35c2..48a4ad0 100644
--- a/src/main/java/com/amazon/carbonado/repo/jdbc/PostgresqlSupportStrategy.java
+++ b/src/main/java/com/amazon/carbonado/repo/jdbc/PostgresqlSupportStrategy.java
@@ -39,14 +39,14 @@ class PostgresqlSupportStrategy extends JDBCSupportStrategy {
     }

 

     @Override

-    String buildSelectWithSlice(String select, boolean limit, boolean offset) {

-        if (limit) {

-            if (offset) {

+    String buildSelectWithSlice(String select, boolean from, boolean to) {

+        if (to) {

+            if (from) {

                 return select.concat(" LIMIT ? OFFSET ?");

             } else {

                 return select.concat(" LIMIT ?");

             }

-        } else if (offset) {

+        } else if (from) {

             return select.concat(" LIMIT ALL OFFSET ?");

         } else {

             return select;