db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From mi...@apache.org
Subject svn commit: r1464594 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/tests/lang/ testing/org/apache/derbyTesting/junit/
Date Thu, 04 Apr 2013 15:27:56 GMT
Author: mikem
Date: Thu Apr  4 15:27:56 2013
New Revision: 1464594

URL: http://svn.apache.org/r1464594
Log:
DERBY-6131 select from view with "upper" and "in" list throws a ClassCastException 

Prior to this fix the following query on a view would throw a ClassCastException
    select name from myView where upper(name) in ('AA', 'BB');
This query got in code that was trying to "push" predicates down, and
that code is only currently build to push constants and column references.
In this case it was mistakenly trying to push down the "upper" call, and
when it tried to cast it as a column reference it got the error.  The fix
was to check the type of the node and not push in this case.


Added:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/Derby6131.java
Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/PredicateList.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/PredicateList.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/PredicateList.java?rev=1464594&r1=1464593&r2=1464594&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/PredicateList.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/PredicateList.java Thu
Apr  4 15:27:56 2013
@@ -1516,13 +1516,31 @@ public class PredicateList extends Query
 				else if (andNode.getLeftOperand() instanceof InListOperatorNode)
 				{
 					inNode = (InListOperatorNode) andNode.getLeftOperand();
-					if (! (inNode.getRightOperandList().isConstantExpression()))
+
+                    if (!(inNode.getLeftOperand() instanceof ColumnReference))
+                    {
+                        // A predicate can be pushed into an underlying select 
+                        // if the source of every ColumnReference in the 
+                        // predicate is itself a ColumnReference.
+                        // In this case the left operand is not a 
+                        // ColumnReference so do not push.
+
+                        continue;
+                    }
+                    else if (!(inNode.getRightOperandList().isConstantExpression()))
+                    {
+                        // only push down constant expressions, 
+                        // skipping this one that is not
+
 						continue;
+                    }
 
 					crNode = (ColumnReference) inNode.getLeftOperand();
 				}
 				else
+                {
 					continue;
+                }
 
 				// Remap this crNode to underlying column reference in the select, if possible.
 				ColumnReference newCRNode = select.findColumnReferenceInResult(crNode.columnName);

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/Derby6131.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/Derby6131.java?rev=1464594&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/Derby6131.java
(added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/Derby6131.java
Thu Apr  4 15:27:56 2013
@@ -0,0 +1,244 @@
+/*
+
+Derby - Class org.apache.derbyTesting.functionTests.tests.lang.NullsTest
+
+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.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.TestConfiguration;
+
+/**
+ * Test case for DERBY-6131: select from view with "upper" and "in" list 
+ * throws a ClassCastException null value functionality.
+ */
+public class Derby6131 extends BaseJDBCTestCase {
+
+    public Derby6131(String name) {
+        super(name);
+    }
+
+    public static Test suite(){
+        return TestConfiguration.defaultSuite(Derby6131.class);
+    }
+
+    public void setUp() throws SQLException{
+        getConnection().setAutoCommit(false);
+    }
+
+    /**
+     * Test the original user report of this issue:
+     * <p>
+     * the issue can be reproduced
+     * 1. create table myTbl1 (name varchar(1000));
+     * 2. create table myTbl2 (name varchar(1000));
+     * 3. create view myView (name) as 
+     *        select t1.name from myTbl1 t1 
+     *        union all select t2.name from myTbl2 t2;
+     * 4. select name from myView where upper(name) in ('AA', 'BB');
+     * #4 failed with 
+     *     "org.apache.derby.impl.sql.compile.SimpleStringOperatorNode 
+     *     incompatible with org.apache.derby.impl.sql.compile.ColumnReference:
+     *     java.lang.ClassCastException"
+     *
+     * If the view is created as 
+     *    "create myView (name) as select t1.name from myTbl1 t1", 
+     *    the query worked fine. 
+     * <p>
+     **/
+    public void testOrigUserRepro()
+        throws SQLException
+    {
+        Statement st = createStatement();
+
+        // 1. create table myTbl1 (name varchar(1000));
+        st.executeUpdate("create table myTbl1 (name varchar(1000))");
+
+        // 2. create table myTbl2 (name varchar(1000));
+        st.executeUpdate("create table myTbl2 (name varchar(1000))");
+
+        // * 3. create view myView (name) as 
+        //          select t1.name from myTbl1 t1 
+        //              union all select t2.name from myTbl2 t2;
+        st.executeUpdate(
+            "create view myView (name) as " + 
+                "select t1.name from myTbl1 t1 " + 
+                    "union all select t2.name from myTbl2 t2");
+
+        // 4. select name from myView where upper(name) in ('AA', 'BB');
+        // #4 failed with 
+        //    "org.apache.derby.impl.sql.compile.SimpleStringOperatorNode 
+        //     incompatible with 
+        //     org.apache.derby.impl.sql.compile.ColumnReference: 
+        //     java.lang.ClassCastException"
+
+        String sql = 
+            "select name from myView where upper(name) in ('AA', 'BB')";
+
+        // no data so just checking if no exception happens.
+        JDBC.assertFullResultSet(st.executeQuery(sql),
+            new String[][]{ });
+
+        dropView("myView");
+        dropTable("myTbl1");
+        dropTable("myTbl2");
+
+        st.close();
+    }
+
+    /**
+     * Test the original DERBY-6131 queries with some data to make sure
+     * results look right in addtion to not getting an exception.
+     * <p>
+     **/
+    public void testOrigUserReproWithData()
+        throws SQLException
+    {
+        Statement st = createStatement();
+
+        // 1. create table myTbl1 (name varchar(1000));
+        st.executeUpdate("create table myTbl1 (name varchar(1000))");
+
+        // 2. create table myTbl2 (name varchar(1000));
+        st.executeUpdate("create table myTbl2 (name varchar(1000))");
+
+        st.executeUpdate(
+            "insert into myTbl1 values ('aA'), ('bB'), ('Cc'), ('Dd')");
+        st.executeUpdate(
+            "insert into myTbl2 values ('eE'), ('fF'), ('GG'), ('hh')");
+
+        // * 3. create view myView (name) as 
+        //          select t1.name from myTbl1 t1 
+        //              union all select t2.name from myTbl2 t2;
+        st.executeUpdate(
+            "create view myView (name) as " + 
+                "select t1.name from myTbl1 t1 " + 
+                    "union all select t2.name from myTbl2 t2");
+
+        // 4. select name from myView where upper(name) in ('AA', 'BB');
+        // before fix #4 failed with 
+        //    "org.apache.derby.impl.sql.compile.SimpleStringOperatorNode 
+        //     incompatible with 
+        //     org.apache.derby.impl.sql.compile.ColumnReference: 
+        //     java.lang.ClassCastException"
+
+        String sql = 
+            "select name from myView where upper(name) in ('AA', 'BB')";
+
+        // should match both values in IN-LIST
+        JDBC.assertFullResultSet(st.executeQuery(sql),
+            new String[][]{ 
+                {"aA"}, 
+                {"bB"}
+            });
+
+        // same test using prepared statement with params rather than constants.
+        String prepared_sql = 
+            "select name from myView where upper(name) in (?, ?)";
+
+        PreparedStatement pstmt = prepareStatement(prepared_sql);
+
+        pstmt.setString(1, "AA");
+        pstmt.setString(2, "BB");
+
+        // should match both values in IN-LIST
+        JDBC.assertFullResultSet(pstmt.executeQuery(),
+            new String[][]{ 
+                {"aA"}, 
+                {"bB"}
+            });
+
+        // look for data across both parts of the union
+        sql = "select name from myView where upper(name) in ('CC', 'HH')";
+
+        // no data so just checking if no exception happens.
+        JDBC.assertFullResultSet(st.executeQuery(sql),
+            new String[][]{ 
+                {"Cc"}, 
+                {"hh"}
+            });
+
+        // same test using prepared statement with params rather than constants.
+        pstmt.setString(1, "CC");
+        pstmt.setString(2, "HH");
+
+        // no data so just checking if no exception happens.
+        JDBC.assertFullResultSet(st.executeQuery(sql),
+            new String[][]{ 
+                {"Cc"}, 
+                {"hh"}
+            });
+
+        // negative test, should not match anything
+        sql = "select name from myView where upper(name) in ('cc', 'hh')";
+
+        // no data so just checking if no exception happens.
+        JDBC.assertFullResultSet(st.executeQuery(sql),
+            new String[][]{ 
+            });
+
+        // same test using prepared statement with params rather than constants.
+        pstmt.setString(1, "cc");
+        pstmt.setString(2, "hh");
+
+        // no data so just checking if no exception happens.
+        JDBC.assertFullResultSet(st.executeQuery(sql),
+            new String[][]{ 
+            });
+
+        // test another function - user lower
+        sql = "select name from myView where lower(name) in ('cc', 'hh')";
+
+        // no data so just checking if no exception happens.
+        JDBC.assertFullResultSet(st.executeQuery(sql),
+            new String[][]{ 
+                {"Cc"}, 
+                {"hh"}
+            });
+
+        // same test using prepared statement with params rather than constants.
+        prepared_sql = 
+            "select name from myView where upper(name) in (?, ?)";
+
+        pstmt = prepareStatement(prepared_sql);
+
+        pstmt.setString(1, "cc");
+        pstmt.setString(2, "hh");
+
+        // no data so just checking if no exception happens.
+        JDBC.assertFullResultSet(st.executeQuery(sql),
+            new String[][]{ 
+                {"Cc"}, 
+                {"hh"}
+            });
+
+        dropView("myView");
+        dropTable("myTbl1");
+        dropTable("myTbl2");
+
+        st.close();
+        pstmt.close();
+    }
+}

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=1464594&r1=1464593&r2=1464594&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
Thu Apr  4 15:27:56 2013
@@ -236,6 +236,7 @@ public class _Suite extends BaseTestCase
         suite.addTest(Derby5652.suite());
         suite.addTest(TruncateTableAndOnlineBackupTest.suite()); 
         suite.addTest(QueryPlanTest.suite());
+        suite.addTest(Derby6131.suite());
         return suite;
 	}
 }

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java?rev=1464594&r1=1464593&r2=1464594&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java Thu
Apr  4 15:27:56 2013
@@ -1053,6 +1053,42 @@ public abstract class BaseJDBCTestCase
     }
 
     /**
+     * Execute a DROP VIEW command using the passed in viewName as-is
+     * and the default connection.
+     * If the DROP VIEW fails because the view does not exist then
+     * the exception is ignored.
+     * @param viewName Table to be dropped.
+     * @throws SQLException
+     */
+    public final void dropView(String viewName) throws SQLException
+    {
+       dropView(getConnection(), viewName);
+    }
+    
+    /**
+     * Execute a DROP VIEW command using the passed in viewName as-is.
+     * If the DROP VIEW fails because the view does not exist then
+     * the exception is ignored.
+     * @param conn Connection to execute the DROP VIEW
+     * @param viewName Table to be dropped.
+     * @throws SQLException
+     */
+    public static void dropView(Connection conn, String viewName) throws SQLException
+    {
+        Statement statement = conn.createStatement();
+        String dropSQL = "DROP VIEW " + viewName;
+        try { 
+            
+            statement.executeUpdate(dropSQL); 
+        } catch (SQLException e) {
+            assertSQLState("42Y55", e);
+        }
+        finally {
+            statement.close();
+        }
+    }
+
+    /**
      * Assert that the query fails (either in compilation,
      * execution, or retrieval of results--doesn't matter)
      * and throws a SQLException with the expected states.



Mime
View raw message