You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by dy...@apache.org on 2008/02/18 13:36:22 UTC
svn commit: r628704 -
/db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NestedWhereSubqueryTest.java
Author: dyre
Date: Mon Feb 18 04:36:20 2008
New Revision: 628704
URL: http://svn.apache.org/viewvc?rev=628704&view=rev
Log:
DERBY-3349, merging to 10.3: Nested WHERE EXISTS queries need improved testing
Merged with svn merge -r 628685:628686 ../derby-repro
Simple merge with no conflicts; no additional changes were necessary.
Modified:
db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NestedWhereSubqueryTest.java
Modified: db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NestedWhereSubqueryTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NestedWhereSubqueryTest.java?rev=628704&r1=628703&r2=628704&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NestedWhereSubqueryTest.java (original)
+++ db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NestedWhereSubqueryTest.java Mon Feb 18 04:36:20 2008
@@ -191,6 +191,170 @@
rs = s.executeQuery(sb.toString());
JDBC.assertUnorderedResultSet(rs, expectedRows);
+ /* A variation of the above WHERE EXISTS but using ANY should return the same rows */
+ sb = new StringBuffer();
+ sb.append("select unbound_e.empid, unbound_p.projid ");
+ sb.append("from departments this, ");
+ sb.append(" employees unbound_e, ");
+ sb.append(" projects unbound_p ");
+ sb.append("where exists ( ");
+ sb.append(" select 1 from employees this_employees_e ");
+ sb.append(" where this_employees_e.empid = any ( ");
+ sb.append(" select this_employees_e_projects_p.empid ");
+ sb.append(" from project_employees this_employees_e_projects_p ");
+ sb.append(" where this_employees_e_projects_p.empid = this_employees_e.empid ");
+ sb.append(" and this_employees_e.department = this.id ");
+ sb.append(" and unbound_p.projid = this_employees_e_projects_p.projid ");
+ sb.append(" and unbound_e.empid = this_employees_e.empid) ");
+ sb.append(" )");
+
+ rs = s.executeQuery(sb.toString());
+ JDBC.assertUnorderedResultSet(rs, expectedRows);
+
+ /*
+ * The next 5 queries were also found problematic as part DERBY-3301
+ */
+ sb = new StringBuffer();
+ sb.append("select unbound_e.empid from departments this, employees unbound_e ");
+ sb.append("where exists ( ");
+ sb.append(" select 1 from employees this_employees_e ");
+ sb.append(" where this_employees_e.department = this.id and ");
+ sb.append(" unbound_e.empid = this_employees_e.empid and this.id = 2)");
+
+ rs = s.executeQuery(sb.toString());
+ expectedRows = new String [][] {{"14"},{"15"}};
+ JDBC.assertUnorderedResultSet(rs, expectedRows);
+
+ sb = new StringBuffer();
+ sb.append("select this.id,unbound_e.empid,unbound_p.projid from departments this, ");
+ sb.append(" employees unbound_e, projects unbound_p ");
+ sb.append("where exists ( ");
+ sb.append(" select 1 from employees this_employees_e ");
+ sb.append(" where exists ( ");
+ sb.append(" select 1 from project_employees this_employees_e_projects_p ");
+ sb.append(" where this_employees_e_projects_p.\"EMPID\" = this_employees_e.empid and ");
+ sb.append(" unbound_p.projid = this_employees_e_projects_p.projid and ");
+ sb.append(" this_employees_e.department = this.id and ");
+ sb.append(" unbound_e.empid = this_employees_e.empid ");
+ sb.append(" )) ");
+
+ rs = s.executeQuery(sb.toString());
+ expectedRows = new String [][] {{"1","11","101"},
+ {"1","12","101"},
+ {"1","13","101"},
+ {"1","12","102"},
+ {"1","13","102"},
+ {"2","14","103"},
+ {"2","15","103"}};
+ JDBC.assertUnorderedResultSet(rs, expectedRows);
+
+ sb = new StringBuffer();
+ sb.append("select unbound_e.empid,unbound_p.projid from departments this, ");
+ sb.append(" employees unbound_e, projects unbound_p ");
+ sb.append("where exists ( ");
+ sb.append(" select 1 from employees this_employees_e ");
+ sb.append(" where exists ( ");
+ sb.append(" select 1 from project_employees this_employees_e_projects_p ");
+ sb.append(" where this_employees_e_projects_p.\"EMPID\" = this_employees_e.empid ");
+ sb.append(" and unbound_p.projid = this_employees_e_projects_p.projid ");
+ sb.append(" and this_employees_e.department = this.id ");
+ sb.append(" and unbound_e.empid = this_employees_e.empid ");
+ sb.append(" and this.id = 1)) ");
+
+ rs = s.executeQuery(sb.toString());
+ expectedRows = new String [][] {{"11","101"},
+ {"12","101"},
+ {"13","101"},
+ {"12","102"},
+ {"13","102"}};
+ JDBC.assertUnorderedResultSet(rs, expectedRows);
+
+ sb = new StringBuffer();
+ sb.append("select unbound_e.empid,unbound_p.projid from departments this, ");
+ sb.append(" employees unbound_e, projects unbound_p ");
+ sb.append("where exists ( ");
+ sb.append(" select 1 from employees this_employees_e ");
+ sb.append(" where exists ( ");
+ sb.append(" select 1 from project_employees this_employees_e_projects_p ");
+ sb.append(" where this_employees_e_projects_p.\"EMPID\" = this_employees_e.empid ");
+ sb.append(" and unbound_p.projid = this_employees_e_projects_p.projid ");
+ sb.append(" and this_employees_e.department = this.id ");
+ sb.append(" and unbound_e.empid = this_employees_e.empid ");
+ sb.append(" and this.companyid = 1))");
+
+ rs = s.executeQuery(sb.toString());
+ expectedRows = new String [][] {{"11","101"},
+ {"12","101"},
+ {"13","101"},
+ {"12","102"},
+ {"13","102"},
+ {"14","103"},
+ {"15","103"}};
+ JDBC.assertUnorderedResultSet(rs, expectedRows);
+
+ sb = new StringBuffer();
+ sb.append("select unbound_e.empid, unbound_p.projid ");
+ sb.append("from departments this, ");
+ sb.append(" employees unbound_e, ");
+ sb.append(" projects unbound_p ");
+ sb.append("where exists ( ");
+ sb.append(" select 1 from employees this_employees_e ");
+ sb.append(" where 1 = 1 and exists ( ");
+ sb.append(" select 1 from project_employees this_employees_e_projects_p ");
+ sb.append(" where this_employees_e_projects_p.empid = this_employees_e.empid ");
+ sb.append(" and this_employees_e.department = this.id ");
+ sb.append(" and unbound_p.projid = this_employees_e_projects_p.projid ");
+ sb.append(" and unbound_e.empid = this_employees_e.empid) ");
+ sb.append(")");
+
+ rs = s.executeQuery(sb.toString());
+ expectedRows = new String [][] {{"11","101"},
+ {"12","101"},
+ {"13","101"},
+ {"12","102"},
+ {"13","102"},
+ {"14","103"},
+ {"15","103"}};
+ JDBC.assertUnorderedResultSet(rs, expectedRows);
+
+ /* Variation of the above using WHERE IN ... WHERE IN */
+ sb = new StringBuffer();
+ sb.append("select unbound_e.empid, unbound_p.projid ");
+ sb.append("from departments this, employees unbound_e, projects unbound_p ");
+ sb.append("where this.id in ( ");
+ sb.append(" select this_employees_e.department from employees this_employees_e ");
+ sb.append(" where this_employees_e.empid in ( ");
+ sb.append(" select this_employees_e_projects_p.empid ");
+ sb.append(" from project_employees this_employees_e_projects_p ");
+ sb.append(" where this_employees_e_projects_p.empid = this_employees_e.empid ");
+ sb.append(" and this_employees_e.department = this.id ");
+ sb.append(" and unbound_p.projid = this_employees_e_projects_p.projid ");
+ sb.append(" and unbound_e.empid = this_employees_e.empid)");
+ sb.append(")");
+
+ rs = s.executeQuery(sb.toString());
+ /* Expected rows are as above */
+ JDBC.assertUnorderedResultSet(rs, expectedRows);
+
+ /* Variation of the above using WHERE ANY ... WHERE ANY */
+ sb = new StringBuffer();
+ sb.append("select unbound_e.empid, unbound_p.projid ");
+ sb.append("from departments this, employees unbound_e, projects unbound_p ");
+ sb.append("where this.id = any ( ");
+ sb.append(" select this_employees_e.department from employees this_employees_e ");
+ sb.append(" where this_employees_e.empid = any ( ");
+ sb.append(" select this_employees_e_projects_p.empid ");
+ sb.append(" from project_employees this_employees_e_projects_p ");
+ sb.append(" where this_employees_e_projects_p.empid = this_employees_e.empid ");
+ sb.append(" and this_employees_e.department = this.id ");
+ sb.append(" and unbound_p.projid = this_employees_e_projects_p.projid ");
+ sb.append(" and unbound_e.empid = this_employees_e.empid)");
+ sb.append(")");
+
+ rs = s.executeQuery(sb.toString());
+ /* Expected rows are as above */
+ JDBC.assertUnorderedResultSet(rs, expectedRows);
+
/*
* Clean up the tables used.
*/