/** Test case for * <a href="https://issues.apache.org/jira/browse/CALCITE-1849">[CALCITE-1849] * Support sub-queries (RexSubQuery) in RelToSqlConverter</a>. */ @Test public void testExistsWithExpand() { String query = "select \"product_name\" from \"product\" a " + "where exists (select count(*) " + "from \"sales_fact_1997\"b " + "where b.\"product_id\" = a.\"product_id\")"; String expected = "SELECT \"product_name\"\n" + "FROM \"foodmart\".\"product\"\n" + "WHERE EXISTS (SELECT COUNT(*)\n" + "FROM \"foodmart\".\"sales_fact_1997\"\n" + "WHERE \"product_id\" = \"product\".\"product_id\")"; sql(query).config(NO_EXPAND_CONFIG).ok(expected); }
/** Test case for * <a href="https://issues.apache.org/jira/browse/CALCITE-1849">[CALCITE-1849] * Support sub-queries (RexSubQuery) in RelToSqlConverter</a>. */ @Test public void testExistsWithExpand() { String query = "select \"product_name\" from \"product\" a " + "where exists (select count(*) " + "from \"sales_fact_1997\"b " + "where b.\"product_id\" = a.\"product_id\")"; String expected = "SELECT \"product_name\"\n" + "FROM \"foodmart\".\"product\"\n" + "WHERE EXISTS (SELECT COUNT(*)\n" + "FROM \"foodmart\".\"sales_fact_1997\"\n" + "WHERE \"product_id\" = \"product\".\"product_id\")"; sql(query).config(NO_EXPAND_CONFIG).ok(expected); }
@Test public void testNotExistsWithExpand() { String query = "select \"product_name\" from \"product\" a " + "where not exists (select count(*) " + "from \"sales_fact_1997\"b " + "where b.\"product_id\" = a.\"product_id\")"; String expected = "SELECT \"product_name\"\n" + "FROM \"foodmart\".\"product\"\n" + "WHERE NOT EXISTS (SELECT COUNT(*)\n" + "FROM \"foodmart\".\"sales_fact_1997\"\n" + "WHERE \"product_id\" = \"product\".\"product_id\")"; sql(query).config(NO_EXPAND_CONFIG).ok(expected); }
@Test public void testSubQueryNotInWithExpand() { String query = "select \"product_name\" from \"product\" a " + "where \"product_id\" not in (select \"product_id\" " + "from \"sales_fact_1997\"b " + "where b.\"product_id\" = a.\"product_id\")"; String expected = "SELECT \"product_name\"\n" + "FROM \"foodmart\".\"product\"\n" + "WHERE \"product_id\" NOT IN (SELECT \"product_id\"\n" + "FROM \"foodmart\".\"sales_fact_1997\"\n" + "WHERE \"product_id\" = \"product\".\"product_id\")"; sql(query).config(NO_EXPAND_CONFIG).ok(expected); }
@Test public void testSubQueryNotInWithExpand() { String query = "select \"product_name\" from \"product\" a " + "where \"product_id\" not in (select \"product_id\" " + "from \"sales_fact_1997\"b " + "where b.\"product_id\" = a.\"product_id\")"; String expected = "SELECT \"product_name\"\n" + "FROM \"foodmart\".\"product\"\n" + "WHERE \"product_id\" NOT IN (SELECT \"product_id\"\n" + "FROM \"foodmart\".\"sales_fact_1997\"\n" + "WHERE \"product_id\" = \"product\".\"product_id\")"; sql(query).config(NO_EXPAND_CONFIG).ok(expected); }
@Test public void testSubQueryInWithExpand2() { String query = "select \"product_name\" from \"product\" a " + "where \"product_id\" in (1, 2)"; String expected = "SELECT \"product_name\"\n" + "FROM \"foodmart\".\"product\"\n" + "WHERE \"product_id\" = 1 OR \"product_id\" = 2"; sql(query).config(NO_EXPAND_CONFIG).ok(expected); }
@Test public void testSubQueryInWithExpand2() { String query = "select \"product_name\" from \"product\" a " + "where \"product_id\" in (1, 2)"; String expected = "SELECT \"product_name\"\n" + "FROM \"foodmart\".\"product\"\n" + "WHERE \"product_id\" = 1 OR \"product_id\" = 2"; sql(query).config(NO_EXPAND_CONFIG).ok(expected); }
@Test public void testNotExistsWithExpand() { String query = "select \"product_name\" from \"product\" a " + "where not exists (select count(*) " + "from \"sales_fact_1997\"b " + "where b.\"product_id\" = a.\"product_id\")"; String expected = "SELECT \"product_name\"\n" + "FROM \"foodmart\".\"product\"\n" + "WHERE NOT EXISTS (SELECT COUNT(*)\n" + "FROM \"foodmart\".\"sales_fact_1997\"\n" + "WHERE \"product_id\" = \"product\".\"product_id\")"; sql(query).config(NO_EXPAND_CONFIG).ok(expected); }
@Test public void testSubQueryInWithExpand() { String query = "select \"product_name\" from \"product\" a " + "where \"product_id\" in (select \"product_id\" " + "from \"sales_fact_1997\"b " + "where b.\"product_id\" = a.\"product_id\")"; String expected = "SELECT \"product_name\"\n" + "FROM \"foodmart\".\"product\"\n" + "WHERE \"product_id\" IN (SELECT \"product_id\"\n" + "FROM \"foodmart\".\"sales_fact_1997\"\n" + "WHERE \"product_id\" = \"product\".\"product_id\")"; sql(query).config(NO_EXPAND_CONFIG).ok(expected); }
@Test public void testSubQueryInWithExpand() { String query = "select \"product_name\" from \"product\" a " + "where \"product_id\" in (select \"product_id\" " + "from \"sales_fact_1997\"b " + "where b.\"product_id\" = a.\"product_id\")"; String expected = "SELECT \"product_name\"\n" + "FROM \"foodmart\".\"product\"\n" + "WHERE \"product_id\" IN (SELECT \"product_id\"\n" + "FROM \"foodmart\".\"sales_fact_1997\"\n" + "WHERE \"product_id\" = \"product\".\"product_id\")"; sql(query).config(NO_EXPAND_CONFIG).ok(expected); }