You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Anurag Shekhar (JIRA)" <ji...@apache.org> on 2008/02/23 13:57:19 UTC
[jira] Created: (DERBY-3456) Allow removing not null from collumns
particpating in unique constraint.
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
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3456) Allow removing not null from
collumns particpating in unique constraint.
Posted by "Myrna van Lunteren (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3456?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12767861#action_12767861 ]
Myrna van Lunteren commented on DERBY-3456:
-------------------------------------------
I don't see any patches waiting in this?
Mike committed the last patches on Mar 07 2008 and Mar 08 2008.
The only concern I see left is that apparently there's an issue with the message not reflecting correct behavior in upgrade mode - and apparently there was a suggestion of adding something to the release notes.
But the code for this has gone in and has been released at least with 10.5 - without the issue getting set to fixed or a release note getting added, so no release note was ever provided, and so it seems to me Army's concern has gotten resolved de facto, so now the error returned during soft upgrade is incorrect.
It seems to me this issue should be closed?
> 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.1.3
> Environment: all
> Reporter: 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.
Re: [jira] Updated: (DERBY-3456) Allow removing not null from collumns
particpating in unique constraint.
Posted by Anurag shekhar <An...@Sun.COM>.
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.
Posted by Mike Matrigali <mi...@sbcglobal.net>.
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.
Posted by "Anurag Shekhar (JIRA)" <ji...@apache.org>.
[ 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.
Posted by "Anurag Shekhar (JIRA)" <ji...@apache.org>.
[ 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] Commented: (DERBY-3456) Allow removing not null from
collumns particpating in unique constraint.
Posted by "A B (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3456?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12575212#action_12575212 ]
A B commented on DERBY-3456:
----------------------------
As follow-up to my previous comment I ran various "conglomerate sharing" scenarios with uniqueWhenNotNull constraints. After applying derby-3456v3.diff, I see the following:
1. uniqueWhenNotNull constraint can share a conglomerate with an existing non-unique index, which renders the uniqueWhenNotNull constraint ineffective (per my previous comment).
2. uniqueWhenNotNull constraint can share a conglomerate with an existing unique index. This seems okay from the "sharing" perspective, but if the unique index is dropped, the "replacement" conglomerate that is created must correctly enforce uniqueWhenNotNull. That is not currently the case; instead, the replacement conglomerate ends up being a normal non-unique conglomerate. Ex:
create table t1 (i int, j int not null, k int);
insert into t1 values (1, -1, 1), (2, -2, 4), (4, -4, 16), (3, -3, 9);
create unique index uix on t1(i,j);
insert into t1 values (null, 1, -1);
-- This will share a conglomerate with UIX.
alter table t1 add constraint uc unique(i,j);
-- Should drop shared conglomerate and re-create a new one
-- that is uniqueWhenNotNull. But currently we just end up
-- with a non-unique index where uniqueWhenNotNull is false.
drop index uix;
-- Should be okay.
insert into t1 values (null, 1, -1);
insert into t1 values (null, 1, -1);
-- Should fail due to uniqueWhenNotNull, but currently succeeds.
insert into t1 values (1, -1, 1);
I think the problem here is that the IndexRowGenerator() which is created for a "sharing" conglomerates does not account for the uniqueWhenNotNull attribute. See line ~492 in CreateIndexConstantAction.java.
3. A foreign key constraint can share a conglomerate with a uniqueWhenNotNull constraint. This seems okay from the "sharing" perspective, but if the uniqueWhenNotNull constraint is dropped, Derby should create a "replacement" conglomerate that is a normal, non-unique conglomerate. That is not currently the case, though; instead, the uniqueWhenNotNull conglomerate remains in tact and incorrectly enforces uniqueness where it should not be enforced (similar to DERBY-3299). Ex:
create table t1 (i int, j int not null, k int);
insert into t1 values (1, -1, 1), (2, -2, 4), (4, -4, 16), (3, -3, 9);
alter table t1 add constraint uc unique(i,j);
create table t2 (a int not null, b int not null);
alter table t2 add constraint pkt2 primary key(a,b);
insert into t2 values (1, -1), (2, -2), (4, -4), (3, -3);
-- This foreign key will share a conglomerate with UC, which is uniqueWhenNotNull.
alter table t1 add constraint fkt1 foreign key (i,j) references t2;
-- Should fail due to UC (and it does).
insert into t1(i,j) values (1, -1);
-- Drop UC, the conglomerate for the foreign key should be re-created as non-unique.
-- But this doesn't currently happen.
alter table t1 drop constraint uc;
-- This should now succeed because UC has been dropped, but it still fails, claiming
-- (incorrectly) that FKT1 is a unique constraint.
insert into t1(i,j) values (1, -1);
I think the problem here is that the "drop(...)" and "describeSharedConglomerate(...)" methods of ConglomerateDescriptor.java do not recognize the "uniqueWhenNotNull" attribute, and thus are not able to detect that a new (non-unique) conglomerate is necessary.
As a summary of all three issues, I think it's safe to say that the changes which were made for DERBY-3299 need to be enhanced to account for the new "uniqueWhenNotNull" type of conglomerate, and to do the correct thing when such a conglomerate is in play.
> 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.
Posted by "Anurag Shekhar (JIRA)" <ji...@apache.org>.
[ 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] Commented: (DERBY-3456) Allow removing not null from
collumns particpating in unique constraint.
Posted by "A B (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3456?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12575180#action_12575180 ]
A B commented on DERBY-3456:
----------------------------
Thanks for the patch, Anurag. It seems that with the patch applied, some conglomerate sharing now occurs where it should not, which can lead to incorrect behavior. For example, the following works as expected:
create table t1 (i int, j int not null);
alter table t1 add constraint uc unique (i,j);
insert into t1 values (2, 2);
-- Should fail due to unique constraint on i, j, and it does.
insert into t1 values (2, 2);
But if I create a non-unique index before creating the unique constraint, the unique constraint is no longer enforced:
create table t1 (i int, j int not null);
create index ixnu on t1 (i,j);
-- This new constraint UC will incorrectly share a conglomerate with "IXNU", which is non-unique...
alter table t1 add constraint uc unique (i,j);
insert into t1 values (2, 2);
-- This should fail due to unique constraint on i, j, but it succeeds.
insert into t1 values (2, 2);
ij> select * from t1;
I |J
-----------------------
2 |2
2 |2
2 rows selected
I think this goes back to my Feb 27th comment: namely, I think the criteria for sharing a conglomerate has to be updated to account for the fact that we can now have non-unique conglomerates which enforce uniqueness in the absence of NULLs. In such a case the uniqueWhenNotNull constraint, which is implemented using a non-unique conglomerate, canNOT share with an existing non-unique conglomerate UNLESS the existing conglomerate is itself uniqueWhenNotNull. At least I think that's how it's should work; can you verify?
> 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.
Posted by "Anurag Shekhar (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Updated: (DERBY-3456) Allow removing not null from collumns
particpating in unique constraint.
Posted by "Anurag Shekhar (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Commented: (DERBY-3456) Allow removing not null from
collumns particpating in unique constraint.
Posted by "Anurag Shekhar (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3456?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12576053#action_12576053 ]
Anurag Shekhar commented on DERBY-3456:
---------------------------------------
upgrade and other tests are included in test patch (erby-3456-Tests_v2.diff).
> 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] Assigned: (DERBY-3456) Allow removing not null from collumns
particpating in unique constraint.
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3456?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden reassigned DERBY-3456:
-------------------------------------
Assignee: (was: Anurag Shekhar)
There has been no activity on this issue for over a year, so unassigning. Anurag, please reassign yourself if you want to do further work on this issue. It looks like there was a patch that was close to ready.
> 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.1.3
> Environment: all
> Reporter: 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.
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ 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] Commented: (DERBY-3456) Allow removing not null from
collumns particpating in unique constraint.
Posted by "A B (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3456?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12573017#action_12573017 ]
A B commented on DERBY-3456:
----------------------------
When creating the backing index for a constraint, Derby currently attempts to share conglomerates if there is an existing conglomerate such that:
1. the set of columns and their order in the constraint-backing index is the same
as that of an existing index AND
2. the ordering attributes are the same AND
3. both the already-existing index and the constraint-backing index being created
are non-unique OR the already existing index is unique
If all three of these conditions are true then Derby will use a single physical conglomerate to support the already-existing index and the new constraint-backing index.
So my question is: when unique constraints over nullable columns are introduced, will this criteria (found in CreateIndexConstantAction.java; search for "shareExisting") need to be updated? In particular, I'm wondering if #3 is still good enough, or will additional logic for nullable unique constraints need to be added?
> 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.
Posted by "Anurag Shekhar (JIRA)" <ji...@apache.org>.
[ 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] Commented: (DERBY-3456) Allow removing not null from
collumns particpating in unique constraint.
Posted by "Anurag Shekhar (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3456?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12575740#action_12575740 ]
Anurag Shekhar commented on DERBY-3456:
---------------------------------------
I have created DERBY-3502 to address issues related to shared backing indexed. Please ignore derby-3456v4.diff
> 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] Resolved: (DERBY-3456) Allow removing not null from collumns
particpating in unique constraint.
Posted by "Myrna van Lunteren (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3456?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Myrna van Lunteren resolved DERBY-3456.
---------------------------------------
Resolution: Fixed
Fix Version/s: 10.4.1.3
Assignee: Anurag Shekhar
Actually, looking at the current code, it appears to me that the issue of the message was resolved after all.
(there's a switch happening based on DataDictionary.DD_VERSION_DERBY_10_4 in ModifyColumnNode).
So, this can just be resolved.
I also noted that there *is* no release note for DERBY-3330 or its subtasks...Probably because DERBY-3330 was closed *after* release of 10.4 into which the functionality went, the check for existence of release note was not carried out. But there's no need for a release note on behalf of this subtask.
> 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.1.3
> Environment: all
> Reporter: Anurag Shekhar
> Assignee: Anurag Shekhar
> Fix For: 10.4.1.3
>
> 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] Issue Comment Edited: (DERBY-3456) Allow removing not null
from collumns particpating in unique constraint.
Posted by "A B (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3456?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12575410#action_12575410 ]
army edited comment on DERBY-3456 at 3/5/08 8:44 AM:
----------------------------------------------------
> Is it ok if I create a new issue for 2 and 3 ? They are actually independent
> issue caused by DERBY-3330 and creating a separate issues for them
> will allow this (3456) issue to remain unblocked.
Sure, that's fine with me :) In fact, if you'd like to put all 3 of the "shared backing index" issues I mentioned above into one new Jira issue, that seems reasonable (and perhaps "cleaner" from an incremental development approach) to me....
was (Author: army):
> Is it ok if I create a new issue for 2 and 3 ? They are actually independent
> issue caused by DERBY-3330 and creating a separate issues for them
> will allow this (3456) issue to remain unblocked.
Sure, that's fine with me :)
> 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.
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ 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] Commented: (DERBY-3456) Allow removing not null from
collumns particpating in unique constraint.
Posted by "Anurag Shekhar (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3456?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12576292#action_12576292 ]
Anurag Shekhar commented on DERBY-3456:
---------------------------------------
Thanks Mike for committing it.
You have missed one file (UniqueConstraintSetNullTest.java) but there
entry is already there in lang/_Suite.java and build is failing.
> 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.
Posted by "Anurag Shekhar (JIRA)" <ji...@apache.org>.
[ 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] Commented: (DERBY-3456) Allow removing not null from
collumns particpating in unique constraint.
Posted by "Dyre Tjeldvoll (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3456?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12575806#action_12575806 ]
Dyre Tjeldvoll commented on DERBY-3456:
---------------------------------------
As RM I would like to see this committed, so I took a brief look. The patch looks good to me, but I have not followed this issue closely so confirmation from an expert in this area would be appreciated.
> 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] Issue Comment Edited: (DERBY-3456) Allow removing not null
from collumns particpating in unique constraint.
Posted by "Anurag Shekhar (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3456?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12575742#action_12575742 ]
anurag edited comment on DERBY-3456 at 3/6/08 7:28 AM:
---------------------------------------------------------------
Reattaching derby-3456v3.diff as derby-3456v5.diff to avoid confusion.
was (Author: anurag):
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] Issue Comment Edited: (DERBY-3456) Allow removing not null
from collumns particpating in unique constraint.
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3456?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12575193#action_12575193 ]
mikem edited comment on DERBY-3456 at 3/4/08 3:46 PM:
---------------------------------------------------------------
how hard would it be to separate out the tests that apply to DERBY-3330 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.
was (Author: mikem):
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.
Re: [jira] Commented: (DERBY-3456) Allow removing not null from collumns
particpating in unique constraint.
Posted by Mike Matrigali <mi...@sbcglobal.net>.
before coding, making patch could you make sure to write up exactly
what you are planning on doing in the new issue. Want to make
sure there are no upgrade/backward compatibility issues being
introduced. Assuming there is no change in error behavior in
soft upgrade mode, what is the exact proposed difference in
hard upgrade mode?
Anurag shekhar wrote:
> I will create a separate issue for it. Even though the amount
> of code changes will be very small it will end breaking several
> tests (relying on either state or message).
> anurag
>
> Mike Matrigali wrote:
>> I have not looked at code so not sure how hard to determine which
>> error to throw, but seems like you should add a new error message for
>> the new code to use, and soft upgrade can continue to use
>> the old message. This is assuming there is no SQL standard for this
>> exact error number.
>>
>>
>> Anurag Shekhar (JIRA) wrote:
>>> [
>>> https://issues.apache.org/jira/browse/DERBY-3456?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12576311#action_12576311
>>> ]
>>> Anurag Shekhar commented on DERBY-3456:
>>> ---------------------------------------
>>>
>>> This error message is thrown while setting a unique constraint
>>> column to null able in soft upgrade mode.
>>> In one of the two instances (soft upgrade or regular run) message
>>> will be wrong. Changed message will give a wrong info in soft upgrade
>>> mode claiming that user was trying to modify a column from primary key
>>> when he is actually modifying the constraint.
>>>
>>> Is it ok to return wrong info in soft upgrade mode ? Probably we can
>>> mention it in release note that in case of soft upgrade mode a wrong
>>> info will be thrown.
>>>
>>>> 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.
>>>
>>
>
>
Re: [jira] Commented: (DERBY-3456) Allow removing not null from
collumns particpating in unique constraint.
Posted by Anurag shekhar <An...@Sun.COM>.
I will create a separate issue for it. Even though the amount
of code changes will be very small it will end breaking several
tests (relying on either state or message).
anurag
Mike Matrigali wrote:
> I have not looked at code so not sure how hard to determine which
> error to throw, but seems like you should add a new error message for
> the new code to use, and soft upgrade can continue to use
> the old message. This is assuming there is no SQL standard for this
> exact error number.
>
>
> Anurag Shekhar (JIRA) wrote:
>> [
>> https://issues.apache.org/jira/browse/DERBY-3456?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12576311#action_12576311
>> ]
>> Anurag Shekhar commented on DERBY-3456:
>> ---------------------------------------
>>
>> This error message is thrown while setting a unique constraint
>> column to null able in soft upgrade mode.
>> In one of the two instances (soft upgrade or regular run) message
>> will be wrong. Changed message will give a wrong info in soft upgrade
>> mode claiming that user was trying to modify a column from primary key
>> when he is actually modifying the constraint.
>>
>> Is it ok to return wrong info in soft upgrade mode ? Probably we can
>> mention it in release note that in case of soft upgrade mode a wrong
>> info will be thrown.
>>
>>> 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.
>>
>
Re: [jira] Commented: (DERBY-3456) Allow removing not null from collumns
particpating in unique constraint.
Posted by Mike Matrigali <mi...@sbcglobal.net>.
I have not looked at code so not sure how hard to determine which error
to throw, but seems like you should add a new error message for the new
code to use, and soft upgrade can continue to use
the old message. This is assuming there is no SQL standard for this
exact error number.
Anurag Shekhar (JIRA) wrote:
> [ https://issues.apache.org/jira/browse/DERBY-3456?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12576311#action_12576311 ]
>
> Anurag Shekhar commented on DERBY-3456:
> ---------------------------------------
>
> This error message is thrown while setting a unique
> constraint column to null able in soft upgrade mode.
>
> In one of the two instances (soft upgrade or regular run) message
> will be wrong. Changed message will give a wrong info in soft upgrade
> mode claiming that user was trying to modify a column from primary key
> when he is actually modifying the constraint.
>
> Is it ok to return wrong info in soft upgrade mode ? Probably we can
> mention it in release note that in case of soft upgrade mode a wrong
> info will be thrown.
>
>> 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.
>
[jira] Commented: (DERBY-3456) Allow removing not null from
collumns particpating in unique constraint.
Posted by "Anurag Shekhar (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3456?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12576311#action_12576311 ]
Anurag Shekhar commented on DERBY-3456:
---------------------------------------
This error message is thrown while setting a unique
constraint column to null able in soft upgrade mode.
In one of the two instances (soft upgrade or regular run) message
will be wrong. Changed message will give a wrong info in soft upgrade
mode claiming that user was trying to modify a column from primary key
when he is actually modifying the constraint.
Is it ok to return wrong info in soft upgrade mode ? Probably we can
mention it in release note that in case of soft upgrade mode a wrong
info will be thrown.
> 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] Commented: (DERBY-3456) Allow removing not null from
collumns particpating in unique constraint.
Posted by "A B (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3456?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12576308#action_12576308 ]
A B commented on DERBY-3456:
----------------------------
Minor observation: the error message 42Z20 currently says:
ERROR 42Z20: Column 'K' cannot be made nullable. It is part of a primary key or unique constraint,
which cannot have any nullable columns.
Should this message be updated to exclude "unique constraint", since a unique constraint can now have a nullable columns?
> 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] Commented: (DERBY-3456) Allow removing not null from
collumns particpating in unique constraint.
Posted by "Anurag Shekhar (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3456?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12575325#action_12575325 ]
Anurag Shekhar commented on DERBY-3456:
---------------------------------------
thanks Army for looking at the patch testing the backing indexes.
I have fixed the 1st issue and will be uploading the patch shortly.
Is it ok if I create a new issue for 2 and 3 ? They are actually
independent issue caused by DERBY-3330 and creating a separate
issues for them will allow this (3456) issue to remain unblocked.
> 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] Commented: (DERBY-3456) Allow removing not null from
collumns particpating in unique constraint.
Posted by "Anurag Shekhar (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3456?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12573031#action_12573031 ]
Anurag Shekhar commented on DERBY-3456:
---------------------------------------
After applying 3330. A unique constraint won't be using a unique index
but will use a non unique backing index with special case of
uniqueWhenNotNull. So Case 3 needs to modified.
Thanks for pointing out.
There is one more minor change in CreateIndexConstantAction required (to
fetch new attribute uniqueWhenNotNull) while creating a replacement index.
I will make these two changes in a separate 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: 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.
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ 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] Commented: (DERBY-3456) Allow removing not null from
collumns particpating in unique constraint.
Posted by "Anurag Shekhar (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3456?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12572243#action_12572243 ]
Anurag Shekhar commented on DERBY-3456:
---------------------------------------
This patch should be applied after applying patch from derby-3330.
> 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.
[jira] Commented: (DERBY-3456) Allow removing not null from
collumns particpating in unique constraint.
Posted by "A B (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3456?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12575410#action_12575410 ]
A B commented on DERBY-3456:
----------------------------
> Is it ok if I create a new issue for 2 and 3 ? They are actually independent
> issue caused by DERBY-3330 and creating a separate issues for them
> will allow this (3456) issue to remain unblocked.
Sure, that's fine with me :)
> 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.
Posted by "Anurag Shekhar (JIRA)" <ji...@apache.org>.
[ 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.
Posted by "Anurag Shekhar (JIRA)" <ji...@apache.org>.
[ 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.