You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@carbondata.apache.org by "Babulal (JIRA)" <ji...@apache.org> on 2018/05/25 09:05:00 UTC
[jira] [Created] (CARBONDATA-2535) [MV] Query Failed in MV datamap
when Having is used in query.
Babulal created CARBONDATA-2535:
-----------------------------------
Summary: [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
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)