[
https://issues.apache.org/jira/browse/CALCITE-4725?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17397791#comment-17397791
]
duan xiong edited comment on CALCITE-4725 at 8/12/21, 3:35 AM:
---------------------------------------------------------------
[~paul8263] I have test some case in PG. for example 1:
{code:java}
true between 'abc' and false
{code}
{color:#172b4d}It failes with exception info:" ERROR: invalid input syntax for
type boolean: "abc""{color}
{color:#172b4d}and in your test. It can validate success.{color}
{color:#172b4d}and example 2:{color}
{code:java}
select date '2012-02-03' between 2 and 3{code}
It failes with exception info::"operator does not exist: +date >= integer+"
So I suggest change
{code:java}
x BETWEEN y AND z{code}
to
{code:java}
x >= y AND x <= z{code}
and then we can to enriched RelNode date type comparable condition about for
example:
{code:java}
select 'dc'>false{code}
In calcite. This show error in code gen. but in pg This can be tested early.
was (Author: nobigo):
[~paul8263] I have test some case in PG. for example 1:{color:#FF0000}
{color}
{code:java}
true between 'abc' and false
{code}
{color:#172b4d}It failes with exception info:" ERROR: invalid input syntax for
type boolean: "abc""{color}
{color:#172b4d}and in your test. It can validate success.{color}
{color:#FF0000}{color:#172b4d}and example 2:{color}{color}
{code:java}
select date '2012-02-03' between 2 and 3{code}
It failes with exception info::"operator does not exist:{color:#de350b} +date
>= integer+{color}"
So I suggest change
{code:java}
x BETWEEN y AND z{code}
to
{code:java}
x >= y AND x <= z{code}
and then we can to enriched RelNode date type comparable condition about for
example:
{code:java}
select 'dc'>false{code}
In calcite. This show error in code gen. but in pg This can be tested early.
> Between clause operands checker should check all combinations of the three
> operands
> -----------------------------------------------------------------------------------
>
> Key: CALCITE-4725
> URL: https://issues.apache.org/jira/browse/CALCITE-4725
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.27.0
> Reporter: Yao Zhang
> Priority: Major
> Labels: pull-request-available
> Time Spent: 0.5h
> Remaining Estimate: 0h
>
> Between clause operands checker should check all combinations of the three
> operands
> Given those SQLs:
> {code:sql}
> create table student (id bigint, name string, score DECIMAL)
> select score from student where score between '1.0' and (id < 1)
> {code}
> As the correct implementation of Calcite, the validation process will success
> because its operands type checking logic is as discussed below:
> Three operands of between clause are student.score(with type DECIMAL), '1.0'
> (with type CHAR[]) and (id < 1) (an expression with the type of Boolean).
> Calcite will check whether those three operands are comparable. It does two
> checks: student.score with '1.0' and '1.0' with (id < 1).
> We look into SqlTypeUtil.isComparable:
> {code:java}
> public static boolean isComparable(RelDataType type1, RelDataType type2) {
> if (type1.isStruct() != type2.isStruct()) {
> return false;
> }
> if (type1.isStruct()) {
> int n = type1.getFieldCount();
> if (n != type2.getFieldCount()) {
> return false;
> }
> for (Pair<RelDataTypeField, RelDataTypeField> pair
> : Pair.zip(type1.getFieldList(), type2.getFieldList())) {
> if (!isComparable(pair.left.getType(), pair.right.getType())) {
> return false;
> }
> }
> return true;
> }
> final RelDataTypeFamily family1 = family(type1);
> final RelDataTypeFamily family2 = family(type2);
> if (family1 == family2) {
> return true;
> }
> // If one of the arguments is of type 'ANY', return true.
> if (family1 == SqlTypeFamily.ANY
> || family2 == SqlTypeFamily.ANY) {
> return true;
> }
> // If one of the arguments is of type 'NULL', return true.
> if (family1 == SqlTypeFamily.NULL
> || family2 == SqlTypeFamily.NULL) {
> return true;
> }
> // We can implicitly convert from character to date
> if (family1 == SqlTypeFamily.CHARACTER
> && canConvertStringInCompare(family2)
> || family2 == SqlTypeFamily.CHARACTER
> && canConvertStringInCompare(family1)) {
> return true;
> }
> return false;
> }
> {code}
> and canConvertStringInCompare:
> {code:java}
> private static boolean canConvertStringInCompare(RelDataTypeFamily
> family) {
> if (family instanceof SqlTypeFamily) {
> SqlTypeFamily sqlTypeFamily = (SqlTypeFamily)family;
> switch(sqlTypeFamily) {
> case DATE:
> case TIME:
> case TIMESTAMP:
> case INTERVAL_DAY_TIME:
> case INTERVAL_YEAR_MONTH:
> case NUMERIC:
> case APPROXIMATE_NUMERIC:
> case EXACT_NUMERIC:
> case INTEGER:
> case BOOLEAN:
> return true;
> }
> }
> return false;
> }
> {code}
> We can see that DECIMAL(actually is NUMERIC) is comparable with CHAR, and
> CHAR is also comparable with BOOLEAN.
> But in conversion stage, it will result in an exception. I will explain below.
> The type of the three operands in where clause is DECIMAL, CHAR and BOOLEAN
> respectively. We cannot get a consistent type for those three operands. As a
> result, Calcite will add no cast functions to those operands. So no auto type
> conversion is performed.
> Then, the between clause will be converted to:
> {code:sql}
> select score from student where score >= '1.0' and score <= (id < 1)
> {code}
> Obviously an integer is not comparable with a Boolean. The execution engine
> will throw ex exception. This is where the problem lies.
> If we change the SQL to:
> {code:sql}
> select score from student where score >= 1.0 and score <= (id < 1)
> {code}
> Calcite can correctly detect the error in validation stage that numeric type
> is not comparable with Boolean type.
> To correct this problem, I suggest for between operand type checker, it
> should perform more strict type check process by checking all combinations of
> those operands.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
