@Test
  public void testSubQueryIn() throws ParseException {
    // from, having
    parse("select *\n" +
        "\n" +
        "from (select b.key, count(*) \n" +
        "\n" +
        "  from `src/_/cbo` b \n" +
        "\n" +
        "  group by b.key\n" +
        "\n" +
        "  having exists \n" +
        "\n" +
        "    (select a.key \n" +
        "\n" +
        "    from `src/_/cbo` a \n" +
        "\n" +
        "    where a.key = b.key and a.value > 'val_9'\n" +
        "\n" +
        "    )\n" +
        "\n" +
        ") a\n" +
        "\n");

    // non agg, non corr
    parse("select * \n" +
        "\n" +
        "from `src/_/cbo` \n" +
        "\n" +
        "where `src/_/cbo`.key in (select key from `src/_/cbo` s1 where s1.key > '9') order by key\n" +
        "\n");

    // distinct, corr
    parse("select * \n" +
        "\n" +
        "from `src/_/cbo` b \n" +
        "\n" +
        "where b.key in\n" +
        "\n" +
        "        (select distinct a.key \n" +
        "\n" +
        "         from `src/_/cbo` a \n" +
        "\n" +
        "         where b.value = a.value and a.key > '9'\n" +
        "\n" +
        "        ) order by b.key\n" +
        "\n");

    // non agg, corr, with join in Parent Query
    parse("select p.p_partkey, li.l_suppkey \n" +
        "\n" +
        "from (select distinct l_partkey as p_partkey from `line/item`) p join `line/item` li on p.p_partkey = li.l_partkey \n" +
        "\n" +
        "where li.l_linenumber = 1 and\n" +
        "\n" +
        " li.l_orderkey in (select l_orderkey from `line/item` where l_shipmode = 'AIR' and l_linenumber = li.l_linenumber)\n" +
        "\n" +
        " order by p.p_partkey\n" +
        "\n");

    // where and having
    parse("select key, value, count(*) \n" +
        "\n" +
        "from `src/_/cbo` b\n" +
        "\n" +
        "where b.key in (select key from `src/_/cbo` where `src/_/cbo`.key > '8')\n" +
        "\n" +
        "group by key, value\n" +
        "\n" +
        "having count(*) in (select count(*) from `src/_/cbo` s1 where s1.key > '9' group by s1.key ) order by key\n" +
        "\n");

    // non agg, non corr, windowing
    parse("select p_mfgr, p_name, avg(p_size) \n" +
        "\n" +
        "from `p/a/r/t` \n" +
        "\n" +
        "group by p_mfgr, p_name\n" +
        "\n" +
        "having p_name in \n" +
        "\n" +
        "  (select first_value(p_name) over(partition by p_mfgr order by p_size) from `p/a/r/t`) order by p_mfgr\n");
  }
Parser tests special character in table names.
parser/src/test/org/apache/hadoop/hive/ql/parse/TestSpecialCharacterInTableNames.java