db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From abr...@apache.org
Subject svn commit: r546183 - in /db/derby/code/trunk/java: engine/org/apache/derby/iapi/types/ engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/loc/ shared/org/apache/derby/shared/common/reference/ testing/org/apache/derbyTesting/functionTest...
Date Mon, 11 Jun 2007 16:25:34 GMT
Author: abrown
Date: Mon Jun 11 09:25:33 2007
New Revision: 546183

URL: http://svn.apache.org/viewvc?view=rev&rev=546183
Log:
DERBY-1623: Add support for an ANSI TRIM function as defined in SQL 2003
standard:

  <trim function> ::= TRIM <left paren> <trim operands> <right paren>
  <trim operands> ::= [ [ <trim specification> ] [ <trim character> ]
    FROM ] <trim source>
  <trim source> ::= <character value expression>
  <trim specification> ::= LEADING | TRAILING | BOTH
  <trim character> ::= <character value expression> 

Patch contributed by: Manish Khettry (manish_khettry@yahoo.com) 

Added:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AnsiTrimTest.java
  (with props)
Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLChar.java
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/StringDataValue.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TernaryOperatorNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
    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/master/db2Compatibility.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLChar.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLChar.java?view=diff&rev=546183&r1=546182&r2=546183
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLChar.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLChar.java Mon Jun 11 09:25:33
2007
@@ -2082,7 +2082,77 @@
 		return result;
 	}
 
-	/** @see StringDataValue#upper 
+	/**
+	 * This function public for testing purposes.
+	 * @param trimType
+	 * @param trimChar
+	 * @param source
+	 * @return
+	 */
+	private String trimInternal(int trimType, char trimChar, String source)
+	{
+		if (source == null) {
+			return null;
+		}
+		
+		int len = source.length();
+		int start = 0;
+		if (trimType == LEADING || trimType == BOTH)
+		{
+			for (; start < len; start++)
+				if (trimChar != source.charAt(start))
+					break;
+		}
+
+		if (start == len)
+			return "";
+
+		int end = len - 1;
+		if (trimType == TRAILING || trimType == BOTH)
+		{
+			for (; end >= 0; end--)
+				if (trimChar != source.charAt(end))
+					break;
+		}
+		if (end == -1)
+			return "";
+
+		return source.substring(start, end + 1);
+	}
+	/**
+	 * 
+	 * @param trimType
+	 * @param trimChar
+	 * @param result
+	 * @return
+	 */
+	public StringDataValue ansiTrim(int trimType, StringDataValue trimChar, StringDataValue
result)
+			throws StandardException {
+
+		if (result == null)
+		{
+			result = getNewVarchar();
+		}
+
+		if (trimChar == null || trimChar.getString() == null)
+		{
+			result.setToNull();
+			return result;
+		}
+
+
+		if (trimChar.getString().length() != 1)
+		{
+			throw StandardException.newException(SQLState.LANG_INVALID_TRIM_CHARACTER, trimChar.getString());
		
+		}
+
+		char trimCharacter = trimChar.getString().charAt(0);
+
+		result.setValue(trimInternal(trimType, trimCharacter, getString()));
+		return result; 
+	}
+
+	/** @see StringDataValue#upper
 	 *
 	 * @exception StandardException		Thrown on error
 	 */

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/StringDataValue.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/StringDataValue.java?view=diff&rev=546183&r1=546182&r2=546183
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/StringDataValue.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/StringDataValue.java Mon Jun
11 09:25:33 2007
@@ -130,6 +130,23 @@
 				StringDataValue result)
 		throws StandardException;
 
+	/**
+	 * The SQL Ansi trim function.
+
+	 * @param trimType type of trim. Possible values are {@link #LEADING}, {@link #TRAILING}
+	 *        or {@link #BOTH}.
+	 * @param trimChar  The character to trim from <em>this</em>
+	 * @param result The result of a previous call to this method,
+	 *					null if not called yet.
+	 * @return A StringDataValue containing the result of the trim().
+	 * @throws StandardException
+	 */
+	public StringDataValue ansiTrim(
+			int trimType,
+			StringDataValue trimChar,
+			StringDataValue result)
+		throws StandardException;
+
 	/** 
 	 * Convert the string to upper case.
 	 *

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TernaryOperatorNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TernaryOperatorNode.java?view=diff&rev=546183&r1=546182&r2=546183
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TernaryOperatorNode.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TernaryOperatorNode.java
Mon Jun 11 09:25:33 2007
@@ -82,7 +82,7 @@
 	public static final int TIMESTAMPADD = 4;
 	public static final int TIMESTAMPDIFF = 5;
 	static final String[] TernaryOperators = {"trim", "LOCATE", "substring", "like", "TIMESTAMPADD",
"TIMESTAMPDIFF"};
-	static final String[] TernaryMethodNames = {"trim", "locate", "substring", "like", "timestampAdd",
"timestampDiff"};
+	static final String[] TernaryMethodNames = {"ansiTrim", "locate", "substring", "like", "timestampAdd",
"timestampDiff"};
 	static final String[] TernaryResultType = {ClassName.StringDataValue, 
 			ClassName.NumberDataValue,
 			ClassName.ConcatableDataValue,
@@ -206,8 +206,9 @@
 	{
 		receiver = receiver.bindExpression(fromList, subqueryList, 
 			aggregateVector);
-		leftOperand = leftOperand.bindExpression(fromList, subqueryList, 
-			aggregateVector);
+
+		leftOperand = leftOperand.bindExpression(fromList, subqueryList,
+			    aggregateVector);
 
 		if (rightOperand != null)
 		{
@@ -288,8 +289,11 @@
 		if (operatorType == TRIM)
 		{
 			mb.push(trimType);
+			leftOperand.generateExpression(acb, mb);
+			mb.upCast(leftInterfaceType);
+
 			mb.getField(field);
-			nargs = 2;
+			nargs = 3;
 			receiverType = receiverInterfaceType;
 		}
 		else if (operatorType == LOCATE)

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?view=diff&rev=546183&r1=546182&r2=546183
==============================================================================
--- 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 Jun
11 09:25:33 2007
@@ -851,6 +851,39 @@
 		return retval;
 	}
 
+    /**
+     * Generate a trim operator node
+     * @param trimSpec one of Leading, Trailing or Both.
+     * @param trimChar the character to trim. Can be null in which case it defaults
+     * to ' '.
+     * @param trimSource expression to be trimmed.
+     */
+    private ValueNode getTrimOperatorNode(Integer trimSpec, ValueNode trimChar,
+        ValueNode trimSource, ContextManager cm) throws StandardException
+    {
+        if (trimChar == null)
+        {
+        	trimChar = (CharConstantNode) nodeFactory.getNode(
+							C_NodeTypes.CHAR_CONSTANT_NODE,
+							" ",
+							getContextManager());
+        }
+        return (ValueNode) nodeFactory.getNode(
+							C_NodeTypes.TRIM_OPERATOR_NODE,
+							trimSource, // receiver
+							trimChar,   // leftOperand.
+							null,
+							ReuseFactory.getInteger(TernaryOperatorNode.TRIM),
+							trimSpec,
+							cm == null ? getContextManager() : cm);
+    }
+
+    private boolean ansiTrimSpecFollows()
+    {
+        return (getToken(2).kind == LEADING || getToken(2).kind == TRAILING
+                || getToken(2).kind == BOTH);
+    }
+    
 	/**
 	 * Determine whether the next sequence of tokens can be the beginning
 	 * of a remainingPredicate() rule.
@@ -1857,7 +1890,7 @@
 //found -- DERBY-PROPERTIES. Treat rest of the characters on the line
 //as one big token and then fetch keyname=value pairs from that token.
 <PROPERTIES_LIST> TOKEN :
-{	
+{
 	<CHECK_PROPERTIES: (~["\n","\r"])* ("\n"|"\r"|"\r\n")?> : DEFAULT
 }
 
@@ -1981,6 +2014,7 @@
 |	<JOIN: "join">
 |	<KEY: "key">
 |	<LAST: "last">
+|	<LEADING: "leading">
 |	<LEFT: "left">
 |	<LIKE: "like">
 |	<LOWER: "lower">
@@ -2051,6 +2085,8 @@
 |	<TRANSACTION: "transaction">
 |	<TRANSLATE: "translate">
 |	<TRANSLATION: "translation">
+|	<TRAILING: "trailing">
+|	<TRIM: "trim">
 |	<TRUE: "true">
 |	<TS: "ts">
 |	<UNION: "union">
@@ -5687,6 +5723,7 @@
 	}
 }
 
+
 /*
  * <A NAME="primary">primary</A>
  * 
@@ -6237,20 +6274,85 @@
 {
 	ValueNode	source;
 	Integer		trimType;
+	ValueNode	ansiTrimNode;
 }
 {
 	trimType = trimType() <LEFT_PAREN> source = additiveExpression(null,0,false) <RIGHT_PAREN>
 	{
-		return (ValueNode) nodeFactory.getNode(
-									C_NodeTypes.TRIM_OPERATOR_NODE,
-									source,
-									source,
-									null,
-									ReuseFactory.getInteger(TernaryOperatorNode.TRIM),
-									trimType,
-									getContextManager());
+		return getTrimOperatorNode(trimType, null, source, null);
+	}
+|
+	<TRIM> ansiTrimNode = ansiTrim()
+	{
+		return ansiTrimNode;
+	}
+
+}
+
+
+ValueNode
+ansiTrim() throws StandardException  :
+{
+    Integer trimSpec = ReuseFactory.getInteger(StringDataValue.BOTH);
+    ValueNode trimChar = null;
+    ValueNode trimSource = null;
+}
+{
+    LOOKAHEAD ({ansiTrimSpecFollows()})
+    <LEFT_PAREN> trimSpec = ansiTrimSpec()
+    (
+         // LEADING FROM <source>
+         LOOKAHEAD(<FROM>)
+         <FROM> trimSource = additiveExpression(null,0,false) <RIGHT_PAREN>
+         {
+             return getTrimOperatorNode(trimSpec, trimChar, trimSource, null);
+         }
+     |
+         // LEADING <char> FROM <source>
+         trimChar = additiveExpression(null,0,false) <FROM> trimSource = additiveExpression(null,0,false)
<RIGHT_PAREN>
+         {
+             return getTrimOperatorNode(trimSpec, trimChar, trimSource, null);
+         }
+    )
+|
+    LOOKAHEAD ({!ansiTrimSpecFollows()})
+    <LEFT_PAREN> trimChar = additiveExpression(null,0,false)
+    (
+        <FROM> trimSource = additiveExpression(null,0,false) <RIGHT_PAREN>
+        {
+            return getTrimOperatorNode(trimSpec, trimChar, trimSource, null);
+        }
+    |
+        <RIGHT_PAREN>
+        {
+            // expr was trim(e)-- we assigned e to trimChar but it is really the trimSource
+            return getTrimOperatorNode(trimSpec, null, trimChar, null);
+        }
+    )
+}
+
+Integer
+ansiTrimSpec() :
+{
+}
+{
+	<TRAILING>
+	{
+		return ReuseFactory.getInteger(StringDataValue.TRAILING);
+	}
+|
+	<LEADING>
+	{
+		return ReuseFactory.getInteger(StringDataValue.LEADING);
+	}
+|
+	<BOTH>
+	{
+		return ReuseFactory.getInteger(StringDataValue.BOTH);
 	}
 }
+
+
 Integer
 trimType() :
 {
@@ -6410,6 +6512,7 @@
                 getToken(1).kind == LCASE ||
                 getToken(1).kind == LTRIM ||
                 getToken(1).kind == RTRIM ||
+                getToken(1).kind == TRIM ||
                 getToken(1).kind == DATE ||
                 getToken(1).kind == TIME ||
                 getToken(1).kind == TIMESTAMP ||
@@ -6516,14 +6619,11 @@
                 if( isJDBCEscape)
                         return (ValueNode) nodeFactory.getNode(
 								C_NodeTypes.CHAR_LENGTH_OPERATOR_NODE,
-								(ValueNode) nodeFactory.getNode(
-								        C_NodeTypes.TRIM_OPERATOR_NODE,
-								        value,
-										value,
-										null,
-								        ReuseFactory.getInteger(TernaryOperatorNode.TRIM),
-								        ReuseFactory.getInteger(StringDataValue.TRAILING),
-                                        localCM),
+								getTrimOperatorNode(
+									ReuseFactory.getInteger(StringDataValue.TRAILING),
+									null,
+									value,
+									localCM),
 								localCM);
 		return (ValueNode) nodeFactory.getNode(
 							C_NodeTypes.DB2_LENGTH_OPERATOR_NODE,
@@ -12679,6 +12779,7 @@
 |	tok = <JOIN>
 |	tok = <KEY>
 |	tok = <LAST>
+|	tok = <LEADING>
 |	tok = <LEFT>
 |	tok = <LIKE>
 |	tok = <LOWER>
@@ -12747,6 +12848,7 @@
 |	tok = <TIMEZONE_HOUR>
 |	tok = <TIMEZONE_MINUTE>
 |	tok = <TO>
+|	tok = <TRAILING>
 |	tok = <TRANSACTION>
 |	tok = <TRANSLATE>
 |	tok = <TRANSLATION>
@@ -12775,6 +12877,7 @@
 |	tok = <LONGINT>
 |	tok = <LTRIM>
 |	tok = <RTRIM>
+|	tok = <TRIM>
 |	tok = <SUBSTR>
 |	tok = <XML>
 |	tok = <XMLPARSE>

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?view=diff&rev=546183&r1=546182&r2=546183
==============================================================================
--- 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 Jun 11 09:25:33
2007
@@ -618,7 +618,13 @@
                 <arg>sequenceName</arg>
             </msg>
 
-            <msg>
+			<msg>
+				<name>22020</name>
+				<text>Invalid trim string, '{0}'. The trim string must be exactly one character
or NULL. It cannot be more than one character.</text>
+				<arg>string</arg>
+			</msg>
+
+			<msg>
                 <name>22025</name>
                 <text>Escape character must be followed by escape character, '_', or
'%'. It cannot be followed by any other character or be at the end of the pattern.</text>
             </msg>

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?view=diff&rev=546183&r1=546182&r2=546183
==============================================================================
--- 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 Jun 11 09:25:33 2007
@@ -692,6 +692,7 @@
     String LOSS_OF_PRECISION_EXCEPTION                                 = "22015.S.1";
 	String LANG_FORMAT_EXCEPTION                                       = "22018";
 	String LANG_INVALID_ESCAPE_CHARACTER                               = "22019";
+	String LANG_INVALID_TRIM_CHARACTER                                 = "22020";
 	String LANG_INVALID_ESCAPE_SEQUENCE                                = "22025";
 	String LANG_INVALID_TRIM_SET                                       = "22027";
     String LANG_STRING_TOO_LONG                                        = "22028";

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out?view=diff&rev=546183&r1=546182&r2=546183
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out
Mon Jun 11 09:25:33 2007
@@ -1363,7 +1363,9 @@
 ij> values OCTET_LENGTH('asdfasdfasdf');
 ERROR 42Y03: 'OCTET_LENGTH' is not recognized as a function or procedure.
 ij> values TRIM('x' FROM 'xasdf x');
-ERROR 42X01: Syntax error: Encountered "FROM" at line 1, column 17.
+1      
+-------
+asdf   
 ij> values SUBSTRING('12345' FROM 3 FOR 2);
 ERROR 42X80: VALUES clause must contain at least one element. Empty elements are not allowed.

 ij> -- Tests for explicit nulls. Not allowed in DB2, defect 5589 

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AnsiTrimTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AnsiTrimTest.java?view=auto&rev=546183
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AnsiTrimTest.java
(added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AnsiTrimTest.java
Mon Jun 11 09:25:33 2007
@@ -0,0 +1,179 @@
+package org.apache.derbyTesting.functionTests.tests.lang;
+
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
+import org.apache.derbyTesting.junit.JDBC;
+import junit.framework.Test;
+import junit.framework.TestSuite;
+
+import java.sql.*;
+import java.io.IOException;
+
+/**
+ * Functional test for ansi trim functionality added for DERBY-1623.
+ * 
+ * @since May 6, 2007
+ */
+public class AnsiTrimTest extends BaseJDBCTestCase {
+
+    /**
+     * Create a test case with the given name.
+     *
+     * @param name of the test case.
+     */
+    public AnsiTrimTest(String name) {
+        super(name);
+    }
+
+    public static Test suite() {
+        TestSuite suite = new TestSuite("AnsiTrimTest");
+        suite.addTestSuite(AnsiTrimTest.class);
+        return new CleanDatabaseTestSetup(suite) {
+            public void decorateSQL(Statement s)
+                    throws SQLException {
+                s.executeUpdate("create table tt (id int, v varchar(16), c char(16), cl clob(10240))");
+                s.executeUpdate("insert into tt values (1, 'abcaca', 'abcaca', 'abcaca')");
+                s.executeUpdate("create table nt (v varchar(2))");
+                s.executeUpdate("insert into nt values (null)");
+            }
+
+        };
+    }
+
+    /**
+     * trim a column with a constant trim char.
+     */
+    public void testColumnsWithConstant()
+            throws SQLException {
+        positiveTest("select trim(' ' from v) from tt where id = 1", "abcaca");
+        positiveTest("select trim('a' from v) from tt where id = 1", "bcac");
+        positiveTest("select trim(both 'a' from v) from tt where id = 1", "bcac");
+        positiveTest("select trim(leading 'a' from v) from tt where id = 1", "bcaca");
+        positiveTest("select trim(trailing 'a' from v) from tt where id = 1", "abcac");
+
+        // chars are padded with spaces at the end.
+        positiveTest("select trim(' ' from c) from tt where id = 1", "abcaca");
+        positiveTest("select trim('a' from c) from tt where id = 1", "bcaca          ");
+        positiveTest("select trim(both 'a' from c) from tt where id = 1", "bcaca        
 ");
+        positiveTest("select trim(leading 'a' from c) from tt where id = 1", "bcaca     
    ");
+        positiveTest("select trim(trailing 'a' from c) from tt where id = 1", "abcaca   
      ");
+
+        //positiveTest("select trim(' ' from cl) from tt", "abcaca");
+    }
+
+    /**
+     * Use a few expressions as the trim character.
+     */
+    public void testTrimCharIsExpr()
+            throws SQLException {
+        String expr;
+        positiveTest(
+                "SELECT count(*) FROM tt  " +
+                "WHERE id = 1 AND (trim (leading substr(v,1,1) from v)) = 'bcaca'", new Integer(1));
+
+        positiveTest(
+                "select trim (both (case when length(v) = 6 then 'a' else 'b' end) from v)
from tt",
+                "bcac");
+
+        positiveTest(
+                "SELECT trim(TRAILING lcase(ucase('a')) from v) from tt", "abcac");
+        
+    }
+
+    /**
+     * A clob column is the input source.
+     */
+    public void testTrimFromClobColumn()
+            throws SQLException, IOException {
+        String sql = "SELECT trim('a' from cl) from tt";
+        ResultSet rs = null;
+
+        PreparedStatement ps = null;
+        try {
+            ps = prepareStatement(sql);
+            rs = ps.executeQuery();
+            // positiveTest does not deal with clobs.
+            assertTrue(rs.next());
+            Clob clob = rs.getClob(1);
+            char[] cbuf = new char[128];
+            assertEquals(4, clob.length());
+            clob.getCharacterStream().read(cbuf);
+            assertEquals("bcac", new String(cbuf, 0, 4));
+            assertFalse(rs.next());
+        } finally {
+            if (rs != null) { try { rs.close(); } catch (SQLException e) {/* ignore */} }
+            if (ps != null) { try { ps.close(); } catch (SQLException e) {/* ignore */} }
+        }
+    }
+    
+        
+    /**
+     * Use a few different expressions as the trim source.
+     */
+    public void testTrimSourceIsExpr()
+            throws SQLException {
+        positiveTest("SELECT trim(' ' from cast(v as char(7))) from tt", "abcaca");
+        positiveTest("SELECT trim('a' from v||v) from tt", "bcacaabcac");
+        positiveTest("SELECT trim('a' from ltrim(rtrim(c))) from tt", "bcac");        
+    }
+
+    /**
+     * All the characters are trimmed.
+     */
+    public void testTrimResultIsEmpty()
+            throws SQLException {
+        positiveTest("select trim(' ' from '     ' ) from tt", "");
+        positiveTest("select trim(LEADING ' ' from '     ' ) from tt", "");
+        positiveTest("select trim(TRAILING ' ' from '     ' ) from tt", "");
+        positiveTest("select trim(BOTH ' ' from '     ' ) from tt", "");
+    }
+
+    public void testSourceIsEmpty()
+            throws SQLException {
+        positiveTest("select trim(' ' from '') from tt", "");
+        positiveTest("select trim(leading ' ' from '') from tt", "");
+        positiveTest("select trim(trailing ' ' from '') from tt", "");
+        positiveTest("select trim(both ' ' from '') from tt", "");
+    }
+
+    public void testSourceIsNull()
+            throws SQLException {
+        positiveTest("select trim(' ' from v) from nt", null);
+        positiveTest("select trim(leading ' ' from v) from nt", null);
+        positiveTest("select trim(trailing ' ' from v) from nt", null);
+        positiveTest("select trim(both ' ' from v) from nt", null);                
+    }
+
+    public void testSourceIsSingleChar()
+            throws SQLException {
+        positiveTest("select trim(' ' from 'a') from nt", "a");
+        positiveTest("select trim(leading ' ' from 'a') from nt", "a");
+        positiveTest("select trim(trailing ' ' from 'a') from nt", "a");
+        positiveTest("select trim(both ' ' from 'a') from nt", "a");        
+
+        positiveTest("select trim('a' from 'a') from nt", "");
+        positiveTest("select trim(leading 'a' from 'a') from nt", "");
+        positiveTest("select trim(trailing 'a' from 'a') from nt", "");
+        positiveTest("select trim(both 'a' from 'a') from nt", "");                
+    }
+
+    public void testCharIsNull() throws SQLException {
+        positiveTest("select trim ((values cast (null as char(1))) from v) from tt", null);
+    }
+
+    private void positiveTest(String sql, Object expected)
+            throws SQLException {
+        ResultSet rs = null;
+        PreparedStatement ps = null;
+        try {
+            ps = prepareStatement(sql);
+            rs = ps.executeQuery();
+            JDBC.assertFullResultSet(rs, new Object[][] {{expected}}, false, /*closeResultSet=*/true);
+        } finally {
+            // assertFullResultSet closes rs.
+            if (ps != null) { ps.close(); }
+        }
+    }
+}
+
+

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AnsiTrimTest.java
------------------------------------------------------------------------------
    svn:eol-style = native

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java?view=diff&rev=546183&r1=546182&r2=546183
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
Mon Jun 11 09:25:33 2007
@@ -59,7 +59,7 @@
         // the nightly runs.
         // suite.addTest(largeCodeGen.suite());
 
-
+        suite.addTest(AnsiTrimTest.suite());
         suite.addTest(CreateTableFromQueryTest.suite());
         suite.addTest(DatabaseClassLoadingTest.suite());
         suite.addTest(DynamicLikeOptimizationTest.suite());



Mime
View raw message