You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@phoenix.apache.org by "Robert Roland (JIRA)" <ji...@apache.org> on 2014/04/23 21:04:19 UTC

[jira] [Created] (PHOENIX-947) Zero length strings are incorrectly treated as NULL

Robert Roland created PHOENIX-947:
-------------------------------------

             Summary: Zero length strings are incorrectly treated as NULL 
                 Key: PHOENIX-947
                 URL: https://issues.apache.org/jira/browse/PHOENIX-947
             Project: Phoenix
          Issue Type: Bug
    Affects Versions: 3.0.0
            Reporter: Robert Roland


Given the following table schema, you should be able to insert an empty string into the TESTSTR column:

{code}
CREATE TABLE TEST1 (
    ID INTEGER NOT NULL PRIMARY KEY,
    TESTSTR VARCHAR(255) NOT NULL,
    TESTSTR2 VARCHAR(255)
);

UPSERT INTO TEST1 (ID, TESTSTR, TESTSTR2)
VALUES (1, '', 'foo');
{code}

Instead, you are given an error:

{noformat}
org.apache.phoenix.schema.ConstraintViolationException: TEST1.TESTSTR may not be null
    at org.apache.phoenix.schema.PTableImpl$PRowImpl.setValue(PTableImpl.java:618)
    at org.apache.phoenix.execute.MutationState.addRowMutations(MutationState.java:176)
    at org.apache.phoenix.execute.MutationState.commit(MutationState.java:350)
    at org.apache.phoenix.jdbc.PhoenixConnection.commit(PhoenixConnection.java:351)
    at org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:229)
    at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:919)
    at sqlline.SqlLine$Commands.execute(SqlLine.java:3673)
    at sqlline.SqlLine$Commands.sql(SqlLine.java:3584)
    at sqlline.SqlLine.dispatch(SqlLine.java:821)
    at sqlline.SqlLine.begin(SqlLine.java:699)
    at sqlline.SqlLine.mainWithInputRedirection(SqlLine.java:441)
    at sqlline.SqlLine.main(SqlLine.java:424)
{noformat}

SQL92 specifies that zero-length strings are not NULL. SQL99 changes this a bit:

"In SQL-92, if the value of a character value expression was a zero-length string or if a zero-length character string was assigned to a target, there were no exception conditions permitted. In SQL:1999, it is implementation-defined whether in these circumstances an exception condition is raised: data-exception--zero-length character string. (This rule provides support for some SQL implementations that do not distinguish between zero-length character strings and the null value.)"
 - p 777, SQL:1999 - Understanding Relational Language Components, Jim Melton, Alan R. Simpson

While SQL99 relaxed this restriction, it would be ideal to be able to represent a zero length string as NOT NULL.



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