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

    https://github.com/apache/carbondata/pull/1856#discussion_r174129743
  
    --- Diff: 
integration/spark-common-test/src/test/scala/org/apache/carbondata/integration/spark/testsuite/timeseries/TestTimeseriesTableSelection.scala
 ---
    @@ -99,124 +113,763 @@ class TestTimeseriesTableSelection extends QueryTest 
with BeforeAndAfterAll {
         sql(s"LOAD DATA LOCAL INPATH '$resourcesPath/timeseriestest.csv' into 
table mainTable")
       }
     
    -  test("test PreAggregate table selection 1") {
    -    val df = sql("select mytime from mainTable group by mytime")
    +  test("test timeseries table selection 1") {
    +    val df = sql("SELECT mytime FROM mainTable GROUP BY mytime")
         preAggTableValidator(df.queryExecution.analyzed, "maintable")
       }
     
    -  test("test PreAggregate table selection 2") {
    -    val df = sql("select timeseries(mytime,'hour') from mainTable group by 
timeseries(mytime,'hour')")
    +  test("test timeseries table selection 2") {
    +    val df = sql("SELECT TIMESERIES(mytime,'hour') FROM mainTable GROUP BY 
TIMESERIES(mytime,'hour')")
         preAggTableValidator(df.queryExecution.analyzed, "maintable_agg0_hour")
       }
     
    -  test("test PreAggregate table selection 3") {
    -    val df = sql("select timeseries(mytime,'milli') from mainTable group 
by timeseries(mytime,'milli')")
    -    preAggTableValidator(df.queryExecution.analyzed, "maintable")
    +  test("test timeseries table selection 3: No enum constant MILLI") {
    +    val e = intercept[Exception] {
    +      val df = sql(
    +        """
    +          | SELECT TIMESERIES(mytime,'milli')
    +          | FROM mainTable
    +          | GROUP BY TIMESERIES(mytime,'milli')
    +        """.stripMargin)
    +      preAggTableValidator(df.queryExecution.analyzed, "maintable")
    +      df.show()
    +    }
    +    assert(e.getMessage.contains(
    +      "No enum constant 
org.apache.carbondata.core.preagg.TimeSeriesFunctionEnum.MILLI"))
       }
     
    -  test("test PreAggregate table selection 4") {
    -    val df = sql("select timeseries(mytime,'year') from mainTable group by 
timeseries(mytime,'year')")
    +  test("test timeseries table selection 4") {
    +    val df = sql("SELECT TIMESERIES(mytime,'year') FROM mainTable GROUP BY 
TIMESERIES(mytime,'year')")
         preAggTableValidator(df.queryExecution.analyzed,"maintable_agg0_year")
       }
     
    -  test("test PreAggregate table selection 5") {
    -    val df = sql("select timeseries(mytime,'day') from mainTable group by 
timeseries(mytime,'day')")
    +  test("test timeseries table selection 5") {
    +    val df = sql("SELECT TIMESERIES(mytime,'day') FROM mainTable GROUP BY 
TIMESERIES(mytime,'day')")
         preAggTableValidator(df.queryExecution.analyzed,"maintable_agg0_day")
       }
     
    -  test("test PreAggregate table selection 6") {
    -    val df = sql("select timeseries(mytime,'month') from mainTable group 
by timeseries(mytime,'month')")
    +  test("test timeseries table selection 6") {
    +    val df = sql("SELECT TIMESERIES(mytime,'month') FROM mainTable GROUP 
BY TIMESERIES(mytime,'month')")
         preAggTableValidator(df.queryExecution.analyzed,"maintable_agg0_month")
       }
     
    -  test("test PreAggregate table selection 7") {
    -    val df = sql("select timeseries(mytime,'minute') from mainTable group 
by timeseries(mytime,'minute')")
    +  test("test timeseries table selection 7") {
    +    val df = sql("SELECT TIMESERIES(mytime,'minute') FROM mainTable GROUP 
BY TIMESERIES(mytime,'minute')")
         
preAggTableValidator(df.queryExecution.analyzed,"maintable_agg0_minute")
       }
     
    -  test("test PreAggregate table selection 8") {
    -    val df = sql("select timeseries(mytime,'second') from mainTable group 
by timeseries(mytime,'second')")
    +  test("test timeseries table selection 8") {
    +    val df = sql("SELECT TIMESERIES(mytime,'second') FROM mainTable GROUP 
BY TIMESERIES(mytime,'second')")
         
preAggTableValidator(df.queryExecution.analyzed,"maintable_agg0_second")
       }
     
    -  test("test PreAggregate table selection 9") {
    -    val df = sql("select timeseries(mytime,'hour') from mainTable where 
timeseries(mytime,'hour')='x' group by timeseries(mytime,'hour')")
    -    preAggTableValidator(df.queryExecution.analyzed,"maintable_agg0_hour")
    +  test("test timeseries table selection 9") {
    +    val df = sql(
    +      """
    +        | SELECT TIMESERIES(mytime,'hour')
    +        | FROM mainTable
    +        | WHERE TIMESERIES(mytime,'hour')='x'
    +        | GROUP BY TIMESERIES(mytime,'hour')
    +      """.stripMargin)
    +    preAggTableValidator(df.queryExecution.analyzed, "maintable_agg0_hour")
       }
     
    -  test("test PreAggregate table selection 10") {
    -    val df = sql("select timeseries(mytime,'hour') from mainTable where 
timeseries(mytime,'hour')='x' group by timeseries(mytime,'hour') order by 
timeseries(mytime,'hour')")
    -    preAggTableValidator(df.queryExecution.analyzed,"maintable_agg0_hour")
    +  test("test timeseries table selection 10") {
    +    val df = sql(
    +      """
    +        | SELECT TIMESERIES(mytime,'hour')
    +        | FROM mainTable
    +        | WHERE TIMESERIES(mytime,'hour')='x'
    +        | GROUP BY TIMESERIES(mytime,'hour')
    +        | ORDER BY TIMESERIES(mytime,'hour')
    +      """.stripMargin)
    +    preAggTableValidator(df.queryExecution.analyzed, "maintable_agg0_hour")
    +  }
    +
    +  test("test timeseries table selection 11") {
    +    val df = sql(
    +      """
    +        | SELECT TIMESERIES(mytime,'hour'),SUM(age)
    +        | FROM mainTable
    +        | WHERE TIMESERIES(mytime,'hour')='x'
    +        | GROUP BY TIMESERIES(mytime,'hour')
    +        | ORDER BY TIMESERIES(mytime,'hour')
    +      """.stripMargin)
    +    preAggTableValidator(df.queryExecution.analyzed, "maintable_agg0_hour")
       }
     
    -  test("test PreAggregate table selection 11") {
    -    val df = sql("select timeseries(mytime,'hour'),sum(age) from mainTable 
where timeseries(mytime,'hour')='x' group by timeseries(mytime,'hour') order by 
timeseries(mytime,'hour')")
    -    preAggTableValidator(df.queryExecution.analyzed,"maintable_agg0_hour")
    +  test("test timeseries table selection 12") {
    +    val df = sql(
    +      """
    +        | SELECT TIMESERIES(mytime,'hour') AS hourlevel,SUM(age) AS SUM
    +        | FROM mainTable
    +        | WHERE TIMESERIES(mytime,'hour')='x'
    +        | GROUP BY TIMESERIES(mytime,'hour')
    +        | ORDER BY TIMESERIES(mytime,'hour')
    +      """.stripMargin)
    +    preAggTableValidator(df.queryExecution.analyzed, "maintable_agg0_hour")
       }
     
    -  test("test PreAggregate table selection 12") {
    -    val df = sql("select timeseries(mytime,'hour')as hourlevel,sum(age) as 
sum from mainTable where timeseries(mytime,'hour')='x' group by 
timeseries(mytime,'hour') order by timeseries(mytime,'hour')")
    -    preAggTableValidator(df.queryExecution.analyzed,"maintable_agg0_hour")
    +  test("test timeseries table selection 13") {
    +    val df = sql(
    +      """
    +        | SELECT TIMESERIES(mytime,'hour')as hourlevel,SUM(age) AS SUM
    +        | FROM mainTable
    +        | WHERE TIMESERIES(mytime,'hour')='x' AND name='vishal'
    +        | GROUP BY TIMESERIES(mytime,'hour')
    +        | ORDER BY TIMESERIES(mytime,'hour')
    +      """.stripMargin)
    +    preAggTableValidator(df.queryExecution.analyzed, "maintable")
       }
     
    -  test("test PreAggregate table selection 13") {
    -    val df = sql("select timeseries(mytime,'hour')as hourlevel,sum(age) as 
sum from mainTable where timeseries(mytime,'hour')='x' and name='vishal' group 
by timeseries(mytime,'hour') order by timeseries(mytime,'hour')")
    -    preAggTableValidator(df.queryExecution.analyzed,"maintable")
    +  test("test timeseries table selection 14: TIMESERIES(mytime,'hour') 
match") {
    +    val df = sql(
    +      """
    +        | SELECT TIMESERIES(mytime,'hour')
    +        | FROM mainTable
    +        | WHERE TIMESERIES(mytime,'hour')='2016-02-23 09:00:00'
    +        | GROUP BY TIMESERIES(mytime,'hour')
    +      """.stripMargin)
    +    preAggTableValidator(df.queryExecution.analyzed, "maintable_agg0_hour")
    +    checkAnswer(df, Row(Timestamp.valueOf("2016-02-23 09:00:00.0")))
       }
     
    -  test("test timeseries table selection 14: Granularity only support 1 and 
throw Exception") {
    -    val e = intercept[MalformedCarbonCommandException] {
    -      sql(
    -        s"""
    -           | CREATE DATAMAP agg3_second ON TABLE mainTable
    -           | USING '$timeSeries'
    -           | DMPROPERTIES (
    -           | 'EVENT_TIME'='dataTime',
    -           | 'HOUR_GRANULARITY'='2')
    -           | AS SELECT dataTime, SUM(age) FROM mainTable
    -           | GROUP BY dataTime
    -       """.stripMargin)
    -    }
    -    assert(e.getMessage.contains("Granularity only support 1"))
    -  }
    -
    -  test("test timeseries table selection 15: Granularity only support 1 and 
throw Exception") {
    -    val e = intercept[MalformedCarbonCommandException] {
    -      sql(
    -        s"""
    -           | CREATE DATAMAP agg3_second ON TABLE mainTable
    -           | USING '$timeSeries'
    -           | DMPROPERTIES (
    -           | 'EVENT_TIME'='dataTime',
    -           | 'HOUR_GRANULARITY'='1.5')
    -           | AS SELECT dataTime, SUM(age) FROM mainTable
    -           | GROUP BY dataTime
    -       """.stripMargin)
    -    }
    -    assert(e.getMessage.contains("Granularity only support 1"))
    -  }
    -
    -  test("test timeseries table selection 16: Granularity only support 1 and 
throw Exception") {
    -    val e = intercept[MalformedCarbonCommandException] {
    -      sql(
    -        s"""
    -           | CREATE DATAMAP agg3_second ON TABLE mainTable
    -           | USING '$timeSeries'
    -           | DMPROPERTIES (
    -           | 'EVENT_TIME'='dataTime',
    -           | 'HOUR_GRANULARITY'='-1')
    -           | AS SELECT dataTime, SUM(age) FROM mainTable
    -           | GROUP BY dataTime
    -       """.stripMargin)
    -    }
    -    assert(e.getMessage.contains("Granularity only support 1"))
    +  test("test timeseries table selection 15: TIMESERIES(mytime,'hour') not 
match") {
    +    val df = sql(
    +      """
    +        | SELECT TIMESERIES(mytime,'hour')
    +        | FROM mainTable
    +        | WHERE TIMESERIES(mytime,'hour')='2016-02-23 09:01:00'
    +        | GROUP BY TIMESERIES(mytime,'hour')
    +      """.stripMargin)
    +    preAggTableValidator(df.queryExecution.analyzed, "maintable_agg0_hour")
    +    checkExistence(df, false, "2016-02-23 09:00:00", "2016-02-23 09:01:00")
    +  }
    +
    +  test("test timeseries table selection 16: TIMESERIES(mytime,'minute') 
match") {
    +    checkExistence(sql("SELECT * FROM mainTable"), true,
    +      "2016-02-23 09:01:30", "2016-02-23 09:02:40")
    +    checkExistence(sql("SELECT * FROM mainTable"), false,
    +      "2016-02-23 09:02:00", "2016-02-23 09:01:00")
    +    val df = sql(
    +      """
    +        |SELECT TIMESERIES(mytime,'minute') 
    +        |FROM mainTable 
    +        |GROUP BY TIMESERIES(mytime,'minute')
    +      """.stripMargin)
    +    preAggTableValidator(df.queryExecution.analyzed, 
"maintable_agg0_minute")
    +    checkExistence(df, true, "2016-02-23 09:02:00", "2016-02-23 09:01:00")
    +    checkAnswer(df,
    +      Seq(Row(Timestamp.valueOf("2016-02-23 09:02:00.0")),
    +        Row(Timestamp.valueOf("2016-02-23 09:01:00.0"))))
    +
    +    val df2 = sql(
    +      """
    +        | SELECT
    +        |   TIMESERIES(mytime,'minute')as minutelevel,
    +        |   SUM(age) AS SUM
    +        | FROM mainTable
    +        | WHERE TIMESERIES(mytime,'minute')='2016-02-23 09:01:00'
    +        | GROUP BY TIMESERIES(mytime,'minute')
    +        | ORDER BY TIMESERIES(mytime,'minute')
    +      """.stripMargin)
    +    preAggTableValidator(df2.queryExecution.analyzed, 
"maintable_agg0_minute")
    +    checkAnswer(df2, Seq(Row(Timestamp.valueOf("2016-02-23 09:01:00"), 
60)))
    +  }
    +
    +  test("test timeseries table selection 17: TIMESERIES(mytime,'minute') 
not match pre agg") {
    +    val df = sql(
    +      """
    +        | SELECT
    +        |   TIMESERIES(mytime,'minute')as minutelevel,
    +        |   SUM(age) AS SUM
    +        | FROM mainTable
    +        | WHERE TIMESERIES(mytime,'minute')='2016-02-23 09:01:00' AND 
name='vishal'
    +        | GROUP BY TIMESERIES(mytime,'minute')
    +        | ORDER BY TIMESERIES(mytime,'minute')
    +      """.stripMargin)
    +    checkAnswer(df, Seq(Row(Timestamp.valueOf("2016-02-23 09:01:00"), 10)))
    +    preAggTableValidator(df.queryExecution.analyzed, "maintable")
    +  }
    +
    +  test("test timeseries table selection 18: select with many GROUP BY AND 
one filter") {
    +    val df = sql(
    +      """
    +        | SELECT
    +        |   TIMESERIES(mytime,'year') AS yearLevel,
    +        |   TIMESERIES(mytime,'month') AS monthLevel,
    +        |   TIMESERIES(mytime,'day') AS dayLevel,
    +        |   TIMESERIES(mytime,'hour') AS hourLevel,
    +        |   TIMESERIES(mytime,'minute') AS minuteLevel,
    +        |   TIMESERIES(mytime,'second') AS secondLevel,
    +        |   SUM(age) AS SUM
    +        | FROM mainTable
    +        | WHERE TIMESERIES(mytime,'minute')='2016-02-23 09:01:00'
    +        | GROUP BY
    +        |   TIMESERIES(mytime,'year'),
    +        |   TIMESERIES(mytime,'month'),
    +        |   TIMESERIES(mytime,'day'),
    +        |   TIMESERIES(mytime,'hour'),
    +        |   TIMESERIES(mytime,'minute'),
    +        |   TIMESERIES(mytime,'second')
    +        | ORDER BY
    +        |   TIMESERIES(mytime,'year'),
    +        |   TIMESERIES(mytime,'month'),
    +        |   TIMESERIES(mytime,'day'),
    +        |   TIMESERIES(mytime,'hour'),
    +        |   TIMESERIES(mytime,'minute'),
    +        |   TIMESERIES(mytime,'second')
    +      """.stripMargin)
    +
    +    checkExistence(df, true,
    +      "2016-01-01 00:00:00",
    +      "2016-02-01 00:00:00",
    +      "2016-02-23 09:00:00",
    +      "2016-02-23 09:01:00",
    +      "2016-02-23 09:01:50",
    +      "30"
    +    )
    +  }
    +
    +  test("test timeseries table selection 19: select with many GROUP BY AND 
many filter") {
    +    val df = sql(
    +      """
    +        | SELECT
    +        |   TIMESERIES(mytime,'year') AS yearLevel,
    +        |   TIMESERIES(mytime,'month') AS monthLevel,
    +        |   TIMESERIES(mytime,'day') AS dayLevel,
    +        |   TIMESERIES(mytime,'hour') AS hourLevel,
    +        |   TIMESERIES(mytime,'minute') AS minuteLevel,
    +        |   TIMESERIES(mytime,'second') AS secondLevel,
    +        |   SUM(age) AS SUM
    +        | FROM mainTable
    +        | WHERE
    +        |   TIMESERIES(mytime,'second')='2016-02-23 09:01:50' and
    +        |   TIMESERIES(mytime,'minute')='2016-02-23 09:01:00' and
    +        |   TIMESERIES(mytime,'hour')='2016-02-23 09:00:00' and
    +        |   TIMESERIES(mytime,'month')='2016-02-01 00:00:00' and
    +        |   TIMESERIES(mytime,'year')='2016-01-01 00:00:00'
    +        | GROUP BY
    +        |   TIMESERIES(mytime,'year'),
    +        |   TIMESERIES(mytime,'month'),
    +        |   TIMESERIES(mytime,'day'),
    +        |   TIMESERIES(mytime,'hour'),
    +        |   TIMESERIES(mytime,'minute'),
    +        |   TIMESERIES(mytime,'second')
    +        | ORDER BY
    +        |   TIMESERIES(mytime,'year'),
    +        |   TIMESERIES(mytime,'month'),
    +        |   TIMESERIES(mytime,'day'),
    +        |   TIMESERIES(mytime,'hour'),
    +        |   TIMESERIES(mytime,'minute'),
    +        |   TIMESERIES(mytime,'second')
    +      """.stripMargin)
    +
    +    checkExistence(df, true,
    +      "2016-01-01 00:00:00",
    +      "2016-02-01 00:00:00",
    +      "2016-02-23 09:00:00",
    +      "2016-02-23 09:01:00",
    +      "2016-02-23 09:01:50",
    +      "30"
    +    )
    +  }
    +
    +  test("test timeseries table selection 20: filter < AND >") {
    --- End diff --
    
    different purpose, but I can remove this.


---

Reply via email to