[
https://issues.apache.org/jira/browse/CALCITE-4725?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17397769#comment-17397769
]
Yao Zhang edited comment on CALCITE-4725 at 8/12/21, 2:59 AM:
--------------------------------------------------------------
Hi [~nobigo],
Thank you for your advice. But there is a question: should we support SQLs like
STRING between INT and BOOLEAN? In your suggestion it supports but in my
solution it does not.
It seems that the "and" keyword implies a comparison relationship. X between A
and B, A should be less than or equal to B. Correct me if I'm wrong.
was (Author: paul8263):
Hi [~nobigo],
Thank you for your advice. But there is a question: should we support SQLs like
STRING between INT and BOOLEAN? In your suggestion it supports but in my
solution it does not.
> 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)
