hi
if the 'when' clause of a case stmt evaluates to non-true (false or null),
it should branch to the 'else' part without evaluating the 'then' part.
Since it is not doing that, this is a bug.
You can do a showplan that shows the clauses generated for the 'case'
stmt. Check why the branch clause is not skipping to 'else' part of clauses.
It should not be evaluating the 'then' part if the condition is not TRUE.
Maybe a step is missing to treat NULL as FALSE during the THEN
comparison.
The specialNulls is used for certain cases where NULLs are treated
as regular values. These are cases like order by or group by where nulls
are equal to other nulls and sort high.
That envvar you showed was only for debugging purpose
and is not needed any more.
This mode should not be turned on for other cases or it may cause
problems with expression evaluation that deal with 3-valued Boolean logic.
you can try a simpler case like:
select case when cast(null as int) > 0 then 1/0 else 0 end from dual;
and it will show the same issue.
Turn pcode off which you have done and also turn query caching off
which will make it easier to debug.
You can also add an 'is not null' to the THEN clause to skip null processing.
something like: case when a is not null and a > 0 then ...
This is only a workaround, the original bug still need to be looked at.
anoop
-----Original Message-----
From: Zhu, Wen-Jun <[email protected]>
Sent: Wednesday, August 22, 2018 1:30 AM
To: [email protected]
Subject: expression involving NULL and special process on it
Hi all,
I meet the following SQL statements:
cqd pcode_opt_level 'off';
create table t1(a double precision);
insert into t1 values(null);
select case when a > 0 then 1 else 0 end from t1; select case when a>0 then 1/0
else 0 end from t1;
the 4th statement is OK as expected, but there is an error for the 5th one.
I know that a expression involving NULL is undefined, but to be compatible with
other databases, like Oracle, Trafodion can enter the ELSE branch.
(It seems that if there is a null in CASE...WHEN expression, both branches
would be calculated and the last result(top of stack?) would be returned?)
As I investigate, I find the code of function ex_comp_clause::processNulls() in
core/sql/exp/exp_comp.cpp:
48│ ex_expr::exp_return_type ex_comp_clause::processNulls(char *op_data[],
49│ CollHeap *heap,
50│ ComDiagsArea
**diagsArea)
51│ {
52│ if (isSpecialNulls())
53│ {
54│ // special nulls. Nulls are values.
55│ // Null = Null, non-null-value < NULL, etc.
56│ short left_is_null = 0;
57│ short right_is_null = 0;
58│
59│ if (getOperand(1)->getNullFlag() && (!op_data[1]))
60│ left_is_null = -1;
61│
62│ if (getOperand(2)->getNullFlag() && (!op_data[2]))
63│ right_is_null = -1;
has dealt with NULL specially.
I have two questions on this:
1. How to turn this feature on?
I find code of BiRelat::BiRelat() in core/sql/optimizer/ItemLog.h:
349 {
350 #ifndef NDEBUG
351 if (NULL != getenv("FORCE_SPECIAL_NULLS")) {
352 specialNulls_ = TRUE;
353 }
354 #endif
turns this on, but it works only under DEBUG mode, why?
2. After this feature turned on, the NULL logic is NOT expected,
so what do this for? To compatible with some other database?
Can I change this logic, or add another special process on NULL?
Thank you.
Regards,
Wenjun Zhu
