[
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)
