[
https://issues.apache.org/jira/browse/PHOENIX-927?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13967302#comment-13967302
]
ASF GitHub Bot commented on PHOENIX-927:
----------------------------------------
Github user maryannxue commented on a diff in the pull request:
https://github.com/apache/incubator-phoenix/pull/28#discussion_r11557080
--- Diff:
phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java ---
@@ -2529,6 +2724,300 @@ public void testSubJoin() throws Exception {
conn.close();
}
}
+
+ @Test
+ public void testJoinWithSubquery() throws Exception {
+ String query1 = "SELECT item.\"item_id\", item.name, supp.sid,
supp.name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " item INNER JOIN (SELECT
\"supplier_id\" sid, name FROM " + JOIN_SUPPLIER_TABLE_FULL_NAME + " WHERE name
BETWEEN 'S1' AND 'S5') AS supp ON item.\"supplier_id\" = supp.sid";
+ String query2 = "SELECT item.\"item_id\", item.name,
supp.\"supplier_id\", supp.name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " item
INNER JOIN (SELECT \"supplier_id\", name FROM " + JOIN_SUPPLIER_TABLE_FULL_NAME
+ ") AS supp ON item.\"supplier_id\" = supp.\"supplier_id\" AND (supp.name =
'S1' OR supp.name = 'S5')";
+ Properties props = new Properties(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ try {
+ PreparedStatement statement = conn.prepareStatement(query1);
+ ResultSet rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000001");
+ assertEquals(rs.getString(2), "T1");
+ assertEquals(rs.getString(3), "0000000001");
+ assertEquals(rs.getString(4), "S1");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000002");
+ assertEquals(rs.getString(2), "T2");
+ assertEquals(rs.getString(3), "0000000001");
+ assertEquals(rs.getString(4), "S1");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000003");
+ assertEquals(rs.getString(2), "T3");
+ assertEquals(rs.getString(3), "0000000002");
+ assertEquals(rs.getString(4), "S2");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000004");
+ assertEquals(rs.getString(2), "T4");
+ assertEquals(rs.getString(3), "0000000002");
+ assertEquals(rs.getString(4), "S2");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000005");
+ assertEquals(rs.getString(2), "T5");
+ assertEquals(rs.getString(3), "0000000005");
+ assertEquals(rs.getString(4), "S5");
+
+ assertFalse(rs.next());
+
+
+ statement = conn.prepareStatement(query2);
+ rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000001");
+ assertEquals(rs.getString(2), "T1");
+ assertEquals(rs.getString(3), "0000000001");
+ assertEquals(rs.getString(4), "S1");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000002");
+ assertEquals(rs.getString(2), "T2");
+ assertEquals(rs.getString(3), "0000000001");
+ assertEquals(rs.getString(4), "S1");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000005");
+ assertEquals(rs.getString(2), "T5");
+ assertEquals(rs.getString(3), "0000000005");
+ assertEquals(rs.getString(4), "S5");
+
+ assertFalse(rs.next());
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testJoinWithSubqueryAndAggregation() throws Exception {
+ String query1 = "SELECT i.name, sum(quantity) FROM " +
JOIN_ORDER_TABLE_FULL_NAME + " o LEFT JOIN (SELECT name, \"item_id\" iid FROM "
+ + JOIN_ITEM_TABLE_FULL_NAME + ") AS i ON o.\"item_id\" = i.iid
GROUP BY i.name ORDER BY i.name";
+ String query2 = "SELECT o.iid, sum(o.quantity) q FROM (SELECT
\"item_id\" iid, quantity FROM " + JOIN_ORDER_TABLE_FULL_NAME + ") AS o LEFT
JOIN (SELECT \"item_id\" FROM "
+ + JOIN_ITEM_TABLE_FULL_NAME + ") AS i ON o.iid =
i.\"item_id\" GROUP BY o.iid ORDER BY q DESC";
+ String query3 = "SELECT i.iid, o.q FROM (SELECT \"item_id\" iid
FROM " + JOIN_ITEM_TABLE_FULL_NAME + ") AS i LEFT JOIN (SELECT \"item_id\" iid,
sum(quantity) q FROM "
+ + JOIN_ORDER_TABLE_FULL_NAME + " GROUP BY \"item_id\") AS
o ON o.iid = i.iid ORDER BY o.q DESC NULLS LAST, i.iid";
+ String query4 = "SELECT i.iid, o.q FROM (SELECT \"item_id\" iid,
sum(quantity) q FROM " + JOIN_ORDER_TABLE_FULL_NAME + " GROUP BY \"item_id\")
AS o JOIN (SELECT \"item_id\" iid FROM "
+ + JOIN_ITEM_TABLE_FULL_NAME + ") AS i ON o.iid = i.iid
ORDER BY o.q DESC, i.iid";
+ Properties props = new Properties(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ try {
+ PreparedStatement statement = conn.prepareStatement(query1);
+ ResultSet rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "T1");
+ assertEquals(rs.getInt(2), 1000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "T2");
+ assertEquals(rs.getInt(2), 3000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "T3");
+ assertEquals(rs.getInt(2), 5000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "T6");
+ assertEquals(rs.getInt(2), 6000);
+
+ assertFalse(rs.next());
+
+ rs = conn.createStatement().executeQuery("EXPLAIN " + query1);
+ assertEquals(plans[14], QueryUtil.getExplainPlan(rs));
+
+ statement = conn.prepareStatement(query2);
+ rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString("o.iid"), "0000000006");
+ assertEquals(rs.getInt("q"), 6000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("o.iid"), "0000000003");
+ assertEquals(rs.getInt("q"), 5000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("o.iid"), "0000000002");
+ assertEquals(rs.getInt("q"), 3000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("o.iid"), "0000000001");
+ assertEquals(rs.getInt("q"), 1000);
+
+ assertFalse(rs.next());
+
+ rs = conn.createStatement().executeQuery("EXPLAIN " + query2);
+ assertEquals(plans[15], QueryUtil.getExplainPlan(rs));
+
+ statement = conn.prepareStatement(query3);
+ rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString("i.iid"), "0000000006");
+ assertEquals(rs.getInt("o.q"), 6000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("i.iid"), "0000000003");
+ assertEquals(rs.getInt("o.q"), 5000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("i.iid"), "0000000002");
+ assertEquals(rs.getInt("o.q"), 3000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("i.iid"), "0000000001");
+ assertEquals(rs.getInt("o.q"), 1000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("i.iid"), "0000000004");
+ assertEquals(rs.getInt("o.q"), 0);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("i.iid"), "0000000005");
+ assertEquals(rs.getInt("o.q"), 0);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("i.iid"), "invalid001");
+ assertEquals(rs.getInt("o.q"), 0);
+
+ assertFalse(rs.next());
+
+ rs = conn.createStatement().executeQuery("EXPLAIN " + query3);
+ assertEquals(plans[16], QueryUtil.getExplainPlan(rs));
+
+ statement = conn.prepareStatement(query4);
+ rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString("i.iid"), "0000000006");
+ assertEquals(rs.getInt("o.q"), 6000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("i.iid"), "0000000003");
+ assertEquals(rs.getInt("o.q"), 5000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("i.iid"), "0000000002");
+ assertEquals(rs.getInt("o.q"), 3000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("i.iid"), "0000000001");
+ assertEquals(rs.getInt("o.q"), 1000);
+
+ assertFalse(rs.next());
+
+ rs = conn.createStatement().executeQuery("EXPLAIN " + query4);
+ assertEquals(plans[17], QueryUtil.getExplainPlan(rs));
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testNestedSubqueries() throws Exception {
+ String query1 = "SELECT q.iname, count(c.name), min(q.sname),
max(o.quantity) FROM (SELECT \"customer_id\" cid, \"item_id\" iid, quantity
FROM " + JOIN_ORDER_TABLE_FULL_NAME + ") AS o LEFT JOIN "
+ + "(SELECT i.iid iid, s.name sname, i.name iname FROM
(SELECT \"supplier_id\" sid, name FROM " + JOIN_SUPPLIER_TABLE_FULL_NAME + ")
AS s RIGHT JOIN (SELECT \"item_id\" iid, name, \"supplier_id\" sid FROM " +
JOIN_ITEM_TABLE_FULL_NAME + ") AS i ON i.sid = s.sid) AS q"
+ + " ON o.iid = q.iid LEFT JOIN (SELECT \"customer_id\"
cid, name FROM "
+ + JOIN_CUSTOMER_TABLE_FULL_NAME + ") AS c ON c.cid = o.cid
GROUP BY q.iname ORDER BY q.iname";
+ String query2 = "SELECT * FROM (SELECT \"customer_id\" cid, name,
phone, address, loc_id, date FROM " + JOIN_CUSTOMER_TABLE_FULL_NAME + ") AS c
INNER JOIN "
+ + "(SELECT o.oid ooid, o.cid ocid, o.iid oiid, o.price
oprice, o.quantity oquantity, o.date odate, qi.iiid iiid, qi.iname iname,
qi.iprice iprice, qi.idiscount1 idiscount1, qi.idiscount2 idiscount2, qi.isid
isid, qi.idescription idescription, qi.ssid ssid, qi.sname sname, qi.sphone
sphone, qi.saddress saddress, qi.sloc_id sloc_id FROM (SELECT \"item_id\" iid,
\"customer_id\" cid, \"order_id\" oid, price, quantity, date FROM " +
JOIN_ORDER_TABLE_FULL_NAME + ") AS o INNER JOIN "
+ + "(SELECT i.iid iiid, i.name iname, i.price iprice,
i.discount1 idiscount1, i.discount2 idiscount2, i.sid isid, i.description
idescription, s.sid ssid, s.name sname, s.phone sphone, s.address saddress,
s.loc_id sloc_id FROM (SELECT \"supplier_id\" sid, name, phone, address, loc_id
FROM " + JOIN_SUPPLIER_TABLE_FULL_NAME + ") AS s RIGHT JOIN (SELECT \"item_id\"
iid, name, price, discount1, discount2, \"supplier_id\" sid, description FROM "
+ JOIN_ITEM_TABLE_FULL_NAME + ") AS i ON i.sid = s.sid) as qi"
+ + " ON o.iid = qi.iiid) as qo ON c.cid = qo.ocid"
+ + " WHERE c.cid <= '0000000005' AND qo.ooid !=
'000000000000003' AND qo.iname != 'T3' ORDER BY c.cid, qo.iname";
+ Properties props = new Properties(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ try {
+ PreparedStatement statement = conn.prepareStatement(query1);
+ ResultSet rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "T1");
+ assertEquals(rs.getInt(2), 1);
+ assertEquals(rs.getString(3), "S1");
+ assertEquals(rs.getInt(4), 1000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "T2");
+ assertEquals(rs.getInt(2), 1);
+ assertEquals(rs.getString(3), "S1");
+ assertEquals(rs.getInt(4), 3000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "T3");
+ assertEquals(rs.getInt(2), 1);
+ assertEquals(rs.getString(3), "S2");
+ assertEquals(rs.getInt(4), 5000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "T6");
+ assertEquals(rs.getInt(2), 2);
+ assertEquals(rs.getString(3), "S6");
+ assertEquals(rs.getInt(4), 4000);
+
+ assertFalse(rs.next());
+
+ statement = conn.prepareStatement(query2);
+ rs = statement.executeQuery();
+ assertTrue(rs.next());
+ assertEquals(rs.getString("c.cid"), "0000000003");
+ assertEquals(rs.getString("c.name"), "C3");
+ assertEquals(rs.getString("c.phone"), "999-999-3333");
+ assertEquals(rs.getString("c.address"), "303 XXX Street");
+ assertNull(rs.getString("c.loc_id"));
+ assertEquals(rs.getDate("c.date"), new
Date(format.parse("2013-11-25 10:06:29").getTime()));
+ assertEquals(rs.getString("qo.ooid"), "000000000000002");
+ assertEquals(rs.getString("qo.ocid"), "0000000003");
+ assertEquals(rs.getString("qo.oiid"), "0000000006");
+ assertEquals(rs.getInt("qo.oprice"), 552);
+ assertEquals(rs.getInt("qo.oquantity"), 2000);
+ assertEquals(rs.getTimestamp("qo.odate"), new
Timestamp(format.parse("2013-11-25 10:06:29").getTime()));
+ assertEquals(rs.getString("qo.iiid"), "0000000006");
+ assertEquals(rs.getString("qo.iname"), "T6");
+ assertEquals(rs.getInt("qo.iprice"), 600);
+ assertEquals(rs.getInt("qo.idiscount1"), 8);
+ assertEquals(rs.getInt("qo.idiscount2"), 15);
+ assertEquals(rs.getString("qo.isid"), "0000000006");
+ assertEquals(rs.getString("qo.idescription"), "Item T6");
+ assertEquals(rs.getString("qo.ssid"), "0000000006");
+ assertEquals(rs.getString("qo.sname"), "S6");
+ assertEquals(rs.getString("qo.sphone"), "888-888-6666");
+ assertEquals(rs.getString("qo.saddress"), "606 YYY Street");
+ assertEquals(rs.getString("qo.sloc_id"), "10006");
+ assertTrue(rs.next());
+ assertEquals(rs.getString("c.cid"), "0000000004");
+ assertEquals(rs.getString("c.name"), "C4");
+ assertEquals(rs.getString("c.phone"), "999-999-4444");
+ assertEquals(rs.getString("c.address"), "404 XXX Street");
+ assertEquals(rs.getString("c.loc_id"), "10004");
+ assertEquals(rs.getDate("c.date"), new
Date(format.parse("2013-11-22 14:22:56").getTime()));
+ assertEquals(rs.getString("qo.ooid"), "000000000000001");
+ assertEquals(rs.getString("qo.ocid"), "0000000004");
+ assertEquals(rs.getString("qo.oiid"), "0000000001");
+ assertEquals(rs.getInt("qo.oprice"), 100);
+ assertEquals(rs.getInt("qo.oquantity"), 1000);
+ assertEquals(rs.getTimestamp("qo.odate"), new
Timestamp(format.parse("2013-11-22 14:22:56").getTime()));
+ assertEquals(rs.getString("qo.iiid"), "0000000001");
+ assertEquals(rs.getString("qo.iname"), "T1");
+ assertEquals(rs.getInt("qo.iprice"), 100);
+ assertEquals(rs.getInt("qo.idiscount1"), 5);
+ assertEquals(rs.getInt("qo.idiscount2"), 10);
+ assertEquals(rs.getString("qo.isid"), "0000000001");
+ assertEquals(rs.getString("qo.idescription"), "Item T1");
+ assertEquals(rs.getString("qo.ssid"), "0000000001");
+ assertEquals(rs.getString("qo.sname"), "S1");
+ assertEquals(rs.getString("qo.sphone"), "888-888-1111");
+ assertEquals(rs.getString("qo.saddress"), "101 YYY Street");
+ assertEquals(rs.getString("qo.sloc_id"), "10001");
+ assertTrue(rs.next());
+ assertEquals(rs.getString("c.cid"), "0000000004");
+ assertEquals(rs.getString("c.name"), "C4");
+ assertEquals(rs.getString("c.phone"), "999-999-4444");
+ assertEquals(rs.getString("c.address"), "404 XXX Street");
+ assertEquals(rs.getString("c.loc_id"), "10004");
+ assertEquals(rs.getDate("c.date"), new
Date(format.parse("2013-11-22 14:22:56").getTime()));
+ assertEquals(rs.getString("qo.ooid"), "000000000000004");
+ assertEquals(rs.getString("qo.ocid"), "0000000004");
+ assertEquals(rs.getString("qo.oiid"), "0000000006");
+ assertEquals(rs.getInt("qo.oprice"), 510);
+ assertEquals(rs.getInt("qo.oquantity"), 4000);
+ assertEquals(rs.getTimestamp("qo.odate"), new
Timestamp(format.parse("2013-11-26 13:26:04").getTime()));
+ assertEquals(rs.getString("qo.iiid"), "0000000006");
+ assertEquals(rs.getString("qo.iname"), "T6");
+ assertEquals(rs.getInt("qo.iprice"), 600);
+ assertEquals(rs.getInt("qo.idiscount1"), 8);
+ assertEquals(rs.getInt("qo.idiscount2"), 15);
+ assertEquals(rs.getString("qo.isid"), "0000000006");
+ assertEquals(rs.getString("qo.idescription"), "Item T6");
+ assertEquals(rs.getString("qo.ssid"), "0000000006");
+ assertEquals(rs.getString("qo.sname"), "S6");
+ assertEquals(rs.getString("qo.sphone"), "888-888-6666");
+ assertEquals(rs.getString("qo.saddress"), "606 YYY Street");
+ assertEquals(rs.getString("qo.sloc_id"), "10006");
+
+ assertFalse(rs.next());
+
+ rs = conn.createStatement().executeQuery("EXPLAIN " + query2);
+ assertEquals(plans[18], QueryUtil.getExplainPlan(rs));
+ } finally {
+ conn.close();
+ }
+ }
--- End diff --
Yes. Subqueries also get re-written for using index. And with the second
query in testNestedSubqueries(), you can see that filters in the main query
also get pushed down into inner queries.
> Support derived tables in joins
> -------------------------------
>
> Key: PHOENIX-927
> URL: https://issues.apache.org/jira/browse/PHOENIX-927
> Project: Phoenix
> Issue Type: Sub-task
> Reporter: Maryann Xue
> Assignee: Maryann Xue
> Labels: enhancement
> Fix For: 3.0.0, 4.0.0, 5.0.0
>
> Original Estimate: 240h
> Remaining Estimate: 240h
>
> Support grammar like:
> SELECT a.col1, b.col2, c.col3 FROM
> (SELECT rk, col1 FROM table1 WHERE col1 LIKE 'foo%' AND col300 IS NULL) AS a
> JOIN (SELECT rk, col2 FROM table2 WHERE col2 LIKE 'bar%') AS b ON a.rk=b.rk
> JOIN (SELECT rk, col3 FROM table3 ) AS c ON a.rk=c.rk;
--
This message was sent by Atlassian JIRA
(v6.2#6252)
