Repository: calcite Updated Branches: refs/heads/master ea4095ad4 -> 61f1cf925
[CALCITE-1897] Add '%' operator as an alternative to 'MOD' (sunjincheng) Add SqlKind.MOD and use it for MOD and PERCENT_REMAINDER functions. Parser now allows "%" (PERCENT_REMAINDER) operator, but only in LENIENT and MYSQL_5 conformance levels. SqlToRelConverter now translates PERCENT_REMAINDER to MOD, so optimization and code-generation use the same code path. Close apache/calcite#506 Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/61f1cf92 Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/61f1cf92 Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/61f1cf92 Branch: refs/heads/master Commit: 61f1cf925a3542bbb0f2aca4d3c66bb95e60c1cb Parents: ea4095a Author: sunjincheng121 <[email protected]> Authored: Thu Aug 3 09:16:21 2017 +0800 Committer: Julian Hyde <[email protected]> Committed: Fri Aug 18 09:27:35 2017 -0700 ---------------------------------------------------------------------- core/src/main/codegen/templates/Parser.jj | 7 +++ .../apache/calcite/runtime/CalciteResource.java | 3 + .../java/org/apache/calcite/sql/SqlKind.java | 5 ++ .../calcite/sql/fun/SqlStdOperatorTable.java | 24 +++++++- .../sql/validate/SqlAbstractConformance.java | 4 ++ .../calcite/sql/validate/SqlConformance.java | 11 ++++ .../sql/validate/SqlConformanceEnum.java | 10 ++++ .../sql2rel/StandardConvertletTable.java | 1 + .../calcite/runtime/CalciteResource.properties | 1 + .../calcite/sql/parser/SqlParserTest.java | 4 +- .../apache/calcite/sql/test/SqlAdvisorTest.java | 1 + .../calcite/sql/test/SqlOperatorBaseTest.java | 62 ++++++++++++++++++++ .../apache/calcite/test/SqlValidatorTest.java | 1 + .../calcite/linq4j/tree/ExpressionType.java | 6 ++ site/_docs/reference.md | 9 +-- 15 files changed, 142 insertions(+), 7 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/61f1cf92/core/src/main/codegen/templates/Parser.jj ---------------------------------------------------------------------- diff --git a/core/src/main/codegen/templates/Parser.jj b/core/src/main/codegen/templates/Parser.jj index a2789d0..a913800 100644 --- a/core/src/main/codegen/templates/Parser.jj +++ b/core/src/main/codegen/templates/Parser.jj @@ -5276,6 +5276,12 @@ SqlBinaryOperator BinaryRowOperator() : | <MINUS> { return SqlStdOperatorTable.MINUS; } | <STAR> { return SqlStdOperatorTable.MULTIPLY; } | <SLASH> { return SqlStdOperatorTable.DIVIDE; } +| <PERCENT_REMAINDER> { + if (!this.conformance.isPercentRemainderAllowed()) { + throw new ParseException(RESOURCE.percentRemainderNotAllowed().str()); + } + return SqlStdOperatorTable.PERCENT_REMAINDER; + } | <CONCAT> { return SqlStdOperatorTable.CONCAT; } | <AND> { return SqlStdOperatorTable.AND; } | <OR> { return SqlStdOperatorTable.OR; } @@ -6388,6 +6394,7 @@ String CommonNonReservedKeyWord() : | < MINUS: "-" > | < STAR: "*" > | < SLASH: "/" > +| < PERCENT_REMAINDER: "%" > | < CONCAT: "||" > | < NAMED_ARGUMENT_ASSIGNMENT: "=>" > | < DOUBLE_PERIOD: ".." > http://git-wip-us.apache.org/repos/asf/calcite/blob/61f1cf92/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java index e5d8cfa..ad389d1 100644 --- a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java +++ b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java @@ -34,6 +34,9 @@ public interface CalciteResource { @BaseMessage("Bang equal ''!='' is not allowed under the current SQL conformance level") ExInst<CalciteException> bangEqualNotAllowed(); + @BaseMessage("Percent remainder ''%'' is not allowed under the current SQL conformance level") + ExInst<CalciteException> percentRemainderNotAllowed(); + @BaseMessage("''LIMIT start, count'' is not allowed under the current SQL conformance level") ExInst<CalciteException> limitStartCountNotAllowed(); http://git-wip-us.apache.org/repos/asf/calcite/blob/61f1cf92/core/src/main/java/org/apache/calcite/sql/SqlKind.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/SqlKind.java b/core/src/main/java/org/apache/calcite/sql/SqlKind.java index c4f3bf1..62dedb6 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java @@ -244,6 +244,11 @@ public enum SqlKind { DIVIDE, /** + * The arithmetic remainder operator, "MOD" (and "%" in some dialects). + */ + MOD, + + /** * The arithmetic plus operator, "+". * * @see #PLUS_PREFIX http://git-wip-us.apache.org/repos/asf/calcite/blob/61f1cf92/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java index e4227f7..a29c988 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java @@ -56,6 +56,7 @@ import org.apache.calcite.sql.type.ReturnTypes; import org.apache.calcite.sql.type.SqlOperandCountRanges; import org.apache.calcite.sql.type.SqlTypeName; import org.apache.calcite.sql.util.ReflectiveSqlOperatorTable; +import org.apache.calcite.sql.validate.SqlConformance; import org.apache.calcite.sql.validate.SqlModality; import org.apache.calcite.sql2rel.AuxiliaryConverter; import org.apache.calcite.util.Litmus; @@ -246,6 +247,22 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable { InferTypes.FIRST_KNOWN, OperandTypes.DIVISION_OPERATOR); + /** + * Arithmetic remainder operator, '<code>%</code>', + * an alternative to {@link #MOD} allowed if under certain conformance levels. + * + * @see SqlConformance#isPercentRemainderAllowed + */ + public static final SqlBinaryOperator PERCENT_REMAINDER = + new SqlBinaryOperator( + "%", + SqlKind.MOD, + 60, + true, + ReturnTypes.ARG1_NULLABLE, + null, + OperandTypes.EXACT_NUMERIC_EXACT_NUMERIC); + /** The {@code RAND_INTEGER([seed, ] bound)} function, which yields a random * integer, optionally with seed. */ public static final SqlRandIntegerFunction RAND_INTEGER = @@ -1319,12 +1336,17 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable { OperandTypes.NUMERIC, SqlFunctionCategory.NUMERIC); + /** + * Arithmetic remainder function {@code MOD}. + * + * @see #PERCENT_REMAINDER + */ public static final SqlFunction MOD = // Return type is same as divisor (2nd operand) // SQL2003 Part2 Section 6.27, Syntax Rules 9 new SqlFunction( "MOD", - SqlKind.OTHER_FUNCTION, + SqlKind.MOD, ReturnTypes.ARG1_NULLABLE, null, OperandTypes.EXACT_NUMERIC_EXACT_NUMERIC, http://git-wip-us.apache.org/repos/asf/calcite/blob/61f1cf92/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java index 4cd8bb8..6fa2483 100644 --- a/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java +++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java @@ -78,6 +78,10 @@ public abstract class SqlAbstractConformance implements SqlConformance { public boolean isLimitStartCountAllowed() { return SqlConformanceEnum.DEFAULT.isLimitStartCountAllowed(); } + + public boolean isPercentRemainderAllowed() { + return SqlConformanceEnum.DEFAULT.isPercentRemainderAllowed(); + } } // End SqlAbstractConformance.java http://git-wip-us.apache.org/repos/asf/calcite/blob/61f1cf92/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java index 9f8e3c0..2b37026 100644 --- a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java +++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java @@ -165,6 +165,17 @@ public interface SqlConformance { boolean isBangEqualAllowed(); /** + * Whether the "%" operator is allowed by the parser as an alternative to the + * {@code mod} function. + * + * <p>Among the built-in conformance levels, true in + * {@link SqlConformanceEnum#LENIENT}, + * {@link SqlConformanceEnum#MYSQL_5}; + * false otherwise. + */ + boolean isPercentRemainderAllowed(); + + /** * Whether {@code MINUS} is allowed as an alternative to {@code EXCEPT} in * the parser. * http://git-wip-us.apache.org/repos/asf/calcite/blob/61f1cf92/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java index 754e24e..9f06c18 100644 --- a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java +++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java @@ -168,6 +168,16 @@ public enum SqlConformanceEnum implements SqlConformance { } } + @Override public boolean isPercentRemainderAllowed() { + switch (this) { + case LENIENT: + case MYSQL_5: + return true; + default: + return false; + } + } + public boolean isApplyAllowed() { switch (this) { case LENIENT: http://git-wip-us.apache.org/repos/asf/calcite/blob/61f1cf92/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java index 970441c..9c2f641 100644 --- a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java +++ b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java @@ -107,6 +107,7 @@ public class StandardConvertletTable extends ReflectiveConvertletTable { addAlias( SqlStdOperatorTable.IS_NOT_UNKNOWN, SqlStdOperatorTable.IS_NOT_NULL); + addAlias(SqlStdOperatorTable.PERCENT_REMAINDER, SqlStdOperatorTable.MOD); // Register convertlets for specific objects. registerOp( http://git-wip-us.apache.org/repos/asf/calcite/blob/61f1cf92/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties ---------------------------------------------------------------------- diff --git a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties index 198f1fc..4dd7c38 100644 --- a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties +++ b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties @@ -18,6 +18,7 @@ # ParserContext=line {0,number,#}, column {1,number,#} BangEqualNotAllowed=Bang equal ''!='' is not allowed under the current SQL conformance level +PercentRemainderNotAllowed=Percent remainder ''%'' is not allowed under the current SQL conformance level LimitStartCountNotAllowed=''LIMIT start, count'' is not allowed under the current SQL conformance level ApplyNotAllowed=APPLY operator is not allowed under the current SQL conformance level IllegalLiteral=Illegal {0} literal {1}: {2} http://git-wip-us.apache.org/repos/asf/calcite/blob/61f1cf92/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java b/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java index b9db101..30f15e6 100644 --- a/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java +++ b/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java @@ -1348,7 +1348,7 @@ public class SqlParserTest { checkExp("1-2+3*4/5/6-7", "(((1 - 2) + (((3 * 4) / 5) / 6)) - 7)"); checkExp("power(2,3)", "POWER(2, 3)"); checkExp("aBs(-2.3e-2)", "ABS(-2.3E-2)"); - checkExp("MOD(5 ,\t\f\r\n2)", "MOD(5, 2)"); + checkExp("MOD(5 ,\t\f\r\n2)", "(MOD(5, 2))"); checkExp("ln(5.43 )", "LN(5.43)"); checkExp("log10(- -.2 )", "LOG10(0.2)"); } @@ -1407,7 +1407,7 @@ public class SqlParserTest { + "FROM `EMP`"); checkExp( "log10(1)\r\n+power(2, mod(\r\n3\n\t\t\f\n,ln(4))*log10(5)-6*log10(7/abs(8)+9))*power(10,11)", - "(LOG10(1) + (POWER(2, ((MOD(3, LN(4)) * LOG10(5)) - (6 * LOG10(((7 / ABS(8)) + 9))))) * POWER(10, 11)))"); + "(LOG10(1) + (POWER(2, (((MOD(3, LN(4))) * LOG10(5)) - (6 * LOG10(((7 / ABS(8)) + 9))))) * POWER(10, 11)))"); } @Test public void testFunctionWithDistinct() { http://git-wip-us.apache.org/repos/asf/calcite/blob/61f1cf92/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java b/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java index f841b9f..e4ec229 100644 --- a/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java +++ b/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java @@ -257,6 +257,7 @@ public class SqlAdvisorTest extends SqlValidatorTestCase { "KEYWORD(-)", "KEYWORD(.)", "KEYWORD(/)", + "KEYWORD(%)", "KEYWORD(<)", "KEYWORD(<=)", "KEYWORD(<>)", http://git-wip-us.apache.org/repos/asf/calcite/blob/61f1cf92/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java index a54e2d0..131932e 100644 --- a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java +++ b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java @@ -1925,6 +1925,68 @@ public abstract class SqlOperatorBaseTest { tester.checkNull(" cast(null as ANY) || cast(null as ANY) "); } + @Test public void testModOperator() { + // "%" is allowed under MYSQL_5 SQL conformance level + final SqlTester tester1 = tester.withConformance(SqlConformanceEnum.MYSQL_5); + tester1.setFor(SqlStdOperatorTable.PERCENT_REMAINDER); + tester1.checkScalarExact("4%2", "0"); + tester1.checkScalarExact("8%5", "3"); + tester1.checkScalarExact("-12%7", "-5"); + tester1.checkScalarExact("-12%-7", "-5"); + tester1.checkScalarExact("12%-7", "5"); + tester1.checkScalarExact( + "cast(12 as tinyint) % cast(-7 as tinyint)", + "TINYINT NOT NULL", + "5"); + if (!DECIMAL) { + return; + } + tester1.checkScalarExact( + "cast(9 as decimal(2, 0)) % 7", + "INTEGER NOT NULL", + "2"); + tester1.checkScalarExact( + "7 % cast(9 as decimal(2, 0))", + "DECIMAL(2, 0) NOT NULL", + "7"); + tester1.checkScalarExact( + "cast(-9 as decimal(2, 0)) % cast(7 as decimal(1, 0))", + "DECIMAL(1, 0) NOT NULL", + "-2"); + } + + @Test public void testModPrecedence() { + // "%" is allowed under MYSQL_5 SQL conformance level + final SqlTester tester1 = tester.withConformance(SqlConformanceEnum.MYSQL_5); + tester1.setFor(SqlStdOperatorTable.PERCENT_REMAINDER); + tester1.checkScalarExact("1 + 5 % 3 % 4 * 14 % 17", "12"); + tester1.checkScalarExact("(1 + 5 % 3) % 4 + 14 % 17", "17"); + } + + @Test public void testModOperatorNull() { + // "%" is allowed under MYSQL_5 SQL conformance level + final SqlTester tester1 = tester.withConformance(SqlConformanceEnum.MYSQL_5); + tester1.checkNull("cast(null as integer) % 2"); + tester1.checkNull("4 % cast(null as tinyint)"); + if (!DECIMAL) { + return; + } + tester1.checkNull("4 % cast(null as decimal(12,0))"); + } + + @Test public void testModOperatorDivByZero() { + // "%" is allowed under MYSQL_5 SQL conformance level + final SqlTester tester1 = tester.withConformance(SqlConformanceEnum.MYSQL_5); + // The extra CASE expression is to fool Janino. It does constant + // reduction and will throw the divide by zero exception while + // compiling the expression. The test frame work would then issue + // unexpected exception occurred during "validation". You cannot + // submit as non-runtime because the janino exception does not have + // error position information and the framework is unhappy with that. + tester1.checkFails( + "3 % case 'a' when 'a' then 0 end", DIVISION_BY_ZERO_MESSAGE, true); + } + @Test public void testDivideOperator() { tester.setFor(SqlStdOperatorTable.DIVIDE); tester.checkScalarExact("10 / 5", "2"); http://git-wip-us.apache.org/repos/asf/calcite/blob/61f1cf92/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java index 15d4431..b30c5bb 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java +++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java @@ -8413,6 +8413,7 @@ public class SqlValidatorTest extends SqlValidatorTestCase { + "\n" + "| left\n" + "\n" + + "% left\n" + "* left\n" + "/ left\n" + "/INT left\n" http://git-wip-us.apache.org/repos/asf/calcite/blob/61f1cf92/linq4j/src/main/java/org/apache/calcite/linq4j/tree/ExpressionType.java ---------------------------------------------------------------------- diff --git a/linq4j/src/main/java/org/apache/calcite/linq4j/tree/ExpressionType.java b/linq4j/src/main/java/org/apache/calcite/linq4j/tree/ExpressionType.java index fcdf3a5..fbdfb7d 100644 --- a/linq4j/src/main/java/org/apache/calcite/linq4j/tree/ExpressionType.java +++ b/linq4j/src/main/java/org/apache/calcite/linq4j/tree/ExpressionType.java @@ -141,6 +141,12 @@ public enum ExpressionType { Divide(" / ", false, 3, false), /** + * A percent remainder operation, such as (a % b), for numeric + * operands. + */ + Mod(" % ", false, 3, false), + + /** * A node that represents an equality comparison, such as {@code a == b} in * Java. */ http://git-wip-us.apache.org/repos/asf/calcite/blob/61f1cf92/site/_docs/reference.md ---------------------------------------------------------------------- diff --git a/site/_docs/reference.md b/site/_docs/reference.md index dc4f3db..a2d9f75 100644 --- a/site/_docs/reference.md +++ b/site/_docs/reference.md @@ -1015,7 +1015,7 @@ The operator precedence and associativity, highest to lowest. | . | left | [ ] (array element) | left | + - (unary plus, minus) | right -| * / | left +| * / % | left | + - | left | BETWEEN, IN, LIKE, SIMILAR, OVERLAPS, CONTAINS etc. | - | < > = <= >= <> != | left @@ -1083,14 +1083,15 @@ comp: | Operator syntax | Description |:------------------------- |:----------- | + numeric | Returns *numeric* -|:- numeric | Returns negative *numeric* +| - numeric | Returns negative *numeric* | numeric1 + numeric2 | Returns *numeric1* plus *numeric2* | numeric1 - numeric2 | Returns *numeric1* minus *numeric2* | numeric1 * numeric2 | Returns *numeric1* multiplied by *numeric2* | numeric1 / numeric2 | Returns *numeric1* divided by *numeric2* +| numeric1 % numeric2 | As *MOD(numeric1, numeric2)* (only in certain [conformance levels]({{ site.apiRoot }}/org/apache/calcite/sql/validate/SqlConformance.html#isPercentRemainderAllowed--)) | POWER(numeric1, numeric2) | Returns *numeric1* raised to the power of *numeric2* | ABS(numeric) | Returns the absolute value of *numeric* -| MOD(numeric, numeric) | Returns the remainder (modulus) of *numeric1* divided by *numeric2*. The result is negative only if *numeric1* is negative +| MOD(numeric1, numeric2) | Returns the remainder (modulus) of *numeric1* divided by *numeric2*. The result is negative only if *numeric1* is negative | SQRT(numeric) | Returns the square root of *numeric* | LN(numeric) | Returns the natural logarithm (base *e*) of *numeric* | LOG10(numeric) | Returns the base 10 logarithm of *numeric* @@ -1112,7 +1113,7 @@ comp: | SIGN(numeric) | Returns the signum of *numeric* | SIN(numeric) | Returns the sine of *numeric* | TAN(numeric) | Returns the tangent of *numeric* -| TRUNCATE(numeric1 [, numeric2]) | Truncates *numeric1* to optionally *numeric2* (if not specified 0) places right to the decimal point. +| TRUNCATE(numeric1 [, numeric2]) | Truncates *numeric1* to optionally *numeric2* (if not specified 0) places right to the decimal point ### Character string operators and functions
