(phoenix) branch 5.2 updated: PHOENIX-7610 Using CAST() on pk columns always result in full table scan (#2150)

[vjasani]

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

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


The following commit(s) were added to refs/heads/5.2 by this push:
     new f1a169a5b0 PHOENIX-7610 Using CAST() on pk columns always result in 
full table scan (#2150)
f1a169a5b0 is described below

commit f1a169a5b059aea386028bfd5289b67e20414ea9
Author: Viraj Jasani <vjas...@apache.org>
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 ++++++
 .../apache/phoenix/end2end/CastAndCoerceIT.java    | 45 ++++++++++++++++++----
 2 files changed, 50 insertions(+), 7 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 fc8fe39b79..27460d6f5d 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
@@ -143,6 +143,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) {
         if (getChild().evaluate(tuple, ptr)) {
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);