[PHOENIX-6619] Secondary indexes on the columns with a default value works incorrectly

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 5.1.2
• Component/s: core
• Assignee: Sergey Soldatov
• Reporter: Sergey Soldatov
• Created: 04/Jan/22 18:27
• Updated: 05/Jan/22 16:25
• Resolved: 05/Jan/22 16:25

Description

When we create an index on the column that has a default value, this value is always used in the index table.

0: jdbc:phoenix:> CREATE TABLE T5(I1 INTEGER NOT NULL, S1 VARCHAR NOT NULL, D1 DATE  DEFAULT DATE'1900-01-01', CONSTRAINT PK PRIMARY KEY(I1,S1));
No rows affected (1.165 seconds)
0: jdbc:phoenix:> upsert into T5 values (1, 'test', '1900-02-02');
1 row affected (0.086 seconds)
0: jdbc:phoenix:> create index I5 on T5 (d1);
select * from t5; 
1 row affected (6.115 seconds)
0: jdbc:phoenix:> select * from t5;
+----+------+------------+
| I1 |  S1  |     D1     |
+----+------+------------+
| 1  | test | 1900-01-01 |
+----+------+------------+
1 row selected (0.261 seconds)
0: jdbc:phoenix:> drop table t5;
No rows affected (1.552 seconds)
0: jdbc:phoenix:> CREATE TABLE T5(I1 INTEGER NOT NULL, S1 VARCHAR NOT NULL, D1 DATE  DEFAULT DATE'1900-01-01', CONSTRAINT PK PRIMARY KEY(I1,S1));
No rows affected (1.162 seconds)
0: jdbc:phoenix:> upsert into T5 values (1, 'test', '1900-02-02');
1 row affected (0.082 seconds)
0: jdbc:phoenix:> select * from t5;
+----+------+------------+
| I1 |  S1  |     D1     |
+----+------+------------+
| 1  | test | 1900-02-02 |
+----+------+------------+
1 row selected (0.141 seconds)
0: jdbc:phoenix:> create index I5 on T5 (d1);
1 row affected (6.065 seconds)
0: jdbc:phoenix:> select * from t5;
+----+------+------------+
| I1 |  S1  |     D1     |
+----+------+------------+
| 1  | test | 1900-01-01 |
+----+------+------------+
1 row selected (0.268 seconds)
0: jdbc:phoenix:> upsert into T5 values (2, 'test2', '1900-03-03');
1 row affected (0.088 seconds)
0: jdbc:phoenix:> select * from t5;
+----+-------+------------+
| I1 |  S1   |     D1     |
+----+-------+------------+
| 1  | test  | 1900-01-01 |
| 2  | test2 | 1900-01-01 |
+----+-------+------------+
2 rows selected (0.278 seconds)

 This also may lead to an exception during the index creation:

0: jdbc:phoenix:> CREATE TABLE T6 (C1 CHAR(10) NOT NULL default ' '  , I1 CHAR(14) DEFAULT ' ',CONSTRAINT PK PRIMARY KEY(C1));
No rows affected (1.163 seconds)
0: jdbc:phoenix:> create index i6 on t6 (I1, C1);
java.lang.ArrayIndexOutOfBoundsException: 127
	at org.apache.phoenix.index.IndexMaintainer.initCachedState(IndexMaintainer.java:1763)
	at org.apache.phoenix.index.IndexMaintainer.<init>(IndexMaintainer.java:629)
	at org.apache.phoenix.index.IndexMaintainer.create(IndexMaintainer.java:146)
	at org.apache.phoenix.schema.PTableImpl.getIndexMaintainer(PTableImpl.java:1660)
	at org.apache.phoenix.compile.ServerBuildIndexCompiler.compile(ServerBuildIndexCompiler.java:103)
	at org.apache.phoenix.schema.MetaDataClient.getMutationPlanForBuildingIndex(MetaDataClient.java:1391)
	at org.apache.phoenix.schema.MetaDataClient.buildIndex(MetaDataClient.java:1400)
	at org.apache.phoenix.schema.MetaDataClient.createIndex(MetaDataClient.java:1811)
	at org.apache.phoenix.compile.CreateIndexCompiler$1.execute(CreateIndexCompiler.java:85)
	at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:547)
	at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:513)
	at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
	at org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:512)
	at org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:500)
	at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:2162)
	at sqlline.Commands.executeSingleQuery(Commands.java:1054)
	at sqlline.Commands.execute(Commands.java:1003)
	at sqlline.Commands.sql(Commands.java:967)
	at sqlline.SqlLine.dispatch(SqlLine.java:734)
	at sqlline.SqlLine.begin(SqlLine.java:541)
	at sqlline.SqlLine.start(SqlLine.java:267)
	at sqlline.SqlLine.main(SqlLine.java:206)
0: jdbc:phoenix:> 

Issue Links

• duplicates: PHOENIX-5865 column that has default value can not be correctly indexed