You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@phoenix.apache.org by "chenglei (Jira)" <ji...@apache.org> on 2020/02/28 14:27:00 UTC
[jira] [Updated] (PHOENIX-5752) Phoenix RVC InListExpression
generates wrong DESC order column scan
[ https://issues.apache.org/jira/browse/PHOENIX-5752?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
chenglei updated PHOENIX-5752:
------------------------------
Labels: DESC (was: )
> Phoenix RVC InListExpression generates wrong DESC order column scan
> -------------------------------------------------------------------
>
> Key: PHOENIX-5752
> URL: https://issues.apache.org/jira/browse/PHOENIX-5752
> Project: Phoenix
> Issue Type: Improvement
> Reporter: Xinyi Yan
> Assignee: Xinyi Yan
> Priority: Major
> Labels: DESC
>
> {code:java}
> @Test
> public void testInListExpressionWithDescOrderWithRightQueryPlan() throws Exception {
> String fullTableName = generateUniqueName();
> String fullViewName = generateUniqueName();
> String tenantView = generateUniqueName();
> String TENANT_SPECIFIC_URL1 = getUrl() + ';' + TENANT_ID_ATTRIB + "=tenant1";
> try (Connection conn = DriverManager.getConnection(getUrl())) {
> conn.setAutoCommit(true);
> try (Statement stmt = conn.createStatement()) {
> stmt.execute("CREATE TABLE " + fullTableName + "(\n" + " TENANT_ID CHAR(15) NOT NULL,\n" + " KEY_PREFIX CHAR(3) NOT NULL,\n" +
> " CONSTRAINT PK PRIMARY KEY (\n" + " TENANT_ID," + " KEY_PREFIX" + ")) MULTI_TENANT=TRUE");
> stmt.execute("CREATE VIEW " + fullViewName + "(\n" + " ID1 VARCHAR NOT NULL,\n" + " ID2 VARCHAR NOT NULL,\n" + " ID3 BIGINT, ID4 BIGINT \n" +
> " CONSTRAINT PKVIEW PRIMARY KEY\n" + " (\n" + " ID1, ID2 DESC\n" + ")) " +
> "AS SELECT * FROM " + fullTableName + " WHERE KEY_PREFIX = '0CY'");
> }
> }
> try (Connection viewConn = DriverManager.getConnection(TENANT_SPECIFIC_URL1)) {
> viewConn.setAutoCommit(true);
> try (Statement stmt = viewConn.createStatement()) {
> stmt.execute("CREATE VIEW IF NOT EXISTS " + tenantView + " AS SELECT * FROM " + fullViewName);
> try (PreparedStatement preparedStmt = viewConn.prepareStatement(
> "SELECT * FROM " + tenantView + " WHERE (ID1, ID2) " +
> "IN (('me', '000000000000500')," +
> "('bar', '000000000000400')," +
> "('foo', '000000000000300'))")) {
> QueryPlan queryPlan = PhoenixRuntime.getOptimizedQueryPlan(preparedStmt);
> assertTrue(queryPlan.getExplainPlan().toString().contains("POINT LOOKUP ON "));
> }
> }
> }
> }
> {code}
> InListExpression generates a range scan instead of a point lookup. This might result in an extremely bad performance for the DELETE and SELECT query. From my perspective, it might be the point that we need to refactor and/or rewrite query optimization code so that we don't need to have two code paths for InListExpresson. We can simply do the query rewrite to ((ID1=? AND ID2=?) or (ID1=? AND ID2=?)) as the right approach.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)