[PHOENIX-2213] ARRAY_LENGTH fails for Decimal type array

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 4.4.0
• Fix Version/s: 4.4.0, 4.5.0

Environment

HortonWorks HDP 2.3_1 using phoenix-4.4.0.2.3.0.0-2557-client.jar running under VirtualBox 4.3.30 r101601 on Mac OS X 10.10.5.

• Assignee: Dumindu Buddhika
• Reporter: Ken Taylor
• Created: 26/Aug/15 20:39
• Updated: 21/Nov/15 02:16
• Resolved: 31/Aug/15 09:10

Description

ARRAY_LENGTH generates a type mismatch if the array is DECIMAL type.

Sample code:

!outputformat vertical
DROP TABLE IF EXISTS mytable;
CREATE TABLE IF NOT EXISTS mytable
(id UNSIGNED_INT NOT NULL,
b.myarray1 INTEGER ARRAY[10],
c.myarray2 VARCHAR ARRAY[20],
d.myarray3 DECIMAL(4,2) ARRAY[30],
CONSTRAINT pk PRIMARY KEY (id));
UPSERT INTO mytable(id, myarray1, myarray2, myarray3)
VALUES(1,ARRAY[1],ARRAY['a','b'],ARRAY[1.11,2.22,3.33]);
select * from mytable;
SELECT ARRAY_LENGTH(myarray1) from mytable;
SELECT ARRAY_LENGTH(myarray2) from mytable;
SELECT ARRAY_LENGTH(myarray3) from mytable;

Produces the following output run under SQLline:

0: jdbc:phoenix:localhost:2181:/hbase> !outputformat vertical
0: jdbc:phoenix:localhost:2181:/hbase> DROP TABLE IF EXISTS mytable;
No rows affected (3.876 seconds)
0: jdbc:phoenix:localhost:2181:/hbase> CREATE TABLE IF NOT EXISTS mytable
. . . . . . . . . . . . . . . . . . .> (id UNSIGNED_INT NOT NULL,
. . . . . . . . . . . . . . . . . . .> b.myarray1 INTEGER ARRAY[10],
. . . . . . . . . . . . . . . . . . .> c.myarray2 VARCHAR ARRAY[20],
. . . . . . . . . . . . . . . . . . .> d.myarray3 DECIMAL(4,2) ARRAY[30],
. . . . . . . . . . . . . . . . . . .> CONSTRAINT pk PRIMARY KEY (id));
No rows affected (1.387 seconds)
0: jdbc:phoenix:localhost:2181:/hbase> UPSERT INTO mytable(id, myarray1, myarray2, myarray3)
. . . . . . . . . . . . . . . . . . .> VALUES(1,ARRAY[1],ARRAY['a','b'],ARRAY[1.11,2.22,3.33]);
1 row affected (0.027 seconds)
0: jdbc:phoenix:localhost:2181:/hbase> select * from mytable;
ID 1
MYARRAY1 [1]
MYARRAY2 ['a', 'b']
MYARRAY3 [1.11, 2.22, 3.33]

1 row selected (0.017 seconds)
0: jdbc:phoenix:localhost:2181:/hbase> SELECT ARRAY_LENGTH(myarray1) from mytable;
ARRAY_LENGTH(B.MYARRAY1) 1

1 row selected (0.015 seconds)
0: jdbc:phoenix:localhost:2181:/hbase> SELECT ARRAY_LENGTH(myarray2) from mytable;
ARRAY_LENGTH(C.MYARRAY2) 2

1 row selected (0.015 seconds)
0: jdbc:phoenix:localhost:2181:/hbase> SELECT ARRAY_LENGTH(myarray3) from mytable;
Error: ERROR 203 (22005): Type mismatch. expected: [BINARY ARRAY, VARBINARY] but was: DECIMAL ARRAY at ARRAY_LENGTH argument 1 (state=22005,code=203)
org.apache.phoenix.schema.ArgumentTypeMismatchException: ERROR 203 (22005): Type mismatch. expected: [BINARY ARRAY, VARBINARY] but was: DECIMAL ARRAY at ARRAY_LENGTH argument 1
at org.apache.phoenix.parse.FunctionParseNode.validate(FunctionParseNode.java:200)
at org.apache.phoenix.compile.ExpressionCompiler.visitLeave(ExpressionCompiler.java:325)
at org.apache.phoenix.compile.ProjectionCompiler$SelectClauseVisitor.visitLeave(ProjectionCompiler.java:637)
at org.apache.phoenix.compile.ProjectionCompiler$SelectClauseVisitor.visitLeave(ProjectionCompiler.java:538)
at org.apache.phoenix.parse.FunctionParseNode.accept(FunctionParseNode.java:87)
at org.apache.phoenix.compile.ProjectionCompiler.compile(ProjectionCompiler.java:396)
at org.apache.phoenix.compile.QueryCompiler.compileSingleFlatQuery(QueryCompiler.java:542)
at org.apache.phoenix.compile.QueryCompiler.compileSingleQuery(QueryCompiler.java:493)
at org.apache.phoenix.compile.QueryCompiler.compileSelect(QueryCompiler.java:205)
at org.apache.phoenix.compile.QueryCompiler.compile(QueryCompiler.java:162)
at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:364)
at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:338)
at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:246)
at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:241)
at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
at org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:240)
at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1250)
at sqlline.Commands.execute(Commands.java:822)
at sqlline.Commands.sql(Commands.java:732)
at sqlline.SqlLine.dispatch(SqlLine.java:808)
at sqlline.SqlLine.begin(SqlLine.java:681)
at sqlline.SqlLine.start(SqlLine.java:398)
at sqlline.SqlLine.main(SqlLine.java:292)
This was on phoenix-4.4.0.2.3.0.0-2557-client.jar