[jira] [Commented] (CARBONDATA-2565) [MV] Join Query Failed with MV data map

[Akash R Nilugal (Jira)]

    [ https://issues.apache.org/jira/browse/CARBONDATA-2565?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16524458#comment-16524458 ]

xubo245 commented on CARBONDATA-2565:
-------------------------------------

It's work fine now in cluster:


{code:java}
0: jdbc:hive2://hadoop1:10000> explain select total from (select name ,sum(salray) as total from mvtest11 group by name) t1 join mvtest9_1 t2 on t1.name= t2.name_t;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+
|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         plan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+
| == CarbonData Profiler ==
Table Scan on mvtest11_mv_2_table
 - total blocklets: 1
 - filter: mvtest11_name <> null
 - pruned by Main DataMap
    - skipped blocklets: 0
Table Scan on mvtest9_1
 - total blocklets: 1
 - filter: name_t <> null
 - pruned by Main DataMap
    - skipped blocklets: 0
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
| == Physical Plan ==
*Project [total#1280L]
+- *BroadcastHashJoin [name#1279], [name_t#1188], Inner, BuildRight
   :- *HashAggregate(keys=[mvtest11_name#1234], functions=[sum(sum_salray#1235L)])
   :  +- Exchange hashpartitioning(mvtest11_name#1234, 200)
   :     +- *HashAggregate(keys=[mvtest11_name#1234], functions=[partial_sum(sum_salray#1235L)])
   :        +- *BatchedScan CarbonDatasourceHadoopRelation [ Database name :default, Table name :mvtest11_mv_2_table, Schema :Some(StructType(StructField(mvtest11_name,StringType,true), StructField(sum_salray,LongType,true))) ] default.mvtest11_mv_2_table[mvtest11_name#1234,sum_salray#1235L] PushedFilters: [IsNotNull(mvtest11_name)]
   +- BroadcastExchange HashedRelationBroadcastMode(List(input[0, string, true]))
      +- *BatchedScan CarbonDatasourceHadoopRelation [ Database name :default, Table name :mvtest9_1, Schema :Some(StructType(StructField(name_t,StringType,true), StructField(age_t,IntegerType,true), StructField(salary_t,IntegerType,true))) ] default.mvtest9_1[name_t#1188] PushedFilters: [IsNotNull(name_t)]  |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+
2 rows selected (0.68 seconds)
0: jdbc:hive2://hadoop1:10000>  select t2.* from (select name ,sum(salray) as total from mvtest11 group by name) t1 join mvtest9_1 t2 on t1.name= t2.name_t;
+---------+--------+-----------+--+
| name_t  | age_t  | salary_t  |
+---------+--------+-----------+--+
| name1   | 12     | 12        |
+---------+--------+-----------+--+
1 row selected (1.342 seconds)
0: jdbc:hive2://hadoop1:10000> select t1.* from (select name ,sum(salray) as total from mvtest11 group by name) t1 join mvtest9_1 t2 on t1.name= t2.name_t;
+--------+--------+--+
|  name  | total  |
+--------+--------+--+
| name1  | 12     |
+--------+--------+--+
1 row selected (0.647 seconds)
0: jdbc:hive2://hadoop1:10000>

{code}

> [MV] Join Query Failed with MV data map
> ----------------------------------------
>
>                 Key: CARBONDATA-2565
>                 URL: https://issues.apache.org/jira/browse/CARBONDATA-2565
>             Project: CarbonData
>          Issue Type: Bug
>            Reporter: Babulal
>            Assignee: xubo245
>            Priority: Major
>
> create table mvtest11 (name string,age int,salray int) stored by 'carbondata';
> create table mvtest9_1( name_t string,age_t int,salary_t int) stored by 'carbondata';
>  
> insert into mvtest11 select 'name1',12,12;
> insert into mvtest9_1 select 'name1',12,12;
> create datamap mvtest11_mv_2 using 'mv' as select name,sum(salray)  from mvtest11  group by name;
> rebuild datamap mvtest11_mv_2
>  
>  
>  
> 0: jdbc:hive2://10.18.222.231:23040> explain select total from (select name ,sum(salray) as total from mvtest11 group by name) t1 join mvtest9_1 t2 on t1.name= t2.name_t;
> Error: org.apache.spark.sql.AnalysisException: cannot resolve '`t1.total`' given input columns: [name_t, salary_t, age_t, name, sum(salray)]; line 1 pos 28;
> 'Project [UDF:preAgg() AS preAgg#6324, 't1.total]
> +- Join Inner, (name#5435 = name_t#2595)
>  :- SubqueryAlias gen_subquery_0
>  : +- Aggregate [name#5435], [name#5435, sum(cast(salray#5437 as bigint)) AS sum(salray)#6323L]
>  : +- SubqueryAlias mvtest11
>  : +- Relation[name#5435,age#5436,salray#5437] CarbonDatasourceHadoopRelation [ Database name :default, Table name :mvtest11, Schema :Some(StructType(StructField(name,StringType,true), StructField(age,IntegerType,true), StructField(salray,IntegerType,true))) ]
>  +- SubqueryAlias t2
>  +- SubqueryAlias mvtest9_1
>  +- Relation[name_t#2595,age_t#2596,salary_t#2597] CarbonDatasourceHadoopRelation [ Database name :default, Table name :mvtest9_1, Schema :Some(StructType(StructField(name_t,StringType,true), StructField(age_t,IntegerType,true), StructField(salary_t,IntegerType,true))) ] (state=,code=0)
>  
>  
> 0: jdbc:hive2://10.18.222.231:23040> select t2.* from (select name ,sum(salray) as total from mvtest11 group by name) t1 join mvtest9_1 t2 on t1.name= t2.name_t;
> +---------+--------+-----------+--+
> | name_t | age_t | salary_t |
> +---------+--------+-----------+--+
> +---------+--------+-----------+--+
> No rows selected (12.672 seconds)
> 0: jdbc:hive2://10.18.222.231:23040> select t1.* from (select name ,sum(salray) as total from mvtest11 group by name) t1 join mvtest9_1 t2 on t1.name= t2.name_t;
> Error: org.apache.spark.sql.AnalysisException: cannot resolve '`t1.total`' given input columns: [salary_t, name_t, sum(salray), name, age_t]; line 1 pos 51;
> 'Project [UDF:preAgg() AS preAgg#6511, name#6512, 't1.total]
> +- Join Inner, (name#6512 = name_t#6515)
>  :- SubqueryAlias gen_subquery_0
>  : +- Aggregate [name#6512], [name#6512, sum(cast(salray#6514 as bigint)) AS sum(salray)#6510L]
>  : +- SubqueryAlias mvtest11
>  : +- Relation[name#6512,age#6513,salray#6514] CarbonDatasourceHadoopRelation [ Database name :default, Table name :mvtest11, Schema :Some(StructType(StructField(name,StringType,true), StructField(age,IntegerType,true), StructField(salray,IntegerType,true))) ]
>  +- SubqueryAlias t2
>  +- SubqueryAlias mvtest9_1
>  +- Relation[name_t#6515,age_t#6516,salary_t#6517] CarbonDatasourceHadoopRelation [ Database name :default, Table name :mvtest9_1, Schema :Some(StructType(StructField(name_t,StringType,true), StructField(age_t,IntegerType,true), StructField(salary_t,IntegerType,true))) ] (state=,code=0)
> 0: jdbc:hive2://10.18.222.231:23040>
>  
>  

--
This message was sent by Atlassian JIRA
(v7.6.3#76005)