[
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 6:08 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}}; besides, they have stored procedures and
pgsql, so it's crucial to provide syntax consistency there. 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}}. 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}
> 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)
