Activity

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.

Satheesh Bandaram
added a comment - 11/May/05 06:48 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 "

Knut Anders Hatlen
added a comment - 25/Jan/08 10:35 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.

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:

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:

Rick Hillegas
added a comment - 03/Sep/10 16:28 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

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)

Bryan Pendleton
added a comment - 04/Sep/10 16:15 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");
}
}

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.

Eranda Sooriyabandara
added a comment - 04/Sep/10 16:24 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.

Eranda Sooriyabandara
added a comment - 04/Sep/10 17:01 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

Rick Hillegas
added a comment - 07/Sep/10 16:23 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

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.

Mike Matrigali
added a comment - 07/Sep/10 19:20 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.

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).

Knut Anders Hatlen
added a comment - 08/Sep/10 09:53 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).

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.

Knut Anders Hatlen
added a comment - 08/Sep/10 11:19 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.

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.

Mike Matrigali
added a comment - 08/Sep/10 17:38 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.

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

Eranda Sooriyabandara
added a comment - 16/Sep/10 13:31 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

Bryan Pendleton
added a comment - 16/Sep/10 15:44 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.

Rick Hillegas
added a comment - 16/Sep/10 16:51 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.

Rick Hillegas
added a comment - 21/Sep/10 16:18 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.

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."

Dag H. Wanvik
added a comment - 21/Sep/10 19:54 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?

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.

Rick Hillegas
added a comment - 21/Sep/10 20:53 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.

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.

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.":

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():

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.

Rick Hillegas
added a comment - 24/Sep/10 18:57 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.

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?

Knut Anders Hatlen
added a comment - 24/Sep/10 19:48 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?

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.

$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.

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.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():

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.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():

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.

Rick Hillegas
added a comment - 27/Sep/10 16:58 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

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.

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.

Rick Hillegas
added a comment - 28/Sep/10 14:55 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.

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

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.

Rick Hillegas
added a comment - 29/Sep/10 14:04 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

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.

Rick Hillegas
added a comment - 30/Sep/10 20:52 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.

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.

Dag H. Wanvik
added a comment - 04/Oct/10 16:03 > 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.

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

Eranda Sooriyabandara
added a comment - 10/Oct/10 19:48 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

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.

Rick Hillegas
added a comment - 11/Oct/10 14:48 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

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

Eranda Sooriyabandara
added a comment - 18/Nov/10 17:13 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

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.

Rick Hillegas
added a comment - 18/Nov/10 20:52 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

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.

Eranda Sooriyabandara
added a comment - 15/Jan/11 06:35 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

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:

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

Eranda Sooriyabandara
added a comment - 21/Jan/11 08:11 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

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.

Rick Hillegas
added a comment - 11/Feb/11 17:16 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

Myrna van Lunteren
added a comment - 16/Aug/11 19:12 - 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.

Eranda Sooriyabandara
added a comment - 03/Sep/11 18:28 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