db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d..@apache.org
Subject svn commit: r1204712 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache/derbyTesting/functionTests/tests/lang/
Date Mon, 21 Nov 2011 21:21:40 GMT
Author: dag
Date: Mon Nov 21 21:21:39 2011
New Revision: 1204712

URL: http://svn.apache.org/viewvc?rev=1204712&view=rev
Log:
DERBY-5501 Subquery is only allowed to return a single column - When using derby with hibernate
(or JPA) queries are created per JPA spec. For tables with multi-column PK, subqueries are
created with two columns in select clause.

Patch derby-5501-3 lift the restriction that the select list in a
[NOT] EXISTS subquery may only contain "*" or a single column. This
implements SQL feature T501 "Enhanced * EXISTS predicate".


Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subquery.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NestedWhereSubqueryTest.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/subquery.sql

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java?rev=1204712&r1=1204711&r2=1204712&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java Mon
Nov 21 21:21:39 2011
@@ -425,7 +425,8 @@ public abstract class ResultSetNode exte
 			resultColumn = (ResultColumn) resultColumns.elementAt(0);
 	
 			/* Nothing to do if query is already select TRUE ... */
-			if (resultColumn.getExpression().isBooleanTrue())
+            if (resultColumn.getExpression().isBooleanTrue() &&
+                    resultColumns.size() == 1)
 			{
 				return this;
 			}

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java?rev=1204712&r1=1204711&r2=1204712&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java Mon
Nov 21 21:21:39 2011
@@ -847,39 +847,45 @@ public class SelectNode extends ResultSe
 	public void verifySelectStarSubquery(FromList outerFromList, int subqueryType) 
 					throws StandardException
 	{
-		if (! ((ResultColumn) resultColumns.elementAt(0) instanceof AllResultColumn) )
-		{
-			return;
-		}
-
-		/* Select * currently only valid for EXISTS/NOT EXISTS.
-		 * NOT EXISTS does not appear prior to preprocessing.
-		 */
-		if (subqueryType != SubqueryNode.EXISTS_SUBQUERY)
-		{
-			throw StandardException.newException(SQLState.LANG_CANT_SELECT_STAR_SUBQUERY);
-		}
-
-		/* If the AllResultColumn is qualified, then we have to verify
-		 * that the qualification is a valid exposed name.
-		 * NOTE: The exposed name can come from an outer query block.
-		 */
-		String		fullTableName;
-			
-		fullTableName = ((AllResultColumn) resultColumns.elementAt(0)).getFullTableName();
-
-		if (fullTableName != null)
-		{
-			if (fromList.getFromTableByName(fullTableName, null, true) == null &&
-				outerFromList.getFromTableByName(fullTableName, null, true) == null)
-			{
-				if (fromList.getFromTableByName(fullTableName, null, false) == null &&
-					outerFromList.getFromTableByName(fullTableName, null, false) == null)
-				{
-					throw StandardException.newException(SQLState.LANG_EXPOSED_NAME_NOT_FOUND, fullTableName);
-				}
-			}
-		}
+        for (int i = 0; i < resultColumns.size(); i++) {
+            if (!((ResultColumn)resultColumns.elementAt(i)
+                     instanceof AllResultColumn) ) {
+                continue;
+            }
+
+            /* Select * currently only valid for EXISTS/NOT EXISTS.  NOT EXISTS
+             * does not appear prior to preprocessing.
+             */
+            if (subqueryType != SubqueryNode.EXISTS_SUBQUERY) {
+                throw StandardException.newException(
+                    SQLState.LANG_CANT_SELECT_STAR_SUBQUERY);
+            }
+
+            /* If the AllResultColumn is qualified, then we have to verify that
+             * the qualification is a valid exposed name.  NOTE: The exposed
+             * name can come from an outer query block.
+             */
+            String fullTableName =
+                ((AllResultColumn)resultColumns.elementAt(i)).
+                getFullTableName();
+
+            if (fullTableName != null) {
+                if (fromList.getFromTableByName
+                        (fullTableName, null, true) == null &&
+                    outerFromList.getFromTableByName
+                        (fullTableName, null, true) == null) {
+
+                    if (fromList.getFromTableByName
+                            (fullTableName, null, false) == null &&
+                        outerFromList.getFromTableByName
+                            (fullTableName, null, false) == null) {
+                        throw StandardException.newException(
+                            SQLState.LANG_EXPOSED_NAME_NOT_FOUND,
+                            fullTableName);
+                    }
+                }
+            }
+        }
 	}
 
 	/** 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java?rev=1204712&r1=1204711&r2=1204712&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java Mon
Nov 21 21:21:39 2011
@@ -464,10 +464,11 @@ public class SubqueryNode extends ValueN
 
 		resultColumns = resultSet.getResultColumns();
 
-		/* The parser does not enforce the fact that a subquery can only return
-		 * a single column, so we must check here.
-		 */
-		if (resultColumns.visibleSize() != 1)
+        /* The parser does not enforce the fact that a subquery (except in the
+         * case of EXISTS; NOT EXISTS does not appear prior to preprocessing)
+         * can only return a single column, so we must check here.
+         */
+        if (subqueryType != EXISTS_SUBQUERY && resultColumns.visibleSize() != 1)
 		{
 			throw StandardException.newException(SQLState.LANG_NON_SINGLE_COLUMN_SUBQUERY);
 		}

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subquery.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subquery.out?rev=1204712&r1=1204711&r2=1204712&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subquery.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subquery.out
Mon Nov 21 21:21:39 2011
@@ -76,9 +76,6 @@ select * from s where exists (select tt.
 ERROR 42X10: 'TT' is not an exposed table name in the scope in which it appears.
 ij> select * from s where exists (select t.* from t tt);
 ERROR 42X10: 'T' is not an exposed table name in the scope in which it appears.
-ij> -- too many columns in select list
-select * from s where exists (select i, s from t);
-ERROR 42X39: Subquery is only allowed to return a single column.
 ij> -- invalid column reference in select list
 select * from s where exists (select nosuchcolumn from t);
 ERROR 42X04: Column 'NOSUCHCOLUMN' is either not in any table in the FROM list or appears
within a join specification and is outside the scope of the join specification or appears
in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement
then 'NOSUCHCOLUMN' is not a column in the target table.
@@ -121,6 +118,12 @@ I          |S     |C                    
 NULL       |NULL  |NULL                          |NULL                          |NULL   
            
 0          |0     |0                             |0                             |0      
            
 1          |1     |1                             |1                             |1      
            
+ij> select * from s where exists (select i, s from t);
+I          |S     |C                             |VC                            |B      
            
+-----------------------------------------------------------------------------------------------------
+NULL       |NULL  |NULL                          |NULL                          |NULL   
            
+0          |0     |0                             |0                             |0      
            
+1          |1     |1                             |1                             |1      
            
 ij> -- subquery returns empty result set
 select * from s where exists (select * from t where i = -1);
 I          |S     |C                             |VC                            |B      
            

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NestedWhereSubqueryTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NestedWhereSubqueryTest.java?rev=1204712&r1=1204711&r2=1204712&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NestedWhereSubqueryTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NestedWhereSubqueryTest.java
Mon Nov 21 21:21:39 2011
@@ -396,7 +396,81 @@ public class NestedWhereSubqueryTest ext
 		s.close();
 	}
 
-	public static Test suite() {
+    /**
+     * Allow multiple columns in EXISTS subquery. SQL feature T501 "Enhanced
+     * EXISTS predicate".
+     * <p/>
+     * Strictly speaking, this test belongs in a general subquery test class,
+     * but pending conversion of subquery.sql to JUnit, testDerby5501 resides
+     * here (FIXME).
+     */
+    public void testDerby5501 () throws SQLException {
+        setAutoCommit(false);
+        Statement s = createStatement();
+
+        s.executeUpdate("create table t5501a(i int, j int, primary key(i,j))");
+        s.executeUpdate("create table t5501b(i int)");
+
+        s.executeUpdate("insert into t5501a values (1,1),(2,2),(3,3),(4,4)");
+        s.executeUpdate("insert into t5501b values 1,3,5");
+
+
+        // works before DERBY-5501
+        ResultSet rs = s.executeQuery(
+            "select i from t5501b t1 where not exists " +
+            "    (select i from t5501a t2 where t1.i=t2.i)");
+        JDBC.assertUnorderedResultSet(rs, new String [][] {{"5"}});
+
+        rs = s.executeQuery(
+            "select i+3.14 from t5501b t1 where not exists " +
+            "    (select i+3.14 from t5501a t2 where t1.i=t2.i)");
+        JDBC.assertUnorderedResultSet(rs, new String [][] {{"8.14"}});
+
+        // works before DERBY-5501: "*" is specially handled already
+        rs = s.executeQuery(
+            "select i from t5501b t1 where not exists " +
+            "    (select * from t5501a t2 where t1.i=t2.i)");
+        JDBC.assertUnorderedResultSet(rs, new String [][] {{"5"}});
+
+
+        // fails before DERBY-5501
+        rs = s.executeQuery(
+            "select i from t5501b t1 where not exists " +
+            "    (select i,j from t5501a t2 where t1.i=t2.i)");
+        JDBC.assertUnorderedResultSet(rs, new String [][] {{"5"}});
+
+        rs = s.executeQuery(
+            "select i from t5501b t1 where not exists " +
+            "    (select true,j from t5501a t2 where t1.i=t2.i)");
+        JDBC.assertUnorderedResultSet(rs, new String [][] {{"5"}});
+
+        s.executeUpdate("delete from t5501a where i=1");
+        rs = s.executeQuery(
+            "select i from t5501b t1 where not exists " +
+            "    (select i,j from t5501a t2 where t1.i=t2.i)");
+        JDBC.assertUnorderedResultSet(rs, new String [][] {{"1"}, {"5"}});
+
+        // should still fail: no column "k" exists
+        assertCompileError(
+            "42X04",
+            "select i from t5501b t1 where not exists " +
+            "    (select i,k from t5501a t2 where t1.i=t2.i)");
+
+        // should still fail: no table "foo" exists
+        assertCompileError(
+            "42X10",
+            "select i from t5501b t1 where not exists " +
+            "    (select t2.*,foo.* from t5501a t2 where t1.i=t2.i)");
+
+        // should still fail: illegal integer format in cast
+        assertCompileError(
+            "22018",
+            "select i from t5501b t1 where not exists " +
+            "   (select t2.*,cast('a' as int) from t5501a t2 where t1.i=t2.i)");
+    }
+
+
+    public static Test suite() {
 		return TestConfiguration.defaultSuite(NestedWhereSubqueryTest.class);
 	}
 }
\ No newline at end of file

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/subquery.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/subquery.sql?rev=1204712&r1=1204711&r2=1204712&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/subquery.sql
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/subquery.sql
Mon Nov 21 21:21:39 2011
@@ -60,8 +60,6 @@ commit;
 -- "mis"qualified all
 select * from s where exists (select tt.* from t);
 select * from s where exists (select t.* from t tt);
--- too many columns in select list
-select * from s where exists (select i, s from t);
 -- invalid column reference in select list
 select * from s where exists (select nosuchcolumn from t);
 -- multiple matches at subquery level
@@ -79,6 +77,7 @@ select * from s u where exists (select u
 -- column reference in select list
 select * from s where exists (select i from t);
 select * from s where exists (select t.i from t);
+select * from s where exists (select i, s from t);
 
 -- subquery returns empty result set
 select * from s where exists (select * from t where i = -1);



Mime
View raw message