xubo245 created CARBONDATA-2661:
-----------------------------------
Summary: Query failed with group by column when MV Datamap created
without group by column
Key: CARBONDATA-2661
URL: https://issues.apache.org/jira/browse/CARBONDATA-2661
Project: CarbonData
Issue Type: Bug
Reporter: xubo245
Assignee: xubo245
{code:java}
drop table if exists fact_table1;
CREATE TABLE fact_table1 (empno int, empname String, designation String, doj
Timestamp,
workgroupcategory int, workgroupcategoryname String, deptno int, deptname
String,
projectcode int, projectjoindate Timestamp, projectenddate Timestamp,attendance
int,
utilization int,salary int)
STORED BY 'org.apache.carbondata.format';
LOAD DATA local inpath 'hdfs://hacluster/user/hive/warehouse//data_mv.csv'
INTO TABLE fact_table1 OPTIONS('DELIMITER'= ',', 'QUOTECHAR'=
'"','timestampformat'='dd-MM-yyyy');
LOAD DATA local inpath 'hdfs://hacluster/user/hive/warehouse//data_mv.csv'
INTO TABLE fact_table1 OPTIONS('DELIMITER'= ',', 'QUOTECHAR'=
'"','timestampformat'='dd-MM-yyyy');
0: jdbc:hive2://hadoop1:10000> create datamap mv1 using 'mv' as select
sum(salary),count(empno) from fact_table1 group by empname;
+---------+--+
| Result |
+---------+--+
+---------+--+
No rows selected (0.399 seconds)
0: jdbc:hive2://hadoop1:10000> rebuild datamap mv1;
+---------+--+
| Result |
+---------+--+
+---------+--+
No rows selected (1.57 seconds)
0: jdbc:hive2://hadoop1:10000> create datamap mv2 using 'mv' as select
sum(salary) from fact_table1 group by empname;
+---------+--+
| Result |
+---------+--+
+---------+--+
No rows selected (0.321 seconds)
0: jdbc:hive2://hadoop1:10000> rebuild datamap mv2;
+---------+--+
| Result |
+---------+--+
+---------+--+
No rows selected (1.241 seconds)
0: jdbc:hive2://hadoop1:10000> show datamap on table fact_table1;
+--------------+------------+--------------------------+--+
| DataMapName | ClassName | Associated Table |
+--------------+------------+--------------------------+--+
| datamap25 | mv | default.datamap25_table |
| mv1 | mv | default.mv1_table |
| mv2 | mv | default.mv2_table |
+--------------+------------+--------------------------+--+
3 rows selected (0.047 seconds)
0: jdbc:hive2://hadoop1:10000> select * from default.mv1_table;
+-------------+--------------+--+
| sum_salary | count_empno |
+-------------+--------------+--+
| 172332 | 18 |
| 162972 | 18 |
| 90720 | 18 |
| 202572 | 18 |
| 90720 | 18 |
| 128232 | 18 |
| 130410 | 18 |
| 202464 | 18 |
| 243846 | 18 |
| 238410 | 18 |
+-------------+--------------+--+
10 rows selected (0.314 seconds)
0: jdbc:hive2://hadoop1:10000> select count(*) from default.mv1_table;
+-----------+--+
| count(1) |
+-----------+--+
| 10 |
+-----------+--+
1 row selected (0.139 seconds)
0: jdbc:hive2://hadoop1:10000> select * from default.mv2_table;
+-------------+--+
| sum_salary |
+-------------+--+
| 172332 |
| 162972 |
| 90720 |
| 202464 |
| 243846 |
| 128232 |
| 130410 |
| 90720 |
| 202572 |
| 238410 |
+-------------+--+
10 rows selected (0.262 seconds)
0: jdbc:hive2://hadoop1:10000> explain select sum(salary) from fact_table1
group by empname;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+
|
plan
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+
| == CarbonData Profiler ==
Table Scan on fact_table1
- total blocklets: 2
- filter: none
- pruned by Main DataMap
- skipped blocklets: 0
|
| == Physical Plan ==
*HashAggregate(keys=[empname#2499], functions=[sum(cast(salary#2511 as
bigint))])
+- Exchange hashpartitioning(empname#2499, 200)
+- *HashAggregate(keys=[empname#2499],
functions=[partial_sum(cast(salary#2511 as bigint))])
+- *BatchedScan CarbonDatasourceHadoopRelation [ Database name :default,
Table name :fact_table1, Schema
:Some(StructType(StructField(empno,IntegerType,true),
StructField(empname,StringType,true), StructField(designation,StringType,true),
StructField(doj,TimestampType,true),
StructField(workgroupcategory,IntegerType,true),
StructField(workgroupcategoryname,StringType,true),
StructField(deptno,IntegerType,true), StructField(deptname,StringType,true),
StructField(projectcode,IntegerType,true),
StructField(projectjoindate,TimestampType,true),
StructField(projectenddate,TimestampType,true),
StructField(attendance,IntegerType,true),
StructField(utilization,IntegerType,true),
StructField(salary,IntegerType,true))) ]
default.fact_table1[empname#2499,salary#2511] |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+
2 rows selected (0.164 seconds)
0: jdbc:hive2://hadoop1:10000> explain select empname,sum(salary) from
fact_table1 group by empname;
Error: org.apache.spark.sql.AnalysisException: cannot resolve
'`fact_table1.empname`' given input columns: [sum_salary, count_empno]; line 6
pos 11;
'Aggregate ['fact_table1.empname], ['fact_table1.empname,
'sum('gen_subsumer_0.sum(salary)) AS sum(salary)#2802]
+- 'SubqueryAlias gen_subsumer_0
+- 'Aggregate ['fact_table1.empname], [sum(sum_salary#2495L) AS
sum(salary)#2800L, sum(count_empno#2496L) AS count(empno)#2801L]
+- SubqueryAlias mv1_table
+- Relation[sum_salary#2495L,count_empno#2496L]
CarbonDatasourceHadoopRelation [ Database name :default, Table name :mv1_table,
Schema :Some(StructType(StructField(sum_salary,LongType,true),
StructField(count_empno,LongType,true))) ] (state=,code=0)
0: jdbc:hive2://hadoop1:10000> create datamap mv3 using 'mv' as select
empname,sum(salary) from fact_table1 group by empname;
+---------+--+
| Result |
+---------+--+
+---------+--+
No rows selected (0.318 seconds)
0: jdbc:hive2://hadoop1:10000> rebuild datamap mv3;
+---------+--+
| Result |
+---------+--+
+---------+--+
No rows selected (1.437 seconds)
0: jdbc:hive2://hadoop1:10000> drop datamap mv3;
+---------+--+
| Result |
+---------+--+
+---------+--+
No rows selected (0.941 seconds)
0: jdbc:hive2://hadoop1:10000> create datamap mv3 using 'mv' as select
empname,sum(salary),count(empno) from fact_table1 group by empname;
+---------+--+
| Result |
+---------+--+
+---------+--+
No rows selected (0.537 seconds)
0: jdbc:hive2://hadoop1:10000> create datamap mv4 using 'mv' as select
empname,sum(salary) from fact_table1 group by empname;
+---------+--+
| Result |
+---------+--+
+---------+--+
No rows selected (0.271 seconds)
0: jdbc:hive2://hadoop1:10000> rebuild datamap mv3;
+---------+--+
| Result |
+---------+--+
+---------+--+
No rows selected (1.525 seconds)
0: jdbc:hive2://hadoop1:10000> rebuild datamap mv4;
+---------+--+
| Result |
+---------+--+
+---------+--+
No rows selected (0.993 seconds)
0: jdbc:hive2://hadoop1:10000> explain select empname,sum(salary) from
fact_table1 group by empname;
Error: org.apache.spark.sql.AnalysisException: cannot resolve
'`fact_table1.empname`' given input columns: [sum_salary, count_empno]; line 6
pos 11;
'Aggregate ['fact_table1.empname], ['fact_table1.empname,
'sum('gen_subsumer_0.sum(salary)) AS sum(salary)#3113]
+- 'SubqueryAlias gen_subsumer_0
+- 'Aggregate ['fact_table1.empname], [sum(sum_salary#2495L) AS
sum(salary)#3111L, sum(count_empno#2496L) AS count(empno)#3112L]
+- SubqueryAlias mv1_table
+- Relation[sum_salary#2495L,count_empno#2496L]
CarbonDatasourceHadoopRelation [ Database name :default, Table name :mv1_table,
Schema :Some(StructType(StructField(sum_salary,LongType,true),
StructField(count_empno,LongType,true))) ] (state=,code=0)
{code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
