You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@phoenix.apache.org by ri...@apache.org on 2021/09/14 12:08:33 UTC
[phoenix] branch 4.x updated: PHOENIX-5072 Cursor Query Loops
Eternally with Local Index, Returns Fine Without It
This is an automated email from the ASF dual-hosted git repository.
richardantal pushed a commit to branch 4.x
in repository https://gitbox.apache.org/repos/asf/phoenix.git
The following commit(s) were added to refs/heads/4.x by this push:
new 5eadf8f PHOENIX-5072 Cursor Query Loops Eternally with Local Index, Returns Fine Without It
5eadf8f is described below
commit 5eadf8f15f79968a31dde5b361a5b303f896f08b
Author: Richard Antal <an...@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 {