Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <449538100.1248403275390.JavaMail.jira@brutus>
Date: Thu, 23 Jul 2009 19:41:15 -0700 (PDT)
From: "Bryan Pendleton (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-4282) strange behavior with the "update
 ... where current of c1" in the CheckConstraintTest
In-Reply-To: <888518209.1245484327332.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


    [ https://issues.apache.org/jira/browse/DERBY-4282?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12734875#action_12734875 ] 

Bryan Pendleton commented on DERBY-4282:
----------------------------------------

I spent some time stepping through this code and trying to figure out what's going on.
Here are some notes on what I learned:

1) Firstly, here are some observations on possible workarounds:
     a) If we change the line
                      st.executeUpdate("update t1 set c1 = c1 where current of \"c1\"");
          to
                      st.executeUpdate("update t1 set c1 = c1,c2=c2 where current of \"c1\"");
          then the problem does not occur
     b) If we, alternately, change the line
                        ResultSet rs = st1.executeQuery("select * from t1 for update");
          to
                        ResultSet rs = st1.executeQuery("select * from t1 for update of c1,c2");
          then the problem does not occur.
     c) Similarly, if we change
                        ResultSet rs = st1.executeQuery("select * from t1 for update");
          to
                        ResultSet rs = st1.executeQuery("select * from t1 for update of c1");
          then the problem does not occur.

2) Secondly, if we change
                        ResultSet rs = st1.executeQuery("select * from t1 for update");
          to
                        ResultSet rs = st1.executeQuery("select * from t1 for update of c2");
   Then we get, correctly I believe, the following error:

           Exception in thread "main" java.sql.SQLSyntaxErrorException: Column 'C1' is not
           in the FOR UPDATE list of cursor 'c1'.

Regardless of how we fix this Jira issue, I think that the 4 above test cases, as well
as the test case in the issue description, should be added to our regression suites.

3) I spent some time stepping through the code in the debugger, and I believe that the
   problem arises around line 435 of UpdateNode.java, where we find this code:

	/*
	** Add the "after" portion of the result row.  This is the update
	** list augmented to include every column in the target table.
	** Those columns that are not being updated are set to themselves.
	** The expanded list will be in the order of the columns in the base
	** table.
	*/
	afterColumns = resultSet.getResultColumns().expandToAll(
				targetTableDescriptor,
				targetTable.getTableName());

   We then arrive at line 2546 of ResultColumnList.java, where we find this code:

	/* Build a ResultColumn/ColumnReference pair for the column */
	rc = makeColumnReferenceFromName( tableName, cd.getColumnName() );

   And then we get to line 4070 of ResultColumnList.java, where we find this code:

		ResultColumn	rc = (ResultColumn) nodeFactory.getNode
			(
				C_NodeTypes.RESULT_COLUMN,
				null,
				nodeFactory.getNode
				(
					C_NodeTypes.COLUMN_REFERENCE,
					columnName,
					tableName,
					cm
				),
				cm
			);

I think that the problem involves the 'null' that we pass as the first argument
to the ResultColumn initialization logic, because this causes the generated 'C2' column
that is pulled from the 'select *' cursor into the 'update' statement to be pulled
as an *UNNAMED* column, which is (later) transformed into a column named 'SQLCOL1',
and ends up resulting in a bind failure for the CHECK constraint, which can't find
column 'C2' in the ResultColumnList, finding instead the columns 'C1' and 'SQLCOL1'.

I think the fix may be as simple as changing 'null' to 'columnName', as in this diff:

Index: java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java (revision 787523)
+++ java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java (working copy)
@@ -4057,7 +4057,7 @@
                ResultColumn    rc = (ResultColumn) nodeFactory.getNode
                        (
                                C_NodeTypes.RESULT_COLUMN,
-                               null,
+                               columnName,
                                nodeFactory.getNode
                                (
                                        C_NodeTypes.COLUMN_REFERENCE,

I did some very simple testing, and this appeared to fix the reproduction case, but
I have not run any other regression tests.

I think that the next steps here ought to be to construct a proper patch, containing:
   1) the code change to ResultColumnList.java
   2) the necessary changes to CheckConstraintTest.java to include the bug script
      from the issue description, together with the additional test cases I
      mentioned above
and then to run a complete set of regression tests to see if the code change
causes any other problems with Derby.


> strange behavior with the "update ... where current of c1" in the CheckConstraintTest
> -------------------------------------------------------------------------------------
>
>                 Key: DERBY-4282
>                 URL: https://issues.apache.org/jira/browse/DERBY-4282
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.0.2.1, 10.1.3.1, 10.2.2.0, 10.3.3.0, 10.4.2.0, 10.5.1.1
>            Reporter: Eranda Sooriyabandara
>            Assignee: Eranda Sooriyabandara
>            Priority: Critical
>             Fix For: 10.5.1.1
>
>         Attachments: cons.java
>
>
> import java.sql.*;
> public class cons
> {
>    public static void main(String []args)
>        throws Exception
>    {
>        Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
>        Connection conn =
>            DriverManager.getConnection("jdbc:derby:testdb;create=true");
>        Statement st = conn.createStatement();
>        st.executeUpdate(
>            "create table t1(c1 int, c2 int, constraint ck1 "
>            + "check(c1 = c2), constraint ck2 check(c2=c1))");
>        st.executeUpdate("insert into t1 values (1, 1),(2, 2),(3, 3),(4, 4)");
>        Statement st1=conn.createStatement();
>        st1.setCursorName("c1");
>        ResultSet rs = st1.executeQuery("select * from t1 for update");
>        rs.next();
>        st.executeUpdate("update t1 set c1 = c1 where current of \"c1\"");
>    }
> }
> Exception in thread "main" java.sql.SQLException: Column 'C2' is either not in any 
> table in the FROM list or appears within a join specification and is outside
> the scope of the join specification or appears in a HAVING clause and is not in
> the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'C2' is not
>  a column in the target table.
>        at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExc
> eptionFactory.java:45)
>        at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:201)
>        at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException
> (TransactionResourceImpl.java:391)
>        at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Tr
> ansactionResourceImpl.java:346)
>        at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConne
> ction.java:2201)
>        at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Connection
> Child.java:81)
>        at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java
> :614)
>        at org.apache.derby.impl.jdbc.EmbedStatement.executeUpdate(EmbedStatemen
> t.java:175)
>        at cons.main(cons.java:25)
> Caused by: ERROR 42X04: Column 'C2' is either not in any table in the FROM list
> or appears within a join specification and is outside the scope of the join spec
> - Show quoted text -
>        ... 2 more

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.