Обсуждение: Sub-query having NULL row returning FALSE result
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
but this is working with other databases
Thanks
Sridhar
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
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:
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
HiPlease go through below casepostgres=# CREATE TABLE emp (id INTEGER unique, ename VARCHAR);CREATE TABLEpostgres=# INSERT INTO emp VALUES (null, 'aaa');INSERT 0 1postgres=# INSERT INTO emp VALUES (null, 'bbb');INSERT 0 1postgres=# INSERT INTO emp VALUES (3, 'ccc');INSERT 0 1postgres=# INSERT INTO emp VALUES (4, 'ddd');INSERT 0 1postgres=# SELECT * FROM emp ;id | ename----+-------| aaa| bbb3 | ccc4 | 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 0postgres=# SELECT * FROM emp ;id | ename----+-------| aaa| bbb3 | ccc4 | ddd(4 rows)postgres=#The application is generating SQL-Statement to avoid exception while insertingThe expected behavior is to INSERT row if the NEW id is not existing in table, but this is doing FALSEPlease 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"
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:
HiPlease go through below casepostgres=# CREATE TABLE emp (id INTEGER unique, ename VARCHAR);CREATE TABLEpostgres=# INSERT INTO emp VALUES (null, 'aaa');INSERT 0 1postgres=# INSERT INTO emp VALUES (null, 'bbb');INSERT 0 1postgres=# INSERT INTO emp VALUES (3, 'ccc');INSERT 0 1postgres=# INSERT INTO emp VALUES (4, 'ddd');INSERT 0 1postgres=# SELECT * FROM emp ;id | ename----+-------| aaa| bbb3 | ccc4 | 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 0postgres=# SELECT * FROM emp ;id | ename----+-------| aaa| bbb3 | ccc4 | ddd(4 rows)postgres=#The application is generating SQL-Statement to avoid exception while insertingThe expected behavior is to INSERT row if the NEW id is not existing in table, but this is doing FALSEPlease advise me if am doing something wrong here or any alternateSubjectively, 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"Oh, and try adding "WHERE id IS NOT NULL"David J.