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:42:14 GMT

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

Mime
View raw message