Please look at the plan generated by Drill:
explain plan with implementation for SELECT… (An idea is to test the generated
SQL in the plan in the DB to see what it does)
One idea is to try to put one of the tables in a sub select table where you
actively rename the column.
WITH s0 as (
SELECT
id as id_alias
, … /* all columns you want */
FROM SqlServer.StuentDetails.P21.Class1
)
SELECT
id_alias, id
FROM s0 as t1
RIGHT OUTER JOIN
SqlServer.StuentDetails.P21.Class2 as t2
ON T1.ProviderID = T2.ID
However, it may break pushdown optimization...
Regards,
Magnus
> 22 jun 2016 kl. 07:54 skrev SanjiV SwaraJ <sanjiv...@gmail.com>:
>
> I want to join two table using same storage plugin. But One Of the Column
> showing null value.I am using drill in window 8 system with latest version
> of drill(i.e;1.6) .
>
> I am using this query:-
>
> SELECT T2.ID <http://t2.id/> AS T_ID,T1.ID <http://t1.id/> AS T1_ID
> ,T1.ProviderID AS ProviderID,
>
> T1.Name AS Name, T2.Code AS Code
> FROM SqlServer.StuentDetails.P21.Class1 AS T1
> right outer JOIN SqlServer.StuentDetails.P21.Class2
> AS T2 ON T1.ProviderID = T2.ID <http://t2.id/>
>
> Here SqlServer is Storage Plugin Name,
>
> StuentDetails is Database Name,
>
> P21 is a Schema Name, Class1,
>
> Class2 are Table Names.
>
> While executing this query T_ID showing Null.
>
> But If I am using two different storage plugin name with same
> credential it works properly.
>
> Note:- NOT ONLY IN SQL SERVER, I TRIED IN MYSQL, ORACLE DB ALSO.. SAME
> PROBLEM CAME. I THINK PROBLEM WITH THE SAME STORAGE PLUGIN NAME HAVING
> SAME DATATYPE, BECAUSE WHEN I TRIED TO GIVE DIFFERENT DATATYPE TO BOTH
> THE TABLE, OUTPUT COMING FINE.
>
> *Is it possible to join two table using same storage plugin name? If
> yes,then What am I doing wrong in this query?*
