Re: H2 1.3-168 possible bag bug for FOREIGN KEYS defined on 2 (or more) fields ?

[James Moger]

Interesting.  I found what I believe to be a bug with foreign keys the other day - also against 1.3.168:

Referenced tables can be dropped without an SQLException.

https://code.google.com/p/h2database/issues/detail?id=422

Perhaps the two are related.

[Noel Grandin]

On 2012-10-16 10:21, Patrox wrote:
> Hi !
> While testing an application we realized than H2 1.3-168 seems to not
> handle properly FOREIGN KEYs defined between 2 tables when a same
> foreign key is defined with more than a single field.
> For example such a FK (below) does not raise a violation when
> inserting bad datas in the couple supposedly protected by the FK. It
> seems to me it behaves like 2 separates FKs on single fields, while it
> is NOT the expected behavior. A composite FK is supposed to protect
> the data combination, not single separate fields values.
> ALTER TABLE
> TEST_POSSIBLE_BUG
> ADD CONSTRAINT FK_TEST001
> FOREIGN KEY (tpg_aaa_id,tpg_bbb_id)
> REFERENCES MASTER_TABLE (aaa_id,bbb_id);
>
>

Can you provide a self-contained test case please?

[Noel Grandin]

Hi

Thank you for the test-case.
There was a bug where it would sometimes use the wrong index when
validating foreign key constraints.
The fix has been committed to SVN and will be in the next release.

Regards, Noel.

On 2012-10-18 11:31, Patrice Castet wrote:
> sure, please find attached :
> 1) a SQL script allowing to reproduce the problem the same way we hit
> it (anonymized)
> 2) a screen capture in .odt (libreOffice 3.6.0.4 open document) where
> you can see HSQLDB 2.2.9 refuses the final INSERT because there is a
> FK violation on FK_TE_C, which is the expected behavior.
> 3) a log file showing that H2 1.3-168 accept the wrong data, without
> raising the expected FK violation ! (last INSERT of SQL file)
> It looks to us a mean FK bug in H2, what do you think about it ?
> thx and regards,
> Patrice
>