1

I'm using DB2 LUW v11.1.4.5,

It seems that EXISTS predicate, when FALSE returns NULL (is it documented ?) In the following query on X is null is true, all the rest acts like FALSE is a synonym for NULL, but the metadata say all the columns are not nullable (see dbfiddle)

with boolstmt as
(
  select  'exists' stmt,
    exists(select 1 from sysibm.sysdummy1 where ibmreqd = 'N') as x
  from sysibm.sysdummy1
 )
select
 stmt,
 x,
 x is null "x is null",
 not x "NOT x",
 x = FALSE "x = FALSE",
 smallint(x) "smallint(x)",
 char(x) "char(x)"
from boolstmt

now with the following query every column is nullable, it shows that x really is NULL with EXISTS

with boolstmt as
(
  select  'exists' stmt,
    exists(select 1 from sysibm.sysdummy1 where ibmreqd = 'N') as x
  from sysibm.sysdummy1
  union all
  select  'count' stmt,
    (select count(*) from sysibm.sysdummy1 where ibmreqd = 'N') > 0 as x
  from sysibm.sysdummy1
 )
select
 stmt,
 x,
 x is null "x is null",
 not x "NOT x",
 x = FALSE "x = FALSE",
 smallint(x) "smallint(x)",
 char(x) "char(x)"
from boolstmt

My question is : did someone know about it, it seems to me that's a problem on the server but do you know if it may come from the JDBC driver (using 4.27.25) ? Is the problem still there using later versions ?

2

An interesting question with the answer that is not so interesting: in this trivial example your EXISTS is optimized away, so an attempt to reference this predicate evaluation result is undefined. If you run db2exfmt (or another tool that can retrieve the "optimized" version of the statement), you will see that this is what gets executed in the end:

SELECT 'exists' AS "STMT",
   $INTERNAL_FUNC$() AS "X",
   $INTERNAL_FUNC$() AS "x is null",
   $INTERNAL_FUNC$() AS "NOT x",
   $INTERNAL_FUNC$() AS "x = FALSE",
   SMALLINT($INTERNAL_FUNC$()) AS "smallint(x)",
   CHAR($INTERNAL_FUNC$()) AS "char(x)",
   $INTERNAL_FUNC$() AS "Y",
   $INTERNAL_FUNC$() AS "y is null",
   $INTERNAL_FUNC$() AS "NOT y",
   $INTERNAL_FUNC$() AS "y = FALSE",
   SMALLINT($INTERNAL_FUNC$()) AS "smallint(y)",
   CHAR($INTERNAL_FUNC$()) AS "char(y)" 
FROM (SELECT NULL 
FROM (VALUES ) AS Q1 
WHERE ('Y' = 'N')) AS Q2

This is likely Working As Designed™, because predicates, of which EXISTS is one, are not meant to be used in place of expressions. Db2 tries to do its best to turn predicate evaluation outcomes into values, but the optimizer has an upper hand.

Things work as you expect when you use expressions and predicates where they respectively belong:

with boolstmt as
(
  select  'exists' stmt,
    case when exists(select 1 from sysibm.sysdummy1 where ibmreqd = 'N') then true else false end as x
  from sysibm.sysdummy1
 )
1
  • OK thanks, you explained it. I guess that's why values exists(select 1 from sysibm.sysdummy1 where ibmreqd = 'N') says SQLCODE=-582. But if the result column had just been mark nullable I had not spent half a day wondering why sometimes FALSE or TRUE = FALSE
    – nfgl
    Aug 13 '20 at 8:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.