You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by ba...@apache.org on 2005/01/26 02:11:15 UTC
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
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);