This is an automated email from the ASF dual-hosted git repository.
kxiao pushed a commit to branch branch-2.0
in repository https://gitbox.apache.org/repos/asf/doris.git
commit 4f31a371c2124197fdd2a3fbdcb5a244f4328880
Author: LiBinfeng <46676950+libinfeng...@users.noreply.github.com>
AuthorDate: Fri Jul 7 17:12:41 2023 +0800
[Fix](Planner) change non boolean return type to boolean (#20599)
Problem: When using no boolean type as return type in where or having
clause, the analyzer will check the return type and throw an error. But in some
other databases, this usage is enable.
Solved: Cast return type to boolean in where clause and having clause.
select *** from *** where case when *** then 1 else 0 end;
---
.../java/org/apache/doris/analysis/SelectStmt.java | 9 +++-
.../java/org/apache/doris/analysis/TableRef.java | 4 ++
.../suites/query_p0/cast/test_cast.groovy | 58 +++++++++++++++++++++-
3 files changed, 69 insertions(+), 2 deletions(-)
diff --git a/fe/fe-core/src/main/java/org/apache/doris/analysis/SelectStmt.java
b/fe/fe-core/src/main/java/org/apache/doris/analysis/SelectStmt.java
index 9720300ab2..468fd07284 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/analysis/SelectStmt.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/SelectStmt.java
@@ -26,6 +26,7 @@ import org.apache.doris.catalog.Column;
import org.apache.doris.catalog.DatabaseIf;
import org.apache.doris.catalog.FunctionSet;
import org.apache.doris.catalog.OlapTable;
+import org.apache.doris.catalog.PrimitiveType;
import org.apache.doris.catalog.Table;
import org.apache.doris.catalog.TableIf;
import org.apache.doris.catalog.TableIf.TableType;
@@ -601,8 +602,8 @@ public class SelectStmt extends QueryStmt {
}
}
- whereClauseRewrite();
if (whereClause != null) {
+ whereClauseRewrite();
if (checkGroupingFn(whereClause)) {
throw new AnalysisException("grouping operations are not
allowed in WHERE.");
}
@@ -851,6 +852,9 @@ public class SelectStmt extends QueryStmt {
} else {
whereClause = new BoolLiteral(true);
}
+ } else if (!whereClause.getType().isBoolean()) {
+ whereClause = new CastExpr(TypeDef.create(PrimitiveType.BOOLEAN),
whereClause);
+ whereClause.setType(Type.BOOLEAN);
}
}
@@ -1263,6 +1267,9 @@ public class SelectStmt extends QueryStmt {
havingClauseAfterAnalyzed = havingClause.substitute(aliasSMap,
analyzer, false);
}
havingClauseAfterAnalyzed =
rewriteQueryExprByMvColumnExpr(havingClauseAfterAnalyzed, analyzer);
+ if (!havingClauseAfterAnalyzed.getType().isBoolean()) {
+ havingClauseAfterAnalyzed =
havingClauseAfterAnalyzed.castTo(Type.BOOLEAN);
+ }
havingClauseAfterAnalyzed.checkReturnsBool("HAVING clause", true);
if (groupingInfo != null) {
groupingInfo.substituteGroupingFn(Arrays.asList(havingClauseAfterAnalyzed),
analyzer);
diff --git a/fe/fe-core/src/main/java/org/apache/doris/analysis/TableRef.java
b/fe/fe-core/src/main/java/org/apache/doris/analysis/TableRef.java
index a99e4cf959..fcfbd39b44 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/analysis/TableRef.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/TableRef.java
@@ -22,6 +22,7 @@ package org.apache.doris.analysis;
import org.apache.doris.catalog.Env;
import org.apache.doris.catalog.TableIf;
+import org.apache.doris.catalog.Type;
import org.apache.doris.catalog.external.HMSExternalTable;
import org.apache.doris.common.AnalysisException;
import org.apache.doris.common.ErrorCode;
@@ -651,6 +652,9 @@ public class TableRef implements ParseNode, Writable {
analyzer.setVisibleSemiJoinedTuple(semiJoinedTupleId);
onClause.analyze(analyzer);
analyzer.setVisibleSemiJoinedTuple(null);
+ if (!onClause.getType().isBoolean()) {
+ onClause = onClause.castTo(Type.BOOLEAN);
+ }
onClause.checkReturnsBool("ON clause", true);
if (onClause.contains(Expr.isAggregatePredicate())) {
throw new AnalysisException(
diff --git a/regression-test/suites/query_p0/cast/test_cast.groovy
b/regression-test/suites/query_p0/cast/test_cast.groovy
index bfe4a87989..59d86eb80e 100644
--- a/regression-test/suites/query_p0/cast/test_cast.groovy
+++ b/regression-test/suites/query_p0/cast/test_cast.groovy
@@ -31,4 +31,60 @@ suite('test_cast') {
sql "select cast(${datetime} as int), cast(${datetime} as bigint),
cast(${datetime} as float), cast(${datetime} as double)"
result([[869930357, 20200101123445l, ((float) 20200101123445l),
((double) 20200101123445l)]])
}
-}
\ No newline at end of file
+
+ def tbl = "test_cast"
+
+ sql """ DROP TABLE IF EXISTS ${tbl}"""
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tbl} (
+ `k0` int
+ )
+ DISTRIBUTED BY HASH(`k0`) BUCKETS 5 properties("replication_num" = "1")
+ """
+ sql """ INSERT INTO ${tbl} VALUES (101);"""
+
+ test {
+ sql "select * from ${tbl} where case when k0 = 101 then 1 else 0 end"
+ result([[101]])
+ }
+
+ test {
+ sql "select * from ${tbl} where case when k0 = 101 then 12 else 0 end"
+ result([[101]])
+ }
+
+ test {
+ sql "select * from ${tbl} where case when k0 = 101 then -12 else 0 end"
+ result([[101]])
+ }
+
+ test {
+ sql "select * from ${tbl} where case when k0 = 101 then 0 else 1 end"
+ result([])
+ }
+
+ test {
+ sql "select * from ${tbl} where case when k0 != 101 then 0 else 1 end"
+ result([[101]])
+ }
+
+ test {
+ sql "select * from ${tbl} where case when k0 = 101 then '1' else 0 end"
+ result([[101]])
+ }
+
+ test {
+ sql "select * from ${tbl} where case when k0 = 101 then '12' else 0
end"
+ result([])
+ }
+
+ test {
+ sql "select * from ${tbl} where case when k0 = 101 then 'false' else 0
end"
+ result([])
+ }
+
+ test {
+ sql "select * from ${tbl} where case when k0 = 101 then 'true' else 1
end"
+ result([[101]])
+ }
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
