You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@trafodion.apache.org by "Zhu, Wen-Jun" <we...@esgyn.cn> on 2018/08/22 08:30:27 UTC
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
答复: expression involving NULL and special process on it
Posted by "Zhu, Wen-Jun" <we...@esgyn.cn>.
Hi,
Thank you for your answer.
Actually I am working on the comment you mentioned in the pull request,
and try to fix these diffs in the regression tests.
The comments in file core/sql/exp/exp_clause.h shows:
// 2*MAX_OPERANDS+0 - pointer to result attribute
And file core/sql/exp/exp_comp.cpp
| 120 *(Lng32 *)op_data[2 * MAX_OPERANDS] = 0;
sets that element, which resides on an array, representing that the result of this expression is null.
I think this NULL would be passed up to the upper level of the expression tree, right?
I have two problems on this:
1. As you mentioned in the comment, ` The place where null becomes false is at the very end of that expression`,
I do not know where the final result of the overall expression comes out, can you help me figure this out?
2. Does that value matters? Previous it is -1, after I set it to 0, the CASE WHEN case passed.
I got these values -1, 1 and 0 from the isSpecialNulls(), but this function would not be invoked in normal condition.
Do you have any document on this expression calculation part?
About the two problems on constraints(based on the pull request 1705 I created):
1. Yes, you are right.
When the datatype in the constraint is signed, it can be inserted,
but if it is unsigned, it cannot be inserted. Why?
2. The value I want to insert is 82, which is far less than 9999, and 10000.
When the constraint is `int1<10000`, the value 82 cannot be inserted,
But then the constraint is `int1<9999`, the value 82 can be inserted.
Is there something special about 10000 of unsigned int?
Thank you for you help.
Regards,
Wenjun Zhu
-----邮件原件-----
发件人: Anoop Sharma <an...@esgyn.com>
发送时间: 2018年9月22日 2:50
收件人: dev@trafodion.apache.org
主题: RE: expression involving NULL and special process on it
hi.
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 name
included in the corresponding table constraint descriptor; the table constraint is not satisfied if
and only if
EXISTS ( SELECT * FROM T WHERE NOT ( SC ) )
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.
thanks.
anoop
-----Original Message-----
From: Zhu, Wen-Jun <we...@esgyn.cn>
Sent: Friday, September 21, 2018 12:43 AM
To: dev@trafodion.apache.org
Subject: 答复: expression involving NULL and special process on it
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 <an...@esgyn.com>>
发送时间: 2018年8月22日 22:19
收件人: dev@trafodion.apache.org<ma...@trafodion.apache.org>
主题: 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 <we...@esgyn.cn>>
Sent: Wednesday, August 22, 2018 1:30 AM
To: dev@trafodion.apache.org<ma...@trafodion.apache.org>
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
RE: expression involving NULL and special process on it
Posted by Anoop Sharma <an...@esgyn.com>.
hi.
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 name
included in the corresponding table constraint descriptor; the table constraint is not satisfied if
and only if
EXISTS ( SELECT * FROM T WHERE NOT ( SC ) )
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.
thanks.
anoop
-----Original Message-----
From: Zhu, Wen-Jun <we...@esgyn.cn>
Sent: Friday, September 21, 2018 12:43 AM
To: dev@trafodion.apache.org
Subject: 答复: expression involving NULL and special process on it
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 <an...@esgyn.com>>
发送时间: 2018年8月22日 22:19
收件人: dev@trafodion.apache.org<ma...@trafodion.apache.org>
主题: 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 <we...@esgyn.cn>>
Sent: Wednesday, August 22, 2018 1:30 AM
To: dev@trafodion.apache.org<ma...@trafodion.apache.org>
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
答复: expression involving NULL and special process on it
Posted by "Zhu, Wen-Jun" <we...@esgyn.cn>.
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 <an...@esgyn.com>
发送时间: 2018年8月22日 22:19
收件人: dev@trafodion.apache.org
主题: 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 <we...@esgyn.cn>
Sent: Wednesday, August 22, 2018 1:30 AM
To: dev@trafodion.apache.org
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
RE: expression involving NULL and special process on it
Posted by Anoop Sharma <an...@esgyn.com>.
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 <we...@esgyn.cn>
Sent: Wednesday, August 22, 2018 1:30 AM
To: dev@trafodion.apache.org
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