You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@drill.apache.org by "Aman Sinha (JIRA)" <ji...@apache.org> on 2015/07/02 22:53:04 UTC
[jira] [Updated] (DRILL-3330) CanNoPlanException during query
planning for join
[ https://issues.apache.org/jira/browse/DRILL-3330?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Aman Sinha updated DRILL-3330:
------------------------------
Description:
I have created two views from my sample data as follows
Sample Data
rsingh@Administrators-MacBook-Pro-4 ~/Downloads/apache-drill-1.0.0$ cat sample-data/master-data.csv
{code}
1,1,20.4.09,201,Orci Donec Nibh PC,1,tellus.eu.augue@congueelitsed.co.uk,200,Dai Woodward,300,Hannah Sims,400,Abdul
{code}
{code}
CREATE VIEW dfs.tmp.`fact_a` AS select distinct CAST(columns[0] as INTEGER) as b_id, CAST(columns[1] as INTEGER) as c_id, CAST(columns[2] as DATE) as a_date, CAST(columns[3] AS INTEGER) as a_value from dfs.root.`/Users/rsingh/Downloads/apache-drill-1.0.0/sample-data/master-data.csv` order by b_id, c_id, a_date;
CREATE VIEW dfs.tmp.`dim_c` AS select CAST(columns[1] as INTEGER) as c_id, CAST(columns[12] as VARCHAR) as c_desc, CAST(columns[11] as INTEGER) as c4_id, CAST(columns[10] as VARCHAR) as c4_desc, CAST(columns[9] as INTEGER) as c3_id,
CAST(columns[7] as INTEGER) as c2_id, CAST(columns[5] as INTEGER) as c1_id from dfs.root.`/Users/rsingh/Downloads/apache-drill-1.0.0/sample-data/master-data.csv` order
by c_id, c4_id, c3_id, c2_id, c1_id;
{code}
while trying to join the views getting CanNotPlanException(huge trace can not paste here)
join query:
{code}
select a11.c_id c_id,
max(a12.c_desc) c_desc,
max(a12.c4_desc) c4_desc,
sum(a11.a_value) a_value
from dfs.tmp.`fact_a` a11
join dfs.tmp.`dim_c` a12
on (a11.c_id =a12.c_id)
group by a11.c_id;
{code}
was:
I have created two views from my sample data as follows
Sample Data
rsingh@Administrators-MacBook-Pro-4 ~/Downloads/apache-drill-1.0.0$ cat sample-data/master-data.csv
1,1,20.4.09,201,Orci Donec Nibh PC,1,tellus.eu.augue@congueelitsed.co.uk,200,Dai Woodward,300,Hannah Sims,400,Abdul
CREATE VIEW dfs.tmp.`fact_a` AS select distinct CAST(columns[0] as INTEGER) as b_id, CAST(columns[1] as INTEGER) as c_id, CAST(columns[2] as DATE) as a_date, CAST(columns[3] AS INTEGER) as a_value from dfs.root.`/Users/rsingh/Downloads/apache-drill-1.0.0/sample-data/master-data.csv` order by b_id, c_id, a_date;
CREATE VIEW dfs.tmp.`dim_c` AS select CAST(columns[1] as INTEGER) as c_id, CAST(columns[12] as VARCHAR) as c_desc, CAST(columns[11] as INTEGER) as c4_id, CAST(columns[10] as VARCHAR) as c4_desc, CAST(columns[9] as INTEGER) as c3_id,
CAST(columns[7] as INTEGER) as c2_id, CAST(columns[5] as INTEGER) as c1_id from dfs.root.`/Users/rsingh/Downloads/apache-drill-1.0.0/sample-data/master-data.csv` order
by c_id, c4_id, c3_id, c2_id, c1_id;
while trying to join the views getting CanNotPlanException(huge trace can not paste here)
join query
select a11.c_id c_id,
max(a12.c_desc) c_desc,
max(a12.c4_desc) c4_desc,
sum(a11.a_value) a_value
from dfs.tmp.`fact_a` a11
join dfs.tmp.`dim_c` a12
on (a11.c_id =a12.c_id)
group by a11.c_id;
> CanNoPlanException during query planning for join
> -------------------------------------------------
>
> Key: DRILL-3330
> URL: https://issues.apache.org/jira/browse/DRILL-3330
> Project: Apache Drill
> Issue Type: Bug
> Components: Query Planning & Optimization
> Affects Versions: 1.0.0
> Environment: Drill 1.0.0
> Reporter: Rajkumar Singh
> Assignee: Jinfeng Ni
> Fix For: 1.2.0
>
>
> I have created two views from my sample data as follows
> Sample Data
> rsingh@Administrators-MacBook-Pro-4 ~/Downloads/apache-drill-1.0.0$ cat sample-data/master-data.csv
> {code}
> 1,1,20.4.09,201,Orci Donec Nibh PC,1,tellus.eu.augue@congueelitsed.co.uk,200,Dai Woodward,300,Hannah Sims,400,Abdul
> {code}
> {code}
> CREATE VIEW dfs.tmp.`fact_a` AS select distinct CAST(columns[0] as INTEGER) as b_id, CAST(columns[1] as INTEGER) as c_id, CAST(columns[2] as DATE) as a_date, CAST(columns[3] AS INTEGER) as a_value from dfs.root.`/Users/rsingh/Downloads/apache-drill-1.0.0/sample-data/master-data.csv` order by b_id, c_id, a_date;
> CREATE VIEW dfs.tmp.`dim_c` AS select CAST(columns[1] as INTEGER) as c_id, CAST(columns[12] as VARCHAR) as c_desc, CAST(columns[11] as INTEGER) as c4_id, CAST(columns[10] as VARCHAR) as c4_desc, CAST(columns[9] as INTEGER) as c3_id,
> CAST(columns[7] as INTEGER) as c2_id, CAST(columns[5] as INTEGER) as c1_id from dfs.root.`/Users/rsingh/Downloads/apache-drill-1.0.0/sample-data/master-data.csv` order
> by c_id, c4_id, c3_id, c2_id, c1_id;
> {code}
> while trying to join the views getting CanNotPlanException(huge trace can not paste here)
> join query:
> {code}
> select a11.c_id c_id,
> max(a12.c_desc) c_desc,
> max(a12.c4_desc) c4_desc,
> sum(a11.a_value) a_value
> from dfs.tmp.`fact_a` a11
> join dfs.tmp.`dim_c` a12
> on (a11.c_id =a12.c_id)
> group by a11.c_id;
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)