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
