[CARBONDATA-2535] [MV] Query Failed in MV datamap when Having is used in query.

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Assignee: Bo Xu
• Reporter: Babulal
• Created: 25/May/18 09:04
• Updated: 09/Jul/18 06:21

Description

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_tablefact7_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
+- Relationempname#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)

 

Issue Links

• is a child of: CARBONDATA-2628 Fix MV defects