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 <akha...@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 >
