db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d..@apache.org
Subject svn commit: r807337 - in /db/derby/code/trunk/java: engine/org/apache/derby/iapi/sql/execute/ engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/impl/sql/execute/ engine/org/apache/derby/loc/ shared/org/apache/derby/shared/common/referen...
Date Mon, 24 Aug 2009 18:55:00 GMT
Author: dag
Date: Mon Aug 24 18:55:00 2009
New Revision: 807337

URL: http://svn.apache.org/viewvc?rev=807337&view=rev
Log:
DERBY-4208 Parameters ? with OFFSET and/or FETCH

This patch implements the use of dynamic parameters with OFFSET/FETCH and adds new tests.

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowCountNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RowCountResultSet.java
    db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml
    db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OffsetFetchNextTest.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java?rev=807337&r1=807336&r2=807337&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java
Mon Aug 24 18:55:00 2009
@@ -1620,8 +1620,9 @@
 	 *		                  which provides the context for the row
 	 *                        allocation operation
 	 * @param resultSetNumber The resultSetNumber for the ResultSet
-	 * @param offset          The offset value (0 by default)
-	 * @param fetchFirst      The fetch first value (-1 if not in use)
+	 * @param offsetMethod   The OFFSET parameter was specified
+	 * @param fetchFirstMethod The FETCH FIRST/NEXT parameter was
+	 *                        specified
 	 * @param optimizerEstimatedRowCount
 	 *                        Estimated total # of rows by optimizer
 	 * @param optimizerEstimatedCost
@@ -1633,8 +1634,8 @@
 		NoPutResultSet source,
 		Activation activation,
 		int resultSetNumber,
-		long offset,
-		long fetchFirst,
+		GeneratedMethod offsetMethod,
+		GeneratedMethod fetchFirstMethod,
 		double optimizerEstimatedRowCount,
 		double optimizerEstimatedCost) throws StandardException;
 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java?rev=807337&r1=807336&r2=807337&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java Mon
Aug 24 18:55:00 2009
@@ -23,6 +23,7 @@
 
 import java.util.ArrayList;
 import java.util.Vector;
+import java.sql.Types;
 
 import org.apache.derby.iapi.error.StandardException;
 import org.apache.derby.iapi.reference.SQLState;
@@ -35,6 +36,8 @@
 import org.apache.derby.impl.sql.CursorInfo;
 import org.apache.derby.impl.sql.CursorTableReference;
 import org.apache.derby.iapi.types.DataValueDescriptor;
+import org.apache.derby.iapi.types.DataTypeDescriptor;
+import org.apache.derby.iapi.types.TypeId;
 
 /**
  * A CursorNode represents a result set that can be returned to a client.
@@ -53,8 +56,8 @@
 
 	private String		name;
 	private OrderByList	orderByList;
-	private NumericConstantNode   offset;     // <result offset clause> value
-	private NumericConstantNode   fetchFirst; // <fetch first clause> value
+	private ValueNode   offset;     // <result offset clause> value
+	private ValueNode   fetchFirst; // <fetch first clause> value
 	private String		statementType;
 	private int		updateMode;
 	private boolean		needTarget;
@@ -106,8 +109,8 @@
 		this.name = (String) name;
 		this.statementType = (String) statementType;
 		this.orderByList = (OrderByList) orderByList;
-		this.offset = (NumericConstantNode)offset;
-		this.fetchFirst = (NumericConstantNode)fetchFirst;
+		this.offset = (ValueNode)offset;
+		this.fetchFirst = (ValueNode)fetchFirst;
 
 		this.updateMode = ((Integer) updateMode).intValue();
 		this.updatableColumns = (Vector) updatableColumns;
@@ -362,7 +365,7 @@
 
 	private void bindOffsetFetch() throws StandardException {
 
-		if (offset != null) {
+		if (offset instanceof ConstantNode) {
 			DataValueDescriptor dvd = ((ConstantNode)offset).getValue();
 			long val = dvd.getLong();
 
@@ -371,9 +374,16 @@
 					SQLState.LANG_INVALID_ROW_COUNT_OFFSET,
 					Long.toString(val) );
 			}
+		} else if (offset instanceof ParameterNode) {
+			offset.
+				setType(new DataTypeDescriptor(
+							TypeId.getBuiltInTypeId(Types.BIGINT),
+							false /* ignored tho; ends up nullable,
+									 so we test for NULL at execute time */));
 		}
 
-		if (fetchFirst != null) {
+
+		if (fetchFirst instanceof ConstantNode) {
 			DataValueDescriptor dvd = ((ConstantNode)fetchFirst).getValue();
 			long val = dvd.getLong();
 
@@ -382,6 +392,12 @@
 					SQLState.LANG_INVALID_ROW_COUNT_FIRST,
 					Long.toString(val) );
 			}
+		} else if (fetchFirst instanceof ParameterNode) {
+			fetchFirst.
+				setType(new DataTypeDescriptor(
+							TypeId.getBuiltInTypeId(Types.BIGINT),
+							false /* ignored tho; ends up nullable,
+									 so we test for NULL at execute time*/));
 		}
 	}
 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowCountNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowCountNode.java?rev=807337&r1=807336&r2=807337&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowCountNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowCountNode.java Mon
Aug 24 18:55:00 2009
@@ -27,6 +27,8 @@
 import org.apache.derby.iapi.services.sanity.SanityManager;
 import org.apache.derby.iapi.reference.ClassName;
 import org.apache.derby.iapi.services.classfile.VMOpcode;
+import org.apache.derby.iapi.types.SQLLongint;
+import org.apache.derby.iapi.reference.ClassName;
 
 /**
  * The result set generated by this node (RowCountResultSet) implements the
@@ -93,19 +95,23 @@
         acb.pushThisAsActivation(mb);  // arg2
         mb.push(resultSetNumber);      // arg3
 
+        boolean dynamicOffset = false;
+        boolean dynamicFetchFirst = false;
+
+        // arg4
+        if (offset != null) {
+            generateExprFun(acb, mb, offset);
+        } else {
+            mb.pushNull(ClassName.GeneratedMethod);
+        }
 
-        // If OFFSET is not given, we pass in the default, i.e 0.
-        long offsetVal =
-            (offset != null) ?
-            ((ConstantNode)offset).getValue().getLong() : 0;
-
-        // If FETCH FIRST is not given, we pass in -1 to RowCountResultSet.
-        long fetchFirstVal =
-            (fetchFirst != null) ?
-            ((ConstantNode)fetchFirst).getValue().getLong() : -1;
+        // arg5
+        if (fetchFirst != null) {
+            generateExprFun(acb, mb, fetchFirst);
+        } else {
+            mb.pushNull(ClassName.GeneratedMethod);
+        }
 
-        mb.push(offsetVal);            // arg4
-        mb.push(fetchFirstVal);        // arg5
         mb.push(costEstimate.rowCount()); // arg6
         mb.push(costEstimate.getEstimatedCost()); // arg7
 
@@ -117,6 +123,31 @@
     }
 
 
+    private void generateExprFun(
+        ExpressionClassBuilder ecb,
+        MethodBuilder mb,
+        ValueNode vn) throws StandardException {
+
+        // Generates:
+        //     Object exprFun { }
+        MethodBuilder exprFun = ecb.newExprFun();
+
+        /* generates:
+         *    return  <dynamic parameter.generate(ecb)>;
+         * and adds it to exprFun
+         */
+        vn.generateExpression(ecb, exprFun);
+        exprFun.methodReturn();
+
+        // we are done modifying exprFun, complete it.
+        exprFun.complete();
+
+        // Pass in the method that will be used to evaluates the dynamic
+        // parameter in RowCountResultSet.
+        ecb.pushMethodReference(mb, exprFun);
+    }
+
+
     /**
      * Convert this object to a String.  See comments in QueryTreeNode.java
      * for how this should be done for tree printing.

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj?rev=807337&r1=807336&r2=807337&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj Mon Aug
24 18:55:00 2009
@@ -497,7 +497,6 @@
 		DataTypeDescriptor[] descriptors = cc.getParameterTypes();
 
 		ParameterNode				newNode;
-		ParameterNode				oldNode;
 		int							paramCount;
 
 		/*
@@ -3115,8 +3114,8 @@
 	int				  isolationLevel = ExecutionContext.UNSPECIFIED_ISOLATION_LEVEL;
 	CursorNode		  retval;
 	OrderByList orderCols = null;
-	NumericConstantNode offset = null;
-	NumericConstantNode fetchFirst = null;
+	ValueNode offset = null;
+	ValueNode fetchFirst = null;
 }
 {
 	queryExpression = queryExpression(null, NO_SET_OP) 
@@ -8069,18 +8068,22 @@
 /*
  * <A NAME="offsetClause">offsetClause</A>
  */
-NumericConstantNode
+ValueNode
 offsetClause() throws StandardException :
 {
-	NumericConstantNode result;
+	ValueNode result = null;
 }
 {
 	// Since OFFSET is not yet a reserved keyword, cf. disambiguation
 	// look-ahead for it w.r.t. offsetClause in method nonReservedKeyword.
 	// This solves the shift/reduce conflict, and allows us to use OFFSET as an
 	// identifier in all contexts.
-	<OFFSET> result = intLiteral() ( <ROW> | <ROWS> )
-    {
+	<OFFSET>
+    ( result = intLiteral()
+	| result = dynamicParameterSpecification()
+    )
+	( <ROW> | <ROWS> )
+	{
 		return result;
 	}
 }
@@ -8089,16 +8092,18 @@
 /*
  * <A NAME="fetchFirstClause">fetchFirstClause</A>
  */
-NumericConstantNode
+ValueNode
 fetchFirstClause() throws StandardException :
 {
 	// The default number of rows to fetch if the literal is omitted is 1:
-	NumericConstantNode result = getNumericNode("1", true);
+	ValueNode result = getNumericNode("1", true);
 }
 {
 	<FETCH> ( <FIRST> | <NEXT> )
-		[ result = intLiteral() ] ( <ROW> | <ROWS> ) <ONLY>
-    {
+		[ result = intLiteral()
+		| result = dynamicParameterSpecification()
+		] ( <ROW> | <ROWS> ) <ONLY>
+	{
 		return result;
 	}
 }
@@ -13893,7 +13898,8 @@
 			getToken(1).kind == OFFSET &&
 			!(getToken(2).kind == PLUS_SIGN ||
 			  getToken(2).kind == MINUS_SIGN ||
-			  getToken(2).kind == EXACT_NUMERIC)
+			  getToken(2).kind == EXACT_NUMERIC ||
+			  getToken(2).kind == QUESTION_MARK)
 		})
 		tok = <OFFSET>
 	|	tok = <OLD>

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java?rev=807337&r1=807336&r2=807337&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java
Mon Aug 24 18:55:00 2009
@@ -1273,8 +1273,8 @@
 		NoPutResultSet source,
 		Activation activation,
 		int resultSetNumber,
-		long offset,
-		long fetchFirst,
+		GeneratedMethod offsetMethod,
+		GeneratedMethod fetchFirstMethod,
 		double optimizerEstimatedRowCount,
 		double optimizerEstimatedCost)
 		throws StandardException
@@ -1282,8 +1282,8 @@
 		return new RowCountResultSet(source,
 									 activation,
 									 resultSetNumber,
-									 offset,
-									 fetchFirst,
+									 offsetMethod,
+									 fetchFirstMethod,
 									 optimizerEstimatedRowCount,
 									 optimizerEstimatedCost);
 	}

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RowCountResultSet.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RowCountResultSet.java?rev=807337&r1=807336&r2=807337&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RowCountResultSet.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RowCountResultSet.java
Mon Aug 24 18:55:00 2009
@@ -21,6 +21,7 @@
 
 package org.apache.derby.impl.sql.execute;
 
+import org.apache.derby.iapi.reference.SQLState;
 import org.apache.derby.iapi.sql.conn.StatementContext;
 import org.apache.derby.iapi.sql.execute.CursorResultSet;
 import org.apache.derby.iapi.sql.execute.ExecRow;
@@ -28,7 +29,9 @@
 import org.apache.derby.iapi.sql.Activation;
 import org.apache.derby.iapi.error.StandardException;
 import org.apache.derby.iapi.services.sanity.SanityManager;
+import org.apache.derby.iapi.services.loader.GeneratedMethod;
 import org.apache.derby.iapi.types.RowLocation;
+import org.apache.derby.iapi.types.DataValueDescriptor;
 
 
 
@@ -49,8 +52,10 @@
     // life of object.
     final NoPutResultSet source;
     final private boolean runTimeStatsOn;
-    final private long offset;
-    final private long fetchFirst;
+    private long offset;
+    private long fetchFirst;
+    final private GeneratedMethod offsetMethod;
+    final private GeneratedMethod fetchFirstMethod;
 
     /**
      * True if we haven't yet fetched any rows from this result set.
@@ -72,8 +77,8 @@
      *                        which provides the context for the row
      *                        allocation operation
      * @param resultSetNumber The resultSetNumber for the ResultSet
-     * @param offset          The offset value (0 by default)
-     * @param fetchFirst      The fetch first value (-1 if not in use)
+     * @param offsetMethod   Generated method
+     * @param fetchFirstMethod Generated method
      * @param optimizerEstimatedRowCount
      *                        Estimated total # of rows by optimizer
      * @param optimizerEstimatedCost
@@ -84,21 +89,22 @@
         (NoPutResultSet s,
          Activation a,
          int resultSetNumber,
-         long offset,
-         long fetchFirst,
+         GeneratedMethod offsetMethod,
+         GeneratedMethod fetchFirstMethod,
          double optimizerEstimatedRowCount,
          double optimizerEstimatedCost)
-        throws StandardException
-    {
+            throws StandardException {
+
         super(a,
               resultSetNumber,
               optimizerEstimatedRowCount,
               optimizerEstimatedCost);
 
+        this.offsetMethod = offsetMethod;
+        this.fetchFirstMethod = fetchFirstMethod;
+
         source = s;
 
-        this.offset = offset;
-        this.fetchFirst = fetchFirst;
         virginal = true;
         rowsFetched = 0;
 
@@ -173,26 +179,77 @@
 
         beginTime = getCurrentTimeMillis();
 
-        if (virginal && offset > 0) {
-            // Only skip rows the first time around
-            virginal = false;
+        if (virginal) {
+            if (offsetMethod != null) {
+                DataValueDescriptor offVal
+                    = (DataValueDescriptor)offsetMethod.invoke(activation);
+
+                if (offVal.isNotNull().getBoolean()) {
+                    offset = offVal.getLong();
+
+                    if (offset < 0) {
+                        throw StandardException.newException(
+                            SQLState.LANG_INVALID_ROW_COUNT_OFFSET,
+                            Long.toString(offset));
+                    } else {
+                        offset = offVal.getLong();
+                    }
+                } else {
+                    throw StandardException.newException(
+                        SQLState.LANG_ROW_COUNT_OFFSET_FIRST_IS_NULL,
+                        "OFFSET");
+                }
+            } else {
+                // not given
+                offset = 0;
+            }
 
-            long offsetCtr = offset;
 
-            do {
-                result = source.getNextRowCore();
-                offsetCtr--;
-
-                if (result != null && offsetCtr >= 0) {
-                    rowsFiltered++;
+            if (fetchFirstMethod != null) {
+                DataValueDescriptor fetchFirstVal
+                    = (DataValueDescriptor)fetchFirstMethod.invoke(activation);
+
+                if (fetchFirstVal.isNotNull().getBoolean()) {
+
+                    fetchFirst = fetchFirstVal.getLong();
+
+                    if (fetchFirst < 1) {
+                        throw StandardException.newException(
+                            SQLState.LANG_INVALID_ROW_COUNT_FIRST,
+                            Long.toString(fetchFirst));
+                    }
                 } else {
-                    break;
+                    throw StandardException.newException(
+                        SQLState.LANG_ROW_COUNT_OFFSET_FIRST_IS_NULL,
+                        "FETCH FIRST/NEXT");
                 }
+            }
 
-            } while (true);
+            if (offset > 0) {
+                // Only skip rows the first time around
+                virginal = false;
+
+                long offsetCtr = offset;
+
+                do {
+                    result = source.getNextRowCore();
+                    offsetCtr--;
+
+                    if (result != null && offsetCtr >= 0) {
+                        rowsFiltered++;
+                    } else {
+                        break;
+                    }
+                } while (true);
+            } else {
+                if (fetchFirstMethod != null && rowsFetched >= fetchFirst) {
+                    result = null;
+                } else {
+                    result = source.getNextRowCore();
+                }
+            }
         } else {
-
-            if (fetchFirst != -1 && rowsFetched >= fetchFirst) {
+            if (fetchFirstMethod != null && rowsFetched >= fetchFirst) {
                 result = null;
             } else {
                 result = source.getNextRowCore();

Modified: db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml?rev=807337&r1=807336&r2=807337&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml Mon Aug 24 18:55:00
2009
@@ -764,6 +764,12 @@
                 <text>Invalid row count for FIRST/NEXT, must be >= 1.</text>
             </msg>
 
+            <msg>
+                <name>2201Z</name>
+                <text>NULL value not allowed for {0} argument.</text>
+                <arg>string</arg>
+            </msg>
+
         </family>
 
 

Modified: db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java?rev=807337&r1=807336&r2=807337&view=diff
==============================================================================
--- db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
(original)
+++ db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
Mon Aug 24 18:55:00 2009
@@ -714,6 +714,7 @@
 	String LANG_ESCAPE_IS_NULL                                  	   = "22501";
 	String LANG_INVALID_ROW_COUNT_OFFSET                               = "2201X";
 	String LANG_INVALID_ROW_COUNT_FIRST                                = "2201W";
+	String LANG_ROW_COUNT_OFFSET_FIRST_IS_NULL                         = "2201Z";
 
 	/*
 	** Integrity violations.

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OffsetFetchNextTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OffsetFetchNextTest.java?rev=807337&r1=807336&r2=807337&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OffsetFetchNextTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OffsetFetchNextTest.java
Mon Aug 24 18:55:00 2009
@@ -27,6 +27,7 @@
 import java.sql.Statement;
 import java.sql.Types;
 import java.sql.PreparedStatement;
+import java.sql.ParameterMetaData ;
 
 import junit.framework.Test;
 import junit.framework.TestSuite;
@@ -41,6 +42,14 @@
  */
 public class OffsetFetchNextTest extends BaseJDBCTestCase {
 
+    private final static String LANG_FORMAT_EXCEPTION = "22018";
+    private final static String LANG_INTEGER_LITERAL_EXPECTED = "42X20";
+    private final static String LANG_INVALID_ROW_COUNT_FIRST = "2201W";
+    private final static String LANG_INVALID_ROW_COUNT_OFFSET = "2201X";
+    private final static String LANG_MISSING_PARMS = "07000";
+    private final static String LANG_SYNTAX_ERROR = "42X01";
+	private final static String LANG_ROW_COUNT_OFFSET_FIRST_IS_NULL = "2201Z";
+
     public OffsetFetchNextTest(String name) {
         super(name);
     }
@@ -103,21 +112,21 @@
 
         // Wrong range in row count argument
 
-        assertStatementError("2201X", st,
+        assertStatementError(LANG_INVALID_ROW_COUNT_OFFSET, st,
                              "select * from t1 offset -1 rows");
 
-        assertStatementError("2201W", st,
+        assertStatementError(LANG_INVALID_ROW_COUNT_FIRST, st,
                              "select * from t1 fetch first 0 rows only");
 
-        assertStatementError("2201W", st,
+        assertStatementError(LANG_INVALID_ROW_COUNT_FIRST, st,
                              "select * from t1 fetch first -1 rows only");
 
         // Wrong type in row count argument
-        assertStatementError("42X20", st,
+        assertStatementError(LANG_INTEGER_LITERAL_EXPECTED, st,
                              "select * from t1 fetch first 3.14 rows only");
 
         // Wrong order of clauses
-        assertStatementError("42X01", st,
+        assertStatementError(LANG_SYNTAX_ERROR, st,
                              "select * from t1 " +
                              "fetch first 0 rows only offset 0 rows");
     }
@@ -660,6 +669,136 @@
         }
     }
 
+    /**
+     * Test dynamic arguments
+     */
+    public void testDynamicArgs() throws SQLException {
+        // Check look-ahead also for ? in grammar since offset is not reserved
+        PreparedStatement ps = prepareStatement(
+            "select * from t1 offset ? rows");
+
+        // Check range errors
+        ps = prepareStatement(
+            "select * from t1 order by b " +
+            "offset ? rows fetch next ? rows only");
+
+        ps.setInt(1, 0);
+        assertPreparedStatementError(LANG_MISSING_PARMS, ps);
+
+        ps.setInt(1, -1);
+        ps.setInt(2, 2);
+        assertPreparedStatementError(LANG_INVALID_ROW_COUNT_OFFSET, ps);
+
+        ps.setInt(1, 0);
+        ps.setInt(2, 0);
+        assertPreparedStatementError(LANG_INVALID_ROW_COUNT_FIRST, ps);
+
+        // Check non-integer values
+        try {
+            ps.setString(1, "aaa");
+        } catch (SQLException e) {
+            assertSQLState(LANG_FORMAT_EXCEPTION, e);
+        }
+
+        try {
+            ps.setString(2, "aaa");
+        } catch (SQLException e) {
+            assertSQLState(LANG_FORMAT_EXCEPTION, e);
+        }
+
+
+        // A normal case
+        String[][] expected = {{"1", "3"}, {"1", "4"}};
+        for (int i = 0; i < 2; i++) {
+            ps.setInt(1,2);
+            ps.setInt(2,2);
+            JDBC.assertFullResultSet(ps.executeQuery(), expected);
+        }
+
+        // Now, note that since we now have different values for offset and
+        // fetch first, we also exercise reusing the result set for this
+        // prepared statement (i.e. the values are computed at execution time,
+        // not at result set generation time). Try long value for change.
+        ps.setLong(1, 1L);
+        ps.setInt(2, 3);
+        expected = new String[][]{{"1", "2"}, {"1", "3"}, {"1", "4"}};
+        JDBC.assertFullResultSet(ps.executeQuery(), expected);
+
+
+        //  Try a large number
+        ps.setLong(1, Integer.MAX_VALUE * 2L);
+        ps.setInt(2, 5);
+        JDBC.assertEmpty(ps.executeQuery());
+
+        // Mix of prepared and not
+        ps = prepareStatement(
+            "select * from t1 order by b " +
+             "offset ? rows fetch next 3 rows only");
+        ps.setLong(1, 1L);
+        JDBC.assertFullResultSet(ps.executeQuery(), expected);
+
+        ps = prepareStatement(
+            "select * from t1 order by b " +
+             "offset 4 rows fetch next ? rows only");
+        ps.setLong(1, 1L);
+        JDBC.assertFullResultSet(ps.executeQuery(),
+                                 new String[][]{{"1", "5"}});
+
+        // Mix of other dyn args and ours:
+        ps = prepareStatement(
+            "select * from t1 where a = ? order by b " +
+             "offset ? rows fetch next 3 rows only");
+        ps.setInt(1, 1);
+        ps.setLong(2, 1L);
+        JDBC.assertFullResultSet(ps.executeQuery(), expected);
+
+        ps = prepareStatement(
+            "select * from t1 where a = ? order by b " +
+             "offset 1 rows fetch next ? rows only");
+        ps.setInt(1, 1);
+        ps.setLong(2, 2L);
+        expected = new String[][]{{"1", "2"}, {"1", "3"}};
+        JDBC.assertFullResultSet(ps.executeQuery(), expected);
+
+
+        // NULLs not allowed (Note: parameter metadata says "isNullable" for
+        // all ? args in Derby...)
+        ps = prepareStatement(
+            "select * from t1 order by b " +
+             "offset ? rows fetch next ? rows only");
+        ps.setNull(1, Types.BIGINT);
+        ps.setInt(2, 2);
+        assertPreparedStatementError(LANG_ROW_COUNT_OFFSET_FIRST_IS_NULL, ps);
+
+        ps.setInt(1,1);
+        ps.setNull(2, Types.BIGINT);
+        assertPreparedStatementError(LANG_ROW_COUNT_OFFSET_FIRST_IS_NULL, ps);
+
+        ps.close();
+    }
+
+    /**
+     * Test dynamic arguments
+     */
+    public void testDynamicArgsMetaData() throws SQLException {
+        PreparedStatement ps = prepareStatement(
+            "select * from t1 where a = ? order by b " +
+            "offset ? rows fetch next ? rows only");
+
+        ParameterMetaData pmd = ps.getParameterMetaData();
+        int[] expectedTypes = { Types.INTEGER, Types.BIGINT, Types.BIGINT };
+
+        for (int i = 0; i < 3; i++) {
+            assertEquals("Unexpected parameter type",
+                         expectedTypes[i], pmd.getParameterType(i+1));
+            assertEquals("Derby ? args are nullable",
+                         // Why is that? Cf. logic in ParameterNode.setType
+                         ParameterMetaData.parameterNullable,
+                         pmd.isNullable(i+1));
+        }
+        ps.close();
+    }
+
     private void queryAndCheck(
         Statement stm,
         String queryText,

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java?rev=807337&r1=807336&r2=807337&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java Mon
Aug 24 18:55:00 2009
@@ -1014,7 +1014,29 @@
         }
         
     }
-    
+
+    /**
+     * Assert that the query fails (either in execution, or retrieval of
+     * results--doesn't matter) and throws a SQLException with the expected
+     * state and error code
+     *
+     * Parameters must have been already bound, if any.
+     *
+     * @param sqlState expected sql state.
+     * @param ps PreparedStatement query object to execute.
+     */
+    public static void assertPreparedStatementError(String sqlState,
+                                                    PreparedStatement ps) {
+        try {
+            boolean haveRS = ps.execute();
+            fetchAndDiscardAllResults(ps, haveRS);
+            fail("Expected error '" + sqlState +
+                "' but no error was thrown.");
+        } catch (SQLException se) {
+            assertSQLState(sqlState, se);
+        }
+    }
+
     /**
      * Assert that execution of the received PreparedStatement
      * object fails (either in execution or when retrieving



Mime
View raw message