phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "ASF GitHub Bot (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (PHOENIX-514) Support functional indexes
Date Mon, 02 Feb 2015 23:46:36 GMT

    [ https://issues.apache.org/jira/browse/PHOENIX-514?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14302338#comment-14302338
] 

ASF GitHub Bot commented on PHOENIX-514:
----------------------------------------

Github user twdsilva commented on a diff in the pull request:

    https://github.com/apache/phoenix/pull/34#discussion_r23970428
  
    --- Diff: phoenix-core/src/it/java/org/apache/phoenix/end2end/index/ExpressionIndexIT.java
---
    @@ -0,0 +1,774 @@
    +/*
    + * Licensed to the Apache Software Foundation (ASF) under one or more contributor license
agreements. See the NOTICE
    + * file distributed with this work for additional information regarding copyright ownership.
The ASF licenses this file
    + * to you under the Apache License, Version 2.0 (the "License"); you may not use this
file except in compliance with the
    + * License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0
Unless required by
    + * applicable law or agreed to in writing, software distributed under the License is
distributed on an "AS IS" BASIS,
    + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License
for the specific language
    + * governing permissions and limitations under the License.
    + */
    +package org.apache.phoenix.end2end.index;
    +
    +import static org.apache.phoenix.util.TestUtil.INDEX_DATA_SCHEMA;
    +import static org.apache.phoenix.util.TestUtil.INDEX_DATA_TABLE;
    +import static org.apache.phoenix.util.TestUtil.MUTABLE_INDEX_DATA_TABLE;
    +import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
    +import static org.junit.Assert.assertEquals;
    +import static org.junit.Assert.assertFalse;
    +import static org.junit.Assert.assertTrue;
    +import static org.junit.Assert.fail;
    +
    +import java.math.BigDecimal;
    +import java.sql.Connection;
    +import java.sql.Date;
    +import java.sql.DriverManager;
    +import java.sql.PreparedStatement;
    +import java.sql.ResultSet;
    +import java.sql.SQLException;
    +import java.util.Properties;
    +
    +import org.apache.commons.lang.StringUtils;
    +import org.apache.phoenix.end2end.BaseHBaseManagedTimeIT;
    +import org.apache.phoenix.exception.SQLExceptionCode;
    +import org.apache.phoenix.query.QueryConstants;
    +import org.apache.phoenix.util.DateUtil;
    +import org.apache.phoenix.util.PropertiesUtil;
    +import org.apache.phoenix.util.QueryUtil;
    +import org.junit.Test;
    +
    +public class ExpressionIndexIT extends BaseHBaseManagedTimeIT {
    +
    +    private static final int NUM_MILLIS_IN_DAY = 86400000;
    +
    +    @Test
    +    public void testImmutableIndexCreationAndUpdate() throws Exception {
    +        helpTestCreateAndUpdate(false, false);
    +    }
    +
    +    @Test
    +    public void testImmutableLocalIndexCreationAndUpdate() throws Exception {
    +        helpTestCreateAndUpdate(false, true);
    +    }
    +
    +    @Test
    +    public void testMutableIndexCreationAndUpdate() throws Exception {
    +        helpTestCreateAndUpdate(true, false);
    +    }
    +
    +    @Test
    +    public void testMutableLocalIndexCreationAndUpdate() throws Exception {
    +        helpTestCreateAndUpdate(true, true);
    +    }
    +
    +    /**
    +     * Adds a row to the index data table
    +     * 
    +     * @param i
    +     *            row number
    +     */
    +    private void insertRow(PreparedStatement stmt, int i) throws SQLException {
    +        // insert row
    +        stmt.setString(1, "varchar" + String.valueOf(i));
    +        stmt.setString(2, "char" + String.valueOf(i));
    +        stmt.setInt(3, i);
    +        stmt.setLong(4, i);
    +        stmt.setBigDecimal(5, new BigDecimal(Double.valueOf(i)));
    +        Date date = new Date(DateUtil.parseDate("2015-01-01 00:00:00").getTime() + (i
- 1) * NUM_MILLIS_IN_DAY);
    +        stmt.setDate(6, date);
    +        stmt.setString(7, "a.varchar" + String.valueOf(i));
    +        stmt.setString(8, "a.char" + String.valueOf(i));
    +        stmt.setInt(9, i);
    +        stmt.setLong(10, i);
    +        stmt.setBigDecimal(11, new BigDecimal((double)i));
    +        stmt.setDate(12, date);
    +        stmt.setString(13, "b.varchar" + String.valueOf(i));
    +        stmt.setString(14, "b.char" + String.valueOf(i));
    +        stmt.setInt(15, i);
    +        stmt.setLong(16, i);
    +        stmt.setBigDecimal(17, new BigDecimal((double)i));
    +        stmt.setDate(18, date);
    +        stmt.executeUpdate();
    +    }
    +
    +    private void verifyResult(ResultSet rs, int i) throws SQLException {
    +        assertTrue(rs.next());
    +        assertEquals("VARCHAR" + String.valueOf(i) + "_" + StringUtils.rightPad("CHAR"
+ String.valueOf(i), 6, ' ')
    +                + "_A.VARCHAR" + String.valueOf(i) + "_" + StringUtils.rightPad("B.CHAR"
+ String.valueOf(i), 10, ' '),
    +                rs.getString(1));
    +        assertEquals(i * 4, rs.getInt(2));
    +        Date date = new Date(DateUtil.parseDate("2015-01-01 00:00:00").getTime() + (i)
* NUM_MILLIS_IN_DAY);
    +        assertEquals(date, rs.getDate(3));
    +        assertEquals(date, rs.getDate(4));
    +        assertEquals(date, rs.getDate(5));
    +        assertEquals("varchar" + String.valueOf(i), rs.getString(6));
    +        assertEquals("char" + String.valueOf(i), rs.getString(7));
    +        assertEquals(i, rs.getInt(8));
    +        assertEquals(i, rs.getLong(9));
    +        assertEquals(i, rs.getDouble(10), 0.000001);
    +        assertEquals(i, rs.getLong(11));
    +        assertEquals(i, rs.getLong(12));
    +    }
    +
    +    protected void helpTestCreateAndUpdate(boolean mutable, boolean localIndex) throws
Exception {
    +        String dataTableName = mutable ? MUTABLE_INDEX_DATA_TABLE : INDEX_DATA_TABLE;
    +        String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR
+ dataTableName;
    +        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    +        Connection conn = DriverManager.getConnection(getUrl(), props);
    +        try {
    +            conn.setAutoCommit(false);
    +            populateDataTable(conn, dataTableName);
    +
    +            // create an expression index
    +            String ddl = "CREATE "
    +                    + (localIndex ? "LOCAL" : "")
    +                    + " INDEX IDX ON "
    +                    + fullDataTableName
    +                    + " ((UPPER(varchar_pk) || '_' || UPPER(char_pk) || '_' || UPPER(varchar_col1)
|| '_' || UPPER(char_col2)),"
    +                    + " (decimal_pk+int_pk+decimal_col2+int_col1)," + " date_pk+1, date1+1,
date2+1 )"
    +                    + " INCLUDE (long_col1, long_col2)";
    +            PreparedStatement stmt = conn.prepareStatement(ddl);
    +            stmt.execute();
    +
    +            // run select query with expression in WHERE clause
    +            String whereSql = "SELECT long_col1, long_col2 from "
    +                    + fullDataTableName
    +                    + " WHERE UPPER(varchar_pk) || '_' || UPPER(char_pk) || '_' || UPPER(varchar_col1)
|| '_' || UPPER(char_col2) = ?"
    +                    + " AND decimal_pk+int_pk+decimal_col2+int_col1=?"
    +                    // since a.date1 and b.date2 are NULLABLE and date is fixed width,
these expressions are stored as
    +                    // DECIMAL in the index (which is not fixed width)
    +                    + " AND date_pk+1=? AND date1+1=? AND date2+1=?";
    +            stmt = conn.prepareStatement(whereSql);
    +            stmt.setString(1, "VARCHAR1_CHAR1 _A.VARCHAR1_B.CHAR1   ");
    +            stmt.setInt(2, 4);
    +            Date date = DateUtil.parseDate("2015-01-02 00:00:00");
    +            stmt.setDate(3, date);
    +            stmt.setDate(4, date);
    +            stmt.setDate(5, date);
    +
    +            // verify that the query does a range scan on the index table
    +            ResultSet rs = stmt.executeQuery("EXPLAIN " + whereSql);
    +            assertEquals(
    +                    localIndex ? "CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_INDEX_TEST."
    +                            + dataTableName
    +                            + " [-32768,'VARCHAR1_CHAR1 _A.VARCHAR1_B.CHAR1   ',4,'2015-01-02
00:00:00.000',1,420,156,800,000,1,420,156,800,000]\nCLIENT MERGE SORT"
    +                            : "CLIENT PARALLEL 1-WAY RANGE SCAN OVER INDEX_TEST.IDX ['VARCHAR1_CHAR1
_A.VARCHAR1_B.CHAR1   ',4,'2015-01-02 00:00:00.000',1,420,156,800,000,1,420,156,800,000]",
    +                    QueryUtil.getExplainPlan(rs));
    +
    +            // verify that the correct results are returned
    +            rs = stmt.executeQuery();
    +            assertTrue(rs.next());
    +            assertEquals(1, rs.getInt(1));
    +            assertEquals(1, rs.getInt(2));
    +            assertFalse(rs.next());
    +
    +            // verify all rows in data table are present in index table
    +            String indexSelectSql = "SELECT UPPER(varchar_pk) || '_' || UPPER(char_pk)
|| '_' || UPPER(varchar_col1) || '_' || UPPER(char_col2), "
    +                    + "decimal_pk+int_pk+decimal_col2+int_col1, "
    +                    + "date_pk+1, date1+1, date2+1, "
    +                    + "varchar_pk, char_pk, int_pk, long_pk, decimal_pk, "
    +                    + "long_col1, long_col2 "
    +                    + "from "
    +                    + fullDataTableName;
    +            rs = conn.createStatement().executeQuery("EXPLAIN " + indexSelectSql);
    +            assertEquals(localIndex ? "CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_"
+ fullDataTableName
    +                    + " [-32768]\nCLIENT MERGE SORT" : "CLIENT PARALLEL 1-WAY FULL SCAN
OVER INDEX_TEST.IDX",
    +                    QueryUtil.getExplainPlan(rs));
    +            rs = conn.createStatement().executeQuery(indexSelectSql);
    +            verifyResult(rs, 1);
    +            verifyResult(rs, 2);
    +
    +            // Insert two more rows to the index data table
    +            String upsert = "UPSERT INTO " + fullDataTableName
    +                    + " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    +            stmt = conn.prepareStatement(upsert);
    +            insertRow(stmt, 3);
    +            insertRow(stmt, 4);
    +            conn.commit();
    +
    +            rs = conn.createStatement().executeQuery(indexSelectSql);
    +            verifyResult(rs, 1);
    +            verifyResult(rs, 2);
    +            // verify that two rows added after index was created were also added to
    +            // the index table
    +            verifyResult(rs, 3);
    +            verifyResult(rs, 4);
    +
    +            // update the first row
    +            upsert = "UPSERT INTO "
    +                    + fullDataTableName
    +                    + "(varchar_pk, char_pk, int_pk, long_pk, decimal_pk, date_pk, a.varchar_col1)
VALUES(?, ?, ?, ?, ?, ?, ?)";
    +
    +            stmt = conn.prepareStatement(upsert);
    +            stmt.setString(1, "varchar1");
    +            stmt.setString(2, "char1");
    +            stmt.setInt(3, 1);
    +            stmt.setLong(4, 1l);
    +            stmt.setBigDecimal(5, new BigDecimal(1.0));
    +            stmt.setDate(6, DateUtil.parseDate("2015-01-01 00:00:00"));
    +            stmt.setString(7, "a.varchar_updated");
    +            stmt.executeUpdate();
    +            conn.commit();
    +
    +            // verify only one row was updated in the data table
    +            String selectSql = "UPPER(varchar_pk) || '_' || UPPER(char_pk) || '_' ||
UPPER(varchar_col1) || '_' || UPPER(char_col2) from "
    +                    + fullDataTableName;
    +            rs = conn.createStatement().executeQuery("SELECT /*+ NO_INDEX */ " + selectSql);
    +            assertTrue(rs.next());
    +            assertEquals("VARCHAR1_CHAR1 _A.VARCHAR_UPDATED_B.CHAR1   ", rs.getString(1));
    +            assertTrue(rs.next());
    +            assertEquals("VARCHAR2_CHAR2 _A.VARCHAR2_B.CHAR2   ", rs.getString(1));
    +            assertTrue(rs.next());
    +            assertEquals("VARCHAR3_CHAR3 _A.VARCHAR3_B.CHAR3   ", rs.getString(1));
    +            assertTrue(rs.next());
    +            assertEquals("VARCHAR4_CHAR4 _A.VARCHAR4_B.CHAR4   ", rs.getString(1));
    +            assertFalse(rs.next());
    +
    +            // verify that the rows in the index table are also updated
    +            rs = conn.createStatement().executeQuery("SELECT " + selectSql);
    +            assertTrue(rs.next());
    +            // if the data table is immutable, the index table will have one more
    +            // row
    --- End diff --
    
    Its because of the fixed width char columns.


> Support functional indexes
> --------------------------
>
>                 Key: PHOENIX-514
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-514
>             Project: Phoenix
>          Issue Type: Task
>            Reporter: James Taylor
>            Assignee: Thomas D'Silva
>              Labels: enhancement
>
> Instead of only defining the set of columns from the data table that make up an index,
you should be able to use expressions.  For example:
>       CREATE INDEX upper_last_name_idx ON person (UPPER(last_name))
> Then in queries that use UPPER(last_name), we can replace them with column references
to the index table.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message