[
https://issues.apache.org/jira/browse/CARBONDATA-2535?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16524452#comment-16524452
]
xubo245 commented on CARBONDATA-2535:
-------------------------------------
It's work fine in cluster:
{code:java}
0: jdbc:hive2://hadoop1:10000> select empname, sum(utilization) as totalutil
from fact7 group by empname having totalutil >20;
+----------+------------+--+
| empname | totalutil |
+----------+------------+--+
+----------+------------+--+
No rows selected (0.443 seconds)
0: jdbc:hive2://hadoop1:10000> explain select empname, sum(utilization) as
totalutil from fact7 group by empname having totalutil >20;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+
|
plan
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+
| == CarbonData Profiler ==
|
| == Physical Plan ==
*Filter (isnotnull(totalutil#560L) && (totalutil#560L > 20))
+- *HashAggregate(keys=[fact7_empname#441],
functions=[sum(sum_utilization#442L)])
+- Exchange hashpartitioning(fact7_empname#441, 200)
+- *HashAggregate(keys=[fact7_empname#441],
functions=[partial_sum(sum_utilization#442L)])
+- *BatchedScan CarbonDatasourceHadoopRelation [ Database name
:default, Table name :datamap57_table, Schema
:Some(StructType(StructField(fact7_empname,StringType,true),
StructField(sum_utilization,LongType,true))) ]
default.datamap57_table[fact7_empname#441,sum_utilization#442L] |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+
2 rows selected (0.25 seconds)
{code}
> [MV] Query Failed in MV datamap when Having is used in query.
> --------------------------------------------------------------
>
> Key: CARBONDATA-2535
> URL: https://issues.apache.org/jira/browse/CARBONDATA-2535
> Project: CarbonData
> Issue Type: Bug
> Reporter: Babulal
> Assignee: xubo245
> Priority: Major
>
> create and load from MVCreateTestCase.scala
>
> CREATE TABLE fact7 (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' TBLPROPERTIES('table_blocksize'='3')
> LOAD DATA local inpath '/tmp/babu/data_big.csv' INTO TABLE fact6
> OPTIONS('DELIMITER'= ',', 'QUOTECHAR'= '"','timestampformat'='dd-MM-yyyy')
>
> create datamap datamap57 using 'mv' as select empname, sum(utilization) from
> fact7 group by empname
>
> Now Run
> 0: jdbc:hive2://10.18.222.231:23040> explain select empname, sum(utilization)
> from fact7 group by empname;
> | plan |
> +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+
> | == Physical Plan ==
> *BatchedScan CarbonDatasourceHadoopRelation [ Database name :babu, Table name
> :*datamap57_table*, Schema
> :Some(StructType(StructField(fact7_empname,StringType,true),
> StructField(sum_utilization,LongType,true))) ]
> babu.datamap57_table[fact7_empname#1228,sum_utilization#1363L] |
> +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+–+
>
> Now run Query with Having0: jdbc:hive2://10.18.222.231:23040> select empname,
> sum(utilization) as totalutil from fact7 group by empname having totalutil
> >20;
> Error: org.apache.spark.sql.AnalysisException: cannot resolve '`totalutil`'
> given input columns: [empname, sum(utilization)]; line 8 pos 3;
> 'Project [UDF:preAgg() AS preAgg#1613, 'gen_subquery_0.empname, 'totalutil]
> +- 'Filter ('totalutil > 20)
> +- SubqueryAlias gen_subquery_0
> +- Aggregate [empname#1614], [empname#1614, sum(cast(utilization#1625 as
> bigint)) AS sum(utilization)#1612L]
> +- SubqueryAlias fact7
> +-
> Relation[empname#1614,designation#1615,doj#1616,workgroupcategory#1617,workgroupcategoryname#1618,deptno#1619,deptname#1620,projectcode#1621,projectjoindate#1622,projectenddate#1623,attendance#1624,utilization#1625,salary#1626]
> CarbonDatasourceHadoopRelation [ Database name :babu, Table name :fact7,
> Schema :Some(StructType(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))) ] (state=,code=0)
> 0: jdbc:hive2://10.18.222.231:23040>
>
>
> Normall this Query is working fine ( create another table without MV and
> tried) fact5 table does not have MV
> 0: jdbc:hive2://10.18.222.231:23040> select empname, sum(utilization) as
> totalutil from *fact5* group by empname having totalutil >20;
> +----------+------------+--+
> | empname | totalutil |
> +----------+------------+--+
> +----------+------------+--+
> No rows selected (9.97 seconds)
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
