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 on calcite. 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.
Should I modify the SameOperatndTypeChecker method
--
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
