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 "George Baklarz (JIRA)" <de...@db.apache.org> on 2005/03/10 17:39:53 UTC
[jira] Created: (DERBY-171) Need Correlation ID in UPDATE/DELETE statements
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
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
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-171) Need Correlation ID in UPDATE/DELETE statements
Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-171?page=all ]
Rick Hillegas updated DERBY-171:
--------------------------------
Description:
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!
was:
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!
Bug 156 appears to be a duplicate of this bug and so should be satisfied by this bugfix.
> 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
[jira] Updated: (DERBY-171) Need Correlation ID in UPDATE/DELETE statements
Posted by "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
Second rev of bugfix. Incorporates Army's feedback: 1) Removes FromBaseTable.java, which had a vacuous diff, 2) Moves regression tests into update.sql and delete.sql.
> 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
[jira] Resolved: (DERBY-171) Need Correlation ID in UPDATE/DELETE statements
Posted by "David Van Couvering (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-171?page=all ]
David Van Couvering resolved DERBY-171:
---------------------------------------
Resolution: Fixed
Patch built successfully, derbyall passed. Committed changes, revision 231366
> 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.1.1.1
> Environment: Windows XP SP1 Professional
> Reporter: George Baklarz
> Assignee: Rick Hillegas
> Fix For: 10.1.1.1
> 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
[jira] Updated: (DERBY-171) Need Correlation ID in UPDATE/DELETE statements
Posted by "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
[jira] Updated: (DERBY-171) Need Correlation ID in UPDATE/DELETE statements
Posted by "David Van Couvering (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-171?page=all ]
David Van Couvering updated DERBY-171:
--------------------------------------
Fix Version: 10.1.1.1
Version: 10.1.1.1
(was: 10.0.2.0)
> 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.1.1.1
> Environment: Windows XP SP1 Professional
> Reporter: George Baklarz
> Assignee: Rick Hillegas
> Fix For: 10.1.1.1
> 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
[jira] Updated: (DERBY-171) Need Correlation ID in UPDATE/DELETE statements
Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-171?page=all ]
Daniel John Debrunner updated DERBY-171:
----------------------------------------
Fix Version: 10.2.0.0
(was: 10.1.2.0)
This was marked as fixed in 10.1.2, but the change was made to the trunk after 10.1 branch was created so the correct checkin is 10.2.0.0. Since this is an improvement and changes the syntax, I do not believe it is suitable for merging into the 10.1 branch.
> 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.1.2.0
> Environment: Windows XP SP1 Professional
> Reporter: George Baklarz
> Assignee: Rick Hillegas
> Fix For: 10.2.0.0
> 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
[jira] Assigned: (DERBY-171) Need Correlation ID in UPDATE/DELETE statements
Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-171?page=all ]
Rick Hillegas reassigned DERBY-171:
-----------------------------------
Assign To: Rick Hillegas
Grabbing this bug.
> 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
>
> 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