Experiences of Apache Derby

I am Eranda Sooriyabandara of University of Moratuwa Department of Computer Science and Engineering.I choose the project Derby test and fix for the 2009 GSoC project and success in approving the project proposal.I work my best to do something to Derby development with the help of Bryan Pendleton as my mentor.

Here are some details about issues I am working


ALTER TABLE Sanity ASSERT in add column with autocommit off

(https://issues.apache.org/jira/browse/DERBY-4244) crated on: 23/May/09; This is a issue Bryan found while we working on the DERBY-4187.

I apply the patch which is modify by Bryan (java/engine/org/apache/derby/impl/sql/compile/AlterTableNode.java) to the trunk. After applying the patch I saw there are some of the initialized table are not there. I paste the "st.executeUpdate("create table t0_1(c1 int)");" in the near above the alter table t0_1; But the problem was when rollback() command applied the table vanishes out and the further coding cannot see a table named t0_1; I used it in the top of the method, and I got the error message as,

It shows that the schema must change to default. It's happen after the connection.rollback() method used. This occurs may because of new connection crated. I use connection.commit() to the row-86 to keep the tables which inserted in createTestObjects() method, when rollback() method call. It successfully ran in my platform. After this Bryan decided to replace the piece of code what the Knut show it as a extra codiing though it was not added a value to the code. After doing it he committed the fix, as revision 795459. This is the end of this issue.


convert checkConstraint.sql to JUnit

(https://issues.apache.org/jira/browse/DERBY-4248) created on:25/May/09;

This is my second issue of converting test to JUNIT. As Before I convert the code using the DERBY-2151 tool. I made some changes to this code considering errors while compiling and running the test. We face a problem with in "update ... where current of c1" and I create a issue on it.

We think to comment the part of failure until the issue DERBY-4282 successful. So now I trying to separate the big test_checkConstraint() method into several small tests as we done on the AlterTableTest. I did break the code into five methods as,

And now I doing the compare the checkConstraint.out and the CheckConstraintTest for looking are there any missing parts and changes.

I did the check and I didn't found any error or missing parts in the code. So I commit it to Bryan as the final patch of the issue.


strange behavior with the "update ... where current of c1" in the CheckConstraintTest

(https://issues.apache.org/jira/browse/DERBY-4282) created on:20/Jun/09;

This is the the bug we face a problem while we working on the DERBY-4248


convert columnDefaults.sql to JUNIT


I converted the columnDefaults.sql using the DERBY-2151 ij to JUNIT tool. In the test I import some libraries, set the package name and make few changes in the row 477(corrected the column names of the table tabWithUserAndSchemaDefaults). Now the test ran successfully in my platform.

I break this test into 4 methods for easy to understand,

It ran successfully in my platform after break in to methods and I search the code for any faults. I could not find faults or missing parts in the converted test except some missing comments. I add those missing comments and send to JIRA as my final patch. After that Bryan commit the patch.


convert inbetween.sql to JUNIT

(https://issues.apache.org/jira/browse/DERBY-4318) I converted the columnDefaults.sql using the DERBY-2151 ij to JUNIT tool.

In the code I face with a problem with the code,


and I replace with the code,


And I got the results fine.

I got a problem with the code of,

rs = st.executeQuery(

And I got the result as,

test_inbetween(org.apache.derbyTesting.functionTests.tests.lang.InbetweenTest)junit.framework.AssertionFailedError: C

olumn value mismatch @ column '1', row 1:

46 dependencies found<.

I passed the test with the code,

rs = st.executeQuery(

But it is differ from what we expect as the result.

Without the previous error I got another error from the code, rs = st.executeQuery(

And as Mamta suggest I did the correction as,

st.executeQuery("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)"); rs=st.executeQuery("SELECT R FROM t");

And this works but I don't know for sure this is correct or wrong.

There were other 3 errors remains in the testing. They are

1.In row 687, if(usingEmbedded()){

2.In the row 2891, rs = st.executeQuery("values (3, cast (null as int))"); for (int i = 1;

3.In row 4620, st.executeUpdate(

The final one gives Syntax Error and I gave the solution by adding braces to the select c1 from t1 and select c2 from t2.

The second error the solution gave by Bryan by changing the code, for (int i = 1;


We can derive the solution as below,

Though we cast null into integer it remains as the same and it just help to pass from,

rs = st.executeQuery("values (3, cast (null as int))");

which does not allow null in the values. But after we cast, it proceed as a null object(not null Integer) and when execution time it gave the NullPointerException. We cannot directly cast the null into Integer ((INTEGER)rs.getObject(i)). But we can do it by give the data type as integer,

pSt.setObject(i, rs.getObject(i), Types.INTEGER);.

To solve the problem(1st) we were faced in the test was solved,

We change the code,

TestSuite suite = new TestSuite("InbetweenTest Test");


TestSuite suite = new TestSuite("InbetweenTest Test");

After that we get 16 dependencies and no open scans as a result of the function ConsistencyChecker(). There are 16 dependencies when run as embedded and only two found when run in ij.The reason for the changes when the ij runs the onsistencyChecker() method does not show the actual no of dependencies. It shows the dependencies changed when ij runs. So the total no of dependencies when the ij runs is 18. When ij runs it shows the no of dependencies as 2(18-16).

Now the code look likes,


I break the long test_inbetween() into several methods. Those methods are,

I compare the test with the output of test file and change the code with adding some comments and removing repeated codings. I think now the test is clean and can commit as a revision.


allow alter table to increase the maximum size of a blob and a clob


I study this issue and I came up with solution with the help of Bryan's comment on the issue, as follow.

I ran the the test as,

ij> connect 'jdbc:derby:MyDbTest;create=true'; ij> create table binarydata(c1 int,data blob(64)); 0 rows inserted/updated/deleted ij> ALTER TABLE binarydata ALTER COLUMN data SET DATA TYPE blob(256M); ERROR 42Z16: Only columns of type VARCHAR may have their length altered.

The error give me the idea of that in the code there must be a code restrict the clob\blob to be resize. So I check the place where the Bryan mentioned.(I think the first place to start is with the 'checkUserType()' method in o.a.d.impl.sql.compile.ModifyColumnNode. Look around line 118. ). So I think the following additional code will success the test.

if (!(typeName.equals(TypeId.VARCHAR_NAME)) && !(typeName.equals(TypeId.VARBIT_NAME)) &&!(typeName.equals(TypeId.BLOB_NAME)))&&!(typeName.equals(TypeId.CLOB_NAME)) ) { throw StandardException.newException(


and it succeed the test

ij version 10.6

ij> connect 'jdbc:derby:testdb;create=true';

ij> create table binarydata(c1 int,data blob(64));

0 rows inserted/updated/deleted

ij> ALTER TABLE binarydata ALTER COLUMN data SET DATA TYPE blob(256M);

0 rows inserted/updated/deleted

After that we added the test for this issue to the CheckConstraintTest and commit.

Conversion of Derby tests into JUnit (last edited 2010-03-31 16:14:24 by 111)