[
https://issues.apache.org/jira/browse/PHOENIX-4120?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16139754#comment-16139754
]
Csaba Skrabak commented on PHOENIX-4120:
----------------------------------------
I think this is the expected behavior in SQL in general. According to
three-valued logic, "name"!='alex' means NOT("name" = 'alex'), which evaluates
to NOT(NULL = 'alex') in the second row of your example. NULL = 'alex'
evaluates to Unknown, its negative is also Unknown, and Unknown in a WHERE
clause should filter out the row. Or am I wrong? See
[https://en.wikipedia.org/wiki/Null_(SQL)#Effect_of_Unknown_in_WHERE_clauses]
> Can't get the records by select when the operator is "!=" and the specified
> column value is null
> --------------------------------------------------------------------------------------------------
>
> Key: PHOENIX-4120
> URL: https://issues.apache.org/jira/browse/PHOENIX-4120
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 4.8.0
> Environment: phoenix-4.8.1-HBase-1.0-SNAPSHOT-server.jar
> Reporter: alexBai
> Labels: null-values
>
> {code:java}
> 0: jdbc:phoenix:> create table alex ("id" BIGINT, "name" varchar(10), "age"
> BIGINT, constraint pk primary key("id"));
> 2 rows affected (1.257 seconds)
> 0: jdbc:phoenix:> upsert into alex values(1, 'alex', 28);
> 1 row affected (0.071 seconds)
> 0: jdbc:phoenix:> upsert into alex values(2, null, 28);
> 1 row affected (0.012 seconds)
> 0: jdbc:phoenix:> select * from alex;
> +-----+-------+------+
> | id | name | age |
> +-----+-------+------+
> | 1 | alex | 28 |
> | 2 | | 28 |
> +-----+-------+------+
> 2 rows selected (0.063 seconds)
> 0: jdbc:phoenix:> select * from alex where "name"!='alex';
> +-----+-------+------+
> | id | name | age |
> +-----+-------+------+
> +-----+-------+------+
> No rows selected (0.053 seconds)
> 0: jdbc:phoenix:> select * from alex where "name" is null;
> +-----+-------+------+
> | id | name | age |
> +-----+-------+------+
> | 2 | | 28 |
> +-----+-------+------+
> {code}
> Does phoenix just design like that?
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
