http://git-wip-us.apache.org/repos/asf/phoenix/blob/153612aa/phoenix-core/src/it/java/org/apache/phoenix/end2end/SubqueryIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/SubqueryIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SubqueryIT.java index d7b3b29..684c3c2 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/SubqueryIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SubqueryIT.java @@ -272,8 +272,11 @@ public class SubqueryIT extends BaseJoinIT { public void testNonCorrelatedSubquery() throws Exception { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); + String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME); + String tableName4 = getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME); + String tableName5 = getTableName(conn, JOIN_COITEM_TABLE_FULL_NAME); try { - String query = "SELECT \"item_id\", name FROM " + getTableName(conn,JOIN_ITEM_TABLE_FULL_NAME) + " WHERE \"item_id\" >= ALL (SELECT \"item_id\" FROM " + getTableName(conn,JOIN_ORDER_TABLE_FULL_NAME) + ") ORDER BY name"; + String query = "SELECT \"item_id\", name FROM " + tableName1 + " WHERE \"item_id\" >= ALL (SELECT \"item_id\" FROM " + tableName4 + ") ORDER BY name"; PreparedStatement statement = conn.prepareStatement(query); ResultSet rs = statement.executeQuery(); assertTrue (rs.next()); @@ -285,7 +288,7 @@ public class SubqueryIT extends BaseJoinIT { assertFalse(rs.next()); - query = "SELECT \"item_id\", name FROM " + getTableName(conn,JOIN_ITEM_TABLE_FULL_NAME) + " WHERE \"item_id\" < ANY (SELECT \"item_id\" FROM " + getTableName(conn,JOIN_ORDER_TABLE_FULL_NAME) + ")"; + query = "SELECT \"item_id\", name FROM " + tableName1 + " WHERE \"item_id\" < ANY (SELECT \"item_id\" FROM " + tableName4 + ")"; statement = conn.prepareStatement(query); rs = statement.executeQuery(); assertTrue (rs.next()); @@ -306,7 +309,7 @@ public class SubqueryIT extends BaseJoinIT { assertFalse(rs.next()); - query = "SELECT \"item_id\", name FROM " + getTableName(conn,JOIN_ITEM_TABLE_FULL_NAME) + " WHERE \"item_id\" < (SELECT max(\"item_id\") FROM " + getTableName(conn,JOIN_ORDER_TABLE_FULL_NAME) + ")"; + query = "SELECT \"item_id\", name FROM " + tableName1 + " WHERE \"item_id\" < (SELECT max(\"item_id\") FROM " + tableName4 + ")"; statement = conn.prepareStatement(query); rs = statement.executeQuery(); assertTrue (rs.next()); @@ -327,7 +330,7 @@ public class SubqueryIT extends BaseJoinIT { assertFalse(rs.next()); - query = "SELECT * FROM " + getTableName(conn, JOIN_COITEM_TABLE_FULL_NAME) + " WHERE (item_id, item_name) != ALL (SELECT \"item_id\", name FROM " + getTableName(conn,JOIN_ITEM_TABLE_FULL_NAME) + ")"; + query = "SELECT * FROM " + tableName5 + " WHERE (item_id, item_name) != ALL (SELECT \"item_id\", name FROM " + tableName1 + ")"; statement = conn.prepareStatement(query); rs = statement.executeQuery(); assertTrue (rs.next()); @@ -338,7 +341,7 @@ public class SubqueryIT extends BaseJoinIT { assertFalse(rs.next()); - query = "SELECT * FROM " + getTableName(conn, JOIN_COITEM_TABLE_FULL_NAME) + " WHERE EXISTS (SELECT \"item_id\", name FROM " + getTableName(conn,JOIN_ITEM_TABLE_FULL_NAME) + ")"; + query = "SELECT * FROM " + tableName5 + " WHERE EXISTS (SELECT \"item_id\", name FROM " + tableName1 + ")"; statement = conn.prepareStatement(query); rs = statement.executeQuery(); assertTrue (rs.next()); @@ -364,7 +367,7 @@ public class SubqueryIT extends BaseJoinIT { assertFalse(rs.next()); - query = "SELECT \"item_id\", name FROM " + getTableName(conn,JOIN_ITEM_TABLE_FULL_NAME) + " WHERE \"item_id\" < (SELECT \"item_id\" FROM " + getTableName(conn,JOIN_ORDER_TABLE_FULL_NAME) + ")"; + query = "SELECT \"item_id\", name FROM " + tableName1 + " WHERE \"item_id\" < (SELECT \"item_id\" FROM " + tableName4 + ")"; statement = conn.prepareStatement(query); try { rs = statement.executeQuery(); @@ -380,8 +383,12 @@ public class SubqueryIT extends BaseJoinIT { public void testInSubquery() throws Exception { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); + String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME); + String tableName2 = getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME); + String tableName4 = getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME); + String tableName5 = getTableName(conn, JOIN_COITEM_TABLE_FULL_NAME); try { - String query = "SELECT \"item_id\", name FROM " + getTableName(conn,JOIN_ITEM_TABLE_FULL_NAME) + " WHERE \"item_id\" IN (SELECT \"item_id\" FROM " + getTableName(conn,JOIN_ORDER_TABLE_FULL_NAME) + ") ORDER BY name"; + String query = "SELECT \"item_id\", name FROM " + tableName1 + " WHERE \"item_id\" IN (SELECT \"item_id\" FROM " + tableName4 + ") ORDER BY name"; PreparedStatement statement = conn.prepareStatement(query); ResultSet rs = statement.executeQuery(); assertTrue (rs.next()); @@ -399,7 +406,7 @@ public class SubqueryIT extends BaseJoinIT { assertFalse(rs.next()); - query = "SELECT \"item_id\", name FROM " + getTableName(conn,JOIN_ITEM_TABLE_FULL_NAME) + " WHERE \"item_id\" NOT IN (SELECT \"item_id\" FROM " + getTableName(conn,JOIN_ORDER_TABLE_FULL_NAME) + ") ORDER BY name"; + query = "SELECT \"item_id\", name FROM " + tableName1 + " WHERE \"item_id\" NOT IN (SELECT \"item_id\" FROM " + tableName4 + ") ORDER BY name"; statement = conn.prepareStatement(query); rs = statement.executeQuery(); assertTrue (rs.next()); @@ -414,7 +421,7 @@ public class SubqueryIT extends BaseJoinIT { assertFalse(rs.next()); - query = "SELECT i.\"item_id\", s.name FROM " + getTableName(conn,JOIN_ITEM_TABLE_FULL_NAME) + " i JOIN " + getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME) + " s ON i.\"supplier_id\" = s.\"supplier_id\" WHERE i.\"item_id\" IN (SELECT \"item_id\" FROM " + getTableName(conn,JOIN_ORDER_TABLE_FULL_NAME) + " WHERE \"order_id\" > '000000000000001') ORDER BY i.name"; + query = "SELECT i.\"item_id\", s.name FROM " + tableName1 + " i JOIN " + tableName2 + " s ON i.\"supplier_id\" = s.\"supplier_id\" WHERE i.\"item_id\" IN (SELECT \"item_id\" FROM " + tableName4 + " WHERE \"order_id\" > '000000000000001') ORDER BY i.name"; statement = conn.prepareStatement(query); rs = statement.executeQuery(); assertTrue (rs.next()); @@ -433,7 +440,7 @@ public class SubqueryIT extends BaseJoinIT { String plan = QueryUtil.getExplainPlan(rs); assertPlansMatch(plans[0], plan); - query = "SELECT i.\"item_id\", s.name FROM " + getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME) + " s LEFT JOIN " + getTableName(conn,JOIN_ITEM_TABLE_FULL_NAME) + " i ON i.\"supplier_id\" = s.\"supplier_id\" WHERE i.\"item_id\" IN (SELECT \"item_id\" FROM " + getTableName(conn,JOIN_ORDER_TABLE_FULL_NAME) + ") ORDER BY i.name"; + query = "SELECT i.\"item_id\", s.name FROM " + tableName2 + " s LEFT JOIN " + tableName1 + " i ON i.\"supplier_id\" = s.\"supplier_id\" WHERE i.\"item_id\" IN (SELECT \"item_id\" FROM " + tableName4 + ") ORDER BY i.name"; statement = conn.prepareStatement(query); rs = statement.executeQuery(); assertTrue (rs.next()); @@ -454,8 +461,8 @@ public class SubqueryIT extends BaseJoinIT { rs = conn.createStatement().executeQuery("EXPLAIN " + query); assertPlansEqual(plans[1], QueryUtil.getExplainPlan(rs)); - query = "SELECT * FROM " + getTableName(conn, JOIN_COITEM_TABLE_FULL_NAME) + " WHERE (item_id, item_name) IN (SELECT \"item_id\", name FROM " + getTableName(conn,JOIN_ITEM_TABLE_FULL_NAME) + " WHERE \"item_id\" NOT IN (SELECT \"item_id\" FROM " + getTableName(conn,JOIN_ORDER_TABLE_FULL_NAME) + "))" - + " OR (co_item_id, co_item_name) IN (SELECT \"item_id\", name FROM " + getTableName(conn,JOIN_ITEM_TABLE_FULL_NAME) + " WHERE \"item_id\" IN (SELECT \"item_id\" FROM " + getTableName(conn,JOIN_ORDER_TABLE_FULL_NAME) + "))"; + query = "SELECT * FROM " + tableName5 + " WHERE (item_id, item_name) IN (SELECT \"item_id\", name FROM " + tableName1 + " WHERE \"item_id\" NOT IN (SELECT \"item_id\" FROM " + tableName4 + "))" + + " OR (co_item_id, co_item_name) IN (SELECT \"item_id\", name FROM " + tableName1 + " WHERE \"item_id\" IN (SELECT \"item_id\" FROM " + tableName4 + "))"; statement = conn.prepareStatement(query); rs = statement.executeQuery(); assertTrue (rs.next()); @@ -483,8 +490,11 @@ public class SubqueryIT extends BaseJoinIT { public void testExistsSubquery() throws Exception { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); + String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME); + String tableName4 = getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME); + String tableName5 = getTableName(conn, JOIN_COITEM_TABLE_FULL_NAME); try { - String query = "SELECT \"item_id\", name FROM " + getTableName(conn,JOIN_ITEM_TABLE_FULL_NAME) + " i WHERE NOT EXISTS (SELECT 1 FROM " + getTableName(conn,JOIN_ORDER_TABLE_FULL_NAME) + " o WHERE o.\"item_id\" = i.\"item_id\") ORDER BY name"; + String query = "SELECT \"item_id\", name FROM " + tableName1 + " i WHERE NOT EXISTS (SELECT 1 FROM " + tableName4 + " o WHERE o.\"item_id\" = i.\"item_id\") ORDER BY name"; PreparedStatement statement = conn.prepareStatement(query); ResultSet rs = statement.executeQuery(); assertTrue (rs.next()); @@ -502,8 +512,8 @@ public class SubqueryIT extends BaseJoinIT { rs = conn.createStatement().executeQuery("EXPLAIN " + query); assertPlansEqual(plans[3], QueryUtil.getExplainPlan(rs)); - query = "SELECT * FROM " + getTableName(conn, JOIN_COITEM_TABLE_FULL_NAME) + " co WHERE EXISTS (SELECT 1 FROM " + getTableName(conn,JOIN_ITEM_TABLE_FULL_NAME) + " i WHERE NOT EXISTS (SELECT 1 FROM " + getTableName(conn,JOIN_ORDER_TABLE_FULL_NAME) + " WHERE \"item_id\" = i.\"item_id\") AND co.item_id = \"item_id\" AND name = co.item_name)" - + " OR EXISTS (SELECT 1 FROM " + getTableName(conn,JOIN_ITEM_TABLE_FULL_NAME) + " WHERE \"item_id\" IN (SELECT \"item_id\" FROM " + getTableName(conn,JOIN_ORDER_TABLE_FULL_NAME) + ") AND co.co_item_id = \"item_id\" AND name = co.co_item_name)"; + query = "SELECT * FROM " + tableName5 + " co WHERE EXISTS (SELECT 1 FROM " + tableName1 + " i WHERE NOT EXISTS (SELECT 1 FROM " + tableName4 + " WHERE \"item_id\" = i.\"item_id\") AND co.item_id = \"item_id\" AND name = co.item_name)" + + " OR EXISTS (SELECT 1 FROM " + tableName1 + " WHERE \"item_id\" IN (SELECT \"item_id\" FROM " + tableName4 + ") AND co.co_item_id = \"item_id\" AND name = co.co_item_name)"; statement = conn.prepareStatement(query); rs = statement.executeQuery(); assertTrue (rs.next()); @@ -531,11 +541,14 @@ public class SubqueryIT extends BaseJoinIT { public void testComparisonSubquery() throws Exception { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); final Connection conn = DriverManager.getConnection(getUrl(), props); + String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME); + String tableName3 = getTableName(conn, JOIN_CUSTOMER_TABLE_FULL_NAME); + String tableName4 = getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME); try { - String query = "SELECT \"order_id\", name FROM " + getTableName(conn,JOIN_ORDER_TABLE_FULL_NAME) + - " o JOIN " + getTableName(conn,JOIN_ITEM_TABLE_FULL_NAME) + + String query = "SELECT \"order_id\", name FROM " + tableName4 + + " o JOIN " + tableName1 + " i ON o.\"item_id\" = i.\"item_id\" WHERE quantity = (SELECT max(quantity) FROM " + - getTableName(conn,JOIN_ORDER_TABLE_FULL_NAME) + " q WHERE o.\"item_id\" = q.\"item_id\")"; + tableName4 + " q WHERE o.\"item_id\" = q.\"item_id\")"; PreparedStatement statement = conn.prepareStatement(query); ResultSet rs = statement.executeQuery(); assertTrue (rs.next()); @@ -553,10 +566,10 @@ public class SubqueryIT extends BaseJoinIT { assertFalse(rs.next()); - query = "SELECT \"order_id\", name FROM " + getTableName(conn,JOIN_ORDER_TABLE_FULL_NAME) + - " o JOIN " + getTableName(conn,JOIN_ITEM_TABLE_FULL_NAME) + + query = "SELECT \"order_id\", name FROM " + tableName4 + + " o JOIN " + tableName1 + " i ON o.\"item_id\" = i.\"item_id\" WHERE quantity = (SELECT max(quantity) FROM " + - getTableName(conn,JOIN_ITEM_TABLE_FULL_NAME) + " i2 JOIN " + getTableName(conn,JOIN_ORDER_TABLE_FULL_NAME) + + tableName1 + " i2 JOIN " + tableName4 + " q ON i2.\"item_id\" = q.\"item_id\" WHERE o.\"item_id\" = i2.\"item_id\")"; statement = conn.prepareStatement(query); rs = statement.executeQuery(); @@ -575,11 +588,11 @@ public class SubqueryIT extends BaseJoinIT { assertFalse(rs.next()); - query = "SELECT name from " + getTableName(conn, JOIN_CUSTOMER_TABLE_FULL_NAME) + + query = "SELECT name from " + tableName3 + " WHERE \"customer_id\" IN (SELECT \"customer_id\" FROM " + - getTableName(conn,JOIN_ITEM_TABLE_FULL_NAME) + " i JOIN " + getTableName(conn,JOIN_ORDER_TABLE_FULL_NAME) + + tableName1 + " i JOIN " + tableName4 + " o ON o.\"item_id\" = i.\"item_id\" WHERE i.name = 'T2' OR quantity > (SELECT avg(quantity) FROM " + - getTableName(conn,JOIN_ORDER_TABLE_FULL_NAME) + " q WHERE o.\"item_id\" = q.\"item_id\"))"; + tableName4 + " q WHERE o.\"item_id\" = q.\"item_id\"))"; statement = conn.prepareStatement(query); rs = statement.executeQuery(); assertTrue (rs.next()); @@ -593,8 +606,8 @@ public class SubqueryIT extends BaseJoinIT { String plan = QueryUtil.getExplainPlan(rs); assertPlansMatch(plans[4], plan); - query = "SELECT \"order_id\" FROM " + getTableName(conn,JOIN_ORDER_TABLE_FULL_NAME) + - " o WHERE quantity = (SELECT quantity FROM " + getTableName(conn,JOIN_ORDER_TABLE_FULL_NAME) + + query = "SELECT \"order_id\" FROM " + tableName4 + + " o WHERE quantity = (SELECT quantity FROM " + tableName4 + " WHERE o.\"item_id\" = \"item_id\" AND \"order_id\" != '000000000000004')"; statement = conn.prepareStatement(query); rs = statement.executeQuery(); @@ -609,8 +622,8 @@ public class SubqueryIT extends BaseJoinIT { assertFalse(rs.next()); - query = "SELECT \"order_id\" FROM " + getTableName(conn,JOIN_ORDER_TABLE_FULL_NAME) + - " o WHERE quantity = (SELECT quantity FROM " + getTableName(conn,JOIN_ORDER_TABLE_FULL_NAME) + + query = "SELECT \"order_id\" FROM " + tableName4 + + " o WHERE quantity = (SELECT quantity FROM " + tableName4 + " WHERE o.\"item_id\" = \"item_id\" AND \"order_id\" != '000000000000003')"; statement = conn.prepareStatement(query); rs = statement.executeQuery(); @@ -620,8 +633,8 @@ public class SubqueryIT extends BaseJoinIT { } catch (SQLException e) { } - query = "SELECT \"order_id\" FROM " + getTableName(conn,JOIN_ORDER_TABLE_FULL_NAME) + - " o WHERE quantity = (SELECT max(quantity) FROM " + getTableName(conn,JOIN_ORDER_TABLE_FULL_NAME) + + query = "SELECT \"order_id\" FROM " + tableName4 + + " o WHERE quantity = (SELECT max(quantity) FROM " + tableName4 + " WHERE o.\"item_id\" = \"item_id\" AND \"order_id\" != '000000000000004' GROUP BY \"order_id\")"; statement = conn.prepareStatement(query); rs = statement.executeQuery(); @@ -636,8 +649,8 @@ public class SubqueryIT extends BaseJoinIT { assertFalse(rs.next()); - query = "SELECT \"order_id\" FROM " + getTableName(conn,JOIN_ORDER_TABLE_FULL_NAME) + - " o WHERE quantity = (SELECT max(quantity) FROM " + getTableName(conn,JOIN_ORDER_TABLE_FULL_NAME) + + query = "SELECT \"order_id\" FROM " + tableName4 + + " o WHERE quantity = (SELECT max(quantity) FROM " + tableName4 + " WHERE o.\"item_id\" = \"item_id\" AND \"order_id\" != '000000000000003' GROUP BY \"order_id\")"; statement = conn.prepareStatement(query); rs = statement.executeQuery(); @@ -655,8 +668,10 @@ public class SubqueryIT extends BaseJoinIT { public void testAnyAllComparisonSubquery() throws Exception { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); + String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME); + String tableName4 = getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME); try { - String query = "SELECT \"order_id\", name FROM " + getTableName(conn,JOIN_ORDER_TABLE_FULL_NAME) + " o JOIN " + getTableName(conn,JOIN_ITEM_TABLE_FULL_NAME) + " i ON o.\"item_id\" = i.\"item_id\" WHERE quantity = ALL(SELECT quantity FROM " + getTableName(conn,JOIN_ORDER_TABLE_FULL_NAME) + " q WHERE o.\"item_id\" = q.\"item_id\")"; + String query = "SELECT \"order_id\", name FROM " + tableName4 + " o JOIN " + tableName1 + " i ON o.\"item_id\" = i.\"item_id\" WHERE quantity = ALL(SELECT quantity FROM " + tableName4 + " q WHERE o.\"item_id\" = q.\"item_id\")"; PreparedStatement statement = conn.prepareStatement(query); ResultSet rs = statement.executeQuery(); assertTrue (rs.next()); @@ -671,7 +686,7 @@ public class SubqueryIT extends BaseJoinIT { assertFalse(rs.next()); - query = "SELECT \"order_id\", name FROM " + getTableName(conn,JOIN_ORDER_TABLE_FULL_NAME) + " o JOIN " + getTableName(conn,JOIN_ITEM_TABLE_FULL_NAME) + " i ON o.\"item_id\" = i.\"item_id\" WHERE quantity != ALL(SELECT max(quantity) FROM " + getTableName(conn,JOIN_ORDER_TABLE_FULL_NAME) + " q WHERE o.\"item_id\" = q.\"item_id\")"; + query = "SELECT \"order_id\", name FROM " + tableName4 + " o JOIN " + tableName1 + " i ON o.\"item_id\" = i.\"item_id\" WHERE quantity != ALL(SELECT max(quantity) FROM " + tableName4 + " q WHERE o.\"item_id\" = q.\"item_id\")"; statement = conn.prepareStatement(query); rs = statement.executeQuery(); assertTrue (rs.next()); @@ -680,7 +695,7 @@ public class SubqueryIT extends BaseJoinIT { assertFalse(rs.next()); - query = "SELECT \"order_id\", name FROM " + getTableName(conn,JOIN_ORDER_TABLE_FULL_NAME) + " o JOIN " + getTableName(conn,JOIN_ITEM_TABLE_FULL_NAME) + " i ON o.\"item_id\" = i.\"item_id\" WHERE quantity != ANY(SELECT quantity FROM " + getTableName(conn,JOIN_ORDER_TABLE_FULL_NAME) + " q WHERE o.\"item_id\" = q.\"item_id\" GROUP BY quantity)"; + query = "SELECT \"order_id\", name FROM " + tableName4 + " o JOIN " + tableName1 + " i ON o.\"item_id\" = i.\"item_id\" WHERE quantity != ANY(SELECT quantity FROM " + tableName4 + " q WHERE o.\"item_id\" = q.\"item_id\" GROUP BY quantity)"; statement = conn.prepareStatement(query); rs = statement.executeQuery(); assertTrue (rs.next()); @@ -702,13 +717,15 @@ public class SubqueryIT extends BaseJoinIT { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); conn.setAutoCommit(true); + String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME); + String tableName4 = getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME); try { conn.createStatement().execute("CREATE TABLE " + tempTable + " (item_id varchar not null primary key, " + " name varchar)"); conn.createStatement().execute("UPSERT INTO " + tempTable + "(item_id, name)" - + " SELECT \"item_id\", name FROM " + getTableName(conn,JOIN_ITEM_TABLE_FULL_NAME) - + " WHERE \"item_id\" NOT IN (SELECT \"item_id\" FROM " + getTableName(conn,JOIN_ORDER_TABLE_FULL_NAME) + ")"); + + " SELECT \"item_id\", name FROM " + tableName1 + + " WHERE \"item_id\" NOT IN (SELECT \"item_id\" FROM " + tableName4 + ")"); String query = "SELECT name FROM " + tempTable + " ORDER BY item_id"; PreparedStatement statement = conn.prepareStatement(query); @@ -732,14 +749,16 @@ public class SubqueryIT extends BaseJoinIT { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); conn.setAutoCommit(true); + String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME); + String tableName4 = getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME); try { conn.createStatement().execute("CREATE TABLE " + tempTable + " (item_id varchar not null primary key, " + " name varchar)"); conn.createStatement().execute("UPSERT INTO " + tempTable + "(item_id, name)" - + " SELECT \"item_id\", name FROM " + getTableName(conn,JOIN_ITEM_TABLE_FULL_NAME)); + + " SELECT \"item_id\", name FROM " + tableName1); - String query = "SELECT count(*) FROM " + getTableName(conn,JOIN_ITEM_TABLE_FULL_NAME); + String query = "SELECT count(*) FROM " + tableName1; PreparedStatement statement = conn.prepareStatement(query); ResultSet rs = statement.executeQuery(); assertTrue (rs.next()); @@ -747,7 +766,7 @@ public class SubqueryIT extends BaseJoinIT { assertFalse(rs.next()); conn.createStatement().execute("DELETE FROM " + tempTable + " WHERE item_id IN (" - + " SELECT \"item_id\" FROM " + getTableName(conn,JOIN_ORDER_TABLE_FULL_NAME) + ")"); + + " SELECT \"item_id\" FROM " + tableName4 + ")"); query = "SELECT name FROM " + tempTable + " ORDER BY item_id"; statement = conn.prepareStatement(query);
http://git-wip-us.apache.org/repos/asf/phoenix/blob/153612aa/phoenix-core/src/it/java/org/apache/phoenix/end2end/SubqueryUsingSortMergeJoinIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/SubqueryUsingSortMergeJoinIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SubqueryUsingSortMergeJoinIT.java index b5e1c7e..e93052c 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/SubqueryUsingSortMergeJoinIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SubqueryUsingSortMergeJoinIT.java @@ -245,8 +245,12 @@ public class SubqueryUsingSortMergeJoinIT extends BaseJoinIT { public void testInSubquery() throws Exception { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); + String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME); + String tableName2 = getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME); + String tableName4 = getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME); + String tableName5 = getTableName(conn, JOIN_COITEM_TABLE_FULL_NAME); try { - String query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"item_id\", name FROM " + getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + " WHERE \"item_id\" IN (SELECT \"item_id\" FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + ") ORDER BY name"; + String query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"item_id\", name FROM " + tableName1 + " WHERE \"item_id\" IN (SELECT \"item_id\" FROM " + tableName4 + ") ORDER BY name"; PreparedStatement statement = conn.prepareStatement(query); ResultSet rs = statement.executeQuery(); assertTrue (rs.next()); @@ -264,7 +268,7 @@ public class SubqueryUsingSortMergeJoinIT extends BaseJoinIT { assertFalse(rs.next()); - query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"item_id\", name FROM " + getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + " WHERE \"item_id\" NOT IN (SELECT \"item_id\" FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + ") ORDER BY name"; + query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"item_id\", name FROM " + tableName1 + " WHERE \"item_id\" NOT IN (SELECT \"item_id\" FROM " + tableName4 + ") ORDER BY name"; statement = conn.prepareStatement(query); rs = statement.executeQuery(); assertTrue (rs.next()); @@ -279,7 +283,7 @@ public class SubqueryUsingSortMergeJoinIT extends BaseJoinIT { assertFalse(rs.next()); - query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ i.\"item_id\", s.name FROM " + getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + " i JOIN " + getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME) + " s ON i.\"supplier_id\" = s.\"supplier_id\" WHERE i.\"item_id\" IN (SELECT \"item_id\" FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " WHERE \"order_id\" > '000000000000001') ORDER BY i.name"; + query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ i.\"item_id\", s.name FROM " + tableName1 + " i JOIN " + tableName2 + " s ON i.\"supplier_id\" = s.\"supplier_id\" WHERE i.\"item_id\" IN (SELECT \"item_id\" FROM " + tableName4 + " WHERE \"order_id\" > '000000000000001') ORDER BY i.name"; statement = conn.prepareStatement(query); rs = statement.executeQuery(); assertTrue (rs.next()); @@ -297,7 +301,7 @@ public class SubqueryUsingSortMergeJoinIT extends BaseJoinIT { rs = conn.createStatement().executeQuery("EXPLAIN " + query); assertPlansEqual(plans[0], QueryUtil.getExplainPlan(rs)); - query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ i.\"item_id\", s.name FROM " + getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME) + " s LEFT JOIN " + getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + " i ON i.\"supplier_id\" = s.\"supplier_id\" WHERE i.\"item_id\" IN (SELECT \"item_id\" FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + ") ORDER BY i.name"; + query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ i.\"item_id\", s.name FROM " + tableName2 + " s LEFT JOIN " + tableName1 + " i ON i.\"supplier_id\" = s.\"supplier_id\" WHERE i.\"item_id\" IN (SELECT \"item_id\" FROM " + tableName4 + ") ORDER BY i.name"; statement = conn.prepareStatement(query); rs = statement.executeQuery(); assertTrue (rs.next()); @@ -315,8 +319,8 @@ public class SubqueryUsingSortMergeJoinIT extends BaseJoinIT { assertFalse(rs.next()); - query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ * FROM " + getTableName(conn, JOIN_COITEM_TABLE_FULL_NAME) + " WHERE (item_id, item_name) IN (SELECT \"item_id\", name FROM " + getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + " WHERE \"item_id\" NOT IN (SELECT \"item_id\" FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + "))" - + " OR (co_item_id, co_item_name) IN (SELECT \"item_id\", name FROM " + getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + " WHERE \"item_id\" IN (SELECT \"item_id\" FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + "))"; + query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ * FROM " + tableName5 + " WHERE (item_id, item_name) IN (SELECT \"item_id\", name FROM " + tableName1 + " WHERE \"item_id\" NOT IN (SELECT \"item_id\" FROM " + tableName4 + "))" + + " OR (co_item_id, co_item_name) IN (SELECT \"item_id\", name FROM " + tableName1 + " WHERE \"item_id\" IN (SELECT \"item_id\" FROM " + tableName4 + "))"; statement = conn.prepareStatement(query); rs = statement.executeQuery(); assertTrue (rs.next()); @@ -344,8 +348,11 @@ public class SubqueryUsingSortMergeJoinIT extends BaseJoinIT { public void testExistsSubquery() throws Exception { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); + String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME); + String tableName4 = getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME); + String tableName5 = getTableName(conn, JOIN_COITEM_TABLE_FULL_NAME); try { - String query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"item_id\", name FROM " + getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + " i WHERE NOT EXISTS (SELECT 1 FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " o WHERE o.\"item_id\" = i.\"item_id\") ORDER BY name"; + String query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"item_id\", name FROM " + tableName1 + " i WHERE NOT EXISTS (SELECT 1 FROM " + tableName4 + " o WHERE o.\"item_id\" = i.\"item_id\") ORDER BY name"; PreparedStatement statement = conn.prepareStatement(query); ResultSet rs = statement.executeQuery(); assertTrue (rs.next()); @@ -360,8 +367,8 @@ public class SubqueryUsingSortMergeJoinIT extends BaseJoinIT { assertFalse(rs.next()); - query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ * FROM " + getTableName(conn, JOIN_COITEM_TABLE_FULL_NAME) + " co WHERE EXISTS (SELECT 1 FROM " + getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + " i WHERE NOT EXISTS (SELECT 1 FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " WHERE \"item_id\" = i.\"item_id\") AND co.item_id = \"item_id\" AND name = co.item_name)" - + " OR EXISTS (SELECT 1 FROM " + getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + " WHERE \"item_id\" IN (SELECT \"item_id\" FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + ") AND co.co_item_id = \"item_id\" AND name = co.co_item_name)"; + query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ * FROM " + tableName5 + " co WHERE EXISTS (SELECT 1 FROM " + tableName1 + " i WHERE NOT EXISTS (SELECT 1 FROM " + tableName4 + " WHERE \"item_id\" = i.\"item_id\") AND co.item_id = \"item_id\" AND name = co.item_name)" + + " OR EXISTS (SELECT 1 FROM " + tableName1 + " WHERE \"item_id\" IN (SELECT \"item_id\" FROM " + tableName4 + ") AND co.co_item_id = \"item_id\" AND name = co.co_item_name)"; statement = conn.prepareStatement(query); rs = statement.executeQuery(); assertTrue (rs.next()); @@ -389,8 +396,11 @@ public class SubqueryUsingSortMergeJoinIT extends BaseJoinIT { public void testComparisonSubquery() throws Exception { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); + String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME); + String tableName3 = getTableName(conn, JOIN_CUSTOMER_TABLE_FULL_NAME); + String tableName4 = getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME); try { - String query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", name FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " o JOIN " + getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + " i ON o.\"item_id\" = i.\"item_id\" WHERE quantity = (SELECT max(quantity) FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " q WHERE o.\"item_id\" = q.\"item_id\")"; + String query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", name FROM " + tableName4 + " o JOIN " + tableName1 + " i ON o.\"item_id\" = i.\"item_id\" WHERE quantity = (SELECT max(quantity) FROM " + tableName4 + " q WHERE o.\"item_id\" = q.\"item_id\")"; PreparedStatement statement = conn.prepareStatement(query); ResultSet rs = statement.executeQuery(); assertTrue (rs.next()); @@ -408,7 +418,7 @@ public class SubqueryUsingSortMergeJoinIT extends BaseJoinIT { assertFalse(rs.next()); - query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ name from " + getTableName(conn, JOIN_CUSTOMER_TABLE_FULL_NAME) + " WHERE \"customer_id\" IN (SELECT \"customer_id\" FROM " + getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + " i JOIN " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " o ON o.\"item_id\" = i.\"item_id\" WHERE i.name = 'T2' OR quantity > (SELECT avg(quantity) FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " q WHERE o.\"item_id\" = q.\"item_id\"))"; + query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ name from " + tableName3 + " WHERE \"customer_id\" IN (SELECT \"customer_id\" FROM " + tableName1 + " i JOIN " + tableName4 + " o ON o.\"item_id\" = i.\"item_id\" WHERE i.name = 'T2' OR quantity > (SELECT avg(quantity) FROM " + tableName4 + " q WHERE o.\"item_id\" = q.\"item_id\"))"; statement = conn.prepareStatement(query); rs = statement.executeQuery(); assertTrue (rs.next()); @@ -422,7 +432,7 @@ public class SubqueryUsingSortMergeJoinIT extends BaseJoinIT { String plan = QueryUtil.getExplainPlan(rs); assertPlansMatch(plans[2], plan); - query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\" FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " o WHERE quantity = (SELECT quantity FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " WHERE o.\"item_id\" = \"item_id\" AND \"order_id\" != '000000000000004')"; + query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\" FROM " + tableName4 + " o WHERE quantity = (SELECT quantity FROM " + tableName4 + " WHERE o.\"item_id\" = \"item_id\" AND \"order_id\" != '000000000000004')"; statement = conn.prepareStatement(query); rs = statement.executeQuery(); assertTrue (rs.next()); @@ -436,7 +446,7 @@ public class SubqueryUsingSortMergeJoinIT extends BaseJoinIT { assertFalse(rs.next()); - query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\" FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " o WHERE quantity = (SELECT quantity FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " WHERE o.\"item_id\" = \"item_id\" AND \"order_id\" != '000000000000003')"; + query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\" FROM " + tableName4 + " o WHERE quantity = (SELECT quantity FROM " + tableName4 + " WHERE o.\"item_id\" = \"item_id\" AND \"order_id\" != '000000000000003')"; statement = conn.prepareStatement(query); rs = statement.executeQuery(); try { @@ -445,7 +455,7 @@ public class SubqueryUsingSortMergeJoinIT extends BaseJoinIT { } catch (SQLException e) { } - query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\" FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " o WHERE quantity = (SELECT max(quantity) FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " WHERE o.\"item_id\" = \"item_id\" AND \"order_id\" != '000000000000004' GROUP BY \"order_id\")"; + query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\" FROM " + tableName4 + " o WHERE quantity = (SELECT max(quantity) FROM " + tableName4 + " WHERE o.\"item_id\" = \"item_id\" AND \"order_id\" != '000000000000004' GROUP BY \"order_id\")"; statement = conn.prepareStatement(query); rs = statement.executeQuery(); assertTrue (rs.next()); @@ -459,7 +469,7 @@ public class SubqueryUsingSortMergeJoinIT extends BaseJoinIT { assertFalse(rs.next()); - query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\" FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " o WHERE quantity = (SELECT max(quantity) FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " WHERE o.\"item_id\" = \"item_id\" AND \"order_id\" != '000000000000003' GROUP BY \"order_id\")"; + query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\" FROM " + tableName4 + " o WHERE quantity = (SELECT max(quantity) FROM " + tableName4 + " WHERE o.\"item_id\" = \"item_id\" AND \"order_id\" != '000000000000003' GROUP BY \"order_id\")"; statement = conn.prepareStatement(query); rs = statement.executeQuery(); try { @@ -476,8 +486,10 @@ public class SubqueryUsingSortMergeJoinIT extends BaseJoinIT { public void testAnyAllComparisonSubquery() throws Exception { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); + String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME); + String tableName4 = getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME); try { - String query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", name FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " o JOIN " + getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + " i ON o.\"item_id\" = i.\"item_id\" WHERE quantity = ALL(SELECT quantity FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " q WHERE o.\"item_id\" = q.\"item_id\")"; + String query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", name FROM " + tableName4 + " o JOIN " + tableName1 + " i ON o.\"item_id\" = i.\"item_id\" WHERE quantity = ALL(SELECT quantity FROM " + tableName4 + " q WHERE o.\"item_id\" = q.\"item_id\")"; PreparedStatement statement = conn.prepareStatement(query); ResultSet rs = statement.executeQuery(); assertTrue (rs.next()); @@ -492,7 +504,7 @@ public class SubqueryUsingSortMergeJoinIT extends BaseJoinIT { assertFalse(rs.next()); - query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", name FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " o JOIN " + getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + " i ON o.\"item_id\" = i.\"item_id\" WHERE quantity != ALL(SELECT max(quantity) FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " q WHERE o.\"item_id\" = q.\"item_id\")"; + query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", name FROM " + tableName4 + " o JOIN " + tableName1 + " i ON o.\"item_id\" = i.\"item_id\" WHERE quantity != ALL(SELECT max(quantity) FROM " + tableName4 + " q WHERE o.\"item_id\" = q.\"item_id\")"; statement = conn.prepareStatement(query); rs = statement.executeQuery(); assertTrue (rs.next()); @@ -501,7 +513,7 @@ public class SubqueryUsingSortMergeJoinIT extends BaseJoinIT { assertFalse(rs.next()); - query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", name FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " o JOIN " + getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + " i ON o.\"item_id\" = i.\"item_id\" WHERE quantity != ANY(SELECT quantity FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " q WHERE o.\"item_id\" = q.\"item_id\" GROUP BY quantity)"; + query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", name FROM " + tableName4 + " o JOIN " + tableName1 + " i ON o.\"item_id\" = i.\"item_id\" WHERE quantity != ANY(SELECT quantity FROM " + tableName4 + " q WHERE o.\"item_id\" = q.\"item_id\" GROUP BY quantity)"; statement = conn.prepareStatement(query); rs = statement.executeQuery(); assertTrue (rs.next()); @@ -523,13 +535,15 @@ public class SubqueryUsingSortMergeJoinIT extends BaseJoinIT { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); conn.setAutoCommit(true); + String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME); + String tableName4 = getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME); try { conn.createStatement().execute("CREATE TABLE " + tempTable + " (item_id varchar not null primary key, " + " name varchar)"); conn.createStatement().execute("UPSERT /*+ USE_SORT_MERGE_JOIN*/ INTO " + tempTable + "(item_id, name)" - + " SELECT \"item_id\", name FROM " + getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) - + " WHERE \"item_id\" NOT IN (SELECT \"item_id\" FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + ")"); + + " SELECT \"item_id\", name FROM " + tableName1 + + " WHERE \"item_id\" NOT IN (SELECT \"item_id\" FROM " + tableName4 + ")"); String query = "SELECT name FROM " + tempTable + " ORDER BY item_id"; PreparedStatement statement = conn.prepareStatement(query);
