Sub-query having NULL row returning FALSE result

Sridhar N Bamandlapally <sridhar.bn1@gmail.com> writes:

postgres=# CREATE TABLE emp (id INTEGER unique, ename VARCHAR);
postgres=# INSERT INTO emp VALUES (null, 'aaa');
...
postgres=# INSERT INTO emp SELECT * FROM (SELECT 5::integer id,
'eee'::varchar ename) nr WHERE id NOT IN (SELECT id FROM emp);
INSERT 0 0

This is expected. NOT IN can never succeed if there are any nulls
returned by the sub-select, because the nulls represent "unknown",
and so it's unknown whether there is a match to the outer "id"
value, and WHERE takes a null (unknown) result as false not true.
Certainly there are things to quibble with in that behavior, but
it's what's been required by the SQL standard since 1992.

but this is working with other databases

Really? None that are compliant with the SQL standard, for sure.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi

The actual statement is MERGE INTO <table> NOT MATCHED, which in PG
migrated to WITH - INSERT

however, yes, the SQL-statement in previous does not work in other
databases too, I was wrong

Thanks, thanks again
Sridhar
OpenText

On Wed, Jun 29, 2016 at 11:58 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

On Wed, Jun 29, 2016 at 2:07 AM, Sridhar N Bamandlapally <
sridhar.bn1@gmail.com> wrote:

Hi

Please go through below case

postgres=# CREATE TABLE emp (id INTEGER unique, ename VARCHAR);
CREATE TABLE
postgres=# INSERT INTO emp VALUES (null, 'aaa');
INSERT 0 1
postgres=# INSERT INTO emp VALUES (null, 'bbb');
INSERT 0 1
postgres=# INSERT INTO emp VALUES (3, 'ccc');
INSERT 0 1
postgres=# INSERT INTO emp VALUES (4, 'ddd');
INSERT 0 1
postgres=# SELECT * FROM emp ;
id | ename
----+-------
| aaa
| bbb
3 | ccc
4 | ddd
(4 rows)

postgres=# SELECT * FROM (SELECT 5::integer id, 'eee'::varchar ename) nr;
id | ename
----+-------
5 | eee
(1 row)

postgres=# INSERT INTO emp SELECT * FROM (SELECT 5::integer id,
'eee'::varchar ename) nr WHERE id NOT IN (SELECT id FROM emp);
INSERT 0 0
postgres=# SELECT * FROM emp ;
id | ename
----+-------
| aaa
| bbb
3 | ccc
4 | ddd
(4 rows)

postgres=#

The application is generating SQL-Statement to avoid exception while
inserting

The expected behavior is to INSERT row if the NEW id is not existing in
table, but this is doing FALSE

Please advise me if am doing something wrong here or any alternate

​Subjectively, you are allowing an ID field to be NULL. That, for me, is
wrong.​

​Given this, as a follow-up to what Tom said, you need to decide what you
wish to happen for your NULL IDs. Until you explain that behavior it is
not possible to provide valid alternatives.​

​Usually you want to use "EXISTS", not "IN"​

​
https://www.postgresql.org/docs/9.6/static/functions-subquery.html#FUNCTIONS-SUBQUERY-EXISTS

Oh, and try adding "WHERE id IS NOT NULL"​

​David J.​

Hi

Just for info.

Actual query in Oracle (below)
----------------------------------
MERGE INTO relDocumentTypeMetaDataName t
USING (SELECT ? as DocumentTypeID, ? as DocumentContextID, ? as
MetaDataNameID, ? as DocumentAbstractionIndexID FROM DUAL) s
ON (t.MetaDataNameID = s.MetaDataNameID AND t.DocumentTypeID =
s.DocumentTypeID)
WHEN NOT MATCHED THEN
INSERT (DocumentTypeID, DocumentContextID, MetaDataNameID, FlatColumnNo)
values
(s.DocumentTypeID, s.DocumentContextID, s.MetaDataNameID, (select
nvl(max(FlatColumnNo), 0) + 1 FROM relDocumentTypeMetaDataName WHERE
DocumentTypeID = ?));

Migrated query in PG (phase 1)
---------------------------------
WITH s AS (SELECT ? as DocumentTypeID, ? as DocumentContextID, ? as
MetaDataNameID, ? as DocumentAbstractionIndexID)
INSERT INTO relDocumentTypeMetaDataName (DocumentTypeID, DocumentContextID,
MetaDataNameID, FlatColumnNo, DocumentAbstractionIndexID)
SELECT s.DocumentTypeID, s.DocumentContextID, s.MetaDataNameID,
(SELECT coalesce(MAX(FlatColumnNo), 0) + 1 FROM relDocumentTypeMetaDataName
WHERE DocumentTypeID = ?), s.DocumentAbstractionIndexID
FROM s WHERE s.DocumentTypeID NOT IN (SELECT DocumentTypeID FROM
relDocumentTypeMetaDataName);

Migrated query in PG (phase 2) - after Tom Lane reply
--------------------------------------------------------
WITH s AS (SELECT ? as DocumentTypeID, ? as DocumentContextID, ? as
MetaDataNameID, ? as DocumentAbstractionIndexID)
INSERT INTO relDocumentTypeMetaDataName (DocumentTypeID, DocumentContextID,
MetaDataNameID, FlatColumnNo, DocumentAbstractionIndexID)
SELECT s.DocumentTypeID, s.DocumentContextID, s.MetaDataNameID,
(SELECT coalesce(MAX(FlatColumnNo), 0) + 1 FROM relDocumentTypeMetaDataName
WHERE DocumentTypeID = ?), s.DocumentAbstractionIndexID
FROM s WHERE s.DocumentTypeID NOT IN (SELECT DocumentTypeID FROM
relDocumentTypeMetaDataName WHERE DocumentTypeID IS NOT NULL);

Thanks
Sridhar
OpenText

On Wed, Jun 29, 2016 at 6:04 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote: