Hi,
I have create a pull request
1705(https://github.com/apache/trafodion/pull/1705), and the CASE WHEN case has
passed.
Bet there are some cases failed.
One of the cases, in core/TEST056, looks like this:
drop table t1;
create table t1(orders int, int1 int unsigned,
constraint int_uniq1 check (int1 < 10000));
insert into t1 (orders) values (82);
*** ERROR[8101] The operation is prevented by check constraint
TRAFODION.SCH.INT_UNIQ1 on table TRAFODION.SCH.T1.
The insertion has failed because of the constraint.
I think it's right, because the result of null< 10000 is not TRUE.
Before this pull request, it can be inserted.
I have two questions:
1. This problems is related to the data type of unsigned(int, largeint,
and so on), which is not supported in other database, like Oracle.
Constraints on other datatype would not prevent the insertion.
I cannot see why.
2. In the constraint, 10000 is important, 9999 is OK.
i.e. (int1<10000) prevents the insertion, but (int<9999) does
not.
Thank you.
Regards,
Wenjun Zhu
-----邮件原件-----
发件人: Anoop Sharma <[email protected]>
发送时间: 2018年8月22日 22:19
收件人: [email protected]
主题: RE: expression involving NULL and special process on it
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
