Re: [Postgres-xc-bugs] Fix a bug about distributing table by varchar column, please review!

[Koichi S. <koi...@gm...>]

Thanks Zhang for the patch.

It looks reasonable to me.

Ashutosh, your thoughts?

Regards;
---
Koichi Suzuki


2014-06-19 19:06 GMT+09:00 ZhangJulian <jul...@ou...>:
> Hi All,
>
> The error is as following:
> ================================
> postgres=# create table t1(c1 varchar(10));
> CREATE TABLE
> postgres=# create table t2(c1 varchar(10));
> CREATE TABLE
> postgres=#
> postgres=#
> postgres=# explain verbose select * from t1,t2 where t1.c1=t2.c1;
>                                            QUERY PLAN
> -------------------------------------------------------------------------------------------------
>  Hash Join  (cost=0.12..0.26 rows=10 width=76)
>    Output: t1.c1, t2.c1
>    Hash Cond: ((t1.c1)::text = (t2.c1)::text)
>    ->  Data Node Scan on t1 "_REMOTE_TABLE_QUERY_"  (cost=0.00..0.00
> rows=1000 width=38)
>          Output: t1.c1
>          Node/s: datanode1, datanode2
>          Remote query: SELECT c1 FROM ONLY public.t1 WHERE true
>    ->  Hash  (cost=0.00..0.00 rows=1000 width=38)
>          Output: t2.c1
>          ->  Data Node Scan on t2 "_REMOTE_TABLE_QUERY__1"  (cost=0.00..0.00
> rows=1000 width=38)
>                Output: t2.c1
>                Node/s: datanode1, datanode2
>                Remote query: SELECT c1 FROM ONLY public.t2 WHERE true
> (13 rows)
> =====================================
>
> If two sides of a join are of VARCHAR type, even they are distribute keys of
> both table, the JOIN still can not been pushed to Datanode.
> I tried to fix it as the appended patch, please advise if it is correct!
>
> After applying the patch:
> =====================================
> postgres=# explain verbose select * from t1,t2 where t1.c1=t2.c1;
>                                              QUERY PLAN
> -----------------------------------------------------------------------------------------------------
>  Data Node Scan on "__REMOTE_FQS_QUERY__"  (cost=0.00..0.00 rows=0 width=0)
>    Output: t1.c1, t2.c1
>    Node/s: datanode1, datanode2
>    Remote query: SELECT t1.c1, t2.c1 FROM public.t1, public.t2 WHERE
> ((t1.c1)::text = (t2.c1)::text)
> (4 rows)
> ========================================
>
> Thanks
> Julian
>
>
> ------------------------------------------------------------------------------
> HPCC Systems Open Source Big Data Platform from LexisNexis Risk Solutions
> Find What Matters Most in Your Big Data with HPCC Systems
> Open Source. Fast. Scalable. Simple. Ideal for Dirty Data.
> Leverages Graph Analysis for Fast Processing & Easy Data Exploration
> http://p.sf.net/sfu/hpccsystems
> _______________________________________________
> Postgres-xc-bugs mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-bugs
>