trafodion-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Anoop Sharma <>
Subject RE: expression involving NULL and special process on it
Date Fri, 21 Sep 2018 18:50:18 GMT

On constraint check result:

Constraint check passes if the result is TRUE or NULL.

Here is the ANSI definition for check/table constraints:

      3) If the <table constraint> is a <check constraint definition>, then let
SC be the <search condition>
      immediately contained in the <check constraint definition> and let T be the table
      included in the corresponding table constraint descriptor; the table constraint is not
satisfied if
      and only if
      is true

If you apply that rule, you will see that the check constraint (a < 10000) passes if 'a'
is null or a is < 10000
but it fails if a is >= 10000.
So the current result in TEST056 is correct.

On your question #1:
Not clear what you are asking. Are you saying that the constraint show different result based
on whether the data type is signed or unsigned?

On your question #2:
Yes, 10000 will prevent insertion and 9999 will allow it.
Was your stmt a comment or a question?

 JIRA review comment:
There is also a review comment on your changes which was related to NULL value of result.
Did you get a chance to look at it or respond to it?

It will help to understand how nulls work in predicates and constraints so that a change
that may solve the issue in the JIRA, does not break something else.



-----Original Message-----
From: Zhu, Wen-Jun <>
Sent: Friday, September 21, 2018 12:43 AM
Subject: 答复: expression involving NULL and special process on it


I have create a pull request 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.

Wenjun Zhu

发件人: Anoop Sharma <<>>
发送时间: 2018年8月22日 22:19
主题: RE: expression involving NULL and special process on it

 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.


-----Original Message-----
From: Zhu, Wen-Jun <<>>
Sent: Wednesday, August 22, 2018 1:30 AM
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;
  59│        if (getOperand(1)->getNullFlag() && (!op_data[1]))
  60│         left_is_null = -1;
  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.

Wenjun Zhu

  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message