phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From 彭昶勳 <cxpeng...@gmail.com>
Subject Re: Join not returning any results in Phoenix
Date Thu, 19 Mar 2015 11:31:24 GMT
Hi Matt,

No, the two plans are a little different.
Two queries and their explain plan are as following

*This query get no results*
select * from myTableB b join myTableA a on b.telnum = a.chargedpartyid
where a.date between b.startdate and b.termdate

CLIENT PARALLEL 3-WAY FULL SCAN OVER MYTABLEB
CLIENT MERGE SORT
     PARALLEL INNER-JOIN TABLE 0
         CLIENT PARALLEL 3-WAY FULL SCAN OVER MYTABLEA
         CLIENT MERGE SORT
     AFTER-JOIN SERVER FILTER BY (B.STARTDATE <= A.DATE AND A.DATE <=
B.TERMDATE)

*This query get correct results*
select * from myTableA a join myTableB b on b.telnum = a.chargedpartyid
where a.date between b.startdate and b.termdate

CLIENT PARALLEL 3-WAY FULL SCAN OVER MYTABLEA
CLIENT MERGE SORT
     PARALLEL INNER-JOIN TABLE 0
         CLIENT PARALLEL 3-WAY FULL SCAN OVER MYTABLEB
         CLIENT MERGE SORT
     AFTER-JOIN SERVER FILTER BY (B.STARTDATE <= A.DATE AND A.DATE <=
B.TERMDATE)

I use two way to run queries (SqlLine & Write Java code with phoenix api)
and get the same results.
I didn't set any row limit and I don't think they have limit row feature.

thanks,
Allen

2015-03-19 18:29 GMT+08:00 Matthew Johnson <matt.johnson@algomi.com>:

> Hi Allen,
>
>
>
> Is the explain plan the same when you do the join the other way around
> (i.e. A join B vs B join A)? What tool are you using to run this query, and
> does it have a ‘Limit Rows’ feature?
>
>
>
> Cheers,
>
> Matt
>
>
>
> *From:* 彭昶勳 [mailto:cxpeng111@gmail.com]
> *Sent:* 19 March 2015 09:17
> *To:* user@phoenix.apache.org
> *Subject:* Re: Join not returning any results in Phoenix
>
>
>
> Hi Sun,
>
>
>
> There is no any error or exception for my query without result.
>
>
>
> The query and the explain plan are as following
>
>
>
> select * from myTableB b join myTableA a on b.telnum = a.chargedpartyid
>
> where a.date between b.startdate and t.termdate
>
>
>
> CLIENT PARALLEL 3-WAY FULL SCAN OVER MYTABLEB
>
> CLIENT MERGE SORT
>
>      PARALLEL INNER-JOIN TABLE 0
>
>          CLIENT PARALLEL 3-WAY FULL SCAN OVER MYTABLEA
>
>          CLIENT MERGE SORT
>
>      AFTER-JOIN SERVER FILTER BY (B.STARTDATE <= A.DATE AND A.DATE <=
> B.TERMDATE)
>
>
>
> thanks,
>
> Allen
>
>
>
> 2015-03-19 16:58 GMT+08:00 Fulin Sun <sunfl@certusnet.com.cn>:
>
> Hi,
>
>
>
> Did you see any error or exception info for your join query without
> results?
>
>
>
> Can you paste your explain plan results for the query?
>
>
>
> Thanks,
>
> Sun.
>
>
> ------------------------------
> ------------------------------
>
> *Certus**Net*
>
>
>
> *From:* 彭昶勳 <cxpeng111@gmail.com>
>
> *Date:* 2015-03-19 16:42
>
> *To:* user <user@phoenix.apache.org>
>
> *Subject:* Join not returning any results in Phoenix
>
> Hi guys,
>
>
>
> I encountered some problems when doing Join queries with Phoenix. I can
> get correct answer from simple query,
>
> but some Join queries return no results.
>
>
>
> I can get correct result by the following query
>
>
>
> select * from myTableA a join myTableB b on b.telnum = a.chargedpartyid
>
> where a.date between b.startdate and t.termdate
>
>
>
> but I get no result if I try
>
>
>
> select * from myTableB b join myTableA a on b.telnum = a.chargedpartyid
>
> where a.date between b.startdate and t.termdate
>
>
>
>
>
> And I can get correct result from both following queries:
>
>
>
> select count(*) from myTableB b join myTableA a on b.telnum =
> a.chargedpartyid
>
> where a.date between b.startdate and t.termdate
>
>
>
> select count(*) from myTableA a join myTableB b on b.telnum =
> a.chargedpartyid
>
> where a.date between b.startdate and t.termdate
>
>
>
> I have tried different size of my tables(10 rows, 1krow, 10k rows)
>
> and the results are all the same.
>
>
>
> Many thanks for any suggestions
>
>
>

Mime
View raw message