NobiGo commented on code in PR #4462:
URL: https://github.com/apache/calcite/pull/4462#discussion_r2193679882
##########
core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java:
##########
@@ -346,7 +346,7 @@ private static RelDataType deriveTypePad(SqlOperatorBinding
binding, RelDataType
@LibraryOperator(libraries = {BIG_QUERY, ORACLE, POSTGRESQL, SPARK, HIVE})
public static final SqlFunction LTRIM =
SqlBasicFunction.create(SqlKind.LTRIM,
- ReturnTypes.ARG0.andThen(SqlTypeTransforms.TO_NULLABLE)
+ ReturnTypes.ARG0.andThen(SqlTypeTransforms.FORCE_NULLABLE)
Review Comment:
Is it only possible to change this in the Oracle mode here?
##########
core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java:
##########
@@ -662,4 +662,18 @@ enum SelectAliasLookup {
* True when the unsigned versions of integer types are supported.
*/
boolean supportsUnsignedTypes();
+
+ /**
+ * Whether to convert empty string to null..
+ *
+ * <p>Consider the {@code SUBSTRING} operator. SUBSTRING('abc', 0, 0) will
+ * return null in Oracle, and return an empty string in other databases
+ * like MySQL, PostgreSQL, SQL Server that support SUBSTRING function.
+ *
+ * <p>Among the built-in conformance levels, true in
+ * {@link SqlConformanceEnum#ORACLE_10},
+ * {@link SqlConformanceEnum#ORACLE_12};
+ * false otherwise.
+ */
+ boolean emptyStringIsNull();
Review Comment:
`treatEmptyStringAsNull` ?
##########
core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java:
##########
@@ -1952,10 +1960,32 @@ private static class TrimConvertlet implements
SqlRexConvertlet {
@Override public RexNode convertCall(SqlRexContext cx, SqlCall call) {
final RexBuilder rexBuilder = cx.getRexBuilder();
+ final SqlParserPos pos = call.getParserPosition();
+ final RelDataTypeFactory typeFactory =
+ cx.getValidator().getTypeFactory();
final RexNode operand =
cx.convertExpression(call.getOperandList().get(0));
- return rexBuilder.makeCall(call.getParserPosition(),
SqlStdOperatorTable.TRIM,
- rexBuilder.makeFlag(flag), rexBuilder.makeLiteral(" "), operand);
+ RexNode rawCall =
+ rexBuilder.makeCall(pos, SqlStdOperatorTable.TRIM,
rexBuilder.makeFlag(flag),
+ rexBuilder.makeLiteral(" "), operand);
+ SqlConformance conformance = cx.getValidator().config().conformance();
+ if (conformance.emptyStringIsNull()) {
+ // Translate
+ // LTRIM/RTRIM(operand0[,operand1,...])
+ //
+ // to the following if we want Oracle semantics
+ // CASE
+ // WHEN LTRIM/RTRIM(operand0[,operand1,...]) = ''
+ // THEN NULL
+ // ELSE LTRIM/RTRIM(operand0[,operand1,...])
+ // END
+ return rexBuilder.makeCall(pos, SqlStdOperatorTable.CASE,
+ rexBuilder.makeCall(pos, SqlStdOperatorTable.EQUALS, rawCall,
+ rexBuilder.makeLiteral("")),
+
rexBuilder.makeNullLiteral(typeFactory.createSqlType(SqlTypeName.NULL)),
Review Comment:
The NULL type here should be the same as that of LTRIM(A)
##########
core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java:
##########
@@ -1952,10 +1960,32 @@ private static class TrimConvertlet implements
SqlRexConvertlet {
@Override public RexNode convertCall(SqlRexContext cx, SqlCall call) {
final RexBuilder rexBuilder = cx.getRexBuilder();
+ final SqlParserPos pos = call.getParserPosition();
+ final RelDataTypeFactory typeFactory =
+ cx.getValidator().getTypeFactory();
final RexNode operand =
cx.convertExpression(call.getOperandList().get(0));
- return rexBuilder.makeCall(call.getParserPosition(),
SqlStdOperatorTable.TRIM,
- rexBuilder.makeFlag(flag), rexBuilder.makeLiteral(" "), operand);
+ RexNode rawCall =
+ rexBuilder.makeCall(pos, SqlStdOperatorTable.TRIM,
rexBuilder.makeFlag(flag),
+ rexBuilder.makeLiteral(" "), operand);
+ SqlConformance conformance = cx.getValidator().config().conformance();
+ if (conformance.emptyStringIsNull()) {
+ // Translate
+ // LTRIM/RTRIM(operand0[,operand1,...])
+ //
+ // to the following if we want Oracle semantics
Review Comment:
Here, we convert LTRIM(A) to the following expression: CASE WHEN LTRIM(A) =
'' THEN NULL ELSE LTRIM(A) END. LTRIM(A) is calculated multiple times. If
LTRIM(A) is a non-deterministic function, it may lead to an uncontrolled final
result.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
