caicancai commented on code in PR #3785:
URL: https://github.com/apache/calcite/pull/3785#discussion_r1596920047
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -10036,6 +10036,45 @@ void assertSubFunReturns(boolean binary, String s, int
start,
checkNvl(f, FunctionAlias.of(SqlLibraryOperators.NVL));
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6397";>[CALCITE-6397]
+ * Add NVL2 function (enabled in Spark library). </a>.
+ */
+ @Test void testNvl2Func() {
+ final SqlOperatorFixture f = fixture();
+ f.setFor(SqlLibraryOperators.NVL2, VmName.EXPAND);
+ f.checkFails("^nvl2(NULL, 2, 1)^",
+ "No match found for function signature "
+ + "NVL2\\(<NULL>, <NUMERIC>, <NUMERIC>\\)", false);
+
+ final SqlOperatorFixture f12 = f
+ .withLibrary(SqlLibrary.SPARK);
+ f12.checkScalar("nvl2(NULL, 2, 1)", "1", "INTEGER NOT NULL");
+ f12.checkScalar("nvl2(true, true, false)", true, "BOOLEAN NOT NULL");
+ f12.checkScalar("nvl2(false, true, false)", true, "BOOLEAN NOT NULL");
+ f12.checkScalar("nvl2(NULL, true, false)", false, "BOOLEAN NOT NULL");
+ f12.checkScalar("nvl2(3, 2, 1)", "2", "INTEGER NOT NULL");
+ f12.checkScalar("nvl2(3, 'a', 'b')", "a", "CHAR(1) NOT NULL");
+ f12.checkScalar("nvl2(NULL, 'a', 'b')", "b", "CHAR(1) NOT NULL");
+ f12.checkScalar("nvl2(NULL, 'ab', 'de')", "de", "CHAR(2) NOT NULL");
+ f12.checkScalar("nvl2('ab', 'abc', 'def')", "abc", "CHAR(3) NOT NULL");
+ f12.checkScalar("nvl2('a', 3, 2)", "3", "INTEGER NOT NULL");
+
+ // check operand 1 and operand 2 are inconsistent
+ f12.checkScalar("NVL2(2.0, 'abc', 2)", "abc", "CHAR(3) NOT NULL");
+ f12.checkScalar("NVL2(NULL, 'abc', 2)", "2", "INTEGER NOT NULL");
+ f12.checkScalar("NVL2(NULL, 3.0, 4.0)", "4.0", "DECIMAL(2, 1) NOT NULL");
+ f12.checkScalar("NVL2(NULL, 'abc', 2)", "2", "INTEGER NOT NULL");
+ f12.checkScalar("NVL2('abc', 3.0, 4.0)", "3.0", "DECIMAL(2, 1) NOT NULL");
+ f12.checkScalar("NVL2(1, 3.0, 'abc')", "3.0", "DECIMAL(2, 1) NOT NULL");
+ f12.checkScalar("NVL2(NULL, 3.0, 'abc')", "abc", "CHAR(3) NOT NULL");
+
+ f12.checkNull("nvl2('ab', CAST(NULL AS VARCHAR(6)), 'def')");
+ f12.checkNull("nvl2(NULL, 'abc', NULL)");
+ f12.checkNull("nvl2(NULL, NULL, NULL)");
Review Comment:
@mihaibudiu Hello, sorry for replying so late. I found a problem.
NVL2 (2.0, 'abc', true) is allowed in type verification. The string type and
the Boolean type are comparable. I am not sure about this. According to my
understanding, it is not allowed. This is also not allowed in spark.
--
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
