svn commit: r993074 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/tests/lang/

[ka...@apache.org]

Author: kahatlen
Date: Mon Sep  6 15:41:02 2010
New Revision: 993074

URL: http://svn.apache.org/viewvc?rev=993074&view=rev
Log:
DERBY-4791 (partial) LIKE operator optimizations and concatenation

Made ConcatenationOperatorNode capable of constant folding so that for
example 'ab' || '%' can be handled the same way as 'ab%'.

Added:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LikeTest.java   (with props)
Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConcatenationOperatorNode.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConcatenationOperatorNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConcatenationOperatorNode.java?rev=993074&r1=993073&r2=993074&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConcatenationOperatorNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConcatenationOperatorNode.java Mon Sep  6 15:41:02 2010
@@ -65,6 +65,34 @@ public class ConcatenationOperatorNode e
 				ClassName.ConcatableDataValue, ClassName.ConcatableDataValue);
 	}
 
+    /**
+     * Check if this node always evaluates to the same value. If so, return
+     * a constant node representing the known result.
+     *
+     * @return a constant node representing the result of this concatenation
+     * operation, or {@code this} if the result is not known up front
+     */
+    ValueNode evaluateConstantExpressions() throws StandardException {
+        if (leftOperand instanceof CharConstantNode &&
+                rightOperand instanceof CharConstantNode) {
+            CharConstantNode leftOp = (CharConstantNode) leftOperand;
+            CharConstantNode rightOp = (CharConstantNode) rightOperand;
+            StringDataValue leftValue = (StringDataValue) leftOp.getValue();
+            StringDataValue rightValue = (StringDataValue) rightOp.getValue();
+
+            StringDataValue resultValue =
+                    (StringDataValue) getTypeServices().getNull();
+            resultValue.concatenate(leftValue, rightValue, resultValue);
+
+            return (ValueNode) getNodeFactory().getNode(
+                    C_NodeTypes.CHAR_CONSTANT_NODE,
+                    resultValue.getString(),
+                    getContextManager());
+        }
+
+        return this;
+    }
+
 	/**
 	 * overrides BindOperatorNode.bindExpression because concatenation has
 	 * special requirements for parameter binding.
@@ -263,7 +291,9 @@ public class ConcatenationOperatorNode e
 		 */
 		this.setLeftRightInterfaceType(tc.interfaceName());
 
-		return this;
+        // Finally, fold constants so that for example LIKE optimization is
+        // able to take advantage of concatenated literals like 'ab' || '%'.
+        return this.evaluateConstantExpressions();
 	}
 
 	/**

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LikeTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LikeTest.java?rev=993074&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LikeTest.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LikeTest.java Mon Sep  6 15:41:02 2010
@@ -0,0 +1,73 @@
+/*
+ * Class org.apache.derbyTesting.functionTests.tests.lang.LikeTest
+ *
+ * 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.SQLException;
+import java.sql.Statement;
+import junit.framework.Test;
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.RuntimeStatisticsParser;
+import org.apache.derbyTesting.junit.SQLUtilities;
+import org.apache.derbyTesting.junit.TestConfiguration;
+
+/**
+ * Tests for statements with a LIKE clause.
+ */
+public class LikeTest extends BaseJDBCTestCase {
+    public LikeTest(String name) {
+        super(name);
+    }
+
+    public static Test suite() {
+        return TestConfiguration.defaultSuite(LikeTest.class);
+    }
+
+    /**
+     * Test that LIKE expressions are optimized and use indexes to limit the
+     * scan if the arguments are concatenated string literals. DERBY-4791.
+     */
+    public void testOptimizeConcatenatedStringLiterals() throws SQLException {
+        setAutoCommit(false);
+        Statement s = createStatement();
+        s.execute("create table t (x varchar(128) primary key, y int)");
+        s.execute("insert into t(x) values " +
+                  "'abc', 'def', 'ghi', 'ab', 'de', 'gh'");
+        s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
+
+        // Check that an optimizable LIKE predicate (one that doesn't begin
+        // with a wildcard) with a string literal picks an index scan.
+        String[][] expectedRows = { {"ab", null}, {"abc", null} };
+        JDBC.assertUnorderedResultSet(
+                s.executeQuery("select * from t where x like 'ab%'"),
+                expectedRows);
+        assertTrue(SQLUtilities.getRuntimeStatisticsParser(s).usedIndexScan());
+
+        // Now do the same test, but concatenate two string literals instead
+        // of using a single string literal. This should be optimized the
+        // same way.
+        JDBC.assertUnorderedResultSet(
+                s.executeQuery("select * from t where x like 'a'||'b'||'%'"),
+                expectedRows);
+        assertTrue(SQLUtilities.getRuntimeStatisticsParser(s).usedIndexScan());
+    }
+}

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LikeTest.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?rev=993074&r1=993073&r2=993074&view=diff
==============================================================================
--- 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 Sep  6 15:41:02 2010
@@ -77,6 +77,7 @@ public class _Suite extends BaseTestCase
         suite.addTest(InsertTest.suite());
         suite.addTest(JoinTest.suite());
 	  suite.addTest(LangScripts.suite());
+        suite.addTest(LikeTest.suite());
         suite.addTest(LojReorderTest.suite());
         suite.addTest(MathTrigFunctionsTest.suite());
 	  suite.addTest(OuterJoinTest.suite());