You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by dj...@apache.org on 2006/11/16 00:22:29 UTC
svn commit: r475490 -
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RoutineTest.java
Author: djd
Date: Wed Nov 15 15:22:26 2006
New Revision: 475490
URL: http://svn.apache.org/viewvc?view=rev&rev=475490
Log:
DERBY-1030 (partial) Add test cases to RoutineTest that demonstrate this bug, where
a function declared as RETURNS NULL ON NULL INPUT can be called when its argument is NULL.
Look for the comment with this bug number.
Modified:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RoutineTest.java
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RoutineTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RoutineTest.java?view=diff&rev=475490&r1=475489&r2=475490
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RoutineTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RoutineTest.java Wed Nov 15 15:22:26 2006
@@ -24,6 +24,7 @@
import java.io.UnsupportedEncodingException;
import java.sql.PreparedStatement;
import java.sql.SQLException;
+import java.sql.Time;
import java.sql.Types;
import junit.framework.Test;
@@ -258,7 +259,94 @@
ps.setNull(1, Types.INTEGER);
assertStatementError("39004", ps); // Can't pass NULL into primitive type
ps.close();
+
+ errors = runSQLCommands(
+ "CREATE FUNCTION NOON_NOCALL(TIME) " +
+ "RETURNS TIME " +
+ "RETURNS NULL ON NULL INPUT " +
+ "EXTERNAL NAME '" +
+ RoutineTest.class.getName() + ".nullAtNoon' " +
+ "LANGUAGE JAVA PARAMETER STYLE JAVA; " +
+ "CREATE FUNCTION NOON_CALL(TIME) " +
+ "RETURNS TIME " +
+ "CALLED ON NULL INPUT " +
+ "EXTERNAL NAME '" +
+ RoutineTest.class.getName() + ".nullAtNoon' " +
+ "LANGUAGE JAVA PARAMETER STYLE JAVA; "
+
+ );
+ assertEquals("errors running DDL", 0, errors);
+
+ // Function maps:
+ // NULL to 11:00:00 (if null can be passed)
+ // 11:00:00 to 11:30:00
+ // 12:00:00 to NULL
+ // any other time to itself
+
+ Time noon = Time.valueOf("12:00:00"); // mapped to null by the function
+ Time tea = Time.valueOf("15:30:00");
+
+ ps = prepareStatement("VALUES NOON_NOCALL(?)");
+ ps.setTime(1, tea);
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), tea.toString());
+ ps.setTime(1, noon);
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), null);
+ ps.setTime(1, null);
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), null);
+ ps.close();
+
+ ps = prepareStatement("VALUES NOON_CALL(?)");
+ ps.setTime(1, tea);
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), tea.toString());
+ ps.setTime(1, noon);
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), null);
+ ps.setTime(1, null);
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), "11:00:00");
+ ps.close();
+
+ // All the nested calls in these cases take take the
+ // value 'tea' will return the same value.
+
+ ps = prepareStatement("VALUES NOON_NOCALL(NOON_NOCALL(?))");
+ ps.setTime(1, tea);
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), tea.toString());
+ ps.setTime(1, noon); // noon->NULL->NULL
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), null);
+ ps.setTime(1, null); // NULL->NULL->NULL
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), null);
+ ps.close();
+ ps = prepareStatement("VALUES NOON_NOCALL(NOON_CALL(?))");
+ ps.setTime(1, tea);
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), tea.toString());
+
+ // DERBY-1030 RESULT SHOULD BE NULL
+ // noon->NULL by inner function
+ // NULL->NULL by outer due to RETURN NULL ON NULL INPUT
+ ps.setTime(1, noon); // noon->NULL->NULL
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), "11:00:00");
+ ps.setTime(1, null); // NULL->11:00:00->11:30:00
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), "11:30:00");
+
+ ps.close();
+
+ ps = prepareStatement("VALUES NOON_CALL(NOON_NOCALL(?))");
+ ps.setTime(1, tea);
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), tea.toString());
+ ps.setTime(1, noon); // noon->NULL->11:00:00
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), "11:00:00");
+ ps.setTime(1, null); // NULL->NULL->11:00:00
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), "11:00:00");
+ ps.close();
+
+ ps = prepareStatement("VALUES NOON_CALL(NOON_CALL(?))");
+ ps.setTime(1, tea);
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), tea.toString());
+ ps.setTime(1, noon); // noon->NULL->11:00:00
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), "11:00:00");
+ ps.setTime(1, null); // NULL->11:00:00->11:30:00
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), "11:30:00");
+ ps.close();
}
/*
@@ -279,6 +367,18 @@
public static int same(int i)
{
return i;
+ }
+
+ public static Time nullAtNoon(Time t) {
+ if (t == null)
+ return Time.valueOf("11:00:00");
+ String s = t.toString();
+ if ("11:00:00".equals(s))
+ return Time.valueOf("11:30:00");
+ if ("12:00:00".equals(s))
+ return null;
+
+ return t;
}
}