db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Eranda Sooriyabandara (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-4282) strange behavior with the "update ... where current of c1" in the CheckConstraintTest
Date Tue, 11 Aug 2009 04:59:14 GMT

     [ https://issues.apache.org/jira/browse/DERBY-4282?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Eranda Sooriyabandara updated DERBY-4282:
-----------------------------------------

    Attachment: DERBY4282.diff

Hi Bryan,
I test with your update and found some problems,

1.When I use the code as,

Statement st1 = conn.createStatement();
        st1.setCursorName("c1");
        ResultSet rs1 = st1.executeQuery("elect * from t1 where c2 = 2 for
update of c1");
        rs1.next();
        st.executeUpdate("update t1 set c1 = c1 where current of \"c1\"");
        assertStatementError("23513", st,
            "update t1 set c1 = c1 + 1 where current of \"c1\"");

it is successfully working but when I change the code as,

Statement st1 = conn.createStatement();
        st1.setCursorName("c1");
        ResultSet rs1 = st1.executeQuery("select * from t1 where c2 = 2 for
update of c1");
        rs1.next();
        expRS=new String[][]{
            {"2","2"}
        };
        JDBC.assertFullResultSet(rs1, expRS);
        st.executeUpdate("update t1 set c1 = c1 where current of \"c1\"");
        assertStatementError("23513", st,
            "update t1 set c1 = c1 + 1 where current of \"c1\"");

It gave me the error "unexpected row count:expected<1> but was<0>"  in the
line of "JDBC.assertFullResultSet(rs1, expRS);";.

I think this is because in the JDBC they called the rs.next() until it's
become false. So the cursor ends from there.
When we reach the line "st.executeUpdate("update t1 set c1 = c1 where
current of \"c1\"");" cursor not available. So we can't use both of them
together. I thought to skip the part of,
        expRS=new String[][]{
            {"2","2"}
        };
        JDBC.assertFullResultSet(rs1, expRS);
from the code.

So I try a method which successful at the end.I send it as a patch file
here.
If it is fine for you then I can do as the same thing for the rest of the
commented codes.


> 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, DERBY4282.diff, DERBY4282.diff
>
>
> 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.


Mime
View raw message