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

    https://github.com/apache/carbondata/pull/1939#discussion_r168085698
  
    --- Diff: 
integration/spark-common-test/src/test/scala/org/apache/carbondata/spark/testsuite/createTable/TestCreateTableAsSelect.scala
 ---
    @@ -170,10 +175,277 @@ class TestCreateTableAsSelect extends QueryTest with 
BeforeAndAfterAll {
         }
       }
     
    +  test("test create table as select with where clause in select from 
parquet table that does not return data") {
    +    sql("DROP TABLE IF EXISTS ctas_select_where_parquet")
    +    sql(
    +      """
    +        | CREATE TABLE ctas_select_where_parquet
    +        | STORED BY 'carbondata'
    +        | as select * FROM parquet_ctas_test
    +        | where key=300""".stripMargin)
    +    checkAnswer(sql("SELECT * FROM ctas_select_where_parquet"),
    +      sql("SELECT * FROM parquet_ctas_test where key=300"))
    +  }
    +
    +  test("test create table as select with where clause in select from 
hive/orc table that does not return data") {
    +    sql("DROP TABLE IF EXISTS ctas_select_where_orc")
    +    sql(
    +      """
    +        | CREATE TABLE ctas_select_where_orc
    +        | STORED BY 'carbondata'
    +        | AS SELECT * FROM orc_ctas_test
    +        | where key=300""".stripMargin)
    +    checkAnswer(sql("SELECT * FROM ctas_select_where_orc"),
    +      sql("SELECT * FROM orc_ctas_test where key=300"))
    +  }
    +
    +  test("test create table as select with select from same carbon table 
name with if not exists clause") {
    +    sql("drop table if exists ctas_same_table_name")
    +    sql("CREATE TABLE ctas_same_table_name(key INT, value STRING) STORED 
BY 'carbondata'")
    +    checkExistence(sql("SHOW TABLES"), true, "ctas_same_table_name")
    +    sql(
    +      """
    +        | CREATE TABLE IF NOT EXISTS ctas_same_table_name
    +        | STORED BY 'carbondata'
    +        | AS SELECT * FROM ctas_same_table_name
    +      """.stripMargin)
    +    intercept[Exception] {
    +      sql(
    +        """
    +          | CREATE TABLE ctas_same_table_name
    +          | STORED BY 'carbondata'
    +          | AS SELECT * FROM ctas_same_table_name
    +        """.stripMargin)
    +    }
    +  }
    +
    +  test("test create table as select with select from same carbon table 
name with if not exists clause and source table not exists") {
    +    sql("DROP TABLE IF EXISTS ctas_same_table_name")
    +    checkExistence(sql("SHOW TABLES"), false, "ctas_same_table_name")
    +    intercept[Exception] {
    +      sql(
    +        """
    +          | CREATE TABLE IF NOT EXISTS ctas_same_table_name
    +          | STORED BY 'carbondata'
    +          | AS SELECT * FROM ctas_same_table_name
    +        """.stripMargin)
    +    }
    +  }
    +
    +  test("test create table as select with select from same carbon table 
name with if not exists clause and source table exists") {
    +    sql("DROP TABLE IF EXISTS ctas_same_table_name")
    +    sql("DROP TABLE IF EXISTS ctas_if_table_name")
    +    sql("CREATE TABLE ctas_same_table_name(key INT, value STRING) STORED 
BY 'carbondata'")
    +    sql(
    +      """
    +        | CREATE TABLE IF NOT EXISTS ctas_if_table_name
    +        | STORED BY 'carbondata'
    +        | AS SELECT * FROM ctas_same_table_name
    +      """.stripMargin)
    +    checkExistence(sql("show tables"), true, "ctas_if_table_name")
    +  }
    +
    +  test("add example for documentation") {
    +    sql("DROP TABLE IF EXISTS target_table")
    +    sql("DROP TABLE IF EXISTS source_table")
    +    // create carbon table and insert data
    +    sql(
    +      """
    +        | CREATE TABLE source_table(
    +        |     id INT,
    +        |     name STRING,
    +        |     city STRING,
    +        |     age INT)
    +        |     STORED AS parquet
    +        |     """.stripMargin)
    +    sql("INSERT INTO source_table SELECT 1,'bob','shenzhen',27")
    +    sql("INSERT INTO source_table SELECT 2,'david','shenzhen',31")
    +    sql(
    +      """
    +        | CREATE TABLE target_table
    +        | STORED BY 'carbondata'
    +        | AS
    +        |   SELECT city,avg(age) FROM source_table group by city
    +      """.stripMargin)
    +    // results:
    +    //    sql("SELECT * FROM target_table").show
    +    //    +--------+--------+
    +    //    |    city|avg(age)|
    +    //    +--------+--------+
    +    //    |shenzhen|    29.0|
    +    //    +--------+--------+
    +    checkAnswer(sql("SELECT * FROM target_table"), Seq(Row("shenzhen", 
29)))
    +  }
    +
    +  test("test create table as select with sum,count,min,max") {
    +    sql("DROP TABLE IF EXISTS target_table")
    +    sql("DROP TABLE IF EXISTS source_table")
    +    // create carbon table and insert data
    +    sql(
    +      """
    +        | CREATE TABLE source_table(
    +        |     id INT,
    +        |     name STRING,
    +        |     city STRING,
    +        |     age INT)
    +        | STORED BY 'carbondata'
    +      """.stripMargin)
    +    sql("INSERT INTO source_table SELECT 1,'bob','shenzhen',27")
    +    sql("INSERT INTO source_table SELECT 2,'david','shenzhen',31")
    +    sql(
    +      """
    +        | CREATE TABLE target_table
    +        | STORED BY 'carbondata'
    +        | AS
    +        |   SELECT city,sum(age),count(age),min(age),max(age)
    +        |   FROM source_table group by city
    +      """.stripMargin)
    +    checkAnswer(sql("SELECT * FROM target_table"), Seq(Row("shenzhen", 58, 
2, 27, 31)))
    +  }
    +
    +  test("test create table as select with insert data into source_table 
after CTAS") {
    +    sql("DROP TABLE IF EXISTS target_table")
    +    sql("DROP TABLE IF EXISTS source_table")
    +    // create carbon table and insert data
    +    sql(
    +      """
    +        | CREATE TABLE source_table(
    +        |     id INT,
    +        |     name STRING,
    +        |     city STRING,
    +        |     age INT)
    +        |     STORED BY 'carbondata'
    +        |     """.stripMargin)
    +    sql("INSERT INTO source_table SELECT 1,'bob','shenzhen',27")
    +    sql("INSERT INTO source_table SELECT 2,'david','shenzhen',31")
    +    sql(
    +      """
    +        | CREATE TABLE target_table
    +        | STORED BY 'carbondata'
    +        | AS
    +        |   SELECT city,sum(age),count(age),min(age),max(age)
    +        |   FROM source_table group by city
    +      """.stripMargin)
    +    sql("INSERT INTO source_table SELECT 1,'bob','shenzhen',27")
    +    sql("INSERT INTO source_table SELECT 2,'david','shenzhen',31")
    +    checkAnswer(sql("SELECT * FROM target_table"), Seq(Row("shenzhen", 58, 
2, 27, 31)))
    +  }
    +
    +  test("test create table as select with auto merge") {
    +    CarbonProperties.getInstance().
    +      addProperty(CarbonCommonConstants.ENABLE_AUTO_LOAD_MERGE, "true")
    +    sql("DROP TABLE IF EXISTS target_table")
    +    sql("DROP TABLE IF EXISTS source_table")
    +    // create carbon table and insert data
    +    sql(
    +      """
    +        | CREATE TABLE source_table(
    +        |     id INT,
    +        |     name STRING,
    +        |     city STRING,
    +        |     age INT)
    +        |     STORED BY 'carbondata'
    +        |     """.stripMargin)
    +    sql("INSERT INTO source_table SELECT 1,'bob','shenzhen',27")
    +    sql("INSERT INTO source_table SELECT 2,'david','shenzhen',31")
    +    sql(
    +      """
    +        | CREATE TABLE target_table
    +        | STORED BY 'carbondata'
    +        | AS
    +        |   SELECT city,avg(age)
    +        |   FROM source_table group by city
    +      """.stripMargin)
    +    sql("INSERT INTO source_table SELECT 1,'bob','shenzhen',27")
    +    sql("INSERT INTO source_table SELECT 2,'david','shenzhen',31")
    +
    +    checkExistence(sql("SHOW SEGMENTS FOR TABLE source_table"), true, 
"Compacted")
    +    checkExistence(sql("SHOW SEGMENTS FOR TABLE target_table"), false, 
"Compacted")
    +
    +    sql("INSERT INTO target_table SELECT 'shenzhen',8")
    +    sql("INSERT INTO target_table SELECT 'shenzhen',9")
    +    sql("INSERT INTO target_table SELECT 'shenzhen',3")
    +    checkExistence(sql("SHOW SEGMENTS FOR TABLE target_table"), true, 
"Compacted")
    +    checkAnswer(sql("SELECT * FROM target_table"),
    +      Seq(Row("shenzhen", 29), Row("shenzhen", 8), Row("shenzhen", 9), 
Row("shenzhen", 3)))
    +    CarbonProperties.getInstance().
    +      addProperty(CarbonCommonConstants.ENABLE_AUTO_LOAD_MERGE,
    +        CarbonCommonConstants.DEFAULT_ENABLE_AUTO_LOAD_MERGE)
    +  }
    +
    +  test("test create table as select with filter, <, and, >=") {
    +    sql("DROP TABLE IF EXISTS target_table")
    +    sql("DROP TABLE IF EXISTS source_table")
    +    // create carbon table and insert data
    +    sql(
    +      """
    +        | CREATE TABLE source_table(
    +        |     id INT,
    +        |     name STRING,
    +        |     city STRING,
    +        |     age INT)
    +        |     STORED BY 'carbondata'
    +        |     """.stripMargin)
    +    sql("INSERT INTO source_table SELECT 1,'bob','shenzhen',27")
    +    sql("INSERT INTO source_table SELECT 2,'david','shenzhen',31")
    +    sql("INSERT INTO source_table SELECT 3,'jack','shenzhen',5")
    +    sql("INSERT INTO source_table SELECT 4,'alice','shenzhen',35")
    +    sql(
    +      """
    +        | CREATE TABLE target_table
    +        | STORED BY 'carbondata'
    +        | AS
    +        |   SELECT city,avg(age)
    +        |   FROM source_table where age > 20 and age <= 31 GROUP BY city
    +      """.stripMargin)
    +
    +    checkAnswer(sql("SELECT * FROM target_table"), Seq(Row("shenzhen", 
29)))
    +  }
    +
    +  test("test create table as select with filter, >=, or, =") {
    +    sql("DROP TABLE IF EXISTS target_table")
    +    sql("DROP TABLE IF EXISTS source_table")
    +    // create carbon table and insert data
    +    sql(
    +      """
    +        | CREATE TABLE source_table(
    +        |     id INT,
    +        |     name STRING,
    +        |     city STRING,
    +        |     age INT)
    +        |     STORED BY 'carbondata'
    +        |     """.stripMargin)
    +    sql("INSERT INTO source_table SELECT 1,'bob','shenzhen',27")
    +    sql("INSERT INTO source_table SELECT 2,'david','shenzhen',31")
    +    sql("INSERT INTO source_table SELECT 3,'jack','shenzhen',5")
    +    sql("INSERT INTO source_table SELECT 4,'alice','shenzhen',35")
    +    sql(
    +      """
    +        | CREATE TABLE target_table
    +        | STORED BY 'carbondata'
    +        | AS
    +        |   SELECT city,avg(age)
    +        |   FROM source_table where age >= 20 or age = 5 group by city
    +      """.stripMargin)
    +
    +    checkAnswer(sql("SELECT * FROM target_table"), Seq(Row("shenzhen", 
24.5)))
    +  }
    +
       override def afterAll {
         sql("DROP TABLE IF EXISTS carbon_ctas_test")
         sql("DROP TABLE IF EXISTS parquet_ctas_test")
         sql("DROP TABLE IF EXISTS orc_ctas_test")
    +    sql("DROP TABLE IF EXISTS ctas_same_table_name")
    +    sql("DROP TABLE IF EXISTS ctas_select_carbon")
    +    sql("DROP TABLE IF EXISTS ctas_select_direct_data")
    +    sql("DROP TABLE IF EXISTS ctas_select_parquet")
    +    sql("DROP TABLE IF EXISTS ctas_select_orc")
    +    sql("DROP TABLE IF EXISTS ctas_select_where_carbon")
    +    sql("DROP TABLE IF EXISTS ctas_select_where_parquet")
    +    sql("DROP TABLE IF EXISTS ctas_select_where_orc")
    +    sql("DROP TABLE IF EXISTS ctas_tblproperties_test")
    +    sql("DROP TABLE IF EXISTS ctas_if_table_name")
    +    sql("DROP TABLE IF EXISTS source_table")
    +    sql("DROP TABLE IF EXISTS target_table")
    --- End diff --
    
    @xubo245....According to me CTAS is a very small feature in terms of lines 
of code and internally it uses create table and insert into flow. So I think 
adding too many test cases for this feature  is not a good idea and it can 
unnecessary increase the UT test cases running time.
    I believe existing test cases are sufficient to validate the basic 
functionality of CTAS feature. If there are any problems with the existing test 
cases you can make modifications and correct them but **I suggest not to add 
any new test cases until and unless there is some specific scenario or bug in 
the feature**


---

Reply via email to