[
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)
