Mailing-List: contact dev-help@phoenix.incubator.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@phoenix.incubator.apache.org
Date: Fri, 2 May 2014 05:32:14 +0000 (UTC)
From: "James Taylor (JIRA)" <jira@apache.org>
To: dev@phoenix.incubator.apache.org
Message-ID: <JIRA.12710112.1398279835497.217978.1399008734605@arcas>
In-Reply-To: <JIRA.12710112.1398279835497@arcas>
References: <JIRA.12710112.1398279835497@arcas>
Subject: [jira] [Commented] (PHOENIX-947) Zero length strings are
 incorrectly treated as NULL
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


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

James Taylor commented on PHOENIX-947:
--------------------------------------

If/when we change our type system, we can entertain this. For now, null is the same as an empty string by design (as it is with Oracle).

> Zero length strings are incorrectly treated as NULL 
> ----------------------------------------------------
>
>                 Key: PHOENIX-947
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-947
>             Project: Phoenix
>          Issue Type: New Feature
>    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)