phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kathiresan S <kathiresanselva...@gmail.com>
Subject Re: Phoenix Array Type Null Element
Date Thu, 07 May 2015 17:24:48 GMT
*An update on TIMESTAMP ARRAY test (for null elements):*

CREATE TABLE TIMESTAMPARRAYTEST (ID VARCHAR, TS TIMESTAMP ARRAY CONSTRAINT
PK PRIMARY KEY(ID))

Connection connection = getConnection();
Timestamp ts1 = new Timestamp(120550);
Timestamp ts2 = new Timestamp(120750);
Timestamp[] timeStampArray = new Timestamp[2];
timeStampArray[0] = ts1;
timeStampArray[1] = null;
PhoenixArray array = new PhoenixArray(PTimestamp.INSTANCE, timeStampArray);
PreparedStatement stmt = connection.prepareStatement("upsert into
timestamparraytest values (?, ?)");
stmt.setString(1, "123");
stmt.setObject(2, array);
stmt.executeUpdate();
connection.commit();

java.sql.SQLException: ERROR 201 (22000): Illegal data. TIMESTAMP may not
be null
at
org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newException(SQLExceptionCode.java:386)
at
org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:145)
at
org.apache.phoenix.schema.types.PDataType.newIllegalDataException(PDataType.java:293)
at org.apache.phoenix.schema.types.PTimestamp.toBytes(PTimestamp.java:43)
at
org.apache.phoenix.schema.types.PhoenixArray.toBytes(PhoenixArray.java:232)
at
org.apache.phoenix.schema.types.PArrayDataType.createArrayBytes(PArrayDataType.java:438)
at
org.apache.phoenix.schema.types.PArrayDataType.toBytes(PArrayDataType.java:89)
at
org.apache.phoenix.schema.types.PTimestampArray.toBytes(PTimestampArray.java:61)
at
org.apache.phoenix.expression.LiteralExpression.newConstant(LiteralExpression.java:174)
at
org.apache.phoenix.expression.LiteralExpression.newConstant(LiteralExpression.java:143)
at
org.apache.phoenix.compile.UpsertCompiler$UpsertValuesCompiler.visit(UpsertCompiler.java:858)
at
org.apache.phoenix.compile.UpsertCompiler$UpsertValuesCompiler.visit(UpsertCompiler.java:842)
at org.apache.phoenix.parse.BindParseNode.accept(BindParseNode.java:47)
at
org.apache.phoenix.compile.UpsertCompiler.compile(UpsertCompiler.java:733)
at
org.apache.phoenix.jdbc.PhoenixStatement$ExecutableUpsertStatement.compilePlan(PhoenixStatement.java:525)
at
org.apache.phoenix.jdbc.PhoenixStatement$ExecutableUpsertStatement.compilePlan(PhoenixStatement.java:513)
at
org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:299)
at
org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:292)
at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
at
org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:290)
at
org.apache.phoenix.jdbc.PhoenixPreparedStatement.executeUpdate(PhoenixPreparedStatement.java:200)

Thanks,
Kathir

On Thu, May 7, 2015 at 11:43 AM, Alex Kamil <alex.kamil@gmail.com> wrote:

> wouldn't converting to 0.0 instead of NULL create incorrect results in
> functions (like AVG, STDEV etc)?
>
> On Thu, May 7, 2015 at 11:14 AM, James Taylor <jamestaylor@apache.org>
> wrote:
>
>> Agreed, we should make it uniform. See PHOENIX-777 - that'd be great
>> if Dumindu could work on that next. Take care to maintain backward
>> compatibility in our array serialization format.
>>
>> Thanks,
>> James
>>
>> On Thu, May 7, 2015 at 8:08 AM, Vasudevan, Ramkrishna S
>> <ramkrishna.s.vasudevan@intel.com> wrote:
>> > James,
>> >
>> >
>> >
>> > It is not always true.  For double we tend to convert the null to 0.0
>> > instead of throwing exception.
>> >
>> > We should make the behavior uniform in this case.  For varchar we
>> support
>> > NULLs anyway explicitly.
>> >
>> >
>> >
>> > Regards
>> >
>> > Ram
>> >
>> >
>> >
>> > From: James Taylor [mailto:jamestaylor@apache.org]
>> > Sent: Thursday, May 7, 2015 8:27 PM
>> >
>> >
>> > To: user@phoenix.apache.org
>> > Subject: Re: Phoenix Array Type Null Element
>> >
>> >
>> >
>> > Note that for an array of primitive types (double, float, BIGINT,
>> integer,
>> > smallint, tinyint), we don't support inserting null as an array element.
>> >
>> > On Thursday, May 7, 2015, Vasudevan, Ramkrishna S
>> > <ramkrishna.s.vasudevan@intel.com> wrote:
>> >
>> > Thanks for the detailed update, Kathir.
>> >
>> >
>> >
>> > From: Kathiresan S [mailto:kathiresanselvaraj@gmail.com]
>> > Sent: Thursday, May 7, 2015 7:50 PM
>> > To: user@phoenix.apache.org
>> > Subject: Re: Phoenix Array Type Null Element
>> >
>> >
>> >
>> > FYI - the version of code i used for bigint array and double array
>> testing
>> > is (4.4.0-HBase-0.98-rc0 + Patch from
>> > https://issues.apache.org/jira/browse/PHOENIX-1949 ). Now, the varchar
>> array
>> > works fine after applying the patch.
>> >
>> >
>> >
>> > And regarding Double array null elements being returned as 0.0, is it
>> > intended? We expect null from PhoenixArray object, when we go though the
>> > elements list in it.
>> >
>> >
>> >
>> > Thanks,
>> >
>> > Kathir
>> >
>> >
>> >
>> > On Thu, May 7, 2015 at 10:06 AM, Vasudevan, Ramkrishna S
>> > <ramkrishna.s.vasudevan@intel.com> wrote:
>> >
>> > Thanks for the info. The NPE cases that was happening may get solved
>> with
>> > the patch that I had attached.
>> >
>> >
>> >
>> > But for the BIGINT case we need to see the reason.
>> >
>> >
>> >
>> > Regards
>> >
>> > Ram
>> >
>> >
>> >
>> > From: Kathiresan S [mailto:kathiresanselvaraj@gmail.com]
>> > Sent: Thursday, May 7, 2015 7:24 PM
>> >
>> >
>> > To: user@phoenix.apache.org
>> > Subject: Re: Phoenix Array Type Null Element
>> >
>> >
>> >
>> > Yes Ram, i get below issues
>> >
>> >
>> >
>> > BIGINT ARRAY
>> >
>> >
>> >
>> > CREATE TABLE BIARRAYNULLTEST1 (ID VARCHAR, SALES BIGINT ARRAY
>> CONSTRAINT PK
>> > PRIMARY KEY (ID))
>> >
>> >
>> >
>> > Hbase>UPSERT INTO BIARRAYNULLTEST1 (ID, SALES)
>> VALUES('123',ARRAY[1,2,null])
>> >
>> > org.apache.phoenix.schema.TypeMismatchException: ERROR 203 (22005): Type
>> > mismatch. INTEGER and DECIMAL for -1.010E+126
>> >
>> > at
>> >
>> org.apache.phoenix.schema.TypeMismatchException.newException(TypeMismatchException.java:53)
>> >
>> > at
>> >
>> org.apache.phoenix.expression.LiteralExpression.newConstant(LiteralExpression.java:171)
>> >
>> > at
>> >
>> org.apache.phoenix.expression.LiteralExpression.newConstant(LiteralExpression.java:143)
>> >
>> > at
>> >
>> org.apache.phoenix.expression.LiteralExpression.newConstant(LiteralExpression.java:135)
>> >
>> > at
>> >
>> org.apache.phoenix.compile.ExpressionCompiler.visitLeave(ExpressionCompiler.java:1561)
>> >
>> > at
>> >
>> org.apache.phoenix.compile.ExpressionCompiler.visitLeave(ExpressionCompiler.java:141)
>> >
>> > at
>> >
>> org.apache.phoenix.parse.ArrayConstructorNode.accept(ArrayConstructorNode.java:43)
>> >
>> > at
>> >
>> org.apache.phoenix.compile.UpsertCompiler.compile(UpsertCompiler.java:733)
>> >
>> > at
>> >
>> org.apache.phoenix.jdbc.PhoenixStatement$ExecutableUpsertStatement.compilePlan(PhoenixStatement.java:525)
>> >
>> > at
>> >
>> org.apache.phoenix.jdbc.PhoenixStatement$ExecutableUpsertStatement.compilePlan(PhoenixStatement.java:513)
>> >
>> > at
>> >
>> org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:299)
>> >
>> > at
>> >
>> org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:292)
>> >
>> > at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
>> >
>> > at
>> >
>> org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:290)
>> >
>> > at
>> >
>> org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:222)
>> >
>> > at
>> >
>> org.apache.phoenix.jdbc.PhoenixPreparedStatement.execute(PhoenixPreparedStatement.java:173)
>> >
>> > at
>> >
>> org.apache.phoenix.jdbc.PhoenixPreparedStatement.execute(PhoenixPreparedStatement.java:178)
>> >
>> >
>> >
>> > Any problem with the syntax above for upsert of bigint array?
>> >
>> >
>> >
>> > DOUBLE ARRAY
>> >
>> >
>> >
>> > I'm getting back 0.0 instead of null references from the array when i
>> do a
>> > select
>> >
>> >
>> >
>> > CREATE TABLE DBLARRAYNULLTEST (ID VARCHAR, AMOUNTS DOUBLE ARRAY
>> CONSTRAINT
>> > PK PRIMARY KEY(ID))
>> >
>> >
>> >
>> > UPSERT INTO DBLARRAYNULLTEST(ID,AMOUNTS)
>> VALUES('123',ARRAY[1.0,null,2.0])
>> >
>> > SELECT * FROM DBLARRAYNULLTEST
>> >
>> > 123|[1.0, 0.0, 2.0]
>> >
>> >
>> >
>> > UPSERT INTO DBLARRAYNULLTEST(ID,AMOUNTS)
>> VALUES('123',ARRAY[null,1.0,2.0])
>> >
>> > SELECT * FROM DBLARRAYNULLTEST
>> >
>> > 123|[0.0, 1.0, 2.0]
>> >
>> >
>> >
>> > Also, we are planning to use TIMESTAMP array with possible null values
>> in
>> > it. I'll test that as well and will let you know.
>> >
>> >
>> >
>> > Below are the critical ones for us that should accept null values
>> >
>> >
>> >
>> > VARCHAR ARRAY (You have fixed this yesterday)
>> >
>> > BIGINT ARRAY
>> >
>> > DOUBLE ARRAY
>> >
>> > TIMESTAMP ARRAY
>> >
>> >
>> >
>> > Thanks,
>> >
>> > Kathir
>> >
>> >
>> >
>> > On Thu, May 7, 2015 at 2:47 AM, Vasudevan, Ramkrishna S
>> > <ramkrishna.s.vasudevan@intel.com> wrote:
>> >
>> > Hi Kathir
>> >
>> >
>> >
>> > Ideally nulls should work with all the ARRAY types.  Do you find any
>> issues
>> > in the behavior of these ARRAY data types when you use ‘nulls’?
>> >
>> >
>> >
>> > Regards
>> >
>> > Ram
>> >
>> >
>> >
>> > From: Kathiresan S [mailto:kathiresanselvaraj@gmail.com]
>> > Sent: Wednesday, May 6, 2015 4:59 PM
>> > To: user@phoenix.apache.org
>> > Subject: Re: Phoenix Array Type Null Element
>> >
>> >
>> >
>> > What are the other Array base data types (like VARCHAR), that could take
>> > null values?
>> >
>> >
>> >
>> > Do the below data types allow NULL as well?
>> >
>> >
>> >
>> > BIGINT ARRAY, DOUBLE ARRAY, DATE ARRAY, VARBINARY ARRAY
>> >
>> >
>> >
>> > Thanks,
>> >
>> > Kathir
>> >
>> >
>> >
>> > On Wed, May 6, 2015 at 6:35 AM, Kathiresan S <
>> kathiresanselvaraj@gmail.com>
>> > wrote:
>> >
>> > Thanks Ram.
>> >
>> >
>> >
>> >
>> >
>> > Thanks,
>> >
>> > Kathir
>> >
>> >
>> >
>> > On Wed, May 6, 2015 at 1:56 AM, Vasudevan, Ramkrishna S
>> > <ramkrishna.s.vasudevan@intel.com> wrote:
>> >
>> > I raised https://issues.apache.org/jira/browse/PHOENIX-1949 for fixing
>> this.
>> >
>> >
>> >
>> > From: Vasudevan, Ramkrishna S [mailto:ramkrishna.s.vasudevan@intel.com]
>> > Sent: Wednesday, May 6, 2015 9:21 AM
>> > To: user@phoenix.apache.org
>> > Subject: RE: Phoenix Array Type Null Element
>> >
>> >
>> >
>> > Ideally varchar array should support NULL. This seems to be a bug.  Can
>> you
>> > file a JIRA for this?
>> >
>> > I can come up with a patch ASAP.
>> >
>> >
>> >
>> > Regards
>> >
>> > Ram
>> >
>> >
>> >
>> > From: Kathiresan S [mailto:kathiresanselvaraj@gmail.com]
>> > Sent: Tuesday, May 5, 2015 10:23 PM
>> > To: user@phoenix.apache.org
>> > Subject: Phoenix Array Type Null Element
>> >
>> >
>> >
>> > Hi,
>> >
>> >
>> >
>> > Is it possible to insert null elements in an array type column?
>> >
>> >
>> >
>> > CREATE TABLE ARRAYTEST124 (ID VARCHAR, NAME VARCHAR ARRAY CONSTRAINT PK
>> > PRIMARY KEY(ID))
>> >
>> >
>> >
>> > UPSERT INTO ARRAYTEST124 (ID, NAME)
>> VALUES('123',ARRAY['ABC','XYZ',null])
>> >
>> > UPSERT INTO ARRAYTEST124 (ID, NAME)
>> VALUES('123',ARRAY['ABC',null,'XYZ'])
>> >
>> > UPSERT INTO ARRAYTEST124 (ID, NAME)
>> VALUES('123',ARRAY[null,'ABC','XYZ'])
>> >
>> >
>> >
>> > I'm using phoenix 4.4.0-HBase-0.98-rc0
>> >
>> >
>> >
>> > I'm getting a null pointer exception, while trying to do the above
>> upserts
>> >
>> >
>> >
>> > java.lang.NullPointerException
>> >
>> >             at
>> > org.apache.phoenix.schema.types.PVarchar.toObject(PVarchar.java:62)
>> >
>> >             at
>> > org.apache.phoenix.schema.types.PDataType.toObject(PDataType.java:979)
>> >
>> >             at
>> > org.apache.phoenix.schema.types.PDataType.toObject(PDataType.java:992)
>> >
>> >             at
>> >
>> org.apache.phoenix.compile.ExpressionCompiler.visitLeave(ExpressionCompiler.java:1275)
>> >
>> >             at
>> >
>> org.apache.phoenix.compile.ExpressionCompiler.visitLeave(ExpressionCompiler.java:142)
>> >
>> >             at
>> >
>> org.apache.phoenix.parse.ArrayConstructorNode.accept(ArrayConstructorNode.java:43)
>> >
>> >             at
>> >
>> org.apache.phoenix.compile.UpsertCompiler.compile(UpsertCompiler.java:733)
>> >
>> >             at
>> >
>> org.apache.phoenix.jdbc.PhoenixStatement$ExecutableUpsertStatement.compilePlan(PhoenixStatement.java:525)
>> >
>> >             at
>> >
>> org.apache.phoenix.jdbc.PhoenixStatement$ExecutableUpsertStatement.compilePlan(PhoenixStatement.java:513)
>> >
>> >             at
>> >
>> org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:299)
>> >
>> >             at
>> >
>> org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:292)
>> >
>> >             at
>> org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
>> >
>> >             at
>> >
>> org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:290)
>> >
>> >             at
>> >
>> org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:222)
>> >
>> >             at
>> >
>> org.apache.phoenix.jdbc.PhoenixPreparedStatement.execute(PhoenixPreparedStatement.java:173)
>> >
>> >             at
>> >
>> org.apache.phoenix.jdbc.PhoenixPreparedStatement.execute(PhoenixPreparedStatement.java:178)
>> >
>> >
>> >
>> > Thanks,
>> >
>> > Kathir
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>>
>
>

Mime
View raw message