Derby
  1. Derby
  2. DERBY-396

Support for ALTER STATEMENT to DROP , MODIFY, RENAME a COLUMN

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 10.3.1.4
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      LINUX

      Description

      Alter Statement should support dropping a column, modifying a column to a different data type , rename a column.

      1. dropColumn_1.diff
        16 kB
        Bryan Pendleton

        Issue Links

          Activity

          Hide
          Rick Hillegas added a comment -

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

          ANSI supplies syntax for dropping a column:

          ALTER TABLE tableName DROP columnName

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

          Derby already supports some datatype modification. It would be interesting to understand the use cases for additional datatype mods.

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

          I can't find ANSI syntax for renaming a column nor can I find this feature in DB2. Oracle and Postgres agree on the following syntax:

          ALTER TABLE tableName RENAME COLUMN oldColumnName TO newColumnName

          MySQL supports the following less intuitive syntax:

          ALTER TABLE tableName CHANGE oldColumnName newColumnName oldDatatype

          Is the Oracle/Postgres agreement a good enough standard?

          Show
          Rick Hillegas added a comment - --------------------------------- ANSI supplies syntax for dropping a column: ALTER TABLE tableName DROP columnName ---------------------------------------------- Derby already supports some datatype modification. It would be interesting to understand the use cases for additional datatype mods. ---------------------------------------------- I can't find ANSI syntax for renaming a column nor can I find this feature in DB2. Oracle and Postgres agree on the following syntax: ALTER TABLE tableName RENAME COLUMN oldColumnName TO newColumnName MySQL supports the following less intuitive syntax: ALTER TABLE tableName CHANGE oldColumnName newColumnName oldDatatype Is the Oracle/Postgres agreement a good enough standard?
          Hide
          Kumar Matcha added a comment -

          Derby is supposed to be SQL-92E compliance and supports key features in the SQL-99 standards. If you take a look at the SQL92 Standard for ALTER TABLE STATEMENT :

          <alter table statement> ::=
          ALTER TABLE <table name> <alter table action>

          <alter table action> ::=
          <add column definition>

          <alter column definition>
          <drop column definition>
          <add table constraint definition>
          <drop table constraint definition>

          So its supposed to provide support for dropping a column. Although RENAME COLUMN is vendor specific, Oracle/Postgres aggrement is a good enough standard.

          Show
          Kumar Matcha added a comment - Derby is supposed to be SQL-92E compliance and supports key features in the SQL-99 standards. If you take a look at the SQL92 Standard for ALTER TABLE STATEMENT : <alter table statement> ::= ALTER TABLE <table name> <alter table action> <alter table action> ::= <add column definition> <alter column definition> <drop column definition> <add table constraint definition> <drop table constraint definition> So its supposed to provide support for dropping a column. Although RENAME COLUMN is vendor specific, Oracle/Postgres aggrement is a good enough standard.
          Hide
          Scott MacDonald added a comment -

          I am presently working on the Rename and Drop column issues.

          As discussed previously on this Comment page, should the Oracle and Postgres syntax for renaming and dropping columns be followed?

          The syntax is:

          ALTER TABLE <table_name> DROP COLUMN <table_name>

          ALTER TABLE <table_name> RENAME COLUMN <old_name> TO <new_name>

          I have also been browsing documentation for Oracle, Postgres, and DB2 and doing some simple testing in DB2 regarding contstraints on renaming and dropping columns.

          I have found that any column can be dropped, so long as it does not affect the referential integrity of the table or any other tables in the database (ie. as long as the column is not a primary or foreign key).

          I have also found that any column can be renamed so long as the new column name is not already the name of another column in the table. The new column name must also be either an ordinary or delimited identifier.

          If anybody has any comments on these topics before I begin implementation, I would like to hear them.

          Thanks,
          Scott

          Show
          Scott MacDonald added a comment - I am presently working on the Rename and Drop column issues. As discussed previously on this Comment page, should the Oracle and Postgres syntax for renaming and dropping columns be followed? The syntax is: ALTER TABLE <table_name> DROP COLUMN <table_name> ALTER TABLE <table_name> RENAME COLUMN <old_name> TO <new_name> I have also been browsing documentation for Oracle, Postgres, and DB2 and doing some simple testing in DB2 regarding contstraints on renaming and dropping columns. I have found that any column can be dropped, so long as it does not affect the referential integrity of the table or any other tables in the database (ie. as long as the column is not a primary or foreign key). I have also found that any column can be renamed so long as the new column name is not already the name of another column in the table. The new column name must also be either an ordinary or delimited identifier. If anybody has any comments on these topics before I begin implementation, I would like to hear them. Thanks, Scott
          Hide
          Satheesh Bandaram added a comment -

          SQL standard specifies DROP COLUMN syntax as:

          ALTER TABLE <table name> DROP [COLUMN ]<column name><drop behavior>

          <drop behavior>::= CASCADE | RESTRICT

          You have to define if you are implementing CASCADE or RESTRICT behavior. Restrict would be easier to implement.

          About RENAME COLUMN, it is not defined in the SQL standard. Derby typically allows SQL conforming syntax, so not sure about this one...

          Show
          Satheesh Bandaram added a comment - SQL standard specifies DROP COLUMN syntax as: ALTER TABLE <table name> DROP [COLUMN ] <column name><drop behavior> <drop behavior>::= CASCADE | RESTRICT You have to define if you are implementing CASCADE or RESTRICT behavior. Restrict would be easier to implement. About RENAME COLUMN, it is not defined in the SQL standard. Derby typically allows SQL conforming syntax, so not sure about this one...
          Hide
          Rick Hillegas added a comment -

          I think that the Oracle/Postgres RENAME syntax is straightforward and good enough. Its similarity to Derby's RENAME TABLE syntax is also inviting. MySQL's syntax is, frankly, odd. ANSI and DB2 are silent on the issue. So I say, go with the inviting, straightforward syntax already embraced by two big players.

          Show
          Rick Hillegas added a comment - I think that the Oracle/Postgres RENAME syntax is straightforward and good enough. Its similarity to Derby's RENAME TABLE syntax is also inviting. MySQL's syntax is, frankly, odd. ANSI and DB2 are silent on the issue. So I say, go with the inviting, straightforward syntax already embraced by two big players.
          Hide
          Rick Hillegas added a comment -

          Enhancement request 396 tracks various ALTER TABLE improvements, including column-dropping.

          Show
          Rick Hillegas added a comment - Enhancement request 396 tracks various ALTER TABLE improvements, including column-dropping.
          Hide
          Neil Weber added a comment -

          What's also odd is that the DatabaseMetaData for Derby returns true for supportsAlterTableWithDropColumn(). Since Derby doesn't support dropping columns, the meta data should be false.

          Show
          Neil Weber added a comment - What's also odd is that the DatabaseMetaData for Derby returns true for supportsAlterTableWithDropColumn(). Since Derby doesn't support dropping columns, the meta data should be false.
          Hide
          Rick Hillegas added a comment -

          Downgrading priority to major.

          Show
          Rick Hillegas added a comment - Downgrading priority to major.
          Hide
          Dheeraj Dhiman added a comment -

          Hello Scott MacDonald !!
          I m dheeraj !
          I m switch from sql server to apache derby for my project !!
          You say We can rename or drop a column from table !!
          But i just try this & exception occur !!
          My case is ::
          I just create a table .

          create table simple( name varchar(100), rollno bigint , fullName varchar(100));
          table create after excuting this statement .

          but now i want to change name of column rollno !!

          I try ur suggetion which is given by you !!

          you say ::

          alter table simple rename column rollno to serialno ;
          After excuting this query error occur ;
          ERROR 42X01: Syntax error: Encountered "RENAME" at line 1, column 17.

          && i m also try another thing if we want drop a column !!
          alter table simple drop column name ;
          ERROR 42X01: Syntax error: Encountered "drop" at line 1, column 17

          SWo now how can i drop or rename a column in apache derby !!
          The table simple table have no primary key & no other forignkey !!
          so how can i do this !!
          I have mail accoutn on your apache derby jira log .
          with name dheeraj.dhiman
          or you can mail me at my email id
          dheeraj.dhiman@gmail.com

          I wanna answer of this problem as early as possible !!
          Thanks !!

          Show
          Dheeraj Dhiman added a comment - Hello Scott MacDonald !! I m dheeraj ! I m switch from sql server to apache derby for my project !! You say We can rename or drop a column from table !! But i just try this & exception occur !! My case is :: I just create a table . create table simple( name varchar(100), rollno bigint , fullName varchar(100)); table create after excuting this statement . but now i want to change name of column rollno !! I try ur suggetion which is given by you !! you say :: alter table simple rename column rollno to serialno ; After excuting this query error occur ; ERROR 42X01: Syntax error: Encountered "RENAME" at line 1, column 17. && i m also try another thing if we want drop a column !! alter table simple drop column name ; ERROR 42X01: Syntax error: Encountered "drop" at line 1, column 17 SWo now how can i drop or rename a column in apache derby !! The table simple table have no primary key & no other forignkey !! so how can i do this !! I have mail accoutn on your apache derby jira log . with name dheeraj.dhiman or you can mail me at my email id dheeraj.dhiman@gmail.com I wanna answer of this problem as early as possible !! Thanks !!
          Hide
          Wade Chandler added a comment -

          Hmmm. I think what Scott said was he is working on it and no where did I see where he said it works. He mentions something working in another server. Did you see him write something else some where besides this issue?

          Show
          Wade Chandler added a comment - Hmmm. I think what Scott said was he is working on it and no where did I see where he said it works. He mentions something working in another server. Did you see him write something else some where besides this issue?
          Hide
          Rick Hillegas added a comment -

          Hi Wade,

          Scott did not submit a patch for this issue and he has moved on to other pursuits. This issue is currently unassigned.

          Show
          Rick Hillegas added a comment - Hi Wade, Scott did not submit a patch for this issue and he has moved on to other pursuits. This issue is currently unassigned.
          Hide
          Bryan Pendleton added a comment -

          I am interested in working on this issue, and would like to hear from others who are interested in it.

          I propose to start by working on ALTER TABLE DROP [COLUMN] column [CASCADE|RESTRICT].

          If that goes well, I'll move on to some of the other ALTER TABLE column variants.

          Following the suggestion made by Dan on the mailing list
          (http://www.nabble.com/forum/ViewPost.jtp?post=1252308), I'm hoping to wire up the
          parser to the existing Alter Table support code.

          I've got a prototype implementation of the DROP COLUMN support working, and
          will be asking the list for some help with parser issues soon.

          Show
          Bryan Pendleton added a comment - I am interested in working on this issue, and would like to hear from others who are interested in it. I propose to start by working on ALTER TABLE DROP [COLUMN] column [CASCADE|RESTRICT] . If that goes well, I'll move on to some of the other ALTER TABLE column variants. Following the suggestion made by Dan on the mailing list ( http://www.nabble.com/forum/ViewPost.jtp?post=1252308 ), I'm hoping to wire up the parser to the existing Alter Table support code. I've got a prototype implementation of the DROP COLUMN support working, and will be asking the list for some help with parser issues soon.
          Hide
          Bryan Pendleton added a comment -

          Attached file 'dropColumn_1.diff' is a first attempt at providing support for
          ALTER TABLE DROP COLUMN

          This patch contains some small changes to the parser/compiler to recognize
          the new ALTER TABLE syntax, and uses the existing code in
          AlterTableConstantAction.java for the execution support.

          The patch contains a small amount of new test code, but really I haven't done
          much testing yet; I suspect that there is a LOT more testing to do, and I'm
          hoping that reviewers will suggest particular test cases that would be of
          interest.

          I'm particularly interested in feedback about the parser changes, and about testing,
          but of course all feedback is very gratefully appreciated.

          Show
          Bryan Pendleton added a comment - Attached file 'dropColumn_1.diff' is a first attempt at providing support for ALTER TABLE DROP COLUMN This patch contains some small changes to the parser/compiler to recognize the new ALTER TABLE syntax, and uses the existing code in AlterTableConstantAction.java for the execution support. The patch contains a small amount of new test code, but really I haven't done much testing yet; I suspect that there is a LOT more testing to do, and I'm hoping that reviewers will suggest particular test cases that would be of interest. I'm particularly interested in feedback about the parser changes, and about testing, but of course all feedback is very gratefully appreciated.
          Hide
          Bryan Pendleton added a comment -

          I propose to file separate JIRA issues for the topics described here:

          1) drop column
          2) rename column
          3) change column from null to not null, or vice versa
          4) change column default value
          5) change column datatype

          DERBY-119 already exists to track issue (3); I will be filing 4 new issues, linked to this one, to track the other topics. DERBY-168 was previously filed for issue (4), but was closed by changing the documentation, so I will open a new issue to track addition of the functionality.

          Show
          Bryan Pendleton added a comment - I propose to file separate JIRA issues for the topics described here: 1) drop column 2) rename column 3) change column from null to not null, or vice versa 4) change column default value 5) change column datatype DERBY-119 already exists to track issue (3); I will be filing 4 new issues, linked to this one, to track the other topics. DERBY-168 was previously filed for issue (4), but was closed by changing the documentation, so I will open a new issue to track addition of the functionality.
          Hide
          Bryan Pendleton added a comment -

          I got confused and opened DERBY-1492 accidentally; it was a duplicate of DERBY-119 so I closed it. Then I opened DERBY-1515 to track changing a column's datatype.

          Show
          Bryan Pendleton added a comment - I got confused and opened DERBY-1492 accidentally; it was a duplicate of DERBY-119 so I closed it. Then I opened DERBY-1515 to track changing a column's datatype.
          Hide
          Bryan Pendleton added a comment -

          I believe this issue can now be closed, and the other issues tracked independently. If you disagree, please comment accordingly.

          Show
          Bryan Pendleton added a comment - I believe this issue can now be closed, and the other issues tracked independently. If you disagree, please comment accordingly.
          Hide
          Kathey Marsden added a comment -

          Since this issue has 14 votes, I think I would prefer to see it left open and the other issues linked as "part of" this one. That way the vote information can be preserved until the complete issue is resolved.

          Show
          Kathey Marsden added a comment - Since this issue has 14 votes, I think I would prefer to see it left open and the other issues linked as "part of" this one. That way the vote information can be preserved until the complete issue is resolved.
          Hide
          Bryan Pendleton added a comment -

          DERBY-119 and DERBY-1491 have made it into the 10.2 release, yay!
          DERBY-1489 has made good progress, but is currently blocked on some
          interactions with the new GRANT/REVOKE feature. No significant progress
          on DERBY-1490 or DERBY-1515 has occurred recently.

          Show
          Bryan Pendleton added a comment - DERBY-119 and DERBY-1491 have made it into the 10.2 release, yay! DERBY-1489 has made good progress, but is currently blocked on some interactions with the new GRANT/REVOKE feature. No significant progress on DERBY-1490 or DERBY-1515 has occurred recently.
          Hide
          Bryan Pendleton added a comment -

          I propose to mark this issue resolved for 10.3.

          Can anybody see any part of this feature which has not yet been addressed?

          Kathey, I know you were concerned that we not prematurely close this issue. How
          do you feel about marking it as resolved at this point?

          Show
          Bryan Pendleton added a comment - I propose to mark this issue resolved for 10.3. Can anybody see any part of this feature which has not yet been addressed? Kathey, I know you were concerned that we not prematurely close this issue. How do you feel about marking it as resolved at this point?
          Hide
          Rajesh Kartha added a comment -

          I tend to agree with Bryan. As I understand ,the only issue that remains is to ALTER a COLUMN's DATATYPE, and the work-around provided in DERBY-1515 seems reasonable enough.

          -Rajesh

          Show
          Rajesh Kartha added a comment - I tend to agree with Bryan. As I understand ,the only issue that remains is to ALTER a COLUMN's DATATYPE, and the work-around provided in DERBY-1515 seems reasonable enough. -Rajesh
          Hide
          Bryan Pendleton added a comment -

          Thanks Rajesh! I've marked this issue as FIXED in 10.3. If the community feels that there are additional needs in the area of table alteration, please either re-open this issue with information about what is needed, or (probably simpler) open a new issue describing the remaining needs.

          Show
          Bryan Pendleton added a comment - Thanks Rajesh! I've marked this issue as FIXED in 10.3. If the community feels that there are additional needs in the area of table alteration, please either re-open this issue with information about what is needed, or (probably simpler) open a new issue describing the remaining needs.
          Hide
          Andrew McIntyre added a comment -

          This issue has been resolved for over a year with no further movement. Closing.

          Show
          Andrew McIntyre added a comment - This issue has been resolved for over a year with no further movement. Closing.

            People

            • Assignee:
              Bryan Pendleton
              Reporter:
              Kumar Matcha
            • Votes:
              14 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development