db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From banda...@apache.org
Subject svn commit: r126453 - in incubator/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile testing/org/apache/derbyTesting/functionTests/master testing/org/apache/derbyTesting/functionTests/tests/lang
Date Wed, 26 Jan 2005 01:11:15 GMT
Author: bandaram
Date: Tue Jan 25 17:11:12 2005
New Revision: 126453

URL: http://svn.apache.org/viewcvs?view=rev&rev=126453
Log:
Derby 124: Set the result type for CONCAT operator correctly for BLOBs and CLOBs.

Patch submitted by Mamta Satoor. (mamta@Remulak.Net)

Modified:
   incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConcatenationOperatorNode.java
   incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/concateTests.out
   incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/concateTests.java

Modified: incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConcatenationOperatorNode.java
Url: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConcatenationOperatorNode.java?view=diff&rev=126453&p1=incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConcatenationOperatorNode.java&r1=126452&p2=incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConcatenationOperatorNode.java&r2=126453
==============================================================================
--- incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConcatenationOperatorNode.java
(original)
+++ incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConcatenationOperatorNode.java
Tue Jan 25 17:11:12 2005
@@ -98,19 +98,21 @@
 		{
 			if (rightOperand.isParameterNode())
 			{
-				throw StandardException.newException(SQLState.LANG_BINARY_OPERANDS_BOTH_PARMS, 
+				throw StandardException.newException(SQLState.LANG_BINARY_OPERANDS_BOTH_PARMS,
 																	operator);
 			}
 
 			TypeId 	leftType;
 
 			/*
-			** A ? on the left gets its type from the right.  There are six
+			** A ? on the left gets its type from the right.  There are eight
 			** legal types for the concatenation operator: CHAR, VARCHAR,
-			** LONG VARCHAR, BIT, BIT VARYING, and LONG BIT VARYING.  If the
-			** right type is one of the bit types, set the parameter type to
+			** LONG VARCHAR, CLOB, BIT, BIT VARYING, LONG BIT VARYING, and BLOB.
+			** If the right type is BLOB, set the parameter type to BLOB with max length.
+			** If the right type is one of the other bit types, set the parameter type to
 			** BIT VARYING with maximum length.
 			**
+			** If the right type is CLOB, set parameter type to CLOB with max length.
 			** If the right type is anything else, set it to VARCHAR with
 			** maximum length.  We count on the resolveConcatOperation method to
 			** catch an illegal type.
@@ -118,19 +120,23 @@
 			** NOTE: When I added the long types, I could have changed the
 			** resulting parameter types to LONG VARCHAR and LONG BIT VARYING,
 			** but they were already VARCHAR and BIT VARYING, and it wasn't
-			** clear to me what effect it would have to change it.
-			**
-			**				-	Jeff
+			** clear to me what effect it would have to change it. -	Jeff
 			*/
 			if (rightOperand.getTypeId().isBitTypeId())
 			{
-				leftType = TypeId.getBuiltInTypeId(Types.VARBINARY);
+				if (rightOperand.getTypeId().isBlobTypeId())
+					leftType = TypeId.getBuiltInTypeId(Types.BLOB);
+				else
+					leftType = TypeId.getBuiltInTypeId(Types.VARBINARY);
 			}
 			else
 			{
-				leftType = TypeId.getBuiltInTypeId(Types.VARCHAR);
+				if (rightOperand.getTypeId().isClobTypeId())
+					leftType = TypeId.getBuiltInTypeId(Types.CLOB);
+				else
+					leftType = TypeId.getBuiltInTypeId(Types.VARCHAR);
 			}
-		
+
 		((ParameterNode) leftOperand).setDescriptor(new DataTypeDescriptor(leftType, true));
 		}
 
@@ -142,28 +148,36 @@
 			TypeId 	rightType;
 
 			/*
-			** A ? on the right gets its type from the left.  There are six
+			** A ? on the right gets its type from the left.  There are eight
 			** legal types for the concatenation operator: CHAR, VARCHAR,
-			** LONG VARCHAR, BIT, BIT VARYING, and LONG BIT VARYING.  If the
-			** left type is one of the bit types, set the parameter type to
-			** BIT VARYING with maximum length.  If the left type is anything
-			** else, set it to VARCHAR with maximum length.  We count on the
-			** resolveConcatOperation method to catch an illegal type.
+			** LONG VARCHAR, CLOB, BIT, BIT VARYING, LONG BIT VARYING, and BLOB.
+			** If the left type is BLOB, set the parameter type to BLOB with max length.
+			** If the left type is one of the other bit types, set the parameter type to
+			** BIT VARYING with maximum length.
+			**
+			** If the left type is CLOB, set parameter type to CLOB with max length.
+			** If the left type is anything else, set it to VARCHAR with
+			** maximum length.  We count on the resolveConcatOperation method to
+			** catch an illegal type.
 			**
 			** NOTE: When I added the long types, I could have changed the
 			** resulting parameter types to LONG VARCHAR and LONG BIT VARYING,
 			** but they were already VARCHAR and BIT VARYING, and it wasn't
-			** clear to me what effect it would have to change it.
-			**
-			**				-	Jeff
+			** clear to me what effect it would have to change it. -	Jeff
 			*/
 			if (leftOperand.getTypeId().isBitTypeId())
 			{
-				rightType = TypeId.getBuiltInTypeId(Types.VARBINARY);
+				if (leftOperand.getTypeId().isBlobTypeId())
+					rightType = TypeId.getBuiltInTypeId(Types.BLOB);
+				else
+					rightType = TypeId.getBuiltInTypeId(Types.VARBINARY);
 			}
 			else
 			{
-				rightType = TypeId.getBuiltInTypeId(Types.VARCHAR);
+				if (leftOperand.getTypeId().isClobTypeId())
+					rightType = TypeId.getBuiltInTypeId(Types.CLOB);
+				else
+					rightType = TypeId.getBuiltInTypeId(Types.VARCHAR);
 			}
 		
 		((ParameterNode) rightOperand).setDescriptor(

Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/concateTests.out
Url: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/concateTests.out?view=diff&rev=126453&p1=incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/concateTests.out&r1=126452&p2=incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/concateTests.out&r2=126453
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/concateTests.out
(original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/concateTests.out
Tue Jan 25 17:11:12 2005
@@ -149,6 +149,12 @@
 datatype of concatenated string is : CHAR
 precision of concatenated string is : 0
 Successful CHAR read of 0 characters
+Test13 - Prepared statement with CLOB(A) and ? concatenations will give result type of CLOB(A+length
of ?)
+  Prior to Derby-124 fix, ? parameter was getting bound to VARCHAR of length 32672 rather
than CLOB.
+  preapre statement with clob||?
+Successful CLOB read of 32702 characters
+  Test - preapre statement with clob||cast(? to cLOB)
+Successful CLOB read of 32702 characters
 Test1 finished - CHAR, VARCHAR, LONGVARCHAR and CLOB concatenation tests
 Test2 - CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, LONGVARCHAR FOR BIT DATA and BLOB concatenation
tests
 Test2a - CHAR FOR BIT DATA concatenations will give result type of CHAR FOR BIT DATA when
concatenated string < 255
@@ -306,4 +312,11 @@
 Test22 - try 2 empty char for bit data concatenation and verify that length comes back as
0 for the result
 datatype of concatenated string is : CHAR () FOR BIT DATA
 precision of concatenated string is : 0
+Test23 - Derby-124 try concatenation in a prepared statement with one operand casted to BLOB
and other as ? parameter
+  Prior to Derby-124 fix, ? parameter was getting bound to VARCHAR TO BIT DATA of length
32672 rather than BLOB.
+  That caused truncation exception when ? parameter was set to > 32672 bytes
+  preapre statement with blob||?
+Successful BLOB read of 32702 bytes
+  Test - preapre statement with blob||cast(? to BLOB)
+Successful BLOB read of 32702 bytes
 Test2 finished - CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, LONGVARCHAR FOR BIT DATA and BLOB
concatenation tests

Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/concateTests.java
Url: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/concateTests.java?view=diff&rev=126453&p1=incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/concateTests.java&r1=126452&p2=incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/concateTests.java&r2=126453
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/concateTests.java
(original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/concateTests.java
Tue Jan 25 17:11:12 2005
@@ -398,8 +398,33 @@
 			dumpSomeMetaDataInfo(s.executeQuery("values('' || '')"), concatenatedSQLTypesNames[0]);
 			verifyStringData(s.executeQuery("values('' || '')"), "");
 
+			System.out.println("Test13 - Prepared statement with CLOB(A) and ? concatenations will
give result type of CLOB(A+length of ?)");
+			System.out.println("  Prior to Derby-124 fix, ? parameter was getting bound to VARCHAR
of length 32672 rather than CLOB.");
+			try {
+			s.executeUpdate("drop table ct");
+			} catch(Exception ex) {}
+			s.executeUpdate("create table ct (c CLOB(100K))");
+			String cData = Formatters.repeatChar("c",32700);
+			String cData1 = "aa";
+			String cConcatenatedData = cData1 + cData;
+			//Prior to fix for Derby-124, the ? was getting bound to VARCHAR with max length of 32670
+			//As a fix for this, if one of the operands of concatenation is CLOB, then the ? parameter
would be bound to CLOB as well
+			System.out.println("  preapre statement with clob||?");
+			ps = conn.prepareStatement("insert into ct values (cast ('aa' as CLOB) || ?)");
+			ps.setString(1, cData);
+			ps.execute();
+			verifyStringData(s.executeQuery("select c from ct"), cConcatenatedData);
+			s.executeUpdate("delete from ct");
+			System.out.println("  Test - preapre statement with clob||cast(? to cLOB)");
+			ps = conn.prepareStatement("insert into ct values (cast ('aa' as CLOB) || cast(? as CLOB))");
+			ps.setString(1, cData);
+			ps.execute();
+			verifyStringData(s.executeQuery("select c from ct"), cConcatenatedData);
+			s.executeUpdate("delete from ct");
+
 			s.executeUpdate("drop table testCLOB_MAIN");
 			s.executeUpdate("drop table t1");
+			s.executeUpdate("drop table ct");
 			System.out.println("Test1 finished - CHAR, VARCHAR, LONGVARCHAR and CLOB concatenation
tests");
 		} catch (SQLException sqle) {
 			org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);
@@ -844,8 +869,41 @@
 			System.out.println("Test22 - try 2 empty char for bit data concatenation and verify that
length comes back as 0 for the result");
 			dumpSomeMetaDataInfo(s.executeQuery("values(X'' || X'')"), concatenatedSQLTypesNames[4]);
 
+			System.out.println("Test23 - Derby-124 try concatenation in a prepared statement with
one operand casted to BLOB and other as ? parameter");
+			System.out.println("  Prior to Derby-124 fix, ? parameter was getting bound to VARCHAR
TO BIT DATA of length 32672 rather than BLOB.");
+			System.out.println("  That caused truncation exception when ? parameter was set to >
32672 bytes");
+			try {
+			s.executeUpdate("drop table bt");
+			} catch(Exception ex) {}
+			s.executeUpdate("create table bt (b BLOB(100K))");
+			byte [] bData = new byte[32700];
+			for (int i = 0; i < bData.length; i++)
+        bData[i] = (byte)(i % 10);
+			byte [] bData1 = new byte[2];
+			bData1[0] = (byte) 0x10;
+			bData1[1] = (byte) 0x10;
+			byte [] bConcatenatedData = new byte[32702];
+			System.arraycopy(bData1, 0, bConcatenatedData, 0, bData1.length);
+			System.arraycopy(bData, 0, bConcatenatedData, bData1.length, bData.length);
+			//Prior to fix for Derby-124, the ? was getting bound to VARCHAR FOR BIT DATA with max
length of 32670
+			//And when ps.setBytes() set 32700 bytes into the ? parameter, at ps.execute() time, you
got following exception
+			//ERROR 22001: A truncation error was encountered trying to shrink VARCHAR () FOR BIT
DATA 'XX-RESOLVE-XX' to length 32672.
+			//As a fix for this, if one of the operands of concatenation is BLOB, then the ? parameter
would be bound to BLOB as well      
+			System.out.println("  preapre statement with blob||?");
+			ps = conn.prepareStatement("insert into bt values (cast (x'1010' as BLOB) || ?)");
+			ps.setBytes(1, bData);
+			ps.execute();
+			verifyByteData(s.executeQuery("select b from bt"), bConcatenatedData);
+			s.executeUpdate("delete from bt");
+			System.out.println("  Test - preapre statement with blob||cast(? to BLOB)");
+			ps = conn.prepareStatement("insert into bt values (cast (x'1010' as BLOB) || cast(? as
BLOB))");
+			ps.setBytes(1, bData);
+			ps.execute();
+			verifyByteData(s.executeQuery("select b from bt"), bConcatenatedData);
+
 			s.executeUpdate("drop table testBLOB_MAIN");  
 			s.executeUpdate("drop table t2");
+			s.executeUpdate("drop table bt");
 			System.out.println("Test2 finished - CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, LONGVARCHAR
FOR BIT DATA and BLOB concatenation tests");
 		} catch (SQLException sqle) {
 			org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);

Mime
View raw message