This is an automated email from the ASF dual-hosted git repository. jhyde pushed a commit to branch main in repository https://gitbox.apache.org/repos/asf/calcite.git
commit 03fd95b97c4c63819d3af9525c06b0f40963def9 Author: askarbozcan <[email protected]> AuthorDate: Thu Mar 2 18:03:01 2023 +0200 [CALCITE-5548] Add MSSQL-style CONVERT function (enabled in MSSql library) Microsoft SQL Server's `CONVERT(type, exp [, style])` function is equivalent to `CAST(exp AS type)` and the JDBC standard function `{fn CONVERT(value, type)}`. This function is not to be confused with standard SQL's `CONVERT` function, which converts a character string from one character set to another. Add `mssqlfunc` connection for QuidemTest. Close apache/calcite#3100 --- core/src/main/codegen/templates/Parser.jj | 38 ++++++++-- .../apache/calcite/sql/fun/SqlCastFunction.java | 14 ++-- .../apache/calcite/sql/fun/SqlConvertFunction.java | 6 +- .../calcite/sql/fun/SqlLibraryOperators.java | 46 ++++++++++++ .../apache/calcite/sql/type/OperandHandlers.java | 10 +++ core/src/test/resources/sql/functions.iq | 84 ++++++++++++++++++++++ site/_docs/reference.md | 3 +- .../apache/calcite/sql/parser/SqlParserTest.java | 19 +++++ .../apache/calcite/sql/test/AbstractSqlTester.java | 3 +- .../java/org/apache/calcite/test/QuidemTest.java | 5 ++ .../org/apache/calcite/test/SqlOperatorTest.java | 28 ++++++++ 11 files changed, 238 insertions(+), 18 deletions(-) diff --git a/core/src/main/codegen/templates/Parser.jj b/core/src/main/codegen/templates/Parser.jj index a30408ff84..da7166b53d 100644 --- a/core/src/main/codegen/templates/Parser.jj +++ b/core/src/main/codegen/templates/Parser.jj @@ -5995,6 +5995,7 @@ SqlNode BuiltinFunctionCall() : SqlDataTypeSpec dt; final SqlIntervalQualifier unit; final SqlNode node; + final SqlLiteral style; // mssql convert 'style' operand } { //~ FUNCTIONS WITH SPECIAL SYNTAX --------------------------------------- @@ -6036,11 +6037,38 @@ SqlNode BuiltinFunctionCall() : | <CONVERT> { s = span(); } <LPAREN> - AddExpression(args, ExprContext.ACCEPT_SUB_QUERY) - <USING> name = SimpleIdentifier() { args.add(name); } - <RPAREN> { - return SqlStdOperatorTable.CONVERT.createCall(s.end(this), args); - } + ( + // CONVERT in the form of CONVERT(x USING y) + + // "AddExpression" matches INTERVAL, + // which can also be 1st token in args of MSSQL CONVERT + // So lookahead another token (to match <USING> vs. <COMMA>) + LOOKAHEAD(2) + AddExpression(args, ExprContext.ACCEPT_SUB_QUERY) + <USING> name = SimpleIdentifier() { args.add(name); } + <RPAREN> { + return SqlStdOperatorTable.CONVERT.createCall(s.end(this), args); + } + | // mssql CONVERT(type, val [,style]) + ( + dt = DataType() { args.add(dt); } + | + <INTERVAL> e = IntervalQualifier() { args.add(e); } + ) + <COMMA> + AddExpression(args, ExprContext.ACCEPT_SUB_QUERY) + [ + <COMMA> + ( + style = UnsignedNumericLiteral() { args.add(style); } + | + <NULL> { args.add(SqlLiteral.createNull(getPos())); } + ) + ] + <RPAREN> { + return SqlLibraryOperators.MSSQL_CONVERT.createCall(s.end(this), args); + } + ) | <TRANSLATE> { s = span(); } <LPAREN> diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlCastFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlCastFunction.java index ce45bf7e24..d3088823ce 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlCastFunction.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlCastFunction.java @@ -83,9 +83,7 @@ public class SqlCastFunction extends SqlFunction { //~ Constructors ----------------------------------------------------------- public SqlCastFunction() { - super("CAST", - SqlKind.CAST, - null, + super("CAST", SqlKind.CAST, SqlCastFunction::inferReturnTypeImpl, InferTypes.FIRST_KNOWN, null, SqlFunctionCategory.SYSTEM); @@ -93,14 +91,12 @@ public class SqlCastFunction extends SqlFunction { //~ Methods ---------------------------------------------------------------- - @Override public RelDataType inferReturnType( - SqlOperatorBinding opBinding) { + static RelDataType inferReturnTypeImpl(SqlOperatorBinding opBinding) { assert opBinding.getOperandCount() == 2; - RelDataType ret = opBinding.getOperandType(1); - RelDataType firstType = opBinding.getOperandType(0); - ret = + final RelDataType firstType = opBinding.getOperandType(0); + final RelDataType ret = opBinding.getTypeFactory().createTypeWithNullability( - ret, + opBinding.getOperandType(1), firstType.isNullable()); if (opBinding instanceof SqlCallBinding) { SqlCallBinding callBinding = (SqlCallBinding) opBinding; diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlConvertFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlConvertFunction.java index 23fbb4b175..f36621858f 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlConvertFunction.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlConvertFunction.java @@ -21,6 +21,8 @@ import org.apache.calcite.sql.SqlFunction; import org.apache.calcite.sql.SqlFunctionCategory; import org.apache.calcite.sql.SqlKind; import org.apache.calcite.sql.SqlWriter; +import org.apache.calcite.sql.type.OperandTypes; +import org.apache.calcite.sql.type.ReturnTypes; /** * Common base for the <code>CONVERT</code> and <code>TRANSLATE</code> @@ -33,9 +35,9 @@ public class SqlConvertFunction extends SqlFunction { super( name, SqlKind.OTHER_FUNCTION, + ReturnTypes.ARG0, null, - null, - null, + OperandTypes.ANY_ANY, SqlFunctionCategory.STRING); } diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java index fe03c2b2d7..cde127a424 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java @@ -22,6 +22,7 @@ import org.apache.calcite.sql.SqlAggFunction; import org.apache.calcite.sql.SqlBasicFunction; import org.apache.calcite.sql.SqlBinaryOperator; import org.apache.calcite.sql.SqlCall; +import org.apache.calcite.sql.SqlCallBinding; import org.apache.calcite.sql.SqlFunction; import org.apache.calcite.sql.SqlFunctionCategory; import org.apache.calcite.sql.SqlKind; @@ -40,6 +41,7 @@ import org.apache.calcite.sql.type.SqlReturnTypeInference; import org.apache.calcite.sql.type.SqlTypeFamily; import org.apache.calcite.sql.type.SqlTypeName; import org.apache.calcite.sql.type.SqlTypeTransforms; +import org.apache.calcite.sql.validate.SqlValidator; import org.apache.calcite.util.Litmus; import org.apache.calcite.util.Optionality; @@ -123,6 +125,50 @@ public abstract class SqlLibraryOperators { OperandTypes.family(SqlTypeFamily.ANY, SqlTypeFamily.DATE, SqlTypeFamily.DATE)); + /** The "CONVERT(type, expr [,style])" function (Microsoft SQL Server). + * + * <p>Syntax: + * <blockquote>{@code + * CONVERT( data_type [ ( length ) ], expression [, style ] ) + * }</blockquote> + * + * <p>The optional "style" argument specifies how the value is going to be + * converted; this implementation ignores the {@code style} parameter. + * + * <p>{@code CONVERT(type, expr, style)} is equivalent to CAST(expr AS type), + * and the implementation delegates most of its logic to actual CAST operator. + * + * <p>Not to be confused with standard {@link SqlStdOperatorTable#CONVERT}, + * which converts a string from one character set to another. */ + @LibraryOperator(libraries = {MSSQL}) + public static final SqlFunction MSSQL_CONVERT = + SqlBasicFunction.create(SqlKind.CAST, + ReturnTypes.andThen(SqlLibraryOperators::transformConvert, + SqlCastFunction::inferReturnTypeImpl), + OperandTypes.repeat(SqlOperandCountRanges.between(2, 3), + OperandTypes.ANY)) + .withName("CONVERT") + .withFunctionType(SqlFunctionCategory.SYSTEM) + .withOperandTypeInference(InferTypes.FIRST_KNOWN) + .withOperandHandler( + OperandHandlers.of(SqlLibraryOperators::transformConvert)); + + /** Transforms a call binding of {@code CONVERT} to an equivalent binding for + * {@code CAST}. */ + private static SqlCallBinding transformConvert(SqlOperatorBinding opBinding) { + // Guaranteed to be a SqlCallBinding, with 2 or 3 arguments + final SqlCallBinding binding = (SqlCallBinding) opBinding; + return new SqlCallBinding(binding.getValidator(), binding.getScope(), + transformConvert(binding.getValidator(), binding.getCall())); + } + + /** Transforms a call to {@code CONVERT} to an equivalent call to + * {@code CAST}. */ + private static SqlCall transformConvert(SqlValidator validator, SqlCall call) { + return SqlStdOperatorTable.CAST.createCall(call.getParserPosition(), + call.operand(1), call.operand(0)); + } + /** The "DATE_PART(timeUnit, datetime)" function * (Databricks, Postgres, Redshift, Snowflake). */ @LibraryOperator(libraries = {POSTGRESQL}) diff --git a/core/src/main/java/org/apache/calcite/sql/type/OperandHandlers.java b/core/src/main/java/org/apache/calcite/sql/type/OperandHandlers.java index 596e26bad9..b45560390c 100644 --- a/core/src/main/java/org/apache/calcite/sql/type/OperandHandlers.java +++ b/core/src/main/java/org/apache/calcite/sql/type/OperandHandlers.java @@ -28,6 +28,7 @@ import org.checkerframework.checker.nullness.qual.Nullable; import java.util.ArrayList; import java.util.List; +import java.util.function.BiFunction; import java.util.function.Function; /** @@ -61,6 +62,15 @@ public abstract class OperandHandlers { public static final SqlOperandHandler OPERAND_1_MIGHT_BE_TIME_FRAME = new TimeFrameOperandHandler(1); + /** Creates an operand handler that applies a function to a call. */ + public static SqlOperandHandler of(BiFunction<SqlValidator, SqlCall, SqlCall> fn) { + return new SqlOperandHandler() { + @Override public SqlNode rewriteCall(SqlValidator validator, SqlCall call) { + return fn.apply(validator, call); + } + }; + } + /** Operand handler for a function whose {@code timeFrameOperand} operand * (0-based) may be a time frame. If the operand is of type * {@link SqlIdentifier}, looks up the custom time frame and converts it to a diff --git a/core/src/test/resources/sql/functions.iq b/core/src/test/resources/sql/functions.iq index a232f838ef..9f5d6b02b1 100644 --- a/core/src/test/resources/sql/functions.iq +++ b/core/src/test/resources/sql/functions.iq @@ -219,4 +219,88 @@ EnumerableCalc(expr#0..4=[{inputs}], expr#5=[IS NOT NULL($t2)], expr#6=[CAST($t2 !ok +# [CALCITE-5548] - Add support for MSSQL CONVERT function +!use mssqlfunc +select CONVERT(INTEGER, 45.4); ++--------+ +| EXPR$0 | ++--------+ +| 45 | ++--------+ +(1 row) + +!ok + +select CONVERT(INTEGER, 45.4, 121); ++--------+ +| EXPR$0 | ++--------+ +| 45 | ++--------+ +(1 row) + +!ok + +select CONVERT(FLOAT, '34.5'); ++--------+ +| EXPR$0 | ++--------+ +| 34.5 | ++--------+ +(1 row) + +!ok + +select CONVERT(DATE, '2011-01-05'); ++------------+ +| EXPR$0 | ++------------+ +| 2011-01-05 | ++------------+ +(1 row) + +!ok + +# style (3rd operand) ignored (for now) +select CONVERT(DATE, '2011-01-05', 999); ++------------+ +| EXPR$0 | ++------------+ +| 2011-01-05 | ++------------+ +(1 row) + +!ok + +select CONVERT(INTEGER, 45.4, 121, 99999); +parse failed: Encountered "," +!error + +# since 'style' operand is ignored, usual behaviour of returning NULL with NULL style is not present +select CONVERT(INTEGER, 5, NULL); ++--------+ +| EXPR$0 | ++--------+ +| 5 | ++--------+ +(1 row) + +!ok + +select CONVERT(INTEGER, NULL, NULL); ++--------+ +| EXPR$0 | ++--------+ +| | ++--------+ +(1 row) + +!ok + +# below not executed due to huge stack trace quidem insists on generating +# unsupported format test for documentation (dd/mm/yyyy) (need MSSQL "style" operand support in enumerable) +# however CONVERT right now is simply a wrapper around CAST +# select CONVERT(DATE, '05/01/2000', 103); + + # End functions.iq diff --git a/site/_docs/reference.md b/site/_docs/reference.md index 587bac802e..bd37dba74d 100644 --- a/site/_docs/reference.md +++ b/site/_docs/reference.md @@ -2638,7 +2638,8 @@ BigQuery's type system uses confusingly different names for types and functions: | b o | COSH(numeric) | Returns the hyperbolic cosine of *numeric* | o | CONCAT(string, string) | Concatenates two strings | b m p | CONCAT(string [, string ]*) | Concatenates two or more strings -| m | COMPRESS(string) | Compresses a string using zlib compression and returns the result as a binary string. +| m | COMPRESS(string) | Compresses a string using zlib compression and returns the result as a binary string +| q | CONVERT(type, expression [ , style ]) | Equivalent to `CAST(expression AS type)`; ignores the *style* operand | p | CONVERT_TIMEZONE(tz1, tz2, datetime) | Converts the timezone of *datetime* from *tz1* to *tz2* | b | CURRENT_DATETIME([ timeZone ]) | Returns the current time as a TIMESTAMP from *timezone* | m | DAYNAME(datetime) | Returns the name, in the connection's locale, of the weekday in *datetime*; for example, it returns '星期日' for both DATE '2020-02-10' and TIMESTAMP '2020-02-10 10:10:10' diff --git a/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java b/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java index 8b22a93ad8..8468edf79d 100644 --- a/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java +++ b/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java @@ -1714,6 +1714,25 @@ public class SqlParserTest { .fails("(?s).*Encountered \"without\" at line 1, column 23.\n.*"); } + /** Test for MSSQL CONVERT parsing, with focus on iffy DATE type and + * testing that the extra "style" operand is parsed + * Other tests are defined in functions.iq + */ + @Test void testMssqlConvert() { + expr("CONVERT(VARCHAR(5), 'xx')") + .same(); + expr("CONVERT(VARCHAR(5), 'xx')") + .same(); + expr("CONVERT(VARCHAR(5), NULL)") + .same(); + expr("CONVERT(VARCHAR(5), NULL, NULL)") + .same(); + expr("CONVERT(DATE, 'xx', 121)") + .same(); + expr("CONVERT(DATE, 'xx')") + .same(); + } + @Test void testLikeAndSimilar() { sql("select * from t where x like '%abc%'") .ok("SELECT *\n" diff --git a/testkit/src/main/java/org/apache/calcite/sql/test/AbstractSqlTester.java b/testkit/src/main/java/org/apache/calcite/sql/test/AbstractSqlTester.java index cc60222543..d68b381cba 100644 --- a/testkit/src/main/java/org/apache/calcite/sql/test/AbstractSqlTester.java +++ b/testkit/src/main/java/org/apache/calcite/sql/test/AbstractSqlTester.java @@ -325,9 +325,10 @@ public abstract class AbstractSqlTester implements SqlTester, AutoCloseable { * @return Query that evaluates a scalar expression */ protected String buildQuery2(SqlTestFactory factory, String expression) { - if (expression.matches("(?i).*percentile_(cont|disc).*")) { + if (expression.matches("(?i).*(percentile_(cont|disc)|convert)\\(.*")) { // PERCENTILE_CONT requires its argument to be a literal, // so converting its argument to a column will cause false errors. + // Similarly, MSSQL-style CONVERT. return buildQuery(expression); } // "values (1 < 5)" diff --git a/testkit/src/main/java/org/apache/calcite/test/QuidemTest.java b/testkit/src/main/java/org/apache/calcite/test/QuidemTest.java index 555c01f95e..224f7a5e3f 100644 --- a/testkit/src/main/java/org/apache/calcite/test/QuidemTest.java +++ b/testkit/src/main/java/org/apache/calcite/test/QuidemTest.java @@ -291,6 +291,11 @@ public abstract class QuidemTest { .with(CalciteConnectionProperty.FUN, "oracle") .with(CalciteAssert.Config.REGULAR) .connect(); + case "mssqlfunc": + return CalciteAssert.that() + .with(CalciteConnectionProperty.FUN, "mssql") + .with(CalciteAssert.Config.REGULAR) + .connect(); case "catchall": return CalciteAssert.that() .with(CalciteConnectionProperty.TIME_ZONE, "UTC") diff --git a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java index 98f7830048..82fb160d9b 100644 --- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java +++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java @@ -1184,6 +1184,34 @@ public class SqlOperatorTest { f.checkNull("cast(cast(null as timestamp) as time)"); } + @Test void testMssqlConvert() { + final SqlOperatorFixture f = fixture(); + f.setFor(SqlLibraryOperators.MSSQL_CONVERT, VmName.EXPAND); + // happy-paths (no need to test all, proper functionality is tested by CAST already + // just need to make sure it works at all + f.checkScalar("convert(INTEGER, 45.4)", "45", "INTEGER NOT NULL"); + f.checkScalar("convert(DATE, '2000-01-01')", "2000-01-01", "DATE NOT NULL"); + + // null-values + f.checkNull("convert(DATE, NULL)"); + } + + @Test void testMssqlConvertWithStyle() { + final SqlOperatorFixture f = fixture(); + f.setFor(SqlLibraryOperators.MSSQL_CONVERT, VmName.EXPAND); + // ensure 'style' argument is ignored + // 3rd argument 'style' is a literal. However, + // AbstractSqlTester converts values to a single value in a column. + // see AbstractSqlTester.buildQuery2 + // But CONVERT 'style' is supposed to be a literal. + // So for now, they are put in a @Disabled test + f.checkScalar("convert(INTEGER, 45.4, 999)", "45", "INTEGER NOT NULL"); + f.checkScalar("convert(DATE, '2000-01-01', 999)", "2000-01-01", "DATE NOT NULL"); + // including 'NULL' style argument + f.checkScalar("convert(DATE, '2000-01-01', NULL)", "2000-01-01", "DATE NOT NULL"); + + } + private static Calendar getFixedCalendar() { Calendar calendar = Util.calendar(); calendar.set(Calendar.YEAR, 2014);
