@Test
  public void testSubQueriesNotIn() throws ParseException {
    // non agg, non corr
    parse("select * \n" +
        "\n" +
        "from \"src/_/cbo\" \n" +
        "\n" +
        "where \"src/_/cbo\".key not in  \n" +
        "\n" +
        "  ( select key  from \"src/_/cbo\" s1 \n" +
        "\n" +
        "    where s1.key > '2'\n" +
        "\n" +
        "  ) order by key\n" +
        "\n");

    // non agg, corr
    parse("select p_mfgr, b.p_name, p_size \n" +
        "\n" +
        "from \"p/a/r/t\" b \n" +
        "\n" +
        "where b.p_name not in \n" +
        "\n" +
        "  (select p_name \n" +
        "\n" +
        "  from (select p_mfgr, p_name, p_size as r from \"p/a/r/t\") a \n" +
        "\n" +
        "  where r < 10 and b.p_mfgr = a.p_mfgr \n" +
        "\n" +
        "  ) order by p_mfgr,p_size");

    // agg, non corr
    parse("" +
        "select p_name, p_size \n" +
        "\n" +
        "from \n" +
        "\n" +
        "\"p/a/r/t\" where \"p/a/r/t\".p_size not in \n" +
        "\n" +
        "  (select avg(p_size) \n" +
        "\n" +
        "  from (select p_size from \"p/a/r/t\") a \n" +
        "\n" +
        "  where p_size < 10\n" +
        "\n" +
        "  ) order by p_name\n");

    // agg, corr
    parse("" +
        "select p_mfgr, p_name, p_size \n" +
        "\n" +
        "from \"p/a/r/t\" b where b.p_size not in \n" +
        "\n" +
        "  (select min(p_size) \n" +
        "\n" +
        "  from (select p_mfgr, p_size from \"p/a/r/t\") a \n" +
        "\n" +
        "  where p_size < 10 and b.p_mfgr = a.p_mfgr\n" +
        "\n" +
        "  ) order by  p_name\n");

    // non agg, non corr, Group By in Parent Query
    parse("select li.l_partkey, count(*) \n" +
        "\n" +
        "from \"line/item\" li \n" +
        "\n" +
        "where li.l_linenumber = 1 and \n" +
        "\n" +
        "  li.l_orderkey not in (select l_orderkey from \"line/item\" where l_shipmode = 'AIR') \n" +
        "\n" +
        "group by li.l_partkey order by li.l_partkey\n");

    // non agg, corr, having
    parse("select b.p_mfgr, min(p_retailprice) \n" +
        "\n" +
        "from \"p/a/r/t\" b \n" +
        "\n" +
        "group by b.p_mfgr\n" +
        "\n" +
        "having b.p_mfgr not in \n" +
        "\n" +
        "  (select p_mfgr \n" +
        "\n" +
        "  from (select p_mfgr, min(p_retailprice) l, max(p_retailprice) r, avg(p_retailprice) a from \"p/a/r/t\" group by p_mfgr) a \n" +
        "\n" +
        "  where min(p_retailprice) = l and r - l > 600\n" +
        "\n" +
        "  )\n" +
        "\n" +
        "  order by b.p_mfgr\n");

    // agg, non corr, having
    parse("select b.p_mfgr, min(p_retailprice) \n" +
        "\n" +
        "from \"p/a/r/t\" b \n" +
        "\n" +
        "group by b.p_mfgr\n" +
        "\n" +
        "having b.p_mfgr not in \n" +
        "\n" +
        "  (select p_mfgr \n" +
        "\n" +
        "  from \"p/a/r/t\" a\n" +
        "\n" +
        "  group by p_mfgr\n" +
        "\n" +
        "  having max(p_retailprice) - min(p_retailprice) > 600\n" +
        "\n" +
        "  )\n" +
        "\n" +
        "  order by b.p_mfgr  \n");
  }
Parser tests special character in table names.
parser/src/test/org/apache/hadoop/hive/ql/parse/TestSpecialCharacterInTableNamesQuotes.java