Return-Path: X-Original-To: apmail-db-derby-commits-archive@www.apache.org Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id AD6547FA3 for ; Mon, 21 Nov 2011 21:22:06 +0000 (UTC) Received: (qmail 33741 invoked by uid 500); 21 Nov 2011 21:22:06 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 33721 invoked by uid 500); 21 Nov 2011 21:22:06 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 33714 invoked by uid 99); 21 Nov 2011 21:22:06 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 21 Nov 2011 21:22:06 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=5.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.4] (HELO eris.apache.org) (140.211.11.4) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 21 Nov 2011 21:22:02 +0000 Received: from eris.apache.org (localhost [127.0.0.1]) by eris.apache.org (Postfix) with ESMTP id E95C523888E4; Mon, 21 Nov 2011 21:21:40 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit 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 -0000 To: derby-commits@db.apache.org From: dag@apache.org X-Mailer: svnmailer-1.0.8-patched Message-Id: <20111121212140.E95C523888E4@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org 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". + *

+ * 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);