You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@carbondata.apache.org by ja...@apache.org on 2016/12/16 02:38:06 UTC
[1/4] incubator-carbondata git commit: fixUnionIssue and add test case
Repository: incubator-carbondata
Updated Branches:
refs/heads/master 526243b09 -> f5ecfbf5c
http://git-wip-us.apache.org/repos/asf/incubator-carbondata/blob/462f6422/integration/spark2/src/test/scala/org/apache/spark/sql/common/util/CarbonFunSuite.scala
----------------------------------------------------------------------
diff --git a/integration/spark2/src/test/scala/org/apache/spark/sql/common/util/CarbonFunSuite.scala b/integration/spark2/src/test/scala/org/apache/spark/sql/common/util/CarbonFunSuite.scala
new file mode 100644
index 0000000..4647e78
--- /dev/null
+++ b/integration/spark2/src/test/scala/org/apache/spark/sql/common/util/CarbonFunSuite.scala
@@ -0,0 +1,49 @@
+/*
+ * 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.spark.sql.common.util
+
+import org.apache.carbondata.common.logging.LogServiceFactory
+import org.scalatest.{FunSuite, Outcome}
+
+
+private[spark] abstract class CarbonFunSuite extends FunSuite {
+
+ private val LOGGER = LogServiceFactory.getLogService(this.getClass.getCanonicalName)
+
+ /**
+ * Log the suite name and the test name before and after each test.
+ *
+ * Subclasses should never override this method. If they wish to run
+ * custom code before and after each test, they should should mix in
+ * the {{org.scalatest.BeforeAndAfter}} trait instead.
+ */
+ final protected override def withFixture(test: NoArgTest): Outcome = {
+ val testName = test.text
+ val suiteName = this.getClass.getName
+ val shortSuiteName = suiteName.replaceAll("org.apache.spark", "o.a.s")
+ try {
+ LOGGER.info(s"\n\n===== TEST OUTPUT FOR $shortSuiteName: '$testName' =====\n")
+ test()
+ } finally {
+ LOGGER.info(s"\n\n===== FINISHED $shortSuiteName: '$testName' =====\n")
+ }
+ }
+
+}
http://git-wip-us.apache.org/repos/asf/incubator-carbondata/blob/462f6422/integration/spark2/src/test/scala/org/apache/spark/sql/common/util/CarbonSessionTest.scala
----------------------------------------------------------------------
diff --git a/integration/spark2/src/test/scala/org/apache/spark/sql/common/util/CarbonSessionTest.scala b/integration/spark2/src/test/scala/org/apache/spark/sql/common/util/CarbonSessionTest.scala
new file mode 100644
index 0000000..d29196e
--- /dev/null
+++ b/integration/spark2/src/test/scala/org/apache/spark/sql/common/util/CarbonSessionTest.scala
@@ -0,0 +1,74 @@
+/*
+ * 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.spark.sql.common.util
+
+import java.io.File
+
+import org.apache.spark.sql.{DataFrame, SparkSession}
+import org.apache.carbondata.core.constants.CarbonCommonConstants
+import org.apache.carbondata.core.util.CarbonProperties
+import org.apache.commons.io.FileUtils
+
+object CarbonSessionTest extends{
+
+ val rootPath = new File(this.getClass.getResource("/").getPath
+ + "../../../..").getCanonicalPath
+ val storeLocation = s"$rootPath/examples/spark2/target/store"
+ val warehouse = s"$rootPath/examples/spark2/target/warehouse"
+ val metastoredb = s"$rootPath/examples/spark2/target/metastore_db"
+
+ val spark = {
+
+ // clean data folder
+ if (true) {
+ val clean = (path: String) => FileUtils.deleteDirectory(new File(path))
+ clean(storeLocation)
+ clean(warehouse)
+ clean(metastoredb)
+ }
+
+ val spark = SparkSession
+ .builder()
+ .master("local")
+ .appName("CarbonExample")
+ .enableHiveSupport()
+ .config("spark.sql.warehouse.dir", warehouse)
+ .config("javax.jdo.option.ConnectionURL",
+ s"jdbc:derby:;databaseName=$metastoredb;create=true")
+ .getOrCreate()
+
+ CarbonProperties.getInstance()
+ .addProperty("carbon.kettle.home", s"$rootPath/processing/carbonplugins")
+ .addProperty("carbon.storelocation", storeLocation)
+
+ spark.sparkContext.setLogLevel("WARN")
+
+ spark
+ }
+
+ val sc = spark.sparkContext
+
+ lazy val implicits = spark.implicits
+
+ def sql(sqlText: String): DataFrame = spark.sql(sqlText)
+
+}
+
+
http://git-wip-us.apache.org/repos/asf/incubator-carbondata/blob/462f6422/integration/spark2/src/test/scala/org/apache/spark/sql/common/util/PlanTest.scala
----------------------------------------------------------------------
diff --git a/integration/spark2/src/test/scala/org/apache/spark/sql/common/util/PlanTest.scala b/integration/spark2/src/test/scala/org/apache/spark/sql/common/util/PlanTest.scala
new file mode 100644
index 0000000..cdd415f
--- /dev/null
+++ b/integration/spark2/src/test/scala/org/apache/spark/sql/common/util/PlanTest.scala
@@ -0,0 +1,59 @@
+/*
+ * 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.spark.sql.common.util
+
+import org.apache.spark.sql.catalyst.expressions._
+import org.apache.spark.sql.catalyst.plans.logical.{Filter, LogicalPlan, OneRowRelation}
+import org.apache.spark.sql.catalyst.util._
+
+/**
+ * Provides helper methods for comparing plans.
+ */
+class PlanTest extends CarbonFunSuite {
+
+ /** Fails the test if the two expressions do not match */
+ protected def compareExpressions(e1: Expression, e2: Expression): Unit = {
+ comparePlans(Filter(e1, OneRowRelation), Filter(e2, OneRowRelation))
+ }
+
+ /** Fails the test if the two plans do not match */
+ protected def comparePlans(plan1: LogicalPlan, plan2: LogicalPlan) {
+ val normalized1 = normalizeExprIds(plan1)
+ val normalized2 = normalizeExprIds(plan2)
+ if (normalized1 != normalized2) {
+ fail(
+ s"""
+ |== FAIL: Plans do not match ===
+ |${sideBySide(normalized1.treeString, normalized2.treeString).mkString("\n")}
+ """.stripMargin)
+ }
+ }
+
+ /**
+ * Since attribute references are given globally unique ids during analysis,
+ * we must normalize them to check if two different queries are identical.
+ */
+ protected def normalizeExprIds(plan: LogicalPlan) = {
+ plan transformAllExpressions {
+ case a: AttributeReference =>
+ AttributeReference(a.name, a.dataType, a.nullable)(exprId = ExprId(0))
+ case a: Alias =>
+ Alias(a.child, a.name)(exprId = ExprId(0))
+ }
+ }
+}
http://git-wip-us.apache.org/repos/asf/incubator-carbondata/blob/462f6422/integration/spark2/src/test/scala/org/apache/spark/sql/common/util/QueryTest.scala
----------------------------------------------------------------------
diff --git a/integration/spark2/src/test/scala/org/apache/spark/sql/common/util/QueryTest.scala b/integration/spark2/src/test/scala/org/apache/spark/sql/common/util/QueryTest.scala
new file mode 100644
index 0000000..44d3bfa
--- /dev/null
+++ b/integration/spark2/src/test/scala/org/apache/spark/sql/common/util/QueryTest.scala
@@ -0,0 +1,149 @@
+/*
+ * 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.spark.sql.common.util
+
+import java.util.{Locale, TimeZone}
+
+import org.apache.carbondata.common.logging.LogServiceFactory
+
+import scala.collection.JavaConversions._
+import org.apache.spark.sql.catalyst.plans._
+import org.apache.spark.sql.catalyst.util._
+import org.apache.spark.sql.{DataFrame, Row, SQLContext}
+
+class QueryTest extends PlanTest {
+
+ val LOGGER = LogServiceFactory.getLogService(this.getClass.getCanonicalName)
+
+ // Timezone is fixed to America/Los_Angeles for those timezone sensitive tests (timestamp_*)
+ TimeZone.setDefault(TimeZone.getTimeZone("America/Los_Angeles"))
+ // Add Locale setting
+ Locale.setDefault(Locale.US)
+
+ /**
+ * Runs the plan and makes sure the answer contains all of the keywords, or the
+ * none of keywords are listed in the answer
+ * @param df the [[DataFrame]] to be executed
+ * @param exists true for make sure the keywords are listed in the output, otherwise
+ * to make sure none of the keyword are not listed in the output
+ * @param keywords keyword in string array
+ */
+ def checkExistence(df: DataFrame, exists: Boolean, keywords: String*) {
+ val outputs = df.collect().map(_.mkString).mkString
+ for (key <- keywords) {
+ if (exists) {
+ assert(outputs.contains(key), s"Failed for $df ($key doesn't exist in result)")
+ } else {
+ assert(!outputs.contains(key), s"Failed for $df ($key existed in the result)")
+ }
+ }
+ }
+
+ def sqlTest(sqlString: String, expectedAnswer: Seq[Row])(implicit sqlContext: SQLContext) {
+ test(sqlString) {
+ checkAnswer(sqlContext.sql(sqlString), expectedAnswer)
+ }
+ }
+
+ /**
+ * Runs the plan and makes sure the answer matches the expected result.
+ * @param df the [[DataFrame]] to be executed
+ * @param expectedAnswer the expected result in a [[Seq]] of [[Row]]s.
+ */
+ protected def checkAnswer(df: DataFrame, expectedAnswer: Seq[Row]): Unit = {
+ QueryTest.checkAnswer(df, expectedAnswer) match {
+ case Some(errorMessage) => fail(errorMessage)
+ case None =>
+ }
+ }
+
+ protected def checkAnswer(df: DataFrame, expectedAnswer: Row): Unit = {
+ checkAnswer(df, Seq(expectedAnswer))
+ }
+
+ protected def checkAnswer(df: DataFrame, expectedAnswer: DataFrame): Unit = {
+ checkAnswer(df, expectedAnswer.collect())
+ }
+}
+
+object QueryTest {
+ def checkAnswer(df: DataFrame, expectedAnswer: java.util.List[Row]): String = {
+ checkAnswer(df, expectedAnswer.toSeq) match {
+ case Some(errorMessage) => errorMessage
+ case None => null
+ }
+ }
+
+ /**
+ * Runs the plan and makes sure the answer matches the expected result.
+ * If there was exception during the execution or the contents of the DataFrame does not
+ * match the expected result, an error message will be returned. Otherwise, a [[None]] will
+ * be returned.
+ * @param df the [[DataFrame]] to be executed
+ * @param expectedAnswer the expected result in a [[Seq]] of [[Row]]s.
+ */
+ def checkAnswer(df: DataFrame, expectedAnswer: Seq[Row]): Option[String] = {
+ val isSorted = df.logicalPlan.collect { case s: logical.Sort => s }.nonEmpty
+ def prepareAnswer(answer: Seq[Row]): Seq[Row] = {
+ // Converts data to types that we can do equality comparison using Scala collections.
+ // For BigDecimal type, the Scala type has a better definition of equality test (similar to
+ // Java's java.math.BigDecimal.compareTo).
+ // For binary arrays, we convert it to Seq to avoid of calling java.util.Arrays.equals for
+ // equality test.
+ val converted: Seq[Row] = answer.map { s =>
+ Row.fromSeq(s.toSeq.map {
+ case d: java.math.BigDecimal => BigDecimal(d)
+ case b: Array[Byte] => b.toSeq
+ case o => o
+ })
+ }
+ if (!isSorted) converted.sortBy(_.toString()) else converted
+ }
+ val sparkAnswer = try df.collect().toSeq catch {
+ case e: Exception =>
+ val errorMessage =
+ s"""
+ |Exception thrown while executing query:
+ |${df.queryExecution}
+ |== Exception ==
+ |$e
+ |${org.apache.spark.sql.catalyst.util.stackTraceToString(e)}
+ """.stripMargin
+ return Some(errorMessage)
+ }
+
+ if (prepareAnswer(expectedAnswer) != prepareAnswer(sparkAnswer)) {
+ val errorMessage =
+ s"""
+ |Results do not match for query:
+ |${df.queryExecution}
+ |== Results ==
+ |${
+ sideBySide(
+ s"== Correct Answer - ${expectedAnswer.size} ==" +:
+ prepareAnswer(expectedAnswer).map(_.toString()),
+ s"== Spark Answer - ${sparkAnswer.size} ==" +:
+ prepareAnswer(sparkAnswer).map(_.toString())).mkString("\n")
+ }
+ """.stripMargin
+ return Some(errorMessage)
+ }
+
+ return None
+ }
+}
[2/4] incubator-carbondata git commit: fixUnionIssue and add test case
Posted by ja...@apache.org.
http://git-wip-us.apache.org/repos/asf/incubator-carbondata/blob/462f6422/integration/spark2/src/test/scala/org/apache/carbondata/spark/testsuite/allqueries/AllDataTypesTestCaseAggregate.scala
----------------------------------------------------------------------
diff --git a/integration/spark2/src/test/scala/org/apache/carbondata/spark/testsuite/allqueries/AllDataTypesTestCaseAggregate.scala b/integration/spark2/src/test/scala/org/apache/carbondata/spark/testsuite/allqueries/AllDataTypesTestCaseAggregate.scala
new file mode 100644
index 0000000..c8d5221
--- /dev/null
+++ b/integration/spark2/src/test/scala/org/apache/carbondata/spark/testsuite/allqueries/AllDataTypesTestCaseAggregate.scala
@@ -0,0 +1,1161 @@
+/*
+ * 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.carbondata.spark.testsuite.allqueries
+
+import java.io.File
+
+import org.apache.spark.sql.{Row, SaveMode}
+import org.apache.spark.sql.common.util.CarbonSessionTest._
+import org.apache.spark.sql.common.util.QueryTest
+import org.scalatest.BeforeAndAfterAll
+import org.apache.carbondata.core.constants.CarbonCommonConstants
+import org.apache.carbondata.core.util.CarbonProperties
+
+/**
+ * Test Class for all query on multiple datatypes
+ *
+ */
+class AllDataTypesTestCaseAggregate extends QueryTest with BeforeAndAfterAll {
+
+ override def beforeAll {
+ clean
+ val currentDirectory = new File(this.getClass.getResource("/").getPath + "/../../../spark")
+ .getCanonicalPath
+
+ sql("drop table if exists Carbon_automation_test")
+ sql("drop table if exists Carbon_automation_hive")
+ sql("drop table if exists Carbon_automation_test_hive")
+
+ sql("create table if not exists Carbon_automation_test (imei string,deviceInformationId int,MAC string,deviceColor string,device_backColor string,modelId string,marketName string,AMSize string,ROMSize string,CUPAudit string,CPIClocked string,series string,productionDate timestamp,bomCode string,internalModels string, deliveryTime string, channelsId string, channelsName string , deliveryAreaId string, deliveryCountry string, deliveryProvince string, deliveryCity string,deliveryDistrict string, deliveryStreet string, oxSingleNumber string,contractNumber int, ActiveCheckTime string, ActiveAreaId string, ActiveCountry string, ActiveProvince string, Activecity string, ActiveDistrict string, ActiveStreet string, ActiveOperatorId string, Active_releaseId string, Active_EMUIVersion string, Active_operaSysVersion string, Active_BacVerNumber string, Active_BacFlashVer string, Active_webUIVersion string, Active_webUITypeCarrVer string,Active_webTypeDataVerNumber string, Active_operatorsVer
sion string, Active_phonePADPartitionedVersions string, Latest_YEAR int, Latest_MONTH int, Latest_DAY int, Latest_HOUR string, Latest_areaId string, Latest_country string, Latest_province string, Latest_city string, Latest_district string, Latest_street string, Latest_releaseId string, Latest_EMUIVersion string, Latest_operaSysVersion string, Latest_BacVerNumber string, Latest_BacFlashVer string, Latest_webUIVersion string, Latest_webUITypeCarrVer string, Latest_webTypeDataVerNumber string, Latest_operatorsVersion string, Latest_phonePADPartitionedVersions string, Latest_operatorId string, gamePointId int,gamePointDescription string) USING org.apache.spark.sql.CarbonSource OPTIONS('dbName'='default', 'tableName'='Carbon_automation_test','DICTIONARY_INCLUDE'='Latest_MONTH,Latest_DAY,deviceInformationId')");
+ CarbonProperties.getInstance()
+ .addProperty(CarbonCommonConstants.CARBON_TIMESTAMP_FORMAT,CarbonCommonConstants.CARBON_TIMESTAMP_DEFAULT_FORMAT)
+
+ sql("create table if not exists Carbon_automation_hive (imei string,deviceInformationId int,MAC string,deviceColor string,device_backColor string,modelId string,marketName string,AMSize string,ROMSize string,CUPAudit string,CPIClocked string,series string,productionDate timestamp,bomCode string,internalModels string, deliveryTime string, channelsId string, channelsName string , deliveryAreaId string, deliveryCountry string, deliveryProvince string, deliveryCity string,deliveryDistrict string, deliveryStreet string, oxSingleNumber string,contractNumber int, ActiveCheckTime string, ActiveAreaId string, ActiveCountry string, ActiveProvince string, Activecity string, ActiveDistrict string, ActiveStreet string, ActiveOperatorId string, Active_releaseId string, Active_EMUIVersion string, Active_operaSysVersion string, Active_BacVerNumber string, Active_BacFlashVer string, Active_webUIVersion string, Active_webUITypeCarrVer string,Active_webTypeDataVerNumber string, Active_operatorsVer
sion string, Active_phonePADPartitionedVersions string, Latest_YEAR int, Latest_MONTH int, Latest_DAY int, Latest_HOUR string, Latest_areaId string, Latest_country string, Latest_province string, Latest_city string, Latest_district string, Latest_street string, Latest_releaseId string, Latest_EMUIVersion string, Latest_operaSysVersion string, Latest_BacVerNumber string, Latest_BacFlashVer string, Latest_webUIVersion string, Latest_webUITypeCarrVer string, Latest_webTypeDataVerNumber string, Latest_operatorsVersion string, Latest_phonePADPartitionedVersions string, Latest_operatorId string, gamePointId int,gamePointDescription string) row format delimited fields terminated by ','");
+ sql("LOAD DATA LOCAL INPATH '"+currentDirectory+"/src/test/resources/100_olap.csv' INTO table Carbon_automation_hive ");
+
+ //hive table
+ sql("create table if not exists Carbon_automation_test_hive (imei string,deviceInformationId int,MAC string,deviceColor string,device_backColor string,modelId string,marketName string,AMSize string,ROMSize string,CUPAudit string,CPIClocked string,series string,productionDate timestamp,bomCode string,internalModels string, deliveryTime string, channelsId string, channelsName string , deliveryAreaId string, deliveryCountry string, deliveryProvince string, deliveryCity string,deliveryDistrict string, deliveryStreet string, oxSingleNumber string,contractNumber int, ActiveCheckTime string, ActiveAreaId string, ActiveCountry string, ActiveProvince string, Activecity string, ActiveDistrict string, ActiveStreet string, ActiveOperatorId string, Active_releaseId string, Active_EMUIVersion string, Active_operaSysVersion string, Active_BacVerNumber string, Active_BacFlashVer string, Active_webUIVersion string, Active_webUITypeCarrVer string,Active_webTypeDataVerNumber string, Active_operato
rsVersion string, Active_phonePADPartitionedVersions string, Latest_YEAR int, Latest_MONTH int, Latest_DAY int, Latest_HOUR string, Latest_areaId string, Latest_country string, Latest_province string, Latest_city string, Latest_district string, Latest_street string, Latest_releaseId string, Latest_EMUIVersion string, Latest_operaSysVersion string, Latest_BacVerNumber string, Latest_BacFlashVer string, Latest_webUIVersion string, Latest_webUITypeCarrVer string, Latest_webTypeDataVerNumber string, Latest_operatorsVersion string, Latest_phonePADPartitionedVersions string, Latest_operatorId string, gamePointId int,gamePointDescription string)row format delimited fields terminated by ','");
+ sql("LOAD DATA LOCAL INPATH '"+currentDirectory+"/src/test/resources/100_olap.csv' INTO table Carbon_automation_test_hive");
+
+ sql("INSERT INTO table Carbon_automation_test select * from Carbon_automation_test_hive");
+ }
+
+ def clean{
+ sql("drop table if exists Carbon_automation_test")
+ sql("drop table if exists Carbon_automation_hive")
+ sql("drop table if exists Carbon_automation_test_hive")
+ }
+
+ override def afterAll {
+ clean
+ CarbonProperties.getInstance()
+ .addProperty(CarbonCommonConstants.CARBON_TIMESTAMP_FORMAT, "dd-MM-yyyy")
+ }
+
+ //Test-22
+ test("select channelsId, sum(Latest_DAY+ 10) as a from Carbon_automation_test group by channelsId") {
+ checkAnswer(
+ sql("select channelsId, sum(Latest_DAY+ 10) as a from Carbon_automation_test group by channelsId"),
+ Seq(Row("1", 132), Row("2", 110), Row("3", 176), Row("4", 132), Row("5", 132), Row("6", 209), Row("7", 198)))
+
+ }
+
+ test("select channelsId, Latest_DAY from Carbon_automation_test where count(channelsId) = 1") {
+ try {
+ sql("select channelsId, Latest_DAY from Carbon_automation_test where count(channelsId) = 1").collect
+ } catch {
+ case ce: UnsupportedOperationException => ce.getMessage
+ case ce: Exception => ce.getMessage
+ }
+ }
+
+ //Test-24
+ test("select channelsId, sum(channelsId+ 10) Total from Carbon_automation_test group by channelsId order by Total") {
+ checkAnswer(
+ sql("select channelsId, sum(channelsId+ 10) Total from Carbon_automation_test group by channelsId order by Total"),
+ Seq(Row("2", 120), Row("1", 132), Row("4", 168), Row("5", 180), Row("3", 208), Row("6", 304), Row("7", 306)))
+
+ }
+
+ //Test-25
+ test("select channelsId, sum(channelsId+channelsId) Total from Carbon_automation_test group by channelsId order by Total") {
+ checkAnswer(
+ sql("select channelsId, sum(channelsId+channelsId) Total from Carbon_automation_test group by channelsId order by Total"),
+ Seq(Row("1", 24), Row("2", 40), Row("3", 96), Row("4", 96), Row("5", 120), Row("6", 228), Row("7", 252)))
+
+ }
+
+ //Test-26
+ test("select channelsId, sum(channelsId+channelsId) Total from Carbon_automation_test group by channelsId") {
+ checkAnswer(
+ sql("select channelsId, sum(channelsId+channelsId) Total from Carbon_automation_test group by channelsId"),
+ Seq(Row("1", 24), Row("2", 40), Row("3", 96), Row("4", 96), Row("5", 120), Row("6", 228), Row("7", 252)))
+
+ }
+
+ //Test-27
+ test("select channelsId, avg(Latest_DAY+ 10) as a from Carbon_automation_test group by channelsId") {
+ checkAnswer(
+ sql("select channelsId, avg(Latest_DAY+ 10) as a from Carbon_automation_test group by channelsId"),
+ Seq(Row("1", 11), Row("2", 11), Row("3", 11), Row("4", 11), Row("5", 11), Row("6", 11), Row("7", 11)))
+
+ }
+
+ //Test-29
+ test("select channelsId, avg(channelsId+ 10) Total from Carbon_automation_test group by channelsId order by Total") {
+ checkAnswer(
+ sql("select channelsId, avg(channelsId+ 10) Total from Carbon_automation_test group by channelsId order by Total"),
+ Seq(Row("1", 11), Row("2", 12), Row("3", 13), Row("4", 14), Row("5", 15), Row("6", 16), Row("7", 17)))
+
+ }
+
+
+ //Test-30
+ test("select channelsId, avg(channelsId+channelsId) Total from Carbon_automation_test group by channelsId order by Total") {
+ checkAnswer(
+ sql("select channelsId, avg(channelsId+channelsId) Total from Carbon_automation_test group by channelsId order by Total"),
+ Seq(Row("1", 2), Row("2", 4), Row("3", 6), Row("4", 8), Row("5", 10), Row("6", 12), Row("7", 14)))
+
+ }
+
+ //Test-31
+ test("select channelsId, count(Latest_DAY+ 10) as a from Carbon_automation_test group by channelsId") {
+ checkAnswer(
+ sql("select channelsId, count(Latest_DAY+ 10) as a from Carbon_automation_test group by channelsId"),
+ Seq(Row("1", 12), Row("2", 10), Row("3", 16), Row("4", 12), Row("5", 12), Row("6", 19), Row("7", 18)))
+
+ }
+
+
+ //Test-33
+ test("select channelsId, count(channelsId+ 10) Total from Carbon_automation_test group by channelsId order by channelsId") {
+ checkAnswer(
+ sql("select channelsId, count(channelsId+ 10) Total from Carbon_automation_test group by channelsId order by channelsId"),
+ Seq(Row("1", 12), Row("2", 10), Row("3", 16), Row("4", 12), Row("5", 12), Row("6", 19), Row("7", 18)))
+
+ }
+
+ //Test-34
+ test("select channelsId, count(channelsId+channelsId) Total from Carbon_automation_test group by channelsId order by channelsId") {
+ checkAnswer(
+ sql("select channelsId, count(channelsId+channelsId) Total from Carbon_automation_test group by channelsId order by channelsId"),
+ Seq(Row("1", 12), Row("2", 10), Row("3", 16), Row("4", 12), Row("5", 12), Row("6", 19), Row("7", 18)))
+
+ }
+
+ //Test-35
+ test("select channelsId, min(Latest_DAY+ 10) as a from Carbon_automation_test group by channelsId") {
+ checkAnswer(
+ sql("select channelsId, min(Latest_DAY+ 10) as a from Carbon_automation_test group by channelsId"),
+ Seq(Row("1", 11), Row("2", 11), Row("3", 11), Row("4", 11), Row("5", 11), Row("6", 11), Row("7", 11)))
+
+ }
+
+
+ //Test-37
+ test("select channelsId, min(channelsId+ 10) Total from Carbon_automation_test group by channelsId order by Total") {
+ checkAnswer(
+ sql("select channelsId, min(channelsId+ 10) Total from Carbon_automation_test group by channelsId order by Total"),
+ Seq(Row("1", 11), Row("2", 12), Row("3", 13), Row("4", 14), Row("5", 15), Row("6", 16), Row("7", 17)))
+
+ }
+
+ //Test-38
+ test("select channelsId, min(channelsId+channelsId) Total from Carbon_automation_test group by channelsId order by Total") {
+ checkAnswer(
+ sql("select channelsId, min(channelsId+channelsId) Total from Carbon_automation_test group by channelsId order by Total"),
+ Seq(Row("1", 2), Row("2", 4), Row("3", 6), Row("4", 8), Row("5", 10), Row("6", 12), Row("7", 14)))
+
+ }
+
+ //Test-39
+ test("select channelsId, max(Latest_DAY+ 10) as a from Carbon_automation_test group by channelsId") {
+ checkAnswer(
+ sql("select channelsId, max(Latest_DAY+ 10) as a from Carbon_automation_test group by channelsId"),
+ Seq(Row("1", 11), Row("2", 11), Row("3", 11), Row("4", 11), Row("5", 11), Row("6", 11), Row("7", 11)))
+
+ }
+
+
+ //Test-41
+ test("select channelsId, max(channelsId+ 10) Total from Carbon_automation_test group by channelsId order by Total")({
+
+ checkAnswer(
+ sql("select channelsId, max(channelsId+ 10) Total from Carbon_automation_test group by channelsId order by Total"),
+ Seq(Row("1", 11), Row("2", 12), Row("3", 13), Row("4", 14), Row("5", 15), Row("6", 16), Row("7", 17)))
+ })
+
+ //Test-42
+ test("select channelsId, max(channelsId+channelsId) Total from Carbon_automation_test group by channelsId order by Total")({
+
+ checkAnswer(
+ sql("select channelsId, max(channelsId+channelsId) Total from Carbon_automation_test group by channelsId order by Total"),
+ Seq(Row("1", 2), Row("2", 4), Row("3", 6), Row("4", 8), Row("5", 10), Row("6", 12), Row("7", 14)))
+ })
+
+ //Test-43
+ test("select Latest_YEAR ,sum(distinct Latest_YEAR)+10 from Carbon_automation_test group by Latest_YEAR")({
+
+ checkAnswer(
+ sql("select Latest_YEAR ,sum(distinct Latest_YEAR)+10 from Carbon_automation_test group by Latest_YEAR"),
+ Seq(Row(2015, 2025)))
+ })
+
+ //Test-44
+
+ test("select Latest_YEAR ,sum(distinct Latest_YEAR)+10 from Carbon_automation_test group by Latest_YEAR.")({
+
+ checkAnswer(
+ sql("select Latest_YEAR ,sum(distinct Latest_YEAR)+10 from Carbon_automation_test group by Latest_YEAR"),
+ Seq(Row(2015, 2025)))
+ })
+
+ //Test-47
+ test("select sum(gamepointid) +10 as a ,series from Carbon_automation_test group by series")({
+
+ checkAnswer(
+ sql("select sum(gamepointid) +10 as a ,series from Carbon_automation_test group by series"),
+ sql("select sum(gamepointid) +10 as a ,series from Carbon_automation_test_hive group by series"))
+ })
+
+ //Test-50
+ test("select sum(gamepointid) +10.36 as a ,series from Carbon_automation_test group by series")({
+
+ checkAnswer(
+ sql("select sum(gamepointid) +10.36 as a ,series from Carbon_automation_test group by series"),
+ sql("select sum(gamepointid) +10.36 as a ,series from Carbon_automation_test_hive group by series"))
+ })
+
+ //TC_055
+ test("select count(deviceinformationid)+10.32 as a ,series from Carbon_automation_test group by series")({
+ checkAnswer(
+ sql("select count(deviceinformationid)+10.32 as a ,series from Carbon_automation_test group by series"),
+ Seq(Row(19.32, "6Series"), Row(25.32, "0Series"), Row(18.32, "4Series"), Row(21.32, "8Series"), Row(21.32, "7Series"), Row(13.32, "1Series"), Row(27.32, "5Series"), Row(18.32, "9Series"), Row(18.32, "3Series"), Row(19.32, "2Series")))
+ })
+
+ //TC_056
+ test("select count(gamepointid) +10.36 as a ,series from Carbon_automation_test group by series")({
+ checkAnswer(
+ sql("select count(gamepointid) +10.36 as a ,series from Carbon_automation_test group by series"),
+ Seq(Row(19.36, "6Series"), Row(25.36, "0Series"), Row(18.36, "4Series"), Row(21.36, "8Series"), Row(21.36, "7Series"), Row(13.36, "1Series"), Row(27.36, "5Series"), Row(18.36, "9Series"), Row(18.36, "3Series"), Row(19.36, "2Series")))
+ })
+
+ //TC_057
+ test("select count(latest_year)+10.364 as a,series from Carbon_automation_test group by series")({
+ checkAnswer(
+ sql("select count(latest_year)+10.364 as a,series from Carbon_automation_test group by series"),
+ Seq(Row(19.364, "6Series"), Row(25.364, "0Series"), Row(18.364, "4Series"), Row(21.364, "8Series"), Row(21.364, "7Series"), Row(13.364, "1Series"), Row(27.364, "5Series"), Row(18.364, "9Series"), Row(18.364, "3Series"), Row(19.364, "2Series")))
+ })
+
+ //TC_058
+ test("select count(distinct series)+10 as a,series from Carbon_automation_test group by series")({
+ checkAnswer(
+ sql("select count(distinct series)+10 as a,series from Carbon_automation_test group by series"),
+ Seq(Row(11, "6Series"), Row(11, "0Series"), Row(11, "4Series"), Row(11, "8Series"), Row(11, "7Series"), Row(11, "1Series"), Row(11, "5Series"), Row(11, "9Series"), Row(11, "3Series"), Row(11, "2Series")))
+ })
+ //TC_060
+ test("select count(*) as a from Carbon_automation_test")({
+ checkAnswer(
+ sql("select count(*) as a from Carbon_automation_test"),
+ Seq(Row(99)))
+ })
+
+ //TC_061
+ test("Select count(1) as a from Carbon_automation_test")({
+ checkAnswer(
+ sql("Select count(1) as a from Carbon_automation_test"),
+ Seq(Row(99)))
+ })
+
+ //TC_062
+ test("select count(imei) as a from Carbon_automation_test")({
+ checkAnswer(
+ sql("select count(imei) as a from Carbon_automation_test"),
+ Seq(Row(99)))
+ })
+
+ //TC_063
+ test("select count(device_backColor) as a from Carbon_automation_test")({
+ checkAnswer(
+ sql("select count(device_backColor) as a from Carbon_automation_test"),
+ Seq(Row(99)))
+ })
+
+ //TC_064
+ test("select count(DISTINCT imei) as a from Carbon_automation_test")({
+ checkAnswer(
+ sql("select count(DISTINCT imei) as a from Carbon_automation_test"),
+ Seq(Row(99)))
+ })
+
+ //TC_065
+ test("select count(DISTINCT series) as a from Carbon_automation_test")({
+ checkAnswer(
+ sql("select count(DISTINCT series) as a from Carbon_automation_test"),
+ Seq(Row(10)))
+ })
+
+ //TC_066
+ test("select count(DISTINCT device_backColor) as a from Carbon_automation_test")({
+ checkAnswer(
+ sql("select count(DISTINCT device_backColor) as a from Carbon_automation_test"),
+ Seq(Row(10)))
+ })
+
+ //TC_067
+ test("select count (if(deviceInformationId>100,NULL,deviceInformationId)) a from Carbon_automation_test")({
+ checkAnswer(
+ sql("select count (if(deviceInformationId>100,NULL,deviceInformationId)) a from Carbon_automation_test"),
+ Seq(Row(3)))
+ })
+ //TC_069
+ test("select count(gamePointId) as a from Carbon_automation_test")({
+ checkAnswer(
+ sql("select count(gamePointId) as a from Carbon_automation_test"),
+ Seq(Row(99)))
+ })
+ //TC_071
+ test("select sum(gamePointId) a from Carbon_automation_test")({
+ checkAnswer(
+ sql("select sum(gamePointId) a from Carbon_automation_test"),
+ sql("select sum(gamePointId) a from Carbon_automation_test_hive"))
+ })
+ //TC_077
+ test("select sum(DISTINCT deviceInformationId) a from Carbon_automation_test")({
+ checkAnswer(
+ sql("select sum(DISTINCT deviceInformationId) a from Carbon_automation_test"),
+ Seq(Row(9594717)))
+ })
+ //TC_080
+ test("select sum (if(deviceInformationId>100,NULL,deviceInformationId)) a from Carbon_automation_test")({
+ checkAnswer(
+ sql("select sum (if(deviceInformationId>100,NULL,deviceInformationId)) a from Carbon_automation_test"),
+ Seq(Row(111)))
+ })
+
+ //TC_081
+ test("select sum( DISTINCT Latest_MONTH) a from Carbon_automation_test")({
+ checkAnswer(
+ sql("select sum( DISTINCT Latest_MONTH) a from Carbon_automation_test"),
+ Seq(Row(7)))
+ })
+
+ //TC_088
+ test("select avg (if(deviceInformationId>100,NULL,deviceInformationId)) a from Carbon_automation_test")({
+ checkAnswer(
+ sql("select avg (if(deviceInformationId>100,NULL,deviceInformationId)) a from Carbon_automation_test"),
+ Seq(Row(37.0)))
+ })
+ //TC_090
+ test("select min(deviceInformationId) a from Carbon_automation_test")({
+ checkAnswer(
+ sql("select min(deviceInformationId) a from Carbon_automation_test"),
+ Seq(Row(1)))
+ })
+
+ //TC_091
+ test("select min(channelsId) a from Carbon_automation_test")({
+ checkAnswer(
+ sql("select min(channelsId) a from Carbon_automation_test"),
+ Seq(Row("1")))
+ })
+
+ //TC_092
+ test("select min(bomCode) a from Carbon_automation_test")({
+ checkAnswer(
+ sql("select min(bomCode) a from Carbon_automation_test"),
+ Seq(Row("1")))
+ })
+
+ //TC_093
+ test("select min(Latest_MONTH) a from Carbon_automation_test")({
+ checkAnswer(
+ sql("select min(Latest_MONTH) a from Carbon_automation_test"),
+ Seq(Row(7)))
+ })
+
+ //TC_095
+ test("select min (if(deviceInformationId>100,NULL,deviceInformationId)) a from Carbon_automation_test")({
+ checkAnswer(
+ sql("select min (if(deviceInformationId>100,NULL,deviceInformationId)) a from Carbon_automation_test"),
+ Seq(Row(1)))
+ })
+ //TC_097
+ test("select max(deviceInformationId) a from Carbon_automation_test")({
+ checkAnswer(
+ sql("select max(deviceInformationId) a from Carbon_automation_test"),
+ Seq(Row(1000000)))
+ })
+
+ //TC_098
+ test("select max(channelsId) a from Carbon_automation_test")({
+ checkAnswer(
+ sql("select max(channelsId) a from Carbon_automation_test"),
+ Seq(Row("7")))
+ })
+
+ //TC_099
+ test("select max(bomCode) a from Carbon_automation_test")({
+ checkAnswer(
+ sql("select max(bomCode) a from Carbon_automation_test"),
+ Seq(Row("100084")))
+ })
+
+ //TC_100
+ test("select max(Latest_MONTH) a from Carbon_automation_test")({
+ checkAnswer(
+ sql("select max(Latest_MONTH) a from Carbon_automation_test"),
+ Seq(Row(7)))
+ })
+
+ //TC_102
+ test("select max (if(deviceInformationId>100,NULL,deviceInformationId)) a from Carbon_automation_test")({
+ checkAnswer(
+ sql("select max (if(deviceInformationId>100,NULL,deviceInformationId)) a from Carbon_automation_test"),
+ Seq(Row(100)))
+ })
+
+ //TC_103
+ test("select variance(deviceInformationId) as a from carbon_automation_test")({
+ checkAnswer(
+ sql("select variance(deviceInformationId) as a from carbon_automation_test"),
+ sql("select variance(deviceInformationId) as a from carbon_automation_hive"))
+ })
+ //TC_105
+ test("select var_samp(deviceInformationId) as a from Carbon_automation_test")({
+ checkAnswer(
+ sql("select var_samp(deviceInformationId) as a from Carbon_automation_test"),
+ Seq(Row(9.405419800040813E9)))
+ })
+
+ //TC_106
+ test("select stddev_pop(deviceInformationId) as a from Carbon_automation_test")({
+ checkAnswer(
+ sql("select stddev_pop(deviceInformationId) as a from Carbon_automation_test"),
+ Seq(Row(96490.49465950707)))
+ })
+
+ //TC_107
+ test("select stddev_samp(deviceInformationId) as a from Carbon_automation_test")({
+ checkAnswer(
+ sql("select stddev_samp(deviceInformationId) as a from Carbon_automation_test"),
+ Seq(Row(96981.54360516652)))
+ })
+
+ //TC_108
+ test("select covar_pop(deviceInformationId,deviceInformationId) as a from Carbon_automation_test")({
+ checkAnswer(
+ sql("select covar_pop(deviceInformationId,deviceInformationId) as a from Carbon_automation_test"),
+ Seq(Row(9310415559.636362)))
+ })
+
+ //TC_109
+ test("select covar_samp(deviceInformationId,deviceInformationId) as a from Carbon_automation_test")({
+ checkAnswer(
+ sql("select covar_samp(deviceInformationId,deviceInformationId) as a from Carbon_automation_test"),
+ Seq(Row(9.405419800040813E9)))
+ })
+
+ //TC_110
+ test("select corr(deviceInformationId,deviceInformationId) as a from Carbon_automation_test")({
+ checkAnswer(
+ sql("select corr(deviceInformationId,deviceInformationId) as a from Carbon_automation_test"),
+ sql("select corr(deviceInformationId,deviceInformationId) as a from Carbon_automation_hive"))
+ })
+
+ //TC_111
+ test("select percentile(deviceInformationId,0.2) as a from Carbon_automation_test")({
+ checkAnswer(
+ sql("select percentile(deviceInformationId,0.2) as a from Carbon_automation_test"),
+ Seq(Row(100006.6)))
+ })
+
+ //TC_113
+ test("select percentile_approx(deviceInformationId,0.2) as a from Carbon_automation_test")({
+ checkAnswer(
+ sql("select percentile_approx(deviceInformationId,0.2) as a from Carbon_automation_test"),
+ Seq(Row(100005.8)))
+ })
+ //TC_127
+ test("select percentile(deviceInformationId,0.2) as a from Carbon_automation_test1")({
+ checkAnswer(
+ sql("select percentile(deviceInformationId,0.2) as a from Carbon_automation_test"),
+ Seq(Row(100006.6)))
+ })
+ //TC_134
+ test("select last(imei) a from Carbon_automation_test")({
+ checkAnswer(
+ sql("select last(imei) a from Carbon_automation_test"),
+ Seq(Row("1AA100084")))
+ })
+ //TC_136
+ test("select series,count(imei) a from Carbon_automation_test group by series order by series")({
+ checkAnswer(
+ sql("select series,count(imei) a from Carbon_automation_test group by series order by series"),
+ Seq(Row("0Series", 15), Row("1Series", 3), Row("2Series", 9), Row("3Series", 8), Row("4Series", 8), Row("5Series", 17), Row("6Series", 9), Row("7Series", 11), Row("8Series", 11), Row("9Series", 8)))
+ })
+
+ //TC_138
+ test("select series,ActiveProvince,count(imei) a from Carbon_automation_test group by ActiveProvince,series order by series,ActiveProvince")({
+ checkAnswer(
+ sql("select series,ActiveProvince,count(imei) a from Carbon_automation_test group by ActiveProvince,series order by series,ActiveProvince"),
+ Seq(Row("0Series", "Guangdong Province", 1), Row("0Series", "Hubei Province", 5), Row("0Series", "Hunan Province", 9), Row("1Series", "Guangdong Province", 2), Row("1Series", "Hunan Province", 1), Row("2Series", "Hubei Province", 3), Row("2Series", "Hunan Province", 6), Row("3Series", "Guangdong Province", 2), Row("3Series", "Hubei Province", 2), Row("3Series", "Hunan Province", 4), Row("4Series", "Guangdong Province", 1), Row("4Series", "Hubei Province", 1), Row("4Series", "Hunan Province", 6), Row("5Series", "Guangdong Province", 5), Row("5Series", "Hubei Province", 3), Row("5Series", "Hunan Province", 9), Row("6Series", "Guangdong Province", 1), Row("6Series", "Hubei Province", 4), Row("6Series", "Hunan Province", 4), Row("7Series", "Guangdong Province", 5), Row("7Series", "Hubei Province", 1), Row("7Series", "Hunan Province", 5), Row("8Series", "Guangdong Province", 2), Row("8Series", "Hubei Province", 6), Row("8Series", "Hunan Province", 3), Row("9Series", "Guangdong Prov
ince", 1), Row("9Series", "Hubei Province", 3), Row("9Series", "Hunan Province", 4)))
+ })
+
+ //TC_139
+ test("select count(distinct deviceColor) a,deliveryProvince from Carbon_automation_test group by deliveryProvince")({
+ checkAnswer(
+ sql("select count(distinct deviceColor) a,deliveryProvince from Carbon_automation_test group by deliveryProvince"),
+ Seq(Row(10, "Hunan Province"), Row(10, "Guangdong Province"), Row(10, "Hubei Province")))
+ })
+
+ //TC_141
+ test("select series,sum(gamePointId) a from Carbon_automation_test group by series order by series")({
+ checkAnswer(
+ sql("select series,sum(gamePointId) a from Carbon_automation_test group by series order by series"),
+ sql("select series,sum(gamePointId) a from Carbon_automation_test_hive group by series order by series"))
+ })
+
+ //TC_162
+ test("select imei,series from Carbon_automation_test where Carbon_automation_test.series IN ('1Series','7Series')")({
+ checkAnswer(
+ sql("select imei,series from Carbon_automation_test where Carbon_automation_test.series IN ('1Series','7Series')"),
+ Seq(Row("1AA1", "7Series"), Row("1AA10", "7Series"), Row("1AA10000", "7Series"), Row("1AA1000000", "7Series"), Row("1AA100005", "1Series"), Row("1AA100013", "1Series"), Row("1AA100026", "7Series"), Row("1AA10003", "7Series"), Row("1AA100030", "7Series"), Row("1AA100031", "7Series"), Row("1AA100032", "1Series"), Row("1AA100037", "7Series"), Row("1AA100054", "7Series"), Row("1AA100055", "7Series")))
+ })
+
+ //TC_163
+ test("select imei,series from Carbon_automation_test where Carbon_automation_test.series NOT IN ('1Series','7Series')")({
+ checkAnswer(
+ sql("select imei,series from Carbon_automation_test where Carbon_automation_test.series NOT IN ('1Series','7Series')"),
+ Seq(Row("1AA100", "5Series"), Row("1AA1000", "5Series"), Row("1AA100000", "9Series"), Row("1AA100001", "0Series"), Row("1AA100002", "0Series"), Row("1AA100003", "5Series"), Row("1AA100004", "4Series"), Row("1AA100006", "6Series"), Row("1AA100007", "9Series"), Row("1AA100008", "8Series"), Row("1AA100009", "0Series"), Row("1AA10001", "2Series"), Row("1AA100010", "3Series"), Row("1AA100011", "0Series"), Row("1AA100012", "4Series"), Row("1AA100014", "5Series"), Row("1AA100015", "4Series"), Row("1AA100016", "3Series"), Row("1AA100017", "9Series"), Row("1AA100018", "8Series"), Row("1AA100019", "5Series"), Row("1AA10002", "0Series"), Row("1AA100020", "5Series"), Row("1AA100021", "0Series"), Row("1AA100022", "5Series"), Row("1AA100023", "5Series"), Row("1AA100024", "6Series"), Row("1AA100025", "0Series"), Row("1AA100027", "0Series"), Row("1AA100028", "5Series"), Row("1AA100029", "2Series"), Row("1AA100033", "8Series"), Row("1AA100034", "2Series"), Row("1AA100035", "5Series"), Row("1AA
100036", "5Series"), Row("1AA100038", "6Series"), Row("1AA100039", "8Series"), Row("1AA10004", "5Series"), Row("1AA100040", "8Series"), Row("1AA100041", "5Series"), Row("1AA100042", "3Series"), Row("1AA100043", "9Series"), Row("1AA100044", "8Series"), Row("1AA100045", "2Series"), Row("1AA100046", "3Series"), Row("1AA100047", "9Series"), Row("1AA100048", "3Series"), Row("1AA100049", "0Series"), Row("1AA10005", "8Series"), Row("1AA100050", "2Series"), Row("1AA100051", "2Series"), Row("1AA100052", "6Series"), Row("1AA100053", "2Series"), Row("1AA100056", "6Series"), Row("1AA100057", "9Series"), Row("1AA100058", "5Series"), Row("1AA100059", "4Series"), Row("1AA10006", "3Series"), Row("1AA100060", "8Series"), Row("1AA100061", "6Series"), Row("1AA100062", "9Series"), Row("1AA100063", "2Series"), Row("1AA100064", "6Series"), Row("1AA100065", "0Series"), Row("1AA100066", "6Series"), Row("1AA100067", "4Series"), Row("1AA100068", "8Series"), Row("1AA100069", "8Series"), Row("1AA10007", "8Seri
es"), Row("1AA100070", "0Series"), Row("1AA100071", "0Series"), Row("1AA100072", "4Series"), Row("1AA100073", "4Series"), Row("1AA100074", "6Series"), Row("1AA100075", "3Series"), Row("1AA100076", "0Series"), Row("1AA100077", "3Series"), Row("1AA100078", "2Series"), Row("1AA100079", "4Series"), Row("1AA10008", "5Series"), Row("1AA100080", "9Series"), Row("1AA100081", "5Series"), Row("1AA100082", "5Series"), Row("1AA100083", "0Series"), Row("1AA100084", "0Series")))
+ })
+
+ //TC_166
+ test("select Upper(series) a from Carbon_automation_test")({
+ checkAnswer(
+ sql("select Upper(series) a from Carbon_automation_test"),
+ Seq(Row("7SERIES"), Row("7SERIES"), Row("5SERIES"), Row("5SERIES"), Row("7SERIES"), Row("9SERIES"), Row("7SERIES"), Row("0SERIES"), Row("0SERIES"), Row("5SERIES"), Row("4SERIES"), Row("1SERIES"), Row("6SERIES"), Row("9SERIES"), Row("8SERIES"), Row("0SERIES"), Row("2SERIES"), Row("3SERIES"), Row("0SERIES"), Row("4SERIES"), Row("1SERIES"), Row("5SERIES"), Row("4SERIES"), Row("3SERIES"), Row("9SERIES"), Row("8SERIES"), Row("5SERIES"), Row("0SERIES"), Row("5SERIES"), Row("0SERIES"), Row("5SERIES"), Row("5SERIES"), Row("6SERIES"), Row("0SERIES"), Row("7SERIES"), Row("0SERIES"), Row("5SERIES"), Row("2SERIES"), Row("7SERIES"), Row("7SERIES"), Row("7SERIES"), Row("1SERIES"), Row("8SERIES"), Row("2SERIES"), Row("5SERIES"), Row("5SERIES"), Row("7SERIES"), Row("6SERIES"), Row("8SERIES"), Row("5SERIES"), Row("8SERIES"), Row("5SERIES"), Row("3SERIES"), Row("9SERIES"), Row("8SERIES"), Row("2SERIES"), Row("3SERIES"), Row("9SERIES"), Row("3SERIES"), Row("0SERIES"), Row("8SERIES"), Row("2SERIE
S"), Row("2SERIES"), Row("6SERIES"), Row("2SERIES"), Row("7SERIES"), Row("7SERIES"), Row("6SERIES"), Row("9SERIES"), Row("5SERIES"), Row("4SERIES"), Row("3SERIES"), Row("8SERIES"), Row("6SERIES"), Row("9SERIES"), Row("2SERIES"), Row("6SERIES"), Row("0SERIES"), Row("6SERIES"), Row("4SERIES"), Row("8SERIES"), Row("8SERIES"), Row("8SERIES"), Row("0SERIES"), Row("0SERIES"), Row("4SERIES"), Row("4SERIES"), Row("6SERIES"), Row("3SERIES"), Row("0SERIES"), Row("3SERIES"), Row("2SERIES"), Row("4SERIES"), Row("5SERIES"), Row("9SERIES"), Row("5SERIES"), Row("5SERIES"), Row("0SERIES"), Row("0SERIES")))
+ })
+
+ //TC_167
+ test("select Upper(Latest_DAY) a from Carbon_automation_test")({
+ checkAnswer(
+ sql("select Upper(Latest_DAY) a from Carbon_automation_test"),
+ Seq(Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"
)))
+ })
+
+ //TC_168
+ test("select imei,series from Carbon_automation_test limit 10")({
+ checkAnswer(
+ sql("select imei,series from Carbon_automation_test limit 10"),
+ Seq(Row("1AA1", "7Series"), Row("1AA10", "7Series"), Row("1AA100", "5Series"), Row("1AA1000", "5Series"), Row("1AA10000", "7Series"), Row("1AA100000", "9Series"), Row("1AA1000000", "7Series"), Row("1AA100001", "0Series"), Row("1AA100002", "0Series"), Row("1AA100003", "5Series")))
+ })
+
+ //TC_171
+ test("select Lower(series) a from Carbon_automation_test")({
+ checkAnswer(
+ sql("select Lower(series) a from Carbon_automation_test"),
+ Seq(Row("7series"), Row("7series"), Row("5series"), Row("5series"), Row("7series"), Row("9series"), Row("7series"), Row("0series"), Row("0series"), Row("5series"), Row("4series"), Row("1series"), Row("6series"), Row("9series"), Row("8series"), Row("0series"), Row("2series"), Row("3series"), Row("0series"), Row("4series"), Row("1series"), Row("5series"), Row("4series"), Row("3series"), Row("9series"), Row("8series"), Row("5series"), Row("0series"), Row("5series"), Row("0series"), Row("5series"), Row("5series"), Row("6series"), Row("0series"), Row("7series"), Row("0series"), Row("5series"), Row("2series"), Row("7series"), Row("7series"), Row("7series"), Row("1series"), Row("8series"), Row("2series"), Row("5series"), Row("5series"), Row("7series"), Row("6series"), Row("8series"), Row("5series"), Row("8series"), Row("5series"), Row("3series"), Row("9series"), Row("8series"), Row("2series"), Row("3series"), Row("9series"), Row("3series"), Row("0series"), Row("8series"), Row("2serie
s"), Row("2series"), Row("6series"), Row("2series"), Row("7series"), Row("7series"), Row("6series"), Row("9series"), Row("5series"), Row("4series"), Row("3series"), Row("8series"), Row("6series"), Row("9series"), Row("2series"), Row("6series"), Row("0series"), Row("6series"), Row("4series"), Row("8series"), Row("8series"), Row("8series"), Row("0series"), Row("0series"), Row("4series"), Row("4series"), Row("6series"), Row("3series"), Row("0series"), Row("3series"), Row("2series"), Row("4series"), Row("5series"), Row("9series"), Row("5series"), Row("5series"), Row("0series"), Row("0series")))
+ })
+
+ //TC_172
+ test("select Lower(Latest_DAY) a from Carbon_automation_test")({
+ checkAnswer(
+ sql("select Lower(Latest_DAY) a from Carbon_automation_test"),
+ Seq(Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"
)))
+ })
+
+ //TC_173
+ test("select distinct Latest_DAY from Carbon_automation_test")({
+ checkAnswer(
+ sql("select distinct Latest_DAY from Carbon_automation_test"),
+ Seq(Row(1)))
+ })
+
+ //TC_175
+ test("select distinct channelsId from Carbon_automation_test")({
+ checkAnswer(
+ sql("select distinct channelsId from Carbon_automation_test"),
+ Seq(Row("1"), Row("2"), Row("3"), Row("4"), Row("5"), Row("6"), Row("7")))
+ })
+
+ //TC_176
+ test("select distinct series from Carbon_automation_test")({
+ checkAnswer(
+ sql("select distinct series from Carbon_automation_test"),
+ Seq(Row("6Series"), Row("0Series"), Row("4Series"), Row("8Series"), Row("7Series"), Row("1Series"), Row("5Series"), Row("9Series"), Row("3Series"), Row("2Series")))
+ })
+
+ //TC_177
+ test("select distinct count(series) as a from Carbon_automation_test group by channelsName")({
+ checkAnswer(
+ sql("select distinct count(series) as a from Carbon_automation_test group by channelsName"),
+ Seq(Row(10), Row(12), Row(16), Row(18), Row(19)))
+ })
+
+ //TC_178
+ test("select distinct count(gamePointId) a from Carbon_automation_test group by channelsName")({
+ checkAnswer(
+ sql("select distinct count(gamePointId) a from Carbon_automation_test group by channelsName"),
+ Seq(Row(10), Row(12), Row(16), Row(18), Row(19)))
+ })
+
+ //TC_179
+ test("select imei,series from Carbon_automation_test limit 101")({
+ checkAnswer(
+ sql("select imei,series from Carbon_automation_test limit 101"),
+ Seq(Row("1AA1", "7Series"), Row("1AA10", "7Series"), Row("1AA100", "5Series"), Row("1AA1000", "5Series"), Row("1AA10000", "7Series"), Row("1AA100000", "9Series"), Row("1AA1000000", "7Series"), Row("1AA100001", "0Series"), Row("1AA100002", "0Series"), Row("1AA100003", "5Series"), Row("1AA100004", "4Series"), Row("1AA100005", "1Series"), Row("1AA100006", "6Series"), Row("1AA100007", "9Series"), Row("1AA100008", "8Series"), Row("1AA100009", "0Series"), Row("1AA10001", "2Series"), Row("1AA100010", "3Series"), Row("1AA100011", "0Series"), Row("1AA100012", "4Series"), Row("1AA100013", "1Series"), Row("1AA100014", "5Series"), Row("1AA100015", "4Series"), Row("1AA100016", "3Series"), Row("1AA100017", "9Series"), Row("1AA100018", "8Series"), Row("1AA100019", "5Series"), Row("1AA10002", "0Series"), Row("1AA100020", "5Series"), Row("1AA100021", "0Series"), Row("1AA100022", "5Series"), Row("1AA100023", "5Series"), Row("1AA100024", "6Series"), Row("1AA100025", "0Series"), Row("1AA100026",
"7Series"), Row("1AA100027", "0Series"), Row("1AA100028", "5Series"), Row("1AA100029", "2Series"), Row("1AA10003", "7Series"), Row("1AA100030", "7Series"), Row("1AA100031", "7Series"), Row("1AA100032", "1Series"), Row("1AA100033", "8Series"), Row("1AA100034", "2Series"), Row("1AA100035", "5Series"), Row("1AA100036", "5Series"), Row("1AA100037", "7Series"), Row("1AA100038", "6Series"), Row("1AA100039", "8Series"), Row("1AA10004", "5Series"), Row("1AA100040", "8Series"), Row("1AA100041", "5Series"), Row("1AA100042", "3Series"), Row("1AA100043", "9Series"), Row("1AA100044", "8Series"), Row("1AA100045", "2Series"), Row("1AA100046", "3Series"), Row("1AA100047", "9Series"), Row("1AA100048", "3Series"), Row("1AA100049", "0Series"), Row("1AA10005", "8Series"), Row("1AA100050", "2Series"), Row("1AA100051", "2Series"), Row("1AA100052", "6Series"), Row("1AA100053", "2Series"), Row("1AA100054", "7Series"), Row("1AA100055", "7Series"), Row("1AA100056", "6Series"), Row("1AA100057", "9Series"), Ro
w("1AA100058", "5Series"), Row("1AA100059", "4Series"), Row("1AA10006", "3Series"), Row("1AA100060", "8Series"), Row("1AA100061", "6Series"), Row("1AA100062", "9Series"), Row("1AA100063", "2Series"), Row("1AA100064", "6Series"), Row("1AA100065", "0Series"), Row("1AA100066", "6Series"), Row("1AA100067", "4Series"), Row("1AA100068", "8Series"), Row("1AA100069", "8Series"), Row("1AA10007", "8Series"), Row("1AA100070", "0Series"), Row("1AA100071", "0Series"), Row("1AA100072", "4Series"), Row("1AA100073", "4Series"), Row("1AA100074", "6Series"), Row("1AA100075", "3Series"), Row("1AA100076", "0Series"), Row("1AA100077", "3Series"), Row("1AA100078", "2Series"), Row("1AA100079", "4Series"), Row("1AA10008", "5Series"), Row("1AA100080", "9Series"), Row("1AA100081", "5Series"), Row("1AA100082", "5Series"), Row("1AA100083", "0Series"), Row("1AA100084", "0Series")))
+ })
+
+ //TC_180
+ test("select series,sum(gamePointId) a from Carbon_automation_test group by series order by series desc")({
+ checkAnswer(
+ sql("select series,sum(gamePointId) a from Carbon_automation_test group by series order by series desc"),
+ sql("select series,sum(gamePointId) a from Carbon_automation_test_hive group by series order by series desc"))
+ })
+
+ //TC_181
+ test("select series,sum(gamePointId) a from Carbon_automation_test group by series order by a desc")({
+ checkAnswer(
+ sql("select series,sum(gamePointId) a from Carbon_automation_test group by series order by a desc"),
+ sql("select series,sum(gamePointId) a from Carbon_automation_test_hive group by series order by a desc"))
+ })
+
+ //TC_182
+ test("select series,sum(gamePointId) a from Carbon_automation_test group by series order by series desc ,a desc")({
+ checkAnswer(
+ sql("select series,sum(gamePointId) a from Carbon_automation_test group by series order by series desc ,a desc"),
+ sql("select series,sum(gamePointId) a from Carbon_automation_test_hive group by series order by series desc ,a desc"))
+ })
+
+ //TC_183
+ test("select series,sum(gamePointId) a from Carbon_automation_test group by series order by series asc")({
+ checkAnswer(
+ sql("select series,sum(gamePointId) a from Carbon_automation_test group by series order by series asc"),
+ sql("select series,sum(gamePointId) a from Carbon_automation_test_hive group by series order by series asc"))
+ })
+
+ //TC_184
+ test("select series,sum(gamePointId) a from Carbon_automation_test group by series order by a asc")({
+ checkAnswer(
+ sql("select series,sum(gamePointId) a from Carbon_automation_test group by series order by a asc"),
+ sql("select series,sum(gamePointId) a from Carbon_automation_test_hive group by series order by a asc"))
+ })
+
+ //TC_185
+ test("select series,sum(gamePointId) a from Carbon_automation_test group by series order by series asc ,a asc")({
+ checkAnswer(
+ sql("select series,sum(gamePointId) a from Carbon_automation_test group by series order by series asc ,a asc"),
+ sql("select series,sum(gamePointId) a from Carbon_automation_test_hive group by series order by series asc ,a asc"))
+ })
+
+ //TC_186
+ test("select series,sum(gamePointId) a from Carbon_automation_test group by series order by series desc ,a asc")({
+ checkAnswer(
+ sql("select series,sum(gamePointId) a from Carbon_automation_test group by series order by series desc ,a asc"),
+ sql("select series,sum(gamePointId) a from Carbon_automation_test_hive group by series order by series desc ,a asc"))
+ })
+
+ //TC_187
+ test("select series,ActiveProvince,sum(gamePointId) a from Carbon_automation_test group by series,ActiveProvince order by series desc,ActiveProvince asc")({
+ checkAnswer(
+ sql("select series,ActiveProvince,sum(gamePointId) a from Carbon_automation_test group by series,ActiveProvince order by series desc,ActiveProvince asc"),
+ sql("select series,ActiveProvince,sum(gamePointId) a from Carbon_automation_test_hive group by series,ActiveProvince order by series desc,ActiveProvince asc"))
+ })
+
+ //TC_208
+ test("select Latest_DAY as a from Carbon_automation_test where Latest_DAY<=>Latest_areaId")({
+ checkAnswer(
+ sql("select Latest_DAY as a from Carbon_automation_test where Latest_DAY<=>Latest_areaId"),
+ Seq(Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1)))
+ })
+
+ //TC_210
+ test("select Latest_DAY from Carbon_automation_test where Latest_DAY<>Latest_areaId")({
+ checkAnswer(
+ sql("select Latest_DAY from Carbon_automation_test where Latest_DAY<>Latest_areaId"),
+ Seq(Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1)))
+ })
+
+ //TC_211
+ test("select Latest_DAY from Carbon_automation_test where Latest_DAY != Latest_areaId")({
+ checkAnswer(
+ sql("select Latest_DAY from Carbon_automation_test where Latest_DAY != Latest_areaId"),
+ Seq(Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1), Row(1)))
+ })
+
+ //TC_212
+ test("select imei, Latest_DAY from Carbon_automation_test where Latest_DAY<Latest_areaId")({
+ checkAnswer(
+ sql("select imei, Latest_DAY from Carbon_automation_test where Latest_DAY<Latest_areaId"),
+ Seq(Row("1AA1", 1), Row("1AA10", 1), Row("1AA100", 1), Row("1AA1000", 1), Row("1AA10000", 1), Row("1AA100000", 1), Row("1AA100001", 1), Row("1AA100002", 1), Row("1AA100003", 1), Row("1AA100004", 1), Row("1AA100006", 1), Row("1AA100007", 1), Row("1AA100008", 1), Row("1AA100009", 1), Row("1AA10001", 1), Row("1AA100010", 1), Row("1AA100011", 1), Row("1AA100012", 1), Row("1AA100013", 1), Row("1AA100014", 1), Row("1AA100015", 1), Row("1AA100016", 1), Row("1AA100017", 1), Row("1AA100018", 1), Row("1AA100019", 1), Row("1AA10002", 1), Row("1AA100020", 1), Row("1AA100021", 1), Row("1AA100022", 1), Row("1AA100023", 1), Row("1AA100024", 1), Row("1AA100029", 1), Row("1AA10003", 1), Row("1AA100030", 1), Row("1AA100031", 1), Row("1AA100032", 1), Row("1AA100033", 1), Row("1AA100035", 1), Row("1AA100036", 1), Row("1AA100037", 1), Row("1AA100038", 1), Row("1AA10004", 1), Row("1AA100040", 1), Row("1AA100041", 1), Row("1AA100042", 1), Row("1AA100043", 1), Row("1AA100044", 1), Row("1AA100045", 1)
, Row("1AA100046", 1), Row("1AA100047", 1), Row("1AA100048", 1), Row("1AA100049", 1), Row("1AA10005", 1), Row("1AA100051", 1), Row("1AA100053", 1), Row("1AA100054", 1), Row("1AA100055", 1), Row("1AA100056", 1), Row("1AA100057", 1), Row("1AA100058", 1), Row("1AA100059", 1), Row("1AA10006", 1), Row("1AA100060", 1), Row("1AA100062", 1), Row("1AA100063", 1), Row("1AA100064", 1), Row("1AA100065", 1), Row("1AA100066", 1), Row("1AA100067", 1), Row("1AA100068", 1), Row("1AA100069", 1), Row("1AA10007", 1), Row("1AA100070", 1), Row("1AA100071", 1), Row("1AA100072", 1), Row("1AA100073", 1), Row("1AA100075", 1), Row("1AA100076", 1), Row("1AA100077", 1), Row("1AA100078", 1), Row("1AA100079", 1), Row("1AA10008", 1), Row("1AA100080", 1), Row("1AA100081", 1), Row("1AA100082", 1), Row("1AA100083", 1), Row("1AA100084", 1)))
+ })
+
+ //TC_213
+ test("select imei, Latest_DAY from Carbon_automation_test where Latest_DAY<=Latest_areaId")({
+ checkAnswer(
+ sql("select imei, Latest_DAY from Carbon_automation_test where Latest_DAY<=Latest_areaId"),
+ Seq(Row("1AA1", 1), Row("1AA10", 1), Row("1AA100", 1), Row("1AA1000", 1), Row("1AA10000", 1), Row("1AA100000", 1), Row("1AA1000000", 1), Row("1AA100001", 1), Row("1AA100002", 1), Row("1AA100003", 1), Row("1AA100004", 1), Row("1AA100005", 1), Row("1AA100006", 1), Row("1AA100007", 1), Row("1AA100008", 1), Row("1AA100009", 1), Row("1AA10001", 1), Row("1AA100010", 1), Row("1AA100011", 1), Row("1AA100012", 1), Row("1AA100013", 1), Row("1AA100014", 1), Row("1AA100015", 1), Row("1AA100016", 1), Row("1AA100017", 1), Row("1AA100018", 1), Row("1AA100019", 1), Row("1AA10002", 1), Row("1AA100020", 1), Row("1AA100021", 1), Row("1AA100022", 1), Row("1AA100023", 1), Row("1AA100024", 1), Row("1AA100025", 1), Row("1AA100026", 1), Row("1AA100027", 1), Row("1AA100028", 1), Row("1AA100029", 1), Row("1AA10003", 1), Row("1AA100030", 1), Row("1AA100031", 1), Row("1AA100032", 1), Row("1AA100033", 1), Row("1AA100034", 1), Row("1AA100035", 1), Row("1AA100036", 1), Row("1AA100037", 1), Row("1AA100038",
1), Row("1AA100039", 1), Row("1AA10004", 1), Row("1AA100040", 1), Row("1AA100041", 1), Row("1AA100042", 1), Row("1AA100043", 1), Row("1AA100044", 1), Row("1AA100045", 1), Row("1AA100046", 1), Row("1AA100047", 1), Row("1AA100048", 1), Row("1AA100049", 1), Row("1AA10005", 1), Row("1AA100050", 1), Row("1AA100051", 1), Row("1AA100052", 1), Row("1AA100053", 1), Row("1AA100054", 1), Row("1AA100055", 1), Row("1AA100056", 1), Row("1AA100057", 1), Row("1AA100058", 1), Row("1AA100059", 1), Row("1AA10006", 1), Row("1AA100060", 1), Row("1AA100061", 1), Row("1AA100062", 1), Row("1AA100063", 1), Row("1AA100064", 1), Row("1AA100065", 1), Row("1AA100066", 1), Row("1AA100067", 1), Row("1AA100068", 1), Row("1AA100069", 1), Row("1AA10007", 1), Row("1AA100070", 1), Row("1AA100071", 1), Row("1AA100072", 1), Row("1AA100073", 1), Row("1AA100074", 1), Row("1AA100075", 1), Row("1AA100076", 1), Row("1AA100077", 1), Row("1AA100078", 1), Row("1AA100079", 1), Row("1AA10008", 1), Row("1AA100080", 1), Row("1AA100
081", 1), Row("1AA100082", 1), Row("1AA100083", 1), Row("1AA100084", 1)))
+ })
+
+ //TC_215
+ test("select imei, Latest_DAY from Carbon_automation_test where Latest_DAY>=Latest_areaId")({
+ checkAnswer(
+ sql("select imei, Latest_DAY from Carbon_automation_test where Latest_DAY>=Latest_areaId"),
+ Seq(Row("1AA1000000", 1), Row("1AA100005", 1), Row("1AA100025", 1), Row("1AA100026", 1), Row("1AA100027", 1), Row("1AA100028", 1), Row("1AA100034", 1), Row("1AA100039", 1), Row("1AA100050", 1), Row("1AA100052", 1), Row("1AA100061", 1), Row("1AA100074", 1)))
+ })
+
+ //TC_216
+ test("select imei, Latest_DAY from Carbon_automation_test where Latest_DAY NOT BETWEEN Latest_areaId AND Latest_HOUR")({
+ checkAnswer(
+ sql("select imei, Latest_DAY from Carbon_automation_test where Latest_DAY NOT BETWEEN Latest_areaId AND Latest_HOUR"),
+ Seq(Row("1AA1", 1), Row("1AA10", 1), Row("1AA100", 1), Row("1AA1000", 1), Row("1AA10000", 1), Row("1AA100000", 1), Row("1AA100001", 1), Row("1AA100002", 1), Row("1AA100003", 1), Row("1AA100004", 1), Row("1AA100006", 1), Row("1AA100007", 1), Row("1AA100008", 1), Row("1AA100009", 1), Row("1AA10001", 1), Row("1AA100010", 1), Row("1AA100011", 1), Row("1AA100012", 1), Row("1AA100013", 1), Row("1AA100014", 1), Row("1AA100015", 1), Row("1AA100016", 1), Row("1AA100017", 1), Row("1AA100018", 1), Row("1AA100019", 1), Row("1AA10002", 1), Row("1AA100020", 1), Row("1AA100021", 1), Row("1AA100022", 1), Row("1AA100023", 1), Row("1AA100024", 1), Row("1AA100029", 1), Row("1AA10003", 1), Row("1AA100030", 1), Row("1AA100031", 1), Row("1AA100032", 1), Row("1AA100033", 1), Row("1AA100035", 1), Row("1AA100036", 1), Row("1AA100037", 1), Row("1AA100038", 1), Row("1AA10004", 1), Row("1AA100040", 1), Row("1AA100041", 1), Row("1AA100042", 1), Row("1AA100043", 1), Row("1AA100044", 1), Row("1AA100045", 1)
, Row("1AA100046", 1), Row("1AA100047", 1), Row("1AA100048", 1), Row("1AA100049", 1), Row("1AA10005", 1), Row("1AA100051", 1), Row("1AA100053", 1), Row("1AA100054", 1), Row("1AA100055", 1), Row("1AA100056", 1), Row("1AA100057", 1), Row("1AA100058", 1), Row("1AA100059", 1), Row("1AA10006", 1), Row("1AA100060", 1), Row("1AA100062", 1), Row("1AA100063", 1), Row("1AA100064", 1), Row("1AA100065", 1), Row("1AA100066", 1), Row("1AA100067", 1), Row("1AA100068", 1), Row("1AA100069", 1), Row("1AA10007", 1), Row("1AA100070", 1), Row("1AA100071", 1), Row("1AA100072", 1), Row("1AA100073", 1), Row("1AA100075", 1), Row("1AA100076", 1), Row("1AA100077", 1), Row("1AA100078", 1), Row("1AA100079", 1), Row("1AA10008", 1), Row("1AA100080", 1), Row("1AA100081", 1), Row("1AA100082", 1), Row("1AA100083", 1), Row("1AA100084", 1)))
+ })
+
+ //TC_219
+ test("select imei, Latest_DAY from Carbon_automation_test where Latest_DAY IS NOT NULL")({
+ checkAnswer(
+ sql("select imei, Latest_DAY from Carbon_automation_test where Latest_DAY IS NOT NULL"),
+ Seq(Row("1AA1", 1), Row("1AA10", 1), Row("1AA100", 1), Row("1AA1000", 1), Row("1AA10000", 1), Row("1AA100000", 1), Row("1AA1000000", 1), Row("1AA100001", 1), Row("1AA100002", 1), Row("1AA100003", 1), Row("1AA100004", 1), Row("1AA100005", 1), Row("1AA100006", 1), Row("1AA100007", 1), Row("1AA100008", 1), Row("1AA100009", 1), Row("1AA10001", 1), Row("1AA100010", 1), Row("1AA100011", 1), Row("1AA100012", 1), Row("1AA100013", 1), Row("1AA100014", 1), Row("1AA100015", 1), Row("1AA100016", 1), Row("1AA100017", 1), Row("1AA100018", 1), Row("1AA100019", 1), Row("1AA10002", 1), Row("1AA100020", 1), Row("1AA100021", 1), Row("1AA100022", 1), Row("1AA100023", 1), Row("1AA100024", 1), Row("1AA100025", 1), Row("1AA100026", 1), Row("1AA100027", 1), Row("1AA100028", 1), Row("1AA100029", 1), Row("1AA10003", 1), Row("1AA100030", 1), Row("1AA100031", 1), Row("1AA100032", 1), Row("1AA100033", 1), Row("1AA100034", 1), Row("1AA100035", 1), Row("1AA100036", 1), Row("1AA100037", 1), Row("1AA100038",
1), Row("1AA100039", 1), Row("1AA10004", 1), Row("1AA100040", 1), Row("1AA100041", 1), Row("1AA100042", 1), Row("1AA100043", 1), Row("1AA100044", 1), Row("1AA100045", 1), Row("1AA100046", 1), Row("1AA100047", 1), Row("1AA100048", 1), Row("1AA100049", 1), Row("1AA10005", 1), Row("1AA100050", 1), Row("1AA100051", 1), Row("1AA100052", 1), Row("1AA100053", 1), Row("1AA100054", 1), Row("1AA100055", 1), Row("1AA100056", 1), Row("1AA100057", 1), Row("1AA100058", 1), Row("1AA100059", 1), Row("1AA10006", 1), Row("1AA100060", 1), Row("1AA100061", 1), Row("1AA100062", 1), Row("1AA100063", 1), Row("1AA100064", 1), Row("1AA100065", 1), Row("1AA100066", 1), Row("1AA100067", 1), Row("1AA100068", 1), Row("1AA100069", 1), Row("1AA10007", 1), Row("1AA100070", 1), Row("1AA100071", 1), Row("1AA100072", 1), Row("1AA100073", 1), Row("1AA100074", 1), Row("1AA100075", 1), Row("1AA100076", 1), Row("1AA100077", 1), Row("1AA100078", 1), Row("1AA100079", 1), Row("1AA10008", 1), Row("1AA100080", 1), Row("1AA100
081", 1), Row("1AA100082", 1), Row("1AA100083", 1), Row("1AA100084", 1)))
+ })
+
+ //TC_22
+ test("select imei, Latest_DAY from Carbon_automation_test where imei IS NOT NULL")({
+ checkAnswer(
+ sql("select imei, Latest_DAY from Carbon_automation_test where imei IS NOT NULL"),
+ Seq(Row("1AA1", 1), Row("1AA10", 1), Row("1AA100", 1), Row("1AA1000", 1), Row("1AA10000", 1), Row("1AA100000", 1), Row("1AA1000000", 1), Row("1AA100001", 1), Row("1AA100002", 1), Row("1AA100003", 1), Row("1AA100004", 1), Row("1AA100005", 1), Row("1AA100006", 1), Row("1AA100007", 1), Row("1AA100008", 1), Row("1AA100009", 1), Row("1AA10001", 1), Row("1AA100010", 1), Row("1AA100011", 1), Row("1AA100012", 1), Row("1AA100013", 1), Row("1AA100014", 1), Row("1AA100015", 1), Row("1AA100016", 1), Row("1AA100017", 1), Row("1AA100018", 1), Row("1AA100019", 1), Row("1AA10002", 1), Row("1AA100020", 1), Row("1AA100021", 1), Row("1AA100022", 1), Row("1AA100023", 1), Row("1AA100024", 1), Row("1AA100025", 1), Row("1AA100026", 1), Row("1AA100027", 1), Row("1AA100028", 1), Row("1AA100029", 1), Row("1AA10003", 1), Row("1AA100030", 1), Row("1AA100031", 1), Row("1AA100032", 1), Row("1AA100033", 1), Row("1AA100034", 1), Row("1AA100035", 1), Row("1AA100036", 1), Row("1AA100037", 1), Row("1AA100038",
1), Row("1AA100039", 1), Row("1AA10004", 1), Row("1AA100040", 1), Row("1AA100041", 1), Row("1AA100042", 1), Row("1AA100043", 1), Row("1AA100044", 1), Row("1AA100045", 1), Row("1AA100046", 1), Row("1AA100047", 1), Row("1AA100048", 1), Row("1AA100049", 1), Row("1AA10005", 1), Row("1AA100050", 1), Row("1AA100051", 1), Row("1AA100052", 1), Row("1AA100053", 1), Row("1AA100054", 1), Row("1AA100055", 1), Row("1AA100056", 1), Row("1AA100057", 1), Row("1AA100058", 1), Row("1AA100059", 1), Row("1AA10006", 1), Row("1AA100060", 1), Row("1AA100061", 1), Row("1AA100062", 1), Row("1AA100063", 1), Row("1AA100064", 1), Row("1AA100065", 1), Row("1AA100066", 1), Row("1AA100067", 1), Row("1AA100068", 1), Row("1AA100069", 1), Row("1AA10007", 1), Row("1AA100070", 1), Row("1AA100071", 1), Row("1AA100072", 1), Row("1AA100073", 1), Row("1AA100074", 1), Row("1AA100075", 1), Row("1AA100076", 1), Row("1AA100077", 1), Row("1AA100078", 1), Row("1AA100079", 1), Row("1AA10008", 1), Row("1AA100080", 1), Row("1AA100
081", 1), Row("1AA100082", 1), Row("1AA100083", 1), Row("1AA100084", 1)))
+ })
+
+ //TC_223
+ test("select * from (select if( Latest_areaId=7,7,NULL) as babu,Latest_MONTH from Carbon_automation_test) qq where babu LIKE Latest_MONTH")({
+ checkAnswer(
+ sql("select * from (select if( Latest_areaId=7,7,NULL) as babu,Latest_MONTH from Carbon_automation_test) qq where babu LIKE Latest_MONTH"),
+ Seq(Row(7, 7), Row(7, 7), Row(7, 7), Row(7, 7), Row(7, 7), Row(7, 7), Row(7, 7), Row(7, 7), Row(7, 7), Row(7, 7), Row(7, 7), Row(7, 7), Row(7, 7), Row(7, 7), Row(7, 7), Row(7, 7), Row(7, 7)))
+ })
+
+ //TC_263
+ test("SELECT AMSize, ActiveAreaId, SUM(gamePointId) AS Sum_gamePointId FROM (select * from Carbon_automation_test) SUB_QRY GROUP BY AMSize, ActiveAreaId ORDER BY AMSize ASC, ActiveAreaId ASC")({
+ checkAnswer(
+ sql("SELECT AMSize, ActiveAreaId, SUM(gamePointId) AS Sum_gamePointId FROM (select * from Carbon_automation_test) SUB_QRY GROUP BY AMSize, ActiveAreaId ORDER BY AMSize ASC, ActiveAreaId ASC"),
+ sql("SELECT AMSize, ActiveAreaId, SUM(gamePointId) AS Sum_gamePointId FROM (select * from Carbon_automation_test_hive) SUB_QRY GROUP BY AMSize, ActiveAreaId ORDER BY AMSize ASC, ActiveAreaId ASC"))
+ })
+
+ //TC_265
+ test("SELECT AMSize, ActiveAreaId, SUM(gamePointId) AS Sum_gamePointId FROM (select * from Carbon_automation_test) SUB_QRY WHERE NOT(AMSize = \"\") GROUP BY AMSize, ActiveAreaId ORDER BY AMSize ASC, ActiveAreaId ASC")({
+ checkAnswer(
+ sql("SELECT AMSize, ActiveAreaId, SUM(gamePointId) AS Sum_gamePointId FROM (select * from Carbon_automation_test) SUB_QRY WHERE NOT(AMSize = \"\") GROUP BY AMSize, ActiveAreaId ORDER BY AMSize ASC, ActiveAreaId ASC"),
+ sql("SELECT AMSize, ActiveAreaId, SUM(gamePointId) AS Sum_gamePointId FROM (select * from Carbon_automation_test_hive) SUB_QRY WHERE NOT(AMSize = \"\") GROUP BY AMSize, ActiveAreaId ORDER BY AMSize ASC, ActiveAreaId ASC"))
+ })
+
+ //TC_274
+ test("SELECT ActiveCountry, ActiveDistrict, Activecity, SUM(gamepointid) AS Sum_gamepointid FROM Carbon_automation_test group by ActiveCountry,ActiveDistrict,Activecity")({
+ checkAnswer(
+ sql("SELECT ActiveCountry, ActiveDistrict, Activecity, SUM(gamepointid) AS Sum_gamepointid FROM Carbon_automation_test group by ActiveCountry,ActiveDistrict,Activecity"),
+ sql("SELECT ActiveCountry, ActiveDistrict, Activecity, SUM(gamepointid) AS Sum_gamepointid FROM Carbon_automation_test_hive group by ActiveCountry,ActiveDistrict,Activecity"))
+ })
+
+ //TC_275
+ test("SELECT Latest_country, Latest_city, Latest_district, SUM(gamepointid) AS Sum_gamepointid FROM (select * from Carbon_automation_test) SUB_QRY GROUP BY Latest_country, Latest_city, Latest_district ORDER BY Latest_country ASC, Latest_city ASC, Latest_district ASC")({
+ checkAnswer(
+ sql("SELECT Latest_country, Latest_city, Latest_district, SUM(gamepointid) AS Sum_gamepointid FROM (select * from Carbon_automation_test) SUB_QRY GROUP BY Latest_country, Latest_city, Latest_district ORDER BY Latest_country ASC, Latest_city ASC, Latest_district ASC"),
+ sql("SELECT Latest_country, Latest_city, Latest_district, SUM(gamepointid) AS Sum_gamepointid FROM (select * from Carbon_automation_test_hive) SUB_QRY GROUP BY Latest_country, Latest_city, Latest_district ORDER BY Latest_country ASC, Latest_city ASC, Latest_district ASC"))
+ })
+
+ //TC_276
+ test("SELECT Activecity, ActiveCountry, ActiveDistrict, COUNT(imei) AS Count_imei FROM (select * from Carbon_automation_test) SUB_QRY GROUP BY Activecity, ActiveCountry, ActiveDistrict ORDER BY Activecity ASC, ActiveCountry ASC, ActiveDistrict ASC")({
+ checkAnswer(
+ sql("SELECT Activecity, ActiveCountry, ActiveDistrict, COUNT(imei) AS Count_imei FROM (select * from Carbon_automation_test) SUB_QRY GROUP BY Activecity, ActiveCountry, ActiveDistrict ORDER BY Activecity ASC, ActiveCountry ASC, ActiveDistrict ASC"),
+ Seq(Row("changsha", "Chinese", "yuhua", 19), Row("guangzhou", "Chinese", "longhua", 8), Row("shenzhen", "Chinese", "longgang", 12), Row("wuhan", "Chinese", "hongshan", 16), Row("xiangtan", "Chinese", "xiangtan", 22), Row("yichang", "Chinese", "yichang", 12), Row("zhuzhou", "Chinese", "tianyuan", 10)))
+ })
+
+ //TC_279
+ test("SELECT ActiveCountry, COUNT(DISTINCT imei) AS DistinctCount_imei FROM (select * from Carbon_automation_test) SUB_QRY GROUP BY ActiveCountry ORDER BY ActiveCountry ASC")({
+ checkAnswer(
+ sql("SELECT ActiveCountry, COUNT(DISTINCT imei) AS DistinctCount_imei FROM (select * from Carbon_automation_test) SUB_QRY GROUP BY ActiveCountry ORDER BY ActiveCountry ASC"),
+ Seq(Row("Chinese", 99)))
+ })
+
+ //TC_282
+ test("SELECT ActiveCountry, ActiveDistrict, Activecity, SUM(gamepointid) AS Sum_gamepointid FROM (select * from Carbon_automation_test) SUB_QRY GROUP BY ActiveCountry, ActiveDistrict, Activecity ORDER BY ActiveCountry ASC, ActiveDistrict ASC, Activecity ASC")({
+ checkAnswer(
+ sql("SELECT ActiveCountry, ActiveDistrict, Activecity, SUM(gamepointid) AS Sum_gamepointid FROM (select * from Carbon_automation_test) SUB_QRY GROUP BY ActiveCountry, ActiveDistrict, Activecity ORDER BY ActiveCountry ASC, ActiveDistrict ASC, Activecity ASC"),
+ sql("SELECT ActiveCountry, ActiveDistrict, Activecity, SUM(gamepointid) AS Sum_gamepointid FROM (select * from Carbon_automation_test_hive) SUB_QRY GROUP BY ActiveCountry, ActiveDistrict, Activecity ORDER BY ActiveCountry ASC, ActiveDistrict ASC, Activecity ASC"))
+ })
+
+ //TC_317
+ test("select channelsId from Carbon_automation_test order by channelsId")({
+ checkAnswer(
+ sql("select channelsId from Carbon_automation_test order by channelsId"),
+ Seq(Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("1"), Row("2"), Row("2"), Row("2"), Row("2"), Row("2"), Row("2"), Row("2"), Row("2"), Row("2"), Row("2"), Row("3"), Row("3"), Row("3"), Row("3"), Row("3"), Row("3"), Row("3"), Row("3"), Row("3"), Row("3"), Row("3"), Row("3"), Row("3"), Row("3"), Row("3"), Row("3"), Row("4"), Row("4"), Row("4"), Row("4"), Row("4"), Row("4"), Row("4"), Row("4"), Row("4"), Row("4"), Row("4"), Row("4"), Row("5"), Row("5"), Row("5"), Row("5"), Row("5"), Row("5"), Row("5"), Row("5"), Row("5"), Row("5"), Row("5"), Row("5"), Row("6"), Row("6"), Row("6"), Row("6"), Row("6"), Row("6"), Row("6"), Row("6"), Row("6"), Row("6"), Row("6"), Row("6"), Row("6"), Row("6"), Row("6"), Row("6"), Row("6"), Row("6"), Row("6"), Row("7"), Row("7"), Row("7"), Row("7"), Row("7"), Row("7"), Row("7"), Row("7"), Row("7"), Row("7"), Row("7"), Row("7"), Row("7"), Row("7"), Row("7"), Row("7"), Row("7"), Row("7"
)))
+ })
+
+ //TC_318
+ test("select count(series),series from Carbon_automation_test group by series having series='6Series'")({
+ checkAnswer(
+ sql("select count(series),series from Carbon_automation_test group by series having series='6Series'"),
+ Seq(Row(9, "6Series")))
+ })
+
+ //TC_319
+ test("SELECT ActiveCountry, ActiveDistrict, Activecity, SUM(gamePointId) AS Sum_gamePointId FROM (select * from Carbon_automation_test) SUB_QRY GROUP BY ActiveCountry, ActiveDistrict, Activecity ORDER BY ActiveCountry ASC, ActiveDistrict ASC, Activecity ASC")({
+ checkAnswer(
+ sql("SELECT ActiveCountry, ActiveDistrict, Activecity, SUM(gamePointId) AS Sum_gamePointId FROM (select * from Carbon_automation_test) SUB_QRY GROUP BY ActiveCountry, ActiveDistrict, Activecity ORDER BY ActiveCountry ASC, ActiveDistrict ASC, Activecity ASC"),
+ sql("SELECT ActiveCountry, ActiveDistrict, Activecity, SUM(gamePointId) AS Sum_gamePointId FROM (select * from Carbon_automation_test_hive) SUB_QRY GROUP BY ActiveCountry, ActiveDistrict, Activecity ORDER BY ActiveCountry ASC, ActiveDistrict ASC, Activecity ASC"))
+ })
+
+ //TC_321
+ test("SELECT ActiveCountry, ActiveDistrict, Activecity, SUM(gamePointId) AS Sum_gamePointId FROM (select * from Carbon_automation_test) SUB_QRY WHERE imei = \"1AA100000\" GROUP BY ActiveCountry, ActiveDistrict, Activecity ORDER BY ActiveCountry ASC, ActiveDistrict ASC, Activecity ASC")({
+ checkAnswer(
+ sql("SELECT ActiveCountry, ActiveDistrict, Activecity, SUM(gamePointId) AS Sum_gamePointId FROM (select * from Carbon_automation_test) SUB_QRY WHERE imei = \"1AA100000\" GROUP BY ActiveCountry, ActiveDistrict, Activecity ORDER BY ActiveCountry ASC, ActiveDistrict ASC, Activecity ASC"),
+ Seq(Row("Chinese", "yichang", "yichang", 136.0)))
+ })
+
+ //TC_384
+ test("SELECT series, SUM(gamePointId) AS Sum_gamePointId FROM (select * from Carbon_automation_test) SUB_QRY GROUP BY series ORDER BY series ASC")({
+ checkAnswer(
+ sql("SELECT series, SUM(gamePointId) AS Sum_gamePointId FROM (select * from Carbon_automation_test) SUB_QRY GROUP BY series ORDER BY series ASC"),
+ sql("SELECT series, SUM(gamePointId) AS Sum_gamePointId FROM (select * from Carbon_automation_test_hive) SUB_QRY GROUP BY series ORDER BY series ASC"))
+ })
+
+ //TC_386
+ test("SELECT channelsId, deliveryCity FROM (select * from Carbon_automation_test) SUB_QRY GROUP BY channelsId, deliveryCity ORDER BY channelsId ASC, deliveryCity ASC")({
+ checkAnswer(
+ sql("SELECT channelsId, deliveryCity FROM (select * from Carbon_automation_test) SUB_QRY GROUP BY channelsId, deliveryCity ORDER BY channelsId ASC, deliveryCity ASC"),
+ Seq(Row("1", "changsha"), Row("1", "guangzhou"), Row("1", "shenzhen"), Row("1", "xiangtan"), Row("1", "yichang"), Row("1", "zhuzhou"), Row("2", "changsha"), Row("2", "guangzhou"), Row("2", "shenzhen"), Row("2", "xiangtan"), Row("2", "yichang"), Row("2", "zhuzhou"), Row("3", "changsha"), Row("3", "guangzhou"), Row("3", "shenzhen"), Row("3", "wuhan"), Row("3", "xiangtan"), Row("3", "yichang"), Row("3", "zhuzhou"), Row("4", "guangzhou"), Row("4", "shenzhen"), Row("4", "xiangtan"), Row("4", "yichang"), Row("4", "zhuzhou"), Row("5", "changsha"), Row("5", "guangzhou"), Row("5", "shenzhen"), Row("5", "wuhan"), Row("5", "xiangtan"), Row("5", "yichang"), Row("5", "zhuzhou"), Row("6", "changsha"), Row("6", "guangzhou"), Row("6", "shenzhen"), Row("6", "wuhan"), Row("6", "xiangtan"), Row("6", "yichang"), Row("6", "zhuzhou"), Row("7", "changsha"), Row("7", "guangzhou"), Row("7", "shenzhen"), Row("7", "wuhan"), Row("7", "xiangtan"), Row("7", "yichang"), Row("7", "zhuzhou")))
+ })
+
+ //TC_387
+ test("SELECT modelId, SUM(gamepointid) AS Sum_gamepointid FROM (select * from Carbon_automation_test) SUB_QRY GROUP BY modelId ORDER BY modelId ASC")({
+ checkAnswer(
+ sql("SELECT modelId, SUM(gamepointid) AS Sum_gamepointid FROM (select * from Carbon_automation_test) SUB_QRY GROUP BY modelId ORDER BY modelId ASC"),
+ sql("SELECT modelId, SUM(gamepointid) AS Sum_gamepointid FROM (select * from Carbon_automation_test_hive) SUB_QRY GROUP BY modelId ORDER BY modelId ASC"))
+ })
+
+ //TC_388
+ test("SELECT imei, channelsId, COUNT(deliveryTime) AS Count_deliveryTime FROM (select * from Carbon_automation_test) SUB_QRY GROUP BY imei, channelsId ORDER BY imei ASC, channelsId ASC")({
+ checkAnswer(
+ sql("SELECT imei, channelsId, COUNT(deliveryTime) AS Count_deliveryTime FROM (select * from Carbon_automation_test) SUB_QRY GROUP BY imei, channelsId ORDER BY imei ASC, channelsId ASC"),
+ Seq(Row("1AA1", "4", 1), Row("1AA10", "4", 1), Row("1AA100", "6", 1), Row("1AA1000", "3", 1), Row("1AA10000", "1", 1), Row("1AA100000", "6", 1), Row("1AA1000000", "6", 1), Row("1AA100001", "7", 1), Row("1AA100002", "3", 1), Row("1AA100003", "3", 1), Row("1AA100004", "2", 1), Row("1AA100005", "1", 1), Row("1AA100006", "2", 1), Row("1AA100007", "3", 1), Row("1AA100008", "1", 1), Row("1AA100009", "3", 1), Row("1AA10001", "5", 1), Row("1AA100010", "6", 1), Row("1AA100011", "1", 1), Row("1AA100012", "2", 1), Row("1AA100013", "6", 1), Row("1AA100014", "3", 1), Row("1AA100015", "1", 1), Row("1AA100016", "3", 1), Row("1AA100017", "3", 1), Row("1AA100018", "4", 1), Row("1AA100019", "3", 1), Row("1AA10002", "5", 1), Row("1AA100020", "7", 1), Row("1AA100021", "6", 1), Row("1AA100022", "5", 1), Row("1AA100023", "3", 1), Row("1AA100024", "2", 1), Row("1AA100025", "1", 1), Row("1AA100026", "7", 1), Row("1AA100027", "4", 1), Row("1AA100028", "5", 1), Row("1AA100029", "5", 1), Row("1AA10003",
"4", 1), Row("1AA100030", "3", 1), Row("1AA100031", "1", 1), Row("1AA100032", "7", 1), Row("1AA100033", "6", 1), Row("1AA100034", "6", 1), Row("1AA100035", "7", 1), Row("1AA100036", "5", 1), Row("1AA100037", "6", 1), Row("1AA100038", "3", 1), Row("1AA100039", "1", 1), Row("1AA10004", "4", 1), Row("1AA100040", "7", 1), Row("1AA100041", "1", 1), Row("1AA100042", "5", 1), Row("1AA100043", "6", 1), Row("1AA100044", "2", 1), Row("1AA100045", "6", 1), Row("1AA100046", "4", 1), Row("1AA100047", "1", 1), Row("1AA100048", "1", 1), Row("1AA100049", "6", 1), Row("1AA10005", "2", 1), Row("1AA100050", "1", 1), Row("1AA100051", "7", 1), Row("1AA100052", "7", 1), Row("1AA100053", "3", 1), Row("1AA100054", "2", 1), Row("1AA100055", "7", 1), Row("1AA100056", "5", 1), Row("1AA100057", "6", 1), Row("1AA100058", "4", 1), Row("1AA100059", "7", 1), Row("1AA10006", "5", 1), Row("1AA100060", "4", 1), Row("1AA100061", "6", 1), Row("1AA100062", "6", 1), Row("1AA100063", "3", 1), Row("1AA100064", "7", 1), Ro
w("1AA100065", "7", 1), Row("1AA100066", "4", 1), Row("1AA100067", "7", 1), Row("1AA100068", "7", 1), Row("1AA100069", "5", 1), Row("1AA10007", "3", 1), Row("1AA100070", "3", 1), Row("1AA100071", "5", 1), Row("1AA100072", "7", 1), Row("1AA100073", "2", 1), Row("1AA100074", "7", 1), Row("1AA100075", "6", 1), Row("1AA100076", "7", 1), Row("1AA100077", "6", 1), Row("1AA100078", "5", 1), Row("1AA100079", "6", 1), Row("1AA10008", "4", 1), Row("1AA100080", "6", 1), Row("1AA100081", "2", 1), Row("1AA100082", "7", 1), Row("1AA100083", "2", 1), Row("1AA100084", "4", 1)))
+ })
+
+ //TC_408
+ test("select imei,series from Carbon_automation_test where series='7Series' order by imei limit 10")({
+ checkAnswer(
+ sql("select imei,series from Carbon_automation_test where series='7Series' order by imei limit 10"),
+ Seq(Row("1AA1", "7Series"), Row("1AA10", "7Series"), Row("1AA10000", "7Series"), Row("1AA1000000", "7Series"), Row("1AA100026", "7Series"), Row("1AA10003", "7Series"), Row("1AA100030", "7Series"), Row("1AA100031", "7Series"), Row("1AA100037", "7Series"), Row("1AA100054", "7Series")))
+ })
+
+ //TC_419
+ test("select count(channelsId) from Carbon_automation_test where modelId is null")({
+ checkAnswer(
+ sql("select count(channelsId) from Carbon_automation_test where modelId is null"),
+ Seq(Row(0)))
+ })
+
+ //TC_420
+ test("select sum(channelsId) from Carbon_automation_test where deviceinformationid is null")({
+ checkAnswer(
+ sql("select sum(channelsId) from Carbon_automation_test where deviceinformationid is null"),
+ Seq(Row(null)))
+ })
+
+ //TC_421
+ test("select avg(channelsName) from Carbon_automation_test where modelId is null")({
+ checkAnswer(
+ sql("select avg(channelsName) from Carbon_automation_test where modelId is null"),
+ Seq(Row(null)))
+ })
+
+ //TC_424
+ test("SELECT count(DISTINCT gamePointId) FROM Carbon_automation_test where imei is null")({
+ checkAnswer(
+ sql("SELECT count(DISTINCT gamePointId) FROM Carbon_automation_test where imei is null"),
+ Seq(Row(0)))
+ })
+
+ //TC_425
+ test("select imei from Carbon_automation_test where contractNumber is NOT null")({
+ checkAnswer(
+ sql("select imei from Carbon_automation_test where contractNumber is NOT null"),
+ Seq(Row("1AA1"), Row("1AA10"), Row("1AA100"), Row("1AA1000"), Row("1AA10000"), Row("1AA100000"), Row("1AA1000000"), Row("1AA100001"), Row("1AA100002"), Row("1AA100003"), Row("1AA100004"), Row("1AA100005"), Row("1AA100006"), Row("1AA100007"), Row("1AA100008"), Row("1AA100009"), Row("1AA10001"), Row("1AA100010"), Row("1AA100011"), Row("1AA100012"), Row("1AA100013"), Row("1AA100014"), Row("1AA100015"), Row("1AA100016"), Row("1AA100017"), Row("1AA100018"), Row("1AA100019"), Row("1AA10002"), Row("1AA100020"), Row("1AA100021"), Row("1AA100022"), Row("1AA100023"), Row("1AA100024"), Row("1AA100025"), Row("1AA100026"), Row("1AA100027"), Row("1AA100028"), Row("1AA100029"), Row("1AA10003"), Row("1AA100030"), Row("1AA100031"), Row("1AA100032"), Row("1AA100033"), Row("1AA100034"), Row("1AA100035"), Row("1AA100036"), Row("1AA100037"), Row("1AA100038"), Row("1AA100039"), Row("1AA10004"), Row("1AA100040"), Row("1AA100041"), Row("1AA100042"), Row("1AA100043"), Row("1AA100044"), Row("1AA100045"
), Row("1AA100046"), Row("1AA100047"), Row("1AA100048"), Row("1AA100049"), Row("1AA10005"), Row("1AA100050"), Row("1AA100051"), Row("1AA100052"), Row("1AA100053"), Row("1AA100054"), Row("1AA100055"), Row("1AA100056"), Row("1AA100057"), Row("1AA100058"), Row("1AA100059"), Row("1AA10006"), Row("1AA100060"), Row("1AA100061"), Row("1AA100062"), Row("1AA100063"), Row("1AA100064"), Row("1AA100065"), Row("1AA100066"), Row("1AA100067"), Row("1AA100068"), Row("1AA100069"), Row("1AA10007"), Row("1AA100070"), Row("1AA100071"), Row("1AA100072"), Row("1AA100073"), Row("1AA100074"), Row("1AA100075"), Row("1AA100076"), Row("1AA100077"), Row("1AA100078"), Row("1AA100079"), Row("1AA10008"), Row("1AA100080"), Row("1AA100081"), Row("1AA100082"), Row("1AA100083"), Row("1AA100084")))
+ })
+
+ //TC_429
+ test("select count(gamePointId) from Carbon_automation_test where imei is NOT null")({
+ checkAnswer(
+ sql("select count(gamePointId) from Carbon_automation_test where imei is NOT null"),
+ Seq(Row(99)))
+ })
+
+ //TC_430
+ test("select count(bomCode) from Carbon_automation_test where contractNumber is NOT null")({
+ checkAnswer(
+ sql("select count(bomCode) from Carbon_automation_test where contractNumber is NOT null"),
+ Seq(Row(99)))
+ })
+
+ //TC_431
+ test("select channelsName from Carbon_automation_test where contractNumber is NOT null")({
+ checkAnswer(
+ sql("select channelsName from Carbon_automation_test where contractNumber is NOT null"),
+ Seq(Row("guomei"), Row("guomei"), Row("yidong"), Row("shuling"), Row("taobao"), Row("yidong"), Row("yidong"), Row("liantong"), Row("shuling"), Row("shuling"), Row("jingdong"), Row("taobao"), Row("jingdong"), Row("shuling"), Row("taobao"), Row("shuling"), Row("shishang"), Row("yidong"), Row("taobao"), Row("jingdong"), Row("yidong"), Row("shuling"), Row("taobao"), Row("shuling"), Row("shuling"), Row("guomei"), Row("shuling"), Row("shishang"), Row("liantong"), Row("yidong"), Row("shishang"), Row("shuling"), Row("jingdong"), Row("taobao"), Row("liantong"), Row("guomei"), Row("shishang"), Row("shishang"), Row("guomei"), Row("shuling"), Row("taobao"), Row("liantong"), Row("yidong"), Row("yidong"), Row("liantong"), Row("shishang"), Row("yidong"), Row("shuling"), Row("taobao"), Row("guomei"), Row("liantong"), Row("taobao"), Row("shishang"), Row("yidong"), Row("jingdong"), Row("yidong"), Row("guomei"), Row("taobao"), Row("taobao"), Row("yidong"), Row("jingdong"), Row("taobao"), Row("li
antong"), Row("liantong"), Row("shuling"), Row("jingdong"), Row("liantong"), Row("shishang"), Row("yidong"), Row("guomei"), Row("liantong"), Row("shishang"), Row("guomei"), Row("yidong"), Row("yidong"), Row("shuling"), Row("liantong"), Row("liantong"), Row("guomei"), Row("liantong"), Row("liantong"), Row("shishang"), Row("shuling"), Row("shuling"), Row("shishang"), Row("liantong"), Row("jingdong"), Row("liantong"), Row("yidong"), Row("liantong"), Row("yidong"), Row("shishang"), Row("yidong"), Row("guomei"), Row("yidong"), Row("jingdong"), Row("liantong"), Row("jingdong"), Row("guomei")))
+ })
+
+ //TC_432
+ test("select channelsId from Carbon_automation_test where gamePointId is NOT null")({
+ checkAnswer(
+ sql("select channelsId from Carbon_automation_test where gamePointId is NOT null"),
+ Seq(Row("4"), Row("4"), Row("6"), Row("3"), Row("1"), Row("6"), Row("6"), Row("7"), Row("3"), Row("3"), Row("2"), Row("1"), Row("2"), Row("3"), Row("1"), Row("3"), Row("5"), Row("6"), Row("1"), Row("2"), Row("6"), Row("3"), Row("1"), Row("3"), Row("3"), Row("4"), Row("3"), Row("5"), Row("7"), Row("6"), Row("5"), Row("3"), Row("2"), Row("1"), Row("7"), Row("4"), Row("5"), Row("5"), Row("4"), Row("3"), Row("1"), Row("7"), Row("6"), Row("6"), Row("7"), Row("5"), Row("6"), Row("3"), Row("1"), Row("4"), Row("7"), Row("1"), Row("5"), Row("6"), Row("2"), Row("6"), Row("4"), Row("1"), Row("1"), Row("6"), Row("2"), Row("1"), Row("7"), Row("7"), Row("3"), Row("2"), Row("7"), Row("5"), Row("6"), Row("4"), Row("7"), Row("5"), Row("4"), Row("6"), Row("6"), Row("3"), Row("7"), Row("7"), Row("4"), Row("7"), Row("7"), Row("5"), Row("3"), Row("3"), Row("5"), Row("7"), Row("2"), Row("7"), Row("6"), Row("7"), Row("6"), Row("5"), Row("6"), Row("4"), Row("6"), Row("2"), Row("7"), Row("2"), Row("4"
)))
+ })
+
+ //TC_433
+ test("select channelsName from Carbon_automation_test where gamePointId is NOT null")({
+ checkAnswer(
+ sql("select channelsName from Carbon_automation_test where gamePointId is NOT null"),
+ Seq(Row("guomei"), Row("guomei"), Row("yidong"), Row("shuling"), Row("taobao"), Row("yidong"), Row("yidong"), Row("liantong"), Row("shuling"), Row("shuling"), Row("jingdong"), Row("taobao"), Row("jingdong"), Row("shuling"), Row("taobao"), Row("shuling"), Row("shishang"), Row("yidong"), Row("taobao"), Row("jingdong"), Row("yidong"), Row("shuling"), Row("taobao"), Row("shuling"), Row("shuling"), Row("guomei"), Row("shuling"), Row("shishang"), Row("liantong"), Row("yidong"), Row("shishang"), Row("shuling"), Row("jingdong"), Row("taobao"), Row("liantong"), Row("guomei"), Row("shishang"), Row("shishang"), Row("guomei"), Row("shuling"), Row("taobao"), Row("liantong"), Row("yidong"), Row("yidong"), Row("liantong"), Row("shishang"), Row("yidong"), Row("shuling"), Row("taobao"), Row("guomei"), Row("liantong"), Row("taobao"), Row("shishang"), Row("yidong"), Row("jingdong"), Row("yidong"), Row("guomei"), Row("taobao"), Row("taobao"), Row("yidong"), Row("jingdong"), Row("taobao"), Row("li
antong"), Row("liantong"), Row("shuling"), Row("jingdong"), Row("liantong"), Row("shishang"), Row("yidong"), Row("guomei"), Row("liantong"), Row("shishang"), Row("guomei"), Row("yidong"), Row("yidong"), Row("shuling"), Row("liantong"), Row("liantong"), Row("guomei"), Row("liantong"), Row("liantong"), Row("shishang"), Row("shuling"), Row("shuling"), Row("shishang"), Row("liantong"), Row("jingdong"), Row("liantong"), Row("yidong"), Row("liantong"), Row("yidong"), Row("shishang"), Row("yidong"), Row("guomei"), Row("yidong"), Row("jingdong"), Row("liantong"), Row("jingdong"), Row("guomei")))
+ })
+
+ //TC_434
+ test("select channelsId from Carbon_automation_test where latest_day is NOT null")({
+ checkAnswer(
+ sql("select channelsId from Carbon_automation_test where latest_day is NOT null"),
+ Seq(Row("4"), Row("4"), Row("6"), Row("3"), Row("1"), Row("6"), Row("6"), Row("7"), Row("3"), Row("3"), Row("2"), Row("1"), Row("2"), Row("3"), Row("1"), Row("3"), Row("5"), Row("6"), Row("1"), Row("2"), Row("6"), Row("3"), Row("1"), Row("3"), Row("3"), Row("4"), Row("3"), Row("5"), Row("7"), Row("6"), Row("5"), Row("3"), Row("2"), Row("1"), Row("7"), Row("4"), Row("5"), Row("5"), Row("4"), Row("3"), Row("1"), Row("7"), Row("6"), Row("6"), Row("7"), Row("5"), Row("6"), Row("3"), Row("1"), Row("4"), Row("7"), Row("1"), Row("5"), Row("6"), Row("2"), Row("6"), Row("4"), Row("1"), Row("1"), Row("6"), Row("2"), Row("1"), Row("7"), Row("7"), Row("3"), Row("2"), Row("7"), Row("5"), Row("6"), Row("4"), Row("7"), Row("5"), Row("4"), Row("6"), Row("6"), Row("3"), Row("7"), Row("7"), Row("4"), Row("7"), Row("7"), Row("5"), Row("3"), Row("3"), Row("5"), Row("7"), Row("2"), Row("7"), Row("6"), Row("7"), Row("6"), Row("5"), Row("6"), Row("4"), Row("6"), Row("2"), Row("7"), Row("2"), Row("4"
)))
+ })
+
+ //TC_435
+ test("select channelsName from Carbon_automation_test where latest_day is NOT null")({
+ checkAnswer(
+ sql("select channelsName from Carbon_automation_test where latest_day is NOT null"),
+ Seq(Row("guomei"), Row("guomei"), Row("yidong"), Row("shuling"), Row("taobao"), Row("yidong"), Row("yidong"), Row("liantong"), Row("shuling"), Row("shuling"), Row("jingdong"), Row("taobao"), Row("jingdong"), Row("shuling"), Row("taobao"), Row("shuling"), Row("shishang"), Row("yidong"), Row("taobao"), Row("jingdong"), Row("yidong"), Row("shuling"), Row("taobao"), Row("shuling"), Row("shuling"), Row("guomei"), Row("shuling"), Row("shishang"), Row("liantong"), Row("yidong"), Row("shishang"), Row("shuling"), Row("jingdong"), Row("taobao"), Row("liantong"), Row("guomei"), Row("shishang"), Row("shishang"), Row("guomei"), Row("shuling"), Row("taobao"), Row("liantong"), Row("yidong"), Row("yidong"), Row("liantong"), Row("shishang"), Row("yidong"), Row("shuling"), Row("taobao"), Row("guomei"), Row("liantong"), Row("taobao"), Row("shishang"), Row("yidong"), Row("jingdong"), Row("yidong"), Row("guomei"), Row("taobao"), Row("taobao"), Row("yidong"), Row("jingdong"), Row("taobao"), Row("li
antong"), Row("liantong"), Row("shuling"), Row("jingdong"), Row("liantong"), Row("shishang"), Row("yidong"), Row("guomei"), Row("liantong"), Row("shishang"), Row("guomei"), Row("yidong"), Row("yidong"), Row("shuling"), Row("liantong"), Row("liantong"), Row("guomei"), Row("liantong"), Row("liantong"), Row("shishang"), Row("shuling"), Row("shuling"), Row("shishang"), Row("liantong"), Row("jingdong"), Row("liantong"), Row("yidong"), Row("liantong"), Row("yidong"), Row("shishang"), Row("yidong"), Row("guomei"), Row("yidong"), Row("jingdong"), Row("liantong"), Row("jingdong"), Row("guomei")))
+ })
+
+ //TC_439
+ test("SELECT min(AMSize) FROM Carbon_automation_test where imei is NOT null")({
+ checkAnswer(
+ sql("SELECT min(AMSize) FROM Carbon_automation_test where imei is NOT null"),
+ Seq(Row("0RAM size")))
+ })
+
+ //TC_448
+ test("select var_samp(Latest_YEAR) from Carbon_automation_test")({
+ checkAnswer(
+ sql("select var_samp(Latest_YEAR) from Carbon_automation_test"),
+ Seq(Row(0.0)))
+ })
+
+ //TC_449
+ test("select var_samp(AMSize) from Carbon_automation_test")({
+ checkAnswer(
+ sql("select var_samp(AMSize) from Carbon_automation_test"),
+ Seq(Row(null)))
+ })
+
+ //TC_451
+ test("select stddev_pop(bomcode)from Carbon_automation_test")({
+ checkAnswer(
+ sql("select stddev_pop(bomcode)from Carbon_automation_test"),
+ Seq(Row(96490.49465950707)))
+ })
+
+ //TC_452
+ test("select stddev_pop(deviceInformationId)from Carbon_automation_test1")({
+ checkAnswer(
+ sql("select stddev_pop(deviceInformationId)from Carbon_automation_test"),
+ Seq(Row(96490.49465950707)))
+ })
+
+ //TC_454
+ test("select stddev_pop(AMSIZE)from Carbon_automation_test")({
+ checkAnswer(
+ sql("select stddev_pop(AMSIZE)from Carbon_automation_test"),
+ Seq(Row(null)))
+ })
+
+ //TC_457
+ test("select stddev_samp(deviceInformationId)from Carbon_automation_test1")({
+ checkAnswer(
+ sql("select stddev_samp(deviceInformationId)from Carbon_automation_test"),
+ Seq(Row(96981.54360516652)))
+ })
+
+ //TC_458
+ test("select stddev_samp(AMSIZE)from Carbon_automation_test")({
+ checkAnswer(
+ sql("select stddev_samp(AMSIZE)from Carbon_automation_test"),
+ Seq(Row(null)))
+ })
+
+ //TC_459
+ test("select stddev_samp(Latest_MONTH)from Carbon_automation_test")({
+ checkAnswer(
+ sql("select stddev_samp(Latest_MONTH)from Carbon_automation_test"),
+ Seq(Row(0.0)))
+ })
+
+ //TC_472
+ test("Select percentile(1,1.0) from Carbon_automation_test2")({
+ checkAnswer(
+ sql("Select percentile(1,1.0) from Carbon_automation_test"),
+ Seq(Row(1.0)))
+ })
+
+ //TC_473
+ test("Select percentile(1,1.0) from Carbon_automation_test")({
+ checkAnswer(
+ sql("Select percentile(1,1.0) from Carbon_automation_test"),
+ Seq(Row(1.0)))
+ })
+
+ //TC_474
+ test("select cast(series as int) as a from Carbon_automation_test limit 10")({
+ checkAnswer(
+ sql("select cast(series as int) as a from Carbon_automation_test limit 10"),
+ Seq(Row(null), Row(null), Row(null), Row(null), Row(null), Row(null), Row(null), Row(null), Row(null), Row(null)))
+ })
+
+ //TC_475
+ test("select cast(modelid as int) as a from Carbon_automation_test limit 10")({
+ checkAnswer(
+ sql("select cast(modelid as int) as a from Carbon_automation_test limit 10"),
+ Seq(Row(109), Row(93), Row(2591), Row(2531), Row(2408), Row(1815), Row(2479), Row(1845), Row(2008), Row(1121)))
+ })
+
+ //TC_476
+ test("Select percentile(1,1.0) from Carbon_automation_test1")({
+ checkAnswer(
+ sql("Select percentile(1,1.0) from Carbon_automation_test"),
+ Seq(Row(1.0)))
+ })
+
+ //TC_481
+ test("select percentile_approx(1,
<TRUNCATED>
[3/4] incubator-carbondata git commit: fixUnionIssue and add test case
Posted by ja...@apache.org.
fixUnionIssue and add test case
Project: http://git-wip-us.apache.org/repos/asf/incubator-carbondata/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-carbondata/commit/462f6422
Tree: http://git-wip-us.apache.org/repos/asf/incubator-carbondata/tree/462f6422
Diff: http://git-wip-us.apache.org/repos/asf/incubator-carbondata/diff/462f6422
Branch: refs/heads/master
Commit: 462f64226428fc255938d8752226cda262ad0ae4
Parents: 526243b
Author: QiangCai <qi...@qq.com>
Authored: Thu Dec 8 19:06:33 2016 +0800
Committer: jackylk <ja...@huawei.com>
Committed: Fri Dec 16 10:13:39 2016 +0800
----------------------------------------------------------------------
.../impl/DictionaryBasedResultCollector.java | 29 +-
.../DictionaryBasedResultCollectorTest.java | 9 +-
.../carbondata/examples/CarbonExample.scala | 12 +
.../apache/carbondata/spark/CarbonFilters.scala | 7 +
.../CarbonDecoderOptimizerHelper.scala | 24 +-
.../readsupport/SparkRowReadSupportImpl.java | 5 +-
.../apache/carbondata/spark/CarbonFilters.scala | 6 +
.../spark/sql/CarbonDataFrameWriter.scala | 3 +-
.../sql/CarbonDatasourceHadoopRelation.scala | 4 +-
.../spark/sql/CarbonDictionaryDecoder.scala | 60 +-
.../scala/org/apache/spark/sql/CarbonScan.scala | 44 +-
.../org/apache/spark/sql/CarbonSource.scala | 3 +-
.../sql/optimizer/CarbonLateDecodeRule.scala | 124 +-
integration/spark2/src/test/resources/data.csv | 11 +
.../AllDataTypesTestCaseAggregate.scala | 1161 ++++++++++++++++++
.../spark/sql/common/util/CarbonFunSuite.scala | 49 +
.../sql/common/util/CarbonSessionTest.scala | 74 ++
.../apache/spark/sql/common/util/PlanTest.scala | 59 +
.../spark/sql/common/util/QueryTest.scala | 149 +++
19 files changed, 1654 insertions(+), 179 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-carbondata/blob/462f6422/core/src/main/java/org/apache/carbondata/scan/collector/impl/DictionaryBasedResultCollector.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/carbondata/scan/collector/impl/DictionaryBasedResultCollector.java b/core/src/main/java/org/apache/carbondata/scan/collector/impl/DictionaryBasedResultCollector.java
index 108677f..2462caa 100644
--- a/core/src/main/java/org/apache/carbondata/scan/collector/impl/DictionaryBasedResultCollector.java
+++ b/core/src/main/java/org/apache/carbondata/scan/collector/impl/DictionaryBasedResultCollector.java
@@ -20,11 +20,13 @@ package org.apache.carbondata.scan.collector.impl;
import java.nio.ByteBuffer;
import java.util.ArrayList;
+import java.util.Arrays;
import java.util.List;
import java.util.Map;
import org.apache.carbondata.common.logging.LogService;
import org.apache.carbondata.common.logging.LogServiceFactory;
+import org.apache.carbondata.core.carbon.metadata.encoder.Encoding;
import org.apache.carbondata.core.keygenerator.directdictionary.DirectDictionaryGenerator;
import org.apache.carbondata.core.keygenerator.directdictionary.DirectDictionaryKeyGeneratorFactory;
import org.apache.carbondata.core.util.CarbonUtil;
@@ -35,6 +37,7 @@ import org.apache.carbondata.scan.model.QueryDimension;
import org.apache.carbondata.scan.model.QueryMeasure;
import org.apache.carbondata.scan.result.AbstractScannedResult;
+import org.apache.commons.lang3.ArrayUtils;
/**
* It is not a collector it is just a scanned result holder.
*/
@@ -52,9 +55,31 @@ public class DictionaryBasedResultCollector extends AbstractScannedResultCollect
* it will keep track of how many record is processed, to handle limit scenario
*/
@Override public List<Object[]> collectData(AbstractScannedResult scannedResult, int batchSize) {
+
List<Object[]> listBasedResult = new ArrayList<>(batchSize);
boolean isMsrsPresent = measureDatatypes.length > 0;
+
QueryDimension[] queryDimensions = tableBlockExecutionInfos.getQueryDimensions();
+ List<Integer> dictionaryIndexes = new ArrayList<Integer>();
+ for (int i = 0; i < queryDimensions.length; i++) {
+ if(queryDimensions[i].getDimension().hasEncoding(Encoding.DICTIONARY) ||
+ queryDimensions[i].getDimension().hasEncoding(Encoding.DIRECT_DICTIONARY) ) {
+ dictionaryIndexes.add(queryDimensions[i].getDimension().getOrdinal());
+ }
+ }
+ int[] primitive = ArrayUtils.toPrimitive(dictionaryIndexes.toArray(
+ new Integer[dictionaryIndexes.size()]));
+ Arrays.sort(primitive);
+ int[] actualIndexInSurrogateKey = new int[dictionaryIndexes.size()];
+ int index = 0;
+ for (int i = 0; i < queryDimensions.length; i++) {
+ if(queryDimensions[i].getDimension().hasEncoding(Encoding.DICTIONARY) ||
+ queryDimensions[i].getDimension().hasEncoding(Encoding.DIRECT_DICTIONARY) ) {
+ actualIndexInSurrogateKey[index++] = Arrays.binarySearch(primitive,
+ queryDimensions[i].getDimension().getOrdinal());
+ }
+ }
+
QueryMeasure[] queryMeasures = tableBlockExecutionInfos.getQueryMeasures();
Map<Integer, GenericQueryType> comlexDimensionInfoMap =
tableBlockExecutionInfos.getComlexDimensionInfoMap();
@@ -99,7 +124,7 @@ public class DictionaryBasedResultCollector extends AbstractScannedResultCollect
.getDirectDictionaryGenerator(queryDimensions[i].getDimension().getDataType());
if (directDictionaryGenerator != null) {
row[order[i]] = directDictionaryGenerator.getValueFromSurrogate(
- surrogateResult[dictionaryColumnIndex++]);
+ surrogateResult[actualIndexInSurrogateKey[dictionaryColumnIndex++]]);
}
} else if (complexDataTypeArray[i]) {
row[order[i]] = comlexDimensionInfoMap
@@ -107,7 +132,7 @@ public class DictionaryBasedResultCollector extends AbstractScannedResultCollect
.getDataBasedOnDataTypeFromSurrogates(
ByteBuffer.wrap(complexTypeKeyArray[complexTypeColumnIndex++]));
} else {
- row[order[i]] = surrogateResult[dictionaryColumnIndex++];
+ row[order[i]] = surrogateResult[actualIndexInSurrogateKey[dictionaryColumnIndex++]];
}
}
http://git-wip-us.apache.org/repos/asf/incubator-carbondata/blob/462f6422/core/src/test/java/org/apache/carbondata/scan/collector/impl/DictionaryBasedResultCollectorTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/carbondata/scan/collector/impl/DictionaryBasedResultCollectorTest.java b/core/src/test/java/org/apache/carbondata/scan/collector/impl/DictionaryBasedResultCollectorTest.java
index 7f21d90..48eab1e 100644
--- a/core/src/test/java/org/apache/carbondata/scan/collector/impl/DictionaryBasedResultCollectorTest.java
+++ b/core/src/test/java/org/apache/carbondata/scan/collector/impl/DictionaryBasedResultCollectorTest.java
@@ -19,14 +19,12 @@
package org.apache.carbondata.scan.collector.impl;
import java.nio.ByteBuffer;
-import java.util.BitSet;
-import java.util.HashMap;
-import java.util.List;
-import java.util.Map;
+import java.util.*;
import org.apache.carbondata.core.carbon.datastore.chunk.MeasureColumnDataChunk;
import org.apache.carbondata.core.carbon.metadata.blocklet.datachunk.PresenceMeta;
import org.apache.carbondata.core.carbon.metadata.datatype.DataType;
+import org.apache.carbondata.core.carbon.metadata.encoder.Encoding;
import org.apache.carbondata.core.carbon.metadata.schema.table.column.CarbonDimension;
import org.apache.carbondata.core.carbon.metadata.schema.table.column.ColumnSchema;
import org.apache.carbondata.core.constants.CarbonCommonConstants;
@@ -74,6 +72,9 @@ public class DictionaryBasedResultCollectorTest {
QueryDimension queryDimension1 = new QueryDimension("QDCol1");
queryDimension1.setQueryOrder(1);
ColumnSchema columnSchema = new ColumnSchema();
+ List encodeList= new ArrayList<Encoding>();
+ encodeList.add(Encoding.DICTIONARY);
+ columnSchema.setEncodingList(encodeList);
queryDimension1.setDimension(new CarbonDimension(columnSchema, 0, 0, 0, 0));
QueryDimension queryDimension2 = new QueryDimension("QDCol2");
queryDimension2.setQueryOrder(2);
http://git-wip-us.apache.org/repos/asf/incubator-carbondata/blob/462f6422/examples/spark2/src/main/scala/org/apache/carbondata/examples/CarbonExample.scala
----------------------------------------------------------------------
diff --git a/examples/spark2/src/main/scala/org/apache/carbondata/examples/CarbonExample.scala b/examples/spark2/src/main/scala/org/apache/carbondata/examples/CarbonExample.scala
index 4aff45a..c2e135a 100644
--- a/examples/spark2/src/main/scala/org/apache/carbondata/examples/CarbonExample.scala
+++ b/examples/spark2/src/main/scala/org/apache/carbondata/examples/CarbonExample.scala
@@ -153,6 +153,18 @@ object CarbonExample {
|where t1.stringField = t2.stringField
""".stripMargin).show
+ spark.sql(
+ """
+ |with t1 as (
+ |select * from carbon_table
+ |union all
+ |select * from carbon_table
+ |)
+ |select t1.*, t2.*
+ |from t1, carbon_table t2
+ |where t1.stringField = t2.stringField
+ """.stripMargin).show
+
// Drop table
spark.sql("DROP TABLE IF EXISTS carbon_table")
spark.sql("DROP TABLE IF EXISTS csv_table")
http://git-wip-us.apache.org/repos/asf/incubator-carbondata/blob/462f6422/integration/spark-common/src/main/scala/org/apache/carbondata/spark/CarbonFilters.scala
----------------------------------------------------------------------
diff --git a/integration/spark-common/src/main/scala/org/apache/carbondata/spark/CarbonFilters.scala b/integration/spark-common/src/main/scala/org/apache/carbondata/spark/CarbonFilters.scala
index 2a580dc..5e58235 100644
--- a/integration/spark-common/src/main/scala/org/apache/carbondata/spark/CarbonFilters.scala
+++ b/integration/spark-common/src/main/scala/org/apache/carbondata/spark/CarbonFilters.scala
@@ -83,6 +83,13 @@ object CarbonFilters {
new ListExpression(
convertToJavaList(values.map(f => getCarbonLiteralExpression(name, f)).toList))))
+ case sources.IsNull(name) =>
+ Some(new EqualToExpression(getCarbonExpression(name),
+ getCarbonLiteralExpression(name, null), true))
+ case sources.IsNotNull(name) =>
+ Some(new NotEqualsExpression(getCarbonExpression(name),
+ getCarbonLiteralExpression(name, null), true))
+
case sources.And(lhs, rhs) =>
(createFilter(lhs) ++ createFilter(rhs)).reduceOption(new AndExpression(_, _))
http://git-wip-us.apache.org/repos/asf/incubator-carbondata/blob/462f6422/integration/spark-common/src/main/scala/org/apache/spark/sql/optimizer/CarbonDecoderOptimizerHelper.scala
----------------------------------------------------------------------
diff --git a/integration/spark-common/src/main/scala/org/apache/spark/sql/optimizer/CarbonDecoderOptimizerHelper.scala b/integration/spark-common/src/main/scala/org/apache/spark/sql/optimizer/CarbonDecoderOptimizerHelper.scala
index 7909a13..6e84e7e 100644
--- a/integration/spark-common/src/main/scala/org/apache/spark/sql/optimizer/CarbonDecoderOptimizerHelper.scala
+++ b/integration/spark-common/src/main/scala/org/apache/spark/sql/optimizer/CarbonDecoderOptimizerHelper.scala
@@ -29,7 +29,7 @@ abstract class AbstractNode
case class Node(cd: CarbonDictionaryTempDecoder) extends AbstractNode
-case class BinaryCarbonNode(left: util.List[AbstractNode], right: util.List[AbstractNode])
+case class ArrayCarbonNode(children: Seq[util.List[AbstractNode]])
extends AbstractNode
case class CarbonDictionaryTempDecoder(
@@ -70,9 +70,16 @@ class CarbonDecoderProcessor {
case j: BinaryNode =>
val leftList = new util.ArrayList[AbstractNode]
val rightList = new util.ArrayList[AbstractNode]
- nodeList.add(BinaryCarbonNode(leftList, rightList))
+ nodeList.add(ArrayCarbonNode(Seq(leftList, rightList)))
process(j.left, leftList)
process(j.right, rightList)
+ case u: Union =>
+ val nodeListSeq = u.children.map { child =>
+ val list = new util.ArrayList[AbstractNode]
+ process(child, list)
+ list
+ }
+ nodeList.add(ArrayCarbonNode(nodeListSeq))
case e: UnaryNode => process(e.child, nodeList)
case _ =>
}
@@ -91,13 +98,12 @@ class CarbonDecoderProcessor {
decoderNotDecode.asScala.foreach(cd.attrsNotDecode.add)
decoderNotDecode.asScala.foreach(cd.attrList.remove)
decoderNotDecode.addAll(cd.attrList)
- case BinaryCarbonNode(left: util.List[AbstractNode], right: util.List[AbstractNode]) =>
- val leftNotDecode = new util.HashSet[AttributeReferenceWrapper]
- val rightNotDecode = new util.HashSet[AttributeReferenceWrapper]
- updateDecoderInternal(left.asScala, leftNotDecode)
- updateDecoderInternal(right.asScala, rightNotDecode)
- decoderNotDecode.addAll(leftNotDecode)
- decoderNotDecode.addAll(rightNotDecode)
+ case ArrayCarbonNode(children) =>
+ children.foreach { child =>
+ val notDecode = new util.HashSet[AttributeReferenceWrapper]
+ updateDecoderInternal(child.asScala, notDecode)
+ decoderNotDecode.addAll(notDecode)
+ }
}
}
http://git-wip-us.apache.org/repos/asf/incubator-carbondata/blob/462f6422/integration/spark2/src/main/java/org/apache/carbondata/spark/readsupport/SparkRowReadSupportImpl.java
----------------------------------------------------------------------
diff --git a/integration/spark2/src/main/java/org/apache/carbondata/spark/readsupport/SparkRowReadSupportImpl.java b/integration/spark2/src/main/java/org/apache/carbondata/spark/readsupport/SparkRowReadSupportImpl.java
index 1bfcdea..499ef0c 100644
--- a/integration/spark2/src/main/java/org/apache/carbondata/spark/readsupport/SparkRowReadSupportImpl.java
+++ b/integration/spark2/src/main/java/org/apache/carbondata/spark/readsupport/SparkRowReadSupportImpl.java
@@ -39,7 +39,10 @@ public class SparkRowReadSupportImpl extends AbstractDictionaryDecodedReadSuppor
@Override public Row readRow(Object[] data) {
for (int i = 0; i < dictionaries.length; i++) {
- if (dictionaries[i] == null && data[i] != null) {
+ if (data[i] == null) {
+ continue;
+ }
+ if (dictionaries[i] == null) {
if (carbonColumns[i].hasEncoding(Encoding.DIRECT_DICTIONARY)) {
//convert the long to timestamp in case of direct dictionary column
if (DataType.TIMESTAMP == carbonColumns[i].getDataType()) {
http://git-wip-us.apache.org/repos/asf/incubator-carbondata/blob/462f6422/integration/spark2/src/main/scala/org/apache/carbondata/spark/CarbonFilters.scala
----------------------------------------------------------------------
diff --git a/integration/spark2/src/main/scala/org/apache/carbondata/spark/CarbonFilters.scala b/integration/spark2/src/main/scala/org/apache/carbondata/spark/CarbonFilters.scala
index 2cd4eb7..6d9fb24 100644
--- a/integration/spark2/src/main/scala/org/apache/carbondata/spark/CarbonFilters.scala
+++ b/integration/spark2/src/main/scala/org/apache/carbondata/spark/CarbonFilters.scala
@@ -83,6 +83,12 @@ object CarbonFilters {
new ListExpression(
convertToJavaList(values.map(f => getCarbonLiteralExpression(name, f)).toList))))
+ case sources.IsNull(name) =>
+ Some(new EqualToExpression(getCarbonExpression(name),
+ getCarbonLiteralExpression(name, null), true))
+ case sources.IsNotNull(name) =>
+ Some(new NotEqualsExpression(getCarbonExpression(name),
+ getCarbonLiteralExpression(name, null), true))
case sources.And(lhs, rhs) =>
(createFilter(lhs) ++ createFilter(rhs)).reduceOption(new AndExpression(_, _))
http://git-wip-us.apache.org/repos/asf/incubator-carbondata/blob/462f6422/integration/spark2/src/main/scala/org/apache/spark/sql/CarbonDataFrameWriter.scala
----------------------------------------------------------------------
diff --git a/integration/spark2/src/main/scala/org/apache/spark/sql/CarbonDataFrameWriter.scala b/integration/spark2/src/main/scala/org/apache/spark/sql/CarbonDataFrameWriter.scala
index 5db5d14..3057bee 100644
--- a/integration/spark2/src/main/scala/org/apache/spark/sql/CarbonDataFrameWriter.scala
+++ b/integration/spark2/src/main/scala/org/apache/spark/sql/CarbonDataFrameWriter.scala
@@ -146,7 +146,8 @@ class CarbonDataFrameWriter(sqlContext: SQLContext, val dataFrame: DataFrame) {
s"""
CREATE TABLE IF NOT EXISTS ${options.dbName}.${options.tableName}
(${ carbonSchema.mkString(", ") })
- using 'org.apache.spark.sql.CarbonRelationProvider'
+ using org.apache.spark.sql.CarbonSource
+ OPTIONS('dbName'='${options.dbName}', 'tableName'='${options.tableName}')
"""
}
http://git-wip-us.apache.org/repos/asf/incubator-carbondata/blob/462f6422/integration/spark2/src/main/scala/org/apache/spark/sql/CarbonDatasourceHadoopRelation.scala
----------------------------------------------------------------------
diff --git a/integration/spark2/src/main/scala/org/apache/spark/sql/CarbonDatasourceHadoopRelation.scala b/integration/spark2/src/main/scala/org/apache/spark/sql/CarbonDatasourceHadoopRelation.scala
index 997106c..09a58ba 100644
--- a/integration/spark2/src/main/scala/org/apache/spark/sql/CarbonDatasourceHadoopRelation.scala
+++ b/integration/spark2/src/main/scala/org/apache/spark/sql/CarbonDatasourceHadoopRelation.scala
@@ -21,9 +21,9 @@ import org.apache.hadoop.conf.Configuration
import org.apache.hadoop.mapred.JobConf
import org.apache.hadoop.mapreduce.Job
import org.apache.spark.rdd.RDD
-import org.apache.spark.sql.execution.command.{ExecutedCommandExec, LoadTableByInsert}
+import org.apache.spark.sql.execution.command.LoadTableByInsert
import org.apache.spark.sql.hive.CarbonRelation
-import org.apache.spark.sql.sources.{BaseRelation, Filter, InsertableRelation, PrunedFilteredScan}
+import org.apache.spark.sql.sources.{BaseRelation, Filter, InsertableRelation}
import org.apache.spark.sql.types.StructType
import org.apache.carbondata.core.carbon.AbsoluteTableIdentifier
http://git-wip-us.apache.org/repos/asf/incubator-carbondata/blob/462f6422/integration/spark2/src/main/scala/org/apache/spark/sql/CarbonDictionaryDecoder.scala
----------------------------------------------------------------------
diff --git a/integration/spark2/src/main/scala/org/apache/spark/sql/CarbonDictionaryDecoder.scala b/integration/spark2/src/main/scala/org/apache/spark/sql/CarbonDictionaryDecoder.scala
index 3b63021..9a625ee 100644
--- a/integration/spark2/src/main/scala/org/apache/spark/sql/CarbonDictionaryDecoder.scala
+++ b/integration/spark2/src/main/scala/org/apache/spark/sql/CarbonDictionaryDecoder.scala
@@ -303,41 +303,41 @@ class CarbonDecoderRDD(
(carbonTable.getFactTableName, carbonTable.getAbsoluteTableIdentifier)
}.toMap
- val cacheProvider: CacheProvider = CacheProvider.getInstance
- val forwardDictionaryCache: Cache[DictionaryColumnUniqueIdentifier, Dictionary] =
- cacheProvider.createCache(CacheType.FORWARD_DICTIONARY, storepath)
- val dicts: Seq[Dictionary] = getDictionary(absoluteTableIdentifiers,
- forwardDictionaryCache)
- val dictIndex = dicts.zipWithIndex.filter(x => x._1 != null).map(x => x._2)
- // add a task completion listener to clear dictionary that is a decisive factor for
- // LRU eviction policy
- val dictionaryTaskCleaner = TaskContext.get
- dictionaryTaskCleaner.addTaskCompletionListener(context =>
- dicts.foreach { dictionary =>
- if (null != dictionary) {
- dictionary.clear
- }
+ val cacheProvider: CacheProvider = CacheProvider.getInstance
+ val forwardDictionaryCache: Cache[DictionaryColumnUniqueIdentifier, Dictionary] =
+ cacheProvider.createCache(CacheType.FORWARD_DICTIONARY, storepath)
+ val dicts: Seq[Dictionary] = getDictionary(absoluteTableIdentifiers,
+ forwardDictionaryCache)
+ val dictIndex = dicts.zipWithIndex.filter(x => x._1 != null).map(x => x._2)
+ // add a task completion listener to clear dictionary that is a decisive factor for
+ // LRU eviction policy
+ val dictionaryTaskCleaner = TaskContext.get
+ dictionaryTaskCleaner.addTaskCompletionListener(context =>
+ dicts.foreach { dictionary =>
+ if (null != dictionary) {
+ dictionary.clear
}
- )
- val iter = firstParent[Row].iterator(split, context)
- new Iterator[Row] {
- var flag = true
- var total = 0L
- override final def hasNext: Boolean = iter.hasNext
+ }
+ )
+ val iter = firstParent[Row].iterator(split, context)
+ new Iterator[Row] {
+ var flag = true
+ var total = 0L
+ override final def hasNext: Boolean = iter.hasNext
- override final def next(): Row = {
- val startTime = System.currentTimeMillis()
- val data = iter.next().asInstanceOf[GenericRow].toSeq.toArray
- dictIndex.foreach { index =>
- if ( data(index) != null) {
- data(index) = DataTypeUtil.getDataBasedOnDataType(dicts(index)
- .getDictionaryValueForKey(data(index).asInstanceOf[Int]),
- getDictionaryColumnIds(index)._3)
- }
+ override final def next(): Row = {
+ val startTime = System.currentTimeMillis()
+ val data = iter.next().asInstanceOf[GenericRow].toSeq.toArray
+ dictIndex.foreach { index =>
+ if ( data(index) != null) {
+ data(index) = DataTypeUtil.getDataBasedOnDataType(dicts(index)
+ .getDictionaryValueForKey(data(index).asInstanceOf[Int]),
+ getDictionaryColumnIds(index)._3)
}
- new GenericRow(data)
}
+ new GenericRow(data)
}
+ }
}
private def isRequiredToDecode = {
http://git-wip-us.apache.org/repos/asf/incubator-carbondata/blob/462f6422/integration/spark2/src/main/scala/org/apache/spark/sql/CarbonScan.scala
----------------------------------------------------------------------
diff --git a/integration/spark2/src/main/scala/org/apache/spark/sql/CarbonScan.scala b/integration/spark2/src/main/scala/org/apache/spark/sql/CarbonScan.scala
index 9e42b44..19c3c9c 100644
--- a/integration/spark2/src/main/scala/org/apache/spark/sql/CarbonScan.scala
+++ b/integration/spark2/src/main/scala/org/apache/spark/sql/CarbonScan.scala
@@ -78,39 +78,27 @@ case class CarbonScan(
attributesRaw = attributeOut
}
- val dimensions = carbonTable.getDimensionByTableName(carbonTable.getFactTableName)
- val measures = carbonTable.getMeasureByTableName(carbonTable.getFactTableName)
- val dimAttr = new Array[Attribute](dimensions.size())
- val msrAttr = new Array[Attribute](measures.size())
+ val columns = carbonTable.getCreateOrderColumn(carbonTable.getFactTableName)
+ val colAttr = new Array[Attribute](columns.size())
attributesRaw.foreach { attr =>
- val carbonDimension =
- carbonTable.getDimensionByName(carbonTable.getFactTableName, attr.name)
- if(carbonDimension != null) {
- dimAttr(dimensions.indexOf(carbonDimension)) = attr
- } else {
- val carbonMeasure =
- carbonTable.getMeasureByName(carbonTable.getFactTableName, attr.name)
- if(carbonMeasure != null) {
- msrAttr(measures.indexOf(carbonMeasure)) = attr
- }
- }
+ val column =
+ carbonTable.getColumnByName(carbonTable.getFactTableName, attr.name)
+ if(column != null) {
+ colAttr(columns.indexOf(column)) = attr
+ }
}
-
- attributesRaw = dimAttr.filter(f => f != null) ++ msrAttr.filter(f => f != null)
+ attributesRaw = colAttr.filter(f => f != null)
var queryOrder: Integer = 0
attributesRaw.foreach { attr =>
- val carbonDimension =
- carbonTable.getDimensionByName(carbonTable.getFactTableName, attr.name)
- if (carbonDimension != null) {
- val dim = new QueryDimension(attr.name)
- dim.setQueryOrder(queryOrder)
- queryOrder = queryOrder + 1
- selectedDims += dim
- } else {
- val carbonMeasure =
- carbonTable.getMeasureByName(carbonTable.getFactTableName, attr.name)
- if (carbonMeasure != null) {
+ val carbonColumn = carbonTable.getColumnByName(carbonTable.getFactTableName, attr.name)
+ if (carbonColumn != null) {
+ if (carbonColumn.isDimesion()) {
+ val dim = new QueryDimension(attr.name)
+ dim.setQueryOrder(queryOrder)
+ queryOrder = queryOrder + 1
+ selectedDims += dim
+ } else {
val m1 = new QueryMeasure(attr.name)
m1.setQueryOrder(queryOrder)
queryOrder = queryOrder + 1
http://git-wip-us.apache.org/repos/asf/incubator-carbondata/blob/462f6422/integration/spark2/src/main/scala/org/apache/spark/sql/CarbonSource.scala
----------------------------------------------------------------------
diff --git a/integration/spark2/src/main/scala/org/apache/spark/sql/CarbonSource.scala b/integration/spark2/src/main/scala/org/apache/spark/sql/CarbonSource.scala
index 2ba8a03..b639ea8 100644
--- a/integration/spark2/src/main/scala/org/apache/spark/sql/CarbonSource.scala
+++ b/integration/spark2/src/main/scala/org/apache/spark/sql/CarbonSource.scala
@@ -30,6 +30,7 @@ import org.apache.spark.sql.sources._
import org.apache.spark.sql.types.{DecimalType, StructType}
import org.apache.carbondata.core.constants.CarbonCommonConstants
+import org.apache.carbondata.core.util.CarbonProperties
import org.apache.carbondata.spark.CarbonOption
/**
@@ -54,7 +55,7 @@ class CarbonSource extends CreatableRelationProvider
"the path to store carbon file is the 'storePath' specified when creating CarbonContext")
val options = new CarbonOption(parameters)
- val storePath = sqlContext.sparkSession.conf.get(CarbonCommonConstants.STORE_LOCATION)
+ val storePath = CarbonProperties.getInstance().getProperty(CarbonCommonConstants.STORE_LOCATION)
val tablePath = new Path(storePath + "/" + options.dbName + "/" + options.tableName)
val isExists = tablePath.getFileSystem(sqlContext.sparkContext.hadoopConfiguration)
.exists(tablePath)
http://git-wip-us.apache.org/repos/asf/incubator-carbondata/blob/462f6422/integration/spark2/src/main/scala/org/apache/spark/sql/optimizer/CarbonLateDecodeRule.scala
----------------------------------------------------------------------
diff --git a/integration/spark2/src/main/scala/org/apache/spark/sql/optimizer/CarbonLateDecodeRule.scala b/integration/spark2/src/main/scala/org/apache/spark/sql/optimizer/CarbonLateDecodeRule.scala
index fb9df70..b3a7d5a 100644
--- a/integration/spark2/src/main/scala/org/apache/spark/sql/optimizer/CarbonLateDecodeRule.scala
+++ b/integration/spark2/src/main/scala/org/apache/spark/sql/optimizer/CarbonLateDecodeRule.scala
@@ -165,34 +165,27 @@ class CarbonLateDecodeRule extends Rule[LogicalPlan] with PredicateHelper {
case union: Union
if !(union.children(0).isInstanceOf[CarbonDictionaryTempDecoder] ||
union.children(1).isInstanceOf[CarbonDictionaryTempDecoder]) =>
- val leftCondAttrs = new util.HashSet[AttributeReferenceWrapper]
- val rightCondAttrs = new util.HashSet[AttributeReferenceWrapper]
- union.children(0).output.foreach(attr =>
- leftCondAttrs.add(AttributeReferenceWrapper(aliasMap.getOrElse(attr, attr))))
- union.children(1).output.foreach(attr =>
- rightCondAttrs.add(AttributeReferenceWrapper(aliasMap.getOrElse(attr, attr))))
- var leftPlan = union.children(0)
- var rightPlan = union.children(1)
- if (hasCarbonRelation(leftPlan) && leftCondAttrs.size() > 0 &&
- !leftPlan.isInstanceOf[CarbonDictionaryCatalystDecoder]) {
- leftPlan = CarbonDictionaryTempDecoder(leftCondAttrs,
- new util.HashSet[AttributeReferenceWrapper](),
- union.children(0))
- }
- if (hasCarbonRelation(rightPlan) && rightCondAttrs.size() > 0 &&
- !rightPlan.isInstanceOf[CarbonDictionaryCatalystDecoder]) {
- rightPlan = CarbonDictionaryTempDecoder(rightCondAttrs,
- new util.HashSet[AttributeReferenceWrapper](),
- union.children(1))
+ val children = union.children.map { child =>
+ val condAttrs = new util.HashSet[AttributeReferenceWrapper]
+ child.output.foreach(attr =>
+ condAttrs.add(AttributeReferenceWrapper(aliasMap.getOrElse(attr, attr))))
+ if (hasCarbonRelation(child) && condAttrs.size() > 0 &&
+ !child.isInstanceOf[CarbonDictionaryCatalystDecoder]) {
+ CarbonDictionaryTempDecoder(condAttrs,
+ new util.HashSet[AttributeReferenceWrapper](),
+ union.children(0))
+ } else {
+ child
+ }
}
if (!decoder) {
decoder = true
CarbonDictionaryTempDecoder(new util.HashSet[AttributeReferenceWrapper](),
new util.HashSet[AttributeReferenceWrapper](),
- Union(leftPlan, rightPlan),
+ Union(children),
isOuter = true)
} else {
- Union(leftPlan, rightPlan)
+ Union(children)
}
case agg: Aggregate if !agg.child.isInstanceOf[CarbonDictionaryTempDecoder] =>
val attrsOndimAggs = new util.HashSet[AttributeReferenceWrapper]
@@ -487,68 +480,32 @@ class CarbonLateDecodeRule extends Rule[LogicalPlan] with PredicateHelper {
case cd: CarbonDictionaryCatalystDecoder =>
cd
case sort: Sort =>
- val tmpAttrMap = new mutable.HashMap[AttributeReferenceWrapper, Attribute]()
- if (sort.child.isInstanceOf[CarbonDictionaryTempDecoder]) {
- val tempDecoder = sort.child.asInstanceOf[CarbonDictionaryTempDecoder]
- tempDecoder.attrList.asScala.foreach{attr => tmpAttrMap.put(attr, attr.attr)}
- }
val sortExprs = sort.order.map { s =>
s.transform {
case attr: AttributeReference =>
- val tempAttr = tmpAttrMap.get(AttributeReferenceWrapper(attr))
- if(tempAttr.isDefined) {
- tempAttr.get
- } else {
- updateDataType(attr, attrMap, allAttrsNotDecode, aliasMap)
- }
+ updateDataType(attr, attrMap, allAttrsNotDecode, aliasMap)
}.asInstanceOf[SortOrder]
}
Sort(sortExprs, sort.global, sort.child)
case agg: Aggregate if !agg.child.isInstanceOf[CarbonDictionaryCatalystDecoder] =>
- val tmpAttrMap = new mutable.HashMap[AttributeReferenceWrapper, Attribute]()
- if (agg.child.isInstanceOf[CarbonDictionaryTempDecoder]) {
- val tempDecoder = agg.child.asInstanceOf[CarbonDictionaryTempDecoder]
- tempDecoder.attrList.asScala.foreach{attr => tmpAttrMap.put(attr, attr.attr)}
- }
-
val aggExps = agg.aggregateExpressions.map { aggExp =>
aggExp.transform {
case attr: AttributeReference =>
- val tempAttr = tmpAttrMap.get(AttributeReferenceWrapper(attr))
- if(tempAttr.isDefined) {
- tempAttr.get
- } else {
- updateDataType(attr, attrMap, allAttrsNotDecode, aliasMap)
- }
+ updateDataType(attr, attrMap, allAttrsNotDecode, aliasMap)
}
}.asInstanceOf[Seq[NamedExpression]]
val grpExps = agg.groupingExpressions.map { gexp =>
gexp.transform {
case attr: AttributeReference =>
- val tempAttr = tmpAttrMap.get(AttributeReferenceWrapper(attr))
- if(tempAttr.isDefined) {
- tempAttr.get
- } else {
- updateDataType(attr, attrMap, allAttrsNotDecode, aliasMap)
- }
+ updateDataType(attr, attrMap, allAttrsNotDecode, aliasMap)
}
}
Aggregate(grpExps, aggExps, agg.child)
case expand: Expand =>
- val tmpAttrMap = new mutable.HashMap[AttributeReferenceWrapper, Attribute]()
- if (expand.child.isInstanceOf[CarbonDictionaryTempDecoder]) {
- val tempDecoder = expand.child.asInstanceOf[CarbonDictionaryTempDecoder]
- tempDecoder.attrList.asScala.foreach{attr => tmpAttrMap.put(attr, attr.attr)}
- }
expand.transformExpressions {
case attr: AttributeReference =>
- val tempAttr = tmpAttrMap.get(AttributeReferenceWrapper(attr))
- if(tempAttr.isDefined) {
- tempAttr.get
- } else {
- updateDataType(attr, attrMap, allAttrsNotDecode, aliasMap)
- }
+ updateDataType(attr, attrMap, allAttrsNotDecode, aliasMap)
}
case filter: Filter =>
filter
@@ -559,71 +516,36 @@ class CarbonLateDecodeRule extends Rule[LogicalPlan] with PredicateHelper {
marker.pushBinaryMarker(allAttrsNotDecode)
u
case p: Project if relations.nonEmpty =>
- val tmpAttrMap = new mutable.HashMap[AttributeReferenceWrapper, Attribute]()
- if (p.child.isInstanceOf[CarbonDictionaryTempDecoder]) {
- val tempDecoder = p.child.asInstanceOf[CarbonDictionaryTempDecoder]
- tempDecoder.attrList.asScala.foreach{attr => tmpAttrMap.put(attr, attr.attr)}
- }
val prExps = p.projectList.map { prExp =>
prExp.transform {
case attr: AttributeReference =>
- val tempAttr = tmpAttrMap.get(AttributeReferenceWrapper(attr))
- if(tempAttr.isDefined) {
- tempAttr.get
- } else {
- updateDataType(attr, attrMap, allAttrsNotDecode, aliasMap)
- }
+ updateDataType(attr, attrMap, allAttrsNotDecode, aliasMap)
}
}.asInstanceOf[Seq[NamedExpression]]
Project(prExps, p.child)
case wd: Window if relations.nonEmpty =>
- val tmpAttrMap = new mutable.HashMap[AttributeReferenceWrapper, Attribute]()
- if (wd.child.isInstanceOf[CarbonDictionaryTempDecoder]) {
- val tempDecoder = wd.child.asInstanceOf[CarbonDictionaryTempDecoder]
- tempDecoder.attrList.asScala.foreach{attr => tmpAttrMap.put(attr, attr.attr)}
- }
val prExps = wd.output.map { prExp =>
prExp.transform {
case attr: AttributeReference =>
- val tempAttr = tmpAttrMap.get(AttributeReferenceWrapper(attr))
- if(tempAttr.isDefined) {
- tempAttr.get
- } else {
- updateDataType(attr, attrMap, allAttrsNotDecode, aliasMap)
- }
+ updateDataType(attr, attrMap, allAttrsNotDecode, aliasMap)
}
}.asInstanceOf[Seq[Attribute]]
val wdExps = wd.windowExpressions.map { gexp =>
gexp.transform {
case attr: AttributeReference =>
- val tempAttr = tmpAttrMap.get(AttributeReferenceWrapper(attr))
- if(tempAttr.isDefined) {
- tempAttr.get
- } else {
- updateDataType(attr, attrMap, allAttrsNotDecode, aliasMap)
- }
+ updateDataType(attr, attrMap, allAttrsNotDecode, aliasMap)
}
}.asInstanceOf[Seq[NamedExpression]]
val partitionSpec = wd.partitionSpec.map{ exp =>
exp.transform {
case attr: AttributeReference =>
- val tempAttr = tmpAttrMap.get(AttributeReferenceWrapper(attr))
- if(tempAttr.isDefined) {
- tempAttr.get
- } else {
- updateDataType(attr, attrMap, allAttrsNotDecode, aliasMap)
- }
+ updateDataType(attr, attrMap, allAttrsNotDecode, aliasMap)
}
}
val orderSpec = wd.orderSpec.map { exp =>
exp.transform {
case attr: AttributeReference =>
- val tempAttr = tmpAttrMap.get(AttributeReferenceWrapper(attr))
- if(tempAttr.isDefined) {
- tempAttr.get
- } else {
- updateDataType(attr, attrMap, allAttrsNotDecode, aliasMap)
- }
+ updateDataType(attr, attrMap, allAttrsNotDecode, aliasMap)
}
}.asInstanceOf[Seq[SortOrder]]
Window(wdExps, partitionSpec, orderSpec, wd.child)
http://git-wip-us.apache.org/repos/asf/incubator-carbondata/blob/462f6422/integration/spark2/src/test/resources/data.csv
----------------------------------------------------------------------
diff --git a/integration/spark2/src/test/resources/data.csv b/integration/spark2/src/test/resources/data.csv
new file mode 100644
index 0000000..4ff67da
--- /dev/null
+++ b/integration/spark2/src/test/resources/data.csv
@@ -0,0 +1,11 @@
+empno,empname,designation,doj,workgroupcategory,workgroupcategoryname,deptno,deptname,projectcode,projectjoindate,projectenddate,attendance,utilization,salary
+11,arvind,SE,17-01-2007,1,developer,10,network,928478,17-02-2007,29-11-2016,96,96.2,5040.56
+12,krithin,SSE,29-05-2008,1,developer,11,protocol,928378,29-06-2008,30-12-2016,85,95.1,7124.21
+13,madhan,TPL,07-07-2009,2,tester,10,network,928478,07-08-2009,30-12-2016,88,99,9054.235
+14,anandh,SA,29-12-2010,3,manager,11,protocol,928278,29-01-2011,29-06-2016,77,92.2,11248.25
+15,ayushi,SSA,09-11-2011,1,developer,12,security,928375,09-12-2011,29-05-2016,99,91.5,13245.48
+16,pramod,SE,14-10-2012,1,developer,13,configManagement,928478,14-11-2012,29-12-2016,86,93,5040.56
+17,gawrav,PL,22-09-2013,2,tester,12,security,928778,22-10-2013,15-11-2016,78,97.45,9574.24
+18,sibi,TL,15-08-2014,2,tester,14,Learning,928176,15-09-2014,29-05-2016,84,98.23,7245.25
+19,shivani,PL,12-05-2015,1,developer,10,network,928977,12-06-2015,12-11-2016,88,91.678,11254.24
+20,bill,PM,01-12-2015,3,manager,14,Learning,928479,01-01-2016,30-11-2016,75,94.22,13547.25
[4/4] incubator-carbondata git commit: [CARBONDATA-516][SPARK2]fix
union issue in CarbonLateDecoderRule This closes #413
Posted by ja...@apache.org.
[CARBONDATA-516][SPARK2]fix union issue in CarbonLateDecoderRule This closes #413
Project: http://git-wip-us.apache.org/repos/asf/incubator-carbondata/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-carbondata/commit/f5ecfbf5
Tree: http://git-wip-us.apache.org/repos/asf/incubator-carbondata/tree/f5ecfbf5
Diff: http://git-wip-us.apache.org/repos/asf/incubator-carbondata/diff/f5ecfbf5
Branch: refs/heads/master
Commit: f5ecfbf5cfb65e883e69606e7911381fd915683a
Parents: 526243b 462f642
Author: jackylk <ja...@huawei.com>
Authored: Fri Dec 16 10:37:47 2016 +0800
Committer: jackylk <ja...@huawei.com>
Committed: Fri Dec 16 10:37:47 2016 +0800
----------------------------------------------------------------------
.../impl/DictionaryBasedResultCollector.java | 29 +-
.../DictionaryBasedResultCollectorTest.java | 9 +-
.../carbondata/examples/CarbonExample.scala | 12 +
.../apache/carbondata/spark/CarbonFilters.scala | 7 +
.../CarbonDecoderOptimizerHelper.scala | 24 +-
.../readsupport/SparkRowReadSupportImpl.java | 5 +-
.../apache/carbondata/spark/CarbonFilters.scala | 6 +
.../spark/sql/CarbonDataFrameWriter.scala | 3 +-
.../sql/CarbonDatasourceHadoopRelation.scala | 4 +-
.../spark/sql/CarbonDictionaryDecoder.scala | 60 +-
.../scala/org/apache/spark/sql/CarbonScan.scala | 44 +-
.../org/apache/spark/sql/CarbonSource.scala | 3 +-
.../sql/optimizer/CarbonLateDecodeRule.scala | 124 +-
integration/spark2/src/test/resources/data.csv | 11 +
.../AllDataTypesTestCaseAggregate.scala | 1161 ++++++++++++++++++
.../spark/sql/common/util/CarbonFunSuite.scala | 49 +
.../sql/common/util/CarbonSessionTest.scala | 74 ++
.../apache/spark/sql/common/util/PlanTest.scala | 59 +
.../spark/sql/common/util/QueryTest.scala | 149 +++
19 files changed, 1654 insertions(+), 179 deletions(-)
----------------------------------------------------------------------