dynamobi-build
commented
12 years ago [author="jvs", created="Tue, 12 Aug 2008 15:05:23 -0500 (GMT-05:00)"]
The numeric behavior is annoying but correct per SQL:2003.
Part 2 Section 9.5 (implemented in org.eigenbase.sql.type.SqlTypeExplicitPrecedenceList)
Part 2 Section 10.4 Syntax Rule 6.b.iii.2.B (implemented in SqlUtil.filterRoutinesByParameterType
I'll add test coverage.
Regarding FRG-128, I'm inclined to keep the current behavior, which kind of makes sense, since you can overload functions on type, but procedures you can only overload on number of parameters (so there's no ambiguity from nulls). But if someone finds a definitive answer in SQL:2003, then we should tighten it up.
[reporter="emcdermid", created="Tue, 12 Aug 2008 11:59:22 -0500 (GMT-05:00)"] Passing a numeric value (like 1.0) to a UDF defined as taking an INTEGER parameter results in an error. For example:
CREATE SCHEMA bugrepro;
SET SCHEMA 'bugrepro';
SET PATH 'bugrepro';
CREATE OR REPLACE FUNCTION plusOne(n INTEGER)
RETURNS INTEGER
CONTAINS SQL
RETURN n + 1;
0: jdbc:farrago:> values plusOne(1);
+---------+
| EXPR$0 |
+---------+
| 2 |
+---------+
1 row selected (0.6 seconds)
0: jdbc:farrago:> values plusOne(1.0);
Error: From line 1, column 8 to line 1, column 15: No match found for function signature UPT(
It's not 100% clear to me that this actually is an error per SQL:2003, but if we decide it is not, we still need better test coverage as well as documentation.
There is a test in udfInvocation.sql for FRG-128 that checks casting of nulls for UDF parameters vs. UDR parameters (FRG-128), but the comments in the test and in the bug itself are ambiguous about whether this is correct behavior per SQL:2003 or merely the current behavior. Either way, we should add a test for the non-null case as well.