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