[jira] [Comment Edited] (KUDU-1642) Add IS NULL predicate type

["Alexey Serbin (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/KUDU-1642?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15526919#comment-15526919 ] 

Alexey Serbin edited comment on KUDU-1642 at 9/27/16 5:55 PM:
--------------------------------------------------------------

Yes, psql supports NULL in IN-list predicates.  At least with PostgreSQL 9.3.  Probably, that's done to support sub-selects like {{SELECT * FROM x WHERE field_x IN (SELECT field_y FROM y}}.  By itself, {{WHERE field_x IN (NULL)}} should result in empty resultset by definition, since it's the same as {{WHERE field_x = NULL}}.

I'm not sure whether supporting that brings any value to the Kudu project as is since sub-selects are not supported in Kudu now, AFAIK. It's more about syntax consistency.

{noformat}
postgres@ubuntu-14:~$ psql
psql (9.3.13)
Type "help" for help.

postgres=# INSERT INTO x VALUES (0, 1);
INSERT 0 1
postgres=# INSERT INTO x VALUES (1, NULL);
INSERT 0 1
postgres=# SELECT * FROM x;
 a | b 
---+---
 0 | 1
 1 |  
(2 rows)

postgres=# SELECT * FROM x WHERE a IN (0, NULL);
 a | b 
---+---
 0 | 1
(1 row)

postgres=# SELECT * FROM x WHERE b IN (1, NULL);
 a | b 
---+---
 0 | 1
(1 row)

postgres=# SELECT * FROM x WHERE b IN (NULL);
 a | b 
---+---
(0 rows)
{noformat}


was (Author: aserbin):
Yes, psql supports NULL in IN-list predicates.  At least with PostgreSQL 9.3.  Probably, that's done to support sub-selects like 'SELECT * FROM x WHERE field_x IN (SELECT field_y FROM y), because {{WHERE field_x IN (NULL)}} should result in empty resultset by definition (it's the same as {{WHERE field_x = NULL}}).

{noformat}
postgres@ubuntu-14:~$ psql
psql (9.3.13)
Type "help" for help.

postgres=# INSERT INTO x VALUES (0, 1);
INSERT 0 1
postgres=# INSERT INTO x VALUES (1, NULL);
INSERT 0 1
postgres=# SELECT * FROM x;
 a | b 
---+---
 0 | 1
 1 |  
(2 rows)

postgres=# SELECT * FROM x WHERE a IN (0, NULL);
 a | b 
---+---
 0 | 1
(1 row)

postgres=# SELECT * FROM x WHERE b IN (1, NULL);
 a | b 
---+---
 0 | 1
(1 row)

postgres=# SELECT * FROM x WHERE b IN (NULL);
 a | b 
---+---
(0 rows)
{noformat}

> Add IS NULL predicate type
> --------------------------
>
>                 Key: KUDU-1642
>                 URL: https://issues.apache.org/jira/browse/KUDU-1642
>             Project: Kudu
>          Issue Type: Sub-task
>          Components: client, tablet
>            Reporter: Dan Burkert
>




--
This message was sent by Atlassian JIRA
(v6.3.4#6332)