Re: Sub-query having NULL row returning FALSE result

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(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Wed, Jun 29, 2016 at 2:07 AM, Sridhar N Bamandlapally <
> sridhar(dot)bn1(at)gmail(dot)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.​
>
>