Network Server Protocol error when select fails and "order by" is specified

• Key: DERBY-5
• Type: Bug
• Status: Closed
• Resolution: Fixed
• Priority: Major
• Assignee: A B
• Reporter: Tulika Agrawal
• Component/s: Network Server
• Affects Version/s: 10.0.2.0
• Fix Version/s: 10.1.1.0, 10.2.1.6

Description

Reporting for Army, filed on derby-dev list.

If, when using the Network Server, one tries to execute a select
statement that fails because of an SQL exception (ex. divide-by-zero),
and if an "order by" clause is specified as part of the select, the
server will throw a distributed protocol exception, instead of the
appropriate error.

Repro (using the "ij" utility)

ij> connect 'jdbc:derby:net://localhost:1527/myDB:user=u;password=p;';
ij> create table t1 (i int, j int);
0 rows inserted/updated/deleted
ij> insert into t1 values (2,0);
1 row inserted/updated/deleted

-- Without an "order by" it's fine...
-- (22012 ==> "Attempt to divide by zero.", which is fine)

ij> select {fn mod(i,j)} from t1;
1
-----------
ERROR 22012: DB2 SQL error: SQLCODE: -1, SQLSTATE: 22012, SQLERRMC: 22012

-- With an order by, it dies...

ij> select {fn mod(i,j)} from t1 order by 1;

ERROR 58009: Execution failed due to a distribution protocol error that
caused deallocation of the conversation. A DRDA Data Stream Syntax Error
was detected. Reason: 0x13

NOTES:

The problem is in the DRDAConnThread.java file, "processCommands(...)"
method, in the "case CodePoint.OPNQRY" block of code. In the case of an
SQL exception, there's a call to "writer.clearBuffer()" that is used to
ensure that _only_ an OPNQFLRM is sent back to the client, not the
OPNQRYRM and/or QRYDSC that may have been written to buffer before the
OPNQFLRM. That call to "clearBuffer" has to be replaced with something
smarter, so that instead of doing a full clear (which causes the problem
shown above), it only backs out the buffer writes that it has made since
beginning the "case CodePoint.OPNQRY" block...

Comments

[A B]

With svn rev 55942 (which was the fix for Derby-35), it turns out that this bug (Derby-5) is now much more likely to occur. The reason is because the chaining fixes for Derby-5 will keep everything belonging to the same chain within an outgoing buffer until the chain ends or the buffer is full (which is the _correct_ behavior), at which point the buffer's contents will be transmitted.

As a result, whenever we hit the error block of code mentioned in the bug description for _this_ bug, we are more likely to have data stored in the buffer, and that means that the incorrect call to "writer.clearBuffer" is more likely to do the wrong thing.

So that said, I guess this will be the next bug I work on...

[A B]

This issue has been "quick fixed" with svn rev 56215. However, I'm not calling it "resolved" yet because there is still a possible usage scenario where this fix won't work--namely, in cases where the buffer that we're trying to "clear" has already been transmitted to the client, thus making it impossible to "undo" the write. Need to look at this and figure out 1) if that scenario is actually possible, and 2) if so, how do we fix it?

NOTE: This scenario was a problem even before rev 56215 was submitted, so we're still better off now than we were before. Now, at least, simple cases like the one mentioned in the bug description should work...

[A B]

The specific issue described in this Jira entry was fixed a while ago by svn revision 56215. I've created a new Jira issue (DERBY-529) for the remaining use case described in the previous comment, and so am marking this issue as resolved and closing it. See DERBY-529 for tracking of the remaining use case.

[Kathey Marsden]

reopen to fix fix version