Mailing-List: contact dev-help@drill.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@drill.apache.org
From: prasadns14 <git@git.apache.org>
To: dev@drill.apache.org
Reply-To: dev@drill.apache.org
References: <git-pr-1033-drill@git.apache.org>
In-Reply-To: <git-pr-1033-drill@git.apache.org>
Subject: [GitHub] drill pull request #1033: DRILL-5952: Implement "CREATE TABLE IF NOT EXISTS"
Content-Type: text/plain
Message-Id: <20171124054919.D9BCFDFB86@git1-us-west.apache.org>
Date: Fri, 24 Nov 2017 05:49:19 +0000 (UTC)
archived-at: Fri, 24 Nov 2017 05:49:22 -0000

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

    https://github.com/apache/drill/pull/1033#discussion_r152900976
  
    --- Diff: exec/java-exec/src/test/java/org/apache/drill/exec/sql/TestViewSupport.java ---
    @@ -311,6 +311,75 @@ public void createViewWhenViewAlreadyExists() throws Exception {
         }
       }
     
    +  @Test // DRILL-5952
    +  public void createViewIfNotExistsWhenTableAlreadyExists() throws Exception {
    +    final String tableName = generateViewName();
    +
    +    try {
    +      final String tableDef = "SELECT region_id, sales_city FROM cp.`region.json` ORDER BY `region_id` LIMIT 2";
    +
    +      test("CREATE TABLE %s.%s as %s", DFS_TMP_SCHEMA, tableName, tableDef);
    +
    +      // Try to create the view with same name in same schema with if not exists clause.
    +      final String createViewSql = String.format("CREATE VIEW IF NOT EXISTS %s.`%s` AS %s", DFS_TMP_SCHEMA, tableName, tableDef);
    +
    +      testBuilder()
    +        .sqlQuery(createViewSql)
    +        .unOrdered()
    +        .baselineColumns("ok", "summary")
    +        .baselineValues(false,
    +          String.format("A non-view table with given name [%s] already exists in schema [%s]", tableName, DFS_TMP_SCHEMA))
    +        .go();
    +
    +    } finally {
    +      FileUtils.deleteQuietly(new File(dirTestWatcher.getDfsTestTmpDir(), tableName));
    +    }
    +  }
    +
    +  @Test // DRILL-5952
    +  public void createViewIfNotExistsWhenViewAlreadyExists() throws Exception {
    +    final String viewName = generateViewName();
    +
    +    try {
    +      final String viewDef1 = "SELECT region_id, sales_city FROM cp.`region.json` ORDER BY `region_id` LIMIT 2";
    +
    +      // Create the view
    +      createViewHelper(DFS_TMP_SCHEMA, viewName, DFS_TMP_SCHEMA, null, viewDef1);
    +
    +      // Try to create the view with same name in same schema with if not exists clause.
    +      final String viewDef2 = "SELECT sales_state_province FROM cp.`region.json` ORDER BY `region_id`";
    +      final String createViewSql = String.format("CREATE VIEW IF NOT EXISTS %s.`%s` AS %s", DFS_TMP_SCHEMA, viewName, viewDef2);
    +
    +      testBuilder()
    +        .sqlQuery(createViewSql)
    +        .unOrdered()
    +        .baselineColumns("ok", "summary")
    +        .baselineValues(false,
    +          String.format("A view with given name [%s] already exists in schema [%s]", viewName, DFS_TMP_SCHEMA))
    +        .go();
    +
    +      // Make sure the view created returns the data expected.
    +      queryViewHelper(String.format("SELECT * FROM %s.`%s` LIMIT 1", DFS_TMP_SCHEMA, viewName),
    +        new String[]{"region_id", "sales_city"},
    +        ImmutableList.of(new Object[]{0L, "None"})
    +      );
    +    } finally {
    +      dropViewHelper(DFS_TMP_SCHEMA, viewName, DFS_TMP_SCHEMA);
    +    }
    +  }
    +
    +  @Test // DRILL-5952
    +  public void createViewWithBothOrReplaceAndIfNotExists() throws Exception {
    +    final String viewName = generateViewName();
    +
    +    final String viewDef = "SELECT region_id, sales_city FROM cp.`region.json`";
    +
    +    // Try to create the view with both <or replace> and <if not exists> clause.
    +    final String createViewSql = String.format("CREATE OR REPLACE VIEW IF NOT EXISTS %s.`%s` AS %s", DFS_TMP_SCHEMA, viewName, viewDef);
    +
    +    errorMsgTestHelper(createViewSql, "Create view statement cannot have both <OR REPLACE> and <IF NOT EXISTS> clause");
    +  }
    +
    --- End diff --
    
    Done


---