leonardBang commented on a change in pull request #9669: [FLINK-13382] [Table
SQL / Planner] Port DecimalITCase to unit tests
URL: https://github.com/apache/flink/pull/9669#discussion_r329531038
##########
File path:
flink-table/flink-table-planner-blink/src/test/scala/org/apache/flink/table/planner/expressions/DecimalTypeTest.scala
##########
@@ -295,32 +310,1042 @@ class DecimalTypeTest extends ExpressionTestBase {
"f4 < f0",
"true")
- // TODO add all tests if FLINK-4070 is fixed
testSqlApi(
"12 < f1",
"true")
}
+ @Test
+ def testTableDataSource(): Unit = {
+
+ // the most basic case
+ testAllApis(
+ 'f6,
+ "f6",
+ "f6",
+ "123")
+
+ testAllApis(
+ 'f7,
+ "f7",
+ "f7",
+ "123.45")
+
+ // data from source are rounded to their declared scale before entering
next step
+ testAllApis(
+ 'f8,
+ "f8",
+ "f8",
+ "100.00")
+
+ testAllApis(
+ 'f8 + 'f8,
+ "f8 + f8",
+ "f8 + f8",
+ "200.00")
+
+ // trailing zeros are padded to the scale
+ testAllApis(
+ 'f9,
+ "f9",
+ "f9",
+ "100.10")
+
+ testAllApis(
+ 'f9 + 'f9,
+ "f9 + f9",
+ "f9 + f9",
+ "200.20")
+
+ // source data is within precision after rounding
+ testAllApis(
+ 'f10,
+ "f10",
+ "f10",
+ "100.00")
+
+ testAllApis(
+ 'f10 + 'f10,
+ "f10 + f10",
+ "f10 + f10",
+ "200.00")
+
+ // source data overflows over precision (after rounding)
+ testAllApis(
+ 'f11,
+ "f11",
+ "f11",
+ "null")
+
+ testAllApis(
+ 'f12,
+ "f12",
+ "f12",
+ "null")
+ }
+
+ @Test
+ def testUnaryPlusMinus(): Unit = {
+
+ testAllApis(
+ + 'f6,
+ "+f6",
+ "+f6",
+ "123")
+
+ testAllApis(
+ - 'f7,
+ "-f7",
+ "-f7",
+ "-123.45")
+
+ testAllApis(
+ - (( + 'f6) - ( - 'f7)),
+ "- (( + f6) - ( - f7))",
+ "- (( + f6) - ( - f7))",
+ "-246.45")
+ }
+
+ @Test
+ def testPlusMinus(): Unit = {
+
+ // see calcite ReturnTypes.DECIMAL_SUM
+ // s = max(s1,s2), p-s = max(p1-s1, p2-s2) + 1
+ // p then is capped at 38
+ testAllApis(
+ 'f13 + 'f14,
+ "f13 + f14",
+ "f13 + f14",
+ "300.2434")
+
+ testAllApis(
+ 'f13 - 'f14,
+ "f13 - f14",
+ "f13 - f14",
+ "-100.0034")
+
+ // INT => DECIMAL(10,0)
+ // approximate + exact => approximate
+ testAllApis(
+ 'f7 + 'f2,
+ "f7 + f2",
+ "f7 + f2",
+ "165.45")
+
+ testAllApis(
+ 'f2 + 'f7,
+ "f2 + f7",
+ "f2 + f7",
+ "165.45")
+
+ testAllApis(
+ 'f7 + 'f3,
+ "f7 + f3",
+ "f7 + f3",
+ "127.65")
+
+ testAllApis(
+ 'f3 + 'f7,
+ "f3 + f7",
+ "f3 + f7",
+ "127.65")
+
+ // our result type precision is capped at 38
+ // SQL2003 $6.26 -- result scale is dictated as max(s1,s2). no
approximation allowed.
+ // calcite -- scale is not reduced; integral part may be reduced. overflow
may occur
+ // (38,10)+(38,28)=>(57,28)=>(38,28)
+ // T-SQL -- scale may be reduced to keep the integral part. approximation
may occur
+ // (38,10)+(38,28)=>(57,28)=>(38,9)
+ testAllApis(
+ 'f15 + 'f16,
+ "f15 + f16",
+ "f15 + f16",
+ "300.0246913578012345678901234567")
+
+ testAllApis(
+ 'f15 - 'f16,
+ "f15 - f16",
+ "f15 - f16",
+ "-100.0000000000012345678901234567")
+
+ // 10 digits integral part
+ testAllApis(
+ 'f17 + 'f18,
+ "f17 + f18",
+ "f17 + f18",
+ "null")
+
+ testAllApis(
+ 'f17 - 'f18,
+ "f17 - f18",
+ "f17 - f18",
+ "null")
+
+ // requires 39 digits
+ testAllApis(
+ 'f19 + 'f19,
+ "f19 + f19",
+ "f19 + f19",
+ "null")
+
+ // overflows in subexpression
+ testAllApis(
+ 'f19 + 'f19 - 'f19,
+ "f19 + f19 - f19",
+ "f19 + f19 - f19",
+ "null")
+ }
+
+ @Test
+ def testMultiply(): Unit = {
+
+ // see calcite ReturnTypes.DECIMAL_PRODUCT
+ // s = s1+s2, p = p1+p2
+ // both p&s are capped at 38
+ // if s>38, result is rounded to s=38, and the integral part can only be
zero
+ testAllApis(
+ 'f20 * 'f20,
+ "f20 * f20",
+ "f20 * f20",
+ "1.0000")
+
+ testAllApis(
+ 'f20 * 'f21,
+ "f20 * f21",
+ "f20 * f21",
+ "2.000000")
+
+ // INT => DECIMAL(10,0)
+ // approximate * exact => approximate
+ testAllApis(
+ 'f20 * 'f22,
+ "f20 * f22",
+ "f20 * f22",
+ "200.00")
+
+ testAllApis(
+ 'f22 * 'f20,
+ "f22 * f20",
+ "f22 * f20",
+ "200.00")
+
+ testAllApis(
+ 'f20 * 'f23,
+ "f20 * f23",
+ "f20 * f23",
+ "3.14")
+
+ testAllApis(
+ 'f23 * 'f20,
+ "f23 * f20",
+ "f23 * f20",
+ "3.14")
+
+ // precision is capped at 38; scale will not be reduced (unless over 38)
+ // similar to plus&minus, and calcite behavior is different from T-SQL.
+ testAllApis(
+ 'f24 * 'f24,
+ "f24 * f24",
+ "f24 * f24",
+ "1.000000000000")
+
+ testAllApis(
+ 'f24 * 'f25,
+ "f24 * f25",
+ "f24 * f25",
+ "2.0000000000000000")
+
+ testAllApis(
+ 'f26 * 'f26,
+ "f26 * f26",
+ "f26 * f26",
+ "0.00010000000000000000000000000000000000"
+ )
+
+ // scalastyle:off
+ // we don't have this ridiculous behavior:
+ //
https://blogs.msdn.microsoft.com/sqlprogrammability/2006/03/29/multiplication-and-division-with-numerics/
+ // scalastyle:on
+
+ testAllApis(
+ 'f27 * 'f28,
+ "f27 * f28",
+ "f27 * f28",
+ "0.00000060000000000000"
+ )
+
+ // result overflow
+ testAllApis(
+ 'f29 * 'f29,
+ "f29 * f29",
+ "f29 * f29",
+ "null"
+ )
+
+ //(60,40)=>(38,38), no space for integral part
+ testAllApis(
+ 'f30 * 'f30,
+ "f30 * f30",
+ "f30 * f30",
+ "null"
+ )
+ }
+
+ @Test
+ def testDivide(): Unit = {
+
+ // the default impl of Calcite apparently borrows from T-SQL, but differs
in details.
+ // Flink overrides it to follow T-SQL exactly. See
FlinkTypeFactory.createDecimalQuotient()
+ testAllApis(
+ 'f31 / 'f32,
+ "f31 / f32",
+ "f31 / f32",
+ "0.333333")
+
+ testAllApis(
+ 'f31 / 'f33,
+ "f31 / f33",
+ "f31 / f33",
+ "0.3333333")
+
+ testAllApis(
+ 'f31 / 'f34,
+ "f31 / f34",
+ "f31 / f34",
+ "0.3333333333")
+
+ testAllApis(
+ 'f31 / 'f35,
+ "f31 / f35",
+ "f31 / f35",
+ "0.333333")
+
+ // INT => DECIMAL(10,0)
+ // approximate / exact => approximate
+ testAllApis(
+ 'f36 / 'f37,
+ "f36 / f37",
+ "f36 / f37",
+ "0.5000000000000")
+
+
+ testAllApis(
+ 'f37 / 'f36,
+ "f37 / f36",
+ "f37 / f36",
+ "2.00000000000")
+
+
+ testAllApis(
+ 'f36 / 'f38,
+ "f36 / f38",
+ "f36 / f38",
+ (1.0/3.0).toString)
+
+ testAllApis(
+ 'f38 / 'f36,
+ "f38 / f36",
+ "f38 / f36",
+ (3.0/1.0).toString)
+
+ // result overflow, because result type integral part is reduced
+ testAllApis(
+ 'f39 / 'f40,
+ "f39 / f40",
+ "f39 / f40",
+ "null")
+ }
+
+ @Test
+ def testMod(): Unit = {
+ // MOD(Exact1, Exact2) => Exact2
+ testAllApis(
+ 'f41 % 'f42,
+ "f41 % f42",
+ "mod(f41, f42)",
+ "3.0000")
+
+ testAllApis(
+ 'f42 % 'f41,
+ "f42 % f41",
+ "mod(f42, f41)",
+ "2.00")
+
+ testAllApis(
+ 'f41 % 'f43,
+ "f41 % f43",
+ "mod(f41, f43)",
+ "3")
+
+ testAllApis(
+ 'f43 % 'f41,
+ "f43 % f41",
+ "mod(f43, f41)",
+ "1.00")
+
+ // signs. consistent with Java's % operator.
+ testAllApis(
+ 'f44 % 'f45,
+ "f44 % f45",
+ "mod(f44, f45)",
+ (3%5).toString)
+
+ testAllApis(
+ -'f44 % 'f45,
+ "-f44 % f45",
+ "mod(-f44, f45)",
+ ((-3)%5).toString)
+
+ testAllApis(
+ 'f44 % -'f45,
+ "f44 % -f45",
+ "mod(f44, -f45)",
+ (3%(-5)).toString)
+
+ testAllApis(
+ -'f44 % -'f45,
+ "-f44 % -f45",
+ "mod(-f44, -f45)",
+ ((-3)%(-5)).toString)
+
+ // rounding in case s1>s2. note that SQL2003 requires s1=s2=0.
+ // (In T-SQL, s2 is expanded to s1, so that there's no rounding.)
+ testAllApis(
+ 'f46 % 'f47,
+ "f46 % f47",
+ "mod(f46, f47)",
+ "3.12")
+ }
+
+ @Test // functions that treat Decimal as exact value
+ def testExactionFunctions(): Unit = {
+
+ testAllApis(
+ ExpressionBuilder.ifThenElse('f48 > 'f49, 'f48, 'f49),
+ "ifThenElse(greaterThan(f48, f49), f48, f49)",
+ "if(f48 > f49, f48, f49)",
+ "3.14")
+
+ testAllApis(
+ 'f48.abs(),
+ "f48.abs()",
+ "abs(f48)",
+ "3.14"
+ )
+
+ testAllApis(
+ (-'f48).abs(),
+ "(-f48).abs()",
+ "abs(-f48)",
+ "3.14"
+ )
+
+ testAllApis(
+ 'f48.floor(),
+ "f48.floor()",
+ "floor(f48)",
+ "3"
+ )
+
+ testAllApis(
+ 'f48.ceil(),
+ "f48.ceil()",
+ "ceil(f48)",
+ "4"
+ )
+
+ // calcite: SIGN(Decimal(p,s))=>Decimal(p,s)
+ testAllApis(
+ 'f48.sign(),
+ "f48.sign()",
+ "sign(f48)",
+ "1.00"
+ )
+
+ testAllApis(
+ (-'f48).sign(),
+ "(-f48).sign()",
+ "sign(-f48)",
+ "-1.00"
+ )
+ testAllApis(
+ ('f48 - 'f48).sign(),
+ "(f48 - f48).sign()",
+ "sign(f48 - f48)",
+ "0.00"
+ )
+
+ // ROUND(Decimal(p,s)[,INT])
+ testAllApis(
+ 'f50.round(0),
+ "f50.round(0)",
+ "round(f50)",
+ "647")
+
+ testAllApis(
+ 'f50.round(0),
+ "f50.round(0)",
+ "round(f50,0)",
+ "647")
+
+ testAllApis(
+ 'f50.round(1),
+ "f50.round(1)",
+ "round(f50,1)",
+ "646.6")
+
+ testAllApis(
+ 'f50.round(2),
+ "f50.round(2)",
+ "round(f50,2)",
+ "646.65")
+
+ testAllApis(
+ 'f50.round(3),
+ "f50.round(3)",
+ "round(f50,3)",
+ "646.646")
+
+ testAllApis(
+ 'f50.round(4),
+ "f50.round(4)",
+ "round(f50,4)",
+ "646.646")
+
+ testAllApis(
+ 'f50.round(-1),
+ "f50.round(-1)",
+ "round(f50,-1)",
+ "650")
+
+ testAllApis(
+ 'f50.round(-2),
+ "f50.round(-2)",
+ "round(f50,-2)",
+ "600")
+
+ testAllApis(
+ 'f50.round(-3),
+ "f50.round(-3)",
+ "round(f50,-3)",
+ "1000")
+
+ testAllApis(
+ 'f50.round(-4),
+ "f50.round(-4)",
+ "round(f50,-4)",
+ "0")
+
+ testAllApis(
+ 'f51.round(1),
+ "f51.round(1)",
+ "round(f51,1)",
+ "100.0")
+
+ testAllApis(
+ (-'f51).round(1),
+ "(-f51).round(1)",
+ "round(-f51,1)",
+ "-100.0")
+
+ testAllApis(
+ ('f51).round(-1),
+ "(f51).round(-1)",
+ "round(f51,-1)",
+ "100")
+
+ testAllApis(
+ (-'f51).round(-1),
+ "(-f51).round(-1)",
+ "round(-f51,-1)",
+ "-100")
+
+ testAllApis(
+ ('f52).round(-1),
+ "(f52).round(-1)",
+ "round(f52,-1)",
+ "null")
+ }
+
+ @Test // functions e.g. sin() that treat Decimal as double
+ def testApproximateFunctions(): Unit = {
+ // skip moving ApproximateFunctions tests from
+ // sql/DecimalITCase.scala and table/DecimalITcase.scala
+ // because these tests will run fail until FLINK-14036 is fixed
+ }
+
+ @Test
+ def testCaseWhen(): Unit = {
+
+ // result type: SQL2003 $9.23, calcite
RelDataTypeFactory.leastRestrictive()
+ testSqlApi(
+ "case f53 when 0 then f53 else f54 end",
+ "0.0100")
+
+ testSqlApi(
+ "case f53 when 0 then f53 else f2 end",
+ "42.0000")
+
+ testSqlApi(
+ "case f53 when 0 then f23 else f53 end",
+ BigDecimal("0.0001").doubleValue().toString)
+ }
+
+ @Test
+ def testCast(): Unit = {
+
+ // String, numeric/Decimal => Decimal
+ testSqlApi(
+ "cast(f48 as Decimal(8,4))",
+ "3.1400")
+
+ testSqlApi(
+ "cast(f2 as Decimal(8,4))",
+ "42.0000")
+
+ testSqlApi(
+ "cast(f3 as Decimal(8,4))",
+ "4.2000")
+
+ testSqlApi(
+ "cast(f55 as Decimal(8,4))",
+ "3.1400")
+
+ // round up
+ testSqlApi(
+ "cast(f56 as Decimal(8,1))",
+ "3.2")
+
+ testSqlApi(
+ "cast(f57 as Decimal(8,1))",
+ "3.2")
+
+ testSqlApi(
+ "cast(f58 as Decimal(8,1))",
+ "3.2")
+
+ testSqlApi(
+ "cast(f59 as Decimal(3,2))",
+ "null")
+
+ // Decimal => String, numeric
+ testSqlApi(
+ "cast(f60 as VARCHAR(64))",
+ "1.99")
+
+ testSqlApi(
+ "cast(f61 as DOUBLE)",
+ "1.99")
+
+ testSqlApi(
+ "cast(f62 as INT)",
+ "1")
+ }
+
+ @Test
+ def testQquality(): Unit = {
Review comment:
thanks，it's a mistake
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
[email protected]
With regards,
Apache Git Services
