[
https://issues.apache.org/jira/browse/PHOENIX-7032?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17783399#comment-17783399
]
ASF GitHub Bot commented on PHOENIX-7032:
-----------------------------------------
jpisaac commented on PR #1701:
URL: https://github.com/apache/phoenix/pull/1701#issuecomment-1797011691
```
CREATE TABLE IF NOT EXISTS S.T1(OID CHAR(15) NOT NULL,KP CHAR(3) NOT NULL,
COL1 VARCHAR, CREATED_DATE DATE,CREATED_BY CHAR(15),LAST_UPDATE
DATE,LAST_UPDATE_BY CHAR(15),SYSTEM_MODSTAMP DATE CONSTRAINT pk PRIMARY KEY
(OID,KP)) MULTI_TENANT=true,COLUMN_ENCODED_BYTES=0;
CREATE VIEW IF NOT EXISTS S.G1_001(ID1 INTEGER not null,ID2 VARCHAR not
null,ID3 INTEGER not null, ROW_ID CHAR(15) not null, COL2 VARCHAR CONSTRAINT pk
PRIMARY KEY (ID1, ID2, ID3, ROW_ID)) AS SELECT * FROM S.T1 WHERE KP = '001';
CREATE INDEX IF NOT EXISTS PI1_COL2_INDEX ON S.G1_001 (COL2) INCLUDE(ROW_ID,
SYSTEM_MODSTAMP) WHERE COL2 IS NOT NULL;
```
Fails with the following exception -
0: jdbc:phoenix:localhost> CREATE INDEX IF NOT EXISTS PI1_COL2_INDEX ON
S.G1_001 (COL2) INCLUDE(ROW_ID, SYSTEM_MODSTAMP) WHERE COL2 IS NOT NULL;
Error: ERROR 1046 (43A03): Column updated in VIEW may not differ from value
specified in WHERE clause. value='a' columnName=KP (state=43A03,code=1046)
java.sql.SQLException: ERROR 1046 (43A03): Column updated in VIEW may not
differ from value specified in WHERE clause. value='a' columnName=KP
at
org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newException(SQLExceptionCode.java:655)
at
org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:229)
at
org.apache.phoenix.compile.UpsertCompiler$UpsertValuesMutationPlan.execute(UpsertCompiler.java:1275)
at
org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:559)
at
org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:525)
at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
at
org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:524)
at
org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:512)
at
org.apache.phoenix.jdbc.PhoenixPreparedStatement.execute(PhoenixPreparedStatement.java:181)
at
org.apache.phoenix.compile.CreateIndexCompiler.verifyIndexWhere(CreateIndexCompiler.java:158)
at
org.apache.phoenix.compile.CreateIndexCompiler.compile(CreateIndexCompiler.java:195)
at
org.apache.phoenix.jdbc.PhoenixStatement$ExecutableCreateIndexStatement.compilePlan(PhoenixStatement.java:1384)
at
org.apache.phoenix.jdbc.PhoenixStatement$ExecutableCreateIndexStatement.compilePlan(PhoenixStatement.java:1370)
at
org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:543)
at
org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:525)
at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
at
org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:524)
at
org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:512)
at
org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:2216)
at sqlline.Commands.execute(Commands.java:814)
at sqlline.Commands.sql(Commands.java:754)
at sqlline.SqlLine.dispatch(SqlLine.java:646)
at sqlline.SqlLine.begin(SqlLine.java:510)
at sqlline.SqlLine.start(SqlLine.java:233)
at sqlline.SqlLine.main(SqlLine.java:175)
> Partial Global Secondary Indexes
> --------------------------------
>
> Key: PHOENIX-7032
> URL: https://issues.apache.org/jira/browse/PHOENIX-7032
> Project: Phoenix
> Issue Type: New Feature
> Reporter: Kadir Ozdemir
> Assignee: Kadir Ozdemir
> Priority: Major
>
> The secondary indexes supported in Phoenix have been full indexes such that
> for every data table row there is an index row. Generating an index row for
> every data table row is not always required. For example, some use cases do
> not require index rows for the data table rows in which indexed column values
> are null. Such indexes are called sparse indexes. Partial indexes generalize
> the concept of sparse indexing and allow users to specify the subset of the
> data table rows for which index rows will be maintained. This subset is
> specified using a WHERE clause added to the CREATE INDEX DDL statement.
> Partial secondary indexes were first proposed by Michael Stonebraker
> [here|https://dsf.berkeley.edu/papers/ERL-M89-17.pdf]. Since then several SQL
> databases (e.g.,
> [Postgres|https://www.postgresql.org/docs/current/indexes-partial.html] and
> [SQLite|https://www.sqlite.org/partialindex.html]) and NoSQL databases
> (e.g., [MongoDB|https://www.mongodb.com/docs/manual/core/index-partial/])
> have supported some form of partial indexes. It is challenging to allow
> arbitrary WHERE clauses in DDL statements. For example, Postgres does not
> allow subqueries in these where clauses and SQLite supports much more
> restrictive where clauses.
> Supporting arbitrary where clauses creates challenges for query optimizers in
> deciding the usability of a partial index for a given query. If the set of
> data table rows that satisfy the query is a subset of the data table rows
> that the partial index points back, then the query can use the index. Thus,
> the query optimizer has to decide if the WHERE clause of the query implies
> the WHERE clause of the index.
> Michael Stonebraker [here|https://dsf.berkeley.edu/papers/ERL-M89-17.pdf]
> suggests that an index WHERE clause is a conjunct of simple terms, i.e:
> i-clause-1 and i-clause-2 and ... and i-clause-m where each clause is of the
> form <column> <operator> <constant>. Hence, the qualification can be
> evaluated for each tuple in the indicated relation without consulting
> additional tuples.
> Phoenix partial indexes will initially support a more general set of index
> WHERE clauses that can be evaluated on a single row with the following
> exceptions
> * Subqueries are not allowed.
> * Like expressions are allowed with very limited support such that an index
> WHERE clause with like expressions can imply/contain a query if the query has
> the same like expressions that the index WHERE clause has.
> * Comparison between columns are allowed without supporting transitivity,
> for example, a > b and b > c does not imply a > c.
> Partial indexes will be supported initially for global secondary indexes,
> i.e., covered global indexes and uncovered global indexes. The local
> secondary indexes will be supported in future.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
