[
https://issues.apache.org/jira/browse/PHOENIX-927?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13967270#comment-13967270
]
ASF GitHub Bot commented on PHOENIX-927:
----------------------------------------
Github user JamesRTaylor commented on a diff in the pull request:
https://github.com/apache/incubator-phoenix/pull/28#discussion_r11556633
--- Diff:
phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java ---
@@ -307,6 +307,103 @@ public void initTable() throws Exception {
" PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
" BUILD HASH TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN
OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME,
+ /*
+ * testJoinWithSubqueryAndAggregation()
+ * SELECT i.name, sum(quantity) FROM joinOrderTable o
+ * LEFT JOIN (SELECT name, item_id iid FROM
joinItemTable) AS i
+ * ON o.item_id = i.iid
+ * GROUP BY i.name ORDER BY i.name
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " +
JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [I.NAME]\n" +
+ "CLIENT MERGE SORT\n" +
+ "CLIENT SORTED BY [I.NAME]\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " +
JOIN_ITEM_TABLE_DISPLAY_NAME,
+ /*
+ * testJoinWithSubqueryAndAggregation()
+ * SELECT o.iid, sum(o.quantity) q
+ * FROM (SELECT item_id iid, quantity FROM
joinOrderTable) AS o
+ * LEFT JOIN (SELECT item_id FROM joinItemTable) AS i
+ * ON o.iid = i.item_id
+ * GROUP BY o.iid ORDER BY q DESC
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " +
JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [O.IID]\n" +
+ "CLIENT MERGE SORT\n" +
+ "CLIENT SORTED BY [SUM(O.QUANTITY) DESC]\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0 (SKIP MERGE)\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " +
JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY FIRST KEY ONLY",
+ /*
+ * testJoinWithSubqueryAndAggregation()
+ * SELECT i.iid, o.q
+ * FROM (SELECT item_id iid FROM joinItemTable) AS i
+ * LEFT JOIN (SELECT item_id iid, sum(quantity) q FROM
joinOrderTable GROUP BY item_id) AS o
+ * ON o.iid = i.iid
+ * ORDER BY o.q DESC NULLS LAST, i.iid
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " +
JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " SERVER SORTED BY [O.Q DESC NULLS LAST, I.IID]\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " +
JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY
[item_id]\n" +
+ " CLIENT MERGE SORT",
+ /*
+ * testJoinWithSubqueryAndAggregation()
+ * SELECT i.iid, o.q
+ * FROM (SELECT item_id iid, sum(quantity) q FROM
joinOrderTable GROUP BY item_id) AS o
+ * JOIN (SELECT item_id iid FROM joinItemTable) AS i
+ * ON o.iid = i.iid
+ * ORDER BY o.q DESC, i.iid
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " +
JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " SERVER SORTED BY [O.Q DESC, I.IID]\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " +
JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY
[item_id]\n" +
+ " CLIENT MERGE SORT",
+ /*
+ * testNestedSubqueries()
--- End diff --
Wow, this is impressive!
> 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)
