Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 10.7.1.1
    • Component/s: SQL
    • Labels:
      None
    • Issue & fix info:
      Newcomer

      Description

      Adding support for truncate table command will aid to portability

      1. changes.diff
        3 kB
        Eranda Sooriyabandara
      2. Derby-268.diff
        5 kB
        Eranda Sooriyabandara
      3. Derby-268.diff
        5 kB
        Eranda Sooriyabandara
      4. derby-268-01-ab-enableForInsaneBuilds.diff
        5 kB
        Rick Hillegas
      5. derby-268-02-aa-permsTest.diff
        7 kB
        Rick Hillegas
      6. derby-268-03-aa-npe.diff
        4 kB
        Rick Hillegas
      7. tests.diff
        4 kB
        Knut Anders Hatlen
      8. TruncateConcurrency.java
        9 kB
        Rick Hillegas
      9. TruncateConcurrency.java
        9 kB
        Rick Hillegas
      10. TruncateConcurrency.java
        8 kB
        Rick Hillegas

        Issue Links

          Activity

          Hide
          Knut Anders Hatlen added a comment -

          [bulk update] Close all resolved issues that haven't been updated for more than one year.

          Show
          Knut Anders Hatlen added a comment - [bulk update] Close all resolved issues that haven't been updated for more than one year.
          Hide
          Myrna van Lunteren added a comment -

          Support for the basic TRUNCATE TABLE command went into 10.7.1.1. I opened DERBY-5403 for implementation of the identityBehavior clause.

          Show
          Myrna van Lunteren added a comment - Support for the basic TRUNCATE TABLE command went into 10.7.1.1. I opened DERBY-5403 for implementation of the identityBehavior clause.
          Hide
          Eranda Sooriyabandara added a comment -

          Hi Myrna,
          I am not working on this issue for now. You may close this issue and split
          off a separate issue to support the identityBehavior functionality. Then I
          can later see into it.
          thanks

          Show
          Eranda Sooriyabandara added a comment - Hi Myrna, I am not working on this issue for now. You may close this issue and split off a separate issue to support the identityBehavior functionality. Then I can later see into it. thanks
          Hide
          Myrna van Lunteren added a comment - - edited

          Eranda, are you still working on this issue?
          If not, we should unassign you, and probably close this issue and split off a separate issue to support the identityBehavior functionality.

          Show
          Myrna van Lunteren added a comment - - edited Eranda, are you still working on this issue? If not, we should unassign you, and probably close this issue and split off a separate issue to support the identityBehavior functionality.
          Hide
          Rick Hillegas added a comment -

          Hi Eranda,

          The idea is that the table element representing the RESTART action should be added at bind() time, not at parse() time. All that happens at parse() time is that you mark the AlterTableNode to note that the user has requested RESTART. The new tableElement should be added at bind() time. At bind() time you will have access to all of the metadata you need and you should be able to find the name of the identity column in structures hanging off the TableDescriptor. You should build the tableElement in AlterTableNode.bind(). See my comment on 2010-11-18 for the processing flow.

          Hope this helps,
          -Rick

          Show
          Rick Hillegas added a comment - Hi Eranda, The idea is that the table element representing the RESTART action should be added at bind() time, not at parse() time. All that happens at parse() time is that you mark the AlterTableNode to note that the user has requested RESTART. The new tableElement should be added at bind() time. At bind() time you will have access to all of the metadata you need and you should be able to find the name of the identity column in structures hanging off the TableDescriptor. You should build the tableElement in AlterTableNode.bind(). See my comment on 2010-11-18 for the processing flow. Hope this helps, -Rick
          Hide
          Eranda Sooriyabandara added a comment -

          Hi Rick,
          But here we cannot use any column name since we do not know the column names of the identity columns.
          Are there any method which we can get all the column information at SQLParser (sqlgrammer.jj)?
          thanks
          Eranda

          Show
          Eranda Sooriyabandara added a comment - Hi Rick, But here we cannot use any column name since we do not know the column names of the identity columns. Are there any method which we can get all the column information at SQLParser (sqlgrammer.jj)? thanks Eranda
          Hide
          Rick Hillegas added a comment -

          Hi Eranda,

          That is the right code for creating a table element list (if there isn't one already). Now you just need to put a node on that list. The node will represent the instruction to restart the identity column at a new value. Here's how sqlgrammar.jj constructs that node when it sees an ALTER TABLE ALTER COLUMN...RESTART WITH... statement:

          <RESTART> <WITH> autoIncrementRestartWith = exactNumber()

          { autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_START_INDEX] = autoIncrementRestartWith; autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_CREATE_MODIFY] = ColumnDefinitionNode.MODIFY_AUTOINCREMENT_RESTART_VALUE; return (TableElementNode) nodeFactory.getNode( C_NodeTypes.MODIFY_COLUMN_DEFAULT_NODE, columnName, null, null, autoIncrementInfo, getContextManager()); }

          Hope this helps,
          -Rick

          Show
          Rick Hillegas added a comment - Hi Eranda, That is the right code for creating a table element list (if there isn't one already). Now you just need to put a node on that list. The node will represent the instruction to restart the identity column at a new value. Here's how sqlgrammar.jj constructs that node when it sees an ALTER TABLE ALTER COLUMN...RESTART WITH... statement: <RESTART> <WITH> autoIncrementRestartWith = exactNumber() { autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_START_INDEX] = autoIncrementRestartWith; autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_CREATE_MODIFY] = ColumnDefinitionNode.MODIFY_AUTOINCREMENT_RESTART_VALUE; return (TableElementNode) nodeFactory.getNode( C_NodeTypes.MODIFY_COLUMN_DEFAULT_NODE, columnName, null, null, autoIncrementInfo, getContextManager()); } Hope this helps, -Rick
          Hide
          Eranda Sooriyabandara added a comment -

          Hi Rick,
          Thanks Rick the previous comment was exactly describing what I want to know.
          I did some work on this and came up with a patch which is not fully completed.
          I use the following method to get the tableElementList. But it gives a TableElementList, which has zero elements.

          TableElementList tableElementList = (TableElementList) nodeFactory.getNode(
          C_NodeTypes.TABLE_ELEMENT_LIST,
          getContextManager());

          Is it a incorrect way to get the table element list.
          And please take a look at the code and let me know your ideas.
          thanks

          Show
          Eranda Sooriyabandara added a comment - Hi Rick, Thanks Rick the previous comment was exactly describing what I want to know. I did some work on this and came up with a patch which is not fully completed. I use the following method to get the tableElementList. But it gives a TableElementList, which has zero elements. TableElementList tableElementList = (TableElementList) nodeFactory.getNode( C_NodeTypes.TABLE_ELEMENT_LIST, getContextManager()); Is it a incorrect way to get the table element list. And please take a look at the code and let me know your ideas. thanks
          Hide
          Rick Hillegas added a comment -

          Hi Eranda,

          I am not sure where you are in the code now, so I may not be answering your question. If you can give me more context, I may be more helpful. For TRUNCATE TABLE, the AlterTableNode is created by the parser. This is the basic processing flow today:

          o The parser digests the TRUNCATE TABLE statement and creates an AlterTableNode.

          o Then the AlterTableNode is bound. That is, object names are looked up in the system catalogs (in this case, the named object in question is the table). As necessary, metadata is copied out of the system catalogs into in-memory structures in the parse tree.

          o Then the AlterTableNode is optimized. Since this is a DDL statement, there is nothing to do during this phase.

          o An executable plan is then generated from the AlterTableNode. This phase is called code-generation. The executable plan is an AlterTableConstantAction.

          o At run-time, the AlterTableConstantAction performs the truncation.

          Here's what I think the code will end up doing, at a high level:

          o At parse time, you will detect the RESTART IDENTITY clause and set some flag in the AlterTableNode.

          o At bind time, you will see that flag. That will cause you to look up the column metadata for the table, find the identity column, then build a TableElementNode which represents the instruction to re-initialize the identity column.

          o At code-generation time, you will turn the TableElementNode into a ColumnInfo which represents the same information. The TableElementNode is a compile-time structure. The ColumnInfo is a runtime structure. What you are doing here is translating compile-time structures into runtime structures.

          o At runtime, the AlterTableConstantAction will notice the ColumnInfo structure and reinitialize the identity column.

          I hope I am not confusing you by answering the wrong question. Keep asking questions so that I can better understand what's puzzling you.

          Thanks,
          -Rick

          Show
          Rick Hillegas added a comment - Hi Eranda, I am not sure where you are in the code now, so I may not be answering your question. If you can give me more context, I may be more helpful. For TRUNCATE TABLE, the AlterTableNode is created by the parser. This is the basic processing flow today: o The parser digests the TRUNCATE TABLE statement and creates an AlterTableNode. o Then the AlterTableNode is bound. That is, object names are looked up in the system catalogs (in this case, the named object in question is the table). As necessary, metadata is copied out of the system catalogs into in-memory structures in the parse tree. o Then the AlterTableNode is optimized. Since this is a DDL statement, there is nothing to do during this phase. o An executable plan is then generated from the AlterTableNode. This phase is called code-generation. The executable plan is an AlterTableConstantAction. o At run-time, the AlterTableConstantAction performs the truncation. Here's what I think the code will end up doing, at a high level: o At parse time, you will detect the RESTART IDENTITY clause and set some flag in the AlterTableNode. o At bind time, you will see that flag. That will cause you to look up the column metadata for the table, find the identity column, then build a TableElementNode which represents the instruction to re-initialize the identity column. o At code-generation time, you will turn the TableElementNode into a ColumnInfo which represents the same information. The TableElementNode is a compile-time structure. The ColumnInfo is a runtime structure. What you are doing here is translating compile-time structures into runtime structures. o At runtime, the AlterTableConstantAction will notice the ColumnInfo structure and reinitialize the identity column. I hope I am not confusing you by answering the wrong question. Keep asking questions so that I can better understand what's puzzling you. Thanks, -Rick
          Hide
          Eranda Sooriyabandara added a comment -

          HI Rick,
          I have a problem with what is the purpose of creating a StatementNode by executing
          nodeFactory.getNode(C_NodeTypes.ALTER_TABLE_NODE,tableName, getContextManager();.
          And how this StatementNode is helping to execute.
          thanks

          Show
          Eranda Sooriyabandara added a comment - HI Rick, I have a problem with what is the purpose of creating a StatementNode by executing nodeFactory.getNode(C_NodeTypes.ALTER_TABLE_NODE,tableName, getContextManager();. And how this StatementNode is helping to execute. thanks
          Hide
          Rick Hillegas added a comment -

          Hi Eranda,

          You are on the right track. sqlgrammar.jj is the right place to start to see how the TableElementList is constructed. But at parse() time you don't have enough information to construct the list for a TRUNCATE TABLE statement. You only have that information at bind() time. That is because it is the responsibility of the bind() phase to look at the metadata to fill in missing facts about the table.

          The nodeFactory.getNode() call cited above will give you an empty TableElementList. You then need to put one TableElementNode on that list, a TableElementNode which represents the action ALTER TABLE ALTER COLUMN...RESTART WITH. To see how to make that node, look in sqlgrammar.jj for the string "<RESTART> <WITH>".

          The TableElementList itself should be constructed in AlterTableNode.bindStatement() once you know that you are dealing with a TRUNCATE TABLE statement.

          Hope this helps,
          -Rick

          Show
          Rick Hillegas added a comment - Hi Eranda, You are on the right track. sqlgrammar.jj is the right place to start to see how the TableElementList is constructed. But at parse() time you don't have enough information to construct the list for a TRUNCATE TABLE statement. You only have that information at bind() time. That is because it is the responsibility of the bind() phase to look at the metadata to fill in missing facts about the table. The nodeFactory.getNode() call cited above will give you an empty TableElementList. You then need to put one TableElementNode on that list, a TableElementNode which represents the action ALTER TABLE ALTER COLUMN...RESTART WITH. To see how to make that node, look in sqlgrammar.jj for the string "<RESTART> <WITH>". The TableElementList itself should be constructed in AlterTableNode.bindStatement() once you know that you are dealing with a TRUNCATE TABLE statement. Hope this helps, -Rick
          Hide
          Eranda Sooriyabandara added a comment -

          Hi Rick,
          Sorry for being late to reply.
          I used "TableElementList tableElementList = (TableElementList) nodeFactory.getNode(C_NodeTypes.TABLE_ELEMENT_LIST, getContextManager());"
          to get the TableElementList inside of the truncateTableStatement() in the SQLParser but it didn't give the actual results of the table which I created.
          For example it gives the number of the columns 0 and when I try to get the first element it gives an exception. Any idea why above doesn't return the correct TableElementList?
          Also here I am attaching the changes I did.
          thanks

          Show
          Eranda Sooriyabandara added a comment - Hi Rick, Sorry for being late to reply. I used "TableElementList tableElementList = (TableElementList) nodeFactory.getNode(C_NodeTypes.TABLE_ELEMENT_LIST, getContextManager());" to get the TableElementList inside of the truncateTableStatement() in the SQLParser but it didn't give the actual results of the table which I created. For example it gives the number of the columns 0 and when I try to get the first element it gives an exception. Any idea why above doesn't return the correct TableElementList? Also here I am attaching the changes I did. thanks
          Hide
          Dag H. Wanvik added a comment -

          > I see no reason to introduce special machinery to model this clause of the TRUNCATE standard. We can revisit > this topic if we decide to build completion conditions for INSERT statements.

          Sounds good to me.

          Show
          Dag H. Wanvik added a comment - > I see no reason to introduce special machinery to model this clause of the TRUNCATE standard. We can revisit > this topic if we decide to build completion conditions for INSERT statements. Sounds good to me.
          Hide
          Rick Hillegas added a comment -

          Hi Kristian,

          Connection.prepareStatement( "truncate table t" ).executeUpdate() returns 0, regardless of how many rows are in the table, just as is done by Connection.prepareStatement( "drop table t" ).executeUpdate(). Regards.

          Show
          Rick Hillegas added a comment - Hi Kristian, Connection.prepareStatement( "truncate table t" ).executeUpdate() returns 0, regardless of how many rows are in the table, just as is done by Connection.prepareStatement( "drop table t" ).executeUpdate(). Regards.
          Hide
          Kristian Waagan added a comment -

          Out of curiosity, is TRUNCATE TABLE returning a row count?

          Show
          Kristian Waagan added a comment - Out of curiosity, is TRUNCATE TABLE returning a row count?
          Hide
          Rick Hillegas added a comment -

          Dag raised the following issue: The 2008 SQL Standard (part 2, section 14.10 <truncate table statement>, General Rule 7) says that if the table was empty to begin with, then TRUNCATE TABLE should raise a "no data" completion condition. Note that the Standard says that the same completion condition should be raised by INSERT statements which end up inserting no rows: see section 14.11 <insert statement>, General Rule 9.

          Completion conditions are represented as SQLStates. I do not see where these completion conditions are modelled in JDBC. In any event, Derby does not do anything special for empty INSERTs. I see no reason to introduce special machinery to model this clause of the TRUNCATE standard. We can revisit this topic if we decide to build completion conditions for INSERT statements.

          Show
          Rick Hillegas added a comment - Dag raised the following issue: The 2008 SQL Standard (part 2, section 14.10 <truncate table statement>, General Rule 7) says that if the table was empty to begin with, then TRUNCATE TABLE should raise a "no data" completion condition. Note that the Standard says that the same completion condition should be raised by INSERT statements which end up inserting no rows: see section 14.11 <insert statement>, General Rule 9. Completion conditions are represented as SQLStates. I do not see where these completion conditions are modelled in JDBC. In any event, Derby does not do anything special for empty INSERTs. I see no reason to introduce special machinery to model this clause of the TRUNCATE standard. We can revisit this topic if we decide to build completion conditions for INSERT statements.
          Hide
          Rick Hillegas added a comment -

          Hi Eranda,

          No need to touch ij, all of this code should be in the engine. The good news is that both TRUNCATE TABLE and ALTER TABLE ALTER COLUMN RESTART are handled by the AlterTableConstantAction machinery at run time. If I were tackling this, I would first try something along these lines:

          o AlterTableNode.bindStatement() will need to build a tableElementList structure for TRUNCATE TABLE, describing the identity column which needs to be re-initialized. For ALTER TABLE ALTER COLUMN RESTART, that tableElementList is created by the parser.

          o That tableElementList structure will then be picked up by AlterTableNode.prepConstantAction and turned into a ColumnInfo array when the run time structures are generated for TRUNCATE TABLE. The ColumnInfo[] structure should contain enough information to describe the change to the identity column.

          o The column info structure will then be processed by AlterTableConstantAction.executeConstantAction() at run time.

          You may need to tweak the code a bit to get this to function, but I think this basic processing flow should work. Please ask more questions if this is too cryptic.

          Hope this helps,
          -Rick

          Show
          Rick Hillegas added a comment - Hi Eranda, No need to touch ij, all of this code should be in the engine. The good news is that both TRUNCATE TABLE and ALTER TABLE ALTER COLUMN RESTART are handled by the AlterTableConstantAction machinery at run time. If I were tackling this, I would first try something along these lines: o AlterTableNode.bindStatement() will need to build a tableElementList structure for TRUNCATE TABLE, describing the identity column which needs to be re-initialized. For ALTER TABLE ALTER COLUMN RESTART, that tableElementList is created by the parser. o That tableElementList structure will then be picked up by AlterTableNode.prepConstantAction and turned into a ColumnInfo array when the run time structures are generated for TRUNCATE TABLE. The ColumnInfo[] structure should contain enough information to describe the change to the identity column. o The column info structure will then be processed by AlterTableConstantAction.executeConstantAction() at run time. You may need to tweak the code a bit to get this to function, but I think this basic processing flow should work. Please ask more questions if this is too cryptic. Hope this helps, -Rick
          Hide
          Eranda Sooriyabandara added a comment -

          Hi Rick,
          Considering your previous comment,
          Where we can execute "ALTER TABLE tableName ALTER COLUMN RESTART WITH initialValue " after truncating table? Is it only from ij.java(by adding a line like "aStatement.execute("ALTER TABLE t1 ALTER COLUMN a RESTART WITH 0")" to ij.jj)?
          thanks

          Show
          Eranda Sooriyabandara added a comment - Hi Rick, Considering your previous comment, Where we can execute "ALTER TABLE tableName ALTER COLUMN RESTART WITH initialValue " after truncating table? Is it only from ij.java(by adding a line like "aStatement.execute("ALTER TABLE t1 ALTER COLUMN a RESTART WITH 0")" to ij.jj)? thanks
          Hide
          Rick Hillegas added a comment -

          Tests passed cleanly for me. Committed derby-268-03-aa-npe.diff at subversion revision 1002232.

          Show
          Rick Hillegas added a comment - Tests passed cleanly for me. Committed derby-268-03-aa-npe.diff at subversion revision 1002232.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-268-03-aa-npe.diff. This patch fixes the NPE seen in the TruncatorConcurrency experiments when the re-initialization statement is TRUNCATE TABLE or DROP TABLE. I am running regression tests now.

          Touches the following files:

          ----------

          M java/engine/org/apache/derby/impl/store/access/conglomerate/GenericController.java

          The NPE occurred while trying to update the statistics for a conglomerate which has been dropped. The fix is to not update the statistics if the conglomerate no longer exists.

          ----------

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/TruncateTableTest.java

          Added a test case to track this fix.

          Show
          Rick Hillegas added a comment - Attaching derby-268-03-aa-npe.diff. This patch fixes the NPE seen in the TruncatorConcurrency experiments when the re-initialization statement is TRUNCATE TABLE or DROP TABLE. I am running regression tests now. Touches the following files: ---------- M java/engine/org/apache/derby/impl/store/access/conglomerate/GenericController.java The NPE occurred while trying to update the statistics for a conglomerate which has been dropped. The fix is to not update the statistics if the conglomerate no longer exists. ---------- M java/testing/org/apache/derbyTesting/functionTests/tests/lang/TruncateTableTest.java Added a test case to track this fix.
          Hide
          Rick Hillegas added a comment -

          Attaching a new rev of TruncateConcurrency. The previous version was always committing the Selector after reading the first row. This version makes the commitSelector option function correctly. Now if the Selector does not commit, I see that the Truncator blocks waiting for a lock if the Truncator is working in a separate transaction.

          This fix does not affect the following conclusions:

          i) The behavior of TRUNCATE TABLE is still consistent with the SQL Standard.

          ii) TRUNCATE TABLE still behaves like DROP TABLE.

          Here are the results after fixing this defect:

          ------------ Re-initialization command is TRUNCATE TABLE -------------

          1) TRUNCATE TABLE command raises "Operation 'TRUNCATE TABLE' cannot be performed on object 'T' because there is an open ResultSet dependent on that object."

          java TruncateConcurrency truncate same sensitive
          java TruncateConcurrency truncate same sensitive commitTruncator
          java TruncateConcurrency truncate same sensitive commitSelector
          java TruncateConcurrency truncate same sensitive commitTruncator commitSelector
          java TruncateConcurrency truncate same insensitive
          java TruncateConcurrency truncate same insensitive commitTruncator
          java TruncateConcurrency truncate same insensitive commitSelector
          java TruncateConcurrency truncate same insensitive commitTruncator commitSelector

          2) TRUNCATE TABLE command hangs waiting for a lock

          java TruncateConcurrency truncate different sensitive
          java TruncateConcurrency truncate different sensitive commitTruncator
          java TruncateConcurrency truncate different insensitive
          java TruncateConcurrency truncate different insensitive commitTruncator

          3) Selector hangs trying to read the second row

          java TruncateConcurrency truncate different sensitive commitSelector
          java TruncateConcurrency truncate different insensitive commitSelector

          4) Selector trips over an NPE trying to read the second row

          java TruncateConcurrency truncate different sensitive commitTruncator commitSelector
          java TruncateConcurrency truncate different insensitive commitTruncator commitSelector

          ------------ Re-initialization command is DROP TABLE -------------

          1') DROP TABLE raises "Operation 'DROP TABLE' cannot be performed on object 'T' because there is an open ResultSet dependent on that object."

          java TruncateConcurrency drop same sensitive
          java TruncateConcurrency drop same sensitive commitTruncator
          java TruncateConcurrency drop same sensitive commitSelector
          java TruncateConcurrency drop same sensitive commitTruncator commitSelector
          java TruncateConcurrency drop same insensitive
          java TruncateConcurrency drop same insensitive commitTruncator
          java TruncateConcurrency drop same insensitive commitSelector
          java TruncateConcurrency drop same insensitive commitTruncator commitSelector

          2') DROP TABLE command hangs waiting for a lock

          java TruncateConcurrency drop different sensitive
          java TruncateConcurrency drop different sensitive commitTruncator
          java TruncateConcurrency drop different insensitive
          java TruncateConcurrency drop different insensitive commitTruncator

          3') Selector hangs trying to read the second row

          java TruncateConcurrency drop different sensitive commitSelector
          java TruncateConcurrency drop different insensitive commitSelector

          4') Selector trips over an NPE trying to read the second row

          java TruncateConcurrency drop different sensitive commitTruncator commitSelector
          java TruncateConcurrency drop different insensitive commitTruncator commitSelector

          ------------ Re-initialization command is DELETE FROM -------------

          The Selector successfully reads both rows

          java TruncateConcurrency delete same sensitive
          java TruncateConcurrency delete same sensitive commitTruncator
          java TruncateConcurrency delete same sensitive commitSelector
          java TruncateConcurrency delete same sensitive commitTruncator commitSelector
          java TruncateConcurrency delete same insensitive
          java TruncateConcurrency delete same insensitive commitTruncator
          java TruncateConcurrency delete same insensitive commitSelector
          java TruncateConcurrency delete same insensitive commitTruncator commitSelector
          java TruncateConcurrency delete different sensitive
          java TruncateConcurrency delete different sensitive commitTruncator
          java TruncateConcurrency delete different sensitive commitSelector
          java TruncateConcurrency delete different sensitive commitTruncator commitSelector
          java TruncateConcurrency delete different insensitive
          java TruncateConcurrency delete different insensitive commitTruncator
          java TruncateConcurrency delete different insensitive commitSelector
          java TruncateConcurrency delete different insensitive commitTruncator commitSelector

          Show
          Rick Hillegas added a comment - Attaching a new rev of TruncateConcurrency. The previous version was always committing the Selector after reading the first row. This version makes the commitSelector option function correctly. Now if the Selector does not commit, I see that the Truncator blocks waiting for a lock if the Truncator is working in a separate transaction. This fix does not affect the following conclusions: i) The behavior of TRUNCATE TABLE is still consistent with the SQL Standard. ii) TRUNCATE TABLE still behaves like DROP TABLE. Here are the results after fixing this defect: ------------ Re-initialization command is TRUNCATE TABLE ------------- 1) TRUNCATE TABLE command raises "Operation 'TRUNCATE TABLE' cannot be performed on object 'T' because there is an open ResultSet dependent on that object." java TruncateConcurrency truncate same sensitive java TruncateConcurrency truncate same sensitive commitTruncator java TruncateConcurrency truncate same sensitive commitSelector java TruncateConcurrency truncate same sensitive commitTruncator commitSelector java TruncateConcurrency truncate same insensitive java TruncateConcurrency truncate same insensitive commitTruncator java TruncateConcurrency truncate same insensitive commitSelector java TruncateConcurrency truncate same insensitive commitTruncator commitSelector 2) TRUNCATE TABLE command hangs waiting for a lock java TruncateConcurrency truncate different sensitive java TruncateConcurrency truncate different sensitive commitTruncator java TruncateConcurrency truncate different insensitive java TruncateConcurrency truncate different insensitive commitTruncator 3) Selector hangs trying to read the second row java TruncateConcurrency truncate different sensitive commitSelector java TruncateConcurrency truncate different insensitive commitSelector 4) Selector trips over an NPE trying to read the second row java TruncateConcurrency truncate different sensitive commitTruncator commitSelector java TruncateConcurrency truncate different insensitive commitTruncator commitSelector ------------ Re-initialization command is DROP TABLE ------------- 1') DROP TABLE raises "Operation 'DROP TABLE' cannot be performed on object 'T' because there is an open ResultSet dependent on that object." java TruncateConcurrency drop same sensitive java TruncateConcurrency drop same sensitive commitTruncator java TruncateConcurrency drop same sensitive commitSelector java TruncateConcurrency drop same sensitive commitTruncator commitSelector java TruncateConcurrency drop same insensitive java TruncateConcurrency drop same insensitive commitTruncator java TruncateConcurrency drop same insensitive commitSelector java TruncateConcurrency drop same insensitive commitTruncator commitSelector 2') DROP TABLE command hangs waiting for a lock java TruncateConcurrency drop different sensitive java TruncateConcurrency drop different sensitive commitTruncator java TruncateConcurrency drop different insensitive java TruncateConcurrency drop different insensitive commitTruncator 3') Selector hangs trying to read the second row java TruncateConcurrency drop different sensitive commitSelector java TruncateConcurrency drop different insensitive commitSelector 4') Selector trips over an NPE trying to read the second row java TruncateConcurrency drop different sensitive commitTruncator commitSelector java TruncateConcurrency drop different insensitive commitTruncator commitSelector ------------ Re-initialization command is DELETE FROM ------------- The Selector successfully reads both rows java TruncateConcurrency delete same sensitive java TruncateConcurrency delete same sensitive commitTruncator java TruncateConcurrency delete same sensitive commitSelector java TruncateConcurrency delete same sensitive commitTruncator commitSelector java TruncateConcurrency delete same insensitive java TruncateConcurrency delete same insensitive commitTruncator java TruncateConcurrency delete same insensitive commitSelector java TruncateConcurrency delete same insensitive commitTruncator commitSelector java TruncateConcurrency delete different sensitive java TruncateConcurrency delete different sensitive commitTruncator java TruncateConcurrency delete different sensitive commitSelector java TruncateConcurrency delete different sensitive commitTruncator commitSelector java TruncateConcurrency delete different insensitive java TruncateConcurrency delete different insensitive commitTruncator java TruncateConcurrency delete different insensitive commitSelector java TruncateConcurrency delete different insensitive commitTruncator commitSelector
          Hide
          Rick Hillegas added a comment -

          Thanks, Knut. Your theory about the hang sounds good to me. Not sure about DERBY-979. I'm seeing a different stack trace than what's recorded on that issue (see below). I do see the same stack trace regardless of whether the truncating operation is TRUNCATE TABLE or DROP TABLE. As you note, TRUNCATE TABLE is behaving like DROP TABLE, which makes some sense since they are both implemented using the DDL machinery. Attaching a new rev of TruncateConcurrency.java. This compares the behavior of TRUNCATE TABLE to DROP TABLE and DELETE FROM.

          ---------

          Here's how to run the new rev of the program:

          java TruncateConcurrency $resetOperation $transaction $sensitivity [ $commit ]*

          where

          $resetOperation = truncate | drop | delete (whether the re-initialization operation should be TRUNCATE TABLE, DROP TABLE, or DELETE
          $transaction = same | different (whether the reader and truncator do their work in the same transaction)
          $sensitivity = sensitive | insensitive (whether the reader should use a sensitive or insensitive cursor)
          $commit = commitSelector | commitTruncator

          If commitSelector is specified, then the Selector commits after
          reading a row but before the truncation.

          If commitTruncator is specified, then the Truncator commits
          immediately after truncation.

          E.g.:

          java TruncateConcurrency different sensitive commitTruncator commitSelector

          -----------

          As previously, I see the following behavior for TRUNCATE TABLE:

          1) In the following experiments, the TRUNCATE TABLE raises "Operation 'TRUNCATE TABLE' cannot be performed on object 'T' because there is an open ResultSet dependent on that object.":

          java TruncateConcurrency truncate same sensitive
          java TruncateConcurrency truncate same sensitive commitTruncator
          java TruncateConcurrency truncate same sensitive commitSelector
          java TruncateConcurrency truncate same sensitive commitTruncator commitSelector
          java TruncateConcurrency truncate same insensitive
          java TruncateConcurrency truncate same insensitive commitTruncator
          java TruncateConcurrency truncate same insensitive commitSelector
          java TruncateConcurrency truncate same insensitive commitTruncator commitSelector

          2) When the Selector and Truncator are DIFFERENT transactions, then Derby misbehaves. There are two kinds of misbehavior here. The sub-cases are distinguished by whether the Truncator committed immediately after truncating the table. Whether the Selector committed after reading its first row is also irrelevant.

          2a) In the following experiments, the Selector triggers an NPE when calling ResultSet.next():

          java TruncateConcurrency truncate different sensitive commitTruncator
          java TruncateConcurrency truncate different sensitive commitTruncator commitSelector
          java TruncateConcurrency truncate different insensitive commitTruncator
          java TruncateConcurrency truncate different insensitive commitTruncator commitSelector

          This is the NPE:

          java.lang.NullPointerException
          at org.apache.derby.impl.store.access.conglomerate.GenericController.setEstimatedRowCount(GenericController.java:224)
          at org.apache.derby.impl.sql.execute.TableScanResultSet.setRowCountIfPossible(TableScanResultSet.java:1325)
          at org.apache.derby.impl.sql.execute.BulkTableScanResultSet.getNextRowCore(BulkTableScanResultSet.java:311)
          at org.apache.derby.impl.sql.execute.ScrollInsensitiveResultSet.getNextRowFromSource(ScrollInsensitiveResultSet.java:801)
          at org.apache.derby.impl.sql.execute.ScrollInsensitiveResultSet.getNextRowCore(ScrollInsensitiveResultSet.java:518)
          at org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow(BasicNoPutResultSetImpl.java:477)
          at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(EmbedResultSet.java:429)
          at org.apache.derby.impl.jdbc.EmbedResultSet.next(EmbedResultSet.java:373)
          at TruncateConcurrency.printRow(TruncateConcurrency.java:188)
          at TruncateConcurrency.justDoIt(TruncateConcurrency.java:97)
          at TruncateConcurrency.main(TruncateConcurrency.java:49)

          2b) The following experiments hang the Selector when calling ResultSet.next():

          java TruncateConcurrency truncate different sensitive
          java TruncateConcurrency truncate different sensitive commitSelector
          java TruncateConcurrency truncate different insensitive
          java TruncateConcurrency truncate different insensitive commitSelector

          -----------

          I see the following behavior for DROP TABLE:

          1') In the following experiments, the DROP TABLE raises "DROP TABLE' cannot be performed on object 'T' because there is an open ResultSet dependent on that object.":

          java TruncateConcurrency drop same sensitive
          java TruncateConcurrency drop same sensitive commitTruncator
          java TruncateConcurrency drop same sensitive commitSelector
          java TruncateConcurrency drop same sensitive commitTruncator commitSelector
          java TruncateConcurrency drop same insensitive
          java TruncateConcurrency drop same insensitive commitTruncator
          java TruncateConcurrency drop same insensitive commitSelector
          java TruncateConcurrency drop same insensitive commitTruncator commitSelector

          2') When the Selector and Truncator are DIFFERENT transactions, then Derby misbehaves. There are two kinds of misbehavior here. The sub-cases are distinguished by whether the Truncator committed immediately after truncating the table. Whether the Selector committed after reading its first row is also irrelevant.

          2a') In the following experiments, the Selector triggers an NPE when calling ResultSet.next():

          java TruncateConcurrency drop different sensitive commitTruncator
          java TruncateConcurrency drop different sensitive commitTruncator commitSelector
          java TruncateConcurrency drop different insensitive commitTruncator
          java TruncateConcurrency drop different insensitive commitTruncator commitSelector

          This is the NPE:

          java.lang.NullPointerException
          at org.apache.derby.impl.store.access.conglomerate.GenericController.setEstimatedRowCount(GenericController.java:224)
          at org.apache.derby.impl.sql.execute.TableScanResultSet.setRowCountIfPossible(TableScanResultSet.java:1325)
          at org.apache.derby.impl.sql.execute.BulkTableScanResultSet.getNextRowCore(BulkTableScanResultSet.java:311)
          at org.apache.derby.impl.sql.execute.ScrollInsensitiveResultSet.getNextRowFromSource(ScrollInsensitiveResultSet.java:801)
          at org.apache.derby.impl.sql.execute.ScrollInsensitiveResultSet.getNextRowCore(ScrollInsensitiveResultSet.java:518)
          at org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow(BasicNoPutResultSetImpl.java:477)
          at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(EmbedResultSet.java:429)
          at org.apache.derby.impl.jdbc.EmbedResultSet.next(EmbedResultSet.java:373)
          at TruncateConcurrency.printRow(TruncateConcurrency.java:188)
          at TruncateConcurrency.justDoIt(TruncateConcurrency.java:97)
          at TruncateConcurrency.main(TruncateConcurrency.java:49)

          2b') The following experiments hang the Selector when calling ResultSet.next():

          java TruncateConcurrency drop different sensitive
          java TruncateConcurrency drop different sensitive commitSelector
          java TruncateConcurrency drop different insensitive
          java TruncateConcurrency drop different insensitive commitSelector

          -----------

          I see the following behavior for DELETE FROM: All of the test cases terminate without errors. The Selector is able to read the second row regardless of the sensitivity of the cursor and regardless of whether the read takes place in the same transaction as DELETE FROM.

          java TruncateConcurrency delete same sensitive
          java TruncateConcurrency delete same sensitive commitTruncator
          java TruncateConcurrency delete same sensitive commitSelector
          java TruncateConcurrency delete same sensitive commitTruncator commitSelector
          java TruncateConcurrency delete same insensitive
          java TruncateConcurrency delete same insensitive commitTruncator
          java TruncateConcurrency delete same insensitive commitSelector
          java TruncateConcurrency delete same insensitive commitTruncator commitSelector
          java TruncateConcurrency delete different sensitive
          java TruncateConcurrency delete different sensitive commitTruncator
          java TruncateConcurrency delete different sensitive commitSelector
          java TruncateConcurrency delete different sensitive commitTruncator commitSelector
          java TruncateConcurrency delete different insensitive
          java TruncateConcurrency delete different insensitive commitTruncator
          java TruncateConcurrency delete different insensitive commitSelector
          java TruncateConcurrency delete different insensitive commitTruncator commitSelector

          Show
          Rick Hillegas added a comment - Thanks, Knut. Your theory about the hang sounds good to me. Not sure about DERBY-979 . I'm seeing a different stack trace than what's recorded on that issue (see below). I do see the same stack trace regardless of whether the truncating operation is TRUNCATE TABLE or DROP TABLE. As you note, TRUNCATE TABLE is behaving like DROP TABLE, which makes some sense since they are both implemented using the DDL machinery. Attaching a new rev of TruncateConcurrency.java. This compares the behavior of TRUNCATE TABLE to DROP TABLE and DELETE FROM. --------- Here's how to run the new rev of the program: java TruncateConcurrency $resetOperation $transaction $sensitivity [ $commit ]* where $resetOperation = truncate | drop | delete (whether the re-initialization operation should be TRUNCATE TABLE, DROP TABLE, or DELETE $transaction = same | different (whether the reader and truncator do their work in the same transaction) $sensitivity = sensitive | insensitive (whether the reader should use a sensitive or insensitive cursor) $commit = commitSelector | commitTruncator If commitSelector is specified, then the Selector commits after reading a row but before the truncation. If commitTruncator is specified, then the Truncator commits immediately after truncation. E.g.: java TruncateConcurrency different sensitive commitTruncator commitSelector ----------- As previously, I see the following behavior for TRUNCATE TABLE: 1) In the following experiments, the TRUNCATE TABLE raises "Operation 'TRUNCATE TABLE' cannot be performed on object 'T' because there is an open ResultSet dependent on that object.": java TruncateConcurrency truncate same sensitive java TruncateConcurrency truncate same sensitive commitTruncator java TruncateConcurrency truncate same sensitive commitSelector java TruncateConcurrency truncate same sensitive commitTruncator commitSelector java TruncateConcurrency truncate same insensitive java TruncateConcurrency truncate same insensitive commitTruncator java TruncateConcurrency truncate same insensitive commitSelector java TruncateConcurrency truncate same insensitive commitTruncator commitSelector 2) When the Selector and Truncator are DIFFERENT transactions, then Derby misbehaves. There are two kinds of misbehavior here. The sub-cases are distinguished by whether the Truncator committed immediately after truncating the table. Whether the Selector committed after reading its first row is also irrelevant. 2a) In the following experiments, the Selector triggers an NPE when calling ResultSet.next(): java TruncateConcurrency truncate different sensitive commitTruncator java TruncateConcurrency truncate different sensitive commitTruncator commitSelector java TruncateConcurrency truncate different insensitive commitTruncator java TruncateConcurrency truncate different insensitive commitTruncator commitSelector This is the NPE: java.lang.NullPointerException at org.apache.derby.impl.store.access.conglomerate.GenericController.setEstimatedRowCount(GenericController.java:224) at org.apache.derby.impl.sql.execute.TableScanResultSet.setRowCountIfPossible(TableScanResultSet.java:1325) at org.apache.derby.impl.sql.execute.BulkTableScanResultSet.getNextRowCore(BulkTableScanResultSet.java:311) at org.apache.derby.impl.sql.execute.ScrollInsensitiveResultSet.getNextRowFromSource(ScrollInsensitiveResultSet.java:801) at org.apache.derby.impl.sql.execute.ScrollInsensitiveResultSet.getNextRowCore(ScrollInsensitiveResultSet.java:518) at org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow(BasicNoPutResultSetImpl.java:477) at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(EmbedResultSet.java:429) at org.apache.derby.impl.jdbc.EmbedResultSet.next(EmbedResultSet.java:373) at TruncateConcurrency.printRow(TruncateConcurrency.java:188) at TruncateConcurrency.justDoIt(TruncateConcurrency.java:97) at TruncateConcurrency.main(TruncateConcurrency.java:49) 2b) The following experiments hang the Selector when calling ResultSet.next(): java TruncateConcurrency truncate different sensitive java TruncateConcurrency truncate different sensitive commitSelector java TruncateConcurrency truncate different insensitive java TruncateConcurrency truncate different insensitive commitSelector ----------- I see the following behavior for DROP TABLE: 1') In the following experiments, the DROP TABLE raises "DROP TABLE' cannot be performed on object 'T' because there is an open ResultSet dependent on that object.": java TruncateConcurrency drop same sensitive java TruncateConcurrency drop same sensitive commitTruncator java TruncateConcurrency drop same sensitive commitSelector java TruncateConcurrency drop same sensitive commitTruncator commitSelector java TruncateConcurrency drop same insensitive java TruncateConcurrency drop same insensitive commitTruncator java TruncateConcurrency drop same insensitive commitSelector java TruncateConcurrency drop same insensitive commitTruncator commitSelector 2') When the Selector and Truncator are DIFFERENT transactions, then Derby misbehaves. There are two kinds of misbehavior here. The sub-cases are distinguished by whether the Truncator committed immediately after truncating the table. Whether the Selector committed after reading its first row is also irrelevant. 2a') In the following experiments, the Selector triggers an NPE when calling ResultSet.next(): java TruncateConcurrency drop different sensitive commitTruncator java TruncateConcurrency drop different sensitive commitTruncator commitSelector java TruncateConcurrency drop different insensitive commitTruncator java TruncateConcurrency drop different insensitive commitTruncator commitSelector This is the NPE: java.lang.NullPointerException at org.apache.derby.impl.store.access.conglomerate.GenericController.setEstimatedRowCount(GenericController.java:224) at org.apache.derby.impl.sql.execute.TableScanResultSet.setRowCountIfPossible(TableScanResultSet.java:1325) at org.apache.derby.impl.sql.execute.BulkTableScanResultSet.getNextRowCore(BulkTableScanResultSet.java:311) at org.apache.derby.impl.sql.execute.ScrollInsensitiveResultSet.getNextRowFromSource(ScrollInsensitiveResultSet.java:801) at org.apache.derby.impl.sql.execute.ScrollInsensitiveResultSet.getNextRowCore(ScrollInsensitiveResultSet.java:518) at org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow(BasicNoPutResultSetImpl.java:477) at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(EmbedResultSet.java:429) at org.apache.derby.impl.jdbc.EmbedResultSet.next(EmbedResultSet.java:373) at TruncateConcurrency.printRow(TruncateConcurrency.java:188) at TruncateConcurrency.justDoIt(TruncateConcurrency.java:97) at TruncateConcurrency.main(TruncateConcurrency.java:49) 2b') The following experiments hang the Selector when calling ResultSet.next(): java TruncateConcurrency drop different sensitive java TruncateConcurrency drop different sensitive commitSelector java TruncateConcurrency drop different insensitive java TruncateConcurrency drop different insensitive commitSelector ----------- I see the following behavior for DELETE FROM: All of the test cases terminate without errors. The Selector is able to read the second row regardless of the sensitivity of the cursor and regardless of whether the read takes place in the same transaction as DELETE FROM. java TruncateConcurrency delete same sensitive java TruncateConcurrency delete same sensitive commitTruncator java TruncateConcurrency delete same sensitive commitSelector java TruncateConcurrency delete same sensitive commitTruncator commitSelector java TruncateConcurrency delete same insensitive java TruncateConcurrency delete same insensitive commitTruncator java TruncateConcurrency delete same insensitive commitSelector java TruncateConcurrency delete same insensitive commitTruncator commitSelector java TruncateConcurrency delete different sensitive java TruncateConcurrency delete different sensitive commitTruncator java TruncateConcurrency delete different sensitive commitSelector java TruncateConcurrency delete different sensitive commitTruncator commitSelector java TruncateConcurrency delete different insensitive java TruncateConcurrency delete different insensitive commitTruncator java TruncateConcurrency delete different insensitive commitSelector java TruncateConcurrency delete different insensitive commitTruncator commitSelector
          Hide
          Knut Anders Hatlen added a comment -

          FWIW, the described behaviour sounds consistent with that of DROP TABLE. I assume the NPE is similar to DERBY-979? And I also assume that the hang you saw in 2b was a lock wait that timed out after a while?

          Show
          Knut Anders Hatlen added a comment - FWIW, the described behaviour sounds consistent with that of DROP TABLE. I assume the NPE is similar to DERBY-979 ? And I also assume that the hang you saw in 2b was a lock wait that timed out after a while?
          Hide
          Rick Hillegas added a comment -

          Attaching TruncateConcurrency.java. This test program explores how TRUNCATE TABLE interacts with holdable cursors. As a result of running this program, I believe:

          i) Derby's behavior is consistent with the Standard.

          ii) However, the test uncovers other bugs.

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

          The program creates two connections, which may be the same, depending on whether "same" or "different" is specified.

          o Selector - This connection opens a holdable cursor for reading a table.

          o Truncator - This connection truncates the table.

          After creating the table and putting 2 rows in it, the program does the following:

          o Selector opens its holdable cursor (sensitive or insensitive, depending on the argument) then reads a row.

          o If commitSelector is set, Selector commits its transaction.

          o Truncator truncates the table.

          o If commitTruncator is set, Truncator commits its transaction.

          o Selector reads the remaining rows from the table.

          ---------

          Here's how to run the program:

          java TruncateConcurrency $transaction $sensitivity [ $commit ]*

          where

          $transaction = same | different (whether the reader and truncator do their work in the same transaction)
          $sensitivity = sensitive | insensitive (whether the reader should use a sensitive or insensitive cursor)
          $commit = commitSelector | commitTruncator

          If commitSelector is specified, then the Selector commits after
          reading a row but before the truncation.

          If commitTruncator is specified, then the Truncator commits
          immediately after truncation.

          E.g.:

          java TruncateConcurrency different sensitive commitTruncator commitSelector

          -----------

          I see the following behavior:

          1) When the cursor is open in the SAME transaction which truncates the table, then the TRUNCATE TABLE command fails. Sensitivity is irrelevant. Whether the transaction commits after reading the first row is also irrelevant.

          That is, in the following experiments, the TRUNCATE TABLE raises "Operation 'TRUNCATE TABLE' cannot be performed on object 'T' because there is an open ResultSet dependent on that object.":

          java TruncateConcurrency same sensitive
          java TruncateConcurrency same sensitive commitTruncator
          java TruncateConcurrency same sensitive commitSelector
          java TruncateConcurrency same sensitive commitTruncator commitSelector
          java TruncateConcurrency same insensitive
          java TruncateConcurrency same insensitive commitTruncator
          java TruncateConcurrency same insensitive commitSelector
          java TruncateConcurrency same insensitive commitTruncator commitSelector

          2) When the Selector and Truncator are DIFFERENT transactions, then Derby's behavior is bizarre. Regardless of sensitivity, after truncation the Selector is able to read the last row from the table. Then on the subsequent ResultSet.next() call, Derby misbehaves. There are two kinds of misbehavior here. The sub-cases are distinguished by whether the Truncator committed immediately after truncating the table. Whether the Selector committed after reading its first row is also irrelevant.

          2a) If the Truncator committed immediately after truncating the table, then the Selector trips across an NPE when calling ResultSet.next().

          That is, in the following experiments, the Selector triggers an NPE when calling ResultSet.next():

          java TruncateConcurrency different sensitive commitTruncator
          java TruncateConcurrency different sensitive commitTruncator commitSelector
          java TruncateConcurrency different insensitive commitTruncator
          java TruncateConcurrency different insensitive commitTruncator commitSelector

          2b) If the Truncator did NOT commit after truncating the table, then the Selector hangs on ResultSet.next().

          That is, the following experiments hang the Selector when calling ResultSet.next():

          java TruncateConcurrency different sensitive
          java TruncateConcurrency different sensitive commitSelector
          java TruncateConcurrency different insensitive
          java TruncateConcurrency different insensitive commitSelector

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

          The SQL Standard, part 2, section 14.10 <truncate table statement>, General Rules 2-4 provide guidance for case (1), that is, for when a cursor on the table is open in the same transaction which truncates the table. In this case, Derby refuses to truncate the table and raises an exception. One could quibble about the wording of the exception, but I do not think that is a serious divergence from the Standard. Derby's behavior in this case seems to be correct to me.

          The Standard does not provide guidance for case (2). I believe that we should fix Derby to behave like case (1). That is, the TRUNCATE TABLE command should raise an exception if there is a cursor open on the table.

          Show
          Rick Hillegas added a comment - Attaching TruncateConcurrency.java. This test program explores how TRUNCATE TABLE interacts with holdable cursors. As a result of running this program, I believe: i) Derby's behavior is consistent with the Standard. ii) However, the test uncovers other bugs. ------------ The program creates two connections, which may be the same, depending on whether "same" or "different" is specified. o Selector - This connection opens a holdable cursor for reading a table. o Truncator - This connection truncates the table. After creating the table and putting 2 rows in it, the program does the following: o Selector opens its holdable cursor (sensitive or insensitive, depending on the argument) then reads a row. o If commitSelector is set, Selector commits its transaction. o Truncator truncates the table. o If commitTruncator is set, Truncator commits its transaction. o Selector reads the remaining rows from the table. --------- Here's how to run the program: java TruncateConcurrency $transaction $sensitivity [ $commit ]* where $transaction = same | different (whether the reader and truncator do their work in the same transaction) $sensitivity = sensitive | insensitive (whether the reader should use a sensitive or insensitive cursor) $commit = commitSelector | commitTruncator If commitSelector is specified, then the Selector commits after reading a row but before the truncation. If commitTruncator is specified, then the Truncator commits immediately after truncation. E.g.: java TruncateConcurrency different sensitive commitTruncator commitSelector ----------- I see the following behavior: 1) When the cursor is open in the SAME transaction which truncates the table, then the TRUNCATE TABLE command fails. Sensitivity is irrelevant. Whether the transaction commits after reading the first row is also irrelevant. That is, in the following experiments, the TRUNCATE TABLE raises "Operation 'TRUNCATE TABLE' cannot be performed on object 'T' because there is an open ResultSet dependent on that object.": java TruncateConcurrency same sensitive java TruncateConcurrency same sensitive commitTruncator java TruncateConcurrency same sensitive commitSelector java TruncateConcurrency same sensitive commitTruncator commitSelector java TruncateConcurrency same insensitive java TruncateConcurrency same insensitive commitTruncator java TruncateConcurrency same insensitive commitSelector java TruncateConcurrency same insensitive commitTruncator commitSelector 2) When the Selector and Truncator are DIFFERENT transactions, then Derby's behavior is bizarre. Regardless of sensitivity, after truncation the Selector is able to read the last row from the table. Then on the subsequent ResultSet.next() call, Derby misbehaves. There are two kinds of misbehavior here. The sub-cases are distinguished by whether the Truncator committed immediately after truncating the table. Whether the Selector committed after reading its first row is also irrelevant. 2a) If the Truncator committed immediately after truncating the table, then the Selector trips across an NPE when calling ResultSet.next(). That is, in the following experiments, the Selector triggers an NPE when calling ResultSet.next(): java TruncateConcurrency different sensitive commitTruncator java TruncateConcurrency different sensitive commitTruncator commitSelector java TruncateConcurrency different insensitive commitTruncator java TruncateConcurrency different insensitive commitTruncator commitSelector 2b) If the Truncator did NOT commit after truncating the table, then the Selector hangs on ResultSet.next(). That is, the following experiments hang the Selector when calling ResultSet.next(): java TruncateConcurrency different sensitive java TruncateConcurrency different sensitive commitSelector java TruncateConcurrency different insensitive java TruncateConcurrency different insensitive commitSelector ------------ The SQL Standard, part 2, section 14.10 <truncate table statement>, General Rules 2-4 provide guidance for case (1), that is, for when a cursor on the table is open in the same transaction which truncates the table. In this case, Derby refuses to truncate the table and raises an exception. One could quibble about the wording of the exception, but I do not think that is a serious divergence from the Standard. Derby's behavior in this case seems to be correct to me. The Standard does not provide guidance for case (2). I believe that we should fix Derby to behave like case (1). That is, the TRUNCATE TABLE command should raise an exception if there is a cursor open on the table.
          Hide
          Rick Hillegas added a comment -

          Hi Dag,

          We don't have a functional spec for this feature beyond our sense about what it means to subset the brief SQL 2008 language. The concurrency issues you raise are worth probing with some tests. There could be some bugs here. At a high level, TRUNCATE TABLE is implemented as a DDL operation (more specifically as a kind of ALTER TABLE statement). That means that like other DDL, an attempt is made to invalidate other statements which reference the table in question. That would be the point at which those other statements would be able to object that an in-flight cursor blocks the DDL. Thanks.

          Show
          Rick Hillegas added a comment - Hi Dag, We don't have a functional spec for this feature beyond our sense about what it means to subset the brief SQL 2008 language. The concurrency issues you raise are worth probing with some tests. There could be some bugs here. At a high level, TRUNCATE TABLE is implemented as a DDL operation (more specifically as a kind of ALTER TABLE statement). That means that like other DDL, an attempt is made to invalidate other statements which reference the table in question. That would be the point at which those other statements would be able to object that an in-flight cursor blocks the DDL. Thanks.
          Hide
          Dag H. Wanvik added a comment -

          I haven't been following this issue closely, but allow me just a quick question.. Is it well defined what would happen for open cursors (result sets), holdable, not holdable, when a table is attempted truncated? forward-only, scrollable, updatable..

          Cf SQL 2008, section 14.10 GR 2,3 onwards.

          Note also GR 7: " If no rows are deleted from T, then a completion condition is raised: no data."

          Do we have/need a functional specification for this feature?

          Show
          Dag H. Wanvik added a comment - I haven't been following this issue closely, but allow me just a quick question.. Is it well defined what would happen for open cursors (result sets), holdable, not holdable, when a table is attempted truncated? forward-only, scrollable, updatable.. Cf SQL 2008, section 14.10 GR 2,3 onwards. Note also GR 7: " If no rows are deleted from T, then a completion condition is raised: no data." Do we have/need a functional specification for this feature?
          Hide
          Rick Hillegas added a comment -

          Attaching derby-268-02-aa-permsTest.diff, which adds a test case to verify that only the owner and the DBO can truncate a table. Committed at subversion revision 999459.

          Show
          Rick Hillegas added a comment - Attaching derby-268-02-aa-permsTest.diff, which adds a test case to verify that only the owner and the DBO can truncate a table. Committed at subversion revision 999459.
          Hide
          Knut Anders Hatlen added a comment -

          Hi Eranda,
          I think sqlgrammar.jj would be a good starting point.

          Show
          Knut Anders Hatlen added a comment - Hi Eranda, I think sqlgrammar.jj would be a good starting point.
          Hide
          Eranda Sooriyabandara added a comment -

          I like to implement IDENTITY clauses. Where should I start implementing? Is it ij.jj?

          Show
          Eranda Sooriyabandara added a comment - I like to implement IDENTITY clauses. Where should I start implementing? Is it ij.jj?
          Hide
          Rick Hillegas added a comment -

          Thanks, Bryan. I have linked this issue to a documentation follow-up task and to a cleanup task to address the code-reuse concerns. At this point, we support the following standard syntax:

          TRUNCATE TABLE tableName

          If someone has the itch, they are welcome to open a related issue to implement the optional IDENTITY clauses.

          Show
          Rick Hillegas added a comment - Thanks, Bryan. I have linked this issue to a documentation follow-up task and to a cleanup task to address the code-reuse concerns. At this point, we support the following standard syntax: TRUNCATE TABLE tableName If someone has the itch, they are welcome to open a related issue to implement the optional IDENTITY clauses.
          Hide
          Bryan Pendleton added a comment -

          I think that we should open a new JIRA to address Rick's 8-sep regarding foreign keys.

          I think we should also open a new JIRA (sub-task) to address documenting the current support.

          And, I think we should mark this issue as resolved, since the basic functionality and tests are in place.

          Show
          Bryan Pendleton added a comment - I think that we should open a new JIRA to address Rick's 8-sep regarding foreign keys. I think we should also open a new JIRA (sub-task) to address documenting the current support. And, I think we should mark this issue as resolved, since the basic functionality and tests are in place.
          Hide
          Eranda Sooriyabandara added a comment -

          Hi Rick,
          I feel that it's a lot to do with this to fix. I can work with the improvement but it take some time to fix the issue. If the this has to be fixed quickly please remove me from the assignee and start work on it.
          thanks

          Show
          Eranda Sooriyabandara added a comment - Hi Rick, I feel that it's a lot to do with this to fix. I can work with the improvement but it take some time to fix the issue. If the this has to be fixed quickly please remove me from the assignee and start work on it. thanks
          Hide
          Rick Hillegas added a comment -

          Re-using the import/replace code will allow truncate to succeed in the following edge case, which currently raises an error:

          o The truncated table is referenced by a foreign key
          o But there are no rows in the referencing table

          It seems to me that the proposal may not be appropriate for an issue flagged for a newcomer. I recommend one of the following:

          o Create a new JIRA for the improvement
          o Remove the "newcomer" flag and negotiate with Eranda about who will do the work

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

          Here is a script which demonstrates the different edge-case behavior of the truncate and import/replace code:

          connect 'jdbc:derby:memory:dummy;create=true';

          create table deltriggertest_t1(x int);
          create table deltriggertest_t2(y int);
          create trigger deltriggertest_tr after delete on deltriggertest_t1 referencing old as old for each row insert into deltriggertest_t2 values old.x;

          create table foreignkey_t1(x int primary key);
          create table foreignkey_t2(y int references foreignkey_t1);

          insert into deltriggertest_t1( x ) values ( 1 );

          insert into foreignkey_t1 values 1,2;
          insert into foreignkey_t2 values 2;

          call syscs_util.syscs_export_table ( null, 'DELTRIGGERTEST_T1', 'z.dat', null, null, null );

          – fails because of delete trigger
          truncate table deltriggertest_t1;
          – fails because of foreign key
          truncate table foreignkey_t1;

          – fails because of delete trigger
          call syscs_util.syscs_import_table( null, 'DELTRIGGERTEST_T1', 'z.dat', null, null, null,1 );
          – fails because foreign key is not satisfied
          call syscs_util.syscs_import_table( null, 'FOREIGNKEY_T1', 'z.dat', null, null, null,1 );

          delete from foreignkey_t2;
          delete from foreignkey_t1;
          delete from deltriggertest_t1;

          – fails because of delete trigger
          truncate table deltriggertest_t1;
          – fails because of foreign key
          truncate table foreignkey_t1;

          – fails because of delete trigger
          call syscs_util.syscs_import_table( null, 'DELTRIGGERTEST_T1', 'z.dat', null, null, null,1 );
          – succeeds
          call syscs_util.syscs_import_table( null, 'FOREIGNKEY_T1', 'z.dat', null, null, null,1 );

          Show
          Rick Hillegas added a comment - Re-using the import/replace code will allow truncate to succeed in the following edge case, which currently raises an error: o The truncated table is referenced by a foreign key o But there are no rows in the referencing table It seems to me that the proposal may not be appropriate for an issue flagged for a newcomer. I recommend one of the following: o Create a new JIRA for the improvement o Remove the "newcomer" flag and negotiate with Eranda about who will do the work ------------------------------ Here is a script which demonstrates the different edge-case behavior of the truncate and import/replace code: connect 'jdbc:derby:memory:dummy;create=true'; create table deltriggertest_t1(x int); create table deltriggertest_t2(y int); create trigger deltriggertest_tr after delete on deltriggertest_t1 referencing old as old for each row insert into deltriggertest_t2 values old.x; create table foreignkey_t1(x int primary key); create table foreignkey_t2(y int references foreignkey_t1); insert into deltriggertest_t1( x ) values ( 1 ); insert into foreignkey_t1 values 1,2; insert into foreignkey_t2 values 2; call syscs_util.syscs_export_table ( null, 'DELTRIGGERTEST_T1', 'z.dat', null, null, null ); – fails because of delete trigger truncate table deltriggertest_t1; – fails because of foreign key truncate table foreignkey_t1; – fails because of delete trigger call syscs_util.syscs_import_table( null, 'DELTRIGGERTEST_T1', 'z.dat', null, null, null,1 ); – fails because foreign key is not satisfied call syscs_util.syscs_import_table( null, 'FOREIGNKEY_T1', 'z.dat', null, null, null,1 ); delete from foreignkey_t2; delete from foreignkey_t1; delete from deltriggertest_t1; – fails because of delete trigger truncate table deltriggertest_t1; – fails because of foreign key truncate table foreignkey_t1; – fails because of delete trigger call syscs_util.syscs_import_table( null, 'DELTRIGGERTEST_T1', 'z.dat', null, null, null,1 ); – succeeds call syscs_util.syscs_import_table( null, 'FOREIGNKEY_T1', 'z.dat', null, null, null,1 );
          Hide
          Mike Matrigali added a comment -

          knut, that sounds like a good approach. or at least sharing more code in the same file - i would trust the existing code from working features more than the truncate code. truncate and compress should be able to share all the code that does the new conglomerate stuff, and just skip the load part. It does share some code which is why it does not have the compress bug. Eventually
          truncate might have more specific code if we implement the other syntax.

          I've been trying to think of what might be missing. Are there any tests for truncate table with sql roles/permissions. It may have been added after truncate code was added.

          Show
          Mike Matrigali added a comment - knut, that sounds like a good approach. or at least sharing more code in the same file - i would trust the existing code from working features more than the truncate code. truncate and compress should be able to share all the code that does the new conglomerate stuff, and just skip the load part. It does share some code which is why it does not have the compress bug. Eventually truncate might have more specific code if we implement the other syntax. I've been trying to think of what might be missing. Are there any tests for truncate table with sql roles/permissions. It may have been added after truncate code was added.
          Hide
          Knut Anders Hatlen added a comment -

          Mike, do you think the implementation would be safer (and perhaps also smaller) if we made it a wrapper around import/replace?

          Show
          Knut Anders Hatlen added a comment - Mike, do you think the implementation would be safer (and perhaps also smaller) if we made it a wrapper around import/replace?
          Hide
          Knut Anders Hatlen added a comment -

          I noticed that TruncateTableTest was being wrapped with TestConfiguration.sqlAuthorizationDecorator(). I assume that was unintended, since there's no use of authorization in the test. Removed it and committed revision 994970. Please shout if adding the decorator was indeed intended. Thanks.

          Show
          Knut Anders Hatlen added a comment - I noticed that TruncateTableTest was being wrapped with TestConfiguration.sqlAuthorizationDecorator(). I assume that was unintended, since there's no use of authorization in the test. Removed it and committed revision 994970. Please shout if adding the decorator was indeed intended. Thanks.
          Hide
          Knut Anders Hatlen added a comment -

          Committed revision 994946.

          Show
          Knut Anders Hatlen added a comment - Committed revision 994946.
          Hide
          Knut Anders Hatlen added a comment -

          I was going to ask whether delete triggers were supposed to be fired on truncate, but ij gave me a reasonable answer:

          ij> truncate table t1;
          ERROR XCL49: TRUNCATE TABLE is not permitted on 'T1' because it has an enabled DELETE trigger (T1_DEL).

          Similarly, it refuses to truncate a table referenced in a foreign key relationship (unless the table is only referenced by itself):

          ij> truncate table t1;
          ERROR XCL48: TRUNCATE TABLE is not permitted on 'T1' because unique/primary key constraints on this table are referenced by enabled foreign key constraints from other tables.

          I've added test cases to verify this behaviour (see attached patch tests.diff). I'll commit the patch when write access to the subversion repository has been restored (it's currently read-only because of maintenance).

          Show
          Knut Anders Hatlen added a comment - I was going to ask whether delete triggers were supposed to be fired on truncate, but ij gave me a reasonable answer: ij> truncate table t1; ERROR XCL49: TRUNCATE TABLE is not permitted on 'T1' because it has an enabled DELETE trigger (T1_DEL). Similarly, it refuses to truncate a table referenced in a foreign key relationship (unless the table is only referenced by itself): ij> truncate table t1; ERROR XCL48: TRUNCATE TABLE is not permitted on 'T1' because unique/primary key constraints on this table are referenced by enabled foreign key constraints from other tables. I've added test cases to verify this behaviour (see attached patch tests.diff). I'll commit the patch when write access to the subversion repository has been restored (it's currently read-only because of maintenance).
          Hide
          Mike Matrigali added a comment -

          to add history to this feature. Truncate table was originally implemented as a prototype and the current state of the code never went through the expected code review, testing, and planning for release. Partly the reason was the standard. But because of the standard it was never "finished". Just because it passes the existing tests does not mean the current code is right, there was very minimal testing effort originally. It is especially likely that the existing code may be missing updates that subsequent features added since the original code might need.

          One should not assume that "hidden code" will just work by enabling it.

          Has anyone read through the existing code and verified it looked right before just doing the parser change? Unfortunately the hardest bugs to find when enabling "dead" code is finding
          the code that is missing due to changes since the dead code was disabled. I didn't see anything obvious, I read the code to make sure it was not going to reintroduce db corruption of DERBY-4677.

          I would suggest more testing and at least making this feature high for buddy testing before releasing it. Maybe look at the ddl testing for offline compress table and make sure the tests also work for truncate table.

          Show
          Mike Matrigali added a comment - to add history to this feature. Truncate table was originally implemented as a prototype and the current state of the code never went through the expected code review, testing, and planning for release. Partly the reason was the standard. But because of the standard it was never "finished". Just because it passes the existing tests does not mean the current code is right, there was very minimal testing effort originally. It is especially likely that the existing code may be missing updates that subsequent features added since the original code might need. One should not assume that "hidden code" will just work by enabling it. Has anyone read through the existing code and verified it looked right before just doing the parser change? Unfortunately the hardest bugs to find when enabling "dead" code is finding the code that is missing due to changes since the dead code was disabled. I didn't see anything obvious, I read the code to make sure it was not going to reintroduce db corruption of DERBY-4677 . I would suggest more testing and at least making this feature high for buddy testing before releasing it. Maybe look at the ddl testing for offline compress table and make sure the tests also work for truncate table.
          Hide
          Rick Hillegas added a comment -

          Tests ran cleanly for me. Committed derby-268-01-ab-enableForInsaneBuilds.diff at subversion revision 993428. Thanks, Eranda.

          Show
          Rick Hillegas added a comment - Tests ran cleanly for me. Committed derby-268-01-ab-enableForInsaneBuilds.diff at subversion revision 993428. Thanks, Eranda.
          Hide
          Rick Hillegas added a comment -

          Thanks for the patch, Eranda. I am running regression tests on an insane build after making a couple changes to the test. See the attached derby-268-01-ab-enableForInsaneBuilds.diff:

          1) I put a primary key on the table so that the test would continue to test index truncation.

          2) I added ORDER BY clauses to the selects so that the query results will be deterministic.

          3) I capitalized the table name in the assertTableRowCount() call because the table name there is case-sensitive and the call was raising an exception.

          Thanks,
          -Rick

          Show
          Rick Hillegas added a comment - Thanks for the patch, Eranda. I am running regression tests on an insane build after making a couple changes to the test. See the attached derby-268-01-ab-enableForInsaneBuilds.diff: 1) I put a primary key on the table so that the test would continue to test index truncation. 2) I added ORDER BY clauses to the selects so that the query results will be deterministic. 3) I capitalized the table name in the assertTableRowCount() call because the table name there is case-sensitive and the call was raising an exception. Thanks, -Rick
          Hide
          Eranda Sooriyabandara added a comment -

          Hi Bryan,
          Yes that is the why truncate table works. After I remove that part in ant.property file, truncate table didn't work as we needed.
          thanks

          Show
          Eranda Sooriyabandara added a comment - Hi Bryan, Yes that is the why truncate table works. After I remove that part in ant.property file, truncate table didn't work as we needed. thanks
          Hide
          Eranda Sooriyabandara added a comment -

          Hi Rick,
          Here is the changes you suggest. The TruncateTableTest passed for me now. If this is satisfying you I will take a look at implementing the optional CONTINUE IDENTITY and RESTART IDENTITY clauses.

          Show
          Eranda Sooriyabandara added a comment - Hi Rick, Here is the changes you suggest. The TruncateTableTest passed for me now. If this is satisfying you I will take a look at implementing the optional CONTINUE IDENTITY and RESTART IDENTITY clauses.
          Hide
          Bryan Pendleton added a comment -

          Hi Eranda,

          Does your 'ant.properties' file contain the line 'sane=true'? If it does, then I think
          you are performing a "debug" build, and TRUNCATE TABLE is enabled.

          Try removing 'sane=true' from your ant.properties, then do 'ant clobber' and 'ant all',
          and see if TRUNCATE TABLE still works.

          The relevant code is this, in java/engine/org/apache/derby/impl/sql/compile/AlterTableNode.java

          public void init(Object objectName)
          throws StandardException
          {

          //truncate table is not suppotted in this release
          //semantics are not yet clearly defined by SQL Council yet
          //truncate will be allowed only in DEBUG builds for testing purposes.
          if (SanityManager.DEBUG)

          { initAndCheck(objectName); /* For now, this init() only called for truncate table */ truncateTable = true; schemaDescriptor = getSchemaDescriptor(); }

          else

          { throw StandardException.newException(SQLState.NOT_IMPLEMENTED, "truncate table"); }

          }

          Show
          Bryan Pendleton added a comment - Hi Eranda, Does your 'ant.properties' file contain the line 'sane=true'? If it does, then I think you are performing a "debug" build, and TRUNCATE TABLE is enabled. Try removing 'sane=true' from your ant.properties, then do 'ant clobber' and 'ant all', and see if TRUNCATE TABLE still works. The relevant code is this, in java/engine/org/apache/derby/impl/sql/compile/AlterTableNode.java public void init(Object objectName) throws StandardException { //truncate table is not suppotted in this release //semantics are not yet clearly defined by SQL Council yet //truncate will be allowed only in DEBUG builds for testing purposes. if (SanityManager.DEBUG) { initAndCheck(objectName); /* For now, this init() only called for truncate table */ truncateTable = true; schemaDescriptor = getSchemaDescriptor(); } else { throw StandardException.newException(SQLState.NOT_IMPLEMENTED, "truncate table"); } }
          Hide
          Eranda Sooriyabandara added a comment -

          Hi Rick,
          It seems truncate table is working for me in the normal mode (without setting the debug mode)

          eranda@Eranda:~/Desktop/Derby/Derby-268/trunk/tests$ java org.apache.derby.tools.ij
          ij version 10.7
          ij> connect 'jdbc:derby:MyDB';
          ij> insert into t1 values(1),(2),(3),(4),(5),(6);
          6 rows inserted/updated/deleted
          ij> select * from t1;
          A
          -----------
          1
          2
          3
          4
          5
          6

          6 rows selected
          ij> truncate table t1;
          0 rows inserted/updated/deleted
          ij> select * from t1;
          A
          -----------

          0 rows selected
          ij>

          What is the reason for this?

          Show
          Eranda Sooriyabandara added a comment - Hi Rick, It seems truncate table is working for me in the normal mode (without setting the debug mode) eranda@Eranda:~/Desktop/Derby/Derby-268/trunk/tests$ java org.apache.derby.tools.ij ij version 10.7 ij> connect 'jdbc:derby:MyDB'; ij> insert into t1 values(1),(2),(3),(4),(5),(6); 6 rows inserted/updated/deleted ij> select * from t1; A ----------- 1 2 3 4 5 6 6 rows selected ij> truncate table t1; 0 rows inserted/updated/deleted ij> select * from t1; A ----------- 0 rows selected ij> What is the reason for this?
          Hide
          Rick Hillegas added a comment -

          As Knut notes, the TRUNCATE TABLE command is defined in the 2008 SQL Standard, part 2, section 14.10 <truncate table statement>. The standard syntax is:

          TRUNCATE TABLE tableName [ identityBehavior ]

          identityBehavior ::=
          CONTINUE IDENTITY

          RESTART IDENTITY

          The default behavior is CONTINUE IDENTITY, which means that the table's identity column (if it has one) continues its sequence where it left off. RESTART IDENTITY means that the identity column resets to start again at its original initial value.

          Derby appears to implement a compatible subset of this standard syntax if you are running in debug mode:

          TRUNCATE TABLE tableName

          Derby preserves the standard CONTINUE IDENTITY default behavior.

          The following comment in AlterTableNode explains why TRUNCATE TABLE has been disabled in non-debug mode:

          //truncate table is not supported in this release
          //semantics are not yet clearly defined by SQL Council yet
          //truncate will be allowed only in DEBUG builds for testing purposes.

          I believe that was a statement about the 2003 rev of the Standard--it is no longer true. I believe that we can expose this useful command in non-debug, production mode now.

          This seems to me to be a good issue for a newcomer. It involves the following:

          1) Remove the disabling logic from the initializer of AlterTableNode.

          2) Write regression tests to verify that TRUNCATE behaves correctly. In particular, verify that CONTINUE IDENTITY semantics are enforced.

          3) Document this command in the Reference Guide.

          A follow-on effort might be to implement the optional CONTINUE IDENTITY and RESTART IDENTITY clauses. Fortunately, the tricky bit of RESTART IDENTITY has already been implemented. The tricky bit is the following implied statement which is executed after truncating the table:

          ALTER TABLE tableName ALTER COLUMN RESTART WITH initialValue

          Show
          Rick Hillegas added a comment - As Knut notes, the TRUNCATE TABLE command is defined in the 2008 SQL Standard, part 2, section 14.10 <truncate table statement>. The standard syntax is: TRUNCATE TABLE tableName [ identityBehavior ] identityBehavior ::= CONTINUE IDENTITY RESTART IDENTITY The default behavior is CONTINUE IDENTITY, which means that the table's identity column (if it has one) continues its sequence where it left off. RESTART IDENTITY means that the identity column resets to start again at its original initial value. Derby appears to implement a compatible subset of this standard syntax if you are running in debug mode: TRUNCATE TABLE tableName Derby preserves the standard CONTINUE IDENTITY default behavior. The following comment in AlterTableNode explains why TRUNCATE TABLE has been disabled in non-debug mode: //truncate table is not supported in this release //semantics are not yet clearly defined by SQL Council yet //truncate will be allowed only in DEBUG builds for testing purposes. I believe that was a statement about the 2003 rev of the Standard--it is no longer true. I believe that we can expose this useful command in non-debug, production mode now. This seems to me to be a good issue for a newcomer. It involves the following: 1) Remove the disabling logic from the initializer of AlterTableNode. 2) Write regression tests to verify that TRUNCATE behaves correctly. In particular, verify that CONTINUE IDENTITY semantics are enforced. 3) Document this command in the Reference Guide. A follow-on effort might be to implement the optional CONTINUE IDENTITY and RESTART IDENTITY clauses. Fortunately, the tricky bit of RESTART IDENTITY has already been implemented. The tricky bit is the following implied statement which is executed after truncating the table: ALTER TABLE tableName ALTER COLUMN RESTART WITH initialValue
          Hide
          Knut Anders Hatlen added a comment -

          Truncate table is now part of the SQL standard (feature F200 in SQL:2008).

          Show
          Knut Anders Hatlen added a comment - Truncate table is now part of the SQL standard (feature F200 in SQL:2008).
          Hide
          Daniel John Debrunner added a comment -

          A more precise statement might be there appears to be code that implements truncate table, its state of completion is probably unknown.

          Show
          Daniel John Debrunner added a comment - A more precise statement might be there appears to be code that implements truncate table, its state of completion is probably unknown.
          Hide
          Knut Anders Hatlen added a comment -

          Truncate table is implemented, but only enabled in debug builds. See this comment in AlterTableNode:

          //truncate table is not suppotted in this release
          //semantics are not yet clearly defined by SQL Council yet
          //truncate will be allowed only in DEBUG builds for testing purposes.

          Show
          Knut Anders Hatlen added a comment - Truncate table is implemented, but only enabled in debug builds. See this comment in AlterTableNode: //truncate table is not suppotted in this release //semantics are not yet clearly defined by SQL Council yet //truncate will be allowed only in DEBUG builds for testing purposes.
          Hide
          Satheesh Bandaram added a comment -

          Truncate table is not part of SQL standard (2003) specification... Mike Matrigali suggested using import procedure with replace option as a workaround.

          "Look at the using the import system procedure with the replace option
          set to true and the source import being empty. I believe this will
          run faster than delete, even better would be if your application had
          a set of real rows to load when you want to truncate and use the table
          again.

          Here is a link to 10.0 documentation for the system procedure:
          http://incubator.apache.org/derby/manuals/reference/sqlj120.html#HDRIMPORTPROC "

          Show
          Satheesh Bandaram added a comment - Truncate table is not part of SQL standard (2003) specification... Mike Matrigali suggested using import procedure with replace option as a workaround. "Look at the using the import system procedure with the replace option set to true and the source import being empty. I believe this will run faster than delete, even better would be if your application had a set of real rows to load when you want to truncate and use the table again. Here is a link to 10.0 documentation for the system procedure: http://incubator.apache.org/derby/manuals/reference/sqlj120.html#HDRIMPORTPROC "

            People

            • Assignee:
              Eranda Sooriyabandara
              Reporter:
              Lance Andersen
            • Votes:
              3 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development