You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by Anirudha Khanna <ak...@marinsoftware.com> on 2015/05/21 13:37:10 UTC
WHERE filter on VARCHAR
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
Re: WHERE filter on VARCHAR
Posted by Alicia Shu <as...@hortonworks.com>.
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 <se...@gmail.com>>
Reply-To: "user@phoenix.apache.org<ma...@phoenix.apache.org>" <us...@phoenix.apache.org>>
Date: Thursday, May 21, 2015 at 5:45 AM
To: "user@phoenix.apache.org<ma...@phoenix.apache.org>" <us...@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 <ak...@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
Re: WHERE filter on VARCHAR
Posted by Sergey Belousov <se...@gmail.com>.
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 <ak...@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
>