db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bpendle...@apache.org
Subject svn commit: r1741380 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/execute/UpdateResultSet.java testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.java
Date Thu, 28 Apr 2016 02:39:00 GMT
Author: bpendleton
Date: Thu Apr 28 02:38:59 2016
New Revision: 1741380

URL: http://svn.apache.org/viewvc?rev=1741380&view=rev
Log:
DERBY-6880: Update failing with java.sql.SQLDataException

This change reverts part of the changes made by revision 1628596 for DERBY-6742.

Specifically, the section of code added to UpdateResultSet.collectAffectedRows
is removed. That code caused problems with certain SQL UPDATE statements
which were compiled with Statement.RETURN_GENERATED_KEYS. The new test cases
added by this change include several examples of such SQL statements.

The JDBC documentation for the intended behavior of UPDATE statements with the
RETURN_GENERATED_KEYS option is unclear; the intended behavior is much
clearer with INSERT statements. Given that I don't understand the intended
behavior, it seems safer to me to return Derby to the previous state for
UPDATE statements; namely, that no attempt is made to compute the set of
generated keys for an UPDATE statement.



Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/UpdateResultSet.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/UpdateResultSet.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/UpdateResultSet.java?rev=1741380&r1=1741379&r2=1741380&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/UpdateResultSet.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/UpdateResultSet.java
Thu Apr 28 02:38:59 2016
@@ -496,44 +496,6 @@ class UpdateResultSet extends DMLWriteGe
 
         while ( row != null )
         {
-            // Collect auto-generated keys if requested.
-            // DERBY-5823: No need to collect them if there are no
-            // auto-generated key columns.
-            if (activation.getAutoGeneratedKeysResultsetMode() &&
-                    autoGeneratedKeysColumnIndexes.length > 0) {
-                autoGeneratedKeysRowsHolder.insert(
-                        getCompactRow(row, autoGeneratedKeysColumnIndexes));
-                /*
-                 * find the value of the identity column. This could either
-                 * have been generated by Derby or supplied by user(user's
-                 * can supply value for "generated by default as identity").
-                 * In both cases, the value can be found in the row. Save
-                 * that value in identityVal. This locally saved value
-                 * is made available to JDBC Statement.RETURN_GENERATED_KEYS 
-                 * or IDENTITY_LOCAL_VAL function only if the UPDATE statement
-                 * has updated only one row. This implementation is as per the
-                 * JDBC spec
-                 */
-                TableDescriptor td = lcc.getDataDictionary().getTableDescriptor(constants.targetUUID);
-            		
-                int maxColumns = td.getMaxColumnID();
-                int col;
-                    
-                for(col=1;col<=maxColumns;col++)
-                {
-                    ColumnDescriptor cd = td.getColumnDescriptor(col);
-                    if(cd.isAutoincrement())
-                    {
-                        break;
-                    }
-                }
-                    
-                if(col <= maxColumns)
-                {
-                    DataValueDescriptor dvd = row.cloneColumn(col);
-                    identityVal = dvd.getLong();
-                }
-            }
             evaluateGenerationClauses( generationClauses, activation, sourceResultSet, row,
true );
 
 			/* By convention, the last column in the result set for an

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.java?rev=1741380&r1=1741379&r2=1741380&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.java
Thu Apr 28 02:38:59 2016
@@ -5905,6 +5905,132 @@ public class GeneratedColumnsTest extend
              );
     }
 
+    public void test_6880_return_keys()
+            throws SQLException
+    {
+        Connection  conn = getConnection();
+        Statement s = createStatement();
+
+        s.execute("create table pipeline(" +
+                  "  id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY " +
+                  "     (START WITH 1, INCREMENT BY 1) primary key, " +
+                  "  name varchar(15) )");
+        s.execute("create table pipeline_command ( " +
+                  "  id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY " +
+                  "     (START WITH 1, INCREMENT BY 1) primary key, " +
+                  "  pipeline_id integer references pipeline(id)," +
+                  "  pguid varchar(40)," +
+                  "  command varchar(512)," +
+                  "  arguments varchar(512)," +
+                  "  status varchar(15) )");
+
+        s.execute("insert into pipeline (name) values 'minipipe'");
+        s.execute("insert into pipeline (name) values 'MegaPipe'");
+        s.execute("insert into pipeline (name) values 'piping hot'");
+
+        s.execute("insert into pipeline_command (pipeline_id, status) " +
+                  "  values (1, 'flowing')");
+        s.execute("insert into pipeline_command (pipeline_id, status) " +
+                  "  values (2, 'gushing')");
+        s.execute("insert into pipeline_command (pipeline_id, status) " +
+                  "  values (3, 'steaming')");
+
+        s.execute("create table emp_bonus(empno char(6),bonus decimal(9,2))");
+	for( int e = 1; e <= 30; e++ )
+            s.execute("insert into emp_bonus( empno, bonus ) values (" +
+	              "'" + e + "', " + ( e * 10 ) + ")");
+
+        PreparedStatement ustmt = conn.prepareStatement(
+                  "update pipeline_command set status = 'WAIT RESULT'" +
+                  "       where id = ?",
+                  Statement.RETURN_GENERATED_KEYS);
+
+	ustmt.setInt( 1, 2 );
+
+	// DERBY-6880: Throws an error : 
+	//     ERROR 22018: Invalid character string format for type long.
+	// With the UpdateResultSet code from DERBY-6742 backed out, Derby
+	// processes the query and returns a getGeneratedKeys result set with
+	// a value of 0 for the only column in the only row.
+	//
+	ustmt.executeUpdate();
+	JDBC.assertFullResultSet( ustmt.getGeneratedKeys(),
+                new String[][] {{"0"}});
+        ustmt.close();
+
+	// On other systems, this variation causes getGeneratedKeys
+	// to return a result set containing the ID = 2 value. Derby processes
+	// the query, and returns a getGeneratedKeys result set with a value
+	// of 0 for the only column in the only row.
+	//
+        ustmt = conn.prepareStatement(
+                  "update pipeline_command set status = 'SECOND RESULT'" +
+                  "       where id = ?",
+                  new String[]{"ID"});
+	ustmt.setInt( 1, 2 );
+	ustmt.executeUpdate();
+	JDBC.assertFullResultSet( ustmt.getGeneratedKeys(),
+                new String[][] {{"0"}});
+        ustmt.close();
+
+	// Note that the case of the column names is significant, even though
+	// non-delimited column names are typically case-insensitive
+	//
+	try {
+            ustmt = conn.prepareStatement(
+                  "update pipeline_command set status = 'SECOND RESULT'" +
+                  "       where id = ?",
+                  new String[]{"id"});
+	    ustmt.setInt( 1, 2 );
+	    ustmt.executeUpdate();
+	} catch (SQLException se ) {
+	    assertSQLState( "X0X0F", se );
+	}
+        ustmt.close();
+
+	// Try specifying a non-generated column in the column list parameter.
+	// Derby considers this to be a (runtime) error.
+	//
+	try {
+            ustmt = conn.prepareStatement(
+                  "update pipeline_command set status = 'THIRD RESULT'" +
+                  "       where id = ?",
+                  new String[]{"STATUS"});
+	    ustmt.setInt( 1, 2 );
+	    ustmt.executeUpdate();
+	} catch (SQLException se ) {
+	    assertSQLState( "X0X0F", se );
+	}
+        ustmt.close();
+
+	// Another attempt at specifying a non-generated column list. Derby
+	// considers this to be an error, but other systems (DB2, at least)
+	// allow the empno column to be classified as a auto-generated key
+	// for the purposes of this query, apparently.
+	//
+	try {
+            ustmt = conn.prepareStatement(
+                  "update emp_bonus set bonus=bonus+300.0",
+                  new String[]{"empno"});
+	    ustmt.executeUpdate();
+	} catch (SQLException se ) {
+	    assertSQLState( "X0X0F", se );
+	}
+        ustmt.close();
+
+	try {
+            ustmt = conn.prepareStatement(
+                  "update emp_bonus set bonus=bonus+300.0",
+                  new String[]{"EMPNO"});
+	    ustmt.executeUpdate();
+	} catch (SQLException se ) {
+	    assertSQLState( "X0X0F", se );
+	}
+        ustmt.close();
+
+        s.close();
+    }
+
     /**
      * Verify that generated columns can be used even if the schema in which
      * the generated column was added does not exist. Regression test case



Mime
View raw message