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