[jira] [Resolved] (CARBONDATA-442) Query result mismatching with Hive

[Akash R Nilugal (Jira)]

     [ https://issues.apache.org/jira/browse/CARBONDATA-442?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Ravindra Pesala resolved CARBONDATA-442.
----------------------------------------
       Resolution: Won't Fix
    Fix Version/s: 1.0.0-incubating

Not an issue.

> Query result mismatching with Hive
> ----------------------------------
>
>                 Key: CARBONDATA-442
>                 URL: https://issues.apache.org/jira/browse/CARBONDATA-442
>             Project: CarbonData
>          Issue Type: Bug
>            Reporter: SWATI RAO
>             Fix For: 1.0.0-incubating
>
>
> => I created table using following command :
> create table Carbon_automation_test5 (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,gamePointDescription string, gamePointId int,contractNumber int) stored by 'org.apache.carbondata.format'
> => Load csv to table :
> LOAD DATA INPATH 'hdfs://localhost:54310/user/hduser/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,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_releaseId,Latest_EMUIVersion,Latest_operaSysVersion,Latest_BacVerNumber,Latest_BacFlashVer,Latest_webUIVersion,Latest_webUITypeCarrVer,Latest_webTypeDataVerNumber,Latest_operatorsVersion,Latest_phonePADPartitionedVersions,Latest_operatorId,gamePointId,gamePointDescription')
> =>now executed SELECT querry :
> 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;
> +------------+----------------+-------------+------------------+--+
> |   AMSize   | ActiveCountry  | Activecity  | Sum_gamePointId  |
> +------------+----------------+-------------+------------------+--+
> | 4RAM size  | Chinese        | changsha    | 200860           |
> | 4RAM size  | Chinese        | guangzhou   | 38016            |
> | 4RAM size  | Chinese        | shenzhen    | 49610            |
> | 4RAM size  | Chinese        | wuhan       | 117568           |
> | 4RAM size  | Chinese        | xiangtan    | 254320           |
> | 4RAM size  | Chinese        | yichang     | 29436            |
> | 5RAM size  | Chinese        | changsha    | 13845            |
> | 5RAM size  | Chinese        | guangzhou   | 23560            |
> | 5RAM size  | Chinese        | wuhan       | 12390            |
> | 6RAM size  | Chinese        | changsha    | 23697            |
> | 6RAM size  | Chinese        | guangzhou   | 15912            |
> | 6RAM size  | Chinese        | shenzhen    | 19278            |
> | 6RAM size  | Chinese        | wuhan       | 29313            |
> | 6RAM size  | Chinese        | xiangtan    | 7794             |
> | 6RAM size  | Chinese        | zhuzhou     | 26568            |
> | 7RAM size  | Chinese        | changsha    | 1057             |
> | 7RAM size  | Chinese        | wuhan       | 27853            |
> | 7RAM size  | Chinese        | yichang     | 14217            |
> | 7RAM size  | Chinese        | zhuzhou     | 15673            |
> | 8RAM size  | Chinese        | guangzhou   | 27380            |
> | 8RAM size  | Chinese        | shenzhen    | 3550             |
> | 8RAM size  | Chinese        | wuhan       | 29700            |
> | 8RAM size  | Chinese        | xiangtan    | 31020            |
> | 8RAM size  | Chinese        | yichang     | 51660            |
> | 8RAM size  | Chinese        | zhuzhou     | 26840            |
> | 9RAM size  | Chinese        | changsha    | 32390            |
> | 9RAM size  | Chinese        | shenzhen    | 30650            |
> | 9RAM size  | Chinese        | wuhan       | 15670            |
> | 9RAM size  | Chinese        | xiangtan    | 58210            |
> | 9RAM size  | Chinese        | yichang     | 5710             |
> +------------+----------------+-------------+------------------+--+
> => Also i have executed above command on hive as follow :
> #  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,gamePointDescription string, gamePointId int,contractNumber int) row format delimited fields terminated by ','
> # LOAD DATA INPATH 'hdfs://localhost:54310/user/hduser/100_olap.csv' INTO table Carbon_automation_test5;
> # 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;
> +------------+----------------+---------------------+------------------+--+
> |   AMSize   | ActiveCountry  |     Activecity      | Sum_gamePointId  |
> +------------+----------------+---------------------+------------------+--+
> | 4RAM size  | 1              | Guangdong Province  | 49610            |
> | 4RAM size  | 2              | Guangdong Province  | 38016            |
> | 4RAM size  | 3              | Hunan Province      | 200860           |
> | 4RAM size  | 4              | Hunan Province      | 254320           |
> | 4RAM size  | 6              | Hubei Province      | 117568           |
> | 4RAM size  | 7              | Hubei Province      | 29436            |
> | 5RAM size  | 2              | Guangdong Province  | 23560            |
> | 5RAM size  | 3              | Hunan Province      | 13845            |
> | 5RAM size  | 6              | Hubei Province      | 12390            |
> | 6RAM size  | 1              | Guangdong Province  | 19278            |
> | 6RAM size  | 2              | Guangdong Province  | 15912            |
> | 6RAM size  | 3              | Hunan Province      | 23697            |
> | 6RAM size  | 4              | Hunan Province      | 7794             |
> | 6RAM size  | 5              | Hunan Province      | 26568            |
> | 6RAM size  | 6              | Hubei Province      | 29313            |
> | 7RAM size  | 3              | Hunan Province      | 1057             |
> | 7RAM size  | 5              | Hunan Province      | 15673            |
> | 7RAM size  | 6              | Hubei Province      | 27853            |
> | 7RAM size  | 7              | Hubei Province      | 14217            |
> | 8RAM size  | 1              | Guangdong Province  | 3550             |
> | 8RAM size  | 2              | Guangdong Province  | 27380            |
> | 8RAM size  | 4              | Hunan Province      | 31020            |
> | 8RAM size  | 5              | Hunan Province      | 26840            |
> | 8RAM size  | 6              | Hubei Province      | 29700            |
> | 8RAM size  | 7              | Hubei Province      | 51660            |
> | 9RAM size  | 1              | Guangdong Province  | 30650            |
> | 9RAM size  | 3              | Hunan Province      | 32390            |
> | 9RAM size  | 4              | Hunan Province      | 58210            |
> | 9RAM size  | 6              | Hubei Province      | 15670            |
> | 9RAM size  | 7              | Hubei Province      | 5710             |
> +------------+----------------+---------------------+------------------+--+

--
This message was sent by Atlassian JIRA
(v6.3.4#6332)