b+ tree

This is according to the Derbe-3892.

b+ tree

View more presentations from bitistu.

DERBY-4244

I apply the patch(you can find it in JIRA) 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,

• testAddColumn(org.apache.derbyTesting.functionTests.tests.lang.AlterTableTest) java.sql.SQLException: Table/View 'T0_1' already exists in Schema 'TEST_DBO'.

It shows that the schema must change to default.

DERBY-4244: A statement makes the problem

I did the following tests,

Without, alter table t0 add column c1 int;

ij> create table t0(c1 int not null constraint p1 primary key);

0 rows inserted/updated/deleted

ij> alter table t0 add column c2 int not null default 0 primary key;

ERROR X0Y58: Attempt to add a primary key constraint to table '"APP"."T0"' failed because the table already has a constr

aint of that type. A table can only have a single primary key constraint.

ij> alter table t0 add column c2 int not null default 0;

ERROR XSCH5: In a base table there was a mismatch between the requested column number 1 and the maximum number of column

s 2.

It didn't pass.

Without, alter table t0 add column c1 int; and alter table t0 add column c2 int not null default 0 primary key;

ij> create table t0(c1 int not null constraint p1 primary key);

0 rows inserted/updated/deleted

ij> alter table t0 add column c2 int not null default 0;

0 rows inserted/updated/deleted

ij> drop table t0;

0 rows inserted/updated/deleted

It succeed.

Without, alter table t0 add column c1 int; only

ij> create table t0(c1 int not null constraint p1 primary key);

0 rows inserted/updated/deleted

ij> alter table t0 add column c1 int;

ERROR X0Y32: Column 'C1' already exists in Table/View '"APP"."T0"'.

ij> alter table t0 add column c2 int not null default 0;

0 rows inserted/updated/deleted

It succeed.

Conclusion:Though the statement alter table t0 add column c2 int not null default 0 primary key; did not affect on the table to create a column. It effect on the following codes.

To see test what codes it effects, We did the following test, With the a small change of the second statement

ij> create table t0(c1 int not null constraint p1 primary key);

0 rows inserted/updated/deleted

ij> alter table t0 add column c3 int not null default 0 primary key;

0 rows inserted/updated/deleted

ij> alter table t0 add column c2 int not null default 0;

0 rows inserted/updated/deleted

It suceed the test.

Conclusion: When the statement alter table t0 add column c2 int not null default 0 primary key; executed(It gives a error), something remains in the database engine that can be effect on creating a new column which has the same name 'c2'.

A word on DERBY-4244

This is a bug identified by Bryan during working on DERBY-4187.

If present bug report in breaf,

ij> connect 'jdbc:derby:mydb;create=true';
ij> autocommit off;
ij> create table t0(c1 int not null constraint p1 primary key);
0 rows inserted/updated/deleted
ij> alter table t0 add column c1 int;
ERROR X0Y32: Column 'C1' already exists in Table/View '"APP"."T0"'.
ij> alter table t0 add column c2 int not null default 0 primary key;
ERROR X0Y58: Attempt to add a primary key constraint to table '"APP"."T0"' failed because the table already has a constr
aint of that type. A table can only have a single primary key constraint.
ij> alter table t0 add column c2 int not null default 0;
ERROR XSCH5: In a base table there was a mismatch between the requested column number 1 and the maximum number of column
s 2.
ij> drop table t0;

Here in normal situation this must be passed or this must create the not null column c2 which has a default value of '0'.

Currently working DERBY issues

Convert altertable.sql into JUNIT
https://issues.apache.org/jira/browse/DERBY-4187
I and Bryan was finished this and these days I my self looking for the code for errors while learning the behavior of DERBY.

ALTER TABLE Sanity ASSERT in add column with autocommit off
https://issues.apache.org/jira/browse/DERBY-4244
I still not actively participated. But I look for it as soon as possible.

Convert checkConstraint.sql into JUNIT
https://issues.apache.org/jira/browse/DERBY-4248
I and Bryan working on this issue these days.

allow alter table to increase the maximum size of a blob and a clob
https://issues.apache.org/jira/browse/DERBY-4256
I still not actively participated. But I look for it as soon as possible.

strange behavior with the "update ... where current of c1" in the CheckConstraintTest
https://issues.apache.org/jira/browse/DERBY-4282
This issue is a bug where Bryan found during the DERBY-4248.