db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From mi...@apache.org
Subject svn commit: r542553 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/LikeEscapeOperatorNode.java testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest2.java
Date Tue, 29 May 2007 14:48:31 GMT
Author: mikem
Date: Tue May 29 07:48:30 2007
New Revision: 542553

URL: http://svn.apache.org/viewvc?view=rev&rev=542553
Log:
DERBY-2710

disabled < and >= lik ooptimization for non unicode default collated databases.  

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/LikeEscapeOperatorNode.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest2.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/LikeEscapeOperatorNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/LikeEscapeOperatorNode.java?view=diff&rev=542553&r1=542552&r2=542553
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/LikeEscapeOperatorNode.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/LikeEscapeOperatorNode.java
Tue May 29 07:48:30 2007
@@ -572,6 +572,29 @@
             return this;
         }
 
+        /* 
+         * In first implementation of non default collation don't attempt
+         * any transformations for LIKE.  
+         *
+         * Future possibilities:
+         * o is it valid to produce a >= clause for a leading constant with
+         *   a wildcard that works across all possible collations?  Is 
+         *   c1 like a% the same as c1 like a% and c1 >= a'\u0000''\u0000',... ?
+         *
+         *   This is what was done for national char's.  It seems like a 
+         *   given collation could sort: ab, a'\u0000'.  Is there any guarantee
+         *   about the sort of the unicode '\u0000'.
+         *
+         * o National char's don't try to produce a < than, is there a way
+         *   in collation?
+         */
+        if (receiver.getTypeServices().getCollationType() != 
+                StringDataValue.COLLATION_TYPE_UCS_BASIC)
+        {
+            // don't do any < or >= transformations for non default collations.
+            return this;
+        }
+
         /* This is where we do the transformation for LIKE to make it 
          * optimizable.
          * c1 LIKE 'asdf%' -> c1 LIKE 'asdf%' AND c1 >= 'asdf' AND c1 < 'asdg'

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest2.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest2.java?view=diff&rev=542553&r1=542552&r2=542553
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest2.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest2.java
Tue May 29 07:48:30 2007
@@ -28,6 +28,9 @@
 import java.sql.SQLException;
 import java.sql.Statement;
 
+import java.text.Collator;
+import java.text.RuleBasedCollator;
+
 import java.util.Locale;
 import java.util.Properties; 
 
@@ -84,17 +87,9 @@
 T21: (TODO) Testing with views
 T22: (TODO) Testing with global temporary tables
 T23: (TODO) Alter table testing. Two specific cases 1)add a new character column and 2)increase
the length of an existing character
-T24: (TODO) Need to add a test case for DERBY-2669 If no territory attribute is not specified
at create database time, then we should ignore the collation attribute if specified.
-column(this 2nd test should not impact the collation type setting of the character column).
-
-13)Performance- CollatorSQLChar has a method called getCollationElementsForString which currently
gets called by like method. 
-getCollationElementsForString gets the collation elements for the value of CollatorSQLChar
class. But say like method is looking 
-for pattern 'A%' and the value of CollatorSQLChar is 'BXXXXXXXXXXXXXXXXXXXXXXX'. This is
eg of one case where it would have been 
-better to get collation element one character of CollatorSQLChar value at a time so we don't
go through the process of getting 
-collation elements for the entire string when we don't really need. This is a performance
issue and could be taken up at the end 
-of the implementation. Comments on this from Dan and Dag can be found in DERBY-2416. 
-15)DERBY-2668 At the time of compilation of a comparison operation, if the collation types
of the operands do not match, then we 
-should throw a meaningful error message
+T24: (DONE) DERBY-2669 If no territory attribute is specified at create 
+            database time, then create collated db based on default territory
+            of Database.
 
 
 **/
@@ -359,66 +354,418 @@
      **************************************************************************
      */
 
-	private boolean is142JVM() 
-	{
-	    String java_version = System.getProperty("java.version");
+    /**
+     * RESOLVE - unfinished LIKE test with dataset of all unicode characters
+     **/
+    private static final void printRuleBasedCollator()
+    {
+        // get en_US Collator rules
+        RuleBasedCollator en_USCollator = 
+            (RuleBasedCollator)Collator.getInstance(Locale.US);
+        String en_rules = en_USCollator.getRules();
+
+        System.out.println("ENGLISH RULES: " + en_rules);
+        System.out.println("ENGLISH RULES: " + formatString(en_rules, true));
+        System.out.println("ENGLISH RULES: " + formatString(en_rules, false));
+    }
 
-        // get string indexes for major/minor numbers
-        int jvm_major_idx = java_version.indexOf('.');
-        int jvm_minor_idx = java_version.indexOf('.', jvm_major_idx + 1);
+    /**
+     * RESOLVE - unfinished LIKE test with dataset of all unicode characters
+     **/
+    private static final String formatString(
+    String  str,
+    boolean all)
+    {
+        // format it as \u0000(x)\u0001(x)...
+        String ret_val = "";
 
-        
-        int jvm_major = 
-            Integer.parseInt(
-                java_version.substring(0, jvm_major_idx));
-        int jvm_minor = 
-            Integer.parseInt(
-                java_version.substring(jvm_major_idx + 1, jvm_minor_idx));
+        for (int i = 0; i < str.length(); i++)
+        {
+            char ch = str.charAt(i);
+
+            if (!all && (ch <= 128))
+            {
+                ret_val += Character.toString(ch);
+            }
+            else
+            {
+                ret_val += 
+                    "\\u" + Integer.toString(ch, 16) + 
+                    "(" + Character.toString(ch) + ")";
+            }
+        }
+        return(ret_val);
+    }
 
+    /**
+     * RESOLVE - unfinished LIKE test with dataset of all unicode characters
+     **/
+    private final void formatLikeResults(
+    Connection  conn,
+    String      query)
+        throws SQLException
+    {
+        Statement s     = conn.createStatement();
+        ResultSet rs    = s.executeQuery(query);
 
-        return(jvm_major == 1 && jvm_minor == 4);
-	}
+
+        String    txt_str   = null;
+        String    ucode_str = null;
+        String    mixed_str = null;
+
+        int       count     = 0;
+
+        while (rs.next())
+        {
+            count++;
+
+            if (count == 1)
+            {
+                txt_str   = "{";
+                ucode_str = "{";
+                mixed_str = "{";
+            }
+            else
+            {
+                txt_str   += ", ";
+                ucode_str += ", ";
+                mixed_str += ", ";
+            }
+
+            String ret_val = rs.getString(1);
+
+            txt_str   += "\"" + ret_val + "\"";
+            
+            // string using \u0104 format for chars
+            ucode_str += formatString(ret_val, true);
+
+            mixed_str += "{\"" + formatString(ret_val, false) + "\"}";
+        }
+
+        if (count != 0)
+        {
+            txt_str   += "}";
+            ucode_str += "}";
+            mixed_str += "}";
+        }
+
+        System.out.println(
+            "Query: " + query + 
+            "\nnumber rows  :" + count     +
+            "\nString Result:" + txt_str   +
+            "\nUcode  Result:" + ucode_str +
+            "\nmixed  Result:" + mixed_str);
+
+        rs.close();
+        s.close();
+    }
 
     /**
-     * Test simple call to DatabaseMetaData.getColumns()
-     * <p>
-     * This test is the same form of the getColumns() call that 
-     * the IMPORT and EXPORT system procedures depend on. 
-     * Currently on ibm and sun 1.4.2 jvm's this test fails.
+     * RESOLVE - unfinished LIKE test with dataset of all unicode characters
      **/
-    private void runDERBY_2703(Connection conn, int db_index)
+    private final void printLikeResults(Connection conn)
         throws SQLException
     {
-        // DERBY-2703, get columns does not work in collated dbs under sun
-        // and ibm 142 jvm's.
-        if (is142JVM())
-            return;
+        /*
+        RESOLVE-COMMENTED OUT
 
-        setUpTable(conn);
+        for (int i = 0; i < LIKE_ALLVALS_TEST.length; i++)
+        {
+            if (verbose_debug)
+            {
+                System.out.println(
+                    "Running like allvals test[" + i + "] = " + 
+                    LIKE_ALLVALS_TEST[i]);
+            }
 
-        ResultSet rs = 
-            conn.getMetaData().getColumns(null, "APP", "CUSTOMER", "%");
+            formatLikeResults(
+                conn,
+                "SELECT STR_VARCHAR FROM ALLVALS WHERE STR_VARCHAR LIKE " +
+                    "'" + LIKE_ALLVALS_TEST[i] + "'");
 
-        Assert.assertTrue("catch bug where no rows are returned.", rs.next());
+            formatLikeResults(
+                conn,
+                "SELECT STR_LONGVARCHAR FROM ALLVALS WHERE STR_LONGVARCHAR LIKE " +
+                    "'" + LIKE_ALLVALS_TEST[i] + "'");
+
+            formatLikeResults(
+                conn,
+                "SELECT STR_CLOB FROM ALLVALS WHERE STR_CLOB LIKE " +
+                    "'" + LIKE_ALLVALS_TEST[i] + "'");
+
+            formatLikeResults(
+                conn,
+                "SELECT STR_CHAR FROM ALLVALS WHERE STR_CHAR LIKE " +
+                    "'" + LIKE_ALLVALS_TEST[i] + "'");
+        }
+        */
+    }
+
+    private void checkLangBasedQuery(
+    Connection  conn,
+    String      query, 
+    String[][]  expectedResult,
+    boolean     ordered) 
+        throws SQLException 
+    {
+        Statement s  = conn.createStatement();
+        ResultSet rs = s.executeQuery(query);
 
         if (verbose_debug)
-            System.out.println("column =" + rs.getString(4));
+        {
+            System.out.println("executed query: " + query);
+        }
 
-        while (rs.next())
+        if (expectedResult == null) //expecting empty resultset from the query
         {
             if (verbose_debug)
-                System.out.println("column =" + rs.getString(4));
+                System.out.println(
+                    "executed query expecting no results: " + query);
+
+            JDBC.assertEmpty(rs);
         }
+        else
+        {
+            if (ordered)
+            {
+                if (verbose_debug)
+                    System.out.println(
+                        "executed query expecting ordered results: " + query);
 
-        // TODO should verify all columns are returned.
+                JDBC.assertFullResultSet(rs, expectedResult);
+            }
+            else
+            {
+                if (verbose_debug)
+                    System.out.println(
+                        "executed query expecting unordered results: " + query);
+
+                JDBC.assertUnorderedResultSet(rs, expectedResult);
+            }
+        }
 
         rs.close();
+        s.close();
+    }
 
-        dropTable(conn);
+    private void checkOneParamQuery(
+    Connection  conn,
+    String      query, 
+    String      param,
+    String[][]  expectedResult, 
+    boolean     ordered) 
+        throws SQLException 
+    {
+        PreparedStatement   ps = conn.prepareStatement(query);
+        ps.setString(1, param);
+        ResultSet           rs = ps.executeQuery();
+
+        if (expectedResult == null) //expecting empty resultset from the query
+        {
+            JDBC.assertEmpty(rs);
+        }
+        else
+        {
+            if (ordered)
+                JDBC.assertFullResultSet(rs,expectedResult);
+            else
+                JDBC.assertUnorderedResultSet(rs, expectedResult);
+        }
+
+
+        // re-execute it to test path through the cache
+        ps.setString(1, param);
+        rs = ps.executeQuery();
+
+        if (expectedResult == null) //expecting empty resultset from the query
+        {
+            JDBC.assertEmpty(rs);
+        }
+        else
+        {
+            if (ordered)
+                JDBC.assertFullResultSet(rs,expectedResult);
+            else
+                JDBC.assertUnorderedResultSet(rs, expectedResult);
+        }
+
+        rs.close();
+        ps.close();
+        conn.commit();
+    }
+
+    /**
+     * Perform export using SYSCS_UTIL.SYSCS_EXPORT_TABLE procedure.
+     */
+    protected void doExportTable(
+    Connection  conn,
+    String      schemaName, 
+    String      tableName, 
+    String      fileName, 
+    String      colDel , 
+    String      charDel, 
+    String      codeset) 
+        throws SQLException 
+    {
+        CallableStatement ps = 
+            conn.prepareCall(
+                "call SYSCS_UTIL.SYSCS_EXPORT_TABLE (? , ? , ? , ?, ? , ?)");
+        ps.setString(1, schemaName);
+        ps.setString(2, tableName);
+        ps.setString(3, fileName);
+        ps.setString(4, colDel);
+        ps.setString(5, charDel);
+        ps.setString(6, codeset);
+        ps.executeUpdate();
+        ps.close();
+    }
+
+    /**
+     * Perform import using SYSCS_UTIL.SYSCS_IMPORT_TABLE procedure.
+     */
+    protected void doImportTable(
+    Connection  conn,
+    String      schemaName, 
+    String      tableName, 
+    String      fileName, 
+    String      colDel, 
+    String      charDel, 
+    String      codeset,
+    int         replace) 
+        throws SQLException 
+    {
+        CallableStatement ps = 
+            conn.prepareCall(
+                "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (?, ?, ?, ?, ?, ?, ?)");
+        ps.setString(1, schemaName);
+        ps.setString(2, tableName);
+        ps.setString(3, fileName);
+        ps.setString(4, colDel);
+        ps.setString(5, charDel);
+        ps.setString(6, codeset);
+        ps.setInt(   7, replace);
+        ps.executeUpdate();
+        ps.close();
+    }
+
+    /**
+     * Produce an expect row set given the order and asc/desc info.
+     * <p>
+     * Given the expected order of rows, the offset of first and last row
+     * to return, and whether rows will be ascending or descending produce
+     * a 2d expected row set.  Each row in the row set represents a row 
+     * with 2 columns (ID, NAME) from the CUSTOMER table used throughout
+     * this test.
+     *
+     * @param expected_order    Expected order of rows in this language.
+     * @param start_offset      expect rows starting at 
+     *                          expected_order[start_offset] up to and including
+     *                          expected_order[stop_offset].
+     * @param stop_offset       expect rows starting at 
+     *                          expected_order[start_offset] up to and including
+     *                          expected_order[stop_offset].
+     * @param ascending_order   true if rows are in order, else rows are in
+     *                          reverse order.
+     **/
+    private String[][] full_row_set(
+    int[]   expected_order,
+    int     start_offset,
+    int     stop_offset,
+    boolean ascending_order)
+    {
+        String[][] ret_order = null;
+
+        int num_vals = stop_offset - start_offset + 1;
+
+        if (num_vals > 0)
+        {
+            ret_order = new String[num_vals][2];
+
+            if (ascending_order)
+            {
+                int dest = 0;
+                for (int src = start_offset; src <= stop_offset; src++)
+                {
+                    ret_order[dest][0] = String.valueOf(expected_order[src]);
+                    ret_order[dest][1] = NAMES[expected_order[src]];
+                    dest++;
+                }
+            }
+            else
+            {
+                // rows are expected in reverse order from what is passsed in,
+                // so swap them to create the output expected result array.
+                int dest = 0;
+                for (int src = stop_offset; src >= start_offset; src--)
+                {
+                    ret_order[dest][0] = String.valueOf(expected_order[src]);
+                    ret_order[dest][1] = NAMES[expected_order[src]];
+                    dest++;
+                }
+            }
+        }
 
+        return(ret_order);
     }
 
     /**
+     * Produce an expect row set given list and offset of row in list.
+     * <p>
+     * Given the list of rows and offset of the expected row in the list
+     * produce a 2d expected row set.  If expected_row is -1 then no row
+     * set is returned.  Each row in the row set represents a row 
+     * with 2 columns (ID, NAME) from the CUSTOMER table used throughout
+     * this test.
+     *
+     * @param expected_row      -1 if no expected row, else 
+     *                          ret_list[expected_row] is single value expected.
+     * @param ret_list          list of strings in data set.
+     **/
+    private String[][] full_row_single_value(
+    int         expected_row,
+    String[]    ret_list)
+    {
+        String[][] ret_order = null;
+
+        if (expected_row != -1)
+        {
+            // if not -1 then exactly one row expected.
+            ret_order = new String[1][2];
+            ret_order[0][0] = String.valueOf(expected_row);
+            ret_order[0][1] = ret_list[expected_row];
+        }
+
+        return(ret_order);
+    }
+
+	private boolean is142JVM() 
+	{
+	    String java_version = System.getProperty("java.version");
+
+        // get string indexes for major/minor numbers
+        int jvm_major_idx = java_version.indexOf('.');
+        int jvm_minor_idx = java_version.indexOf('.', jvm_major_idx + 1);
+
+        
+        int jvm_major = 
+            Integer.parseInt(
+                java_version.substring(0, jvm_major_idx));
+        int jvm_minor = 
+            Integer.parseInt(
+                java_version.substring(jvm_major_idx + 1, jvm_minor_idx));
+
+
+        return(jvm_major == 1 && jvm_minor == 4);
+	}
+
+
+
+    /**************************************************************************
+     * Set up and clean up routines.
+     **************************************************************************
+     */
+
+    /**
      * Creates a database and return connection to database.
      * <p>
      * Creates a database with territory/collation as indicated by db_index
@@ -488,6 +835,8 @@
 
     private void setUpLikeTable(Connection conn) throws SQLException 
     {
+        conn.setAutoCommit(false);
+
         Statement s = conn.createStatement();
         s.execute(
             "CREATE TABLE CUSTOMER ("              +
@@ -497,217 +846,178 @@
                 "NAME_CLOB          CLOB,"         +
                 "ID                 INT)");
 
-        conn.setAutoCommit(false);
         PreparedStatement ps = 
             conn.prepareStatement("INSERT INTO CUSTOMER VALUES(?,?,?,?,?)");
-
-        for (int i = 0; i < LIKE_NAMES.length; i++)
-        {
-            ps.setString(1, LIKE_NAMES[i]);
-            ps.setString(2, LIKE_NAMES[i]);
-            ps.setString(3, LIKE_NAMES[i]);
-            ps.setString(4, LIKE_NAMES[i]);
-            ps.setInt(   5, i);
-            ps.executeUpdate();
-        }
-
-        conn.commit();
-        ps.close();
-        s.close();
-    }
-
-    /**
-     * Perform export using SYSCS_UTIL.SYSCS_EXPORT_TABLE procedure.
-     */
-    protected void doExportTable(
-    Connection  conn,
-    String      schemaName, 
-    String      tableName, 
-    String      fileName, 
-    String      colDel , 
-    String      charDel, 
-    String      codeset) 
-        throws SQLException 
-    {
-        CallableStatement ps = 
-            conn.prepareCall(
-                "call SYSCS_UTIL.SYSCS_EXPORT_TABLE (? , ? , ? , ?, ? , ?)");
-        ps.setString(1, schemaName);
-        ps.setString(2, tableName);
-        ps.setString(3, fileName);
-        ps.setString(4, colDel);
-        ps.setString(5, charDel);
-        ps.setString(6, codeset);
-        ps.executeUpdate();
-        ps.close();
-    }
-
-    /**
-     * Perform import using SYSCS_UTIL.SYSCS_IMPORT_TABLE procedure.
-     */
-    protected void doImportTable(
-    Connection  conn,
-    String      schemaName, 
-    String      tableName, 
-    String      fileName, 
-    String      colDel, 
-    String      charDel, 
-    String      codeset,
-    int         replace) 
-        throws SQLException 
-    {
-        CallableStatement ps = 
-            conn.prepareCall(
-                "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (?, ?, ?, ?, ?, ?, ?)");
-        ps.setString(1, schemaName);
-        ps.setString(2, tableName);
-        ps.setString(3, fileName);
-        ps.setString(4, colDel);
-        ps.setString(5, charDel);
-        ps.setString(6, codeset);
-        ps.setInt(   7, replace);
-        ps.executeUpdate();
+
+        for (int i = 0; i < LIKE_NAMES.length; i++)
+        {
+            ps.setString(1, LIKE_NAMES[i]);
+            ps.setString(2, LIKE_NAMES[i]);
+            ps.setString(3, LIKE_NAMES[i]);
+            ps.setString(4, LIKE_NAMES[i]);
+            ps.setInt(   5, i);
+            ps.executeUpdate();
+        }
+
         ps.close();
+        s.close();
     }
 
+    
     /**
-     * Produce an expect row set given the order and asc/desc info.
-     * <p>
-     * Given the expected order of rows, the offset of first and last row
-     * to return, and whether rows will be ascending or descending produce
-     * a 2d expected row set.  Each row in the row set represents a row 
-     * with 2 columns (ID, NAME) from the CUSTOMER table used throughout
-     * this test.
-     *
-     * @param expected_order    Expected order of rows in this language.
-     * @param start_offset      expect rows starting at 
-     *                          expected_order[start_offset] up to and including
-     *                          expected_order[stop_offset].
-     * @param stop_offset       expect rows starting at 
-     *                          expected_order[start_offset] up to and including
-     *                          expected_order[stop_offset].
-     * @param ascending_order   true if rows are in order, else rows are in
-     *                          reverse order.
+     * RESOLVE - unfinished LIKE test with dataset of all unicode characters
      **/
-    private String[][] full_row_set(
-    int[]   expected_order,
-    int     start_offset,
-    int     stop_offset,
-    boolean ascending_order)
+    private void setUpALLVALS(Connection conn) 
+        throws SQLException 
     {
-        String[][] ret_order = null;
+        Statement s = conn.createStatement();
 
-        int num_vals = stop_offset - start_offset + 1;
+        s.execute(
+            "CREATE TABLE ALLVALS ("              +
+                "STR_CHAR          CHAR(3), "    +
+                "STR_VARCHAR       VARCHAR(40),"  +
+                "STR_LONGVARCHAR   LONG VARCHAR," +
+                "STR_CLOB          CLOB,"         +
+                "ID                INT)");
 
-        if (num_vals > 0)
+        PreparedStatement ps = 
+            conn.prepareStatement("INSERT INTO ALLVALS VALUES(?,?,?,?,?)");
+
+        char[]  single_char = new char[1];
+        char[]  leading_b   = new char[2];
+        char[]  trailing_b  = new char[2];
+        char[]  middle_b    = new char[3];
+
+        leading_b[0]                        = 'b';
+        trailing_b[trailing_b.length - 1]   = 'b';
+        middle_b[1]                         = 'b';
+
+        int max_char = (int) Character.MAX_VALUE;
+
+        long before_load_ms = System.currentTimeMillis();
+
+        for (int i = Character.MIN_VALUE; i <= max_char; i++)
         {
-            ret_order = new String[num_vals][2];
+            // insert a row with string value of a single unicode char
+            single_char[0] = (char) i;
+            String  str_val = String.valueOf(single_char);
+
+            ps.setString(1, str_val);
+            ps.setString(2, str_val);
+            ps.setString(3, str_val);
+            ps.setString(4, str_val);
+            ps.setInt(   5, i);
+            ps.executeUpdate();
 
-            if (ascending_order)
-            {
-                int dest = 0;
-                for (int src = start_offset; src <= stop_offset; src++)
-                {
-                    ret_order[dest][0] = String.valueOf(expected_order[src]);
-                    ret_order[dest][1] = NAMES[expected_order[src]];
-                    dest++;
-                }
-            }
-            else
-            {
-                // rows are expected in reverse order from what is passsed in,
-                // so swap them to create the output expected result array.
-                int dest = 0;
-                for (int src = stop_offset; src >= start_offset; src--)
-                {
-                    ret_order[dest][0] = String.valueOf(expected_order[src]);
-                    ret_order[dest][1] = NAMES[expected_order[src]];
-                    dest++;
-                }
-            }
+            // insert a row with 'b' followed by unicode value followed by 'b'
+            leading_b[1] = (char) i;
+            str_val = String.valueOf(leading_b);
+
+            ps.setString(1, str_val);
+            ps.setString(2, str_val);
+            ps.setString(3, str_val);
+            ps.setString(4, str_val);
+            ps.setInt(   5, i);
+            ps.executeUpdate();
+
+            // insert a row with unicode value followed by 'b' 
+            trailing_b[0] = (char) i;
+            str_val = String.valueOf(trailing_b);
+
+            ps.setString(1, str_val);
+            ps.setString(2, str_val);
+            ps.setString(3, str_val);
+            ps.setString(4, str_val);
+            ps.setInt(   5, i);
+            ps.executeUpdate();
+
+            // insert a row with unicode value followed by 'b' 
+            // followed by unicode value
+            middle_b[0] = (char) i;
+            middle_b[2] = (char) i;
+            str_val = String.valueOf(middle_b);
+
+            ps.setString(1, str_val);
+            ps.setString(2, str_val);
+            ps.setString(3, str_val);
+            ps.setString(4, str_val);
+            ps.setInt(   5, i);
+            ps.executeUpdate();
         }
 
-        return(ret_order);
-    }
+        long after_load_ms = System.currentTimeMillis();
 
-    /**
-     * Produce an expect row set given list and offset of row in list.
-     * <p>
-     * Given the list of rows and offset of the expected row in the list
-     * produce a 2d expected row set.  If expected_row is -1 then no row
-     * set is returned.  Each row in the row set represents a row 
-     * with 2 columns (ID, NAME) from the CUSTOMER table used throughout
-     * this test.
-     *
-     * @param expected_row      -1 if no expected row, else 
-     *                          ret_list[expected_row] is single value expected.
-     * @param ret_list          list of strings in data set.
-     **/
-    private String[][] full_row_single_value(
-    int         expected_row,
-    String[]    ret_list)
-    {
-        String[][] ret_order = null;
+        conn.commit();
 
-        if (expected_row != -1)
+        long after_commit_ms = System.currentTimeMillis();
+
+        if (verbose_debug)
         {
-            // if not -1 then exactly one row expected.
-            ret_order = new String[1][2];
-            ret_order[0][0] = String.valueOf(expected_row);
-            ret_order[0][1] = ret_list[expected_row];
+
+            System.out.println("Loaded and committed ALLVALS table:");
+            System.out.println(
+                "load time = "   + (after_load_ms   - before_load_ms) +
+                "commit time = " + (after_commit_ms - after_load_ms));
         }
 
-        return(ret_order);
+        ps.close();
+        s.close();
     }
 
 
-    private void checkLangBasedQuery(
-    Connection  conn,
-    String      query, 
-    String[][]  expectedResult,
-    boolean     ordered) 
+    private void dropLikeTable(Connection conn) 
         throws SQLException 
     {
-        Statement s  = conn.createStatement();
-        ResultSet rs = s.executeQuery(query);
-
-        if (expectedResult == null) //expecting empty resultset from the query
-            JDBC.assertEmpty(rs);
-        else
-            JDBC.assertFullResultSet(rs,expectedResult);
-
+        Statement s = conn.createStatement();
+	
+        s.execute("DROP TABLE CUSTOMER");     
         s.close();
+
     }
 
-    private void checkOneParamQuery(
-    Connection  conn,
-    String      query, 
-    String      param,
-    String[][]  expectedResult) 
-        throws SQLException 
+    /**************************************************************************
+     * run*() tests, called from the actual test*() tests.
+     **************************************************************************
+     */
+
+
+    /**
+     * Test simple call to DatabaseMetaData.getColumns()
+     * <p>
+     * This test is the same form of the getColumns() call that 
+     * the IMPORT and EXPORT system procedures depend on. 
+     * Currently on ibm and sun 1.4.2 jvm's this test fails.
+     **/
+    private void runDERBY_2703(Connection conn, int db_index)
+        throws SQLException
     {
-        PreparedStatement   ps = conn.prepareStatement(query);
-        ps.setString(1, param);
-        ResultSet           rs = ps.executeQuery();
+        // DERBY-2703, get columns does not work in collated dbs under sun
+        // and ibm 142 jvm's.
+        if (is142JVM())
+            return;
 
-        if (expectedResult == null) //expecting empty resultset from the query
-            JDBC.assertEmpty(rs);
-        else
-            JDBC.assertFullResultSet(rs,expectedResult);
+        setUpTable(conn);
+
+        ResultSet rs = 
+            conn.getMetaData().getColumns(null, "APP", "CUSTOMER", "%");
 
+        Assert.assertTrue("catch bug where no rows are returned.", rs.next());
 
-        // re-execute it to test path through the cache
-        ps.setString(1, param);
-        rs = ps.executeQuery();
+        if (verbose_debug)
+            System.out.println("column =" + rs.getString(4));
 
-        if (expectedResult == null) //expecting empty resultset from the query
-            JDBC.assertEmpty(rs);
-        else
-            JDBC.assertFullResultSet(rs,expectedResult);
+        while (rs.next())
+        {
+            if (verbose_debug)
+                System.out.println("column =" + rs.getString(4));
+        }
+
+        // TODO should verify all columns are returned.
 
         rs.close();
-        ps.close();
-        conn.commit();
+
+        dropTable(conn);
+
     }
 
     /**************************************************************************
@@ -806,7 +1116,8 @@
                     expected_order, 
                     0, 
                     i - 1,
-                    true));
+                    true),
+                true);
 
             // '<=' test
             checkOneParamQuery(
@@ -817,7 +1128,8 @@
                     expected_order, 
                     0, 
                     i,
-                    true));
+                    true),
+                true);
 
             // '=' test
             checkOneParamQuery(
@@ -828,7 +1140,8 @@
                     expected_order, 
                     i, 
                     i,
-                    true));
+                    true),
+                true);
 
             // '>=' test
             checkOneParamQuery(
@@ -839,7 +1152,8 @@
                     expected_order, 
                     i, 
                     expected_order.length - 1,
-                    true));
+                    true),
+                true);
 
             // '>' test
             checkOneParamQuery(
@@ -850,7 +1164,8 @@
                     expected_order, 
                     i + 1, 
                     expected_order.length - 1,
-                    true));
+                    true),
+                true);
         }
     }
 
@@ -1074,7 +1389,7 @@
                 true);
         }
 
-        dropTable(conn);
+        dropLikeTable(conn);
     }
 
 
@@ -1292,6 +1607,87 @@
     }
 
 
+    private static final String[] derby2670_pattern =
+    {
+        "%",
+        "a%",
+        "b%",
+        "c%",
+        "%a%",
+        "%b%",
+        "%c%",
+        "%a",
+        "%b",
+        "%c"
+    };
+
+    private static final String[][][] derby2670_pattern_result = 
+    {
+        // pattern = % 
+        { {"a"}, 
+          {"A"}, 
+          {" a"}, 
+          {"-a"}, 
+          {"\u00ADa"}, 
+          {"b"}, 
+          {"B"}, 
+          {" b"}, 
+          {"-b"}, 
+          {"\u00ADb"}, 
+          {"C"},
+          {"ekstra\u00ADarbeid"}, 
+          {"ekstrabetaling"}, 
+          {"ekstraarbeid"}, 
+          {"Wanvik"}, 
+          {"Wågan"},
+          {"Waagan"}, 
+          {"W\u00E5han"}
+        },
+        // pattern = a% 
+        { {"a"} },
+        // pattern = b% 
+        { {"b"} },
+        // pattern = c% 
+        null,
+        // pattern = %a% 
+        { {"a"}, 
+          {" a"}, 
+          {"-a"}, 
+          {"\u00ADa"}, 
+          {"ekstra\u00ADarbeid"}, 
+          {"ekstrabetaling"}, 
+          {"Wanvik"}, 
+          {"Wågan"},
+          {"Waagan"}, 
+          {"W\u00E5han"}
+        },
+        // pattern = %b% 
+        { {"b"}, 
+          {" b"}, 
+          {"-b"}, 
+          {"\u00ADb"}, 
+          {"ekstra\u00ADarbeid"}, 
+          {"ekstrabetaling"}, 
+          {"ekstraarbeid"}
+        }, 
+        // pattern = %c% 
+        null,
+        // pattern = %a
+        { {"a"}, 
+          {" a"}, 
+          {"-a"}, 
+          {"\u00ADa"} 
+        }, 
+        // pattern = %b
+        { {"b"}, 
+          {" b"}, 
+          {"-b"}, 
+          {"\u00ADb"}
+        }, 
+        // pattern = %c
+        null
+    };
+
     /**
      * Test case for DERBY-2670 - problem with like in no like processing.
      * <p>
@@ -1299,6 +1695,10 @@
      * C, obviously wrong for like %a%.  The code was incorrectly caching
      * collation key info in a DataValueDescriptor across the reuse of the
      * holder object from one row to the next.
+     * <p>
+     * Added more patterns to also test DERBY-2710 and DERBY-2706, both
+     * to do with bad like optimization which can not be applied to collation
+     * based like.
      **/
     private void runDerby2670(
     Connection conn)
@@ -1319,24 +1719,110 @@
             ps.setString(1, rows[i]);
             ps.executeUpdate();
         }
+        ps.close();
+
+        Assert.assertTrue(
+            "source and result arrays do not match for derby2670",
+            derby2670_pattern_result.length == derby2670_pattern.length);
+
+        String like_qry = "select * from t where x like ";
+        PreparedStatement ps_like = 
+            conn.prepareStatement("select * from t where x like ?");
+        PreparedStatement ps_like_orderby = 
+            conn.prepareStatement("select * from t where x like ? order by x");
+
+        for (int i = 0; i < derby2670_pattern.length; i++)
+        {
+            // Try just unordered like with constant pattern
+            String qry = like_qry + "'" + derby2670_pattern[i] + "'";
+
+            checkLangBasedQuery(
+                conn, qry, derby2670_pattern_result[i], false);
+
+            // add an order by 
+            qry += " order by x";
+
+            checkLangBasedQuery(
+                conn, qry, derby2670_pattern_result[i], false);
+
+            // try parameter for pattern
+            ps_like.setString(1, derby2670_pattern[i]);
+            ResultSet rs = ps_like.executeQuery();
+
+            if (derby2670_pattern_result[i] == null)
+                JDBC.assertEmpty(rs);
+            else
+                JDBC.assertUnorderedResultSet(rs, derby2670_pattern_result[i]);
+
+            rs.close();
+            rs = null;
+
+            // try parameter for pattern
+            ps_like_orderby.setString(1, derby2670_pattern[i]);
+            rs = ps_like_orderby.executeQuery();
+
+            if (derby2670_pattern_result[i] == null)
+                JDBC.assertEmpty(rs);
+            else
+                JDBC.assertFullResultSet(rs, derby2670_pattern_result[i]);
+
+            rs.close();
+            rs = null;
+        }
+
+        // add an index and try it again.
+        s.executeUpdate("create index t_idx on t (x)");
+        like_qry = "select * from t where x like ";
+
+        for (int i = 0; i < derby2670_pattern.length; i++)
+        {
+            // Try just unordered like with constant pattern
+            String qry = like_qry + "'" + derby2670_pattern[i] + "'";
+
+            checkLangBasedQuery(
+                conn, qry, derby2670_pattern_result[i], false);
+
+            // add an order by 
+            qry += " order by x";
+
+            checkLangBasedQuery(
+                conn, qry, derby2670_pattern_result[i], false);
+
+            // try parameter for pattern
+            ps_like.setString(1, derby2670_pattern[i]);
+            ResultSet rs = ps_like.executeQuery();
+
+            if (derby2670_pattern_result[i] == null)
+                JDBC.assertEmpty(rs);
+            else
+                JDBC.assertUnorderedResultSet(rs, derby2670_pattern_result[i]);
+
+            rs.close();
+            rs = null;
+
+            // try parameter for pattern
+            ps_like_orderby.setString(1, derby2670_pattern[i]);
+            rs = ps_like_orderby.executeQuery();
+
+
+            if (derby2670_pattern_result[i] == null)
+                JDBC.assertEmpty(rs);
+            else
+                JDBC.assertFullResultSet(rs, derby2670_pattern_result[i]);
+
+            rs.close();
+            rs = null;
+        }
+
 
-        checkLangBasedQuery(
-            conn, 
-            "select * from t where x like '%a%'",
-            new String[][] 
-            { 
-                {"Waagan"}, {"W\u00E5han"}, {"Wanvik"}, {"Wågan"}, 
-                {"ekstrabetaling"}, {"ekstra\u00ADarbeid"}, {"\u00ADa"}, {"a"}, 
-                {"-a"}, {" a"}
-            },
-            true);
 
         s.executeUpdate("drop table t");
 
         conn.commit();
         
         // cleanup
-        ps.close();
+        ps_like_orderby.close();
+        ps_like.close();
         s.close();
     }
 
@@ -1405,6 +1891,7 @@
 
         runLikeTests(conn, db_index);
 
+
         /*
         TODO -MIKEM, this test does not work yet.
         runAlterTableDropColumn(conn, db_index);
@@ -1430,6 +1917,7 @@
         runTestIter(conn, TEST_ENGLISH);
         conn.close();
     }
+
     public void testPolishCollation() throws SQLException
     {
         Connection conn = setUpDBandOpenConnection(TEST_POLISH);
@@ -1439,11 +1927,20 @@
     public void testNorwayCollation() throws SQLException
     {
         Connection conn = setUpDBandOpenConnection(TEST_NORWAY);
-        runTestIter(conn, TEST_NORWAY);
-
         runDerby2670(conn);
+        runTestIter(conn, TEST_NORWAY);
         conn.close();
     }
+    /**
+     * Test creating a TERRITORY_BASED collated database by only setting
+     * the collation attribute.  The Territory will be picked up from the
+     * default territory of the JVM.
+     *
+     * Tests:
+     * T24: DERBY-2669 If no territory attribute is specified at create 
+     *      database time, then create collated db based on default 
+     *      territory of Database.
+     **/
     public void testDefaultJVMTerritoryCollation() throws SQLException
     {
         boolean run_test = false;
@@ -1475,10 +1972,8 @@
         }
     }
     
-    
     public static Test suite() 
     {
-
         // only test in embedded mode, all tests are server side actions.
         Test test =  
                TestConfiguration.embeddedSuite(CollationTest2.class);
@@ -1513,7 +2008,6 @@
         // database to use for testing collation, default jvm territory
         test = TestConfiguration.additionalDatabaseDecorator(
                     test, TEST_DATABASE[TEST_DEFAULT_TERRITORY]);
-
 
         return test;
     }



Mime
View raw message