[jira] Updated: (DERBY-3456) Allow removing not null from collumns particpating in unique constraint.
[ https://issues.apache.org/jira/browse/DERBY-3456?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Mike Matrigali updated DERBY-3456: -- Description: Enable alter table to change a column that participates in a unique constraint to allow nulls. I have committed the derby-3456-Tests_v2.diff and derby-3456v5.diff patches. I believe these are the only outstanding patches for this issue. I did a review of the changes, and ran complete set of tests against ibm15 jvm. Thanks to army for providing the initial reviews that led to the complete patch. > Allow removing not null from collumns particpating in unique constraint. > > > Key: DERBY-3456 > URL: https://issues.apache.org/jira/browse/DERBY-3456 > Project: Derby > Issue Type: Sub-task > Components: SQL, Store >Affects Versions: 10.4.0.0 > Environment: all >Reporter: Anurag Shekhar >Assignee: Anurag Shekhar > Attachments: altertable.diff, derby-3456-Tests.diff, > derby-3456-Tests_v2.diff, derby-3456v1.diff, derby-3456v2.diff, > derby-3456v3.diff, derby-3456v4.diff, derby-3456v5.diff, setnulltest.diff, > upgradetests.diff, upgradetests_v2.diff > > > Enable alter table to change a column that participates in a unique > constraint to allow nulls. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3456) Allow removing not null from collumns particpating in unique constraint.
[ https://issues.apache.org/jira/browse/DERBY-3456?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Mike Matrigali updated DERBY-3456: -- i am looking at committing these patches. Are the upgrade patches still valid, or was all of that moved to the already committed work in DERBY-3330? Right now I am looking at derby-3456v5.diff and the v2 test patch, but not the upgrade patches. > Allow removing not null from collumns particpating in unique constraint. > > > Key: DERBY-3456 > URL: https://issues.apache.org/jira/browse/DERBY-3456 > Project: Derby > Issue Type: Sub-task > Components: SQL, Store >Affects Versions: 10.4.0.0 > Environment: all >Reporter: Anurag Shekhar >Assignee: Anurag Shekhar > Attachments: altertable.diff, derby-3456-Tests.diff, > derby-3456-Tests_v2.diff, derby-3456v1.diff, derby-3456v2.diff, > derby-3456v3.diff, derby-3456v4.diff, derby-3456v5.diff, setnulltest.diff, > upgradetests.diff, upgradetests_v2.diff > > -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3456) Allow removing not null from collumns particpating in unique constraint.
[ https://issues.apache.org/jira/browse/DERBY-3456?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Anurag Shekhar updated DERBY-3456: -- Attachment: derby-3456-Tests_v2.diff derby-3456-Tests_v2.diff Fixed conflicts with latest svn version. > Allow removing not null from collumns particpating in unique constraint. > > > Key: DERBY-3456 > URL: https://issues.apache.org/jira/browse/DERBY-3456 > Project: Derby > Issue Type: Sub-task > Components: SQL, Store >Affects Versions: 10.4.0.0 > Environment: all >Reporter: Anurag Shekhar >Assignee: Anurag Shekhar > Attachments: altertable.diff, derby-3456-Tests.diff, > derby-3456-Tests_v2.diff, derby-3456v1.diff, derby-3456v2.diff, > derby-3456v3.diff, derby-3456v4.diff, derby-3456v5.diff, setnulltest.diff, > upgradetests.diff, upgradetests_v2.diff > > -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3456) Allow removing not null from collumns particpating in unique constraint.
[ https://issues.apache.org/jira/browse/DERBY-3456?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Anurag Shekhar updated DERBY-3456: -- Attachment: derby-3456v5.diff Reattaching derby-3456v5.diff to avoid confusion. > Allow removing not null from collumns particpating in unique constraint. > > > Key: DERBY-3456 > URL: https://issues.apache.org/jira/browse/DERBY-3456 > Project: Derby > Issue Type: Sub-task > Components: SQL, Store >Affects Versions: 10.4.0.0 > Environment: all >Reporter: Anurag Shekhar >Assignee: Anurag Shekhar > Attachments: altertable.diff, derby-3456-Tests.diff, > derby-3456v1.diff, derby-3456v2.diff, derby-3456v3.diff, derby-3456v4.diff, > derby-3456v5.diff, setnulltest.diff, upgradetests.diff, upgradetests_v2.diff > > -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3456) Allow removing not null from collumns particpating in unique constraint.
[ https://issues.apache.org/jira/browse/DERBY-3456?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Anurag Shekhar updated DERBY-3456: -- Attachment: derby-3456v4.diff This patch fixes issues related to unique constraint using existing non unique index as backing index (see Army's comments issues 1). I have updated lterTableConstantAction.java to check for uniqueWithDuplicateNulls while deciding whether to share existing index or not. In case there is a uniqueWithDuplicateNulls index it can be used by a non unique index but not vice-versa. I am investigating other two issues. > Allow removing not null from collumns particpating in unique constraint. > > > Key: DERBY-3456 > URL: https://issues.apache.org/jira/browse/DERBY-3456 > Project: Derby > Issue Type: Sub-task > Components: SQL, Store >Affects Versions: 10.4.0.0 > Environment: all >Reporter: Anurag Shekhar >Assignee: Anurag Shekhar > Attachments: altertable.diff, derby-3456-Tests.diff, > derby-3456v1.diff, derby-3456v2.diff, derby-3456v3.diff, derby-3456v4.diff, > setnulltest.diff, upgradetests.diff, upgradetests_v2.diff > > -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3456) Allow removing not null from collumns particpating in unique constraint.
[ https://issues.apache.org/jira/browse/DERBY-3456?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Mike Matrigali updated DERBY-3456: -- how hard would it be to separate out the tests that apply to DERBY-3300 and submit that patch there. I would like to get as complete a set of tests submitted for that change before working on applying patches for DERBY-3456 code changes. > Allow removing not null from collumns particpating in unique constraint. > > > Key: DERBY-3456 > URL: https://issues.apache.org/jira/browse/DERBY-3456 > Project: Derby > Issue Type: Sub-task > Components: SQL, Store >Affects Versions: 10.4.0.0 > Environment: all >Reporter: Anurag Shekhar >Assignee: Anurag Shekhar > Attachments: altertable.diff, derby-3456-Tests.diff, > derby-3456v1.diff, derby-3456v2.diff, derby-3456v3.diff, setnulltest.diff, > upgradetests.diff, upgradetests_v2.diff > > -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3456) Allow removing not null from collumns particpating in unique constraint.
[ https://issues.apache.org/jira/browse/DERBY-3456?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Anurag Shekhar updated DERBY-3456: -- Attachment: derby-3456-Tests.diff derby-3456v3.diff Description of derby-3456v3.diff I have regenerated the patch with latest trunk. In my last patch (v2) I had made a mistake, I was skipping creating new index if the existing was unique index, while setting the key part to null able. It should be other ways. I have fixed that in this patch. Description of derby-3456-Tests.diff This has Test Cases for 1. Soft and Hard upgrade 2. Index tests (created while running older version and soft upgrade and used during other phases). 3. New test case to test setting a column participating in unique constraint to null. 4. Updated lang/altertable.sql and altertable.out for setting column participating in unique constraint to null. (Modified to expect success) I haven't finished running tests with these two patch. > Allow removing not null from collumns particpating in unique constraint. > > > Key: DERBY-3456 > URL: https://issues.apache.org/jira/browse/DERBY-3456 > Project: Derby > Issue Type: Sub-task > Components: SQL, Store >Affects Versions: 10.4.0.0 > Environment: all >Reporter: Anurag Shekhar >Assignee: Anurag Shekhar > Attachments: altertable.diff, derby-3456-Tests.diff, > derby-3456v1.diff, derby-3456v2.diff, derby-3456v3.diff, setnulltest.diff, > upgradetests.diff, upgradetests_v2.diff > > -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3456) Allow removing not null from collumns particpating in unique constraint.
[ https://issues.apache.org/jira/browse/DERBY-3456?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Anurag Shekhar updated DERBY-3456: -- Attachment: derby-3456v2.diff derby-3456v2.diff updated to work with latest patch for DERBY-3330 (derby-3330v13.diff) I will regenerate the test case patches after 3330 is checked in. > Allow removing not null from collumns particpating in unique constraint. > > > Key: DERBY-3456 > URL: https://issues.apache.org/jira/browse/DERBY-3456 > Project: Derby > Issue Type: Sub-task > Components: SQL, Store >Affects Versions: 10.4.0.0 > Environment: all >Reporter: Anurag Shekhar >Assignee: Anurag Shekhar > Attachments: altertable.diff, derby-3456v1.diff, derby-3456v2.diff, > setnulltest.diff, upgradetests.diff, upgradetests_v2.diff > > -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3456) Allow removing not null from collumns particpating in unique constraint.
[ https://issues.apache.org/jira/browse/DERBY-3456?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Anurag Shekhar updated DERBY-3456: -- Attachment: altertable.diff setnulltest.diff I have run derbyall harness and upgrade tests with derby-3330v11.diff. There is one failure in altertable.sql test (it expects setting a column null able should fail). I am attaching altertable.diff to fix this failure. I have also written another test to test setting nulls to a unique constraint after creation the constraint. The same test also tests setting the column back to not null. setnulltest.diff as this new test. I will be regenerating all the test cases related patch after 3330 is checked in as there are some conflicts between the two. main patch dery-3456v1 doesn't have any conflicts so I will just test it again after derby-3330 is checked, > Allow removing not null from collumns particpating in unique constraint. > > > Key: DERBY-3456 > URL: https://issues.apache.org/jira/browse/DERBY-3456 > Project: Derby > Issue Type: Sub-task > Components: SQL, Store >Affects Versions: 10.4.0.0 > Environment: all >Reporter: Anurag Shekhar >Assignee: Anurag Shekhar > Attachments: altertable.diff, derby-3456v1.diff, setnulltest.diff, > upgradetests.diff, upgradetests_v2.diff > > -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3456) Allow removing not null from collumns particpating in unique constraint.
[ https://issues.apache.org/jira/browse/DERBY-3456?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Anurag Shekhar updated DERBY-3456: -- Attachment: upgradetests_v2.diff This patch contains additional tests to test indexes created at various phases of upgrades. It creates a set of indexes (unique and non unique with null able and not null columns) and tests in others phases. > Allow removing not null from collumns particpating in unique constraint. > > > Key: DERBY-3456 > URL: https://issues.apache.org/jira/browse/DERBY-3456 > Project: Derby > Issue Type: Sub-task > Components: SQL, Store >Affects Versions: 10.4.0.0 > Environment: all >Reporter: Anurag Shekhar >Assignee: Anurag Shekhar > Attachments: derby-3456v1.diff, upgradetests.diff, > upgradetests_v2.diff > > -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
Re: [jira] Updated: (DERBY-3456) Allow removing not null from collumns particpating in unique constraint.
Thanks Mike for looking at the issue. I will update the upgrade testcases with the tests you have suggested. I am not sure what you mean by retry code. Do you mean the situation where rescanning of BTree is required to identify the slot to insert ? I was testing it with 2 ij windows. I have also written a test case for it and attached to DERBY-3473 (https://issues.apache.org/jira/browse/DERBY-3473). anurag Mike Matrigali wrote: could you add the following tests: o) just cover all the create index cases to make sure all the format id btree code gets exercised. Some cases may not be affected by your code but would be good to verify. Also by having all these cases I think it will cover all the stored format paths for the new format stuff in the btree container. 1) create and use unique index (nullable and non-nullable), non-unique index (nullable and non-nullable), non-null constraint in 10.3. 2) create and use unique index (nullable and non-nullable), non-unique index (nullable and non-nullable), non-null constraint in 10.4 soft upgrade. 3) create and use unique index (nullable and non-nullable), non-unique index (nullable and non-nullable), unique constraint (nullable and non-nullable) in 10.4 hard upgrade, verify their null handling is as expected. 4) In 10.4 soft upgrade, access and use those indexes created in step 1, verify their null handling is as expected. 5) In 10.4 hard upgrade, access and use those indexes created in step 1, verify their null handling is as expected. 6) In 10.4 hard upgrade, access and use those indexes created in step 2, verify their null handling is as expected. hopefully one routine can be reused for all of the above and just add calls for each case, but with different index names for each case. By verify expected behavior I mean: unique nullable index - only one null allowed unique non-nullable index - no null allowed non-unique index (nullable) - multiple nulls allowed non-unique index (non-nullable) - no nulls allowed non-nullable constraint - no nulls allowed nullable constraint - multiple nulls allowed I don't know if the upgrade framework supports this, but you should at least by hand test at least once that you can access all the above after you shutdown the database and then access it again. Some of the upgrade code only will get exercised if the index is accessed for the first time, as the contain information is otherwise cached. It is important to test that the new formatid stuff is being read from DISK correctly. Also how have you been testing your retry code? Anurag Shekhar (JIRA) wrote: [ https://issues.apache.org/jira/browse/DERBY-3456?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Anurag Shekhar updated DERBY-3456: -- Attachment: upgradetests.diff This patch has upgrade tests for unique constraint. under soft upgrade 1. Setting columns from unique constraint to null able - should fail 2. creation of unique constraint over nullable columns - should fail 3. creation of unique constraint over not null column - should pass under post soft upgrade test unique constraint created in soft upgrade phase. under hard upgrade 1. Tests unique constraint creation prior to upgrade 2. Tests unique constraint over null able columns - should pass 3. Test setting column from unique constraint to null able - should pass Allow removing not null from collumns particpating in unique constraint. Key: DERBY-3456 URL: https://issues.apache.org/jira/browse/DERBY-3456 Project: Derby Issue Type: Sub-task Components: SQL, Store Affects Versions: 10.4.0.0 Environment: all Reporter: Anurag Shekhar Assignee: Anurag Shekhar Attachments: derby-3456v1.diff, upgradetests.diff
Re: [jira] Updated: (DERBY-3456) Allow removing not null from collumns particpating in unique constraint.
could you add the following tests: o) just cover all the create index cases to make sure all the format id btree code gets exercised. Some cases may not be affected by your code but would be good to verify. Also by having all these cases I think it will cover all the stored format paths for the new format stuff in the btree container. 1) create and use unique index (nullable and non-nullable), non-unique index (nullable and non-nullable), non-null constraint in 10.3. 2) create and use unique index (nullable and non-nullable), non-unique index (nullable and non-nullable), non-null constraint in 10.4 soft upgrade. 3) create and use unique index (nullable and non-nullable), non-unique index (nullable and non-nullable), unique constraint (nullable and non-nullable) in 10.4 hard upgrade, verify their null handling is as expected. 4) In 10.4 soft upgrade, access and use those indexes created in step 1, verify their null handling is as expected. 5) In 10.4 hard upgrade, access and use those indexes created in step 1, verify their null handling is as expected. 6) In 10.4 hard upgrade, access and use those indexes created in step 2, verify their null handling is as expected. hopefully one routine can be reused for all of the above and just add calls for each case, but with different index names for each case. By verify expected behavior I mean: unique nullable index - only one null allowed unique non-nullable index - no null allowed non-unique index (nullable) - multiple nulls allowed non-unique index (non-nullable) - no nulls allowed non-nullable constraint - no nulls allowed nullable constraint - multiple nulls allowed I don't know if the upgrade framework supports this, but you should at least by hand test at least once that you can access all the above after you shutdown the database and then access it again. Some of the upgrade code only will get exercised if the index is accessed for the first time, as the contain information is otherwise cached. It is important to test that the new formatid stuff is being read from DISK correctly. Also how have you been testing your retry code? Anurag Shekhar (JIRA) wrote: [ https://issues.apache.org/jira/browse/DERBY-3456?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Anurag Shekhar updated DERBY-3456: -- Attachment: upgradetests.diff This patch has upgrade tests for unique constraint. under soft upgrade 1. Setting columns from unique constraint to null able - should fail 2. creation of unique constraint over nullable columns - should fail 3. creation of unique constraint over not null column - should pass under post soft upgrade test unique constraint created in soft upgrade phase. under hard upgrade 1. Tests unique constraint creation prior to upgrade 2. Tests unique constraint over null able columns - should pass 3. Test setting column from unique constraint to null able - should pass Allow removing not null from collumns particpating in unique constraint. Key: DERBY-3456 URL: https://issues.apache.org/jira/browse/DERBY-3456 Project: Derby Issue Type: Sub-task Components: SQL, Store Affects Versions: 10.4.0.0 Environment: all Reporter: Anurag Shekhar Assignee: Anurag Shekhar Attachments: derby-3456v1.diff, upgradetests.diff
[jira] Updated: (DERBY-3456) Allow removing not null from collumns particpating in unique constraint.
[ https://issues.apache.org/jira/browse/DERBY-3456?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Anurag Shekhar updated DERBY-3456: -- Attachment: upgradetests.diff This patch has upgrade tests for unique constraint. under soft upgrade 1. Setting columns from unique constraint to null able - should fail 2. creation of unique constraint over nullable columns - should fail 3. creation of unique constraint over not null column - should pass under post soft upgrade test unique constraint created in soft upgrade phase. under hard upgrade 1. Tests unique constraint creation prior to upgrade 2. Tests unique constraint over null able columns - should pass 3. Test setting column from unique constraint to null able - should pass > Allow removing not null from collumns particpating in unique constraint. > > > Key: DERBY-3456 > URL: https://issues.apache.org/jira/browse/DERBY-3456 > Project: Derby > Issue Type: Sub-task > Components: SQL, Store >Affects Versions: 10.4.0.0 > Environment: all >Reporter: Anurag Shekhar >Assignee: Anurag Shekhar > Attachments: derby-3456v1.diff, upgradetests.diff > > -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3456) Allow removing not null from collumns particpating in unique constraint.
[ https://issues.apache.org/jira/browse/DERBY-3456?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Anurag Shekhar updated DERBY-3456: -- Attachment: derby-3456v1.diff derby-3456v1.diff enables setting columns (if they are part of unique constraint) null able . If the backing index for the unique constraints are unique indexes (ie created before hard upgrade) it drops the index and recreates them with index type as duplicate and unique when not null. Setting column null able works only after hard upgrade. If the data base is running in soft upgrade mode setting the column null able will continue to throw exception. modified files java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java Check for Unique Constraint is now optional (only if the data dictionary version is not the latest one). java/engine/org/apache/derby/impl/sql/execute/DDLSingleTableConstantAction.java added a new method () to drop backing index and recreating with type set to non unique and unique when not null. Added code, while setting column to null able, to check if the column is participating in a unique constraint and if the unique constraint was created before hard upgrade (index type will be unique in this case) use the method in DDLSingleTableConstantAction to recreate the backing index. > Allow removing not null from collumns particpating in unique constraint. > > > Key: DERBY-3456 > URL: https://issues.apache.org/jira/browse/DERBY-3456 > Project: Derby > Issue Type: Sub-task > Components: SQL, Store >Affects Versions: 10.4.0.0 > Environment: all >Reporter: Anurag Shekhar >Assignee: Anurag Shekhar > Attachments: derby-3456v1.diff > > -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
