[jira] [Commented] (PHOENIX-4120) Can't get the records by select when the operator is "!=" and the specified column value is null

["Csaba Skrabak (JIRA)" <ji...@apache.org>]

    [ 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)