[jira] [Commented] (PHOENIX-82) Allow ARRAY to be compared and used at end of PK

["ramkrishna.s.vasudevan (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/PHOENIX-82?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13918029#comment-13918029 ] 

ramkrishna.s.vasudevan commented on PHOENIX-82:
-----------------------------------------------

{code}
                if ((colDef.getDataType() == PDataType.VARBINARY || colDef.getDataType().isArrayType())  
                        && SchemaUtil.isPKColumn(column)
                        && pkColumnsNames.size() > 1 
                        && column.getPosition() < pkColumnsNames.size() - 1) {
{code}
Is the above check right? If I have 
{code}
"CREATE TABLE t ( a_string_array VARCHAR(100) ARRAY[4], b_string_array VARCHAR(100) ARRAY[4], k VARCHAR  \n"
                                + " CONSTRAINT pk PRIMARY KEY (b_string_array, k))"
{code}
Now the pkColumnNames has 2 items and the positon of b_string_array is 1.  Now the above check (as we subtract 1) from it will not work correctly. Also other thing is I feel that we should check if the current column is in pkColumnsNames and if it is check the position of it with respect to the total no of columns.  What do you think?

> Allow ARRAY to be compared and used at end of PK
> ------------------------------------------------
>
>                 Key: PHOENIX-82
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-82
>             Project: Phoenix
>          Issue Type: Bug
>    Affects Versions: 3.0.0
>            Reporter: James Taylor
>            Assignee: ramkrishna.s.vasudevan
>             Fix For: 3.0.0
>
>
> Now that our ARRAY types are directly comparable, we can allow them to be used in comparison expressions such as array1 < array2. We should only allow arrays that have the same type to be comparable.
> We can also allow an ARRAY to be used in the PK constraint, as long as it's used at the end (as our KeyValueSchema doesn't know how to skip over it if it's used in the middle yet, though that'd be possible too).
> The following changes are needed:
> - In PDataType.isCoercibleTo for array types, return true only if the baseType.isBinaryComparable(otherType) is true.
> - In ComparisonExpression.create(CompareOp op, List<Expression> children, ImmutableBytesWritable ptr), remove the following block of code:
> {code}
>         // We don't yet support comparison between entire arrays
>         if ( ( (lhsExprDataType != null && lhsExprDataType.isArrayType()) || 
>                (rhsExprDataType != null && rhsExprDataType.isArrayType()) ) &&
>              ( op != CompareOp.EQUAL && op != CompareOp.NOT_EQUAL ) ) {
>             throw new SQLExceptionInfo.Builder(SQLExceptionCode.NON_EQUALITY_ARRAY_COMPARISON)
>             .setMessage(ComparisonExpression.toString(op, children)).build().buildException();
>         }
> {code}
> - In MetaDataClient.createTableInternal(), remove the code that prevents an array from being used in the PK:
> {code}
>                     // disallow array type usage in primary key constraint
>                     if (colDef.isArray()) {
>                         throw new SQLExceptionInfo.Builder(SQLExceptionCode.ARRAY_NOT_ALLOWED_IN_PRIMARY_KEY)
>                         .setSchemaName(schemaName)
>                         .setTableName(tableName)
>                         .setColumnName(colDef.getColumnDefName().getColumnName())
>                         .build().buildException();
>                     }
> {code}
> - Also in MetaDataClient.createTableInternal(), add a check for array here to insure it only appears at the end:
> {code}
>                 if (( colDef.getDataType() == PDataType.VARBINARY || PDataType.isArray(colDef.getDataType()) )
>                         && SchemaUtil.isPKColumn(column)
>                         && pkColumnsNames.size() > 1 
>                         && column.getPosition() < pkColumnsNames.size() - 1) {
> {code}
> - Remove test in QueryCompilerTest that tests that an array is not allowed in the PK.
> - Add tests that validate comparison between arrays in the where clause of a query.
> - Add tests that validate that an array may only be used at the end of the PK.
> - Add tests that validate a composite row key with an array at the end functions correct.



--
This message was sent by Atlassian JIRA
(v6.2#6252)