You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@carbondata.apache.org by "Ravindra Pesala (JIRA)" <ji...@apache.org> on 2018/07/09 06:22:00 UTC
[jira] [Commented] (CARBONDATA-2535) [MV] Query Failed in MV
datamap when Having is used in query.
[ https://issues.apache.org/jira/browse/CARBONDATA-2535?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16536565#comment-16536565 ]
Ravindra Pesala commented on CARBONDATA-2535:
---------------------------------------------
It is working in latest code, please close this issue
> [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)