http://git-wip-us.apache.org/repos/asf/carbondata/blob/14624953/integration/spark-common-cluster-test/src/test/scala/org/apache/carbondata/cluster/sdv/generated/QueriesExcludeDictionaryTestCase.scala ---------------------------------------------------------------------- diff --git a/integration/spark-common-cluster-test/src/test/scala/org/apache/carbondata/cluster/sdv/generated/QueriesExcludeDictionaryTestCase.scala b/integration/spark-common-cluster-test/src/test/scala/org/apache/carbondata/cluster/sdv/generated/QueriesExcludeDictionaryTestCase.scala new file mode 100644 index 0000000..03c2f98 --- /dev/null +++ b/integration/spark-common-cluster-test/src/test/scala/org/apache/carbondata/cluster/sdv/generated/QueriesExcludeDictionaryTestCase.scala @@ -0,0 +1,5044 @@ + +/* + * 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.cluster.sdv.generated + +import org.apache.spark.sql.common.util._ +import org.scalatest.BeforeAndAfterAll + +/** + * Test Class for QueriesExcludeDictionaryTestCase to verify all scenerios + */ + +class QueriesExcludeDictionaryTestCase extends QueryTest with BeforeAndAfterAll { + + + //VMALL_DICTIONARY_EXCLUDE_CreateCube + test("VMALL_DICTIONARY_EXCLUDE_CreateCube", Include) { + sql(s"""drop table if exists VMALL_DICTIONARY_EXCLUDE""").collect + sql(s"""drop table if exists VMALL_DICTIONARY_EXCLUDE1_hive""").collect + + sql(s"""create table VMALL_DICTIONARY_EXCLUDE (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, 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_phonePA DPartitionedVersions string, Latest_YEAR int, Latest_MONTH int, Latest_DAY Decimal(30,10), 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, gamePointDescription string,gamePointId double,contractNumber BigInt) STORED BY 'org.apache.carbondata.format' TBLPROPERTIES('DICTIONARY_EXCLUDE'='imei')""").collect + + sql(s"""create table VMALL_DICTIONARY_EXCLUDE1_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 BigInt,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_phoneP ADPartitionedVersions string,Latest_YEAR int,Latest_MONTH int,Latest_DAY Decimal(30,10),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 double,gamePointDescription string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','""").collect + + + } + + + //VMALL_DICTIONARY_EXCLUDE_CreateCube_count + test("VMALL_DICTIONARY_EXCLUDE_CreateCube_count", Include) { + + sql(s"""select count(*) from VMALL_DICTIONARY_EXCLUDE""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_DataLoad + test("VMALL_DICTIONARY_EXCLUDE_DataLoad", Include) { + + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/100_olap_C20.csv' INTO table VMALL_DICTIONARY_EXCLUDE options ('DELIMITER'=',', 'QUOTECHAR'='"', 'BAD_RECORDS_ACTION'='FORCE','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,ActiveCountry,ActiveProvince,Activecity,ActiveDistrict,ActiveStreet,ActiveOperatorId,Active_releaseId,Active_EMUIVersion,Active_operaSysVersion,Active_BacVerNumber,Active_BacFlashVer,Active_webUIVersion,Active_webUITypeCarrVer,Active_webTypeDataVerNumber,Active_operatorsVersion,Active_phonePADPartitionedVersions,Latest_YEAR,Latest_MONTH,Latest_DAY,Latest_HOUR,Latest_areaId,Latest_country,Latest_province,Latest_city,Latest_district,Latest_street,Latest_rele aseId,Latest_EMUIVersion,Latest_operaSysVersion,Latest_BacVerNumber,Latest_BacFlashVer,Latest_webUIVersion,Latest_webUITypeCarrVer,Latest_webTypeDataVerNumber,Latest_operatorsVersion,Latest_phonePADPartitionedVersions,Latest_operatorId,gamePointId,gamePointDescription')""").collect + + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/100_olap_C20.csv' INTO table VMALL_DICTIONARY_EXCLUDE1_hive """).collect + + + } + + + //VMALL_DICTIONARY_EXCLUDE_CreateCube1 + test("VMALL_DICTIONARY_EXCLUDE_CreateCube1", Include) { + sql(s"""drop table if exists VMALL_DICTIONARY_EXCLUDE1""").collect + + sql(s"""create table VMALL_DICTIONARY_EXCLUDE1 (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, 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_phoneP ADPartitionedVersions string, Latest_YEAR int, Latest_MONTH int, Latest_DAY Decimal(30,10), 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, gamePointDescription string,gamePointId double,contractNumber BigInt) STORED BY 'org.apache.carbondata.format' TBLPROPERTIES('DICTIONARY_EXCLUDE'='imei')""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_DataLoad1 + test("VMALL_DICTIONARY_EXCLUDE_DataLoad1", Include) { + + sql(s"""LOAD DATA INPATH '$resourcesPath/Data/100_olap_C20.csv' INTO table VMALL_DICTIONARY_EXCLUDE1 options ('DELIMITER'=',', 'QUOTECHAR'='"', 'BAD_RECORDS_ACTION'='FORCE','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,ActiveCountry,ActiveProvince,Activecity,ActiveDistrict,ActiveStreet,ActiveOperatorId,Active_releaseId,Active_EMUIVersion,Active_operaSysVersion,Active_BacVerNumber,Active_BacFlashVer,Active_webUIVersion,Active_webUITypeCarrVer,Active_webTypeDataVerNumber,Active_operatorsVersion,Active_phonePADPartitionedVersions,Latest_YEAR,Latest_MONTH,Latest_DAY,Latest_HOUR,Latest_areaId,Latest_country,Latest_province,Latest_city,Latest_district,Latest_street,Latest_rel easeId,Latest_EMUIVersion,Latest_operaSysVersion,Latest_BacVerNumber,Latest_BacFlashVer,Latest_webUIVersion,Latest_webUITypeCarrVer,Latest_webTypeDataVerNumber,Latest_operatorsVersion,Latest_phonePADPartitionedVersions,Latest_operatorId,gamePointId,gamePointDescription')""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_001 + test("VMALL_DICTIONARY_EXCLUDE_001", Include) { + + checkAnswer(s"""Select count(imei) from VMALL_DICTIONARY_EXCLUDE""", + s"""Select count(imei) from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_001") + + } + + + //VMALL_DICTIONARY_EXCLUDE_002 + test("VMALL_DICTIONARY_EXCLUDE_002", Include) { + + checkAnswer(s"""select count(DISTINCT imei) as a from VMALL_DICTIONARY_EXCLUDE""", + s"""select count(DISTINCT imei) as a from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_002") + + } + + + //VMALL_DICTIONARY_EXCLUDE_003 + test("VMALL_DICTIONARY_EXCLUDE_003", Include) { + + checkAnswer(s"""select sum(Latest_month)+10 as a ,imei from VMALL_DICTIONARY_EXCLUDE group by imei order by imei""", + s"""select sum(Latest_month)+10 as a ,imei from VMALL_DICTIONARY_EXCLUDE1_hive group by imei order by imei""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_003") + + } + + + //VMALL_DICTIONARY_EXCLUDE_004 + test("VMALL_DICTIONARY_EXCLUDE_004", Include) { + + checkAnswer(s"""select max(imei),min(imei) from VMALL_DICTIONARY_EXCLUDE""", + s"""select max(imei),min(imei) from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_004") + + } + + + //VMALL_DICTIONARY_EXCLUDE_005 + test("VMALL_DICTIONARY_EXCLUDE_005", Include) { + + checkAnswer(s"""select min(imei), max(imei) Total from VMALL_DICTIONARY_EXCLUDE group by channelsId order by Total""", + s"""select min(imei), max(imei) Total from VMALL_DICTIONARY_EXCLUDE1_hive group by channelsId order by Total""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_005") + + } + + + //VMALL_DICTIONARY_EXCLUDE_006 + test("VMALL_DICTIONARY_EXCLUDE_006", Include) { + + sql(s"""select last(imei) a from VMALL_DICTIONARY_EXCLUDE group by imei order by imei limit 1""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_007 + test("VMALL_DICTIONARY_EXCLUDE_007", Include) { + + sql(s"""select FIRST(imei) a from VMALL_DICTIONARY_EXCLUDE group by imei order by imei limit 1""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_008 + test("VMALL_DICTIONARY_EXCLUDE_008", Include) { + + checkAnswer(s"""select imei,count(imei) a from VMALL_DICTIONARY_EXCLUDE group by imei order by imei""", + s"""select imei,count(imei) a from VMALL_DICTIONARY_EXCLUDE1_hive group by imei order by imei""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_008") + + } + + + //VMALL_DICTIONARY_EXCLUDE_009 + test("VMALL_DICTIONARY_EXCLUDE_009", Include) { + + checkAnswer(s"""select Lower(imei) a from VMALL_DICTIONARY_EXCLUDE order by imei""", + s"""select Lower(imei) a from VMALL_DICTIONARY_EXCLUDE1_hive order by imei""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_009") + + } + + + //VMALL_DICTIONARY_EXCLUDE_010 + test("VMALL_DICTIONARY_EXCLUDE_010", Include) { + + checkAnswer(s"""select distinct imei from VMALL_DICTIONARY_EXCLUDE order by imei""", + s"""select distinct imei from VMALL_DICTIONARY_EXCLUDE1_hive order by imei""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_010") + + } + + + //VMALL_DICTIONARY_EXCLUDE_011 + test("VMALL_DICTIONARY_EXCLUDE_011", Include) { + + checkAnswer(s"""select imei from VMALL_DICTIONARY_EXCLUDE order by imei limit 101 """, + s"""select imei from VMALL_DICTIONARY_EXCLUDE1_hive order by imei limit 101 """, "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_011") + + } + + + //VMALL_DICTIONARY_EXCLUDE_012 + test("VMALL_DICTIONARY_EXCLUDE_012", Include) { + + sql(s"""select imei as a from VMALL_DICTIONARY_EXCLUDE order by a asc limit 10""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_013 + test("VMALL_DICTIONARY_EXCLUDE_013", Include) { + + checkAnswer(s"""select imei from VMALL_DICTIONARY_EXCLUDE where (contractNumber == 9223372047700) and (imei=='1AA100004')""", + s"""select imei from VMALL_DICTIONARY_EXCLUDE1_hive where (contractNumber == 9223372047700) and (imei=='1AA100004')""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_013") + + } + + + //VMALL_DICTIONARY_EXCLUDE_014 + test("VMALL_DICTIONARY_EXCLUDE_014", Include) { + + checkAnswer(s"""select imei from VMALL_DICTIONARY_EXCLUDE where imei !='1AA100064' order by imei""", + s"""select imei from VMALL_DICTIONARY_EXCLUDE1_hive where imei !='1AA100064' order by imei""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_014") + + } + + + //VMALL_DICTIONARY_EXCLUDE_015 + test("VMALL_DICTIONARY_EXCLUDE_015", Include) { + + checkAnswer(s"""select imei from VMALL_DICTIONARY_EXCLUDE where (deviceInformationId=100 and deviceColor='1Device Color') OR (deviceInformationId=10 and deviceColor='0Device Color')""", + s"""select imei from VMALL_DICTIONARY_EXCLUDE1_hive where (deviceInformationId=100 and deviceColor='1Device Color') OR (deviceInformationId=10 and deviceColor='0Device Color')""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_015") + + } + + + //VMALL_DICTIONARY_EXCLUDE_016 + test("VMALL_DICTIONARY_EXCLUDE_016", Include) { + + checkAnswer(s"""select imei from VMALL_DICTIONARY_EXCLUDE where imei !='1AA100012' order by imei""", + s"""select imei from VMALL_DICTIONARY_EXCLUDE1_hive where imei !='1AA100012' order by imei""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_016") + + } + + + //VMALL_DICTIONARY_EXCLUDE_017 + test("VMALL_DICTIONARY_EXCLUDE_017", Include) { + + checkAnswer(s"""select imei from VMALL_DICTIONARY_EXCLUDE where imei >'1AA100012' order by imei""", + s"""select imei from VMALL_DICTIONARY_EXCLUDE1_hive where imei >'1AA100012' order by imei""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_017") + + } + + + //VMALL_DICTIONARY_EXCLUDE_018 + test("VMALL_DICTIONARY_EXCLUDE_018", Include) { + + checkAnswer(s"""select imei from VMALL_DICTIONARY_EXCLUDE where imei<>imei""", + s"""select imei from VMALL_DICTIONARY_EXCLUDE1_hive where imei<>imei""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_018") + + } + + + //VMALL_DICTIONARY_EXCLUDE_019 + test("VMALL_DICTIONARY_EXCLUDE_019", Include) { + + checkAnswer(s"""select imei from VMALL_DICTIONARY_EXCLUDE where imei != Latest_areaId order by imei""", + s"""select imei from VMALL_DICTIONARY_EXCLUDE1_hive where imei != Latest_areaId order by imei""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_019") + + } + + + //VMALL_DICTIONARY_EXCLUDE_020 + test("VMALL_DICTIONARY_EXCLUDE_020", Include) { + + checkAnswer(s"""select imei from VMALL_DICTIONARY_EXCLUDE where Latest_areaId<imei order by imei""", + s"""select imei from VMALL_DICTIONARY_EXCLUDE1_hive where Latest_areaId<imei order by imei""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_020") + + } + + + //VMALL_DICTIONARY_EXCLUDE_021 + test("VMALL_DICTIONARY_EXCLUDE_021", Include) { + + checkAnswer(s"""select imei from VMALL_DICTIONARY_EXCLUDE where Latest_DAY<=imei order by imei""", + s"""select imei from VMALL_DICTIONARY_EXCLUDE1_hive where Latest_DAY<=imei order by imei""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_021") + + } + + + //VMALL_DICTIONARY_EXCLUDE_022 + test("VMALL_DICTIONARY_EXCLUDE_022", Include) { + + checkAnswer(s"""select imei from VMALL_DICTIONARY_EXCLUDE where imei <'1AA10002' order by imei""", + s"""select imei from VMALL_DICTIONARY_EXCLUDE1_hive where imei <'1AA10002' order by imei""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_022") + + } + + + //VMALL_DICTIONARY_EXCLUDE_023 + test("VMALL_DICTIONARY_EXCLUDE_023", Include) { + + checkAnswer(s"""select Latest_day from VMALL_DICTIONARY_EXCLUDE where imei IS NULL""", + s"""select Latest_day from VMALL_DICTIONARY_EXCLUDE1_hive where imei IS NULL""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_023") + + } + + + //VMALL_DICTIONARY_EXCLUDE_024 + test("VMALL_DICTIONARY_EXCLUDE_024", Include) { + + checkAnswer(s"""select Latest_day from VMALL_DICTIONARY_EXCLUDE where imei IS NOT NULL order by Latest_day""", + s"""select Latest_day from VMALL_DICTIONARY_EXCLUDE1_hive where imei IS NOT NULL order by Latest_day""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_024") + + } + + + //VMALL_DICTIONARY_EXCLUDE_025 + test("VMALL_DICTIONARY_EXCLUDE_025", Include) { + + checkAnswer(s"""Select count(imei),min(imei) from VMALL_DICTIONARY_EXCLUDE """, + s"""Select count(imei),min(imei) from VMALL_DICTIONARY_EXCLUDE1_hive """, "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_025") + + } + + + //VMALL_DICTIONARY_EXCLUDE_026 + test("VMALL_DICTIONARY_EXCLUDE_026", Include) { + + checkAnswer(s"""select count(DISTINCT imei,latest_day) as a from VMALL_DICTIONARY_EXCLUDE""", + s"""select count(DISTINCT imei,latest_day) as a from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_026") + + } + + + //VMALL_DICTIONARY_EXCLUDE_027 + test("VMALL_DICTIONARY_EXCLUDE_027", Include) { + + checkAnswer(s"""select max(imei),min(imei),count(imei) from VMALL_DICTIONARY_EXCLUDE""", + s"""select max(imei),min(imei),count(imei) from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_027") + + } + + + //VMALL_DICTIONARY_EXCLUDE_028 + test("VMALL_DICTIONARY_EXCLUDE_028", Include) { + + checkAnswer(s"""select sum(imei),avg(imei),count(imei) a from VMALL_DICTIONARY_EXCLUDE""", + s"""select sum(imei),avg(imei),count(imei) a from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_028") + + } + + + //VMALL_DICTIONARY_EXCLUDE_029 + test("VMALL_DICTIONARY_EXCLUDE_029", Include) { + + sql(s"""select last(imei),Min(imei),max(imei) a from VMALL_DICTIONARY_EXCLUDE order by a""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_030 + test("VMALL_DICTIONARY_EXCLUDE_030", Include) { + + sql(s"""select FIRST(imei),Last(imei) a from VMALL_DICTIONARY_EXCLUDE group by imei order by imei limit 1""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_031 + test("VMALL_DICTIONARY_EXCLUDE_031", Include) { + + checkAnswer(s"""select imei,count(imei) a from VMALL_DICTIONARY_EXCLUDE group by imei order by imei""", + s"""select imei,count(imei) a from VMALL_DICTIONARY_EXCLUDE1_hive group by imei order by imei""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_031") + + } + + + //VMALL_DICTIONARY_EXCLUDE_032 + test("VMALL_DICTIONARY_EXCLUDE_032", Include) { + + checkAnswer(s"""select Lower(imei),upper(imei) a from VMALL_DICTIONARY_EXCLUDE order by imei""", + s"""select Lower(imei),upper(imei) a from VMALL_DICTIONARY_EXCLUDE1_hive order by imei""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_032") + + } + + + //VMALL_DICTIONARY_EXCLUDE_033 + test("VMALL_DICTIONARY_EXCLUDE_033", Include) { + + checkAnswer(s"""select imei as a from VMALL_DICTIONARY_EXCLUDE order by a asc limit 10""", + s"""select imei as a from VMALL_DICTIONARY_EXCLUDE1_hive order by a asc limit 10""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_033") + + } + + + //VMALL_DICTIONARY_EXCLUDE_034 + test("VMALL_DICTIONARY_EXCLUDE_034", Include) { + + checkAnswer(s"""select imei from VMALL_DICTIONARY_EXCLUDE where (contractNumber == 9223372047700) and (imei=='1AA100012')""", + s"""select imei from VMALL_DICTIONARY_EXCLUDE1_hive where (contractNumber == 9223372047700) and (imei=='1AA100012')""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_034") + + } + + + //VMALL_DICTIONARY_EXCLUDE_035 + test("VMALL_DICTIONARY_EXCLUDE_035", Include) { + + checkAnswer(s"""select imei from VMALL_DICTIONARY_EXCLUDE where imei !='8imei' order by imei""", + s"""select imei from VMALL_DICTIONARY_EXCLUDE1_hive where imei !='8imei' order by imei""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_035") + + } + + + //VMALL_DICTIONARY_EXCLUDE_036 + test("VMALL_DICTIONARY_EXCLUDE_036", Include) { + + checkAnswer(s"""select imei from VMALL_DICTIONARY_EXCLUDE where (deviceInformationId=100 and deviceColor='1Device Color') OR (deviceInformationId=10 and deviceColor='0Device Color')""", + s"""select imei from VMALL_DICTIONARY_EXCLUDE1_hive where (deviceInformationId=100 and deviceColor='1Device Color') OR (deviceInformationId=10 and deviceColor='0Device Color')""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_036") + + } + + + //VMALL_DICTIONARY_EXCLUDE_037 + test("VMALL_DICTIONARY_EXCLUDE_037", Include) { + + checkAnswer(s"""Select count(contractNumber) from VMALL_DICTIONARY_EXCLUDE""", + s"""Select count(contractNumber) from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_037") + + } + + + //VMALL_DICTIONARY_EXCLUDE_038 + test("VMALL_DICTIONARY_EXCLUDE_038", Include) { + + checkAnswer(s"""select count(DISTINCT contractNumber) as a from VMALL_DICTIONARY_EXCLUDE""", + s"""select count(DISTINCT contractNumber) as a from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_038") + + } + + + //VMALL_DICTIONARY_EXCLUDE_039 + test("VMALL_DICTIONARY_EXCLUDE_039", Include) { + + checkAnswer(s"""select sum(contractNumber)+10 as a ,contractNumber from VMALL_DICTIONARY_EXCLUDE group by contractNumber""", + s"""select sum(contractNumber)+10 as a ,contractNumber from VMALL_DICTIONARY_EXCLUDE1_hive group by contractNumber""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_039") + + } + + + //VMALL_DICTIONARY_EXCLUDE_040 + test("VMALL_DICTIONARY_EXCLUDE_040", Include) { + + checkAnswer(s"""select max(contractNumber),min(contractNumber) from VMALL_DICTIONARY_EXCLUDE""", + s"""select max(contractNumber),min(contractNumber) from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_040") + + } + + + //VMALL_DICTIONARY_EXCLUDE_041 + test("VMALL_DICTIONARY_EXCLUDE_041", Include) { + + checkAnswer(s"""select sum(contractNumber) a from VMALL_DICTIONARY_EXCLUDE""", + s"""select sum(contractNumber) a from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_041") + + } + + + //VMALL_DICTIONARY_EXCLUDE_042 + test("VMALL_DICTIONARY_EXCLUDE_042", Include) { + + checkAnswer(s"""select avg(contractNumber) a from VMALL_DICTIONARY_EXCLUDE""", + s"""select avg(contractNumber) a from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_042") + + } + + + //VMALL_DICTIONARY_EXCLUDE_043 + test("VMALL_DICTIONARY_EXCLUDE_043", Include) { + + checkAnswer(s"""select min(contractNumber) a from VMALL_DICTIONARY_EXCLUDE""", + s"""select min(contractNumber) a from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_043") + + } + + + //VMALL_DICTIONARY_EXCLUDE_044 + test("VMALL_DICTIONARY_EXCLUDE_044", Include) { + + sql(s"""select variance(contractNumber) as a from (select contractNumber from VMALL_DICTIONARY_EXCLUDE order by contractNumber) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_045 + ignore("VMALL_DICTIONARY_EXCLUDE_045", Include) { + + checkAnswer(s"""select var_pop(contractNumber) as a from (select * from VMALL_DICTIONARY_EXCLUDE order by contractNumber) t""", + s"""select var_pop(contractNumber) as a from (select * from VMALL_DICTIONARY_EXCLUDE1_hive order by contractNumber) t""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_045") + + } + + + //VMALL_DICTIONARY_EXCLUDE_046 + ignore("VMALL_DICTIONARY_EXCLUDE_046", Include) { + + checkAnswer(s"""select var_samp(contractNumber) as a from (select * from VMALL_DICTIONARY_EXCLUDE order by contractNumber) t""", + s"""select var_samp(contractNumber) as a from (select * from VMALL_DICTIONARY_EXCLUDE1_hive order by contractNumber) t""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_046") + + } + + + //VMALL_DICTIONARY_EXCLUDE_047 + test("VMALL_DICTIONARY_EXCLUDE_047", Include) { + + sql(s"""select stddev_pop(contractNumber) as a from (select contractNumber from VMALL_DICTIONARY_EXCLUDE order by contractNumber) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_048 + test("VMALL_DICTIONARY_EXCLUDE_048", Include) { + + sql(s"""select stddev_samp(contractNumber) as a from (select contractNumber from VMALL_DICTIONARY_EXCLUDE order by contractNumber) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_049 + test("VMALL_DICTIONARY_EXCLUDE_049", Include) { + + sql(s"""select covar_pop(contractNumber,contractNumber) as a from (select contractNumber from VMALL_DICTIONARY_EXCLUDE order by contractNumber) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_050 + test("VMALL_DICTIONARY_EXCLUDE_050", Include) { + + sql(s"""select covar_samp(contractNumber,contractNumber) as a from (select contractNumber from VMALL_DICTIONARY_EXCLUDE order by contractNumber) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_051 + test("VMALL_DICTIONARY_EXCLUDE_051", Include) { + + checkAnswer(s"""select corr(contractNumber,contractNumber) as a from VMALL_DICTIONARY_EXCLUDE""", + s"""select corr(contractNumber,contractNumber) as a from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_051") + + } + + + //VMALL_DICTIONARY_EXCLUDE_052 + test("VMALL_DICTIONARY_EXCLUDE_052", Include) { + + sql(s"""select percentile_approx(contractNumber,0.2) as a from (select contractNumber from VMALL_DICTIONARY_EXCLUDE order by contractNumber) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_053 + test("VMALL_DICTIONARY_EXCLUDE_053", Include) { + + sql(s"""select percentile_approx(contractNumber,0.2,5) as a from (select contractNumber from VMALL_DICTIONARY_EXCLUDE order by contractNumber) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_054 + test("VMALL_DICTIONARY_EXCLUDE_054", Include) { + + sql(s"""select percentile_approx(contractNumber,array(0.2,0.3,0.99)) as a from (select contractNumber from VMALL_DICTIONARY_EXCLUDE order by contractNumber) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_055 + test("VMALL_DICTIONARY_EXCLUDE_055", Include) { + + sql(s"""select percentile_approx(contractNumber,array(0.2,0.3,0.99),5) as a from (select contractNumber from VMALL_DICTIONARY_EXCLUDE order by contractNumber) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_056 + test("VMALL_DICTIONARY_EXCLUDE_056", Include) { + + sql(s"""select histogram_numeric(contractNumber,2) as a from (select contractNumber from VMALL_DICTIONARY_EXCLUDE order by contractNumber) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_057 + test("VMALL_DICTIONARY_EXCLUDE_057", Include) { + + checkAnswer(s"""select contractNumber+ 10 as a from VMALL_DICTIONARY_EXCLUDE order by a""", + s"""select contractNumber+ 10 as a from VMALL_DICTIONARY_EXCLUDE1_hive order by a""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_057") + + } + + + //VMALL_DICTIONARY_EXCLUDE_058 + test("VMALL_DICTIONARY_EXCLUDE_058", Include) { + + checkAnswer(s"""select min(contractNumber), max(contractNumber+ 10) Total from VMALL_DICTIONARY_EXCLUDE group by channelsId order by Total""", + s"""select min(contractNumber), max(contractNumber+ 10) Total from VMALL_DICTIONARY_EXCLUDE1_hive group by channelsId order by Total""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_058") + + } + + + //VMALL_DICTIONARY_EXCLUDE_059 + test("VMALL_DICTIONARY_EXCLUDE_059", Include) { + + sql(s"""select last(contractNumber) a from VMALL_DICTIONARY_EXCLUDE order by a""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_060 + test("VMALL_DICTIONARY_EXCLUDE_060", Include) { + + sql(s"""select FIRST(contractNumber) a from VMALL_DICTIONARY_EXCLUDE order by a""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_061 + test("VMALL_DICTIONARY_EXCLUDE_061", Include) { + + checkAnswer(s"""select contractNumber,count(contractNumber) a from VMALL_DICTIONARY_EXCLUDE group by contractNumber order by contractNumber""", + s"""select contractNumber,count(contractNumber) a from VMALL_DICTIONARY_EXCLUDE1_hive group by contractNumber order by contractNumber""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_061") + + } + + + //VMALL_DICTIONARY_EXCLUDE_062 + test("VMALL_DICTIONARY_EXCLUDE_062", Include) { + + checkAnswer(s"""select Lower(contractNumber) a from VMALL_DICTIONARY_EXCLUDE order by contractNumber""", + s"""select Lower(contractNumber) a from VMALL_DICTIONARY_EXCLUDE1_hive order by contractNumber""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_062") + + } + + + //VMALL_DICTIONARY_EXCLUDE_063 + test("VMALL_DICTIONARY_EXCLUDE_063", Include) { + + checkAnswer(s"""select distinct contractNumber from VMALL_DICTIONARY_EXCLUDE order by contractNumber""", + s"""select distinct contractNumber from VMALL_DICTIONARY_EXCLUDE1_hive order by contractNumber""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_063") + + } + + + //VMALL_DICTIONARY_EXCLUDE_064 + test("VMALL_DICTIONARY_EXCLUDE_064", Include) { + + checkAnswer(s"""select contractNumber from VMALL_DICTIONARY_EXCLUDE order by contractNumber limit 101""", + s"""select contractNumber from VMALL_DICTIONARY_EXCLUDE1_hive order by contractNumber limit 101""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_064") + + } + + + //VMALL_DICTIONARY_EXCLUDE_065 + test("VMALL_DICTIONARY_EXCLUDE_065", Include) { + + checkAnswer(s"""select contractNumber as a from VMALL_DICTIONARY_EXCLUDE order by a asc limit 10""", + s"""select contractNumber as a from VMALL_DICTIONARY_EXCLUDE1_hive order by a asc limit 10""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_065") + + } + + + //VMALL_DICTIONARY_EXCLUDE_066 + test("VMALL_DICTIONARY_EXCLUDE_066", Include) { + + checkAnswer(s"""select contractNumber from VMALL_DICTIONARY_EXCLUDE where (contractNumber == 9223372047700) and (imei=='1AA100012')""", + s"""select contractNumber from VMALL_DICTIONARY_EXCLUDE1_hive where (contractNumber == 9223372047700) and (imei=='1AA100012')""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_066") + + } + + + //VMALL_DICTIONARY_EXCLUDE_067 + test("VMALL_DICTIONARY_EXCLUDE_067", Include) { + + checkAnswer(s"""select contractNumber from VMALL_DICTIONARY_EXCLUDE where contractNumber !=9223372047700 order by contractNumber""", + s"""select contractNumber from VMALL_DICTIONARY_EXCLUDE1_hive where contractNumber !=9223372047700 order by contractNumber""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_067") + + } + + + //VMALL_DICTIONARY_EXCLUDE_068 + test("VMALL_DICTIONARY_EXCLUDE_068", Include) { + + checkAnswer(s"""select contractNumber from VMALL_DICTIONARY_EXCLUDE where (deviceInformationId=100 and deviceColor='1Device Color') OR (deviceInformationId=10 and deviceColor='0Device Color') order by contractNumber""", + s"""select contractNumber from VMALL_DICTIONARY_EXCLUDE1_hive where (deviceInformationId=100 and deviceColor='1Device Color') OR (deviceInformationId=10 and deviceColor='0Device Color') order by contractNumber""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_068") + + } + + + //VMALL_DICTIONARY_EXCLUDE_069 + test("VMALL_DICTIONARY_EXCLUDE_069", Include) { + + checkAnswer(s"""select contractNumber from VMALL_DICTIONARY_EXCLUDE where contractNumber !=9223372047700 order by contractNumber""", + s"""select contractNumber from VMALL_DICTIONARY_EXCLUDE1_hive where contractNumber !=9223372047700 order by contractNumber""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_069") + + } + + + //VMALL_DICTIONARY_EXCLUDE_070 + test("VMALL_DICTIONARY_EXCLUDE_070", Include) { + + checkAnswer(s"""select contractNumber from VMALL_DICTIONARY_EXCLUDE where contractNumber >9223372047700 order by contractNumber""", + s"""select contractNumber from VMALL_DICTIONARY_EXCLUDE1_hive where contractNumber >9223372047700 order by contractNumber""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_070") + + } + + + //VMALL_DICTIONARY_EXCLUDE_071 + test("VMALL_DICTIONARY_EXCLUDE_071", Include) { + + checkAnswer(s"""select contractNumber from VMALL_DICTIONARY_EXCLUDE where contractNumber<>contractNumber""", + s"""select contractNumber from VMALL_DICTIONARY_EXCLUDE1_hive where contractNumber<>contractNumber""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_071") + + } + + + //VMALL_DICTIONARY_EXCLUDE_072 + test("VMALL_DICTIONARY_EXCLUDE_072", Include) { + + checkAnswer(s"""select contractNumber from VMALL_DICTIONARY_EXCLUDE where contractNumber != Latest_areaId order by contractNumber""", + s"""select contractNumber from VMALL_DICTIONARY_EXCLUDE1_hive where contractNumber != Latest_areaId order by contractNumber""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_072") + + } + + + //VMALL_DICTIONARY_EXCLUDE_073 + test("VMALL_DICTIONARY_EXCLUDE_073", Include) { + + checkAnswer(s"""select contractNumber, contractNumber from VMALL_DICTIONARY_EXCLUDE where Latest_areaId<contractNumber order by contractNumber""", + s"""select contractNumber, contractNumber from VMALL_DICTIONARY_EXCLUDE1_hive where Latest_areaId<contractNumber order by contractNumber""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_073") + + } + + + //VMALL_DICTIONARY_EXCLUDE_074 + test("VMALL_DICTIONARY_EXCLUDE_074", Include) { + + checkAnswer(s"""select contractNumber, contractNumber from VMALL_DICTIONARY_EXCLUDE where Latest_DAY<=contractNumber order by contractNumber""", + s"""select contractNumber, contractNumber from VMALL_DICTIONARY_EXCLUDE1_hive where Latest_DAY<=contractNumber order by contractNumber""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_074") + + } + + + //VMALL_DICTIONARY_EXCLUDE_075 + test("VMALL_DICTIONARY_EXCLUDE_075", Include) { + + checkAnswer(s"""select contractNumber from VMALL_DICTIONARY_EXCLUDE where contractNumber <1000 order by contractNumber""", + s"""select contractNumber from VMALL_DICTIONARY_EXCLUDE1_hive where contractNumber <1000 order by contractNumber""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_075") + + } + + + //VMALL_DICTIONARY_EXCLUDE_076 + test("VMALL_DICTIONARY_EXCLUDE_076", Include) { + + checkAnswer(s"""select contractNumber from VMALL_DICTIONARY_EXCLUDE where contractNumber >1000 order by contractNumber""", + s"""select contractNumber from VMALL_DICTIONARY_EXCLUDE1_hive where contractNumber >1000 order by contractNumber""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_076") + + } + + + //VMALL_DICTIONARY_EXCLUDE_077 + test("VMALL_DICTIONARY_EXCLUDE_077", Include) { + + checkAnswer(s"""select contractNumber from VMALL_DICTIONARY_EXCLUDE where contractNumber IS NULL order by contractNumber""", + s"""select contractNumber from VMALL_DICTIONARY_EXCLUDE1_hive where contractNumber IS NULL order by contractNumber""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_077") + + } + + + //VMALL_DICTIONARY_EXCLUDE_078 + test("VMALL_DICTIONARY_EXCLUDE_078", Include) { + + checkAnswer(s"""select contractNumber from VMALL_DICTIONARY_EXCLUDE where Latest_DAY IS NOT NULL order by contractNumber""", + s"""select contractNumber from VMALL_DICTIONARY_EXCLUDE1_hive where Latest_DAY IS NOT NULL order by contractNumber""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_078") + + } + + + //VMALL_DICTIONARY_EXCLUDE_079 + test("VMALL_DICTIONARY_EXCLUDE_079", Include) { + + checkAnswer(s"""Select count(Latest_DAY) from VMALL_DICTIONARY_EXCLUDE""", + s"""Select count(Latest_DAY) from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_079") + + } + + + //VMALL_DICTIONARY_EXCLUDE_080 + test("VMALL_DICTIONARY_EXCLUDE_080", Include) { + + checkAnswer(s"""select count(DISTINCT Latest_DAY) as a from VMALL_DICTIONARY_EXCLUDE""", + s"""select count(DISTINCT Latest_DAY) as a from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_080") + + } + + + //VMALL_DICTIONARY_EXCLUDE_081 + test("VMALL_DICTIONARY_EXCLUDE_081", Include) { + + checkAnswer(s"""select sum(Latest_DAY)+10 as a ,Latest_DAY from VMALL_DICTIONARY_EXCLUDE group by Latest_DAY order by a""", + s"""select sum(Latest_DAY)+10 as a ,Latest_DAY from VMALL_DICTIONARY_EXCLUDE1_hive group by Latest_DAY order by a""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_081") + + } + + + //VMALL_DICTIONARY_EXCLUDE_082 + test("VMALL_DICTIONARY_EXCLUDE_082", Include) { + + checkAnswer(s"""select max(Latest_DAY),min(Latest_DAY) from VMALL_DICTIONARY_EXCLUDE""", + s"""select max(Latest_DAY),min(Latest_DAY) from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_082") + + } + + + //VMALL_DICTIONARY_EXCLUDE_083 + test("VMALL_DICTIONARY_EXCLUDE_083", Include) { + + checkAnswer(s"""select sum(Latest_DAY) a from VMALL_DICTIONARY_EXCLUDE""", + s"""select sum(Latest_DAY) a from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_083") + + } + + + //VMALL_DICTIONARY_EXCLUDE_084 + test("VMALL_DICTIONARY_EXCLUDE_084", Include) { + + checkAnswer(s"""select avg(Latest_DAY) a from VMALL_DICTIONARY_EXCLUDE""", + s"""select avg(Latest_DAY) a from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_084") + + } + + + //VMALL_DICTIONARY_EXCLUDE_085 + test("VMALL_DICTIONARY_EXCLUDE_085", Include) { + + checkAnswer(s"""select min(Latest_DAY) a from VMALL_DICTIONARY_EXCLUDE""", + s"""select min(Latest_DAY) a from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_085") + + } + + + //VMALL_DICTIONARY_EXCLUDE_086 + test("VMALL_DICTIONARY_EXCLUDE_086", Include) { + + sql(s"""select variance(Latest_DAY) as a from (select Latest_DAY from VMALL_DICTIONARY_EXCLUDE order by Latest_DAY) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_087 + test("VMALL_DICTIONARY_EXCLUDE_087", Include) { + + sql(s"""select var_pop(Latest_DAY) as a from (select Latest_DAY from VMALL_DICTIONARY_EXCLUDE order by Latest_DAY) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_088 + test("VMALL_DICTIONARY_EXCLUDE_088", Include) { + + sql(s"""select var_samp(Latest_DAY) as a from (select Latest_DAY from VMALL_DICTIONARY_EXCLUDE order by Latest_DAY) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_089 + test("VMALL_DICTIONARY_EXCLUDE_089", Include) { + + sql(s"""select stddev_pop(Latest_DAY) as a from (select Latest_DAY from VMALL_DICTIONARY_EXCLUDE order by Latest_DAY) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_090 + test("VMALL_DICTIONARY_EXCLUDE_090", Include) { + + sql(s"""select stddev_samp(Latest_DAY) as a from (select Latest_DAY from VMALL_DICTIONARY_EXCLUDE order by Latest_DAY) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_091 + test("VMALL_DICTIONARY_EXCLUDE_091", Include) { + + sql(s"""select covar_pop(Latest_DAY,Latest_DAY) as a from (select Latest_DAY from VMALL_DICTIONARY_EXCLUDE order by Latest_DAY) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_092 + test("VMALL_DICTIONARY_EXCLUDE_092", Include) { + + sql(s"""select covar_samp(Latest_DAY,Latest_DAY) as a from (select Latest_DAY from VMALL_DICTIONARY_EXCLUDE order by Latest_DAY) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_093 + test("VMALL_DICTIONARY_EXCLUDE_093", Include) { + + checkAnswer(s"""select corr(Latest_DAY,Latest_DAY) as a from VMALL_DICTIONARY_EXCLUDE""", + s"""select corr(Latest_DAY,Latest_DAY) as a from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_093") + + } + + + //VMALL_DICTIONARY_EXCLUDE_094 + test("VMALL_DICTIONARY_EXCLUDE_094", Include) { + + sql(s"""select percentile_approx(Latest_DAY,0.2) as a from (select Latest_DAY from VMALL_DICTIONARY_EXCLUDE order by Latest_DAY) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_095 + test("VMALL_DICTIONARY_EXCLUDE_095", Include) { + + sql(s"""select percentile_approx(Latest_DAY,0.2,5) as a from (select Latest_DAY from VMALL_DICTIONARY_EXCLUDE order by Latest_DAY) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_096 + test("VMALL_DICTIONARY_EXCLUDE_096", Include) { + + sql(s"""select percentile_approx(Latest_DAY,array(0.2,0.3,0.99)) as a from (select Latest_DAY from VMALL_DICTIONARY_EXCLUDE order by Latest_DAY) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_097 + test("VMALL_DICTIONARY_EXCLUDE_097", Include) { + + sql(s"""select percentile_approx(Latest_DAY,array(0.2,0.3,0.99),5) as a from (select Latest_DAY from VMALL_DICTIONARY_EXCLUDE order by Latest_DAY) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_098 + test("VMALL_DICTIONARY_EXCLUDE_098", Include) { + + sql(s"""select histogram_numeric(Latest_DAY,2) as a from (select Latest_DAY from VMALL_DICTIONARY_EXCLUDE order by Latest_DAY) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_099 + test("VMALL_DICTIONARY_EXCLUDE_099", Include) { + + checkAnswer(s"""select Latest_DAY, Latest_DAY+ 10 as a from VMALL_DICTIONARY_EXCLUDE order by a""", + s"""select Latest_DAY, Latest_DAY+ 10 as a from VMALL_DICTIONARY_EXCLUDE1_hive order by a""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_099") + + } + + + //VMALL_DICTIONARY_EXCLUDE_100 + test("VMALL_DICTIONARY_EXCLUDE_100", Include) { + + checkAnswer(s"""select min(Latest_DAY) d, max(Latest_DAY+ 10) Total from VMALL_DICTIONARY_EXCLUDE group by channelsId order by d, Total""", + s"""select min(Latest_DAY) d, max(Latest_DAY+ 10) Total from VMALL_DICTIONARY_EXCLUDE1_hive group by channelsId order by d,Total""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_100") + + } + + + //VMALL_DICTIONARY_EXCLUDE_101 + test("VMALL_DICTIONARY_EXCLUDE_101", Include) { + + sql(s"""select last(Latest_DAY) a from VMALL_DICTIONARY_EXCLUDE order by a""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_102 + test("VMALL_DICTIONARY_EXCLUDE_102", Include) { + + sql(s"""select FIRST(Latest_DAY) a from (select Latest_DAY from VMALL_DICTIONARY_EXCLUDE order by Latest_DAY) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_103 + test("VMALL_DICTIONARY_EXCLUDE_103", Include) { + + checkAnswer(s"""select Latest_DAY,count(Latest_DAY) a from VMALL_DICTIONARY_EXCLUDE group by Latest_DAY order by Latest_DAY""", + s"""select Latest_DAY,count(Latest_DAY) a from VMALL_DICTIONARY_EXCLUDE1_hive group by Latest_DAY order by Latest_DAY""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_103") + + } + + + //VMALL_DICTIONARY_EXCLUDE_104 + test("VMALL_DICTIONARY_EXCLUDE_104", Include) { + + checkAnswer(s"""select Lower(Latest_DAY) a from VMALL_DICTIONARY_EXCLUDE order by a""", + s"""select Lower(Latest_DAY) a from VMALL_DICTIONARY_EXCLUDE1_hive order by a""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_104") + + } + + + //VMALL_DICTIONARY_EXCLUDE_105 + test("VMALL_DICTIONARY_EXCLUDE_105", Include) { + + checkAnswer(s"""select distinct Latest_DAY from VMALL_DICTIONARY_EXCLUDE order by Latest_DAY""", + s"""select distinct Latest_DAY from VMALL_DICTIONARY_EXCLUDE1_hive order by Latest_DAY""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_105") + + } + + + //VMALL_DICTIONARY_EXCLUDE_106 + test("VMALL_DICTIONARY_EXCLUDE_106", Include) { + + checkAnswer(s"""select Latest_DAY from VMALL_DICTIONARY_EXCLUDE order by Latest_DAY limit 101""", + s"""select Latest_DAY from VMALL_DICTIONARY_EXCLUDE1_hive order by Latest_DAY limit 101""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_106") + + } + + + //VMALL_DICTIONARY_EXCLUDE_107 + test("VMALL_DICTIONARY_EXCLUDE_107", Include) { + + checkAnswer(s"""select Latest_DAY as a from VMALL_DICTIONARY_EXCLUDE order by a asc limit 10""", + s"""select Latest_DAY as a from VMALL_DICTIONARY_EXCLUDE1_hive order by a asc limit 10""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_107") + + } + + + //VMALL_DICTIONARY_EXCLUDE_108 + test("VMALL_DICTIONARY_EXCLUDE_108", Include) { + + checkAnswer(s"""select Latest_DAY from VMALL_DICTIONARY_EXCLUDE where (Latest_DAY == 1234567890123450.0000000000) and (imei=='1AA1')""", + s"""select Latest_DAY from VMALL_DICTIONARY_EXCLUDE1_hive where (Latest_DAY == 1234567890123450.0000000000) and (imei=='1AA1')""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_108") + + } + + + //VMALL_DICTIONARY_EXCLUDE_109 + test("VMALL_DICTIONARY_EXCLUDE_109", Include) { + + checkAnswer(s"""select Latest_DAY from VMALL_DICTIONARY_EXCLUDE where Latest_DAY !=1234567890123450.0000000000 order by Latest_DAY""", + s"""select Latest_DAY from VMALL_DICTIONARY_EXCLUDE1_hive where Latest_DAY !=1234567890123450.0000000000 order by Latest_DAY""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_109") + + } + + + //VMALL_DICTIONARY_EXCLUDE_110 + test("VMALL_DICTIONARY_EXCLUDE_110", Include) { + + checkAnswer(s"""select Latest_DAY from VMALL_DICTIONARY_EXCLUDE where (deviceInformationId=100 and deviceColor='1Device Color') OR (deviceInformationId=10 and deviceColor='0Device Color')""", + s"""select Latest_DAY from VMALL_DICTIONARY_EXCLUDE1_hive where (deviceInformationId=100 and deviceColor='1Device Color') OR (deviceInformationId=10 and deviceColor='0Device Color')""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_110") + + } + + + //VMALL_DICTIONARY_EXCLUDE_111 + test("VMALL_DICTIONARY_EXCLUDE_111", Include) { + + checkAnswer(s"""select Latest_DAY from VMALL_DICTIONARY_EXCLUDE where Latest_DAY !=1234567890123450.0000000000 order by Latest_DAY""", + s"""select Latest_DAY from VMALL_DICTIONARY_EXCLUDE1_hive where Latest_DAY !=1234567890123450.0000000000 order by Latest_DAY""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_111") + + } + + + //VMALL_DICTIONARY_EXCLUDE_112 + test("VMALL_DICTIONARY_EXCLUDE_112", Include) { + + checkAnswer(s"""select Latest_DAY from VMALL_DICTIONARY_EXCLUDE where Latest_DAY >1234567890123450.0000000000 order by Latest_DAY""", + s"""select Latest_DAY from VMALL_DICTIONARY_EXCLUDE1_hive where Latest_DAY >1234567890123450.0000000000 order by Latest_DAY""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_112") + + } + + + //VMALL_DICTIONARY_EXCLUDE_113 + test("VMALL_DICTIONARY_EXCLUDE_113", Include) { + + checkAnswer(s"""select Latest_DAY from VMALL_DICTIONARY_EXCLUDE where Latest_DAY<>Latest_DAY""", + s"""select Latest_DAY from VMALL_DICTIONARY_EXCLUDE1_hive where Latest_DAY<>Latest_DAY""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_113") + + } + + + //VMALL_DICTIONARY_EXCLUDE_114 + test("VMALL_DICTIONARY_EXCLUDE_114", Include) { + + checkAnswer(s"""select Latest_DAY from VMALL_DICTIONARY_EXCLUDE where Latest_DAY != Latest_areaId order by Latest_DAY""", + s"""select Latest_DAY from VMALL_DICTIONARY_EXCLUDE1_hive where Latest_DAY != Latest_areaId order by Latest_DAY""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_114") + + } + + + //VMALL_DICTIONARY_EXCLUDE_115 + test("VMALL_DICTIONARY_EXCLUDE_115", Include) { + + checkAnswer(s"""select Latest_DAY, Latest_DAY from VMALL_DICTIONARY_EXCLUDE where Latest_areaId<Latest_DAY order by Latest_DAY""", + s"""select Latest_DAY, Latest_DAY from VMALL_DICTIONARY_EXCLUDE1_hive where Latest_areaId<Latest_DAY order by Latest_DAY""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_115") + + } + + + //VMALL_DICTIONARY_EXCLUDE_116 + test("VMALL_DICTIONARY_EXCLUDE_116", Include) { + + checkAnswer(s"""select Latest_DAY, Latest_DAY from VMALL_DICTIONARY_EXCLUDE where Latest_DAY<=Latest_DAY order by Latest_DAY""", + s"""select Latest_DAY, Latest_DAY from VMALL_DICTIONARY_EXCLUDE1_hive where Latest_DAY<=Latest_DAY order by Latest_DAY""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_116") + + } + + + //VMALL_DICTIONARY_EXCLUDE_117 + test("VMALL_DICTIONARY_EXCLUDE_117", Include) { + + checkAnswer(s"""select Latest_DAY from VMALL_DICTIONARY_EXCLUDE where Latest_DAY <1000 order by Latest_DAY""", + s"""select Latest_DAY from VMALL_DICTIONARY_EXCLUDE1_hive where Latest_DAY <1000 order by Latest_DAY""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_117") + + } + + + //VMALL_DICTIONARY_EXCLUDE_118 + test("VMALL_DICTIONARY_EXCLUDE_118", Include) { + + checkAnswer(s"""select Latest_DAY from VMALL_DICTIONARY_EXCLUDE where Latest_DAY >1000 order by Latest_DAY""", + s"""select Latest_DAY from VMALL_DICTIONARY_EXCLUDE1_hive where Latest_DAY >1000 order by Latest_DAY""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_118") + + } + + + //VMALL_DICTIONARY_EXCLUDE_119 + test("VMALL_DICTIONARY_EXCLUDE_119", Include) { + + checkAnswer(s"""select Latest_DAY from VMALL_DICTIONARY_EXCLUDE where Latest_DAY IS NULL order by Latest_DAY""", + s"""select Latest_DAY from VMALL_DICTIONARY_EXCLUDE1_hive where Latest_DAY IS NULL order by Latest_DAY""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_119") + + } + + + //VMALL_DICTIONARY_EXCLUDE_120 + test("VMALL_DICTIONARY_EXCLUDE_120", Include) { + + checkAnswer(s"""select Latest_DAY from VMALL_DICTIONARY_EXCLUDE where Latest_DAY IS NOT NULL order by Latest_DAY""", + s"""select Latest_DAY from VMALL_DICTIONARY_EXCLUDE1_hive where Latest_DAY IS NOT NULL order by Latest_DAY""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_120") + + } + + + //VMALL_DICTIONARY_EXCLUDE_121 + test("VMALL_DICTIONARY_EXCLUDE_121", Include) { + + checkAnswer(s"""Select count(gamePointId) from VMALL_DICTIONARY_EXCLUDE""", + s"""Select count(gamePointId) from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_121") + + } + + + //VMALL_DICTIONARY_EXCLUDE_122 + test("VMALL_DICTIONARY_EXCLUDE_122", Include) { + + checkAnswer(s"""select count(DISTINCT gamePointId) as a from VMALL_DICTIONARY_EXCLUDE""", + s"""select count(DISTINCT gamePointId) as a from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_122") + + } + + + //VMALL_DICTIONARY_EXCLUDE_123 + test("VMALL_DICTIONARY_EXCLUDE_123", Include) { + + checkAnswer(s"""select sum(gamePointId)+10 as a ,gamePointId from VMALL_DICTIONARY_EXCLUDE group by gamePointId order by a""", + s"""select sum(gamePointId)+10 as a ,gamePointId from VMALL_DICTIONARY_EXCLUDE1_hive group by gamePointId order by a""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_123") + + } + + + //VMALL_DICTIONARY_EXCLUDE_124 + test("VMALL_DICTIONARY_EXCLUDE_124", Include) { + + checkAnswer(s"""select max(gamePointId),min(gamePointId) from VMALL_DICTIONARY_EXCLUDE""", + s"""select max(gamePointId),min(gamePointId) from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_124") + + } + + + //VMALL_DICTIONARY_EXCLUDE_125 + ignore("VMALL_DICTIONARY_EXCLUDE_125", Include) { + + checkAnswer(s"""select sum(gamePointId) a from (select gamePointId from VMALL_DICTIONARY_EXCLUDE order by gamePointId)""", + s"""select sum(gamePointId) a from (select gamePointId from VMALL_DICTIONARY_EXCLUDE1_hive order by gamePointId)""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_125") + + } + + + //VMALL_DICTIONARY_EXCLUDE_126 + ignore("VMALL_DICTIONARY_EXCLUDE_126", Include) { + + checkAnswer(s"""select avg(gamePointId) a from (select gamePointId from VMALL_DICTIONARY_EXCLUDE order by gamePointId)""", + s"""select avg(gamePointId) a from (select gamePointId from VMALL_DICTIONARY_EXCLUDE1_hive order by gamePointId)""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_126") + + } + + + //VMALL_DICTIONARY_EXCLUDE_127 + test("VMALL_DICTIONARY_EXCLUDE_127", Include) { + + checkAnswer(s"""select min(gamePointId) a from VMALL_DICTIONARY_EXCLUDE""", + s"""select min(gamePointId) a from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_127") + + } + + + //VMALL_DICTIONARY_EXCLUDE_128 + test("VMALL_DICTIONARY_EXCLUDE_128", Include) { + + sql(s"""select variance(gamePointId) as a from (select gamePointId from VMALL_DICTIONARY_EXCLUDE order by gamePointId) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_129 + test("VMALL_DICTIONARY_EXCLUDE_129", Include) { + + sql(s"""select var_pop(gamePointId) as a from (select gamePointId from VMALL_DICTIONARY_EXCLUDE order by gamePointId) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_130 + test("VMALL_DICTIONARY_EXCLUDE_130", Include) { + + sql(s"""select var_samp(gamePointId) as a from (select gamePointId from VMALL_DICTIONARY_EXCLUDE order by gamePointId) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_131 + test("VMALL_DICTIONARY_EXCLUDE_131", Include) { + + sql(s"""select stddev_pop(gamePointId) as a from (select gamePointId from VMALL_DICTIONARY_EXCLUDE order by gamePointId) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_132 + test("VMALL_DICTIONARY_EXCLUDE_132", Include) { + + sql(s"""select stddev_samp(gamePointId) as a from (select gamePointId from VMALL_DICTIONARY_EXCLUDE order by gamePointId) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_133 + test("VMALL_DICTIONARY_EXCLUDE_133", Include) { + + sql(s"""select covar_pop(gamePointId,gamePointId) as a from (select gamePointId from VMALL_DICTIONARY_EXCLUDE order by gamePointId) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_134 + test("VMALL_DICTIONARY_EXCLUDE_134", Include) { + + sql(s"""select covar_samp(gamePointId,gamePointId) as a from (select gamePointId from VMALL_DICTIONARY_EXCLUDE order by gamePointId) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_135 + test("VMALL_DICTIONARY_EXCLUDE_135", Include) { + + checkAnswer(s"""select corr(gamePointId,gamePointId) as a from VMALL_DICTIONARY_EXCLUDE""", + s"""select corr(gamePointId,gamePointId) as a from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_135") + + } + + + //VMALL_DICTIONARY_EXCLUDE_136 + test("VMALL_DICTIONARY_EXCLUDE_136", Include) { + + sql(s"""select percentile_approx(gamePointId,0.2) as a from (select gamePointId from VMALL_DICTIONARY_EXCLUDE order by gamePointId) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_137 + test("VMALL_DICTIONARY_EXCLUDE_137", Include) { + + sql(s"""select percentile_approx(gamePointId,0.2,5) as a from (select gamePointId from VMALL_DICTIONARY_EXCLUDE order by gamePointId) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_138 + test("VMALL_DICTIONARY_EXCLUDE_138", Include) { + + sql(s"""select percentile_approx(gamePointId,array(0.2,0.3,0.99)) as a from (select gamePointId from VMALL_DICTIONARY_EXCLUDE order by gamePointId) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_139 + test("VMALL_DICTIONARY_EXCLUDE_139", Include) { + + sql(s"""select percentile_approx(gamePointId,array(0.2,0.3,0.99),5) as a from (select gamePointId from VMALL_DICTIONARY_EXCLUDE order by gamePointId) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_140 + test("VMALL_DICTIONARY_EXCLUDE_140", Include) { + + sql(s"""select histogram_numeric(gamePointId,2) as a from (select gamePointId from VMALL_DICTIONARY_EXCLUDE order by gamePointId) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_141 + test("VMALL_DICTIONARY_EXCLUDE_141", Include) { + + checkAnswer(s"""select gamePointId, gamePointId+ 10 as a from VMALL_DICTIONARY_EXCLUDE order by a""", + s"""select gamePointId, gamePointId+ 10 as a from VMALL_DICTIONARY_EXCLUDE1_hive order by a""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_141") + + } + + + //VMALL_DICTIONARY_EXCLUDE_142 + test("VMALL_DICTIONARY_EXCLUDE_142", Include) { + + checkAnswer(s"""select min(gamePointId), max(gamePointId+ 10) Total from VMALL_DICTIONARY_EXCLUDE group by channelsId order by Total""", + s"""select min(gamePointId), max(gamePointId+ 10) Total from VMALL_DICTIONARY_EXCLUDE1_hive group by channelsId order by Total""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_142") + + } + + + //VMALL_DICTIONARY_EXCLUDE_143 + test("VMALL_DICTIONARY_EXCLUDE_143", Include) { + + sql(s"""select last(gamePointId) a from (select gamePointId from VMALL_DICTIONARY_EXCLUDE order by gamePointId) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_144 + ignore("VMALL_DICTIONARY_EXCLUDE_144", Include) { + + checkAnswer(s"""select FIRST(gamePointId) a from VMALL_DICTIONARY_EXCLUDE order by a""", + s"""select FIRST(gamePointId) a from VMALL_DICTIONARY_EXCLUDE1_hive order by a""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_144") + + } + + + //VMALL_DICTIONARY_EXCLUDE_145 + test("VMALL_DICTIONARY_EXCLUDE_145", Include) { + + checkAnswer(s"""select gamePointId,count(gamePointId) a from VMALL_DICTIONARY_EXCLUDE group by gamePointId order by gamePointId""", + s"""select gamePointId,count(gamePointId) a from VMALL_DICTIONARY_EXCLUDE1_hive group by gamePointId order by gamePointId""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_145") + + } + + + //VMALL_DICTIONARY_EXCLUDE_146 + test("VMALL_DICTIONARY_EXCLUDE_146", Include) { + + checkAnswer(s"""select Lower(gamePointId) a from VMALL_DICTIONARY_EXCLUDE order by gamePointId""", + s"""select Lower(gamePointId) a from VMALL_DICTIONARY_EXCLUDE1_hive order by gamePointId""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_146") + + } + + + //VMALL_DICTIONARY_EXCLUDE_147 + test("VMALL_DICTIONARY_EXCLUDE_147", Include) { + + checkAnswer(s"""select distinct gamePointId from VMALL_DICTIONARY_EXCLUDE order by gamePointId""", + s"""select distinct gamePointId from VMALL_DICTIONARY_EXCLUDE1_hive order by gamePointId""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_147") + + } + + + //VMALL_DICTIONARY_EXCLUDE_148 + test("VMALL_DICTIONARY_EXCLUDE_148", Include) { + + checkAnswer(s"""select gamePointId from VMALL_DICTIONARY_EXCLUDE order by gamePointId limit 101""", + s"""select gamePointId from VMALL_DICTIONARY_EXCLUDE1_hive order by gamePointId limit 101""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_148") + + } + + + //VMALL_DICTIONARY_EXCLUDE_149 + test("VMALL_DICTIONARY_EXCLUDE_149", Include) { + + checkAnswer(s"""select gamePointId as a from VMALL_DICTIONARY_EXCLUDE order by a asc limit 10""", + s"""select gamePointId as a from VMALL_DICTIONARY_EXCLUDE1_hive order by a asc limit 10""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_149") + + } + + + //VMALL_DICTIONARY_EXCLUDE_150 + test("VMALL_DICTIONARY_EXCLUDE_150", Include) { + + checkAnswer(s"""select gamePointId from VMALL_DICTIONARY_EXCLUDE where (gamePointId == 4.70133553923674E43) and (imei=='1AA100084')""", + s"""select gamePointId from VMALL_DICTIONARY_EXCLUDE1_hive where (gamePointId == 4.70133553923674E43) and (imei=='1AA100084')""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_150") + + } + + + //VMALL_DICTIONARY_EXCLUDE_151 + test("VMALL_DICTIONARY_EXCLUDE_151", Include) { + + checkAnswer(s"""select gamePointId from VMALL_DICTIONARY_EXCLUDE where gamePointId !=4.70133553923674E43 order by gamePointId""", + s"""select gamePointId from VMALL_DICTIONARY_EXCLUDE1_hive where gamePointId !=4.70133553923674E43 order by gamePointId""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_151") + + } + + + //VMALL_DICTIONARY_EXCLUDE_152 + test("VMALL_DICTIONARY_EXCLUDE_152", Include) { + + checkAnswer(s"""select gamePointId from VMALL_DICTIONARY_EXCLUDE where (deviceInformationId=100 and deviceColor='1Device Color') OR (deviceInformationId=10 and deviceColor='0Device Color')""", + s"""select gamePointId from VMALL_DICTIONARY_EXCLUDE1_hive where (deviceInformationId=100 and deviceColor='1Device Color') OR (deviceInformationId=10 and deviceColor='0Device Color')""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_152") + + } + + + //VMALL_DICTIONARY_EXCLUDE_153 + test("VMALL_DICTIONARY_EXCLUDE_153", Include) { + + checkAnswer(s"""select gamePointId from VMALL_DICTIONARY_EXCLUDE where gamePointId !=4.70133553923674E43""", + s"""select gamePointId from VMALL_DICTIONARY_EXCLUDE1_hive where gamePointId !=4.70133553923674E43""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_153") + + } + + + //VMALL_DICTIONARY_EXCLUDE_154 + test("VMALL_DICTIONARY_EXCLUDE_154", Include) { + + checkAnswer(s"""select gamePointId from VMALL_DICTIONARY_EXCLUDE where gamePointId >4.70133553923674E43""", + s"""select gamePointId from VMALL_DICTIONARY_EXCLUDE1_hive where gamePointId >4.70133553923674E43""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_154") + + } + + + //VMALL_DICTIONARY_EXCLUDE_155 + test("VMALL_DICTIONARY_EXCLUDE_155", Include) { + + checkAnswer(s"""select gamePointId from VMALL_DICTIONARY_EXCLUDE where gamePointId<>gamePointId""", + s"""select gamePointId from VMALL_DICTIONARY_EXCLUDE1_hive where gamePointId<>gamePointId""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_155") + + } + + + //VMALL_DICTIONARY_EXCLUDE_156 + test("VMALL_DICTIONARY_EXCLUDE_156", Include) { + + checkAnswer(s"""select gamePointId from VMALL_DICTIONARY_EXCLUDE where gamePointId != Latest_areaId order by gamePointId""", + s"""select gamePointId from VMALL_DICTIONARY_EXCLUDE1_hive where gamePointId != Latest_areaId order by gamePointId""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_156") + + } + + + //VMALL_DICTIONARY_EXCLUDE_157 + test("VMALL_DICTIONARY_EXCLUDE_157", Include) { + + checkAnswer(s"""select gamePointId, gamePointId from VMALL_DICTIONARY_EXCLUDE where Latest_areaId<gamePointId order by gamePointId""", + s"""select gamePointId, gamePointId from VMALL_DICTIONARY_EXCLUDE1_hive where Latest_areaId<gamePointId order by gamePointId""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_157") + + } + + + //VMALL_DICTIONARY_EXCLUDE_158 + test("VMALL_DICTIONARY_EXCLUDE_158", Include) { + + checkAnswer(s"""select gamePointId, gamePointId from VMALL_DICTIONARY_EXCLUDE where gamePointId<=gamePointId order by gamePointId""", + s"""select gamePointId, gamePointId from VMALL_DICTIONARY_EXCLUDE1_hive where gamePointId<=gamePointId order by gamePointId""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_158") + + } + + + //VMALL_DICTIONARY_EXCLUDE_159 + test("VMALL_DICTIONARY_EXCLUDE_159", Include) { + + checkAnswer(s"""select gamePointId from VMALL_DICTIONARY_EXCLUDE where gamePointId <1000 order by gamePointId""", + s"""select gamePointId from VMALL_DICTIONARY_EXCLUDE1_hive where gamePointId <1000 order by gamePointId""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_159") + + } + + + //VMALL_DICTIONARY_EXCLUDE_160 + test("VMALL_DICTIONARY_EXCLUDE_160", Include) { + + checkAnswer(s"""select gamePointId from VMALL_DICTIONARY_EXCLUDE where gamePointId >1000 order by gamePointId""", + s"""select gamePointId from VMALL_DICTIONARY_EXCLUDE1_hive where gamePointId >1000 order by gamePointId""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_160") + + } + + + //VMALL_DICTIONARY_EXCLUDE_161 + test("VMALL_DICTIONARY_EXCLUDE_161", Include) { + + checkAnswer(s"""select gamePointId from VMALL_DICTIONARY_EXCLUDE where gamePointId IS NULL order by gamePointId""", + s"""select gamePointId from VMALL_DICTIONARY_EXCLUDE1_hive where gamePointId IS NULL order by gamePointId""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_161") + + } + + + //VMALL_DICTIONARY_EXCLUDE_162 + test("VMALL_DICTIONARY_EXCLUDE_162", Include) { + + checkAnswer(s"""select gamePointId from VMALL_DICTIONARY_EXCLUDE where gamePointId IS NOT NULL order by gamePointId""", + s"""select gamePointId from VMALL_DICTIONARY_EXCLUDE1_hive where gamePointId IS NOT NULL order by gamePointId""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_162") + + } + + + //VMALL_DICTIONARY_EXCLUDE_163 + test("VMALL_DICTIONARY_EXCLUDE_163", Include) { + + checkAnswer(s"""Select count(productionDate) from VMALL_DICTIONARY_EXCLUDE""", + s"""Select count(productionDate) from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_163") + + } + + + //VMALL_DICTIONARY_EXCLUDE_164 + test("VMALL_DICTIONARY_EXCLUDE_164", Include) { + + checkAnswer(s"""select count(DISTINCT productionDate) as a from VMALL_DICTIONARY_EXCLUDE""", + s"""select count(DISTINCT productionDate) as a from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_164") + + } + + + //VMALL_DICTIONARY_EXCLUDE_165 + test("VMALL_DICTIONARY_EXCLUDE_165", Include) { + + checkAnswer(s"""select sum(productionDate)+10 as a ,productionDate from VMALL_DICTIONARY_EXCLUDE group by productionDate order by productionDate""", + s"""select sum(productionDate)+10 as a ,productionDate from VMALL_DICTIONARY_EXCLUDE1_hive group by productionDate order by productionDate""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_165") + + } + + + //VMALL_DICTIONARY_EXCLUDE_166 + test("VMALL_DICTIONARY_EXCLUDE_166", Include) { + + checkAnswer(s"""select max(productionDate),min(productionDate) from VMALL_DICTIONARY_EXCLUDE""", + s"""select max(productionDate),min(productionDate) from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_166") + + } + + + //VMALL_DICTIONARY_EXCLUDE_167 + test("VMALL_DICTIONARY_EXCLUDE_167", Include) { + + checkAnswer(s"""select sum(productionDate) a from VMALL_DICTIONARY_EXCLUDE""", + s"""select sum(productionDate) a from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_167") + + } + + + //VMALL_DICTIONARY_EXCLUDE_168 + test("VMALL_DICTIONARY_EXCLUDE_168", Include) { + + checkAnswer(s"""select avg(productionDate) a from VMALL_DICTIONARY_EXCLUDE""", + s"""select avg(productionDate) a from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_168") + + } + + + //VMALL_DICTIONARY_EXCLUDE_169 + test("VMALL_DICTIONARY_EXCLUDE_169", Include) { + + checkAnswer(s"""select min(productionDate) a from VMALL_DICTIONARY_EXCLUDE""", + s"""select min(productionDate) a from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_169") + + } + + + //VMALL_DICTIONARY_EXCLUDE_170 + test("VMALL_DICTIONARY_EXCLUDE_170", Include) { + + sql(s"""select variance(gamePointId) as a from (select gamePointId from VMALL_DICTIONARY_EXCLUDE order by gamePointId) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_171 + ignore("VMALL_DICTIONARY_EXCLUDE_171", Include) { + + checkAnswer(s"""select var_pop(gamePointId) as a from (select * from VMALL_DICTIONARY_EXCLUDE order by gamePointId)""", + s"""select var_pop(gamePointId) as a from (select * from VMALL_DICTIONARY_EXCLUDE1_hive order by gamePointId)""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_171") + + } + + + //VMALL_DICTIONARY_EXCLUDE_172 + ignore("VMALL_DICTIONARY_EXCLUDE_172", Include) { + + checkAnswer(s"""select var_samp(gamePointId) as a from (select * from VMALL_DICTIONARY_EXCLUDE order by gamePointId)""", + s"""select var_samp(gamePointId) as a from (select * from VMALL_DICTIONARY_EXCLUDE1_hive order by gamePointId)""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_172") + + } + + + //VMALL_DICTIONARY_EXCLUDE_173 + test("VMALL_DICTIONARY_EXCLUDE_173", Include) { + + sql(s"""select stddev_pop(gamePointId) as a from (select gamePointId from VMALL_DICTIONARY_EXCLUDE order by gamePointId) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_174 + test("VMALL_DICTIONARY_EXCLUDE_174", Include) { + + sql(s"""select stddev_samp(gamePointId) as a from (select gamePointId from VMALL_DICTIONARY_EXCLUDE order by gamePointId) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_175 + test("VMALL_DICTIONARY_EXCLUDE_175", Include) { + + sql(s"""select covar_pop(gamePointId,gamePointId) as a from (select gamePointId from VMALL_DICTIONARY_EXCLUDE order by gamePointId) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_176 + test("VMALL_DICTIONARY_EXCLUDE_176", Include) { + + sql(s"""select covar_samp(gamePointId,gamePointId) as a from (select gamePointId from VMALL_DICTIONARY_EXCLUDE order by gamePointId) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_177 + test("VMALL_DICTIONARY_EXCLUDE_177", Include) { + + checkAnswer(s"""select corr(gamePointId,gamePointId) as a from VMALL_DICTIONARY_EXCLUDE""", + s"""select corr(gamePointId,gamePointId) as a from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_177") + + } + + + //VMALL_DICTIONARY_EXCLUDE_178 + test("VMALL_DICTIONARY_EXCLUDE_178", Include) { + + sql(s"""select percentile_approx(gamePointId,0.2) as a from (select gamePointId from VMALL_DICTIONARY_EXCLUDE order by gamePointId) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_179 + test("VMALL_DICTIONARY_EXCLUDE_179", Include) { + + sql(s"""select percentile_approx(gamePointId,0.2,5) as a from (select gamePointId from VMALL_DICTIONARY_EXCLUDE order by gamePointId) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_180 + test("VMALL_DICTIONARY_EXCLUDE_180", Include) { + + sql(s"""select percentile_approx(gamePointId,array(0.2,0.3,0.99)) as a from (select gamePointId from VMALL_DICTIONARY_EXCLUDE order by gamePointId) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_181 + test("VMALL_DICTIONARY_EXCLUDE_181", Include) { + + sql(s"""select percentile_approx(gamePointId,array(0.2,0.3,0.99),5) as a from (select gamePointId from VMALL_DICTIONARY_EXCLUDE order by gamePointId) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_182 + test("VMALL_DICTIONARY_EXCLUDE_182", Include) { + + sql(s"""select histogram_numeric(productionDate,2) as a from (select productionDate from VMALL_DICTIONARY_EXCLUDE order by productionDate) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_183 + test("VMALL_DICTIONARY_EXCLUDE_183", Include) { + + sql(s"""select last(productionDate) a from VMALL_DICTIONARY_EXCLUDE order by a""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_184 + test("VMALL_DICTIONARY_EXCLUDE_184", Include) { + + sql(s"""select FIRST(productionDate) a from (select productionDate from VMALL_DICTIONARY_EXCLUDE order by productionDate) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_185 + test("VMALL_DICTIONARY_EXCLUDE_185", Include) { + + checkAnswer(s"""select productionDate,count(productionDate) a from VMALL_DICTIONARY_EXCLUDE group by productionDate order by productionDate""", + s"""select productionDate,count(productionDate) a from VMALL_DICTIONARY_EXCLUDE1_hive group by productionDate order by productionDate""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_185") + + } + + + //VMALL_DICTIONARY_EXCLUDE_186 + test("VMALL_DICTIONARY_EXCLUDE_186", Include) { + + checkAnswer(s"""select Lower(productionDate) a from VMALL_DICTIONARY_EXCLUDE order by productionDate""", + s"""select Lower(productionDate) a from VMALL_DICTIONARY_EXCLUDE1_hive order by productionDate""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_186") + + } + + + //VMALL_DICTIONARY_EXCLUDE_187 + test("VMALL_DICTIONARY_EXCLUDE_187", Include) { + + checkAnswer(s"""select distinct productionDate from VMALL_DICTIONARY_EXCLUDE order by productionDate""", + s"""select distinct productionDate from VMALL_DICTIONARY_EXCLUDE1_hive order by productionDate""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_187") + + } + + + //VMALL_DICTIONARY_EXCLUDE_188 + test("VMALL_DICTIONARY_EXCLUDE_188", Include) { + + checkAnswer(s"""select productionDate from VMALL_DICTIONARY_EXCLUDE order by productionDate limit 101""", + s"""select productionDate from VMALL_DICTIONARY_EXCLUDE1_hive order by productionDate limit 101""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_188") + + } + + + //VMALL_DICTIONARY_EXCLUDE_189 + test("VMALL_DICTIONARY_EXCLUDE_189", Include) { + + checkAnswer(s"""select productionDate as a from VMALL_DICTIONARY_EXCLUDE order by a asc limit 10""", + s"""select productionDate as a from VMALL_DICTIONARY_EXCLUDE1_hive order by a asc limit 10""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_189") + + } + + + //VMALL_DICTIONARY_EXCLUDE_190 + test("VMALL_DICTIONARY_EXCLUDE_190", Include) { + + checkAnswer(s"""select productionDate from VMALL_DICTIONARY_EXCLUDE where (productionDate == '2015-07-01 12:07:28.0') and (productionDate=='2015-07-01 12:07:28.0')""", + s"""select productionDate from VMALL_DICTIONARY_EXCLUDE1_hive where (productionDate == '2015-07-01 12:07:28.0') and (productionDate=='2015-07-01 12:07:28.0')""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_190") + + } + + + //VMALL_DICTIONARY_EXCLUDE_191 + test("VMALL_DICTIONARY_EXCLUDE_191", Include) { + + checkAnswer(s"""select productionDate from VMALL_DICTIONARY_EXCLUDE where productionDate !='2015-07-01 12:07:28.0' order by productionDate""", + s"""select productionDate from VMALL_DICTIONARY_EXCLUDE1_hive where productionDate !='2015-07-01 12:07:28.0' order by productionDate""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_191") + + } + + + //VMALL_DICTIONARY_EXCLUDE_192 + test("VMALL_DICTIONARY_EXCLUDE_192", Include) { + + checkAnswer(s"""select productionDate from VMALL_DICTIONARY_EXCLUDE where (deviceInformationId=100 and deviceColor='1Device Color') OR (deviceInformationId=10 and deviceColor='0Device Color')""", + s"""select productionDate from VMALL_DICTIONARY_EXCLUDE1_hive where (deviceInformationId=100 and deviceColor='1Device Color') OR (deviceInformationId=10 and deviceColor='0Device Color')""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_192") + + } + + + //VMALL_DICTIONARY_EXCLUDE_193 + test("VMALL_DICTIONARY_EXCLUDE_193", Include) { + + checkAnswer(s"""select productionDate from VMALL_DICTIONARY_EXCLUDE where productionDate !='2015-07-01 12:07:28.0' order by productionDate""", + s"""select productionDate from VMALL_DICTIONARY_EXCLUDE1_hive where productionDate !='2015-07-01 12:07:28.0' order by productionDate""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_193") + + } + + + //VMALL_DICTIONARY_EXCLUDE_194 + test("VMALL_DICTIONARY_EXCLUDE_194", Include) { + + checkAnswer(s"""select productionDate from VMALL_DICTIONARY_EXCLUDE where productionDate >'2015-07-01 12:07:28.0' order by productionDate""", + s"""select productionDate from VMALL_DICTIONARY_EXCLUDE1_hive where productionDate >'2015-07-01 12:07:28.0' order by productionDate""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_194") + + } + + + //VMALL_DICTIONARY_EXCLUDE_195 + test("VMALL_DICTIONARY_EXCLUDE_195", Include) { + + checkAnswer(s"""select productionDate from VMALL_DICTIONARY_EXCLUDE where productionDate<>productionDate order by productionDate""", + s"""select productionDate from VMALL_DICTIONARY_EXCLUDE1_hive where productionDate<>productionDate order by productionDate""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_195") + + } + + + //VMALL_DICTIONARY_EXCLUDE_196 + test("VMALL_DICTIONARY_EXCLUDE_196", Include) { + + checkAnswer(s"""select productionDate from VMALL_DICTIONARY_EXCLUDE where productionDate != Latest_areaId order by productionDate""", + s"""select productionDate from VMALL_DICTIONARY_EXCLUDE1_hive where productionDate != Latest_areaId order by productionDate""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_196") + + } + + + //VMALL_DICTIONARY_EXCLUDE_197 + test("VMALL_DICTIONARY_EXCLUDE_197", Include) { + + checkAnswer(s"""select productionDate from VMALL_DICTIONARY_EXCLUDE where Latest_areaId<productionDate order by productionDate""", + s"""select productionDate from VMALL_DICTIONARY_EXCLUDE1_hive where Latest_areaId<productionDate order by productionDate""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_197") + + } + + + //VMALL_DICTIONARY_EXCLUDE_198 + test("VMALL_DICTIONARY_EXCLUDE_198", Include) { + + checkAnswer(s"""select productionDate from VMALL_DICTIONARY_EXCLUDE where productionDate<=productionDate order by productionDate""", + s"""select productionDate from VMALL_DICTIONARY_EXCLUDE1_hive where productionDate<=productionDate order by productionDate""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_198") + + } + + + //VMALL_DICTIONARY_EXCLUDE_199 + test("VMALL_DICTIONARY_EXCLUDE_199", Include) { + + checkAnswer(s"""select productionDate from VMALL_DICTIONARY_EXCLUDE where productionDate <cast('2015-07-01 12:07:28.0' as timestamp) order by productionDate""", + s"""select productionDate from VMALL_DICTIONARY_EXCLUDE1_hive where productionDate <cast('2015-07-01 12:07:28.0' as timestamp) order by productionDate""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_199") + + } + + + //VMALL_DICTIONARY_EXCLUDE_200 + test("VMALL_DICTIONARY_EXCLUDE_200", Include) { + + checkAnswer(s"""select productionDate from VMALL_DICTIONARY_EXCLUDE where productionDate IS NULL""", + s"""select productionDate from VMALL_DICTIONARY_EXCLUDE1_hive where productionDate IS NULL""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_200") + + } + + + //VMALL_DICTIONARY_EXCLUDE_201 + test("VMALL_DICTIONARY_EXCLUDE_201", Include) { + + checkAnswer(s"""select productionDate from VMALL_DICTIONARY_EXCLUDE where productionDate IS NOT NULL order by productionDate""", + s"""select productionDate from VMALL_DICTIONARY_EXCLUDE1_hive where productionDate IS NOT NULL order by productionDate""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_201") + + } + + + //VMALL_DICTIONARY_EXCLUDE_202 + test("VMALL_DICTIONARY_EXCLUDE_202", Include) { + + checkAnswer(s"""Select count(deviceInformationId) from VMALL_DICTIONARY_EXCLUDE""", + s"""Select count(deviceInformationId) from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_202") + + } + + + //VMALL_DICTIONARY_EXCLUDE_203 + test("VMALL_DICTIONARY_EXCLUDE_203", Include) { + + checkAnswer(s"""select count(DISTINCT deviceInformationId) as a from VMALL_DICTIONARY_EXCLUDE""", + s"""select count(DISTINCT deviceInformationId) as a from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_203") + + } + + + //VMALL_DICTIONARY_EXCLUDE_204 + test("VMALL_DICTIONARY_EXCLUDE_204", Include) { + + checkAnswer(s"""select sum(deviceInformationId)+10 as a ,deviceInformationId from VMALL_DICTIONARY_EXCLUDE group by deviceInformationId order by deviceInformationId""", + s"""select sum(deviceInformationId)+10 as a ,deviceInformationId from VMALL_DICTIONARY_EXCLUDE1_hive group by deviceInformationId order by deviceInformationId""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_204") + + } + + + //VMALL_DICTIONARY_EXCLUDE_205 + test("VMALL_DICTIONARY_EXCLUDE_205", Include) { + + checkAnswer(s"""select max(deviceInformationId),min(deviceInformationId) from VMALL_DICTIONARY_EXCLUDE""", + s"""select max(deviceInformationId),min(deviceInformationId) from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_205") + + } + + + //VMALL_DICTIONARY_EXCLUDE_206 + test("VMALL_DICTIONARY_EXCLUDE_206", Include) { + + checkAnswer(s"""select sum(deviceInformationId) a from VMALL_DICTIONARY_EXCLUDE""", + s"""select sum(deviceInformationId) a from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_206") + + } + + + //VMALL_DICTIONARY_EXCLUDE_207 + test("VMALL_DICTIONARY_EXCLUDE_207", Include) { + + checkAnswer(s"""select avg(deviceInformationId) a from VMALL_DICTIONARY_EXCLUDE""", + s"""select avg(deviceInformationId) a from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_207") + + } + + + //VMALL_DICTIONARY_EXCLUDE_208 + test("VMALL_DICTIONARY_EXCLUDE_208", Include) { + + checkAnswer(s"""select min(deviceInformationId) a from VMALL_DICTIONARY_EXCLUDE""", + s"""select min(deviceInformationId) a from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_208") + + } + + + //VMALL_DICTIONARY_EXCLUDE_209 + test("VMALL_DICTIONARY_EXCLUDE_209", Include) { + + sql(s"""select variance(deviceInformationId) as a from (select deviceInformationId from VMALL_DICTIONARY_EXCLUDE order by deviceInformationId) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_210 + ignore("VMALL_DICTIONARY_EXCLUDE_210", Include) { + + checkAnswer(s"""select var_pop(deviceInformationId) as a from VMALL_DICTIONARY_EXCLUDE""", + s"""select var_pop(deviceInformationId) as a from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_210") + + } + + + //VMALL_DICTIONARY_EXCLUDE_211 + ignore("VMALL_DICTIONARY_EXCLUDE_211", Include) { + + checkAnswer(s"""select var_samp(deviceInformationId) as a from VMALL_DICTIONARY_EXCLUDE""", + s"""select var_samp(deviceInformationId) as a from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_211") + + } + + + //VMALL_DICTIONARY_EXCLUDE_212 + test("VMALL_DICTIONARY_EXCLUDE_212", Include) { + + sql(s"""select stddev_pop(deviceInformationId) as a from (select deviceInformationId from VMALL_DICTIONARY_EXCLUDE order by deviceInformationId) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_213 + test("VMALL_DICTIONARY_EXCLUDE_213", Include) { + + sql(s"""select stddev_samp(deviceInformationId) as a from (select deviceInformationId from VMALL_DICTIONARY_EXCLUDE order by deviceInformationId) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_214 + test("VMALL_DICTIONARY_EXCLUDE_214", Include) { + + sql(s"""select covar_pop(deviceInformationId,deviceInformationId) as a from (select deviceInformationId from VMALL_DICTIONARY_EXCLUDE order by deviceInformationId) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_215 + test("VMALL_DICTIONARY_EXCLUDE_215", Include) { + + sql(s"""select covar_samp(deviceInformationId,deviceInformationId) as a from (select deviceInformationId from VMALL_DICTIONARY_EXCLUDE order by deviceInformationId) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_216 + test("VMALL_DICTIONARY_EXCLUDE_216", Include) { + + checkAnswer(s"""select corr(deviceInformationId,deviceInformationId) as a from VMALL_DICTIONARY_EXCLUDE""", + s"""select corr(deviceInformationId,deviceInformationId) as a from VMALL_DICTIONARY_EXCLUDE1_hive""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_216") + + } + + + //VMALL_DICTIONARY_EXCLUDE_217 + test("VMALL_DICTIONARY_EXCLUDE_217", Include) { + + sql(s"""select percentile_approx(deviceInformationId,0.2) as a from (select deviceInformationId from VMALL_DICTIONARY_EXCLUDE order by deviceInformationId) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_218 + test("VMALL_DICTIONARY_EXCLUDE_218", Include) { + + sql(s"""select percentile_approx(deviceInformationId,0.2,5) as a from (select deviceInformationId from VMALL_DICTIONARY_EXCLUDE order by deviceInformationId) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_219 + test("VMALL_DICTIONARY_EXCLUDE_219", Include) { + + sql(s"""select percentile_approx(deviceInformationId,array(0.2,0.3,0.99)) as a from (select deviceInformationId from VMALL_DICTIONARY_EXCLUDE order by deviceInformationId) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_220 + test("VMALL_DICTIONARY_EXCLUDE_220", Include) { + + sql(s"""select percentile_approx(deviceInformationId,array(0.2,0.3,0.99),5) as a from (select deviceInformationId from VMALL_DICTIONARY_EXCLUDE order by deviceInformationId) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_221 + test("VMALL_DICTIONARY_EXCLUDE_221", Include) { + + sql(s"""select histogram_numeric(deviceInformationId,2) as a from (select deviceInformationId from VMALL_DICTIONARY_EXCLUDE order by deviceInformationId) t""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_222 + test("VMALL_DICTIONARY_EXCLUDE_222", Include) { + + checkAnswer(s"""select deviceInformationId, deviceInformationId+ 10 as a from VMALL_DICTIONARY_EXCLUDE order by deviceInformationId""", + s"""select deviceInformationId, deviceInformationId+ 10 as a from VMALL_DICTIONARY_EXCLUDE1_hive order by deviceInformationId""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_222") + + } + + + //VMALL_DICTIONARY_EXCLUDE_223 + test("VMALL_DICTIONARY_EXCLUDE_223", Include) { + + checkAnswer(s"""select min(deviceInformationId), max(deviceInformationId+ 10) Total from VMALL_DICTIONARY_EXCLUDE group by channelsId order by Total""", + s"""select min(deviceInformationId), max(deviceInformationId+ 10) Total from VMALL_DICTIONARY_EXCLUDE1_hive group by channelsId order by Total""", "QueriesExcludeDictionaryTestCase_VMALL_DICTIONARY_EXCLUDE_223") + + } + + + //VMALL_DICTIONARY_EXCLUDE_224 + test("VMALL_DICTIONARY_EXCLUDE_224", Include) { + + sql(s"""select last(deviceInformationId) a from VMALL_DICTIONARY_EXCLUDE order by a""").collect + + } + + + //VMALL_DICTIONARY_EXCLUDE_225 + test("VMALL_DICTIONARY_EXCLUDE_225", Include) { + + sql(s"""select FIRST(deviceInformationId) a from (select deviceInformationId from VMALL_DICTIONARY_EXCLUDE order by deviceInformationId) t""").collect + + } + + + //VMALL_DICTIONARY_E
<TRUNCATED>
