@Test
public void testSchemaCompletion() throws SqlParseException {

    String sql1 = "SELECT a \n" + "FROM a.KYLIN_SALES as KYLIN_SALES\n"
            + "INNER JOIN \"A\".KYLIN_ACCOUNT as BUYER_ACCOUNT\n"
            + "ON KYLIN_SALES.BUYER_ID = BUYER_ACCOUNT.ACCOUNT_ID\n"
            + "INNER JOIN \"KYLIN_COUNTRY\" as BUYER_COUNTRY\n"
            + "ON BUYER_ACCOUNT.ACCOUNT_COUNTRY = BUYER_COUNTRY.COUNTRY LIMIT 5";
    String sql2 = "select * from DB2.t,DB2.tt,ttt";
    String sql3 = "SELECT t1.week_beg_dt, t1.sum_price, t2.cnt\n" + "FROM (\n"
            + "  select test_cal_dt.week_beg_dt, sum(price) as sum_price\n" + "  from DB1.\"test_kylin_fact\"\n"
            + "  inner JOIN test_cal_dt as test_cal_dt\n" + "  ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt\n"
            + "  inner JOIN test_category_groupings\n"
            + "  ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id\n"
            + "  inner JOIN test_sites as test_sites\n" + "  ON test_kylin_fact.lstg_site_id = test_sites.site_id\n"
            + "  where price > 100\n" + "  group by test_cal_dt.week_beg_dt\n" + "  having sum(price) > 1000\n"
            + "  order by sum(price)\n" + ") t1\n" + "inner join  (\n"
            + "  select test_cal_dt.week_beg_dt, count(*) as cnt\n" + "  from DB1.test_kylin_fact\n"
            + "  inner JOIN test_cal_dt as test_cal_dt\n" + "  ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt\n"
            + "  inner JOIN test_category_groupings\n"
            + "  ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id\n"
            + "  inner JOIN test_sites as test_sites\n" + "  ON test_kylin_fact.lstg_site_id = test_sites.site_id\n"
            + "  group by test_cal_dt.week_beg_dt\n" + ") t2\n" + "on t1.week_beg_dt=t2.week_beg_dt limit 5";
    String exceptSQL1 = "SELECT a \n" + "FROM a.KYLIN_SALES as KYLIN_SALES\n"
            + "INNER JOIN \"A\".KYLIN_ACCOUNT as BUYER_ACCOUNT\n"
            + "ON KYLIN_SALES.BUYER_ID = BUYER_ACCOUNT.ACCOUNT_ID\n"
            + "INNER JOIN EDW.\"KYLIN_COUNTRY\" as BUYER_COUNTRY\n"
            + "ON BUYER_ACCOUNT.ACCOUNT_COUNTRY = BUYER_COUNTRY.COUNTRY LIMIT 5";
    String exceptSQL2 = "select * from DB2.t,DB2.tt,EDW.ttt";
    String exceptSQL3 = "SELECT t1.week_beg_dt, t1.sum_price, t2.cnt\n" + "FROM (\n"
            + "  select test_cal_dt.week_beg_dt, sum(price) as sum_price\n" + "  from DB1.\"test_kylin_fact\"\n"
            + "  inner JOIN EDW.test_cal_dt as test_cal_dt\n" + "  ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt\n"
            + "  inner JOIN EDW.test_category_groupings\n"
            + "  ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id\n"
            + "  inner JOIN EDW.test_sites as test_sites\n"
            + "  ON test_kylin_fact.lstg_site_id = test_sites.site_id\n" + "  where price > 100\n"
            + "  group by test_cal_dt.week_beg_dt\n" + "  having sum(price) > 1000\n" + "  order by sum(price)\n"
            + ") t1\n" + "inner join  (\n" + "  select test_cal_dt.week_beg_dt, count(*) as cnt\n"
            + "  from DB1.test_kylin_fact\n" + "  inner JOIN EDW.test_cal_dt as test_cal_dt\n"
            + "  ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt\n" + "  inner JOIN EDW.test_category_groupings\n"
            + "  ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id\n"
            + "  inner JOIN EDW.test_sites as test_sites\n"
            + "  ON test_kylin_fact.lstg_site_id = test_sites.site_id\n" + "  group by test_cal_dt.week_beg_dt\n"
            + ") t2\n" + "on t1.week_beg_dt=t2.week_beg_dt limit 5";
    Assert.assertEquals(exceptSQL1, PushDownUtil.schemaCompletion(sql1, "EDW"));
    Assert.assertEquals(exceptSQL2, PushDownUtil.schemaCompletion(sql2, "EDW"));
    Assert.assertEquals(exceptSQL3, PushDownUtil.schemaCompletion(sql3, "EDW"));
}