Funny phenomenon with referential integrity...

[Dirk Steinkamp]

Hi,

I encountered a funny phenomenon with the most recent Axion-HEAD... (2-Nov-2005)

There seems to be some bug when checking referential integrity columns. Looks as if Axion is checking the column names of the foreign key against the parent table! If column names in parent and dependent table are identical it doesn't matter, if they one gets an Exception:

        java.sql.SQLException: undefined column, attribute, or parameter name

See the last two ALTER TABLE commands in the sample script below...
- B2A works fine as table B and table A have identical column names
- C2A doesn't work


Dirk


---------------------------------------------------------------

create table A
(X number(9,0));

create table B
(X number(9,0));

create table C
(Y number(9,0));


ALTER TABLE A ADD CONSTRAINT A_PK PRIMARY KEY (X);

ALTER TABLE B
    ADD CONSTRAINT B2A
             FOREIGN KEY (X)
             REFERENCES A;

ALTER TABLE C
    ADD CONSTRAINT C2A
             FOREIGN KEY (Y)
             REFERENCES A;


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Ahimanikya Satapathy]

I think Axion is ok; it just implements ANSI 2003 spec.

Try this,
ALTER TABLE C ADD CONSTRAINT C2A FOREIGN KEY (Y) REFERENCES A(X);

BTW, try the same test in oracle or other db that is available to you...

> -----Original Message-----
> From: Dirk Steinkamp [mailto:D.Steinkamp@...]
> Sent: Wednesday, November 02, 2005 9:16 AM
> To: users@...
> Subject: Funny phenomenon with referential integrity...
>
> Hi,
>
> I encountered a funny phenomenon with the most recent Axion-HEAD...
(2-Nov-
> 2005)
>
> There seems to be some bug when checking referential integrity
columns. Looks as
> if Axion is checking the column names of the foreign key against the
parent table! If
> column names in parent and dependent table are identical it doesn't
matter, if they
> one gets an Exception:
>
> java.sql.SQLException: undefined column, attribute, or parameter
name

>
> See the last two ALTER TABLE commands in the sample script below...
> - B2A works fine as table B and table A have identical column names
> - C2A doesn't work
>
>
> Dirk
>
>
> ---------------------------------------------------------------
>
> create table A
> (X number(9,0));
>
> create table B
> (X number(9,0));
>
> create table C
> (Y number(9,0));
>
>
> ALTER TABLE A ADD CONSTRAINT A_PK PRIMARY KEY (X);
>
> ALTER TABLE B
>     ADD CONSTRAINT B2A
>              FOREIGN KEY (X)
>              REFERENCES A;
>
> ALTER TABLE C
>     ADD CONSTRAINT C2A
>              FOREIGN KEY (Y)
>              REFERENCES A;
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@...
> For additional commands, e-mail: users-help@...

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Dirk Steinkamp]

> ALTER TABLE C ADD CONSTRAINT C2A FOREIGN KEY (Y) REFERENCES A(X);

Thank you for the additional syntax! That works for me!

> I think Axion is ok; it just implements ANSI 2003 spec.

unfortunately I don't have a ANSI-SQL 2003 spec at hand, only SQL-99 (Panny/Taudes)... and there it's described differently.

> BTW, try the same test in oracle or other db that is available to you...

I had tried it with Oracle 9i - that made me stumble over it, because Oracle took my original script without any errors.

I looked what Oracle 10g documents about this topic and I found the following at

        http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/clauses002.htm#sthref2432

-------------------------------------
[Second paragraph, note one but last sentence:]

The table or view containing the foreign key is called the child object, and the table or view containing the referenced key is called the parent object. The foreign key and the referenced key can be in the same table or view. In this case, the parent and child tables are the same. If you identify only the parent table or view and omit the column name, then the foreign key automatically references the primary key of the parent table or view. The corresponding column or columns of the foreign key and the referenced key must match in order and datatype.
------------------------------------

So either Oracle doesn't follow ANSI 2003 in this point or Axion doesn't ;-)...


Anyhow: I'm fine with the above solution and can continue working. Thanks for the great work on Axion, I really appreciate it!


Dirk


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Ahimanikya Satapathy-2]

If the column name is same in both tables we take it, we could check for
PK like oracle does.

> -----Original Message-----
> From: Dirk Steinkamp [mailto:D.Steinkamp@...]
> Sent: Wednesday, November 02, 2005 4:12 PM
> To: users@...
> Subject: RE: Funny phenomenon with referential integrity...
>
> > ALTER TABLE C ADD CONSTRAINT C2A FOREIGN KEY (Y) REFERENCES
> A(X);
>
> Thank you for the additional syntax! That works for me!
>
> > I think Axion is ok; it just implements ANSI 2003 spec.
>
> unfortunately I don't have a ANSI-SQL 2003 spec at hand, only SQL-99
> (Panny/Taudes)... and there it's described differently.
>
> > BTW, try the same test in oracle or other db that is available to

you...
>
> I had tried it with Oracle 9i - that made me stumble over it, because
Oracle took my
> original script without any errors.
>
> I looked what Oracle 10g documents about this topic and I found the
following at
>
> http://download-
>
uk.oracle.com/docs/cd/B19306_01/server.102/b14200/clauses002.htm#sthref2
432
>
> -------------------------------------
> [Second paragraph, note one but last sentence:]
>
> The table or view containing the foreign key is called the child
object, and the table
> or view containing the referenced key is called the parent object. The
foreign key
> and the referenced key can be in the same table or view. In this case,
the parent
> and child tables are the same. If you identify only the parent table
or view and omit
> the column name, then the foreign key automatically references the
primary key of
> the parent table or view. The corresponding column or columns of the
foreign key
> and the referenced key must match in order and datatype.
> ------------------------------------
>
> So either Oracle doesn't follow ANSI 2003 in this point or Axion
doesn't ;-)...
>
>
> Anyhow: I'm fine with the above solution and can continue working.
Thanks for the
> great work on Axion, I really appreciate it!
>
>
> Dirk
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@...
> For additional commands, e-mail: users-help@...


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Dirk Steinkamp]

One more question:

is it intended to support multi-column-foreign-keys? Axion seems to support single-column FKs and multi-column-PKs at the moment, right?


Thanks
Dirk


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Ahimanikya Satapathy]

We don't have support for multi column PK, but it may not be that
difficult to implement it.

Axion FK does support multiple keys, for example

ALTER TABLE C ADD CONSTRAINT C2A FOREIGN KEY (X,Y) REFERENCES
A(X,Y);

The above should work



> -----Original Message-----
> From: Dirk Steinkamp [mailto:D.Steinkamp@...]
> Sent: Wednesday, November 02, 2005 5:01 PM
> To: users@...
> Subject: RE: RE: Funny phenomenon with referential integrity...
>
> One more question:
>
> is it intended to support multi-column-foreign-keys? Axion seems to
support single-

> column FKs and multi-column-PKs at the moment, right?
>
>
> Thanks
> Dirk
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@...
> For additional commands, e-mail: users-help@...

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Dirk Steinkamp]

> We don't have support for multi column PK, but it may not be that
> difficult to implement it.

I tried it:

        create table A
        (X number(9,0),
         Y number(9,0));

        ALTER TABLE A ADD CONSTRAINT A_PK PRIMARY KEY (X,Y);

Axion accepts the command but seems to ignore checking the uniqueness:

        insert into a values (1,1);
        insert into a values (1,2);
        insert into a values (2,1);
        insert into a values (2,2);

If I have only one column in the PK-definition it seems to work.

> Axion FK does support multiple keys, for example

> ALTER TABLE C ADD CONSTRAINT C2A FOREIGN KEY (X,Y) REFERENCES
> A(X,Y);

> The above should work

no, that doesn't work. With a command like this I get:

        java.sql.SQLException: org.axiondb.parser.ParseException: Parse error at line 1, column 65.  Encountered: ,



---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Ahimanikya Satapathy]

OK. I have added this to axion too. So now

ALTER TABLE C ADD CONSTRAINT C2A FOREIGN KEY (Y) REFERENCES
A;

Should work, I have checked this in to cvs-head.

> -----Original Message-----
> From: Dirk Steinkamp [mailto:D.Steinkamp@...]
> Sent: Wednesday, November 02, 2005 4:12 PM
> To: users@...
> Subject: RE: Funny phenomenon with referential integrity...
>
> > ALTER TABLE C ADD CONSTRAINT C2A FOREIGN KEY (Y) REFERENCES
> A(X);
>
> Thank you for the additional syntax! That works for me!
>
> > I think Axion is ok; it just implements ANSI 2003 spec.
>
> unfortunately I don't have a ANSI-SQL 2003 spec at hand, only SQL-99
> (Panny/Taudes)... and there it's described differently.
>
> > BTW, try the same test in oracle or other db that is available to

you...
>
> I had tried it with Oracle 9i - that made me stumble over it, because
Oracle took my
> original script without any errors.
>
> I looked what Oracle 10g documents about this topic and I found the
following at
>
> http://download-
>
uk.oracle.com/docs/cd/B19306_01/server.102/b14200/clauses002.htm#sthref2
432
>
> -------------------------------------
> [Second paragraph, note one but last sentence:]
>
> The table or view containing the foreign key is called the child
object, and the table
> or view containing the referenced key is called the parent object. The
foreign key
> and the referenced key can be in the same table or view. In this case,
the parent
> and child tables are the same. If you identify only the parent table
or view and omit
> the column name, then the foreign key automatically references the
primary key of
> the parent table or view. The corresponding column or columns of the
foreign key
> and the referenced key must match in order and datatype.
> ------------------------------------
>
> So either Oracle doesn't follow ANSI 2003 in this point or Axion
doesn't ;-)...
>
>
> Anyhow: I'm fine with the above solution and can continue working.
Thanks for the
> great work on Axion, I really appreciate it!
>
>
> Dirk
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@...
> For additional commands, e-mail: users-help@...


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Ahimanikya Satapathy]

Looks like some parser issues, I will try to fix that. I see the object
having the support for muti-column FK.

> -----Original Message-----
> From: Dirk Steinkamp [mailto:D.Steinkamp@...]
> Sent: Wednesday, November 02, 2005 5:24 PM
> To: users@...
> Subject: RE: Funny phenomenon with referential integrity...
>
> > We don't have support for multi column PK, but it may not be that
> > difficult to implement it.
>
> I tried it:
>
> create table A
> (X number(9,0),
> Y number(9,0));
>
> ALTER TABLE A ADD CONSTRAINT A_PK PRIMARY KEY (X,Y);
>
> Axion accepts the command but seems to ignore checking the uniqueness:
>
> insert into a values (1,1);
> insert into a values (1,2);
> insert into a values (2,1);
> insert into a values (2,2);
>
> If I have only one column in the PK-definition it seems to work.
>
> > Axion FK does support multiple keys, for example
>
> > ALTER TABLE C ADD CONSTRAINT C2A FOREIGN KEY (X,Y) REFERENCES
> > A(X,Y);
>
> > The above should work
>
> no, that doesn't work. With a command like this I get:
>
> java.sql.SQLException: org.axiondb.parser.ParseException: Parse

error at
> line 1, column 65.  Encountered: ,
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@...
> For additional commands, e-mail: users-help@...


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Dirk Steinkamp]

> OK. I have added this to axion too. So now

> ALTER TABLE C ADD CONSTRAINT C2A FOREIGN KEY (Y) REFERENCES
> A;

> Should work, I have checked this in to cvs-head.

great! I'll check it out.


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Ahimanikya Satapathy]

Fixed and checked in. But we might have issues when you try to delete
rows from parent table, which child is referring, I have added a todo we
will fix it sometime.

> -----Original Message-----
> From: Dirk Steinkamp [mailto:D.Steinkamp@...]
> Sent: Wednesday, November 02, 2005 5:24 PM
> To: users@...
> Subject: RE: Funny phenomenon with referential integrity...
>
> > We don't have support for multi column PK, but it may not be that
> > difficult to implement it.
>
> I tried it:
>
> create table A
> (X number(9,0),
> Y number(9,0));
>
> ALTER TABLE A ADD CONSTRAINT A_PK PRIMARY KEY (X,Y);
>
> Axion accepts the command but seems to ignore checking the uniqueness:
>
> insert into a values (1,1);
> insert into a values (1,2);
> insert into a values (2,1);
> insert into a values (2,2);
>
> If I have only one column in the PK-definition it seems to work.
>
> > Axion FK does support multiple keys, for example
>
> > ALTER TABLE C ADD CONSTRAINT C2A FOREIGN KEY (X,Y) REFERENCES
> > A(X,Y);
>
> > The above should work
>
> no, that doesn't work. With a command like this I get:
>
> java.sql.SQLException: org.axiondb.parser.ParseException: Parse

error at
> line 1, column 65.  Encountered: ,
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@...
> For additional commands, e-mail: users-help@...


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Dirk Steinkamp]

Thanks for the quick response!!!

> Fixed and checked in. But we might have issues when you try to delete
> rows from parent table, which child is referring, I have added a todo we
> will fix it sometime.

yes, I checked it: deleting simply doesn't check any dependent records (at least one of the following would be nice: RESTRICT / ON DELETE CASCADE / ON DELETE SET NULL).

I also tried the multi-column-PK/FK-feature and found two additional issues

test code:
-------------------------------------------------------------------------
create table A
(X number(9,0),
 Y number(9,0));

create table B
(X number(9,0),
 Y number(9,0));

ALTER TABLE A ADD CONSTRAINT A_PK PRIMARY KEY (X,Y);

ALTER TABLE B ADD CONSTRAINT B2A FOREIGN KEY (X,Y) REFERENCES A;

insert into a values (1,1);
insert into a values (1,2);
insert into b values (1,1);
insert into b values (1,2);
-----------------------------------------------------------------------------

ISSUES:

1. When run with an in-memory database the 4th INSERT doesn't work:

        java.sql.SQLException: FOREIGN KEY constraint B2A violated.

2, When run with a disk database the 2nd constraint causes the following error message:

        java.sql.SQLException: Unable to write meta file test\B\B.META for table B (java.io.NotSerializableException: org.axiondb.engine.TransactableTableImpl)


Any idea?

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Ahimanikya Satapathy]

Don't create ALTER TABLE A ADD CONSTRAINT A_PK PRIMARY KEY (X,Y); for
now,  since we don't support multi-key PK, it not behave as expected. I
will see if I will get it work.

> -----Original Message-----
> From: Dirk Steinkamp [mailto:D.Steinkamp@...]
> Sent: Wednesday, November 02, 2005 6:31 PM
> To: users@...
> Subject: RE: RE: Funny phenomenon with referential integrity...
>
> Thanks for the quick response!!!
>
> > Fixed and checked in. But we might have issues when you try to
delete
> > rows from parent table, which child is referring, I have added a
todo we
> > will fix it sometime.
>
> yes, I checked it: deleting simply doesn't check any dependent records
(at least one
> of the following would be nice: RESTRICT / ON DELETE CASCADE / ON
DELETE
> SET NULL).
>
> I also tried the multi-column-PK/FK-feature and found two additional
issues
>
> test code:
>
------------------------------------------------------------------------
-

> create table A
> (X number(9,0),
>  Y number(9,0));
>
> create table B
> (X number(9,0),
>  Y number(9,0));
>
> ALTER TABLE A ADD CONSTRAINT A_PK PRIMARY KEY (X,Y);
>
> ALTER TABLE B ADD CONSTRAINT B2A FOREIGN KEY (X,Y) REFERENCES A;
>
> insert into a values (1,1);
> insert into a values (1,2);
> insert into b values (1,1);
> insert into b values (1,2);
>

------------------------------------------------------------------------
-----
>
> ISSUES:
>
> 1. When run with an in-memory database the 4th INSERT doesn't work:
>
> java.sql.SQLException: FOREIGN KEY constraint B2A violated.
>
> 2, When run with a disk database the 2nd constraint causes the
following error
> message:
>
> java.sql.SQLException: Unable to write meta file test\B\B.META
for table B
> (java.io.NotSerializableException:
org.axiondb.engine.TransactableTableImpl)
>
>
> Any idea?
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@...
> For additional commands, e-mail: users-help@...


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Dirk Steinkamp]

> Don't create ALTER TABLE A ADD CONSTRAINT A_PK PRIMARY KEY (X,Y); for
> now,  since we don't support multi-key PK, it not behave as expected. I
> will see if I will get it work.

the stated exception occurs independent of an existing PK when adding single- or multi-column-FK:

        java.sql.SQLException: Unable to write meta file test\B\B.META for table B (java.io.NotSerializableException: org.axiondb.engine.TransactableTableImpl)



---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Dirk Steinkamp]

Hi,

when testing the new HEAD export I got the impression there are some more issues maybe around TransactableTableImp ...

I got the following exception after inserting some data and doing some extensive delete operations on them:

java.lang.IndexOutOfBoundsException: Should be at least 0 and less than 964, found 1196
  at org.apache.commons.collections.primitives.ArrayUnsignedIntList.checkRange(ArrayUnsignedIntList.java:295)
  at org.apache.commons.collections.primitives.ArrayUnsignedIntList.get(ArrayUnsignedIntList.java:139)
  at org.axiondb.io.AxionFileSystem$PidxList.get(AxionFileSystem.java:369)
  at org.axiondb.engine.tables.BaseDiskTable.getRow(BaseDiskTable.java:193)
  at org.axiondb.engine.TransactableTableImpl.getRow(TransactableTableImpl.java:360)
  at org.axiondb.engine.rows.LazyRow.getRow(LazyRow.java:85)
  at org.axiondb.engine.rows.LazyRow.get(LazyRow.java:70)
  at org.axiondb.RowDecorator.get(RowDecorator.java:64)
  at org.axiondb.ColumnIdentifier.evaluate(ColumnIdentifier.java:121)
  at org.axiondb.jdbc.AxionResultSet.getValue(AxionResultSet.java:1265)
  at org.axiondb.jdbc.AxionResultSet.getObject(AxionResultSet.java:555)
  at org.axiondb.jdbc.AxionResultSet.getObject(AxionResultSet.java:563)
  at org.axiondb.jdbc.BaseAxionResultSetDecorator.getObject(BaseAxionResultSetDecorator.java:274)
  at org.axiondb.jdbc.BaseAxionResultSetDecorator.getObject(BaseAxionResultSetDecorator.java:274)
  at entity.Entity.setValuesFromResultSet(Entity.java:362)
  at (...)


it seems a little hard to boil it down to a simple test case ... maybe you have a idea anyhow.

Thanks
Dirk

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Ahimanikya Satapathy]

Looks like  there is some syncronization issue between the index and the
data.

The row is deleted from the table but somehow the index container thinks
the row exist and try to get it from the table.

Send the command sequence, I will try to see if I can reproduce.

> -----Original Message-----
> From: Dirk Steinkamp [mailto:D.Steinkamp@...]
> Sent: Thursday, November 03, 2005 2:19 AM
> To: users@...
> Subject: More issues...
>
> Hi,
>
> when testing the new HEAD export I got the impression there are some
more
> issues maybe around TransactableTableImp ...
>
> I got the following exception after inserting some data and doing some
extensive
> delete operations on them:
>
> java.lang.IndexOutOfBoundsException: Should be at least 0 and less
than 964,
> found 1196
>   at
>
org.apache.commons.collections.primitives.ArrayUnsignedIntList.checkRang
e(Arra
> yUnsignedIntList.java:295)
>   at
>
org.apache.commons.collections.primitives.ArrayUnsignedIntList.get(Array
Unsigne
> dIntList.java:139)
>   at
org.axiondb.io.AxionFileSystem$PidxList.get(AxionFileSystem.java:369)
>   at
> org.axiondb.engine.tables.BaseDiskTable.getRow(BaseDiskTable.java:193)
>   at
>
org.axiondb.engine.TransactableTableImpl.getRow(TransactableTableImpl.ja
va:36
> 0)
>   at org.axiondb.engine.rows.LazyRow.getRow(LazyRow.java:85)
>   at org.axiondb.engine.rows.LazyRow.get(LazyRow.java:70)
>   at org.axiondb.RowDecorator.get(RowDecorator.java:64)
>   at
org.axiondb.ColumnIdentifier.evaluate(ColumnIdentifier.java:121)
>   at
org.axiondb.jdbc.AxionResultSet.getValue(AxionResultSet.java:1265)
>   at
org.axiondb.jdbc.AxionResultSet.getObject(AxionResultSet.java:555)
>   at
org.axiondb.jdbc.AxionResultSet.getObject(AxionResultSet.java:563)
>   at
>
org.axiondb.jdbc.BaseAxionResultSetDecorator.getObject(BaseAxionResultSe
tDec
> orator.java:274)
>   at
>
org.axiondb.jdbc.BaseAxionResultSetDecorator.getObject(BaseAxionResultSe
tDec
> orator.java:274)
>   at entity.Entity.setValuesFromResultSet(Entity.java:362)
>   at (...)
>
>
> it seems a little hard to boil it down to a simple test case ... maybe
you have a idea
> anyhow.
>
> Thanks
> Dirk
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@...
> For additional commands, e-mail: users-help@...


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...