Repository: phoenix Updated Branches: refs/heads/4.x-HBase-0.98 f6dfb6d3a -> 96b3ceedb
PHOENIX-3453 Secondary index and query using distinct: Outer query results in ERROR 201 (22000): Illegal data. CHAR types may only contain single byte characters (chenglei) Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/96b3ceed Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/96b3ceed Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/96b3ceed Branch: refs/heads/4.x-HBase-0.98 Commit: 96b3ceedb8ecf8dc9a46fea2a2752b742a8e0e61 Parents: f6dfb6d Author: James Taylor <jamestay...@apache.org> Authored: Tue Feb 14 12:19:17 2017 -0800 Committer: James Taylor <jamestay...@apache.org> Committed: Tue Feb 14 12:20:19 2017 -0800 ---------------------------------------------------------------------- .../apache/phoenix/end2end/GroupByCaseIT.java | 66 +++++++++++++++++++- .../phoenix/expression/CoerceExpression.java | 2 +- .../phoenix/compile/QueryCompilerTest.java | 42 +++++++++++++ 3 files changed, 106 insertions(+), 4 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/phoenix/blob/96b3ceed/phoenix-core/src/it/java/org/apache/phoenix/end2end/GroupByCaseIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/GroupByCaseIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/GroupByCaseIT.java index ca54502..629e9ae 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/GroupByCaseIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/GroupByCaseIT.java @@ -866,12 +866,72 @@ public class GroupByCaseIT extends ParallelStatsDisabledIT { } } - private void assertResultSet(ResultSet rs,String[][] rows) throws Exception { + @Test + public void testGroupByCoerceExpressionBug3453() throws Exception { + final Connection conn = DriverManager.getConnection(getUrl()); + try { + //Type is INT + String intTableName=generateUniqueName(); + String sql="CREATE TABLE "+ intTableName +"("+ + "ENTITY_ID INTEGER NOT NULL,"+ + "CONTAINER_ID INTEGER NOT NULL,"+ + "SCORE INTEGER NOT NULL,"+ + "CONSTRAINT TEST_PK PRIMARY KEY (ENTITY_ID DESC,CONTAINER_ID DESC,SCORE DESC))"; + + conn.createStatement().execute(sql); + conn.createStatement().execute("UPSERT INTO "+intTableName+" VALUES (1,1,1)"); + conn.commit(); + + sql="select DISTINCT entity_id, score from ( select entity_id, score from "+intTableName+" limit 1)"; + ResultSet rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new Object[][]{{1,1}}); + + conn.createStatement().execute("UPSERT INTO "+intTableName+" VALUES (2,2,2)"); + conn.createStatement().execute("UPSERT INTO "+intTableName+" VALUES (3,3,3)"); + conn.commit(); + + sql="select DISTINCT entity_id, score from ( select entity_id, score from "+intTableName+" limit 3) order by entity_id"; + rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new Object[][]{{1,1},{2,2},{3,3}}); + + sql="select DISTINCT entity_id, score from ( select entity_id, score from "+intTableName+" limit 3) order by entity_id desc"; + rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new Object[][]{{3,3},{2,2},{1,1}}); + + //Type is CHAR + String charTableName=generateUniqueName(); + sql="CREATE TABLE "+ charTableName +"("+ + "ENTITY_ID CHAR(15) NOT NULL,"+ + "CONTAINER_ID INTEGER NOT NULL,"+ + "SCORE INTEGER NOT NULL,"+ + "CONSTRAINT TEST_PK PRIMARY KEY (ENTITY_ID DESC,CONTAINER_ID DESC,SCORE DESC))"; + + conn.createStatement().execute(sql); + conn.createStatement().execute("UPSERT INTO "+charTableName+" VALUES ('entity1',1,1)"); + conn.createStatement().execute("UPSERT INTO "+charTableName+" VALUES ('entity2',2,2)"); + conn.createStatement().execute("UPSERT INTO "+charTableName+" VALUES ('entity3',3,3)"); + conn.commit(); + + sql="select DISTINCT entity_id, score from ( select entity_id, score from "+charTableName+" limit 3) order by entity_id"; + rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new Object[][]{{"entity1",1},{"entity2",2},{"entity3",3}}); + + sql="select DISTINCT entity_id, score from ( select entity_id, score from "+charTableName+" limit 3) order by entity_id desc"; + rs=conn.prepareStatement(sql).executeQuery(); + assertResultSet(rs, new Object[][]{{"entity3",3},{"entity2",2},{"entity1",1}}); + } finally { + if(conn!=null) { + conn.close(); + } + } + } + + private void assertResultSet(ResultSet rs,Object[][] rows) throws Exception { for(int rowIndex=0;rowIndex<rows.length;rowIndex++) { assertTrue(rs.next()); for(int columnIndex=1;columnIndex<= rows[rowIndex].length;columnIndex++) { - String realValue=rs.getString(columnIndex); - String expectedValue=rows[rowIndex][columnIndex-1]; + Object realValue=rs.getObject(columnIndex); + Object expectedValue=rows[rowIndex][columnIndex-1]; if(realValue==null) { assertTrue(expectedValue==null); } http://git-wip-us.apache.org/repos/asf/phoenix/blob/96b3ceed/phoenix-core/src/main/java/org/apache/phoenix/expression/CoerceExpression.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/expression/CoerceExpression.java b/phoenix-core/src/main/java/org/apache/phoenix/expression/CoerceExpression.java index c31cb0a..19404f0 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/expression/CoerceExpression.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/expression/CoerceExpression.java @@ -65,7 +65,7 @@ public class CoerceExpression extends BaseSingleExpression { //Package protected for tests CoerceExpression(Expression expression, PDataType toType) { - this(expression, toType, SortOrder.getDefault(), null, true); + this(expression, toType, expression.getSortOrder(), null, true); } CoerceExpression(Expression expression, PDataType toType, SortOrder toSortOrder, Integer maxLength, boolean rowKeyOrderOptimizable) { http://git-wip-us.apache.org/repos/asf/phoenix/blob/96b3ceed/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java b/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java index 8336f81..2c4eccc 100644 --- a/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java +++ b/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java @@ -72,6 +72,7 @@ import org.apache.phoenix.schema.ColumnNotFoundException; import org.apache.phoenix.schema.PColumn; import org.apache.phoenix.schema.PTable; import org.apache.phoenix.schema.PTableKey; +import org.apache.phoenix.schema.SortOrder; import org.apache.phoenix.schema.types.PChar; import org.apache.phoenix.schema.types.PDecimal; import org.apache.phoenix.schema.types.PInteger; @@ -3879,6 +3880,47 @@ public class QueryCompilerTest extends BaseConnectionlessQueryTest { } } + @Test + public void testGroupByCoerceExpressionBug3453() throws Exception { + Connection conn = null; + try { + conn= DriverManager.getConnection(getUrl()); + String tableName="GROUPBY3453_INT"; + String sql="CREATE TABLE "+ tableName +"("+ + "ENTITY_ID INTEGER NOT NULL,"+ + "CONTAINER_ID INTEGER NOT NULL,"+ + "SCORE INTEGER NOT NULL,"+ + "CONSTRAINT TEST_PK PRIMARY KEY (ENTITY_ID DESC,CONTAINER_ID DESC,SCORE DESC))"; + conn.createStatement().execute(sql); + sql="select DISTINCT entity_id, score from ( select entity_id, score from "+tableName+" limit 1)"; + QueryPlan queryPlan=getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().getExpressions().get(0).getSortOrder()==SortOrder.DESC); + assertTrue(queryPlan.getGroupBy().getExpressions().get(1).getSortOrder()==SortOrder.DESC); + assertTrue(queryPlan.getGroupBy().getKeyExpressions().get(0).getSortOrder()==SortOrder.DESC); + assertTrue(queryPlan.getGroupBy().getKeyExpressions().get(1).getSortOrder()==SortOrder.DESC); + + sql="select DISTINCT entity_id, score from ( select entity_id, score from "+tableName+" limit 3) order by entity_id"; + queryPlan=getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().getExpressions().get(0).getSortOrder()==SortOrder.DESC); + assertTrue(queryPlan.getGroupBy().getExpressions().get(1).getSortOrder()==SortOrder.DESC); + assertTrue(queryPlan.getGroupBy().getKeyExpressions().get(0).getSortOrder()==SortOrder.DESC); + assertTrue(queryPlan.getGroupBy().getKeyExpressions().get(1).getSortOrder()==SortOrder.DESC); + assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).getExpression().getSortOrder()==SortOrder.DESC); + + sql="select DISTINCT entity_id, score from ( select entity_id, score from "+tableName+" limit 3) order by entity_id desc"; + queryPlan=getQueryPlan(conn, sql); + assertTrue(queryPlan.getGroupBy().getExpressions().get(0).getSortOrder()==SortOrder.DESC); + assertTrue(queryPlan.getGroupBy().getExpressions().get(1).getSortOrder()==SortOrder.DESC); + assertTrue(queryPlan.getGroupBy().getKeyExpressions().get(0).getSortOrder()==SortOrder.DESC); + assertTrue(queryPlan.getGroupBy().getKeyExpressions().get(1).getSortOrder()==SortOrder.DESC); + assertTrue(queryPlan.getOrderBy()==OrderBy.FWD_ROW_KEY_ORDER_BY); + } finally { + if(conn!=null) { + conn.close(); + } + } + } + private static QueryPlan getQueryPlan(Connection conn,String sql) throws SQLException { PhoenixPreparedStatement statement = conn.prepareStatement(sql).unwrap(PhoenixPreparedStatement.class); QueryPlan queryPlan = statement.optimizeQuery(sql);