phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Alicia Shu <a...@hortonworks.com>
Subject Re: WHERE filter on VARCHAR
Date Thu, 21 May 2015 19:43:57 GMT
In your case, the "status" is nullable. You can get all the results back by using:

select * from TEST1 where "status" != 'deleted' or "status" is null;

Alicia

From: Sergey Belousov <sergey.belousov@gmail.com<mailto:sergey.belousov@gmail.com>>
Reply-To: "user@phoenix.apache.org<mailto:user@phoenix.apache.org>" <user@phoenix.apache.org<mailto:user@phoenix.apache.org>>
Date: Thursday, May 21, 2015 at 5:45 AM
To: "user@phoenix.apache.org<mailto:user@phoenix.apache.org>" <user@phoenix.apache.org<mailto:user@phoenix.apache.org>>
Subject: Re: WHERE filter on VARCHAR

Because NULL is not equal != 'deleted'.
Most databases I worked with treat null in such way (behaviour can be configured)
use http://phoenix.apache.org/language/functions.html#coalesce

In the ANSI SQL standard, the value of NULL is defined as unknown. It is not equal to anything,
not even another NULL value. Also, a null value is never not equal to another value. By default,
T-SQL adopts the same behavior, but it can be turned off using the SET ANSI_NULLS OFFcommand
or setting the database ANSI_NULLS option. In addition, certain database operations cannot
or should not be performed if ANSI_NULLS is turned off. Therefore, it is safest to make all
T-SQL code ANSI compliant.

http://www.hpenterprisesecurity.com/vulncat/en/vulncat/sql/code_correctness_erroneous_null_comparison_tsql.html


On Thu, May 21, 2015 at 7:37 AM, Anirudha Khanna <akhanna@marinsoftware.com<mailto:akhanna@marinsoftware.com>>
wrote:
Hi All,

Have a question regarding a Where clause filter on a string(varchar) column. My table is as
follows,

CREATE TABLE IF NOT EXISTS TEST1 (
"id" UNSIGNED_LONG NOT NULL,
"status" VARCHAR, CONSTRAINT "pk_1" PRIMARY KEY ("id")) VERSIONS = 2;

The table is populated to look like,
0: jdbc:phoenix:localhost> select * from TEST1;
+-----+----------+
| id  |  status  |
+-----+----------+
| 1   | live     |
| 2   | deleted  |
| 3   |          |            <-- row has status == NULL
+-----+----------+

Now if I want all the rows that do not have status as deleted, I use the query, select * from
TEST1 where "status" != 'deleted';
But this returns me only 1 row,
0: jdbc:phoenix:localhost> select * from TEST1 where "status" != 'deleted';
+-----+---------+
| id  | status  |
+-----+---------+
| 1   | live    |
+-----+---------+

Why is the row with a NULL status being filtered out?

Help appreciated.

Cheers,
Anirudha


Mime
View raw message