[jira] [Updated] (TAJO-968) Self-Join query (including partitioned table) doesn't run unexpectedly using auto broad cast join.

["Jaehwa Jung (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/TAJO-968?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Jaehwa Jung updated TAJO-968:
-----------------------------

    Description: 
I found that self-join (including partitioned table) didn't run against my expectation using auto broad cast join.
For example, I created three tables as follows:
{code:xml}
create table table1 (id int, name text, score float, type text) 
using csv with ('csvfile.delimiter'='|') ;

create table table2 (id int, name text, score float) 
using csv with ('csvfile.delimiter'='|') partition by column(type text);

create table table3 (id int, name text, score float) 
using csv with ('csvfile.delimiter'='|') partition by column(type text);
{code}

There is same data in all table as follows:
{code:xm}
1|name1-1|1.1|a
2|name1-2|2.3|b
3|name1-3|3.4|c
4|name1-4|4.5|d
5|name1-5|5.6|e
{code}

Following queries have to print same result as follows:
{code:xml}
select a.* from table1 a join table1 b on a.type = b.type where a.type in ('a');

 select a.* from table2 a join table3 b on a.type = b.type where a.type in ('a');

select a.* from table2 a join table2 b on a.type = b.type where a.type in ('a');
{code}

But above queries print result data differently.
First and second query print result as follows:
{code:xml}
id,  name,  score,  type
-------------------------------
1,  name1-1,  1.1,  a
{code} 

But last query print result as follows:
{code:xml}
id,  name,  score,  type
-------------------------------
1,  name1-1,  1.1,  a
1,  name1-1,  1.1,  a
{code}


  was:
I found that self-join didn't run against my expectation in column partition.
For example, I created three tables as follows:
{code:xml}
create table table1 (id int, name text, score float, type text) 
using csv with ('csvfile.delimiter'='|') ;

create table table2 (id int, name text, score float) 
using csv with ('csvfile.delimiter'='|') partition by column(type text);

create table table3 (id int, name text, score float) 
using csv with ('csvfile.delimiter'='|') partition by column(type text);
{code}

There is same data in all table as follows:
{code:xm}
1|name1-1|1.1|a
2|name1-2|2.3|b
3|name1-3|3.4|c
4|name1-4|4.5|d
5|name1-5|5.6|e
{code}

Following queries have to print same result as follows:
{code:xml}
select a.* from table1 a join table1 b on a.type = b.type where a.type in ('a');

 select a.* from table2 a join table3 b on a.type = b.type where a.type in ('a');

select a.* from table2 a join table2 b on a.type = b.type where a.type in ('a');
{code}

But above queries print result data differently.
First and second query print result as follows:
{code:xml}
id,  name,  score,  type
-------------------------------
1,  name1-1,  1.1,  a
{code} 

But last query print result as follows:
{code:xml}
id,  name,  score,  type
-------------------------------
1,  name1-1,  1.1,  a
1,  name1-1,  1.1,  a
{code}



> Self-Join query (including partitioned table) doesn't run unexpectedly using auto broad cast join.
> --------------------------------------------------------------------------------------------------
>
>                 Key: TAJO-968
>                 URL: https://issues.apache.org/jira/browse/TAJO-968
>             Project: Tajo
>          Issue Type: Bug
>          Components: physical operator, planner/optimizer
>            Reporter: Jaehwa Jung
>            Assignee: Jaehwa Jung
>
> I found that self-join (including partitioned table) didn't run against my expectation using auto broad cast join.
> For example, I created three tables as follows:
> {code:xml}
> create table table1 (id int, name text, score float, type text) 
> using csv with ('csvfile.delimiter'='|') ;
> create table table2 (id int, name text, score float) 
> using csv with ('csvfile.delimiter'='|') partition by column(type text);
> create table table3 (id int, name text, score float) 
> using csv with ('csvfile.delimiter'='|') partition by column(type text);
> {code}
> There is same data in all table as follows:
> {code:xm}
> 1|name1-1|1.1|a
> 2|name1-2|2.3|b
> 3|name1-3|3.4|c
> 4|name1-4|4.5|d
> 5|name1-5|5.6|e
> {code}
> Following queries have to print same result as follows:
> {code:xml}
> select a.* from table1 a join table1 b on a.type = b.type where a.type in ('a');
>  select a.* from table2 a join table3 b on a.type = b.type where a.type in ('a');
> select a.* from table2 a join table2 b on a.type = b.type where a.type in ('a');
> {code}
> But above queries print result data differently.
> First and second query print result as follows:
> {code:xml}
> id,  name,  score,  type
> -------------------------------
> 1,  name1-1,  1.1,  a
> {code} 
> But last query print result as follows:
> {code:xml}
> id,  name,  score,  type
> -------------------------------
> 1,  name1-1,  1.1,  a
> 1,  name1-1,  1.1,  a
> {code}



--
This message was sent by Atlassian JIRA
(v6.2#6252)