phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "ASF GitHub Bot (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (PHOENIX-927) Support derived tables in joins
Date Sat, 12 Apr 2014 00:10:15 GMT

    [ 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)

Mime
View raw message