Subquery error

SRA1

Im getting an error when using a very complex query,

can some one give an example for this error.

 

ERROR 4197: NULL value found in a column used by a subquery
HINT: IN/ANY subqueries within another expression are not supported if any column values are NULL

sKwa

Hi!

 

daniel=> create table t1(x int);
CREATE TABLE
daniel=> copy t1 from stdin direct;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1
>> 2
>> 3
>> \.
daniel=> create table t2(x int);
CREATE TABLE
daniel=> copy t2 from stdin direct null as 'X';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> X
>> 2
>> 4
>> \.
daniel=> -- NO ERROR
daniel=> SELECT * FROM t1 WHERE (x IN (SELECT x FROM t2));
 x
---
 2
(1 row)

daniel=> -- ERROR
daniel=> SELECT * FROM t1 WHERE (x IN (SELECT x FROM t2) > 0);
ERROR 4197:  NULL value found in a column used by a subquery
HINT:  IN/ANY subqueries within another expression are not supported if any column values are NULL
daniel=>

sKwa

 

 

(x IN (SELECT x FROM t2)) => x IN (NULL, 2, 4)

...

(x IN (SELECT x FROM t2) > 0) => for each value (NULL, 2, 4) > 0 - here is error

 

what does it mean NULL > 0 ?

cid

@sKwa
I realize this has been posted many years ago, but I cannot find too many explanations on this issue (which I encountered as well, just posted the question),
I have the same error with a CREATE statement.

I find it strange though that something like:
SELECT x FROM t2 WHERE x > 0;
would work.