[jira] [Commented] (TAJO-680) Improve the IN operator to support sub queries

["ASF GitHub Bot (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/TAJO-680?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14696388#comment-14696388 ] 

ASF GitHub Bot commented on TAJO-680:
-------------------------------------

Github user hyunsik commented on a diff in the pull request:

    https://github.com/apache/tajo/pull/620#discussion_r37046834
  
    --- Diff: tajo-core/src/test/java/org/apache/tajo/engine/query/TestInSubquery.java ---
    @@ -0,0 +1,176 @@
    +/**
    + * Licensed to the Apache Software Foundation (ASF) under one
    + * or more contributor license agreements.  See the NOTICE file
    + * distributed with this work for additional information
    + * regarding copyright ownership.  The ASF licenses this file
    + * to you under the Apache License, Version 2.0 (the
    + * "License"); you may not use this file except in compliance
    + * with the License.  You may obtain a copy of the License at
    + *
    + *     http://www.apache.org/licenses/LICENSE-2.0
    + *
    + * Unless required by applicable law or agreed to in writing, software
    + * distributed under the License is distributed on an "AS IS" BASIS,
    + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    + * See the License for the specific language governing permissions and
    + * limitations under the License.
    + */
    +
    +package org.apache.tajo.engine.query;
    +
    +import org.apache.tajo.IntegrationTest;
    +import org.apache.tajo.NamedTest;
    +import org.apache.tajo.error.Errors.ResultCode;
    +import org.apache.tajo.exception.TajoRuntimeException;
    +import org.junit.AfterClass;
    +import org.junit.BeforeClass;
    +import org.junit.Test;
    +import org.junit.experimental.categories.Category;
    +import org.junit.runner.RunWith;
    +import org.junit.runners.Parameterized;
    +
    +import java.sql.SQLException;
    +
    +import static org.junit.Assert.assertEquals;
    +import static org.junit.Assert.fail;
    +
    +@Category(IntegrationTest.class)
    +@RunWith(Parameterized.class)
    +@NamedTest("TestJoinQuery")
    +public class TestInSubquery extends TestJoinQuery {
    +
    +  public TestInSubquery(String joinOption) throws Exception {
    +    super(joinOption);
    +  }
    +
    +  @BeforeClass
    +  public static void setup() throws Exception {
    +    TestJoinQuery.setup();
    +  }
    +
    +  @AfterClass
    +  public static void classTearDown() throws SQLException {
    +    TestJoinQuery.classTearDown();
    +  }
    +
    +  @Test
    +  @Option(withExplain = false, withExplainGlobal = false, parameterized = true, sort = true)
    +  @SimpleTest()
    +  public final void testInSubQuery() throws Exception {
    +    runSimpleTests();
    +  }
    +
    +  @Test
    +  @Option(withExplain = false, withExplainGlobal = false, parameterized = true, sort = true)
    +  @SimpleTest()
    +  public final void testInSubQuery2() throws Exception {
    +    runSimpleTests();
    +  }
    +
    +  @Test
    +  @Option(withExplain = false, withExplainGlobal = false, parameterized = true, sort = true)
    +  @SimpleTest()
    +  public final void testNestedInSubQuery() throws Exception {
    +    runSimpleTests();
    +  }
    +
    +  @Test
    +  @Option(withExplain = false, withExplainGlobal = false, parameterized = true, sort = true)
    +  @SimpleTest()
    +  public final void testInSubQueryWithOtherConditions() throws Exception {
    +    runSimpleTests();
    +  }
    +
    +  @Test
    +  @Option(withExplain = false, withExplainGlobal = false, parameterized = true, sort = true)
    +  @SimpleTest()
    +  public final void testMultipleInSubQuery() throws Exception {
    +    runSimpleTests();
    +  }
    +
    +  @Test
    +  @Option(withExplain = false, withExplainGlobal = false, parameterized = true, sort = true)
    +  @SimpleTest()
    +  public final void testInSubQueryWithJoin() throws Exception {
    +    runSimpleTests();
    +  }
    +
    +  @Test
    +  @Option(withExplain = false, withExplainGlobal = false, parameterized = true, sort = true)
    +  @SimpleTest()
    +  public final void testInSubQueryWithTableSubQuery() throws Exception {
    +    runSimpleTests();
    +  }
    +
    +  @Test
    +  @Option(withExplain = false, withExplainGlobal = false, parameterized = true, sort = true)
    +  @SimpleTest()
    +  public final void testNotInSubQuery() throws Exception {
    +    runSimpleTests();
    +  }
    +
    +  @Test
    +  @Option(withExplain = false, withExplainGlobal = false, parameterized = true, sort = true)
    +  @SimpleTest()
    +  public final void testMultipleNotInSubQuery() throws Exception {
    +    runSimpleTests();
    +  }
    +
    +  @Test
    +  @Option(withExplain = false, withExplainGlobal = false, parameterized = true, sort = true)
    +  @SimpleTest()
    +  public final void testNestedNotInSubQuery() throws Exception {
    +    runSimpleTests();
    +  }
    +
    +  @Test
    +  @Option(withExplain = false, withExplainGlobal = false, parameterized = true, sort = true)
    +  @SimpleTest()
    +  public final void testInAndNotInSubQuery() throws Exception {
    +    runSimpleTests();
    +  }
    +
    +  @Test
    +  @Option(withExplain = false, withExplainGlobal = false, parameterized = true, sort = true)
    +  @SimpleTest()
    +  public final void testNestedInAndNotInSubQuery() throws Exception {
    +    runSimpleTests();
    +  }
    +
    +  @Test
    +  @Option(withExplain = false, withExplainGlobal = false, parameterized = true, sort = true)
    +  @SimpleTest()
    +  public final void testNestedInSubQuery2() throws Exception {
    +    // select c_name from customer
    +    // where c_nationkey in (
    +    //    select n_nationkey from nation where n_name like 'C%' and n_regionkey in (
    +    //    select count(*)-1 from region where r_regionkey > 0 and r_regionkey < 3))
    +    runSimpleTests();
    +  }
    +
    +  @Test()
    +  public final void testCorrelatedSubQuery() throws Exception {
    +    // Use try-catch clause to verify the exact error message
    +    try {
    +      executeString("select * from nation where n_regionkey in (select r_regionkey from region where n_name > r_name)");
    +      fail("Correlated subquery must raise the UnimplementedException.");
    +    } catch (TajoRuntimeException e) {
    +      assertEquals(ResultCode.NOT_IMPLEMENTED, e.getErrorCode());
    +    }
    +  }
    +
    +  @Test
    +  @Option(withExplain = false, withExplainGlobal = false, parameterized = true, sort = true)
    +  @SimpleTest()
    +  public final void testSameKeyNameOfOuterAndInnerQueries() throws Exception {
    +    runSimpleTests();
    +  }
    +
    +  @Test
    +  @Option(parameterized = true, sort = true)
    +  @SimpleTest()
    +  public final void testWithAsteriskAndJoin() throws Exception {
    +    // select * from lineitem, orders where l_orderkey = o_orderkey and l_partkey in (select l_partkey from lineitem where l_linenumber in (1, 3, 5, 7, 9))
    --- End diff --
    
    It exceeds the column width of coding standards.


> Improve the IN operator to support sub queries
> ----------------------------------------------
>
>                 Key: TAJO-680
>                 URL: https://issues.apache.org/jira/browse/TAJO-680
>             Project: Tajo
>          Issue Type: Improvement
>          Components: distributed query plan, SQL Parser
>            Reporter: Jihoon Son
>            Assignee: Jihoon Son
>             Fix For: 0.11.0
>
>         Attachments: Distributed plan.png, Logical plan.png
>
>
> Currently, the IN operator can be used with only sets of values.
> We need to improve it to support sub queries as the following example query.
> {noformat}
> tajo> select * from nation where n_regionkey in (select r_regionkey from region); 
> {noformat}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)