db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kahat...@apache.org
Subject svn commit: r539744 - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang: DynamicLikeOptimizationTest.java _Suite.java
Date Sat, 19 May 2007 12:54:47 GMT
Author: kahatlen
Date: Sat May 19 05:54:46 2007
New Revision: 539744

URL: http://svn.apache.org/viewvc?view=rev&rev=539744
Log:
DERBY-2642 (partial) Convert lang/dynamicLikeOptimization.sql to JUnit

First version of DynamicLikeOptimizationTest.

Added:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DynamicLikeOptimizationTest.java
  (with props)
Modified:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DynamicLikeOptimizationTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DynamicLikeOptimizationTest.java?view=auto&rev=539744
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DynamicLikeOptimizationTest.java
(added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DynamicLikeOptimizationTest.java
Sat May 19 05:54:46 2007
@@ -0,0 +1,524 @@
+/*
+ * Class org.apache.derbyTesting.functionTests.tests.lang.DynamicLikeOptimizationTest
+ *
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.derbyTesting.functionTests.tests.lang;
+
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.sql.Types;
+import java.util.HashMap;
+import java.util.Iterator;
+import java.util.Map;
+import junit.framework.Test;
+import junit.framework.TestSuite;
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.TestConfiguration;
+
+/**
+ * Test the dynamic like optimization.
+ *
+ * <p><b>NOTE:</b> the metadata test does a bunch of likes with parameters.
+ */
+public class DynamicLikeOptimizationTest extends BaseJDBCTestCase {
+    /** All rows in the cei table. */
+    private static final Object[][] CEI_ROWS = {
+        { new Integer(0), "Alarms", "AlarmDisk999" },
+        { new Integer(1), "Alarms", "AlarmFS-usr" },
+        { new Integer(2), "Alarms", "AlarmPower" },
+        { new Integer(3), "Alert", "AlertBattery" },
+        { new Integer(4), "Alert", "AlertUPS" },
+        { new Integer(5), "Warning", "WarnIntrusion" },
+        { new Integer(6), "Warning", "WarnUnlockDoor" },
+        { new Integer(7), "Warning", "Warn%Unlock%Door" },
+        { new Integer(8), "Warning", "W_Unlock_Door" },
+    };
+
+    public DynamicLikeOptimizationTest(String name) {
+        super(name);
+    }
+
+    public static Test suite() {
+        TestSuite tests = new TestSuite("DynamicLikeOptimizationTest");
+        tests.addTestSuite(DynamicLikeOptimizationTest.class);
+        tests.addTest(TestConfiguration.clientServerDecorator(
+                          new TestSuite(DynamicLikeOptimizationTest.class)));
+        return new CleanDatabaseTestSetup(tests) {
+            protected void decorateSQL(Statement stmt) throws SQLException {
+                stmt.executeUpdate("create table t1(c11 int)");
+                stmt.executeUpdate("insert into t1 values 1");
+
+                stmt.executeUpdate("create table test(id char(10), " +
+                                   "c10 char(10), vc10 varchar(10))");
+                PreparedStatement insert = getConnection().prepareStatement(
+                    "insert into test values (?,?,?)");
+                String[] values = {
+                    "asdf", "asdg", "aasdf", "%foobar", "foo%bar", "foo_bar"
+                };
+                for (int i = 0; i < values.length; i++) {
+                    for (int j = 1; j <= 3; j++) {
+                        insert.setString(j, values[i]);
+                    }
+                    insert.executeUpdate();
+                }
+                insert.setString(1, "V-NULL");
+                insert.setString(2, null);
+                insert.setString(3, null);
+                insert.executeUpdate();
+                insert.setString(1, "MAX_CHAR");
+                insert.setString(2, "\uFA2D");
+                insert.setString(3, "\uFA2D");
+                insert.executeUpdate();
+                insert.close();
+
+                stmt.executeUpdate(
+                    "create table likeable(match_me varchar(10), " +
+                    "pattern varchar(10), esc varchar(1))");
+                stmt.executeUpdate(
+                    "insert into likeable values " +
+                    "('foo%bar', 'fooZ%bar', 'Z'), " +
+                    "('foo%bar', '%Z%ba_', 'Z')," +
+                    "('foo%bar', 'fooZ%baZ', 'Z')");
+
+                stmt.executeUpdate(
+                    "create table cei(id int, name varchar(192) not null, " +
+                    "source varchar(252) not null)");
+
+                PreparedStatement cei = getConnection().prepareStatement(
+                    "insert into cei values (?,?,?)");
+                for (int i = 0; i < CEI_ROWS.length; i++) {
+                    for (int j = 0; j < CEI_ROWS[i].length; j++) {
+                        cei.setObject(j+1, CEI_ROWS[i][j]);
+                    }
+                    cei.executeUpdate();
+                }
+                cei.close();
+            }
+        };
+    }
+
+    protected void setUp() throws SQLException {
+        getConnection().setAutoCommit(false);
+    }
+
+    public void testSimpleLikePredicates() throws SQLException {
+        PreparedStatement ps =
+            prepareStatement("select 1 from t1 where 'asdf' like ?");
+
+        // queries that expect one row
+        String[] one = { "%", "%f", "asd%", "_%", "%_", "%asdf" };
+        for (int i = 0; i < one.length; i++) {
+            ps.setString(1, one[i]);
+            JDBC.assertSingleValueResultSet(ps.executeQuery(), "1");
+        }
+
+        // queries that expect empty result set
+        String[] empty = { "", "%g", "_asdf", null };
+        for (int i = 0; i < empty.length; i++) {
+            ps.setObject(1, empty[i], Types.VARCHAR);
+            JDBC.assertEmpty(ps.executeQuery());
+        }
+
+        ps.close();
+    }
+
+    public void testEscapeSyntax() throws SQLException {
+        PreparedStatement ps =
+            prepareStatement("select 1 from t1 where '%foobar' " +
+                             "like 'Z%foobar' escape ?");
+
+        // match: optimize to LIKE and ==
+        ps.setString(1, "Z");
+        JDBC.assertSingleValueResultSet(ps.executeQuery(), "1");
+
+        // invalid escape strings
+        String[][] invalid = {
+            { "raZ", "22019" },
+            { "", "22019" },
+            { null, "22501" },
+        };
+        for (int i = 0; i < invalid.length; i++) {
+            ps.setObject(1, invalid[i][0], Types.VARCHAR);
+            try {
+                ps.executeQuery();
+                fail();
+            } catch (SQLException e) {
+                assertSQLState(invalid[i][1], e);
+            }
+        }
+
+        // no match, wrong char
+        ps.setString(1, "%");
+        JDBC.assertEmpty(ps.executeQuery());
+
+        ps.close();
+    }
+
+    public void testWildcardAsEscape() throws SQLException {
+        Statement s = createStatement();
+        JDBC.assertSingleValueResultSet(
+            s.executeQuery(
+                "select 1 from t1 where '%foobar' like '%%foobar' escape '%'"),
+            "1");
+        JDBC.assertSingleValueResultSet(
+            s.executeQuery(
+                "select 1 from t1 where '_foobar' like '__foobar' escape '_'"),
+            "1");
+        s.close();
+    }
+
+    public void testEscapeSyntax2() throws SQLException {
+        PreparedStatement ps = prepareStatement(
+            "select 1 from t1 where '%foobar' like ? escape ?");
+
+        ps.setString(1, "Z%foobar");
+        ps.setString(2, "Z");
+        JDBC.assertSingleValueResultSet(ps.executeQuery(), "1");
+
+        ps.setString(2, "");
+        try {
+            ps.executeQuery();
+            fail();
+        } catch (SQLException e) {
+            assertSQLState("22019", e);
+        }
+
+        ps.close();
+    }
+
+    public void testEscapeSyntax3() throws SQLException {
+        PreparedStatement ps = prepareStatement(
+            "select 1 from t1 where '%foobar' like ? escape 'Z'");
+
+        ps.setString(1, "x%foobar");
+        JDBC.assertEmpty(ps.executeQuery());
+
+        ps.setString(1, "Z%foobar");
+        JDBC.assertSingleValueResultSet(ps.executeQuery(), "1");
+
+        ps.close();
+    }
+
+    public void testEscapeSyntax4() throws SQLException {
+        PreparedStatement ps = prepareStatement(
+            "select 1 from t1 where '%foobar' like ? escape '$'");
+
+        ps.setString(1, "$%f%bar");
+        JDBC.assertSingleValueResultSet(ps.executeQuery(), "1");
+
+        ps.close();
+    }
+
+    public void testEscapeSyntax5() throws SQLException {
+        PreparedStatement ps = prepareStatement(
+            "select 1 from t1 where 'Z%foobar' like ? escape 'Z'");
+
+        ps.setString(1, "ZZZ%foo%a_");
+        JDBC.assertSingleValueResultSet(ps.executeQuery(), "1");
+
+        ps.close();
+    }
+
+    public void testLikeWithHighestValidCharacter() throws SQLException {
+        // \uFA2D - the highest valid character according to
+        // Character.isDefined() of JDK 1.4;
+        PreparedStatement ps =
+            prepareStatement("select 1 from t1 where '\uFA2D' like ?");
+
+        String[] match = { "%", "_", "\uFA2D" };
+        for (int i = 0; i < match.length; i++) {
+            ps.setString(1, match[i]);
+            JDBC.assertSingleValueResultSet(ps.executeQuery(), "1");
+        }
+
+        ps.setString(1, "");
+        JDBC.assertEmpty(ps.executeQuery());
+
+        ps.close();
+    }
+
+    public void testGeneratedPredicatesCHAR() throws SQLException {
+        PreparedStatement ps =
+            prepareStatement("select id from test where c10 like ?");
+        String[][][] expected = {
+            /* null */ { },
+            /* 1 */ { },
+            /* "" */ { },
+            /* % */  { {"MAX_CHAR"}, {"asdf"}, {"asdg"}, {"aasdf"},
+                       {"%foobar"}, {"foo%bar"}, {"foo_bar"} },
+            /* %f */ { },
+            /* %g */ { },
+            /* asd% */ { {"asdf"},{"asdg"} },
+            /* _% */ { {"MAX_CHAR"}, {"asdf"}, {"asdg"}, {"aasdf"},
+                       {"%foobar"}, {"foo%bar"}, {"foo_bar"} },
+            /* %_ */ { {"MAX_CHAR"}, {"asdf"}, {"asdg"}, {"aasdf"},
+                       {"%foobar"}, {"foo%bar"}, {"foo_bar"} },
+            /* _asdf */ { },
+            /* _asdf % */ { {"aasdf"} },
+            /* %asdf */ { },
+        };
+        testGeneratedPredicates(ps, expected);
+    }
+
+    public void testGeneratedPredicatesVARCHAR() throws SQLException {
+        PreparedStatement ps =
+            prepareStatement("select id from test where vc10 like ?");
+        String[][][] expected = {
+            /* null */ { },
+            /* 1 */ { },
+            /* "" */ { },
+            /* % */  { {"MAX_CHAR"}, {"asdf"}, {"asdg"}, {"aasdf"},
+                       {"%foobar"}, {"foo%bar"}, {"foo_bar"} },
+            /* %f */ { {"asdf"}, {"aasdf"} },
+            /* %g */ { {"asdg"} },
+            /* asd% */ { {"asdf"},{"asdg"} },
+            /* _% */ { {"MAX_CHAR"}, {"asdf"}, {"asdg"}, {"aasdf"},
+                       {"%foobar"}, {"foo%bar"}, {"foo_bar"} },
+            /* %_ */ { {"MAX_CHAR"}, {"asdf"}, {"asdg"}, {"aasdf"},
+                       {"%foobar"}, {"foo%bar"}, {"foo_bar"} },
+            /* _asdf */ { {"aasdf"} },
+            /* _asdf % */ { },
+            /* %asdf */ { {"asdf"}, {"aasdf"} },
+        };
+        testGeneratedPredicates(ps, expected);
+    }
+
+    /**
+     * Helper method for <code>testGeneratedPredicates*</code>. Executes a
+     * prepared statement with different parameter values and compares result
+     * to an array of expected rows.
+     *
+     * @param ps the prepared statement to execute
+     * @param rows array of expected rows to be returned for the different
+     * executions
+     */
+    private void testGeneratedPredicates(PreparedStatement ps,
+                                         String[][][] rows)
+            throws SQLException {
+        Object[] args = {
+            null, new Integer(1), "", "%", "%f", "%g", "asd%", "_%", "%_",
+            "_asdf", "_asdf %", "%asdf"
+        };
+        assertEquals(args.length, rows.length);
+
+        for (int i = 0; i < args.length; i++) {
+            if (args[i] == null) {
+                ps.setString(1, null);
+            } else {
+                ps.setObject(1, args[i]);
+            }
+            JDBC.assertUnorderedResultSet(ps.executeQuery(), rows[i]);
+        }
+        ps.close();
+    }
+
+    public void testStringAndPatternAndEscapeFromTable() throws SQLException {
+        PreparedStatement ps =
+            prepareStatement("select match_me from likeable " +
+                             "where match_me like pattern escape esc");
+
+        // In embedded, the first two should go fine, third one should fail
+        // because the escape character is not followed by _ or %. In
+        // client/server mode, executeQuery() should fail because of
+        // pre-fetching. (This test only works correctly if the rows are
+        // returned in the insert order, which happens to be the case but is
+        // not guaranteed.)
+        ResultSet rs = null;
+        boolean twoSuccessful = false;
+        try {
+            rs = ps.executeQuery();
+            assertTrue(rs.next());
+            assertTrue(rs.next());
+            twoSuccessful = true;
+            rs.next();
+            fail();
+        } catch (SQLException e) {
+            assertSQLState("22025", e);
+            assertTrue((usingEmbedded() && twoSuccessful) ||
+                       (usingDerbyNetClient() && (rs == null)));
+        }
+        if (rs != null) {
+            rs.close();
+        }
+
+        PreparedStatement del = prepareStatement("delete from likeable");
+        assertEquals(3, del.executeUpdate());
+
+        PreparedStatement ins = prepareStatement("insert into likeable " +
+                                                 "values (?, ?, ?)");
+        ins.setString(1, "foo%bar");
+        ins.setString(2, "foo%bar");
+        ins.setString(3, null);
+        ins.executeUpdate();
+
+        try {
+            JDBC.assertDrainResults(ps.executeQuery());
+            fail();
+        } catch (SQLException e) {
+            assertSQLState("22501", e);
+        }
+
+        assertEquals(1, del.executeUpdate());
+
+        ins.setString(3, "");
+        ins.executeUpdate();
+
+        try {
+            JDBC.assertDrainResults(ps.executeQuery());
+            fail();
+        } catch (SQLException e) {
+            assertSQLState("22019", e);
+        }
+
+        ps.close();
+        del.close();
+        ins.close();
+    }
+
+    /**
+     * Test defect 6002/6039.
+     */
+    public void testEscapeWithBackslash() throws SQLException {
+        PreparedStatement ps = prepareStatement(
+            "select id, name, source from cei where " +
+            "(name LIKE ? escape '\\') and (source like ? escape '\\') " +
+            "order by source asc, name asc");
+
+        HashMap inputOutput = new HashMap();
+        inputOutput.put(
+            new String[] {"%", "%"},
+            new Object[][] {
+                CEI_ROWS[0], CEI_ROWS[1], CEI_ROWS[2], CEI_ROWS[3], CEI_ROWS[4],
+                CEI_ROWS[8], CEI_ROWS[7], CEI_ROWS[5], CEI_ROWS[6]
+            });
+        inputOutput.put(
+            new String[] {"Alarms", "AlarmDisk%"},
+            new Object[][] { CEI_ROWS[0] });
+        inputOutput.put(
+            new String[] {"A%", "%"},
+            new Object[][] {
+                CEI_ROWS[0], CEI_ROWS[1], CEI_ROWS[2], CEI_ROWS[3], CEI_ROWS[4],
+            });
+        inputOutput.put(
+            new String[] {"%", "___rm%"},
+            new Object[][] { CEI_ROWS[0], CEI_ROWS[1], CEI_ROWS[2] });
+        inputOutput.put(
+            new String[] {"Warning", "%oor"},
+            new Object[][] { CEI_ROWS[8], CEI_ROWS[7], CEI_ROWS[6] });
+        inputOutput.put(
+            new String[] {"Warning", "Warn\\%Unlock\\%Door"},
+            new Object[][] { CEI_ROWS[7] });
+        inputOutput.put(
+            new String[] {"Warning", "%\\%Unlo%"},
+            new Object[][] { CEI_ROWS[7] });
+        inputOutput.put(
+            new String[] {"Warning", "W\\_Unloc%"},
+            new Object[][] { CEI_ROWS[8] });
+        inputOutput.put(
+            new String[] {"Warning", "_\\_Unlock\\_Door"},
+            new Object[][] { CEI_ROWS[8] });
+        inputOutput.put(
+            new String[] {"W%", "Warn\\%Unlock\\%Door"},
+            new Object[][] { CEI_ROWS[7] });
+        inputOutput.put(
+            new String[] {"%ing", "W\\_Unlock\\_%Door"},
+            new Object[][] { CEI_ROWS[8] });
+        inputOutput.put(new String[] {"Bogus", "Name"}, new Object[][] {});
+
+        for (Iterator it = inputOutput.entrySet().iterator(); it.hasNext(); ) {
+            Map.Entry entry = (Map.Entry) it.next();
+            String[] args = (String[]) entry.getKey();
+            Object[][] rows = (Object[][]) entry.getValue();
+            ps.setObject(1, args[0]);
+            ps.setObject(2, args[1]);
+            JDBC.assertFullResultSet(ps.executeQuery(), rows, false);
+        }
+
+        ps.close();
+    }
+
+    /**
+     * Test that % matches tab characters (DERBY-1262).
+     */
+    public void testTabs() throws SQLException {
+        Statement s = createStatement();
+        s.executeUpdate("insert into test values " +
+                        "('asd\tp', 'asd\tp', 'asd\tp'), " +
+                        "('ase\tp', 'ase\tg', 'ase\tg')");
+
+        String[][] expected = { {"asdf"}, {"asdg"}, {"asd\tp"} };
+        JDBC.assertUnorderedResultSet(
+            s.executeQuery("select c10 from test where c10 like 'asd%'"),
+            expected);
+
+        PreparedStatement ps =
+            prepareStatement("select c10 from test where c10 like ?");
+        ps.setString(1, "asd%");
+        JDBC.assertUnorderedResultSet(ps.executeQuery(), expected);
+
+        s.close();
+        ps.close();
+    }
+
+    /**
+     * Test that it is possible to escape an escape character that is before
+     * the first wildcard (% or _) in the pattern (DERBY-1386).
+     */
+    public void testEscapedEscapeCharacterPrecedingFirstWildcard()
+            throws SQLException {
+        Statement s = createStatement();
+        s.executeUpdate("insert into test values " +
+                        "('abc#def', 'abc#def', 'abc#def'), " +
+                        "('abc\\def', 'abc\\def', 'abc\\def')");
+
+        PreparedStatement[] ps = {
+            prepareStatement("select id from test where c10 like ? escape ?"),
+            prepareStatement("select id from test where vc10 like ? escape ?")
+        };
+
+        String[][] inputOutput = {
+            { "abc##%", "#", "abc#def", "abc#def" },
+            { "abc\\\\%", "\\", "abc\\def", "abc\\def" },
+            { "abc##_ef", "#", null, "abc#def" },
+            { "abc\\\\_ef", "\\", null, "abc\\def" },
+        };
+
+        for (int i = 0; i < inputOutput.length; i++) {
+            for (int j = 0; j < ps.length; j++) {
+                ps[j].setString(1, inputOutput[i][0]);
+                ps[j].setString(2, inputOutput[i][1]);
+                ResultSet rs = ps[j].executeQuery();
+                String expected = inputOutput[i][2+j];
+                if (expected == null) {
+                    JDBC.assertEmpty(rs);
+                } else {
+                    JDBC.assertSingleValueResultSet(rs, expected);
+                }
+            }
+        }
+
+        s.close();
+        ps[0].close();
+        ps[1].close();
+    }
+}

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DynamicLikeOptimizationTest.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=539744&r1=539743&r2=539744
==============================================================================
--- 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
Sat May 19 05:54:46 2007
@@ -62,6 +62,7 @@
 
         suite.addTest(CreateTableFromQueryTest.suite());
         suite.addTest(DatabaseClassLoadingTest.suite());
+        suite.addTest(DynamicLikeOptimizationTest.suite());
         suite.addTest(ExistsWithSetOpsTest.suite());
         suite.addTest(GrantRevokeTest.suite());
         suite.addTest(GroupByExpressionTest.suite());



Mime
View raw message