carbondata-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ravipes...@apache.org
Subject [1/3] incubator-carbondata git commit: AllDataTypesTestCase5 succeeded
Date Mon, 12 Dec 2016 13:05:01 GMT
Repository: incubator-carbondata
Updated Branches:
  refs/heads/master e478cf74c -> 09d4c7af2


http://git-wip-us.apache.org/repos/asf/incubator-carbondata/blob/a738d525/integration-testcases/src/test/scala/org/apache/spark/testsuite/allqueries/AllDataTypesTestCase5.scala
----------------------------------------------------------------------
diff --git a/integration-testcases/src/test/scala/org/apache/spark/testsuite/allqueries/AllDataTypesTestCase5.scala b/integration-testcases/src/test/scala/org/apache/spark/testsuite/allqueries/AllDataTypesTestCase5.scala
index ddf0d39..339eb4c 100644
--- a/integration-testcases/src/test/scala/org/apache/spark/testsuite/allqueries/AllDataTypesTestCase5.scala
+++ b/integration-testcases/src/test/scala/org/apache/spark/testsuite/allqueries/AllDataTypesTestCase5.scala
@@ -30,9 +30,9 @@ import org.apache.carbondata.core.constants.CarbonCommonConstants
 import org.apache.carbondata.core.util.CarbonProperties
 
 /**
-  * Test Class for all queries on multiple datatypes
-  * Manohar
-  */
+ * Test Class for all queries on multiple datatypes
+ * Manohar
+ */
 class AllDataTypesTestCase5 extends QueryTest with BeforeAndAfterAll {
 
   override def beforeAll {
@@ -67,8 +67,9 @@ class AllDataTypesTestCase5 extends QueryTest with BeforeAndAfterAll {
         "Latest_operatorsVersion string, Latest_phonePADPartitionedVersions string, " +
         "Latest_operatorId string, gamePointDescription string,gamePointId decimal,contractNumber" +
         " decimal) stored by 'org.apache.carbondata.format'")
-      sql("LOAD DATA LOCAL INPATH '" + currentDirectory +"/src/test/resources/Vmall_100_olap.csv' INTO table " +
-          "Carbon_automation_vmall_test1 OPTIONS('DELIMITER'= ',' ,'QUOTECHAR'= '\\\"', " +
+      sql("LOAD DATA LOCAL INPATH '" + currentDirectory +
+          "/src/test/resources/Vmall_100_olap.csv' INTO table " +
+          "Carbon_automation_vmall_test1 OPTIONS('DELIMITER'= ',' ,'QUOTECHAR'= '\"', " +
           "'FILEHEADER'= 'imei,deviceInformationId,MAC,deviceColor,device_backColor,modelId," +
           "marketName,AMSize,ROMSize,CUPAudit,CPIClocked,series,productionDate,bomCode," +
           "internalModels,deliveryTime,channelsId,channelsName,deliveryAreaId,deliveryCountry," +
@@ -103,15 +104,19 @@ class AllDataTypesTestCase5 extends QueryTest with BeforeAndAfterAll {
         "Latest_BacFlashVer string, Latest_webUIVersion string, Latest_webUITypeCarrVer string, " +
         "Latest_webTypeDataVerNumber string, Latest_operatorsVersion string, " +
         "Latest_phonePADPartitionedVersions string, Latest_operatorId string, " +
-        "gamePointDescription string, gamePointId int,contractNumber int) stored by 'org.apache" +
+        " gamePointId int,contractNumber int,gamePointDescription string) stored by 'org.apache" +
         ".carbondata.format'")
+      CarbonProperties.getInstance()
+        .addProperty(CarbonCommonConstants.CARBON_TIMESTAMP_FORMAT,
+          CarbonCommonConstants.CARBON_TIMESTAMP_DEFAULT_FORMAT
+        )
       sql("LOAD DATA LOCAL INPATH '" + currentDirectory +
           "/src/test/resources/100_olap.csv' INTO table Carbon_automation_test5 OPTIONS" +
           "('DELIMITER'= ',' ,'QUOTECHAR'= '\"', 'FILEHEADER'= 'imei,deviceInformationId,MAC," +
           "deviceColor,device_backColor,modelId,marketName,AMSize,ROMSize,CUPAudit,CPIClocked," +
           "series,productionDate,bomCode,internalModels,deliveryTime,channelsId,channelsName," +
           "deliveryAreaId,deliveryCountry,deliveryProvince,deliveryCity,deliveryDistrict," +
-          "deliveryStreet,oxSingleNumber,contractNumber,ActiveCheckTime,ActiveAreaId," +
+          "deliveryStreet,oxSingleNumber,ActiveCheckTime,ActiveAreaId," +
           "ActiveCountry,ActiveProvince,Activecity,ActiveDistrict,ActiveStreet,ActiveOperatorId," +
           "Active_releaseId,Active_EMUIVersion,Active_operaSysVersion,Active_BacVerNumber," +
           "Active_BacFlashVer,Active_webUIVersion,Active_webUITypeCarrVer," +
@@ -121,7 +126,36 @@ class AllDataTypesTestCase5 extends QueryTest with BeforeAndAfterAll {
           "Latest_street,Latest_releaseId,Latest_EMUIVersion,Latest_operaSysVersion," +
           "Latest_BacVerNumber,Latest_BacFlashVer,Latest_webUIVersion,Latest_webUITypeCarrVer," +
           "Latest_webTypeDataVerNumber,Latest_operatorsVersion," +
-          "Latest_phonePADPartitionedVersions,Latest_operatorId,gamePointId,gamePointDescription')")
+          "Latest_phonePADPartitionedVersions,Latest_operatorId,gamePointId," +
+          "contractNumber,gamePointDescription')")
+
+
+      sql(
+        "create table Carbon_automation_test5_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 " +
+        "string,bomCode string,internalModels string, deliveryTime string, channelsId string, " +
+        "channelsName string , deliveryAreaId string, deliveryCountry string, deliveryProvince " +
+        "string, deliveryCity string,deliveryDistrict string, deliveryStreet string, " +
+        "oxSingleNumber string, 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_operatorsVersion 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,contractNumber 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_test5_hive")
     } catch {
       case e: Exception => print("ERROR : " + e.getMessage)
     }
@@ -132,6 +166,7 @@ class AllDataTypesTestCase5 extends QueryTest with BeforeAndAfterAll {
     try {
       sql("drop table Carbon_automation_vmall_test1")
       sql("drop table Carbon_automation_test5")
+      sql("drop table Carbon_automation_test5_hive")
     } catch {
       case e: Exception => print("ERROR : " + e.getMessage)
     }
@@ -141,41 +176,40 @@ class AllDataTypesTestCase5 extends QueryTest with BeforeAndAfterAll {
   //TC_508
   test(
     "SELECT  Carbon_automation_test5.gamePointId AS gamePointId,Carbon_automation_test5.AMSize AS" +
-      " AMSize, Carbon_automation_test5.ActiveCountry AS ActiveCountry, Carbon_automation_test5" +
-      ".Activecity AS Activecity FROM ( SELECT AMSize,gamePointId, ActiveCountry, Activecity FROM" +
-      " (select * from Carbon_automation_test5) SUB_QRY ) Carbon_automation_test5 INNER JOIN ( " +
-      "SELECT ActiveCountry, Activecity, AMSize FROM (select * from Carbon_automation_test5) " +
-      "SUB_QRY ) Carbon_automation_vmall_test1 ON Carbon_automation_test5.AMSize = " +
-      "Carbon_automation_vmall_test1.AMSize WHERE Carbon_automation_test5.AMSize LIKE '%1%' GROUP" +
-      " BY Carbon_automation_test5.AMSize, Carbon_automation_test5.ActiveCountry, " +
-      "Carbon_automation_test5.Activecity,Carbon_automation_test5. gamePointId ORDER BY " +
-      "Carbon_automation_test5.AMSize ASC, Carbon_automation_test5.ActiveCountry ASC, " +
-      "Carbon_automation_test5.Activecity ASC"
+    " AMSize, Carbon_automation_test5.ActiveCountry AS ActiveCountry, Carbon_automation_test5" +
+    ".Activecity AS Activecity FROM ( SELECT AMSize,gamePointId, ActiveCountry, Activecity FROM" +
+    " (select * from Carbon_automation_test5) SUB_QRY ) Carbon_automation_test5 INNER JOIN ( " +
+    "SELECT ActiveCountry, Activecity, AMSize FROM (select * from Carbon_automation_test5) " +
+    "SUB_QRY ) Carbon_automation_vmall_test1 ON Carbon_automation_test5.AMSize = " +
+    "Carbon_automation_vmall_test1.AMSize WHERE Carbon_automation_test5.AMSize LIKE '%1%' GROUP" +
+    " BY Carbon_automation_test5.AMSize, Carbon_automation_test5.ActiveCountry, " +
+    "Carbon_automation_test5.Activecity,Carbon_automation_test5. gamePointId ORDER BY " +
+    "Carbon_automation_test5.AMSize ASC, Carbon_automation_test5.ActiveCountry ASC, " +
+    "Carbon_automation_test5.Activecity ASC"
   )({
     checkAnswer(
       sql(
         "SELECT  Carbon_automation_test5.gamePointId AS gamePointId,Carbon_automation_test5" +
-          ".AMSize AS AMSize, Carbon_automation_test5.ActiveCountry AS ActiveCountry, " +
-          "Carbon_automation_test5.Activecity AS Activecity FROM ( SELECT AMSize,gamePointId, " +
-          "ActiveCountry, Activecity FROM (select * from Carbon_automation_test5) SUB_QRY ) " +
-          "Carbon_automation_test5 INNER JOIN ( SELECT ActiveCountry, Activecity, AMSize FROM " +
-          "(select * from Carbon_automation_test5) SUB_QRY ) Carbon_automation_vmall_test1 ON " +
-          "Carbon_automation_test5.AMSize = Carbon_automation_vmall_test1.AMSize WHERE " +
-          "Carbon_automation_test5.AMSize LIKE '%1%' GROUP BY Carbon_automation_test5.AMSize, " +
-          "Carbon_automation_test5.ActiveCountry, Carbon_automation_test5.Activecity," +
-          "Carbon_automation_test5. gamePointId ORDER BY Carbon_automation_test5.AMSize ASC, " +
-          "Carbon_automation_test5.ActiveCountry ASC, Carbon_automation_test5.Activecity ASC"
+        ".AMSize AS AMSize, Carbon_automation_test5.ActiveCountry AS ActiveCountry, " +
+        "Carbon_automation_test5.Activecity AS Activecity FROM ( SELECT AMSize,gamePointId, " +
+        "ActiveCountry, Activecity FROM (select * from Carbon_automation_test5) SUB_QRY ) " +
+        "Carbon_automation_test5 INNER JOIN ( SELECT ActiveCountry, Activecity, AMSize FROM " +
+        "(select * from Carbon_automation_test5) SUB_QRY ) Carbon_automation_vmall_test1 ON " +
+        "Carbon_automation_test5.AMSize = Carbon_automation_vmall_test1.AMSize WHERE " +
+        "Carbon_automation_test5.AMSize LIKE '%1%' GROUP BY Carbon_automation_test5.AMSize, " +
+        "Carbon_automation_test5.ActiveCountry, Carbon_automation_test5.Activecity," +
+        "Carbon_automation_test5. gamePointId ORDER BY Carbon_automation_test5.AMSize ASC, " +
+        "Carbon_automation_test5.ActiveCountry ASC, Carbon_automation_test5.Activecity ASC"
       ),
-      Seq(Row(1333.0, "1RAM size", "Chinese", "guangzhou"),
-        Row(256.0, "1RAM size", "Chinese", "shenzhen"),
-        Row(2175.0, "1RAM size", "Chinese", "xiangtan"),
-        Row(202.0, "1RAM size", "Chinese", "xiangtan"),
-        Row(2734.0, "1RAM size", "Chinese", "xiangtan"),
-        Row(2399.0, "1RAM size", "Chinese", "xiangtan"),
-        Row(2078.0, "1RAM size", "Chinese", "yichang"),
-        Row(1864.0, "1RAM size", "Chinese", "yichang"),
-        Row(2745.0, "1RAM size", "Chinese", "zhuzhou")
-      )
+      Seq(Row(100020, "1RAM size", "1", "Guangdong Province"),
+        Row(100030, "1RAM size", "2", "Guangdong Province"),
+        Row(10000, "1RAM size", "4", "Hunan Province"),
+        Row(100041, "1RAM size", "4", "Hunan Province"),
+        Row(100048, "1RAM size", "4", "Hunan Province"),
+        Row(100011, "1RAM size", "4", "Hunan Province"),
+        Row(100042, "1RAM size", "5", "Hunan Province"),
+        Row(100040, "1RAM size", "7", "Hubei Province"),
+        Row(100066, "1RAM size", "7", "Hubei Province"))
     )
   }
   )
@@ -183,62 +217,49 @@ class AllDataTypesTestCase5 extends QueryTest with BeforeAndAfterAll {
   //TC_518
   test(
     "SELECT Carbon_automation_test5.AMSize AS AMSize, Carbon_automation_test5.ActiveCountry AS " +
-      "ActiveCountry, Carbon_automation_test5.Activecity AS Activecity , SUM" +
-      "(Carbon_automation_test5.gamePointId) AS Sum_gamePointId FROM ( SELECT AMSize, " +
-      "ActiveCountry,gamePointId, Activecity FROM (select * from Carbon_automation_test5) SUB_QRY" +
-      " ) Carbon_automation_test5 INNER JOIN ( SELECT ActiveCountry, Activecity, AMSize FROM " +
-      "(select * from Carbon_automation_test5) SUB_QRY ) Carbon_automation_vmall_test1 ON " +
-      "Carbon_automation_test5.AMSize = Carbon_automation_vmall_test1.AMSize WHERE NOT" +
-      "(Carbon_automation_test5.AMSize <= \"3RAM size\") GROUP BY Carbon_automation_test5.AMSize," +
-      " Carbon_automation_test5.ActiveCountry, Carbon_automation_test5.Activecity ORDER BY " +
-      "Carbon_automation_test5.AMSize ASC, Carbon_automation_test5.ActiveCountry ASC, " +
-      "Carbon_automation_test5.Activecity ASC"
+    "ActiveCountry, Carbon_automation_test5.Activecity AS Activecity , SUM" +
+    "(Carbon_automation_test5.gamePointId) AS Sum_gamePointId FROM ( SELECT AMSize, " +
+    "ActiveCountry,gamePointId, Activecity FROM (select * from Carbon_automation_test5) SUB_QRY" +
+    " ) Carbon_automation_test5 INNER JOIN ( SELECT ActiveCountry, Activecity, AMSize FROM " +
+    "(select * from Carbon_automation_test5) SUB_QRY ) Carbon_automation_vmall_test1 ON " +
+    "Carbon_automation_test5.AMSize = Carbon_automation_vmall_test1.AMSize WHERE NOT" +
+    "(Carbon_automation_test5.AMSize <= \"3RAM size\") GROUP BY Carbon_automation_test5.AMSize," +
+    " Carbon_automation_test5.ActiveCountry, Carbon_automation_test5.Activecity ORDER BY " +
+    "Carbon_automation_test5.AMSize ASC, Carbon_automation_test5.ActiveCountry ASC, " +
+    "Carbon_automation_test5.Activecity ASC"
   )({
     checkAnswer(
       sql(
         "SELECT Carbon_automation_test5.AMSize AS AMSize, Carbon_automation_test5.ActiveCountry " +
-          "AS ActiveCountry, Carbon_automation_test5.Activecity AS Activecity , SUM" +
-          "(Carbon_automation_test5.gamePointId) AS Sum_gamePointId FROM ( SELECT AMSize, " +
-          "ActiveCountry,gamePointId, Activecity FROM (select * from Carbon_automation_test5) " +
-          "SUB_QRY ) Carbon_automation_test5 INNER JOIN ( SELECT ActiveCountry, Activecity, " +
-          "AMSize FROM (select * from Carbon_automation_test5) SUB_QRY ) " +
-          "Carbon_automation_vmall_test1 ON Carbon_automation_test5.AMSize = " +
-          "Carbon_automation_vmall_test1.AMSize WHERE NOT(Carbon_automation_test5.AMSize <= " +
-          "\"3RAM size\") GROUP BY Carbon_automation_test5.AMSize, Carbon_automation_test5" +
-          ".ActiveCountry, Carbon_automation_test5.Activecity ORDER BY Carbon_automation_test5" +
-          ".AMSize ASC, Carbon_automation_test5.ActiveCountry ASC, Carbon_automation_test5" +
-          ".Activecity ASC"
+        "AS ActiveCountry, Carbon_automation_test5.Activecity AS Activecity , SUM" +
+        "(Carbon_automation_test5.gamePointId) AS Sum_gamePointId FROM ( SELECT AMSize, " +
+        "ActiveCountry,gamePointId, Activecity FROM (select * from Carbon_automation_test5) " +
+        "SUB_QRY ) Carbon_automation_test5 INNER JOIN ( SELECT ActiveCountry, Activecity, " +
+        "AMSize FROM (select * from Carbon_automation_test5) SUB_QRY ) " +
+        "Carbon_automation_vmall_test1 ON Carbon_automation_test5.AMSize = " +
+        "Carbon_automation_vmall_test1.AMSize WHERE NOT(Carbon_automation_test5.AMSize <= " +
+        "\"3RAM size\") GROUP BY Carbon_automation_test5.AMSize, Carbon_automation_test5" +
+        ".ActiveCountry, Carbon_automation_test5.Activecity ORDER BY Carbon_automation_test5" +
+        ".AMSize ASC, Carbon_automation_test5.ActiveCountry ASC, Carbon_automation_test5" +
+        ".Activecity ASC"
       ),
-      Seq(Row("4RAM size", "Chinese", "changsha", 200860.0),
-        Row("4RAM size", "Chinese", "guangzhou", 38016.0),
-        Row("4RAM size", "Chinese", "shenzhen", 49610.0),
-        Row("4RAM size", "Chinese", "wuhan", 117581.96999999999),
-        Row("4RAM size", "Chinese", "xiangtan", 254320.0),
-        Row("4RAM size", "Chinese", "yichang", 29436.0),
-        Row("5RAM size", "Chinese", "changsha", 13845.0),
-        Row("5RAM size", "Chinese", "guangzhou", 23560.0),
-        Row("5RAM size", "Chinese", "wuhan", 12390.0),
-        Row("6RAM size", "Chinese", "changsha", 23697.0),
-        Row("6RAM size", "Chinese", "guangzhou", 15912.0),
-        Row("6RAM size", "Chinese", "shenzhen", 19278.0),
-        Row("6RAM size", "Chinese", "wuhan", 29313.0),
-        Row("6RAM size", "Chinese", "xiangtan", 7794.0),
-        Row("6RAM size", "Chinese", "zhuzhou", 26568.0),
-        Row("7RAM size", "Chinese", "changsha", 1057.0),
-        Row("7RAM size", "Chinese", "wuhan", 27853.0),
-        Row("7RAM size", "Chinese", "yichang", 14217.0),
-        Row("7RAM size", "Chinese", "zhuzhou", 15673.0),
-        Row("8RAM size", "Chinese", "guangzhou", 27385.619999999995),
-        Row("8RAM size", "Chinese", "shenzhen", 3550.0),
-        Row("8RAM size", "Chinese", "wuhan", 29700.0),
-        Row("8RAM size", "Chinese", "xiangtan", 31020.0),
-        Row("8RAM size", "Chinese", "yichang", 51660.0),
-        Row("8RAM size", "Chinese", "zhuzhou", 26840.0),
-        Row("9RAM size", "Chinese", "changsha", 32390.0),
-        Row("9RAM size", "Chinese", "shenzhen", 30650.0),
-        Row("9RAM size", "Chinese", "wuhan", 15670.0),
-        Row("9RAM size", "Chinese", "xiangtan", 58210.0),
-        Row("9RAM size", "Chinese", "yichang", 5710.0)
+      sql(
+        "SELECT Carbon_automation_test5_hive.AMSize AS AMSize, Carbon_automation_test5_hive" +
+        ".ActiveCountry " +
+        "AS ActiveCountry, Carbon_automation_test5_hive.Activecity AS Activecity , SUM" +
+        "(Carbon_automation_test5_hive.gamePointId) AS Sum_gamePointId FROM ( SELECT AMSize, " +
+        "ActiveCountry,gamePointId, Activecity FROM (select * from Carbon_automation_test5_hive) " +
+        "SUB_QRY ) Carbon_automation_test5_hive INNER JOIN ( SELECT ActiveCountry, Activecity, " +
+        "AMSize FROM (select * from Carbon_automation_test5_hive) SUB_QRY ) " +
+        "Carbon_automation_vmall_test1 ON Carbon_automation_test5_hive.AMSize = " +
+        "Carbon_automation_vmall_test1.AMSize WHERE NOT(Carbon_automation_test5_hive.AMSize <= " +
+        "\"3RAM size\") GROUP BY Carbon_automation_test5_hive.AMSize, " +
+        "Carbon_automation_test5_hive" +
+        ".ActiveCountry, Carbon_automation_test5_hive.Activecity ORDER BY " +
+        "Carbon_automation_test5_hive" +
+        ".AMSize ASC, Carbon_automation_test5_hive.ActiveCountry ASC, " +
+        "Carbon_automation_test5_hive" +
+        ".Activecity ASC"
       )
     )
   }
@@ -247,30 +268,32 @@ class AllDataTypesTestCase5 extends QueryTest with BeforeAndAfterAll {
   //TC_519
   test(
     "SELECT Carbon_automation_test5.Activecity AS Activecity FROM ( SELECT AMSize, ActiveCountry," +
-      " Activecity FROM (select * from Carbon_automation_test5) SUB_QRY ) Carbon_automation_test5" +
-      " INNER JOIN ( SELECT ActiveCountry, Activecity, AMSize FROM (select * from " +
-      "Carbon_automation_test5) SUB_QRY ) Carbon_automation_vmall_test1 ON " +
-      "Carbon_automation_test5.AMSize = Carbon_automation_vmall_test1.AMSize GROUP BY " +
-      "Carbon_automation_test5.Activecity ORDER BY Carbon_automation_test5.Activecity ASC LIMIT " +
-      "5000"
+    " Activecity FROM (select * from Carbon_automation_test5) SUB_QRY ) Carbon_automation_test5" +
+    " INNER JOIN ( SELECT ActiveCountry, Activecity, AMSize FROM (select * from " +
+    "Carbon_automation_test5) SUB_QRY ) Carbon_automation_vmall_test1 ON " +
+    "Carbon_automation_test5.AMSize = Carbon_automation_vmall_test1.AMSize GROUP BY " +
+    "Carbon_automation_test5.Activecity ORDER BY Carbon_automation_test5.Activecity ASC LIMIT " +
+    "5000"
   )({
     checkAnswer(
       sql(
         "SELECT Carbon_automation_test5.Activecity AS Activecity FROM ( SELECT AMSize, " +
-          "ActiveCountry, Activecity FROM (select * from Carbon_automation_test5) SUB_QRY ) " +
-          "Carbon_automation_test5 INNER JOIN ( SELECT ActiveCountry, Activecity, AMSize FROM " +
-          "(select * from Carbon_automation_test5) SUB_QRY ) Carbon_automation_vmall_test1 ON " +
-          "Carbon_automation_test5.AMSize = Carbon_automation_vmall_test1.AMSize GROUP BY " +
-          "Carbon_automation_test5.Activecity ORDER BY Carbon_automation_test5.Activecity ASC " +
-          "LIMIT 5000"
+        "ActiveCountry, Activecity FROM (select * from Carbon_automation_test5) SUB_QRY ) " +
+        "Carbon_automation_test5 INNER JOIN ( SELECT ActiveCountry, Activecity, AMSize FROM " +
+        "(select * from Carbon_automation_test5) SUB_QRY ) Carbon_automation_vmall_test1 ON " +
+        "Carbon_automation_test5.AMSize = Carbon_automation_vmall_test1.AMSize GROUP BY " +
+        "Carbon_automation_test5.Activecity ORDER BY Carbon_automation_test5.Activecity ASC " +
+        "LIMIT 5000"
       ),
-      Seq(Row("changsha"),
-        Row("guangzhou"),
-        Row("shenzhen"),
-        Row("wuhan"),
-        Row("xiangtan"),
-        Row("yichang"),
-        Row("zhuzhou")
+      sql(
+        "SELECT Carbon_automation_test5_hive.Activecity AS Activecity FROM ( SELECT AMSize, " +
+        "ActiveCountry, Activecity FROM (select * from Carbon_automation_test5_hive) SUB_QRY ) " +
+        "Carbon_automation_test5_hive INNER JOIN ( SELECT ActiveCountry, Activecity, AMSize FROM " +
+        "(select * from Carbon_automation_test5_hive) SUB_QRY ) Carbon_automation_vmall_test1 ON " +
+        "Carbon_automation_test5_hive.AMSize = Carbon_automation_vmall_test1.AMSize GROUP BY " +
+        "Carbon_automation_test5_hive.Activecity ORDER BY Carbon_automation_test5_hive.Activecity" +
+        " ASC " +
+        "LIMIT 5000"
       )
     )
   }
@@ -281,60 +304,36 @@ class AllDataTypesTestCase5 extends QueryTest with BeforeAndAfterAll {
     checkAnswer(
       sql(
         "SELECT Carbon_automation_test5.AMSize AS AMSize, Carbon_automation_test5.ActiveCountry " +
-          "AS ActiveCountry, Carbon_automation_test5.Activecity AS Activecity, SUM" +
-          "(Carbon_automation_test5.gamePointId) AS Sum_gamePointId FROM ( SELECT AMSize," +
-          "gamePointId, ActiveCountry, Activecity FROM (select * from Carbon_automation_test5) " +
-          "SUB_QRY ) Carbon_automation_test5 INNER JOIN ( SELECT ActiveCountry, Activecity, " +
-          "AMSize FROM (select * from Carbon_automation_test5) SUB_QRY ) " +
-          "Carbon_automation_vmall_test1 ON Carbon_automation_test5.AMSize = " +
-          "Carbon_automation_vmall_test1.AMSize WHERE NOT(Carbon_automation_test5.Activecity LIKE" +
-          " 'xian%') GROUP BY Carbon_automation_test5.AMSize, Carbon_automation_test5" +
-          ".ActiveCountry, Carbon_automation_test5.Activecity ORDER BY Carbon_automation_test5" +
-          ".AMSize ASC, Carbon_automation_test5.ActiveCountry ASC, Carbon_automation_test5" +
-          ".Activecity ASC"
+        "AS ActiveCountry, Carbon_automation_test5.Activecity AS Activecity, SUM" +
+        "(Carbon_automation_test5.gamePointId) AS Sum_gamePointId FROM ( SELECT AMSize," +
+        "gamePointId, ActiveCountry, Activecity FROM (select * from Carbon_automation_test5) " +
+        "SUB_QRY ) Carbon_automation_test5 INNER JOIN ( SELECT ActiveCountry, Activecity, " +
+        "AMSize FROM (select * from Carbon_automation_test5) SUB_QRY ) " +
+        "Carbon_automation_vmall_test1 ON Carbon_automation_test5.AMSize = " +
+        "Carbon_automation_vmall_test1.AMSize WHERE NOT(Carbon_automation_test5.Activecity LIKE" +
+        " 'xian%') GROUP BY Carbon_automation_test5.AMSize, Carbon_automation_test5" +
+        ".ActiveCountry, Carbon_automation_test5.Activecity ORDER BY Carbon_automation_test5" +
+        ".AMSize ASC, Carbon_automation_test5.ActiveCountry ASC, Carbon_automation_test5" +
+        ".Activecity ASC"
       ),
-      Seq(Row("0RAM size", "Chinese", "changsha", 84293.0),
-        Row("0RAM size", "Chinese", "guangzhou", 869.0),
-        Row("0RAM size", "Chinese", "shenzhen", 31339.0),
-        Row("0RAM size", "Chinese", "wuhan", 66902.0),
-        Row("0RAM size", "Chinese", "zhuzhou", 14751.0),
-        Row("1RAM size", "Chinese", "guangzhou", 11997.0),
-        Row("1RAM size", "Chinese", "shenzhen", 2304.0),
-        Row("1RAM size", "Chinese", "yichang", 35478.0),
-        Row("1RAM size", "Chinese", "zhuzhou", 24705.0),
-        Row("2RAM size", "Chinese", "changsha", 3946.0),
-        Row("3RAM size", "Chinese", "changsha", 40082.0),
-        Row("3RAM size", "Chinese", "guangzhou", 27986.0),
-        Row("3RAM size", "Chinese", "shenzhen", 92960.0),
-        Row("3RAM size", "Chinese", "wuhan", 36890.0),
-        Row("3RAM size", "Chinese", "yichang", 20874.0),
-        Row("3RAM size", "Chinese", "zhuzhou", 79786.0),
-        Row("4RAM size", "Chinese", "changsha", 200860.0),
-        Row("4RAM size", "Chinese", "guangzhou", 38016.0),
-        Row("4RAM size", "Chinese", "shenzhen", 49610.0),
-        Row("4RAM size", "Chinese", "wuhan", 117581.96999999999),
-        Row("4RAM size", "Chinese", "yichang", 29436.0),
-        Row("5RAM size", "Chinese", "changsha", 13845.0),
-        Row("5RAM size", "Chinese", "guangzhou", 23560.0),
-        Row("5RAM size", "Chinese", "wuhan", 12390.0),
-        Row("6RAM size", "Chinese", "changsha", 23697.0),
-        Row("6RAM size", "Chinese", "guangzhou", 15912.0),
-        Row("6RAM size", "Chinese", "shenzhen", 19278.0),
-        Row("6RAM size", "Chinese", "wuhan", 29313.0),
-        Row("6RAM size", "Chinese", "zhuzhou", 26568.0),
-        Row("7RAM size", "Chinese", "changsha", 1057.0),
-        Row("7RAM size", "Chinese", "wuhan", 27853.0),
-        Row("7RAM size", "Chinese", "yichang", 14217.0),
-        Row("7RAM size", "Chinese", "zhuzhou", 15673.0),
-        Row("8RAM size", "Chinese", "guangzhou", 27385.619999999995),
-        Row("8RAM size", "Chinese", "shenzhen", 3550.0),
-        Row("8RAM size", "Chinese", "wuhan", 29700.0),
-        Row("8RAM size", "Chinese", "yichang", 51660.0),
-        Row("8RAM size", "Chinese", "zhuzhou", 26840.0),
-        Row("9RAM size", "Chinese", "changsha", 32390.0),
-        Row("9RAM size", "Chinese", "shenzhen", 30650.0),
-        Row("9RAM size", "Chinese", "wuhan", 15670.0),
-        Row("9RAM size", "Chinese", "yichang", 5710.0)
+      sql(
+        "SELECT Carbon_automation_test5_hive.AMSize AS AMSize, Carbon_automation_test5_hive" +
+        ".ActiveCountry " +
+        "AS ActiveCountry, Carbon_automation_test5_hive.Activecity AS Activecity, SUM" +
+        "(Carbon_automation_test5_hive.gamePointId) AS Sum_gamePointId FROM ( SELECT AMSize," +
+        "gamePointId, ActiveCountry, Activecity FROM (select * from Carbon_automation_test5_hive)" +
+        " " +
+        "SUB_QRY ) Carbon_automation_test5_hive INNER JOIN ( SELECT ActiveCountry, Activecity, " +
+        "AMSize FROM (select * from Carbon_automation_test5_hive) SUB_QRY ) " +
+        "Carbon_automation_vmall_test1 ON Carbon_automation_test5_hive.AMSize = " +
+        "Carbon_automation_vmall_test1.AMSize WHERE NOT(Carbon_automation_test5_hive.Activecity " +
+        "LIKE" +
+        " 'xian%') GROUP BY Carbon_automation_test5_hive.AMSize, Carbon_automation_test5_hive" +
+        ".ActiveCountry, Carbon_automation_test5_hive.Activecity ORDER BY " +
+        "Carbon_automation_test5_hive" +
+        ".AMSize ASC, Carbon_automation_test5_hive.ActiveCountry ASC, " +
+        "Carbon_automation_test5_hive" +
+        ".Activecity ASC"
       )
     )
   }
@@ -343,62 +342,50 @@ class AllDataTypesTestCase5 extends QueryTest with BeforeAndAfterAll {
   //TC_522
   test(
     "SELECT Carbon_automation_test5.AMSize AS AMSize, Carbon_automation_test5.ActiveCountry AS " +
-      "ActiveCountry, Carbon_automation_test5.Activecity AS Activecity, SUM" +
-      "(Carbon_automation_test5.gamePointId) AS Sum_gamePointId FROM ( SELECT AMSize, " +
-      "ActiveCountry, Activecity,gamePointId  FROM (select * from Carbon_automation_test5) " +
-      "SUB_QRY ) Carbon_automation_test5 INNER JOIN ( SELECT ActiveCountry, Activecity, AMSize " +
-      "FROM (select * from Carbon_automation_test5) SUB_QRY ) Carbon_automation_vmall_test1 ON " +
-      "Carbon_automation_test5.AMSize = Carbon_automation_vmall_test1.AMSize WHERE NOT" +
-      "(Carbon_automation_test5.AMSize BETWEEN \"4RAM size\" AND \"7RAM size\") GROUP BY " +
-      "Carbon_automation_test5.AMSize, Carbon_automation_test5.ActiveCountry, " +
-      "Carbon_automation_test5.Activecity ORDER BY Carbon_automation_test5.AMSize ASC, " +
-      "Carbon_automation_test5.ActiveCountry ASC, Carbon_automation_test5.Activecity ASC"
+    "ActiveCountry, Carbon_automation_test5.Activecity AS Activecity, SUM" +
+    "(Carbon_automation_test5.gamePointId) AS Sum_gamePointId FROM ( SELECT AMSize, " +
+    "ActiveCountry, Activecity,gamePointId  FROM (select * from Carbon_automation_test5) " +
+    "SUB_QRY ) Carbon_automation_test5 INNER JOIN ( SELECT ActiveCountry, Activecity, AMSize " +
+    "FROM (select * from Carbon_automation_test5) SUB_QRY ) Carbon_automation_vmall_test1 ON " +
+    "Carbon_automation_test5.AMSize = Carbon_automation_vmall_test1.AMSize WHERE NOT" +
+    "(Carbon_automation_test5.AMSize BETWEEN \"4RAM size\" AND \"7RAM size\") GROUP BY " +
+    "Carbon_automation_test5.AMSize, Carbon_automation_test5.ActiveCountry, " +
+    "Carbon_automation_test5.Activecity ORDER BY Carbon_automation_test5.AMSize ASC, " +
+    "Carbon_automation_test5.ActiveCountry ASC, Carbon_automation_test5.Activecity ASC"
   )({
     checkAnswer(
       sql(
         "SELECT Carbon_automation_test5.AMSize AS AMSize, Carbon_automation_test5.ActiveCountry " +
-          "AS ActiveCountry, Carbon_automation_test5.Activecity AS Activecity, SUM" +
-          "(Carbon_automation_test5.gamePointId) AS Sum_gamePointId FROM ( SELECT AMSize, " +
-          "ActiveCountry, Activecity,gamePointId  FROM (select * from Carbon_automation_test5) " +
-          "SUB_QRY ) Carbon_automation_test5 INNER JOIN ( SELECT ActiveCountry, Activecity, " +
-          "AMSize FROM (select * from Carbon_automation_test5) SUB_QRY ) " +
-          "Carbon_automation_vmall_test1 ON Carbon_automation_test5.AMSize = " +
-          "Carbon_automation_vmall_test1.AMSize WHERE NOT(Carbon_automation_test5.AMSize BETWEEN " +
-          "\"4RAM size\" AND \"7RAM size\") GROUP BY Carbon_automation_test5.AMSize, " +
-          "Carbon_automation_test5.ActiveCountry, Carbon_automation_test5.Activecity ORDER BY " +
-          "Carbon_automation_test5.AMSize ASC, Carbon_automation_test5.ActiveCountry ASC, " +
-          "Carbon_automation_test5.Activecity ASC"
+        "AS ActiveCountry, Carbon_automation_test5.Activecity AS Activecity, SUM" +
+        "(Carbon_automation_test5.gamePointId) AS Sum_gamePointId FROM ( SELECT AMSize, " +
+        "ActiveCountry, Activecity,gamePointId  FROM (select * from Carbon_automation_test5) " +
+        "SUB_QRY ) Carbon_automation_test5 INNER JOIN ( SELECT ActiveCountry, Activecity, " +
+        "AMSize FROM (select * from Carbon_automation_test5) SUB_QRY ) " +
+        "Carbon_automation_vmall_test1 ON Carbon_automation_test5.AMSize = " +
+        "Carbon_automation_vmall_test1.AMSize WHERE NOT(Carbon_automation_test5.AMSize BETWEEN " +
+        "\"4RAM size\" AND \"7RAM size\") GROUP BY Carbon_automation_test5.AMSize, " +
+        "Carbon_automation_test5.ActiveCountry, Carbon_automation_test5.Activecity ORDER BY " +
+        "Carbon_automation_test5.AMSize ASC, Carbon_automation_test5.ActiveCountry ASC, " +
+        "Carbon_automation_test5.Activecity ASC"
       ),
-      Seq(Row("0RAM size", "Chinese", "changsha", 84293.0),
-        Row("0RAM size", "Chinese", "guangzhou", 869.0),
-        Row("0RAM size", "Chinese", "shenzhen", 31339.0),
-        Row("0RAM size", "Chinese", "wuhan", 66902.0),
-        Row("0RAM size", "Chinese", "zhuzhou", 14751.0),
-        Row("1RAM size", "Chinese", "guangzhou", 11997.0),
-        Row("1RAM size", "Chinese", "shenzhen", 2304.0),
-        Row("1RAM size", "Chinese", "xiangtan", 67590.0),
-        Row("1RAM size", "Chinese", "yichang", 35478.0),
-        Row("1RAM size", "Chinese", "zhuzhou", 24705.0),
-        Row("2RAM size", "Chinese", "changsha", 3946.0),
-        Row("2RAM size", "Chinese", "xiangtan", 2700.0),
-        Row("3RAM size", "Chinese", "changsha", 40082.0),
-        Row("3RAM size", "Chinese", "guangzhou", 27986.0),
-        Row("3RAM size", "Chinese", "shenzhen", 92960.0),
-        Row("3RAM size", "Chinese", "wuhan", 36890.0),
-        Row("3RAM size", "Chinese", "xiangtan", 53536.0),
-        Row("3RAM size", "Chinese", "yichang", 20874.0),
-        Row("3RAM size", "Chinese", "zhuzhou", 79786.0),
-        Row("8RAM size", "Chinese", "guangzhou", 27385.619999999995),
-        Row("8RAM size", "Chinese", "shenzhen", 3550.0),
-        Row("8RAM size", "Chinese", "wuhan", 29700.0),
-        Row("8RAM size", "Chinese", "xiangtan", 31020.0),
-        Row("8RAM size", "Chinese", "yichang", 51660.0),
-        Row("8RAM size", "Chinese", "zhuzhou", 26840.0),
-        Row("9RAM size", "Chinese", "changsha", 32390.0),
-        Row("9RAM size", "Chinese", "shenzhen", 30650.0),
-        Row("9RAM size", "Chinese", "wuhan", 15670.0),
-        Row("9RAM size", "Chinese", "xiangtan", 58210.0),
-        Row("9RAM size", "Chinese", "yichang", 5710.0)
+      sql(
+        "SELECT Carbon_automation_test5_hive.AMSize AS AMSize, Carbon_automation_test5_hive" +
+        ".ActiveCountry " +
+        "AS ActiveCountry, Carbon_automation_test5_hive.Activecity AS Activecity, SUM" +
+        "(Carbon_automation_test5_hive.gamePointId) AS Sum_gamePointId FROM ( SELECT AMSize, " +
+        "ActiveCountry, Activecity,gamePointId  FROM (select * from Carbon_automation_test5_hive)" +
+        " " +
+        "SUB_QRY ) Carbon_automation_test5_hive INNER JOIN ( SELECT ActiveCountry, Activecity, " +
+        "AMSize FROM (select * from Carbon_automation_test5_hive) SUB_QRY ) " +
+        "Carbon_automation_vmall_test1 ON Carbon_automation_test5_hive.AMSize = " +
+        "Carbon_automation_vmall_test1.AMSize WHERE NOT(Carbon_automation_test5_hive.AMSize " +
+        "BETWEEN " +
+        "\"4RAM size\" AND \"7RAM size\") GROUP BY Carbon_automation_test5_hive.AMSize, " +
+        "Carbon_automation_test5_hive.ActiveCountry, Carbon_automation_test5_hive.Activecity " +
+        "ORDER BY " +
+        "Carbon_automation_test5_hive.AMSize ASC, Carbon_automation_test5_hive.ActiveCountry ASC," +
+        " " +
+        "Carbon_automation_test5_hive.Activecity ASC"
       )
     )
   }
@@ -407,72 +394,48 @@ class AllDataTypesTestCase5 extends QueryTest with BeforeAndAfterAll {
   //TC_523
   test(
     "SELECT Carbon_automation_test5.AMSize AS AMSize, Carbon_automation_test5.ActiveCountry AS " +
-      "ActiveCountry, Carbon_automation_test5.Activecity AS Activecity, SUM" +
-      "(Carbon_automation_test5.gamePointId) AS Sum_gamePointId FROM ( SELECT AMSize, " +
-      "ActiveCountry, Activecity,gamePointId FROM (select * from Carbon_automation_test5) SUB_QRY" +
-      " ) Carbon_automation_test5 INNER JOIN ( SELECT ActiveCountry, Activecity, AMSize FROM " +
-      "(select * from Carbon_automation_test5) SUB_QRY ) Carbon_automation_vmall_test1 ON " +
-      "Carbon_automation_test5.AMSize = Carbon_automation_vmall_test1.AMSize WHERE NOT" +
-      "(Carbon_automation_test5.AMSize IN (\"5RAM size\",\"8RAM size\")) GROUP BY " +
-      "Carbon_automation_test5.AMSize, Carbon_automation_test5.ActiveCountry, " +
-      "Carbon_automation_test5.Activecity ORDER BY Carbon_automation_test5.AMSize ASC, " +
-      "Carbon_automation_test5.ActiveCountry ASC, Carbon_automation_test5.Activecity ASC"
+    "ActiveCountry, Carbon_automation_test5.Activecity AS Activecity, SUM" +
+    "(Carbon_automation_test5.gamePointId) AS Sum_gamePointId FROM ( SELECT AMSize, " +
+    "ActiveCountry, Activecity,gamePointId FROM (select * from Carbon_automation_test5) SUB_QRY" +
+    " ) Carbon_automation_test5 INNER JOIN ( SELECT ActiveCountry, Activecity, AMSize FROM " +
+    "(select * from Carbon_automation_test5) SUB_QRY ) Carbon_automation_vmall_test1 ON " +
+    "Carbon_automation_test5.AMSize = Carbon_automation_vmall_test1.AMSize WHERE NOT" +
+    "(Carbon_automation_test5.AMSize IN (\"5RAM size\",\"8RAM size\")) GROUP BY " +
+    "Carbon_automation_test5.AMSize, Carbon_automation_test5.ActiveCountry, " +
+    "Carbon_automation_test5.Activecity ORDER BY Carbon_automation_test5.AMSize ASC, " +
+    "Carbon_automation_test5.ActiveCountry ASC, Carbon_automation_test5.Activecity ASC"
   )({
     checkAnswer(
       sql(
         "SELECT Carbon_automation_test5.AMSize AS AMSize, Carbon_automation_test5.ActiveCountry " +
-          "AS ActiveCountry, Carbon_automation_test5.Activecity AS Activecity, SUM" +
-          "(Carbon_automation_test5.gamePointId) AS Sum_gamePointId FROM ( SELECT AMSize, " +
-          "ActiveCountry, Activecity,gamePointId FROM (select * from Carbon_automation_test5) " +
-          "SUB_QRY ) Carbon_automation_test5 INNER JOIN ( SELECT ActiveCountry, Activecity, " +
-          "AMSize FROM (select * from Carbon_automation_test5) SUB_QRY ) " +
-          "Carbon_automation_vmall_test1 ON Carbon_automation_test5.AMSize = " +
-          "Carbon_automation_vmall_test1.AMSize WHERE NOT(Carbon_automation_test5.AMSize IN " +
-          "(\"5RAM size\",\"8RAM size\")) GROUP BY Carbon_automation_test5.AMSize, " +
-          "Carbon_automation_test5.ActiveCountry, Carbon_automation_test5.Activecity ORDER BY " +
-          "Carbon_automation_test5.AMSize ASC, Carbon_automation_test5.ActiveCountry ASC, " +
-          "Carbon_automation_test5.Activecity ASC"
+        "AS ActiveCountry, Carbon_automation_test5.Activecity AS Activecity, SUM" +
+        "(Carbon_automation_test5.gamePointId) AS Sum_gamePointId FROM ( SELECT AMSize, " +
+        "ActiveCountry, Activecity,gamePointId FROM (select * from Carbon_automation_test5) " +
+        "SUB_QRY ) Carbon_automation_test5 INNER JOIN ( SELECT ActiveCountry, Activecity, " +
+        "AMSize FROM (select * from Carbon_automation_test5) SUB_QRY ) " +
+        "Carbon_automation_vmall_test1 ON Carbon_automation_test5.AMSize = " +
+        "Carbon_automation_vmall_test1.AMSize WHERE NOT(Carbon_automation_test5.AMSize IN " +
+        "(\"5RAM size\",\"8RAM size\")) GROUP BY Carbon_automation_test5.AMSize, " +
+        "Carbon_automation_test5.ActiveCountry, Carbon_automation_test5.Activecity ORDER BY " +
+        "Carbon_automation_test5.AMSize ASC, Carbon_automation_test5.ActiveCountry ASC, " +
+        "Carbon_automation_test5.Activecity ASC"
       ),
-      Seq(Row("0RAM size", "Chinese", "changsha", 84293.0),
-        Row("0RAM size", "Chinese", "guangzhou", 869.0),
-        Row("0RAM size", "Chinese", "shenzhen", 31339.0),
-        Row("0RAM size", "Chinese", "wuhan", 66902.0),
-        Row("0RAM size", "Chinese", "zhuzhou", 14751.0),
-        Row("1RAM size", "Chinese", "guangzhou", 11997.0),
-        Row("1RAM size", "Chinese", "shenzhen", 2304.0),
-        Row("1RAM size", "Chinese", "xiangtan", 67590.0),
-        Row("1RAM size", "Chinese", "yichang", 35478.0),
-        Row("1RAM size", "Chinese", "zhuzhou", 24705.0),
-        Row("2RAM size", "Chinese", "changsha", 3946.0),
-        Row("2RAM size", "Chinese", "xiangtan", 2700.0),
-        Row("3RAM size", "Chinese", "changsha", 40082.0),
-        Row("3RAM size", "Chinese", "guangzhou", 27986.0),
-        Row("3RAM size", "Chinese", "shenzhen", 92960.0),
-        Row("3RAM size", "Chinese", "wuhan", 36890.0),
-        Row("3RAM size", "Chinese", "xiangtan", 53536.0),
-        Row("3RAM size", "Chinese", "yichang", 20874.0),
-        Row("3RAM size", "Chinese", "zhuzhou", 79786.0),
-        Row("4RAM size", "Chinese", "changsha", 200860.0),
-        Row("4RAM size", "Chinese", "guangzhou", 38016.0),
-        Row("4RAM size", "Chinese", "shenzhen", 49610.0),
-        Row("4RAM size", "Chinese", "wuhan", 117581.96999999999),
-        Row("4RAM size", "Chinese", "xiangtan", 254320.0),
-        Row("4RAM size", "Chinese", "yichang", 29436.0),
-        Row("6RAM size", "Chinese", "changsha", 23697.0),
-        Row("6RAM size", "Chinese", "guangzhou", 15912.0),
-        Row("6RAM size", "Chinese", "shenzhen", 19278.0),
-        Row("6RAM size", "Chinese", "wuhan", 29313.0),
-        Row("6RAM size", "Chinese", "xiangtan", 7794.0),
-        Row("6RAM size", "Chinese", "zhuzhou", 26568.0),
-        Row("7RAM size", "Chinese", "changsha", 1057.0),
-        Row("7RAM size", "Chinese", "wuhan", 27853.0),
-        Row("7RAM size", "Chinese", "yichang", 14217.0),
-        Row("7RAM size", "Chinese", "zhuzhou", 15673.0),
-        Row("9RAM size", "Chinese", "changsha", 32390.0),
-        Row("9RAM size", "Chinese", "shenzhen", 30650.0),
-        Row("9RAM size", "Chinese", "wuhan", 15670.0),
-        Row("9RAM size", "Chinese", "xiangtan", 58210.0),
-        Row("9RAM size", "Chinese", "yichang", 5710.0)
+      sql(
+        "SELECT Carbon_automation_test5_hive.AMSize AS AMSize, Carbon_automation_test5_hive" +
+        ".ActiveCountry " +
+        "AS ActiveCountry, Carbon_automation_test5_hive.Activecity AS Activecity, SUM" +
+        "(Carbon_automation_test5_hive.gamePointId) AS Sum_gamePointId FROM ( SELECT AMSize, " +
+        "ActiveCountry, Activecity,gamePointId FROM (select * from Carbon_automation_test5_hive) " +
+        "SUB_QRY ) Carbon_automation_test5_hive INNER JOIN ( SELECT ActiveCountry, Activecity, " +
+        "AMSize FROM (select * from Carbon_automation_test5_hive) SUB_QRY ) " +
+        "Carbon_automation_vmall_test1 ON Carbon_automation_test5_hive.AMSize = " +
+        "Carbon_automation_vmall_test1.AMSize WHERE NOT(Carbon_automation_test5_hive.AMSize IN " +
+        "(\"5RAM size\",\"8RAM size\")) GROUP BY Carbon_automation_test5_hive.AMSize, " +
+        "Carbon_automation_test5_hive.ActiveCountry, Carbon_automation_test5_hive.Activecity " +
+        "ORDER BY " +
+        "Carbon_automation_test5_hive.AMSize ASC, Carbon_automation_test5_hive.ActiveCountry ASC," +
+        " " +
+        "Carbon_automation_test5_hive.Activecity ASC"
       )
     )
   }
@@ -481,80 +444,48 @@ class AllDataTypesTestCase5 extends QueryTest with BeforeAndAfterAll {
   //TC_524
   test(
     "SELECT Carbon_automation_test5.AMSize AS AMSize, Carbon_automation_test5.ActiveCountry AS " +
-      "ActiveCountry, Carbon_automation_test5.Activecity AS Activecity, SUM" +
-      "(Carbon_automation_test5.gamePointId) AS Sum_gamePointId FROM ( SELECT AMSize, " +
-      "ActiveCountry, Activecity,gamePointId FROM (select * from Carbon_automation_test5) SUB_QRY" +
-      " ) Carbon_automation_test5 INNER JOIN ( SELECT ActiveCountry, Activecity, AMSize FROM " +
-      "(select * from Carbon_automation_test5) SUB_QRY ) Carbon_automation_vmall_test1 ON " +
-      "Carbon_automation_test5.AMSize = Carbon_automation_vmall_test1.AMSize WHERE " +
-      "Carbon_automation_test5.AMSize IS NOT NULL GROUP BY Carbon_automation_test5.AMSize, " +
-      "Carbon_automation_test5.ActiveCountry, Carbon_automation_test5.Activecity ORDER BY " +
-      "Carbon_automation_test5.AMSize ASC, Carbon_automation_test5.ActiveCountry ASC, " +
-      "Carbon_automation_test5.Activecity ASC"
+    "ActiveCountry, Carbon_automation_test5.Activecity AS Activecity, SUM" +
+    "(Carbon_automation_test5.gamePointId) AS Sum_gamePointId FROM ( SELECT AMSize, " +
+    "ActiveCountry, Activecity,gamePointId FROM (select * from Carbon_automation_test5) SUB_QRY" +
+    " ) Carbon_automation_test5 INNER JOIN ( SELECT ActiveCountry, Activecity, AMSize FROM " +
+    "(select * from Carbon_automation_test5) SUB_QRY ) Carbon_automation_vmall_test1 ON " +
+    "Carbon_automation_test5.AMSize = Carbon_automation_vmall_test1.AMSize WHERE " +
+    "Carbon_automation_test5.AMSize IS NOT NULL GROUP BY Carbon_automation_test5.AMSize, " +
+    "Carbon_automation_test5.ActiveCountry, Carbon_automation_test5.Activecity ORDER BY " +
+    "Carbon_automation_test5.AMSize ASC, Carbon_automation_test5.ActiveCountry ASC, " +
+    "Carbon_automation_test5.Activecity ASC"
   )({
     checkAnswer(
       sql(
         "SELECT Carbon_automation_test5.AMSize AS AMSize, Carbon_automation_test5.ActiveCountry " +
-          "AS ActiveCountry, Carbon_automation_test5.Activecity AS Activecity, SUM" +
-          "(Carbon_automation_test5.gamePointId) AS Sum_gamePointId FROM ( SELECT AMSize, " +
-          "ActiveCountry, Activecity,gamePointId FROM (select * from Carbon_automation_test5) " +
-          "SUB_QRY ) Carbon_automation_test5 INNER JOIN ( SELECT ActiveCountry, Activecity, " +
-          "AMSize FROM (select * from Carbon_automation_test5) SUB_QRY ) " +
-          "Carbon_automation_vmall_test1 ON Carbon_automation_test5.AMSize = " +
-          "Carbon_automation_vmall_test1.AMSize WHERE Carbon_automation_test5.AMSize IS NOT NULL " +
-          "GROUP BY Carbon_automation_test5.AMSize, Carbon_automation_test5.ActiveCountry, " +
-          "Carbon_automation_test5.Activecity ORDER BY Carbon_automation_test5.AMSize ASC, " +
-          "Carbon_automation_test5.ActiveCountry ASC, Carbon_automation_test5.Activecity ASC"
+        "AS ActiveCountry, Carbon_automation_test5.Activecity AS Activecity, SUM" +
+        "(Carbon_automation_test5.gamePointId) AS Sum_gamePointId FROM ( SELECT AMSize, " +
+        "ActiveCountry, Activecity,gamePointId FROM (select * from Carbon_automation_test5) " +
+        "SUB_QRY ) Carbon_automation_test5 INNER JOIN ( SELECT ActiveCountry, Activecity, " +
+        "AMSize FROM (select * from Carbon_automation_test5) SUB_QRY ) " +
+        "Carbon_automation_vmall_test1 ON Carbon_automation_test5.AMSize = " +
+        "Carbon_automation_vmall_test1.AMSize WHERE Carbon_automation_test5.AMSize IS NOT NULL " +
+        "GROUP BY Carbon_automation_test5.AMSize, Carbon_automation_test5.ActiveCountry, " +
+        "Carbon_automation_test5.Activecity ORDER BY Carbon_automation_test5.AMSize ASC, " +
+        "Carbon_automation_test5.ActiveCountry ASC, Carbon_automation_test5.Activecity ASC"
       ),
-      Seq(Row("0RAM size", "Chinese", "changsha", 84293.0),
-        Row("0RAM size", "Chinese", "guangzhou", 869.0),
-        Row("0RAM size", "Chinese", "shenzhen", 31339.0),
-        Row("0RAM size", "Chinese", "wuhan", 66902.0),
-        Row("0RAM size", "Chinese", "zhuzhou", 14751.0),
-        Row("1RAM size", "Chinese", "guangzhou", 11997.0),
-        Row("1RAM size", "Chinese", "shenzhen", 2304.0),
-        Row("1RAM size", "Chinese", "xiangtan", 67590.0),
-        Row("1RAM size", "Chinese", "yichang", 35478.0),
-        Row("1RAM size", "Chinese", "zhuzhou", 24705.0),
-        Row("2RAM size", "Chinese", "changsha", 3946.0),
-        Row("2RAM size", "Chinese", "xiangtan", 2700.0),
-        Row("3RAM size", "Chinese", "changsha", 40082.0),
-        Row("3RAM size", "Chinese", "guangzhou", 27986.0),
-        Row("3RAM size", "Chinese", "shenzhen", 92960.0),
-        Row("3RAM size", "Chinese", "wuhan", 36890.0),
-        Row("3RAM size", "Chinese", "xiangtan", 53536.0),
-        Row("3RAM size", "Chinese", "yichang", 20874.0),
-        Row("3RAM size", "Chinese", "zhuzhou", 79786.0),
-        Row("4RAM size", "Chinese", "changsha", 200860.0),
-        Row("4RAM size", "Chinese", "guangzhou", 38016.0),
-        Row("4RAM size", "Chinese", "shenzhen", 49610.0),
-        Row("4RAM size", "Chinese", "wuhan", 117581.96999999999),
-        Row("4RAM size", "Chinese", "xiangtan", 254320.0),
-        Row("4RAM size", "Chinese", "yichang", 29436.0),
-        Row("5RAM size", "Chinese", "changsha", 13845.0),
-        Row("5RAM size", "Chinese", "guangzhou", 23560.0),
-        Row("5RAM size", "Chinese", "wuhan", 12390.0),
-        Row("6RAM size", "Chinese", "changsha", 23697.0),
-        Row("6RAM size", "Chinese", "guangzhou", 15912.0),
-        Row("6RAM size", "Chinese", "shenzhen", 19278.0),
-        Row("6RAM size", "Chinese", "wuhan", 29313.0),
-        Row("6RAM size", "Chinese", "xiangtan", 7794.0),
-        Row("6RAM size", "Chinese", "zhuzhou", 26568.0),
-        Row("7RAM size", "Chinese", "changsha", 1057.0),
-        Row("7RAM size", "Chinese", "wuhan", 27853.0),
-        Row("7RAM size", "Chinese", "yichang", 14217.0),
-        Row("7RAM size", "Chinese", "zhuzhou", 15673.0),
-        Row("8RAM size", "Chinese", "guangzhou", 27385.619999999995),
-        Row("8RAM size", "Chinese", "shenzhen", 3550.0),
-        Row("8RAM size", "Chinese", "wuhan", 29700.0),
-        Row("8RAM size", "Chinese", "xiangtan", 31020.0),
-        Row("8RAM size", "Chinese", "yichang", 51660.0),
-        Row("8RAM size", "Chinese", "zhuzhou", 26840.0),
-        Row("9RAM size", "Chinese", "changsha", 32390.0),
-        Row("9RAM size", "Chinese", "shenzhen", 30650.0),
-        Row("9RAM size", "Chinese", "wuhan", 15670.0),
-        Row("9RAM size", "Chinese", "xiangtan", 58210.0),
-        Row("9RAM size", "Chinese", "yichang", 5710.0)
+      sql(
+        "SELECT Carbon_automation_test5_hive.AMSize AS AMSize, Carbon_automation_test5_hive" +
+        ".ActiveCountry " +
+        "AS ActiveCountry, Carbon_automation_test5_hive.Activecity AS Activecity, SUM" +
+        "(Carbon_automation_test5_hive.gamePointId) AS Sum_gamePointId FROM ( SELECT AMSize, " +
+        "ActiveCountry, Activecity,gamePointId FROM (select * from Carbon_automation_test5_hive) " +
+        "SUB_QRY ) Carbon_automation_test5_hive INNER JOIN ( SELECT ActiveCountry, Activecity, " +
+        "AMSize FROM (select * from Carbon_automation_test5_hive) SUB_QRY ) " +
+        "Carbon_automation_vmall_test1 ON Carbon_automation_test5_hive.AMSize = " +
+        "Carbon_automation_vmall_test1.AMSize WHERE Carbon_automation_test5_hive.AMSize IS NOT " +
+        "NULL " +
+        "GROUP BY Carbon_automation_test5_hive.AMSize, Carbon_automation_test5_hive" +
+        ".ActiveCountry, " +
+        "Carbon_automation_test5_hive.Activecity ORDER BY Carbon_automation_test5_hive.AMSize " +
+        "ASC, " +
+        "Carbon_automation_test5_hive.ActiveCountry ASC, Carbon_automation_test5_hive.Activecity " +
+        "ASC"
       )
     )
   }
@@ -563,81 +494,51 @@ class AllDataTypesTestCase5 extends QueryTest with BeforeAndAfterAll {
   //TC_528
   test(
     "SELECT Carbon_automation_test5.AMSize AS AMSize, Carbon_automation_test5.ActiveCountry AS " +
-      "ActiveCountry, Carbon_automation_test5.Activecity AS Activecity, SUM" +
-      "(Carbon_automation_test5.gamePointId) AS Sum_gamePointId, SUM(Carbon_automation_test5" +
-      ".deviceInformationId) AS Sum_deviceInformationId FROM ( SELECT AMSize, gamePointId," +
-      "ActiveCountry,deviceInformationId, Activecity FROM (select * from Carbon_automation_test5)" +
-      " SUB_QRY ) Carbon_automation_test5 INNER JOIN ( SELECT ActiveCountry, Activecity, AMSize " +
-      "FROM (select * from Carbon_automation_test5) SUB_QRY ) Carbon_automation_vmall_test1 ON " +
-      "Carbon_automation_test5.AMSize = Carbon_automation_vmall_test1.AMSize GROUP BY " +
-      "Carbon_automation_test5.AMSize, Carbon_automation_test5.ActiveCountry, " +
-      "Carbon_automation_test5.Activecity ORDER BY Carbon_automation_test5.AMSize ASC, " +
-      "Carbon_automation_test5.ActiveCountry ASC, Carbon_automation_test5.Activecity ASC"
+    "ActiveCountry, Carbon_automation_test5.Activecity AS Activecity, SUM" +
+    "(Carbon_automation_test5.gamePointId) AS Sum_gamePointId, SUM(Carbon_automation_test5" +
+    ".deviceInformationId) AS Sum_deviceInformationId FROM ( SELECT AMSize, gamePointId," +
+    "ActiveCountry,deviceInformationId, Activecity FROM (select * from Carbon_automation_test5)" +
+    " SUB_QRY ) Carbon_automation_test5 INNER JOIN ( SELECT ActiveCountry, Activecity, AMSize " +
+    "FROM (select * from Carbon_automation_test5) SUB_QRY ) Carbon_automation_vmall_test1 ON " +
+    "Carbon_automation_test5.AMSize = Carbon_automation_vmall_test1.AMSize GROUP BY " +
+    "Carbon_automation_test5.AMSize, Carbon_automation_test5.ActiveCountry, " +
+    "Carbon_automation_test5.Activecity ORDER BY Carbon_automation_test5.AMSize ASC, " +
+    "Carbon_automation_test5.ActiveCountry ASC, Carbon_automation_test5.Activecity ASC"
   )({
     checkAnswer(
       sql(
         "SELECT Carbon_automation_test5.AMSize AS AMSize, Carbon_automation_test5.ActiveCountry " +
-          "AS ActiveCountry, Carbon_automation_test5.Activecity AS Activecity, SUM" +
-          "(Carbon_automation_test5.gamePointId) AS Sum_gamePointId, SUM(Carbon_automation_test5" +
-          ".deviceInformationId) AS Sum_deviceInformationId FROM ( SELECT AMSize, gamePointId," +
-          "ActiveCountry,deviceInformationId, Activecity FROM (select * from " +
-          "Carbon_automation_test5) SUB_QRY ) Carbon_automation_test5 INNER JOIN ( SELECT " +
-          "ActiveCountry, Activecity, AMSize FROM (select * from Carbon_automation_test5) SUB_QRY" +
-          " ) Carbon_automation_vmall_test1 ON Carbon_automation_test5.AMSize = " +
-          "Carbon_automation_vmall_test1.AMSize GROUP BY Carbon_automation_test5.AMSize, " +
-          "Carbon_automation_test5.ActiveCountry, Carbon_automation_test5.Activecity ORDER BY " +
-          "Carbon_automation_test5.AMSize ASC, Carbon_automation_test5.ActiveCountry ASC, " +
-          "Carbon_automation_test5.Activecity ASC"
+        "AS ActiveCountry, Carbon_automation_test5.Activecity AS Activecity, SUM" +
+        "(Carbon_automation_test5.gamePointId) AS Sum_gamePointId, SUM(Carbon_automation_test5" +
+        ".deviceInformationId) AS Sum_deviceInformationId FROM ( SELECT AMSize, gamePointId," +
+        "ActiveCountry,deviceInformationId, Activecity FROM (select * from " +
+        "Carbon_automation_test5) SUB_QRY ) Carbon_automation_test5 INNER JOIN ( SELECT " +
+        "ActiveCountry, Activecity, AMSize FROM (select * from Carbon_automation_test5) SUB_QRY" +
+        " ) Carbon_automation_vmall_test1 ON Carbon_automation_test5.AMSize = " +
+        "Carbon_automation_vmall_test1.AMSize GROUP BY Carbon_automation_test5.AMSize, " +
+        "Carbon_automation_test5.ActiveCountry, Carbon_automation_test5.Activecity ORDER BY " +
+        "Carbon_automation_test5.AMSize ASC, Carbon_automation_test5.ActiveCountry ASC, " +
+        "Carbon_automation_test5.Activecity ASC"
       ),
-      Seq(Row("0RAM size", "Chinese", "changsha", 84293.0, 4401364),
-        Row("0RAM size", "Chinese", "guangzhou", 869.0, 1100110),
-        Row("0RAM size", "Chinese", "shenzhen", 31339.0, 1100308),
-        Row("0RAM size", "Chinese", "wuhan", 66902.0, 4401639),
-        Row("0RAM size", "Chinese", "zhuzhou", 14751.0, 1100022),
-        Row("1RAM size", "Chinese", "guangzhou", 11997.0, 900270),
-        Row("1RAM size", "Chinese", "shenzhen", 2304.0, 900180),
-        Row("1RAM size", "Chinese", "xiangtan", 67590.0, 2790900),
-        Row("1RAM size", "Chinese", "yichang", 35478.0, 1800954),
-        Row("1RAM size", "Chinese", "zhuzhou", 24705.0, 900378),
-        Row("2RAM size", "Chinese", "changsha", 3946.0, 200142),
-        Row("2RAM size", "Chinese", "xiangtan", 2700.0, 20014),
-        Row("3RAM size", "Chinese", "changsha", 40082.0, 1400210),
-        Row("3RAM size", "Chinese", "guangzhou", 27986.0, 1400308),
-        Row("3RAM size", "Chinese", "shenzhen", 92960.0, 5603668),
-        Row("3RAM size", "Chinese", "wuhan", 36890.0, 1400812),
-        Row("3RAM size", "Chinese", "xiangtan", 53536.0, 4201974),
-        Row("3RAM size", "Chinese", "yichang", 20874.0, 1400966),
-        Row("3RAM size", "Chinese", "zhuzhou", 79786.0, 2941190),
-        Row("4RAM size", "Chinese", "changsha", 200860.0, 11225038),
-        Row("4RAM size", "Chinese", "guangzhou", 38016.0, 2201210),
-        Row("4RAM size", "Chinese", "shenzhen", 49610.0, 2421408),
-        Row("4RAM size", "Chinese", "wuhan", 117581.96999999999, 4402222),
-        Row("4RAM size", "Chinese", "xiangtan", 254320.0, 33004774),
-        Row("4RAM size", "Chinese", "yichang", 29436.0, 8803168),
-        Row("5RAM size", "Chinese", "changsha", 13845.0, 505385),
-        Row("5RAM size", "Chinese", "guangzhou", 23560.0, 1000460),
-        Row("5RAM size", "Chinese", "wuhan", 12390.0, 50030),
-        Row("6RAM size", "Chinese", "changsha", 23697.0, 1800909),
-        Row("6RAM size", "Chinese", "guangzhou", 15912.0, 900234),
-        Row("6RAM size", "Chinese", "shenzhen", 19278.0, 900315),
-        Row("6RAM size", "Chinese", "wuhan", 29313.0, 1801125),
-        Row("6RAM size", "Chinese", "xiangtan", 7794.0, 990117),
-        Row("6RAM size", "Chinese", "zhuzhou", 26568.0, 900558),
-        Row("7RAM size", "Chinese", "changsha", 1057.0, 700098),
-        Row("7RAM size", "Chinese", "wuhan", 27853.0, 2100455),
-        Row("7RAM size", "Chinese", "yichang", 14217.0, 700931),
-        Row("7RAM size", "Chinese", "zhuzhou", 15673.0, 700021),
-        Row("8RAM size", "Chinese", "guangzhou", 27385.619999999995, 10),
-        Row("8RAM size", "Chinese", "shenzhen", 3550.0, 1000130),
-        Row("8RAM size", "Chinese", "wuhan", 29700.0, 1000040),
-        Row("8RAM size", "Chinese", "xiangtan", 31020.0, 2000540),
-        Row("8RAM size", "Chinese", "yichang", 51660.0, 1100250),
-        Row("8RAM size", "Chinese", "zhuzhou", 26840.0, 3001960),
-        Row("9RAM size", "Chinese", "changsha", 32390.0, 2000730),
-        Row("9RAM size", "Chinese", "shenzhen", 30650.0, 2000980),
-        Row("9RAM size", "Chinese", "wuhan", 15670.0, 1000700),
-        Row("9RAM size", "Chinese", "xiangtan", 58210.0, 3102370),
-        Row("9RAM size", "Chinese", "yichang", 5710.0, 1000430)
+      sql(
+        "SELECT Carbon_automation_test5_hive.AMSize AS AMSize, Carbon_automation_test5_hive" +
+        ".ActiveCountry " +
+        "AS ActiveCountry, Carbon_automation_test5_hive.Activecity AS Activecity, SUM" +
+        "(Carbon_automation_test5_hive.gamePointId) AS Sum_gamePointId, SUM" +
+        "(Carbon_automation_test5_hive" +
+        ".deviceInformationId) AS Sum_deviceInformationId FROM ( SELECT AMSize, gamePointId," +
+        "ActiveCountry,deviceInformationId, Activecity FROM (select * from " +
+        "Carbon_automation_test5_hive) SUB_QRY ) Carbon_automation_test5_hive INNER JOIN ( SELECT" +
+        " " +
+        "ActiveCountry, Activecity, AMSize FROM (select * from Carbon_automation_test5_hive) " +
+        "SUB_QRY" +
+        " ) Carbon_automation_vmall_test1 ON Carbon_automation_test5_hive.AMSize = " +
+        "Carbon_automation_vmall_test1.AMSize GROUP BY Carbon_automation_test5_hive.AMSize, " +
+        "Carbon_automation_test5_hive.ActiveCountry, Carbon_automation_test5_hive.Activecity " +
+        "ORDER BY " +
+        "Carbon_automation_test5_hive.AMSize ASC, Carbon_automation_test5_hive.ActiveCountry ASC," +
+        " " +
+        "Carbon_automation_test5_hive.Activecity ASC"
       )
     )
   }
@@ -646,81 +547,51 @@ class AllDataTypesTestCase5 extends QueryTest with BeforeAndAfterAll {
   //TC_529
   test(
     "SELECT Carbon_automation_test5.AMSize AS AMSize, Carbon_automation_test5.ActiveCountry AS " +
-      "ActiveCountry, Carbon_automation_test5.Activecity AS Activecity, SUM" +
-      "(Carbon_automation_test5.gamePointId) AS Sum_gamePointId, AVG(Carbon_automation_test5" +
-      ".deviceInformationId) AS avg_deviceInformationId FROM ( SELECT AMSize,deviceInformationId," +
-      " gamePointId, ActiveCountry, Activecity FROM (select * from Carbon_automation_test5) " +
-      "SUB_QRY ) Carbon_automation_test5 INNER JOIN ( SELECT ActiveCountry, Activecity, AMSize " +
-      "FROM (select * from Carbon_automation_test5) SUB_QRY ) Carbon_automation_vmall_test1 ON " +
-      "Carbon_automation_test5.AMSize = Carbon_automation_vmall_test1.AMSize GROUP BY " +
-      "Carbon_automation_test5.AMSize, Carbon_automation_test5.ActiveCountry, " +
-      "Carbon_automation_test5.Activecity ORDER BY Carbon_automation_test5.AMSize ASC, " +
-      "Carbon_automation_test5.ActiveCountry ASC, Carbon_automation_test5.Activecity ASC"
+    "ActiveCountry, Carbon_automation_test5.Activecity AS Activecity, SUM" +
+    "(Carbon_automation_test5.gamePointId) AS Sum_gamePointId, AVG(Carbon_automation_test5" +
+    ".deviceInformationId) AS avg_deviceInformationId FROM ( SELECT AMSize,deviceInformationId," +
+    " gamePointId, ActiveCountry, Activecity FROM (select * from Carbon_automation_test5) " +
+    "SUB_QRY ) Carbon_automation_test5 INNER JOIN ( SELECT ActiveCountry, Activecity, AMSize " +
+    "FROM (select * from Carbon_automation_test5) SUB_QRY ) Carbon_automation_vmall_test1 ON " +
+    "Carbon_automation_test5.AMSize = Carbon_automation_vmall_test1.AMSize GROUP BY " +
+    "Carbon_automation_test5.AMSize, Carbon_automation_test5.ActiveCountry, " +
+    "Carbon_automation_test5.Activecity ORDER BY Carbon_automation_test5.AMSize ASC, " +
+    "Carbon_automation_test5.ActiveCountry ASC, Carbon_automation_test5.Activecity ASC"
   )({
     checkAnswer(
       sql(
         "SELECT Carbon_automation_test5.AMSize AS AMSize, Carbon_automation_test5.ActiveCountry " +
-          "AS ActiveCountry, Carbon_automation_test5.Activecity AS Activecity, SUM" +
-          "(Carbon_automation_test5.gamePointId) AS Sum_gamePointId, AVG(Carbon_automation_test5" +
-          ".deviceInformationId) AS avg_deviceInformationId FROM ( SELECT AMSize," +
-          "deviceInformationId, gamePointId, ActiveCountry, Activecity FROM (select * from " +
-          "Carbon_automation_test5) SUB_QRY ) Carbon_automation_test5 INNER JOIN ( SELECT " +
-          "ActiveCountry, Activecity, AMSize FROM (select * from Carbon_automation_test5) SUB_QRY" +
-          " ) Carbon_automation_vmall_test1 ON Carbon_automation_test5.AMSize = " +
-          "Carbon_automation_vmall_test1.AMSize GROUP BY Carbon_automation_test5.AMSize, " +
-          "Carbon_automation_test5.ActiveCountry, Carbon_automation_test5.Activecity ORDER BY " +
-          "Carbon_automation_test5.AMSize ASC, Carbon_automation_test5.ActiveCountry ASC, " +
-          "Carbon_automation_test5.Activecity ASC"
+        "AS ActiveCountry, Carbon_automation_test5.Activecity AS Activecity, SUM" +
+        "(Carbon_automation_test5.gamePointId) AS Sum_gamePointId, AVG(Carbon_automation_test5" +
+        ".deviceInformationId) AS avg_deviceInformationId FROM ( SELECT AMSize," +
+        "deviceInformationId, gamePointId, ActiveCountry, Activecity FROM (select * from " +
+        "Carbon_automation_test5) SUB_QRY ) Carbon_automation_test5 INNER JOIN ( SELECT " +
+        "ActiveCountry, Activecity, AMSize FROM (select * from Carbon_automation_test5) SUB_QRY" +
+        " ) Carbon_automation_vmall_test1 ON Carbon_automation_test5.AMSize = " +
+        "Carbon_automation_vmall_test1.AMSize GROUP BY Carbon_automation_test5.AMSize, " +
+        "Carbon_automation_test5.ActiveCountry, Carbon_automation_test5.Activecity ORDER BY " +
+        "Carbon_automation_test5.AMSize ASC, Carbon_automation_test5.ActiveCountry ASC, " +
+        "Carbon_automation_test5.Activecity ASC"
       ),
-      Seq(Row("0RAM size", "Chinese", "changsha", 84293.0, 100031.0),
-        Row("0RAM size", "Chinese", "guangzhou", 869.0, 100010.0),
-        Row("0RAM size", "Chinese", "shenzhen", 31339.0, 100028.0),
-        Row("0RAM size", "Chinese", "wuhan", 66902.0, 100037.25),
-        Row("0RAM size", "Chinese", "zhuzhou", 14751.0, 100002.0),
-        Row("1RAM size", "Chinese", "guangzhou", 11997.0, 100030.0),
-        Row("1RAM size", "Chinese", "shenzhen", 2304.0, 100020.0),
-        Row("1RAM size", "Chinese", "xiangtan", 67590.0, 77525.0),
-        Row("1RAM size", "Chinese", "yichang", 35478.0, 100053.0),
-        Row("1RAM size", "Chinese", "zhuzhou", 24705.0, 100042.0),
-        Row("2RAM size", "Chinese", "changsha", 3946.0, 100071.0),
-        Row("2RAM size", "Chinese", "xiangtan", 2700.0, 10007.0),
-        Row("3RAM size", "Chinese", "changsha", 40082.0, 100015.0),
-        Row("3RAM size", "Chinese", "guangzhou", 27986.0, 100022.0),
-        Row("3RAM size", "Chinese", "shenzhen", 92960.0, 100065.5),
-        Row("3RAM size", "Chinese", "wuhan", 36890.0, 100058.0),
-        Row("3RAM size", "Chinese", "xiangtan", 53536.0, 100047.0),
-        Row("3RAM size", "Chinese", "yichang", 20874.0, 100069.0),
-        Row("3RAM size", "Chinese", "zhuzhou", 79786.0, 70028.33333333333),
-        Row("4RAM size", "Chinese", "changsha", 200860.0, 85038.16666666667),
-        Row("4RAM size", "Chinese", "guangzhou", 38016.0, 100055.0),
-        Row("4RAM size", "Chinese", "shenzhen", 49610.0, 55032.0),
-        Row("4RAM size", "Chinese", "wuhan", 117581.96999999999, 66700.33333333333),
-        Row("4RAM size", "Chinese", "xiangtan", 254320.0, 250036.16666666666),
-        Row("4RAM size", "Chinese", "yichang", 29436.0, 100036.0),
-        Row("5RAM size", "Chinese", "changsha", 13845.0, 50538.5),
-        Row("5RAM size", "Chinese", "guangzhou", 23560.0, 100046.0),
-        Row("5RAM size", "Chinese", "wuhan", 12390.0, 10006.0),
-        Row("6RAM size", "Chinese", "changsha", 23697.0, 100050.5),
-        Row("6RAM size", "Chinese", "guangzhou", 15912.0, 100026.0),
-        Row("6RAM size", "Chinese", "shenzhen", 19278.0, 100035.0),
-        Row("6RAM size", "Chinese", "wuhan", 29313.0, 100062.5),
-        Row("6RAM size", "Chinese", "xiangtan", 7794.0, 55006.5),
-        Row("6RAM size", "Chinese", "zhuzhou", 26568.0, 100062.0),
-        Row("7RAM size", "Chinese", "changsha", 1057.0, 100014.0),
-        Row("7RAM size", "Chinese", "wuhan", 27853.0, 100021.66666666667),
-        Row("7RAM size", "Chinese", "yichang", 14217.0, 50066.5),
-        Row("7RAM size", "Chinese", "zhuzhou", 15673.0, 100003.0),
-        Row("8RAM size", "Chinese", "guangzhou", 27385.619999999995, 1.0),
-        Row("8RAM size", "Chinese", "shenzhen", 3550.0, 100013.0),
-        Row("8RAM size", "Chinese", "wuhan", 29700.0, 100004.0),
-        Row("8RAM size", "Chinese", "xiangtan", 31020.0, 100027.0),
-        Row("8RAM size", "Chinese", "yichang", 51660.0, 55012.5),
-        Row("8RAM size", "Chinese", "zhuzhou", 26840.0, 100065.33333333333),
-        Row("9RAM size", "Chinese", "changsha", 32390.0, 100036.5),
-        Row("9RAM size", "Chinese", "shenzhen", 30650.0, 100049.0),
-        Row("9RAM size", "Chinese", "wuhan", 15670.0, 100070.0),
-        Row("9RAM size", "Chinese", "xiangtan", 58210.0, 77559.25),
-        Row("9RAM size", "Chinese", "yichang", 5710.0, 100043.0)
+      sql(
+        "SELECT Carbon_automation_test5_hive.AMSize AS AMSize, Carbon_automation_test5_hive" +
+        ".ActiveCountry " +
+        "AS ActiveCountry, Carbon_automation_test5_hive.Activecity AS Activecity, SUM" +
+        "(Carbon_automation_test5_hive.gamePointId) AS Sum_gamePointId, AVG" +
+        "(Carbon_automation_test5_hive" +
+        ".deviceInformationId) AS avg_deviceInformationId FROM ( SELECT AMSize," +
+        "deviceInformationId, gamePointId, ActiveCountry, Activecity FROM (select * from " +
+        "Carbon_automation_test5_hive) SUB_QRY ) Carbon_automation_test5_hive INNER JOIN ( SELECT" +
+        " " +
+        "ActiveCountry, Activecity, AMSize FROM (select * from Carbon_automation_test5_hive) " +
+        "SUB_QRY" +
+        " ) Carbon_automation_vmall_test1 ON Carbon_automation_test5_hive.AMSize = " +
+        "Carbon_automation_vmall_test1.AMSize GROUP BY Carbon_automation_test5_hive.AMSize, " +
+        "Carbon_automation_test5_hive.ActiveCountry, Carbon_automation_test5_hive.Activecity " +
+        "ORDER BY " +
+        "Carbon_automation_test5_hive.AMSize ASC, Carbon_automation_test5_hive.ActiveCountry ASC," +
+        " " +
+        "Carbon_automation_test5_hive.Activecity ASC"
       )
     )
   }
@@ -729,81 +600,50 @@ class AllDataTypesTestCase5 extends QueryTest with BeforeAndAfterAll {
   //TC_530
   test(
     "SELECT Carbon_automation_test5.AMSize AS AMSize, Carbon_automation_test5.ActiveCountry AS " +
-      "ActiveCountry, Carbon_automation_test5.Activecity AS Activecity, SUM" +
-      "(Carbon_automation_test5.gamePointId) AS Sum_gamePointId, COUNT(Carbon_automation_test5" +
-      ".deviceInformationId) AS Count_deviceInformationId FROM ( SELECT AMSize, ActiveCountry," +
-      "gamePointId, Activecity,deviceInformationId FROM (select * from Carbon_automation_test5) " +
-      "SUB_QRY ) Carbon_automation_test5 INNER JOIN ( SELECT ActiveCountry, Activecity, AMSize " +
-      "FROM (select * from Carbon_automation_test5) SUB_QRY ) Carbon_automation_vmall_test1 ON " +
-      "Carbon_automation_test5.AMSize = Carbon_automation_vmall_test1.AMSize GROUP BY " +
-      "Carbon_automation_test5.AMSize, Carbon_automation_test5.ActiveCountry, " +
-      "Carbon_automation_test5.Activecity ORDER BY Carbon_automation_test5.AMSize ASC, " +
-      "Carbon_automation_test5.ActiveCountry ASC, Carbon_automation_test5.Activecity ASC"
+    "ActiveCountry, Carbon_automation_test5.Activecity AS Activecity, SUM" +
+    "(Carbon_automation_test5.gamePointId) AS Sum_gamePointId, COUNT(Carbon_automation_test5" +
+    ".deviceInformationId) AS Count_deviceInformationId FROM ( SELECT AMSize, ActiveCountry," +
+    "gamePointId, Activecity,deviceInformationId FROM (select * from Carbon_automation_test5) " +
+    "SUB_QRY ) Carbon_automation_test5 INNER JOIN ( SELECT ActiveCountry, Activecity, AMSize " +
+    "FROM (select * from Carbon_automation_test5) SUB_QRY ) Carbon_automation_vmall_test1 ON " +
+    "Carbon_automation_test5.AMSize = Carbon_automation_vmall_test1.AMSize GROUP BY " +
+    "Carbon_automation_test5.AMSize, Carbon_automation_test5.ActiveCountry, " +
+    "Carbon_automation_test5.Activecity ORDER BY Carbon_automation_test5.AMSize ASC, " +
+    "Carbon_automation_test5.ActiveCountry ASC, Carbon_automation_test5.Activecity ASC"
   )({
     checkAnswer(
       sql(
         "SELECT Carbon_automation_test5.AMSize AS AMSize, Carbon_automation_test5.ActiveCountry " +
-          "AS ActiveCountry, Carbon_automation_test5.Activecity AS Activecity, SUM" +
-          "(Carbon_automation_test5.gamePointId) AS Sum_gamePointId, COUNT" +
-          "(Carbon_automation_test5.deviceInformationId) AS Count_deviceInformationId FROM ( " +
-          "SELECT AMSize, ActiveCountry,gamePointId, Activecity,deviceInformationId FROM (select " +
-          "* from Carbon_automation_test5) SUB_QRY ) Carbon_automation_test5 INNER JOIN ( SELECT " +
-          "ActiveCountry, Activecity, AMSize FROM (select * from Carbon_automation_test5) SUB_QRY" +
-          " ) Carbon_automation_vmall_test1 ON Carbon_automation_test5.AMSize = " +
-          "Carbon_automation_vmall_test1.AMSize GROUP BY Carbon_automation_test5.AMSize, " +
-          "Carbon_automation_test5.ActiveCountry, Carbon_automation_test5.Activecity ORDER BY " +
-          "Carbon_automation_test5.AMSize ASC, Carbon_automation_test5.ActiveCountry ASC, " +
-          "Carbon_automation_test5.Activecity ASC"
+        "AS ActiveCountry, Carbon_automation_test5.Activecity AS Activecity, SUM" +
+        "(Carbon_automation_test5.gamePointId) AS Sum_gamePointId, COUNT" +
+        "(Carbon_automation_test5.deviceInformationId) AS Count_deviceInformationId FROM ( " +
+        "SELECT AMSize, ActiveCountry,gamePointId, Activecity,deviceInformationId FROM (select " +
+        "* from Carbon_automation_test5) SUB_QRY ) Carbon_automation_test5 INNER JOIN ( SELECT " +
+        "ActiveCountry, Activecity, AMSize FROM (select * from Carbon_automation_test5) SUB_QRY" +
+        " ) Carbon_automation_vmall_test1 ON Carbon_automation_test5.AMSize = " +
+        "Carbon_automation_vmall_test1.AMSize GROUP BY Carbon_automation_test5.AMSize, " +
+        "Carbon_automation_test5.ActiveCountry, Carbon_automation_test5.Activecity ORDER BY " +
+        "Carbon_automation_test5.AMSize ASC, Carbon_automation_test5.ActiveCountry ASC, " +
+        "Carbon_automation_test5.Activecity ASC"
       ),
-      Seq(Row("0RAM size", "Chinese", "changsha", 84293.0, 44),
-        Row("0RAM size", "Chinese", "guangzhou", 869.0, 11),
-        Row("0RAM size", "Chinese", "shenzhen", 31339.0, 11),
-        Row("0RAM size", "Chinese", "wuhan", 66902.0, 44),
-        Row("0RAM size", "Chinese", "zhuzhou", 14751.0, 11),
-        Row("1RAM size", "Chinese", "guangzhou", 11997.0, 9),
-        Row("1RAM size", "Chinese", "shenzhen", 2304.0, 9),
-        Row("1RAM size", "Chinese", "xiangtan", 67590.0, 36),
-        Row("1RAM size", "Chinese", "yichang", 35478.0, 18),
-        Row("1RAM size", "Chinese", "zhuzhou", 24705.0, 9),
-        Row("2RAM size", "Chinese", "changsha", 3946.0, 2),
-        Row("2RAM size", "Chinese", "xiangtan", 2700.0, 2),
-        Row("3RAM size", "Chinese", "changsha", 40082.0, 14),
-        Row("3RAM size", "Chinese", "guangzhou", 27986.0, 14),
-        Row("3RAM size", "Chinese", "shenzhen", 92960.0, 56),
-        Row("3RAM size", "Chinese", "wuhan", 36890.0, 14),
-        Row("3RAM size", "Chinese", "xiangtan", 53536.0, 42),
-        Row("3RAM size", "Chinese", "yichang", 20874.0, 14),
-        Row("3RAM size", "Chinese", "zhuzhou", 79786.0, 42),
-        Row("4RAM size", "Chinese", "changsha", 200860.0, 132),
-        Row("4RAM size", "Chinese", "guangzhou", 38016.0, 22),
-        Row("4RAM size", "Chinese", "shenzhen", 49610.0, 44),
-        Row("4RAM size", "Chinese", "wuhan", 117581.96999999999, 66),
-        Row("4RAM size", "Chinese", "xiangtan", 254320.0, 132),
-        Row("4RAM size", "Chinese", "yichang", 29436.0, 88),
-        Row("5RAM size", "Chinese", "changsha", 13845.0, 10),
-        Row("5RAM size", "Chinese", "guangzhou", 23560.0, 10),
-        Row("5RAM size", "Chinese", "wuhan", 12390.0, 5),
-        Row("6RAM size", "Chinese", "changsha", 23697.0, 18),
-        Row("6RAM size", "Chinese", "guangzhou", 15912.0, 9),
-        Row("6RAM size", "Chinese", "shenzhen", 19278.0, 9),
-        Row("6RAM size", "Chinese", "wuhan", 29313.0, 18),
-        Row("6RAM size", "Chinese", "xiangtan", 7794.0, 18),
-        Row("6RAM size", "Chinese", "zhuzhou", 26568.0, 9),
-        Row("7RAM size", "Chinese", "changsha", 1057.0, 7),
-        Row("7RAM size", "Chinese", "wuhan", 27853.0, 21),
-        Row("7RAM size", "Chinese", "yichang", 14217.0, 14),
-        Row("7RAM size", "Chinese", "zhuzhou", 15673.0, 7),
-        Row("8RAM size", "Chinese", "guangzhou", 27385.619999999995, 10),
-        Row("8RAM size", "Chinese", "shenzhen", 3550.0, 10),
-        Row("8RAM size", "Chinese", "wuhan", 29700.0, 10),
-        Row("8RAM size", "Chinese", "xiangtan", 31020.0, 20),
-        Row("8RAM size", "Chinese", "yichang", 51660.0, 20),
-        Row("8RAM size", "Chinese", "zhuzhou", 26840.0, 30),
-        Row("9RAM size", "Chinese", "changsha", 32390.0, 20),
-        Row("9RAM size", "Chinese", "shenzhen", 30650.0, 20),
-        Row("9RAM size", "Chinese", "wuhan", 15670.0, 10),
-        Row("9RAM size", "Chinese", "xiangtan", 58210.0, 40),
-        Row("9RAM size", "Chinese", "yichang", 5710.0, 10)
+      sql(
+        "SELECT Carbon_automation_test5_hive.AMSize AS AMSize, Carbon_automation_test5_hive" +
+        ".ActiveCountry " +
+        "AS ActiveCountry, Carbon_automation_test5_hive.Activecity AS Activecity, SUM" +
+        "(Carbon_automation_test5_hive.gamePointId) AS Sum_gamePointId, COUNT" +
+        "(Carbon_automation_test5_hive.deviceInformationId) AS Count_deviceInformationId FROM ( " +
+        "SELECT AMSize, ActiveCountry,gamePointId, Activecity,deviceInformationId FROM (select " +
+        "* from Carbon_automation_test5_hive) SUB_QRY ) Carbon_automation_test5_hive INNER JOIN (" +
+        " SELECT " +
+        "ActiveCountry, Activecity, AMSize FROM (select * from Carbon_automation_test5_hive) " +
+        "SUB_QRY" +
+        " ) Carbon_automation_vmall_test1 ON Carbon_automation_test5_hive.AMSize = " +
+        "Carbon_automation_vmall_test1.AMSize GROUP BY Carbon_automation_test5_hive.AMSize, " +
+        "Carbon_automation_test5_hive.ActiveCountry, Carbon_automation_test5_hive.Activecity " +
+        "ORDER BY " +
+        "Carbon_automation_test5_hive.AMSize ASC, Carbon_automation_test5_hive.ActiveCountry ASC," +
+        " " +
+        "Carbon_automation_test5_hive.Activecity ASC"
       )
     )
   }
@@ -812,82 +652,51 @@ class AllDataTypesTestCase5 extends QueryTest with BeforeAndAfterAll {
   //TC_531
   test(
     "SELECT Carbon_automation_test5.AMSize AS AMSize, Carbon_automation_test5.ActiveCountry AS " +
-      "ActiveCountry, Carbon_automation_test5.Activecity AS Activecity, SUM" +
-      "(Carbon_automation_test5.gamePointId) AS Sum_gamePointId, COUNT(DISTINCT " +
-      "Carbon_automation_test5.deviceInformationId) AS LONG_COL_0 FROM ( SELECT AMSize, " +
-      "ActiveCountry,gamePointId,deviceInformationId, Activecity FROM (select * from " +
-      "Carbon_automation_test5) SUB_QRY ) Carbon_automation_test5 INNER JOIN ( SELECT " +
-      "ActiveCountry, Activecity, AMSize FROM (select * from Carbon_automation_test5) SUB_QRY ) " +
-      "Carbon_automation_vmall_test1 ON Carbon_automation_test5.AMSize = " +
-      "Carbon_automation_vmall_test1.AMSize GROUP BY Carbon_automation_test5.AMSize, " +
-      "Carbon_automation_test5.ActiveCountry, Carbon_automation_test5.Activecity ORDER BY " +
-      "Carbon_automation_test5.AMSize ASC, Carbon_automation_test5.ActiveCountry ASC, " +
-      "Carbon_automation_test5.Activecity ASC"
+    "ActiveCountry, Carbon_automation_test5.Activecity AS Activecity, SUM" +
+    "(Carbon_automation_test5.gamePointId) AS Sum_gamePointId, COUNT(DISTINCT " +
+    "Carbon_automation_test5.deviceInformationId) AS LONG_COL_0 FROM ( SELECT AMSize, " +
+    "ActiveCountry,gamePointId,deviceInformationId, Activecity FROM (select * from " +
+    "Carbon_automation_test5) SUB_QRY ) Carbon_automation_test5 INNER JOIN ( SELECT " +
+    "ActiveCountry, Activecity, AMSize FROM (select * from Carbon_automation_test5) SUB_QRY ) " +
+    "Carbon_automation_vmall_test1 ON Carbon_automation_test5.AMSize = " +
+    "Carbon_automation_vmall_test1.AMSize GROUP BY Carbon_automation_test5.AMSize, " +
+    "Carbon_automation_test5.ActiveCountry, Carbon_automation_test5.Activecity ORDER BY " +
+    "Carbon_automation_test5.AMSize ASC, Carbon_automation_test5.ActiveCountry ASC, " +
+    "Carbon_automation_test5.Activecity ASC"
   )({
     checkAnswer(
       sql(
         "SELECT Carbon_automation_test5.AMSize AS AMSize, Carbon_automation_test5.ActiveCountry " +
-          "AS ActiveCountry, Carbon_automation_test5.Activecity AS Activecity, SUM" +
-          "(Carbon_automation_test5.gamePointId) AS Sum_gamePointId, COUNT(DISTINCT " +
-          "Carbon_automation_test5.deviceInformationId) AS LONG_COL_0 FROM ( SELECT AMSize, " +
-          "ActiveCountry,gamePointId,deviceInformationId, Activecity FROM (select * from " +
-          "Carbon_automation_test5) SUB_QRY ) Carbon_automation_test5 INNER JOIN ( SELECT " +
-          "ActiveCountry, Activecity, AMSize FROM (select * from Carbon_automation_test5) SUB_QRY" +
-          " ) Carbon_automation_vmall_test1 ON Carbon_automation_test5.AMSize = " +
-          "Carbon_automation_vmall_test1.AMSize GROUP BY Carbon_automation_test5.AMSize, " +
-          "Carbon_automation_test5.ActiveCountry, Carbon_automation_test5.Activecity ORDER BY " +
-          "Carbon_automation_test5.AMSize ASC, Carbon_automation_test5.ActiveCountry ASC, " +
-          "Carbon_automation_test5.Activecity ASC"
+        "AS ActiveCountry, Carbon_automation_test5.Activecity AS Activecity, SUM" +
+        "(Carbon_automation_test5.gamePointId) AS Sum_gamePointId, COUNT(DISTINCT " +
+        "Carbon_automation_test5.deviceInformationId) AS LONG_COL_0 FROM ( SELECT AMSize, " +
+        "ActiveCountry,gamePointId,deviceInformationId, Activecity FROM (select * from " +
+        "Carbon_automation_test5) SUB_QRY ) Carbon_automation_test5 INNER JOIN ( SELECT " +
+        "ActiveCountry, Activecity, AMSize FROM (select * from Carbon_automation_test5) SUB_QRY" +
+        " ) Carbon_automation_vmall_test1 ON Carbon_automation_test5.AMSize = " +
+        "Carbon_automation_vmall_test1.AMSize GROUP BY Carbon_automation_test5.AMSize, " +
+        "Carbon_automation_test5.ActiveCountry, Carbon_automation_test5.Activecity ORDER BY " +
+        "Carbon_automation_test5.AMSize ASC, Carbon_automation_test5.ActiveCountry ASC, " +
+        "Carbon_automation_test5.Activecity ASC"
       ),
-      Seq(Row("0RAM size", "Chinese", "changsha", 84293.0, 4),
-        Row("0RAM size", "Chinese", "guangzhou", 869.0, 1),
-        Row("0RAM size", "Chinese", "shenzhen", 31339.0, 1),
-        Row("0RAM size", "Chinese", "wuhan", 66902.0, 4),
-        Row("0RAM size", "Chinese", "zhuzhou", 14751.0, 1),
-        Row("1RAM size", "Chinese", "guangzhou", 11997.0, 1),
-        Row("1RAM size", "Chinese", "shenzhen", 2304.0, 1),
-        Row("1RAM size", "Chinese", "xiangtan", 67590.0, 4),
-        Row("1RAM size", "Chinese", "yichang", 35478.0, 2),
-        Row("1RAM size", "Chinese", "zhuzhou", 24705.0, 1),
-        Row("2RAM size", "Chinese", "changsha", 3946.0, 1),
-        Row("2RAM size", "Chinese", "xiangtan", 2700.0, 1),
-        Row("3RAM size", "Chinese", "changsha", 40082.0, 1),
-        Row("3RAM size", "Chinese", "guangzhou", 27986.0, 1),
-        Row("3RAM size", "Chinese", "shenzhen", 92960.0, 4),
-        Row("3RAM size", "Chinese", "wuhan", 36890.0, 1),
-        Row("3RAM size", "Chinese", "xiangtan", 53536.0, 3),
-        Row("3RAM size", "Chinese", "yichang", 20874.0, 1),
-        Row("3RAM size", "Chinese", "zhuzhou", 79786.0, 3),
-        Row("4RAM size", "Chinese", "changsha", 200860.0, 6),
-        Row("4RAM size", "Chinese", "guangzhou", 38016.0, 1),
-        Row("4RAM size", "Chinese", "shenzhen", 49610.0, 2),
-        Row("4RAM size", "Chinese", "wuhan", 117581.96999999999, 3),
-        Row("4RAM size", "Chinese", "xiangtan", 254320.0, 6),
-        Row("4RAM size", "Chinese", "yichang", 29436.0, 4),
-        Row("5RAM size", "Chinese", "changsha", 13845.0, 2),
-        Row("5RAM size", "Chinese", "guangzhou", 23560.0, 2),
-        Row("5RAM size", "Chinese", "wuhan", 12390.0, 1),
-        Row("6RAM size", "Chinese", "changsha", 23697.0, 2),
-        Row("6RAM size", "Chinese", "guangzhou", 15912.0, 1),
-        Row("6RAM size", "Chinese", "shenzhen", 19278.0, 1),
-        Row("6RAM size", "Chinese", "wuhan", 29313.0, 2),
-        Row("6RAM size", "Chinese", "xiangtan", 7794.0, 2),
-        Row("6RAM size", "Chinese", "zhuzhou", 26568.0, 1),
-        Row("7RAM size", "Chinese", "changsha", 1057.0, 1),
-        Row("7RAM size", "Chinese", "wuhan", 27853.0, 3),
-        Row("7RAM size", "Chinese", "yichang", 14217.0, 2),
-        Row("7RAM size", "Chinese", "zhuzhou", 15673.0, 1),
-        Row("8RAM size", "Chinese", "guangzhou", 27385.619999999995, 1),
-        Row("8RAM size", "Chinese", "shenzhen", 3550.0, 1),
-        Row("8RAM size", "Chinese", "wuhan", 29700.0, 1),
-        Row("8RAM size", "Chinese", "xiangtan", 31020.0, 2),
-        Row("8RAM size", "Chinese", "yichang", 51660.0, 2),
-        Row("8RAM size", "Chinese", "zhuzhou", 26840.0, 3),
-        Row("9RAM size", "Chinese", "changsha", 32390.0, 2),
-        Row("9RAM size", "Chinese", "shenzhen", 30650.0, 2),
-        Row("9RAM size", "Chinese", "wuhan", 15670.0, 1),
-        Row("9RAM size", "Chinese", "xiangtan", 58210.0, 4),
-        Row("9RAM size", "Chinese", "yichang", 5710.0, 1)
+      sql(
+        "SELECT Carbon_automation_test5_hive.AMSize AS AMSize, Carbon_automation_test5_hive" +
+        ".ActiveCountry " +
+        "AS ActiveCountry, Carbon_automation_test5_hive.Activecity AS Activecity, SUM" +
+        "(Carbon_automation_test5_hive.gamePointId) AS Sum_gamePointId, COUNT(DISTINCT " +
+        "Carbon_automation_test5_hive.deviceInformationId) AS LONG_COL_0 FROM ( SELECT AMSize, " +
+        "ActiveCountry,gamePointId,deviceInformationId, Activecity FROM (select * from " +
+        "Carbon_automation_test5_hive) SUB_QRY ) Carbon_automation_test5_hive INNER JOIN ( SELECT" +
+        " " +
+        "ActiveCountry, Activecity, AMSize FROM (select * from Carbon_automation_test5_hive) " +
+        "SUB_QRY" +
+        " ) Carbon_automation_vmall_test1 ON Carbon_automation_test5_hive.AMSize = " +
+        "Carbon_automation_vmall_test1.AMSize GROUP BY Carbon_automation_test5_hive.AMSize, " +
+        "Carbon_automation_test5_hive.ActiveCountry, Carbon_automation_test5_hive.Activecity " +
+        "ORDER BY " +
+        "Carbon_automation_test5_hive.AMSize ASC, Carbon_automation_test5_hive.ActiveCountry ASC," +
+        " " +
+        "Carbon_automation_test5_hive.Activecity ASC"
       )
     )
   }
@@ -896,82 +705,52 @@ class AllDataTypesTestCase5 extends QueryTest with BeforeAndAfterAll {
   //TC_533
   test(
     "SELECT Carbon_automation_test5.AMSize AS AMSize, Carbon_automation_test5.ActiveCountry AS " +
-      "ActiveCountry, Carbon_automation_test5.Activecity AS Activecity, SUM" +
-      "(Carbon_automation_test5.gamePointId) AS Sum_gamePointId, MAX(Carbon_automation_test5" +
-      ".deviceInformationId) AS Max_deviceInformationId FROM ( SELECT AMSize,gamePointId," +
-      "deviceInformationId, ActiveCountry, Activecity FROM (select * from " +
-      "Carbon_automation_test5) SUB_QRY ) Carbon_automation_test5 INNER JOIN ( SELECT " +
-      "ActiveCountry, Activecity, AMSize FROM (select * from Carbon_automation_test5) SUB_QRY ) " +
-      "Carbon_automation_vmall_test1 ON Carbon_automation_test5.AMSize = " +
-      "Carbon_automation_vmall_test1.AMSize GROUP BY Carbon_automation_test5.AMSize, " +
-      "Carbon_automation_test5.ActiveCountry, Carbon_automation_test5.Activecity ORDER BY " +
-      "Carbon_automation_test5.AMSize ASC, Carbon_automation_test5.ActiveCountry ASC, " +
-      "Carbon_automation_test5.Activecity ASC"
+    "ActiveCountry, Carbon_automation_test5.Activecity AS Activecity, SUM" +
+    "(Carbon_automation_test5.gamePointId) AS Sum_gamePointId, MAX(Carbon_automation_test5" +
+    ".deviceInformationId) AS Max_deviceInformationId FROM ( SELECT AMSize,gamePointId," +
+    "deviceInformationId, ActiveCountry, Activecity FROM (select * from " +
+    "Carbon_automation_test5) SUB_QRY ) Carbon_automation_test5 INNER JOIN ( SELECT " +
+    "ActiveCountry, Activecity, AMSize FROM (select * from Carbon_automation_test5) SUB_QRY ) " +
+    "Carbon_automation_vmall_test1 ON Carbon_automation_test5.AMSize = " +
+    "Carbon_automation_vmall_test1.AMSize GROUP BY Carbon_automation_test5.AMSize, " +
+    "Carbon_automation_test5.ActiveCountry, Carbon_automation_test5.Activecity ORDER BY " +
+    "Carbon_automation_test5.AMSize ASC, Carbon_automation_test5.ActiveCountry ASC, " +
+    "Carbon_automation_test5.Activecity ASC"
   )({
     checkAnswer(
       sql(
         "SELECT Carbon_automation_test5.AMSize AS AMSize, Carbon_automation_test5.ActiveCountry " +
-          "AS ActiveCountry, Carbon_automation_test5.Activecity AS Activecity, SUM" +
-          "(Carbon_automation_test5.gamePointId) AS Sum_gamePointId, MAX(Carbon_automation_test5" +
-          ".deviceInformationId) AS Max_deviceInformationId FROM ( SELECT AMSize,gamePointId," +
-          "deviceInformationId, ActiveCountry, Activecity FROM (select * from " +
-          "Carbon_automation_test5) SUB_QRY ) Carbon_automation_test5 INNER JOIN ( SELECT " +
-          "ActiveCountry, Activecity, AMSize FROM (select * from Carbon_automation_test5) SUB_QRY" +
-          " ) Carbon_automation_vmall_test1 ON Carbon_automation_test5.AMSize = " +
-          "Carbon_automation_vmall_test1.AMSize GROUP BY Carbon_automation_test5.AMSize, " +
-          "Carbon_automation_test5.ActiveCountry, Carbon_automation_test5.Activecity ORDER BY " +
-          "Carbon_automation_test5.AMSize ASC, Carbon_automation_test5.ActiveCountry ASC, " +
-          "Carbon_automation_test5.Activecity ASC"
+        "AS ActiveCountry, Carbon_automation_test5.Activecity AS Activecity, SUM" +
+        "(Carbon_automation_test5.gamePointId) AS Sum_gamePointId, MAX(Carbon_automation_test5" +
+        ".deviceInformationId) AS Max_deviceInformationId FROM ( SELECT AMSize,gamePointId," +
+        "deviceInformationId, ActiveCountry, Activecity FROM (select * from " +
+        "Carbon_automation_test5) SUB_QRY ) Carbon_automation_test5 INNER JOIN ( SELECT " +
+        "ActiveCountry, Activecity, AMSize FROM (select * from Carbon_automation_test5) SUB_QRY" +
+        " ) Carbon_automation_vmall_test1 ON Carbon_automation_test5.AMSize = " +
+        "Carbon_automation_vmall_test1.AMSize GROUP BY Carbon_automation_test5.AMSize, " +
+        "Carbon_automation_test5.ActiveCountry, Carbon_automation_test5.Activecity ORDER BY " +
+        "Carbon_automation_test5.AMSize ASC, Carbon_automation

<TRUNCATED>


Mime
View raw message