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:
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);
There is same data in all table as follows:
Unable to find source-code formatter for language: xm. Available languages are: actionscript, ada, applescript, bash, c, c#, c++, cpp, css, erlang, go, groovy, haskell, html, java, javascript, js, json, lua, none, nyan, objc, perl, php, python, r, rainbow, ruby, scala, sh, sql, swift, visualbasic, xml, yaml
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
Following queries have to print same result as follows:
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');
But above queries print result data differently.
First and second query print result as follows:
id, name, score, type ------------------------------- 1, name1-1, 1.1, a
But last query print result as follows:
id, name, score, type ------------------------------- 1, name1-1, 1.1, a 1, name1-1, 1.1, a