Strangeness with CASE stmts and ATOMIC keyword

[Seenu]

I'm experiencing some puzzling behaviour with some of my UDFs when
declaring them as ATOMIC.. Basically I'm invoking another UDF (which
uses some Java code) in one branch of a CASE statment, and if that UDF
is declared as ATOMIC then both branches get executed!

I've put together some simple SQL (see below) which shows this. When I
invoke "select bar(cast (null as integer)) from sysibm.sysdummy1" I get
this error:

SQL0470N The user defined routine "BAZ" (specific name
"SQL050825134727100") has a null value for argument "1" that could not
be
passed. SQLSTATE=39004
So it seems that even though I passed in a null value, the other CASE
branch got executed. But.. if I change bar to use foo2() instead, it
works as expected.

Any ideas? btw, I'm running DB2 8.1.0 on AIX

Thanks!
Seenu


CREATE FUNCTION BAZ (INTEGER) RETURNS VARCHAR(5)
LANGUAGE Java
EXTERNAL NAME 'Dummy!printInt'
PARAMETER STYLE JAVA
/
CREATE FUNCTION FOO1 (
inputData INTEGER
)
RETURNS VARCHAR(5)
F1: BEGIN ATOMIC
RETURN
BAZ(inputData);
END
/

CREATE FUNCTION FOO2 (
inputData INTEGER
)
RETURNS VARCHAR(5)
RETURN
BAZ(inputData)
/

CREATE FUNCTION BAR (
inputData INTEGER
)
RETURNS VARCHAR(5)
RETURN
CASE
WHEN inputData IS NULL THEN
NULL
ELSE
foo1(inputData)
-- foo2(inputData)
END
/

[Serge Rielau]

Seenu wrote:

I'm experiencing some puzzling behaviour with some of my UDFs when
declaring them as ATOMIC.. Basically I'm invoking another UDF (which
uses some Java code) in one branch of a CASE statment, and if that UDF
is declared as ATOMIC then both branches get executed!

<snip>
This is a known quirk.
To explain why one works and the other doesn't would blow the scope of
this group.
DB2 generally treats CASE more like a function and may precompute all
arguments IFF the arguments are sufficiently complex (like a subquery or
a complex SQL function).
Strictly speaking the SQL standard requires the THEN expression
toexecute when the WHEN expression has been evalutaed to true.
DB2 for LUW behaves more like: "return the THEN expression for which the
WHEN expression is true".

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Seenu]

Thanks for the quick reply Serge!

I was able to fix my original problem by simplifying my UDFs a bit (and
making some of them not atomic). Is there some documentation available
which explains when DB2 would choose to precompute all CASE branches?

Thanks,
Seenu

[Serge Rielau]

Seenu wrote:

Thanks for the quick reply Serge!

I was able to fix my original problem by simplifying my UDFs a bit (and
making some of them not atomic). Is there some documentation available
which explains when DB2 would choose to precompute all CASE branches?

There will likely be a sentence like this in a doc refresh:
"When a CASE expression contains a subquery or a SQL UDF DB2 does not
guarantee that WHEN clauses are executed in the order specified and that
THEN cluases are executed only if this respective WHEN clause is TRUE."

Again being more specific than that would require intimate understanding
of the query compiler and not be suitable for the SQL Ref.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Knut Stolze]

Serge Rielau wrote:

Seenu wrote:

Thanks for the quick reply Serge!

I was able to fix my original problem by simplifying my UDFs a bit (and
making some of them not atomic). Is there some documentation available
which explains when DB2 would choose to precompute all CASE branches?

There will likely be a sentence like this in a doc refresh:
"When a CASE expression contains a subquery or a SQL UDF DB2 does not
guarantee that WHEN clauses are executed in the order specified and that
THEN cluases are executed only if this respective WHEN clause is TRUE."

Again being more specific than that would require intimate understanding
of the query compiler and not be suitable for the SQL Ref.

The usual work-around for a CASE expression like

CASE
WHEN cond1
THEN expr1
WHEN cond2
THEN expr2
ELSE expr3
END

is to use a construct like this:

CASE
WHEN cond1
THEN ( SELECT expr1
FROM sysibm.sysdummy1
WHERE cond1 )
WHEN cond2
THEN ( SELECT expr2
FROM sysibm.sysdummy1
WHERE NOT ( cond1 ) AND cond2 )
ELSE ( SELECT expr3
FROM sysibm.sysdummy1
WHERE NOT ( cond1 ) AND NOT ( cond2 ) )
END
And depending on the complexity of the various conditions, you can do this:

SELECT CASE
WHEN t.c = 1
THEN ( SELECT expr1
FROM sysibm.sysdummy1
WHERE t.c = 1 )
WHEN t.c = 2
THEN ( SELECT expr2
FROM sysibm.sysdummy1
WHERE t.c = 2 )
ELSE ( SELECT expr3
FROM sysibm.sysdummy1
WHERE t.c = 3 )
FROM ..., TABLE ( VALUES (
CASE
WHEN cond1 THEN 1
WHEN cond2 THEN 2
ELSE 3
END ) ) AS t(c)

Here you will have the various conditions to be evaluated only once in the
WHEN branches of the CASE in the FROM clause.

--
Knut Stolze
Information Integration Development
IBM Germany / University of Jena