This is an automated email from the ASF dual-hosted git repository.

richardantal pushed a commit to branch 4.16
in repository https://gitbox.apache.org/repos/asf/phoenix.git


The following commit(s) were added to refs/heads/4.16 by this push:
     new a166b33  PHOENIX-5072 Cursor Query Loops Eternally with Local Index, 
Returns Fine Without It
a166b33 is described below

commit a166b33f698f45ba2d2ef873419effe93a3d0606
Author: Richard Antal <antal97rich...@gmail.com>
AuthorDate: Tue Aug 17 16:38:16 2021 +0200

    PHOENIX-5072 Cursor Query Loops Eternally with Local Index, Returns Fine 
Without It
---
 .../end2end/CursorWithRowValueConstructorIT.java   | 92 ++++++++++++++++++++++
 .../phoenix/compile/DeclareCursorCompiler.java     |  8 +-
 2 files changed, 98 insertions(+), 2 deletions(-)

diff --git 
a/phoenix-core/src/it/java/org/apache/phoenix/end2end/CursorWithRowValueConstructorIT.java
 
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/CursorWithRowValueConstructorIT.java
index e5457d6..6e91d1f 100644
--- 
a/phoenix-core/src/it/java/org/apache/phoenix/end2end/CursorWithRowValueConstructorIT.java
+++ 
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/CursorWithRowValueConstructorIT.java
@@ -40,10 +40,13 @@ import java.sql.Date;
 import java.sql.DriverManager;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
+import java.sql.Statement;
 import java.sql.SQLException;
 import java.sql.Timestamp;
+import java.text.DecimalFormat;
 import java.util.Properties;
 import java.util.Random;
+import java.util.UUID;
 
 import org.apache.phoenix.util.CursorUtil;
 import org.apache.phoenix.util.DateUtil;
@@ -669,4 +672,93 @@ public class CursorWithRowValueConstructorIT extends 
ParallelStatsDisabledIT {
             conn.close();
         }
     }
+
+    @Test
+    public void testCursorWithIndex() throws Exception {
+        String cursorName = generateUniqueName();
+        String tableName = generateUniqueName();
+        String indexName = generateUniqueName();
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        Connection conn = DriverManager.getConnection(getUrl(), props);
+
+        Statement stmt = conn.createStatement();
+
+        String createTable = "CREATE TABLE IF NOT EXISTS " + tableName +"\n" +
+                "(  \n" +
+                "   ID                             VARCHAR    NOT NULL,\n" +
+                "   NAME                           VARCHAR    ,\n" +
+                "   ANOTHER_VALUE                  VARCHAR    ,\n" +
+                "   TRANSACTION_TIME               TIMESTAMP  ,\n" +
+                "   CONSTRAINT pk PRIMARY KEY(ID)\n" +
+                ")";
+        stmt.execute(createTable);
+
+        //Creating an index
+        String createIndex = "CREATE LOCAL INDEX " + indexName + " ON " + 
tableName + "(NAME, TRANSACTION_TIME DESC) INCLUDE(ANOTHER_VALUE)";
+        stmt.execute(createIndex);
+
+        //Insert Some  Items.
+        DecimalFormat dmf = new DecimalFormat("0000");
+        final String prefix = "ReferenceData.Country/";
+        for (int i = 0; i < 10; i++)
+        {
+            for (int j = 0; j < 10; j++)
+            {
+                PreparedStatement prstmt = conn.prepareStatement("UPSERT INTO 
" + tableName + " VALUES(?,?,?,?)");
+                prstmt.setString(1, UUID.randomUUID().toString());
+                prstmt.setString(2,prefix + dmf.format(i+j*1000));
+                prstmt.setString(3,UUID.randomUUID().toString());
+                prstmt.setTimestamp(4, new 
Timestamp(System.currentTimeMillis()));
+                prstmt.execute();
+                conn.commit();
+                prstmt.close();
+            }
+        }
+
+        String countSQL = "SELECT COUNT(1) AS TOTAL_ITEMS FROM " + tableName + 
" where NAME like 'ReferenceData.Country/2%' ";
+        ResultSet rs = stmt.executeQuery(countSQL);
+        rs.next();
+        final int totalCount = rs.getInt("TOTAL_ITEMS");
+        rs.close();
+
+        //Now a Cursor
+        String cursorSQL = "DECLARE " + cursorName + " CURSOR FOR SELECT 
NAME,ANOTHER_VALUE FROM "
+                + tableName + " where NAME like 'ReferenceData.Country/2%' 
ORDER BY TRANSACTION_TIME DESC";
+        PreparedStatement cursorStatement = conn.prepareStatement(cursorSQL);
+        cursorStatement.execute();
+        PreparedStatement openCursorStatement = conn.prepareStatement("OPEN " 
+ cursorName);
+        openCursorStatement.execute();
+
+        rs = stmt.executeQuery("EXPLAIN FETCH NEXT 10 ROWS FROM " + 
cursorName);
+        rs.next();
+        assertTrue(rs.getString(1)
+                .contains("CLIENT PARALLEL 1-WAY RANGE SCAN"));
+        PreparedStatement next10Rows = conn.prepareStatement("FETCH NEXT 10 
ROWS FROM " + cursorName);
+        int itemsReturnedByCursor = 0;
+        while(true)
+        {
+            ResultSet cursorRS = next10Rows.executeQuery();
+            int rowsReadBeforeEmpty = 0;
+            while(cursorRS.next())
+            {
+                itemsReturnedByCursor++;
+                rowsReadBeforeEmpty++;
+            }
+            if(rowsReadBeforeEmpty > 0 )
+            {
+                cursorRS.close();
+            }
+            else
+            {
+                conn.prepareStatement("CLOSE " + cursorName).executeUpdate();
+                break;
+            }
+
+            if(itemsReturnedByCursor > (totalCount * 3))
+            {
+                break;
+            }
+        }
+        assertEquals(totalCount, itemsReturnedByCursor);
+    }
 }
diff --git 
a/phoenix-core/src/main/java/org/apache/phoenix/compile/DeclareCursorCompiler.java
 
b/phoenix-core/src/main/java/org/apache/phoenix/compile/DeclareCursorCompiler.java
index 5280291..89acfe1 100644
--- 
a/phoenix-core/src/main/java/org/apache/phoenix/compile/DeclareCursorCompiler.java
+++ 
b/phoenix-core/src/main/java/org/apache/phoenix/compile/DeclareCursorCompiler.java
@@ -45,10 +45,14 @@ public class DeclareCursorCompiler {
     private final Operation operation;
     private QueryPlan queryPlan;
 
-    public DeclareCursorCompiler(PhoenixStatement statement, Operation 
operation, QueryPlan queryPlan) {
+    public DeclareCursorCompiler(PhoenixStatement statement, Operation 
operation, QueryPlan queryPlan) throws SQLException {
         this.statement = statement;
         this.operation = operation;
-        this.queryPlan = queryPlan;
+        // See PHOENIX-5072
+        // We optimize the plan inside the CursorFetchPlan here at the first 
place.
+        // Later when the next optimize is called, the original 
CursorFetchPlan will be selected as there won't be any better plans.
+        this.queryPlan = 
statement.getConnection().getQueryServices().getOptimizer()
+                .optimize(statement, queryPlan);
     }
 
     public MutationPlan compile(final DeclareCursorStatement declare) throws 
SQLException {

Reply via email to