This is an automated email from the ASF dual-hosted git repository.
vjasani pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/phoenix.git
The following commit(s) were added to refs/heads/master by this push:
new 9cb48832a7 PHOENIX-7610 Using CAST() on pk columns always result in
full table scan (#2150)
9cb48832a7 is described below
commit 9cb48832a7e9b9a972d682535179ab6a2fd0cb16
Author: Viraj Jasani <[email protected]>
AuthorDate: Sat May 17 18:50:55 2025 -0700
PHOENIX-7610 Using CAST() on pk columns always result in full table scan
(#2150)
---
.../phoenix/expression/CoerceExpression.java | 12 ++++++
.../java/org/apache/phoenix/end2end/Bson4IT.java | 4 +-
.../apache/phoenix/end2end/CastAndCoerceIT.java | 45 ++++++++++++++++++----
3 files changed, 51 insertions(+), 10 deletions(-)
diff --git
a/phoenix-core-client/src/main/java/org/apache/phoenix/expression/CoerceExpression.java
b/phoenix-core-client/src/main/java/org/apache/phoenix/expression/CoerceExpression.java
index f6d5c21d4d..bd7e44b5b0 100644
---
a/phoenix-core-client/src/main/java/org/apache/phoenix/expression/CoerceExpression.java
+++
b/phoenix-core-client/src/main/java/org/apache/phoenix/expression/CoerceExpression.java
@@ -146,6 +146,18 @@ public class CoerceExpression extends BaseSingleExpression
{
WritableUtils.writeVInt(output, maxLength == null ? -1 : maxLength);
}
+ @Override
+ public boolean isStateless() {
+ // It is important to associate the stateless-ness of the
CoerceExpression
+ // child with the CoerceExpression. Without this, ComparisonExpression
and
+ // KeyExpressionVisitor will not be evaluated on the client side, and
+ // thus WhereOptimizer will always select Full table scan
+ // even for the query that is supposed to use Range scan or Point
lookup
+ // on the single row.
+ // Jira: PHOENIX-7610.
+ return getChild().isStateless();
+ }
+
@Override
public boolean evaluate(Tuple tuple, ImmutableBytesWritable ptr) {
// For CoerceExpression evaluation, lhs is coerced to rhs literal
expression. However,
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/Bson4IT.java
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/Bson4IT.java
index 26b2172875..4084561623 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/Bson4IT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/Bson4IT.java
@@ -484,9 +484,7 @@ public class Bson4IT extends ParallelStatsDisabledIT {
assertFalse(rs.next());
- // TODO : Fix this separately, using CAST with PK column results into
full table scan due
- // to bug.
- // validateExplainPlan(stmt, query, tableName, "POINT LOOKUP ON 1 KEY ");
+ validateExplainPlan(stmt, query, tableName, "POINT LOOKUP ON 1 KEY ");
query = "SELECT * FROM " + tableName +
" WHERE PK1 != CAST('" + sample1 + "' AS BSON)";
diff --git
a/phoenix-core/src/it/java/org/apache/phoenix/end2end/CastAndCoerceIT.java
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/CastAndCoerceIT.java
index d5183c36da..66931a1b30 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/CastAndCoerceIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/CastAndCoerceIT.java
@@ -33,6 +33,9 @@ import java.sql.ResultSet;
import java.util.Collection;
import java.util.Properties;
+import org.apache.phoenix.compile.ExplainPlan;
+import org.apache.phoenix.compile.ExplainPlanAttributes;
+import org.apache.phoenix.jdbc.PhoenixPreparedStatement;
import org.apache.phoenix.util.PropertiesUtil;
import org.junit.Test;
import org.junit.experimental.categories.Category;
@@ -142,26 +145,54 @@ public class CastAndCoerceIT extends BaseQueryIT {
conn.close();
}
}
-
+
@Test
public void testCoerceTinyIntToSmallInt() throws Exception {
- String query = "SELECT entity_id FROM " + tableName + " WHERE
organization_id=? AND a_byte >= a_short";
+ String query = "SELECT entity_id FROM " + tableName
+ + " WHERE organization_id=? AND a_byte >= a_short";
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(url, props);
- try {
+ try (Connection conn = DriverManager.getConnection(url, props)) {
PreparedStatement statement = conn.prepareStatement(query);
statement.setString(1, tenantId);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals(ROW9, rs.getString(1));
assertFalse(rs.next());
- } finally {
- conn.close();
+
+ ExplainPlan plan =
statement.unwrap(PhoenixPreparedStatement.class).optimizeQuery(query)
+ .getExplainPlan();
+ ExplainPlanAttributes explainPlanAttributes =
plan.getPlanStepsAsAttributes();
+ assertEquals(tableName, explainPlanAttributes.getTableName());
+ assertEquals("PARALLEL 1-WAY",
explainPlanAttributes.getIteratorTypeAndScanSize());
+ assertEquals("RANGE SCAN ",
explainPlanAttributes.getExplainScanType());
+ }
+ }
+
+ @Test
+ public void testCoerceWithRangeScan() throws Exception {
+ String query = "SELECT entity_id FROM " + tableName
+ + " WHERE organization_id = cast(? as varchar) AND "
+ + "cast(a_byte as smallint) >= a_short";
+ String url = getUrl();
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ try (Connection conn = DriverManager.getConnection(url, props)) {
+ PreparedStatement statement = conn.prepareStatement(query);
+ statement.setString(1, tenantId);
+ ResultSet rs = statement.executeQuery();
+ assertTrue(rs.next());
+ assertEquals(ROW9, rs.getString(1));
+ assertFalse(rs.next());
+
+ ExplainPlan plan =
statement.unwrap(PhoenixPreparedStatement.class).optimizeQuery(query)
+ .getExplainPlan();
+ ExplainPlanAttributes explainPlanAttributes =
plan.getPlanStepsAsAttributes();
+ assertEquals(tableName, explainPlanAttributes.getTableName());
+ assertEquals("PARALLEL 1-WAY",
explainPlanAttributes.getIteratorTypeAndScanSize());
+ assertEquals("RANGE SCAN ",
explainPlanAttributes.getExplainScanType());
}
}
-
@Test
public void testCoerceDateToBigInt() throws Exception {
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
