[
https://issues.apache.org/jira/browse/CARBONDATA-2539?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Prasanna Ravichandran updated CARBONDATA-2539:
----------------------------------------------
Attachment: data.csv
> MV Dataset - Subqueries is not accessing the data from the MV datamap.
> ----------------------------------------------------------------------
>
> Key: CARBONDATA-2539
> URL: https://issues.apache.org/jira/browse/CARBONDATA-2539
> Project: CarbonData
> Issue Type: Bug
> Components: data-query
> Environment: 3 node opensource ANT cluster.
> Reporter: Prasanna Ravichandran
> Priority: Minor
> Attachments: data.csv
>
>
> Inner subquery is not accessing the data from the MV datamap. It is accessing
> the data from the main table.
> Test queries - Spark shell:
> scala> carbon.sql("drop table if exists origintable").show()
> ++
> ||
> ++
> ++
> scala> carbon.sql("CREATE TABLE originTable (empno int, 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'").show(200,false)
> ++
> ||
> ++
> ++
> scala> carbon.sql("LOAD DATA local inpath
> 'hdfs://hacluster/user/prasanna/data.csv' INTO TABLE originTable
> OPTIONS('DELIMITER'= ',', 'QUOTECHAR'=
> '\"','timestampformat'='dd-MM-yyyy')").show(200,false)
> ++
> ||
> ++
> ++
>
> scala> carbon.sql("drop datamap datamap_subqry").show(200,false)
> ++
> ||
> ++
> ++
> scala> carbon.sql("create datamap datamap_subqry using 'mv' as select
> min(salary) from originTable group by empno").show(200,false)
> ++
> ||
> ++
> ++
> scala> carbon.sql("explain SELECT max(empno) FROM originTable WHERE salary IN
> (select min(salary) from originTable group by empno ) group by
> empname").show(200,false)
> +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
> |plan |
> +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
> |== CarbonData Profiler ==
> Table Scan on origintable
> - total blocklets: 2
> - filter: none
> - pruned by Main DataMap
> - skipped blocklets: 0
> Table Scan on origintable
> - total blocklets: 2
> - filter: none
> - pruned by Main DataMap
> - skipped blocklets: 0
> |
> |== Physical Plan ==
> *HashAggregate(keys=[empname#2132], functions=[max(empno#2131)])
> +- Exchange hashpartitioning(empname#2132, 200)
> +- *HashAggregate(keys=[empname#2132], functions=[partial_max(empno#2131)])
> +- *Project [empno#2131, empname#2132]
> +- *BroadcastHashJoin [salary#2144], [*min(salary*)#2219], LeftSemi,
> BuildRight
> :- *BatchedScan CarbonDatasourceHadoopRelation [ *Database name :default,
> Table name :origintable*, Schema
> :Some(StructType(StructField(empno,IntegerType,true),
> 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))) ]
> default.origintable[empno#2131,empname#2132,designation#2133,doj#2134,workgroupcategory#2135,workgroupcategoryname#2136,deptno#2137,deptname#2138,projectcode#2139,projectjoindate#2140,projectenddate#2141,attendance#2142,utilization#2143,salary#2144]
> +- BroadcastExchange HashedRelationBroadcastMode(List(cast(input[0, int,
> true] as bigint)))
> +- *HashAggregate(keys=[empno#2131], functions=[min(salary#2144)])
> +- Exchange hashpartitioning(empno#2131, 200)
> +- *HashAggregate(keys=[empno#2131], functions=[partial_min(salary#2144)])
> +- *BatchedScan CarbonDatasourceHadoopRelation [ Database name :default,
> Table name :origintable, Schema
> :Some(StructType(StructField(empno,IntegerType,true),
> 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))) ]
> default.origintable[empno#2131,salary#2144]|
> +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
