db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kahat...@apache.org
Subject svn commit: r882106 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/loc/ shared/org/apache/derby/shared/common/reference/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache/derbyTe...
Date Thu, 19 Nov 2009 11:03:11 GMT
Author: kahatlen
Date: Thu Nov 19 11:03:01 2009
New Revision: 882106

URL: http://svn.apache.org/viewvc?rev=882106&view=rev
Log:
DERBY-4380: Subqueries not allowed in ON clause

Removed the check that disallowed subqueries in ON clauses. Also
removed the double binding of the ON clause in JoinNode since it made
the query tree inconsistent for some kinds of subqueries.

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml
    db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml158.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml160.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/innerjoin.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/lojreorder.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/outerjoin.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/innerjoin.sql
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/lojreorder.sql
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/outerjoin.sql

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java?rev=882106&r1=882105&r2=882106&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java Thu Nov
19 11:03:01 2009
@@ -799,40 +799,13 @@
 			fromList.addElement((FromTable) leftResultSet);
 			fromList.addElement((FromTable) rightResultSet);
 
-			/* First bind with all tables in the from clause, to detect ambiguous
-			 * references. Push the left and right children to the front of the
-			 * fromListParam before binding the join clause.  (We will
-			 * remove it before returning.)  Valid column references in
-			 * the join clause are limited to columns from the 2 tables being
-			 * joined
-			 */
-			fromListParam.insertElementAt(rightResultSet, 0);
-			fromListParam.insertElementAt(leftResultSet, 0);
 			joinClause = joinClause.bindExpression(
-									  fromListParam, subqueryList,
-									  aggregateVector);
-
-			/* Now bind with two tables being joined. If this raises column not found exception,
-			 * then we have a reference to other tables in the from clause. Raise invalid
-			 * ON clause error to match DB2.
-			 */
-			try {
-				joinClause = joinClause.bindExpression(
 									  fromList, subqueryList,
 									  aggregateVector);
-			} catch (StandardException se) {
-				if (se.getSQLState().equals(SQLState.LANG_COLUMN_NOT_FOUND))
-					throw StandardException.newException(SQLState.LANG_DB2_ON_CLAUSE_INVALID); 
-				throw se;
-			}
 
 			// SQL 2003, section 7.7 SR 5
 			SelectNode.checkNoWindowFunctions(joinClause, "ON");
 
-
-			/* DB2 doesn't allow subquerries in the ON clause */
-			if (subqueryList.size() > 0)
-				throw StandardException.newException(SQLState.LANG_DB2_ON_CLAUSE_INVALID); 
 			/*
 			** We cannot have aggregates in the ON clause.
 			** In the future, if we relax this, we'll need
@@ -843,9 +816,6 @@
 			{
 				throw StandardException.newException(SQLState.LANG_NO_AGGREGATES_IN_ON_CLAUSE);
 			}
-
-			fromListParam.removeElementAt(0);
-			fromListParam.removeElementAt(0);
 		}
 		/* USING clause */
 		else if (usingClause != null)

Modified: db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml?rev=882106&r1=882105&r2=882106&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml Thu Nov 19 11:03:01
2009
@@ -1422,11 +1422,6 @@
             </msg>
 
             <msg>
-                <name>42972</name>
-                <text>An ON clause associated with a JOIN operator is not valid.</text>
-            </msg>
-
-            <msg>
                 <name>42995</name>
                 <text>The requested function does not apply to global temporary tables.</text>
             </msg>

Modified: db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java?rev=882106&r1=882105&r2=882106&view=diff
==============================================================================
--- db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
(original)
+++ db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
Thu Nov 19 11:03:01 2009
@@ -797,7 +797,6 @@
 	String LANG_TABLE_REQUIRES_COLUMN_NAMES                            = "42909";
 	String LANG_DELETE_RULE_VIOLATION		   					       = "42915";
 	String LANG_SYNONYM_CIRCULAR   		   					           = "42916";
-	String LANG_DB2_ON_CLAUSE_INVALID		   					       = "42972";
 	String LANG_SYNTAX_ERROR                                           = "42X01";
 	String LANG_LEXICAL_ERROR                                          = "42X02";
 	String LANG_AMBIGUOUS_COLUMN_NAME                                  = "42X03";

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml158.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml158.out?rev=882106&r1=882105&r2=882106&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml158.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml158.out
Thu Nov 19 11:03:01 2009
@@ -25,7 +25,7 @@
       ON PROJ.CITY <> STAFF.CITY
       AND EMPNUM = WORKS.EMPNUM
       AND PNUM = WORKS.PNUM);
-ERROR 42972: An ON clause associated with a JOIN operator is not valid.
+ERROR 42X04: Column 'WORKS.EMPNUM' 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 'WORKS.EMPNUM' is not a column in the target table.
 ij> -- PASS:0857 If ERROR OR SQLSTATE = 42X04
 
    SELECT EMPNUM, PNUM FROM WORKS

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml160.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml160.out?rev=882106&r1=882105&r2=882106&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml160.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml160.out
Thu Nov 19 11:03:01 2009
@@ -24,7 +24,7 @@
      AND BUDGET > AVG (OSTAFF.GRADE) * 1000
      WHERE HU.WORKS.EMPNUM = OSTAFF.EMPNUM) FROM HU.STAFF AS OSTAFF
      ORDER BY 2, 1;
-ERROR 42972: An ON clause associated with a JOIN operator is not valid.
+ERROR 42X04: Column 'OSTAFF.GRADE' 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 'OSTAFF.GRADE' is not a column in the target table.
 ij> -- PASS:0859 If 5 rows are returned in the following order?
 --               empnum   count
 --               ======   =====

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/innerjoin.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/innerjoin.out?rev=882106&r1=882105&r2=882106&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/innerjoin.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/innerjoin.out
Thu Nov 19 11:03:01 2009
@@ -71,14 +71,14 @@
 ij> -- join clause only allowed to contain column references from tables being
 -- joined. DB2 doesn't allow references to correlated columns
 select * from t1, t2 join t3 on t1.c1 = t2.c1;
-ERROR 42972: An ON clause associated with a JOIN operator is not valid.
+ERROR 42X04: Column 'T1.C1' 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
'T1.C1' is not a column in the target table.
 ij> -- should match db2's behavior by raising an error
 select * from t2 b inner join t3 c on a.c1 = b.c1 and b.c1 = c.c1;
 ERROR 42X04: Column 'A.C1' 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
'A.C1' is not a column in the target table.
 ij> select * from t3 b where exists (select * from t1 a inner join t2 on b.c1 = t2.c1);
-ERROR 42972: An ON clause associated with a JOIN operator is not valid.
+ERROR 42X04: Column 'B.C1' 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
'B.C1' is not a column in the target table.
 ij> select * from t3 where exists (select * from t1 inner join t2 on t3.c1 = t2.c1);
-ERROR 42972: An ON clause associated with a JOIN operator is not valid.
+ERROR 42X04: Column 'T3.C1' 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
'T3.C1' is not a column in the target table.
 ij> -- positive tests
 
 select a.c1 from t1 a join t2 b on a.c1 = b.c1;
@@ -123,17 +123,23 @@
 C1         |C1         
 -----------------------
 1          |1          
-ij> -- subquery in join clause, not allowed in DB2 compatibility mode
+ij> -- subquery in join clause
 select * from t1 a join t2 b 
 on a.c1 = b.c1 and a.c1 = (select c1 from t1 where a.c1 = t1.c1);
-ERROR 42972: An ON clause associated with a JOIN operator is not valid.
+C1         |C1         
+-----------------------
+1          |1          
+3          |3          
 ij> select * from t1 a join t2 b 
 on a.c1 = b.c1 and a.c1 in (select c1 from t1 where a.c1 = t1.c1);
-ERROR 42972: An ON clause associated with a JOIN operator is not valid.
+C1         |C1         
+-----------------------
+1          |1          
+3          |3          
 ij> -- correlated columns
 select * from t1 a
 where exists (select * from t1 inner join t2 on a.c1 = t2.c1);
-ERROR 42972: An ON clause associated with a JOIN operator is not valid.
+ERROR 42X04: Column 'A.C1' 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
'A.C1' is not a column in the target table.
 ij> -- nested joins
 select * from t1 join t2 on t1.c1 = t2.c1 inner join t3 on t1.c1 = t3.c1;
 C1         |C1         |C1         

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/lojreorder.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/lojreorder.out?rev=882106&r1=882105&r2=882106&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/lojreorder.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/lojreorder.out
Thu Nov 19 11:03:01 2009
@@ -192,9 +192,22 @@
 -- In DB2 UDB, any column referenced in an expression of the 
 -- join-condition must be a column of one of the operand tables of 
 -- the associated join (in the scope of the same joined-table clause).
--- this query should fail
+-- DERBY-4380: This query used to fail because column B was seen as ambiguous
+-- (could belong to both T1 and T2). However, the scope of the ON clause
+-- makes it unabiguous; the first occurrence of B must be T1.B, and the second
+-- one must be T2.B.
 select t1.*, s2.* from t t1 left outer join s on (b = e), t t2 left outer join s s2 on (b
= e);
-ERROR 42X03: Column name 'B' is in more than one table in the FROM list.
+A          |B           |C    |D          |E           |F    
+-------------------------------------------------------------
+1          |1.000       |1    |NULL       |NULL        |NULL 
+1          |1.000       |1    |2          |2.000       |2    
+1          |1.000       |1    |3          |3.000       |3    
+2          |2.000       |2    |NULL       |NULL        |NULL 
+2          |2.000       |2    |2          |2.000       |2    
+2          |2.000       |2    |3          |3.000       |3    
+3          |3.000       |3    |NULL       |NULL        |NULL 
+3          |3.000       |3    |2          |2.000       |2    
+3          |3.000       |3    |3          |3.000       |3    
 ij> -- a view of a regular join
 create view jv (fv, ev, dv, cv, bv, av) as (select f, e, d, c, b, a from t, s where b = e);
 0 rows inserted/updated/deleted
@@ -1947,9 +1960,8 @@
 --------------------------------------------------
 ij> -- ---------------------------------------------------------------------;
 -- test unit 3. subquery in OUTER JOIN ON clause;
--- Not allowed in DB2 compatibility mode
 -- ---------------------------------------------------------------------;
--- 301 - '='/like/in in RIGHT JOIN ON condition with correlated IN subquery; Error.
+-- 301 - '='/like/in in RIGHT JOIN ON condition with correlated IN subquery
 select part, p.num, product, pt.NUM, price
 from k55admin.parts p right join k55admin.products pt
 on (pt.product in ('Bolt','Nuts') or
@@ -1961,8 +1973,17 @@
         on 1=0
         where a.num>=pt.num)
 order by 1,2,3,4;
-ERROR 42972: An ON clause associated with a JOIN operator is not valid.
-ij> -- 302 - '='/like/in in LEFT JOIN ON condition with uncorrelated IN subquery; Error.
+PART      |NUM   |PRODUCT        |NUM   |PRICE    
+--------------------------------------------------
+NULL      |NULL  |Generator      |10    |45.75    
+NULL      |NULL  |Hammer         |50    |5.75     
+NULL      |NULL  |Relay          |30    |7.55     
+NULL      |NULL  |Ruler          |30    |8.75     
+NULL      |NULL  |Sander         |20    |35.75    
+NULL      |NULL  |Saw            |205   |18.90    
+NULL      |NULL  |Screwdriver    |505   |3.70     
+NULL      |NULL  |NULL           |20    |NULL     
+ij> -- 302 - '='/like/in in LEFT JOIN ON condition with uncorrelated IN subquery
 select part, p.num, product, pt.NUM, price
 from k55admin.parts p left join k55admin.products pt
 on (pt.product in ('Bolt','Nuts') or
@@ -1973,8 +1994,17 @@
         from k55admin.parts b left join k55admin.products a
         on 1=0)
 order by 1,2,3,4;
-ERROR 42972: An ON clause associated with a JOIN operator is not valid.
-ij> -- 303 - '=' and inlist in RIGHT JOIN ON condition with correlated exists subquery;
Error.
+PART      |NUM   |PRODUCT        |NUM   |PRICE    
+--------------------------------------------------
+Blades    |205   |NULL           |NULL  |NULL     
+Magnets   |10    |NULL           |NULL  |NULL     
+Oil       |160   |NULL           |NULL  |NULL     
+Paper     |20    |NULL           |NULL  |NULL     
+Plastic   |30    |NULL           |NULL  |NULL     
+Steel     |30    |NULL           |NULL  |NULL     
+Wire      |10    |NULL           |NULL  |NULL     
+NULL      |30    |NULL           |NULL  |NULL     
+ij> -- 303 - '=' and inlist in RIGHT JOIN ON condition with correlated exists subquery
 select part, p.num, product, pt.NUM, price
 from k55admin.parts p right join k55admin.products pt
 on pt.product in ('Bolt','Nuts') and p.num = pt.num
@@ -1984,8 +2014,17 @@
         on 1=0
         where a.num>=pt.num)
 order by 1,2,3,4;
-ERROR 42972: An ON clause associated with a JOIN operator is not valid.
-ij> -- 304 - '=' and inlist in LEFT JOIN ON condition with uncorrelated exists subquery;
Error.
+PART      |NUM   |PRODUCT        |NUM   |PRICE    
+--------------------------------------------------
+NULL      |NULL  |Generator      |10    |45.75    
+NULL      |NULL  |Hammer         |50    |5.75     
+NULL      |NULL  |Relay          |30    |7.55     
+NULL      |NULL  |Ruler          |30    |8.75     
+NULL      |NULL  |Sander         |20    |35.75    
+NULL      |NULL  |Saw            |205   |18.90    
+NULL      |NULL  |Screwdriver    |505   |3.70     
+NULL      |NULL  |NULL           |20    |NULL     
+ij> -- 304 - '=' and inlist in LEFT JOIN ON condition with uncorrelated exists subquery
 select part, p.num, product, pt.NUM, price
 from k55admin.parts p left join k55admin.products pt
 on pt.product in ('Bolt','Nuts') and p.num = pt.num
@@ -1994,8 +2033,17 @@
         from k55admin.parts b left join k55admin.products a
         on 1=0)
 order by 1,2,3,4;
-ERROR 42972: An ON clause associated with a JOIN operator is not valid.
-ij> -- 305 - '='/like/in in RIGHT JOIN ON condition with correlated '>=ALL' subquery;
Error.
+PART      |NUM   |PRODUCT        |NUM   |PRICE    
+--------------------------------------------------
+Blades    |205   |NULL           |NULL  |NULL     
+Magnets   |10    |NULL           |NULL  |NULL     
+Oil       |160   |NULL           |NULL  |NULL     
+Paper     |20    |NULL           |NULL  |NULL     
+Plastic   |30    |NULL           |NULL  |NULL     
+Steel     |30    |NULL           |NULL  |NULL     
+Wire      |10    |NULL           |NULL  |NULL     
+NULL      |30    |NULL           |NULL  |NULL     
+ij> -- 305 - '='/like/in in RIGHT JOIN ON condition with correlated '>=ALL' subquery
 select part, p.num, product, pt.NUM, price
 from k55admin.parts p right join k55admin.products pt
 on (pt.product in ('Bolt','Nuts') or
@@ -2007,8 +2055,22 @@
         on 1=0
         where a.num>=pt.num)
 order by 1,2,3,4;
-ERROR 42972: An ON clause associated with a JOIN operator is not valid.
-ij> -- 306 - '='/like/in in LEFT JOIN ON condition with uncorrelated scalar subquery;
Error.
+PART      |NUM   |PRODUCT        |NUM   |PRICE    
+--------------------------------------------------
+Blades    |205   |Saw            |205   |18.90    
+Magnets   |10    |Generator      |10    |45.75    
+Paper     |20    |Sander         |20    |35.75    
+Paper     |20    |NULL           |20    |NULL     
+Plastic   |30    |Relay          |30    |7.55     
+Plastic   |30    |Ruler          |30    |8.75     
+Steel     |30    |Relay          |30    |7.55     
+Steel     |30    |Ruler          |30    |8.75     
+Wire      |10    |Generator      |10    |45.75    
+NULL      |30    |Relay          |30    |7.55     
+NULL      |30    |Ruler          |30    |8.75     
+NULL      |NULL  |Hammer         |50    |5.75     
+NULL      |NULL  |Screwdriver    |505   |3.70     
+ij> -- 306 - '='/like/in in LEFT JOIN ON condition with uncorrelated scalar subquery
 select part, p.num, product, pt.NUM, price
 from k55admin.parts p left join k55admin.products pt
 on (pt.product in ('Bolt','Nuts') or
@@ -2019,7 +2081,16 @@
         from k55admin.parts b left join k55admin.products a
         on 1=0)
 order by 1,2,3,4;
-ERROR 42972: An ON clause associated with a JOIN operator is not valid.
+PART      |NUM   |PRODUCT        |NUM   |PRICE    
+--------------------------------------------------
+Blades    |205   |NULL           |NULL  |NULL     
+Magnets   |10    |NULL           |NULL  |NULL     
+Oil       |160   |NULL           |NULL  |NULL     
+Paper     |20    |NULL           |NULL  |NULL     
+Plastic   |30    |NULL           |NULL  |NULL     
+Steel     |30    |NULL           |NULL  |NULL     
+Wire      |10    |NULL           |NULL  |NULL     
+NULL      |30    |NULL           |NULL  |NULL     
 ij> -- coj202.clp
 ---------------------------------------------------------------------
 --      inner join (105)

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/outerjoin.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/outerjoin.out?rev=882106&r1=882105&r2=882106&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/outerjoin.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/outerjoin.out
Thu Nov 19 11:03:01 2009
@@ -247,23 +247,55 @@
 C1         |C1         
 -----------------------
 1          |1          
-ij> -- subquery in join clause. Not allowed in the DB2 compatibility mode. ERROR.
+ij> -- subquery in join clause.
 -- egs of using {oj --} syntax
 select * from t1 a left outer join t2 b 
 on a.c1 = b.c1 and a.c1 = (select c1 from t1 where a.c1 = t1.c1 and a.c1 = 1);
-ERROR 42972: An ON clause associated with a JOIN operator is not valid.
+C1         |C1         
+-----------------------
+1          |1          
+2          |NULL       
+2          |NULL       
+3          |NULL       
+4          |NULL       
 ij> select * from {oj t1 a left outer join t2 b 
 on a.c1 = b.c1 and a.c1 = (select c1 from t1 where a.c1 = t1.c1 and a.c1 = 1)};
-ERROR 42972: An ON clause associated with a JOIN operator is not valid.
+C1         |C1         
+-----------------------
+1          |1          
+2          |NULL       
+2          |NULL       
+3          |NULL       
+4          |NULL       
 ij> select * from t1 a left outer join t2 b 
 on a.c1 = b.c1 and a.c1 = (select c1 from t1 where a.c1 = t1.c1 and a.c1 <> 2);
-ERROR 42972: An ON clause associated with a JOIN operator is not valid.
+C1         |C1         
+-----------------------
+1          |1          
+2          |NULL       
+2          |NULL       
+3          |3          
+3          |3          
+4          |NULL       
 ij> select * from {oj t1 a left outer join t2 b 
 on a.c1 = b.c1 and a.c1 = (select c1 from t1 where a.c1 = t1.c1 and a.c1 <> 2)};
-ERROR 42972: An ON clause associated with a JOIN operator is not valid.
+C1         |C1         
+-----------------------
+1          |1          
+2          |NULL       
+2          |NULL       
+3          |3          
+3          |3          
+4          |NULL       
 ij> select * from t1 a right outer join t2 b 
 on a.c1 = b.c1 and a.c1 in (select c1 from t1 where a.c1 = t1.c1);
-ERROR 42972: An ON clause associated with a JOIN operator is not valid.
+C1         |C1         
+-----------------------
+1          |1          
+3          |3          
+3          |3          
+NULL       |5          
+NULL       |6          
 ij> -- outer join in subquery
 -- egs of using {oj --} syntax
 select * from t1 a
@@ -1915,14 +1947,26 @@
 0 rows inserted/updated/deleted
 ij> insert into ttab2 values (1,1),(2,2);
 2 rows inserted/updated/deleted
-ij> -- this statement should raise an error because 
--- more than one object table includes column "b"
+ij> -- DERBY-4380: These statements used to raise an error because
+-- more than one object table includes column "b". But the scope of the
+-- ON clauses makes it clear which table they belong to in each case, so
+-- they should not fail.
 select cor1.*, cor2.* from ttab1 cor1 left outer join ttab2 on (b = d),
 		ttab1 left outer join ttab2 cor2 on (b = d);
-ERROR 42X03: Column name 'B' is in more than one table in the FROM list.
+A          |B          |C          |D          
+-----------------------------------------------
+1          |1          |1          |1          
+1          |1          |2          |2          
+2          |2          |1          |1          
+2          |2          |2          |2          
 ij> select cor1.*, cor2.* from ttab1 cor1 left outer join ttab2 on (b = d),
 		ttab1 left outer join ttab2 cor2 on (b = cor2.d);
-ERROR 42X03: Column name 'B' is in more than one table in the FROM list.
+A          |B          |C          |D          
+-----------------------------------------------
+1          |1          |1          |1          
+1          |1          |2          |2          
+2          |2          |1          |1          
+2          |2          |2          |2          
 ij> -- This should pass
 select cor1.*, cor2.* from ttab1 left outer join ttab2 on (b = d), 
 		ttab1 cor1 left outer join ttab2 cor2 on (cor1.b = cor2.d);
@@ -1936,7 +1980,7 @@
 select * from ttab1, ttab1 left outer join ttab2 on (a=c);
 ERROR 42X03: Column name 'TTAB1.A' is in more than one table in the FROM list.
 ij> select * from ttab1 cor1, ttab1 left outer join ttab2 on (cor1.a=c);
-ERROR 42972: An ON clause associated with a JOIN operator is not valid.
+ERROR 42X04: Column 'COR1.A' 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
'COR1.A' is not a column in the target table.
 ij> -- This should pass
 select * from ttab1, ttab1 cor1 left outer join ttab2 on (cor1.a=c);
 A          |B          |A          |B          |C          |D          

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java?rev=882106&r1=882105&r2=882106&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java
Thu Nov 19 11:03:01 2009
@@ -697,4 +697,46 @@
         assertStatementError(TABLE_NAME_NOT_IN_SCOPE, s,
                 "select xyz.* from t1 join t2 using (b)");
     }
+
+    /**
+     * Test that ON clauses can contain subqueries (DERBY-4380).
+     */
+    public void testSubqueryInON() throws SQLException {
+        setAutoCommit(false);
+
+        Statement s = createStatement();
+        s.execute("create table t1(a int)");
+        s.execute("insert into t1 values 1,2,3");
+        s.execute("create table t2(b int)");
+        s.execute("insert into t2 values 1,2");
+        s.execute("create table t3(c int)");
+        s.execute("insert into t3 values 2,3");
+
+        JDBC.assertUnorderedResultSet(
+            s.executeQuery(
+                "select * from t1 join t2 on a = some (select c from t3)"),
+            new String[][]{{"2", "1"}, {"2", "2"}, {"3", "1"}, {"3", "2"}});
+
+        JDBC.assertUnorderedResultSet(
+            s.executeQuery("select * from t1 left join t2 " +
+                           "on a = b and b not in (select c from t3)"),
+            new String[][]{{"1", "1"}, {"2", null}, {"3", null}});
+
+        JDBC.assertUnorderedResultSet(
+            s.executeQuery("select * from t3 join t2 on exists " +
+                           "(select * from t2 join t1 on exists " +
+                           "(select * from t3 where c = a))"),
+            new String[][]{{"2", "1"}, {"2", "2"}, {"3", "1"}, {"3", "2"}});
+
+        JDBC.assertSingleValueResultSet(
+            s.executeQuery("select a from t1 join t2 " +
+                           "on a = (select count(*) from t3) and a = b"),
+            "2");
+
+        // This query used to cause NullPointerException with early versions
+        // of the DERBY-4380 patch.
+        JDBC.assertEmpty(s.executeQuery(
+            "select * from t1 join t2 on exists " +
+            "(select * from t3 x left join t3 y on 1=0 where y.c=1)"));
+    }
 }

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/innerjoin.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/innerjoin.sql?rev=882106&r1=882105&r2=882106&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/innerjoin.sql
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/innerjoin.sql
Thu Nov 19 11:03:01 2009
@@ -86,7 +86,7 @@
 select * from t1 join t2 on t1.c1 = t2.c1 where t1.c1 = 1;
 select * from t1 join t2 on t1.c1 = 1 where t2.c1 = t1.c1;
 
--- subquery in join clause, not allowed in DB2 compatibility mode
+-- subquery in join clause
 select * from t1 a join t2 b 
 on a.c1 = b.c1 and a.c1 = (select c1 from t1 where a.c1 = t1.c1);
 select * from t1 a join t2 b 

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/lojreorder.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/lojreorder.sql?rev=882106&r1=882105&r2=882106&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/lojreorder.sql
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/lojreorder.sql
Thu Nov 19 11:03:01 2009
@@ -79,7 +79,10 @@
 -- In DB2 UDB, any column referenced in an expression of the 
 -- join-condition must be a column of one of the operand tables of 
 -- the associated join (in the scope of the same joined-table clause).
--- this query should fail
+-- DERBY-4380: This query used to fail because column B was seen as ambiguous
+-- (could belong to both T1 and T2). However, the scope of the ON clause
+-- makes it unabiguous; the first occurrence of B must be T1.B, and the second
+-- one must be T2.B.
 select t1.*, s2.* from t t1 left outer join s on (b = e), t t2 left outer join s s2 on (b
= e);
 
 -- a view of a regular join
@@ -789,10 +792,9 @@
 
 -- ---------------------------------------------------------------------;
 -- test unit 3. subquery in OUTER JOIN ON clause;
--- Not allowed in DB2 compatibility mode
 -- ---------------------------------------------------------------------;
 
--- 301 - '='/like/in in RIGHT JOIN ON condition with correlated IN subquery; Error.
+-- 301 - '='/like/in in RIGHT JOIN ON condition with correlated IN subquery
 select part, p.num, product, pt.NUM, price
 from k55admin.parts p right join k55admin.products pt
 on (pt.product in ('Bolt','Nuts') or
@@ -805,7 +807,7 @@
         where a.num>=pt.num)
 order by 1,2,3,4;
 
--- 302 - '='/like/in in LEFT JOIN ON condition with uncorrelated IN subquery; Error.
+-- 302 - '='/like/in in LEFT JOIN ON condition with uncorrelated IN subquery
 select part, p.num, product, pt.NUM, price
 from k55admin.parts p left join k55admin.products pt
 on (pt.product in ('Bolt','Nuts') or
@@ -817,7 +819,7 @@
         on 1=0)
 order by 1,2,3,4;
 
--- 303 - '=' and inlist in RIGHT JOIN ON condition with correlated exists subquery; Error.
+-- 303 - '=' and inlist in RIGHT JOIN ON condition with correlated exists subquery
 select part, p.num, product, pt.NUM, price
 from k55admin.parts p right join k55admin.products pt
 on pt.product in ('Bolt','Nuts') and p.num = pt.num
@@ -828,7 +830,7 @@
         where a.num>=pt.num)
 order by 1,2,3,4;
 
--- 304 - '=' and inlist in LEFT JOIN ON condition with uncorrelated exists subquery; Error.
+-- 304 - '=' and inlist in LEFT JOIN ON condition with uncorrelated exists subquery
 select part, p.num, product, pt.NUM, price
 from k55admin.parts p left join k55admin.products pt
 on pt.product in ('Bolt','Nuts') and p.num = pt.num
@@ -838,7 +840,7 @@
         on 1=0)
 order by 1,2,3,4;
 
--- 305 - '='/like/in in RIGHT JOIN ON condition with correlated '>=ALL' subquery; Error.
+-- 305 - '='/like/in in RIGHT JOIN ON condition with correlated '>=ALL' subquery
 select part, p.num, product, pt.NUM, price
 from k55admin.parts p right join k55admin.products pt
 on (pt.product in ('Bolt','Nuts') or
@@ -851,7 +853,7 @@
         where a.num>=pt.num)
 order by 1,2,3,4;
 
--- 306 - '='/like/in in LEFT JOIN ON condition with uncorrelated scalar subquery; Error.
+-- 306 - '='/like/in in LEFT JOIN ON condition with uncorrelated scalar subquery
 select part, p.num, product, pt.NUM, price
 from k55admin.parts p left join k55admin.products pt
 on (pt.product in ('Bolt','Nuts') or

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/outerjoin.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/outerjoin.sql?rev=882106&r1=882105&r2=882106&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/outerjoin.sql
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/outerjoin.sql
Thu Nov 19 11:03:01 2009
@@ -100,7 +100,7 @@
 select * from t1 right outer join t2 on t1.c1 = 1 where t2.c1 = t1.c1;
 select * from {oj t1 right outer join t2 on t1.c1 = 1} where t2.c1 = t1.c1;
 
--- subquery in join clause. Not allowed in the DB2 compatibility mode. ERROR.
+-- subquery in join clause.
 -- egs of using {oj --} syntax
 select * from t1 a left outer join t2 b 
 on a.c1 = b.c1 and a.c1 = (select c1 from t1 where a.c1 = t1.c1 and a.c1 = 1);
@@ -463,8 +463,10 @@
 create table ttab2 (c int, d int);
 insert into ttab2 values (1,1),(2,2);
 
--- this statement should raise an error because 
--- more than one object table includes column "b"
+-- DERBY-4380: These statements used to raise an error because
+-- more than one object table includes column "b". But the scope of the
+-- ON clauses makes it clear which table they belong to in each case, so
+-- they should not fail.
 select cor1.*, cor2.* from ttab1 cor1 left outer join ttab2 on (b = d),
 		ttab1 left outer join ttab2 cor2 on (b = d);
 select cor1.*, cor2.* from ttab1 cor1 left outer join ttab2 on (b = d),



Mime
View raw message