[jira] Updated: (DERBY-171) Need Correlation ID in UPDATE/DELETE statements

["Rick Hillegas (JIRA)" <de...@db.apache.org>]

     [ http://issues.apache.org/jira/browse/DERBY-171?page=all ]

Rick Hillegas updated DERBY-171:
--------------------------------

    Attachment: bug171.diff
                bug171.html

I am attaching the subversion diffs for my fix to this bug: bug171.diff. I am also attaching a description of the changes which need to be made to the Derby Reference manual: bug171.html. This bugfix affects the following files:

M      trunk\java\engine\org\apache\derby\impl\sql\compile\FromTable.java
M      trunk\java\engine\org\apache\derby\impl\sql\compile\FromSubquery.java
M      trunk\java\engine\org\apache\derby\impl\sql\compile\SelectNode.java
M      trunk\java\engine\org\apache\derby\impl\sql\compile\ResultColumn.java
M      trunk\java\engine\org\apache\derby\impl\sql\compile\DeleteNode.java
M      trunk\java\engine\org\apache\derby\impl\sql\compile\UpdateNode.java
M      trunk\java\engine\org\apache\derby\impl\sql\compile\FromList.java
M      trunk\java\engine\org\apache\derby\impl\sql\compile\FromBaseTable.java
M      trunk\java\engine\org\apache\derby\impl\sql\compile\CurrentOfNode.java
M      trunk\java\engine\org\apache\derby\impl\sql\compile\sqlgrammar.jj
M      trunk\java\engine\org\apache\derby\impl\sql\compile\ColumnReference.java
A      trunk\java\testing\org\apache\derbyTesting\functionTests\tests\lang\bug171.sql
M      trunk\java\testing\org\apache\derbyTesting\functionTests\tests\lang\refActions1.sql
M      trunk\java\testing\org\apache\derbyTesting\functionTests\tests\lang\copyfiles.ant
M      trunk\java\testing\org\apache\derbyTesting\functionTests\master\refActions1.out
A      trunk\java\testing\org\apache\derbyTesting\functionTests\master\bug171.out
M      trunk\java\testing\org\apache\derbyTesting\functionTests\suites\derbylang.runall

I ran derbyall and it reported one failed test: NSinSameJVM. I am ignoring this failure because this test failed the same way on a clean snapshot of the repository before I made my changes.

I have added a new regression test (lang/bug171.sql) to track this bugfix.

I have added the optional correlation name clauses to the UPDATE and DELETE productions in the parser and added supporting bind-time logic. In addition to fixing this particular bug, I have significantly changed  the binding of correlated subqueries which have GROUP BY or HAVING clauses: I am now passing the outer fromList context down the subquery binding stack. This makes it possible to bind correlated references in those subqueries and fixes a cluster of other bugs. These other correlated subqueries were failing to compile because the bind logic did not match the way that the parser rewrites the query tree in these cases. I tripped across these problems in the lang/refActions1.sql regression test. I have extensively updated the canon for that test. Looking at the old canon, it appears to me that the old canon was riddled with incorrect results.

> Need Correlation ID in UPDATE/DELETE statements
> -----------------------------------------------
>
>          Key: DERBY-171
>          URL: http://issues.apache.org/jira/browse/DERBY-171
>      Project: Derby
>         Type: Improvement
>   Components: SQL
>     Versions: 10.0.2.0
>  Environment: Windows XP SP1 Professional
>     Reporter: George Baklarz
>     Assignee: Rick Hillegas
>  Attachments: bug171.diff, bug171.html
>
> You currently can't use a correlation ID in an UPDATE/DELETE statement. This makes it cumbersome to do the following:
> UPDATE EMPLOYEE_BONUS
>   SET BONUS = (SELECT SUM(BONUSES.BONUS) FROM BONUSES WHERE 
>           EMPLOYEE_BONUS.EMPL_ID = BONUSES.EMPL_ID);
> The use of a correlation ID makes this easier to code.
> UPDATE EMPLOYEE E
>   SET BONUS = (SELECT SUM(B.BONUS) FROM BONUSES B WHERE B.EMPL_ID = E.EMPL_ID);
> This is particularly important if you get carried away with long SCHEMA and TABLE names!

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira