This is an automated email from the ASF dual-hosted git repository. apurtell pushed a commit to branch PHOENIX-7562-feature in repository https://gitbox.apache.org/repos/asf/phoenix.git
commit b48685915e65c586a57e37348201d458fc00446d 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);
