You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Myrna van Lunteren (JIRA)" <ji...@apache.org> on 2009/09/10 00:57:57 UTC
[jira] Created: (DERBY-4373) different results with network server
vs. embedded on select from a temporary table with resultset cursor hold
over commit
different results with network server vs. embedded on select from a temporary table with resultset cursor hold over commit
--------------------------------------------------------------------------------------------------------------------------
Key: DERBY-4373
URL: https://issues.apache.org/jira/browse/DERBY-4373
Project: Derby
Issue Type: Bug
Components: JDBC
Affects Versions: 10.6.0.0
Reporter: Myrna van Lunteren
Found this during review of conversion of declareGlobalTempTableJavaJDBC30 to junit (DERBY-2895) - when I tried to run the test with network server:
We define a statement like so:
Statement s1 = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY,
ResultSet.HOLD_CURSORS_OVER_COMMIT );
and global temp table like so:
s1.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit delete rows not logged");
Then, we insert 2 rows, open a result set that selects *, then do commit.
With a new resultset, we do another select, which with network server gives 0 rows, but with embedded, 2.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4373) different results with network
server vs. embedded on select from a temporary table with resultset cursor
hold over commit
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4373?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12761027#action_12761027 ]
Dag H. Wanvik commented on DERBY-4373:
--------------------------------------
Interesting. So, what is the right behavior here? It would seem that for a new result set, the temp table should be empty ("on commit delete rows"). The old cursor could still see rows if prefetched, perhaps.
> different results with network server vs. embedded on select from a temporary table with resultset cursor hold over commit
> --------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4373
> URL: https://issues.apache.org/jira/browse/DERBY-4373
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.6.0.0
> Reporter: Myrna van Lunteren
> Attachments: repro_d4373.java
>
>
> Found this during review of conversion of declareGlobalTempTableJavaJDBC30 to junit (DERBY-2895) - when I tried to run the test with network server:
> We define a statement like so:
> Statement s1 = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY,
> ResultSet.HOLD_CURSORS_OVER_COMMIT );
> and global temp table like so:
> s1.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit delete rows not logged");
> Then, we insert 2 rows, open a result set that selects *, then do commit.
> With a new resultset, we do another select, which with network server gives 0 rows, but with embedded, 2.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4373) different results with network server
vs. embedded on select from a temporary table with resultset cursor hold
over commit
Posted by "Myrna van Lunteren (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4373?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Myrna van Lunteren updated DERBY-4373:
--------------------------------------
Attachment: repro_d4373.java
> different results with network server vs. embedded on select from a temporary table with resultset cursor hold over commit
> --------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4373
> URL: https://issues.apache.org/jira/browse/DERBY-4373
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.6.0.0
> Reporter: Myrna van Lunteren
> Attachments: repro_d4373.java
>
>
> Found this during review of conversion of declareGlobalTempTableJavaJDBC30 to junit (DERBY-2895) - when I tried to run the test with network server:
> We define a statement like so:
> Statement s1 = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY,
> ResultSet.HOLD_CURSORS_OVER_COMMIT );
> and global temp table like so:
> s1.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit delete rows not logged");
> Then, we insert 2 rows, open a result set that selects *, then do commit.
> With a new resultset, we do another select, which with network server gives 0 rows, but with embedded, 2.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4373) different results with network server
vs. embedded on select from a temporary table with resultset cursor hold
over commit
Posted by "Myrna van Lunteren (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4373?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Myrna van Lunteren updated DERBY-4373:
--------------------------------------
Bug behavior facts: [Embedded/Client difference, Wrong query result]
> different results with network server vs. embedded on select from a temporary table with resultset cursor hold over commit
> --------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4373
> URL: https://issues.apache.org/jira/browse/DERBY-4373
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.6.0.0
> Reporter: Myrna van Lunteren
> Attachments: repro_d4373.java
>
>
> Found this during review of conversion of declareGlobalTempTableJavaJDBC30 to junit (DERBY-2895) - when I tried to run the test with network server:
> We define a statement like so:
> Statement s1 = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY,
> ResultSet.HOLD_CURSORS_OVER_COMMIT );
> and global temp table like so:
> s1.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit delete rows not logged");
> Then, we insert 2 rows, open a result set that selects *, then do commit.
> With a new resultset, we do another select, which with network server gives 0 rows, but with embedded, 2.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4373) different results with network
server vs. embedded on select from a temporary table with resultset cursor
hold over commit
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4373?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12761442#action_12761442 ]
Dag H. Wanvik commented on DERBY-4373:
--------------------------------------
Looking at the repro, the "new result set" that varies between drivers is a COUNT(*) of the tmp table *after* one call to next() and then commit. Maybe for the embedded case, a cursor is still open on tmp since one row has not been read yet, so rows have not gone away, whereas for the client, all rows have been prefetched, so the engine was free to remove the rows? If anything, I think embedded shows wrong behavior here: the commit *has* happened, so it's ok the delete the rows, I think.
> different results with network server vs. embedded on select from a temporary table with resultset cursor hold over commit
> --------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4373
> URL: https://issues.apache.org/jira/browse/DERBY-4373
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.6.0.0
> Reporter: Myrna van Lunteren
> Attachments: repro_d4373.java
>
>
> Found this during review of conversion of declareGlobalTempTableJavaJDBC30 to junit (DERBY-2895) - when I tried to run the test with network server:
> We define a statement like so:
> Statement s1 = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY,
> ResultSet.HOLD_CURSORS_OVER_COMMIT );
> and global temp table like so:
> s1.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit delete rows not logged");
> Then, we insert 2 rows, open a result set that selects *, then do commit.
> With a new resultset, we do another select, which with network server gives 0 rows, but with embedded, 2.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4373) different results with network
server vs. embedded on select from a temporary table with resultset cursor
hold over commit
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4373?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12917553#action_12917553 ]
Knut Anders Hatlen commented on DERBY-4373:
-------------------------------------------
Thanks for analyzing this issue, Mamta.
>From you description, it sounds to me like what the network client does is not necessarily wrong, just different. Since the forward-only result set has been exhausted, the caller cannot actually use the result set anymore, and that's why the server thinks it's ok to close the cursor earlier.
The lifetime of a cursor may differ between client and embedded in both ends. The client will start fetching rows from the cursor on executeQuery(), whereas embedded won't do it until next() is called. And the client driver will release the resources held by a forward-only result set when the last row has been fetched, whereas the embedded driver waits until ResultSet.close() is called. These differences cause some differences in when locks are obtained and released, but they also make it possible to save two round-trips between the client and the server for many SELECT statements.
I'm willing to accept that client and embedded behave differently on the boundaries in this case. So I'd be satisfied with a solution that makes the test expect a difference between client and embedded (with an explanation in a comment), or one that makes the test use a result set that's not exhausted when testing that an open held cursor prevents emptying the temporary table. But if you still want to make the drivers behave the same way on the boundaries, please make sure that you don't add extra client/server round-trips on close for queries that don't access temporary tables.
> different results with network server vs. embedded on select from a temporary table with resultset cursor hold over commit
> --------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4373
> URL: https://issues.apache.org/jira/browse/DERBY-4373
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.6.1.0
> Reporter: Myrna van Lunteren
> Attachments: repro_d4373.java
>
>
> Found this during review of conversion of declareGlobalTempTableJavaJDBC30 to junit (DERBY-2895) - when I tried to run the test with network server:
> We define a statement like so:
> Statement s1 = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY,
> ResultSet.HOLD_CURSORS_OVER_COMMIT );
> and global temp table like so:
> s1.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit delete rows not logged");
> Then, we insert 2 rows, open a result set that selects *, then do commit.
> With a new resultset, we do another select, which with network server gives 0 rows, but with embedded, 2.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4373) different results with network
server vs. embedded on select from a temporary table with resultset cursor
hold over commit
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4373?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12917126#action_12917126 ]
Mamta A. Satoor commented on DERBY-4373:
----------------------------------------
It appears that the embedded behavior is correct. Following is the explanation of the behavior of the embedded and network server(one thing to note is that the Derby resultsets by default are held open over commit. In the repro, all the resultsets are created with the default holdability).
The relevant part of the attached repro can be broken down to following steps
1)autocommit is set to false
2)declare global temp table with on commit delete rows
3)create resultset rs1tmp
4)rs1tmp.next
5)rs1tmp.close
6)create resultset rs1
7)rs1.next
8)commit
9)create resultset rs5tmp
As we can see in the above sequence of steps, the second resultset rs1 is opened(step 6) before commit and the user never explicitly closes that resultset before the commit(step 8).
In the embedded mode, during the commit(step 8), we check if there are any open resultsets over a temporary table and if yes, then even though the temporary table has been defined to delete the rows on commit, it will not delete the rows because of that open resultset. Because of that, after the commit(step 8), when a new resultset is created(step 9) on that temporary table, it still finds all the rows intact in the temporary table.
Network server on the other hand tries to do optimization with the closing of the resultset. For the steps above, when the first resultset rs1tmp is created(step 3), network server reads all the data and since it is a forward only resultset, it closes the resultset even before client requested close(step 5) of that resultset. Next, when the resultset rs1 is created(step 6), network server reads all the data for that resultset and it closes this resultset too (without the explicit request from the client to close it). Next when commit(step 8) is issued by the client, we check if there are any open resultsets over the temporary table and of course, in case of network server, we don't find any because network server went ahead and closed them and hence all the rows from the temporary table get deleted. It seems like that the network server should not close the resultset implicitly if they are supposed to be held open over commit. I will get that logic a try and see what happens. I will highly appreciate if there is any feedback on my analysis or the approach to fix the problem.
> different results with network server vs. embedded on select from a temporary table with resultset cursor hold over commit
> --------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4373
> URL: https://issues.apache.org/jira/browse/DERBY-4373
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.6.1.0
> Reporter: Myrna van Lunteren
> Attachments: repro_d4373.java
>
>
> Found this during review of conversion of declareGlobalTempTableJavaJDBC30 to junit (DERBY-2895) - when I tried to run the test with network server:
> We define a statement like so:
> Statement s1 = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY,
> ResultSet.HOLD_CURSORS_OVER_COMMIT );
> and global temp table like so:
> s1.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit delete rows not logged");
> Then, we insert 2 rows, open a result set that selects *, then do commit.
> With a new resultset, we do another select, which with network server gives 0 rows, but with embedded, 2.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4373) different results with network
server vs. embedded on select from a temporary table with resultset cursor
hold over commit
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4373?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12761199#action_12761199 ]
Knut Anders Hatlen commented on DERBY-4373:
-------------------------------------------
If the prefetching was causing this, I'd expect the result to be the opposite (2 rows with network server, 0 rows with embedded). The client driver has prefetched rows on executeQuery() since DERBY-822, whereas embedded normally doesn't start fetching until ResultSet.next() is called.
But if I understand the description correctly (the "with a new resultset" part in particular), the result sets that give different results on network/embedded are created after commit, so there is no way the prefetching could have happened before commit.
> different results with network server vs. embedded on select from a temporary table with resultset cursor hold over commit
> --------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4373
> URL: https://issues.apache.org/jira/browse/DERBY-4373
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.6.0.0
> Reporter: Myrna van Lunteren
> Attachments: repro_d4373.java
>
>
> Found this during review of conversion of declareGlobalTempTableJavaJDBC30 to junit (DERBY-2895) - when I tried to run the test with network server:
> We define a statement like so:
> Statement s1 = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY,
> ResultSet.HOLD_CURSORS_OVER_COMMIT );
> and global temp table like so:
> s1.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit delete rows not logged");
> Then, we insert 2 rows, open a result set that selects *, then do commit.
> With a new resultset, we do another select, which with network server gives 0 rows, but with embedded, 2.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.