[
https://issues.apache.org/jira/browse/PHOENIX-6235?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
wangMu updated PHOENIX-6235:
----------------------------
Description:
I executed the same statement in different ways and got different results
{code:java}
explain select /*+USE_SORT_MERGE_JOIN */
s.loanacno,s.term,s.paiddate,s.shouldprin,s.shouldinter,s.shouldfee,s.shouldprinpenalty,s.shouldinterpenalty,s.sotherkillamt,s.discoutinter,
r.realprin,r.realinter,r.realfee,r.realprinpenalty,r.realinterpenalty,r.otherkillamt,r.billdate,r.overdueflag,r.paidflag,r.channel,r.subssource,
d.paiddate,d.yd_smonthprofit,d.rd_sguafee,d.rd_sassushareamt,d.yd_rmonthprofit,d.rd_rguafee,d.rd_rassushareamt
from lake.yd_lake_plan_s s
left join lake.yd_lake_plan_r r on s.loanacno = r.loanacno and s.term = r.term
left join lake.yd_lake_plan_rd d on s.loanacno = d.loanacno and s.term = d.term
left join lake.yd_lake_loan l on s.loanacno = l.loanacno
where l.orgcode = '01G0' limit 10;
{code}
Squirrel client:
{noformat}
CLIENT 13-CHUNK 10 ROWS 1360 BYTES SERIA 943718400
4442183 1606324866213
SERVER FILTER BY FIRST KEY ONLY 943718400
4442183 1606324866213
SERVER 10 ROW LIMIT 943718400
4442183 1606324866213
CLIENT 10 ROW LIMIT 943718400
4442183 1606324866213
PARALLEL LEFT-JOIN TABLE 0 943718400
4442183 1606324866213
CLIENT 12-CHUNK 1895016 ROWS 314 943718400
4442183 1606324866213
SERVER FILTER BY FIRST KEY O 943718400
4442183 1606324866213
PARALLEL LEFT-JOIN TABLE 1 943718400
4442183 1606324866213
CLIENT 6-CHUNK 2496609 ROWS 3145 943718400
4442183 1606324866213
SERVER FILTER BY FIRST KEY O 943718400
4442183 1606324866213
PARALLEL LEFT-JOIN TABLE 2 943718400
4442183 1606324866213
CLIENT 24-CHUNK 50558 ROWS 31457 943718400
4442183 1606324866213
AFTER-JOIN SERVER FILTER BY L.ORGCOD 943718400
4442183 1606324866213
JOIN-SCANNER 10 ROW LIMIT 943718400
4442183 1606324866213 {noformat}
shell sqlline:
{noformat}
SORT-MERGE-JOIN (LEFT) TABLES
1258291200 6755218
1606324866213
SORT-MERGE-JOIN (LEFT) TABLES
1258291200 6755218
1606324866213
SORT-MERGE-JOIN (LEFT) TABLES
1258291200 6755218
1606324866213
CLIENT 13-CHUNK 2313035 ROWS 314572800 BYTES PARALLEL 13-WAY FULL
SCAN OVER LAKE:YD_LAKE_PLAN_S_INDEX 1258291200 6755218
1606324866213
SERVER FILTER BY FIRST KEY ONLY
1258291200 6755218
1606324866213
CLIENT MERGE SORT
1258291200 6755218
1606324866213
AND
1258291200 6755218
1606324866213
CLIENT 12-CHUNK 1895016 ROWS 314572800 BYTES PARALLEL 12-WAY FULL
SCAN OVER LAKE:YD_LAKE_PLAN_R_INDEX 1258291200 6755218
1606324866213
SERVER FILTER BY FIRST KEY ONLY
1258291200 6755218
1606324866213
CLIENT MERGE SORT
1258291200 6755218
1606324866213
AND
1258291200 6755218
1606324866213
CLIENT 6-CHUNK 2496609 ROWS 314572800 BYTES PARALLEL 6-WAY FULL SCAN
OVER LAKE:YD_LAKE_PLAN_RD_INDEX 1258291200 6755218
1606324866213
SERVER FILTER BY FIRST KEY ONLY
1258291200 6755218
1606324866213
CLIENT MERGE SORT
1258291200 6755218
1606324866213
AND
1258291200 6755218
1606324866213
CLIENT 24-CHUNK 50558 ROWS 314572800 BYTES PARALLEL 24-WAY FULL SCAN OVER
LAKE:YD_LAKE_LOAN 1258291200 6755218
1606324866213
CLIENT MERGE SORT
1258291200 6755218
1606324866213
CLIENT FILTER BY L.ORGCODE = '01G0'
1258291200 6755218
1606324866213
CLIENT 10 ROW LIMIT
1258291200 6755218
1606324866213
{noformat}
Squirrel used query server connection.I guess the forced index was unexpectedly
escaped during the serialization pass, causing the query statement to become a
normal query.
was:
I executed the same statement in different ways and got different results
{code:java}
explain select /*+USE_SORT_MERGE_JOIN */
s.loanacno,s.term,s.paiddate,s.shouldprin,s.shouldinter,s.shouldfee,s.shouldprinpenalty,s.shouldinterpenalty,s.sotherkillamt,s.discoutinter,
r.realprin,r.realinter,r.realfee,r.realprinpenalty,r.realinterpenalty,r.otherkillamt,r.billdate,r.overdueflag,r.paidflag,r.channel,r.subssource,
d.paiddate,d.yd_smonthprofit,d.rd_sguafee,d.rd_sassushareamt,d.yd_rmonthprofit,d.rd_rguafee,d.rd_rassushareamt
from lake.yd_lake_plan_s s
left join lake.yd_lake_plan_r r on s.loanacno = r.loanacno and s.term = r.term
left join lake.yd_lake_plan_rd d on s.loanacno = d.loanacno and s.term = d.term
left join lake.yd_lake_loan l on s.loanacno = l.loanacno
where l.orgcode = '01G0' limit 10;
{code}
Squirrel client:
{noformat}
CLIENT 13-CHUNK 10 ROWS 1360 BYTES SERIA 943718400
4442183 1606324866213
SERVER FILTER BY FIRST KEY ONLY 943718400
4442183 1606324866213
SERVER 10 ROW LIMIT 943718400
4442183 1606324866213
CLIENT 10 ROW LIMIT 943718400
4442183 1606324866213
PARALLEL LEFT-JOIN TABLE 0 943718400
4442183 1606324866213
CLIENT 12-CHUNK 1895016 ROWS 314 943718400
4442183 1606324866213
SERVER FILTER BY FIRST KEY O 943718400
4442183 1606324866213
PARALLEL LEFT-JOIN TABLE 1 943718400
4442183 1606324866213
CLIENT 6-CHUNK 2496609 ROWS 3145 943718400
4442183 1606324866213
SERVER FILTER BY FIRST KEY O 943718400
4442183 1606324866213
PARALLEL LEFT-JOIN TABLE 2 943718400
4442183 1606324866213
CLIENT 24-CHUNK 50558 ROWS 31457 943718400
4442183 1606324866213
AFTER-JOIN SERVER FILTER BY L.ORGCOD 943718400
4442183 1606324866213
JOIN-SCANNER 10 ROW LIMIT 943718400
4442183 1606324866213 {noformat}
shell sqlline:
{noformat}
SORT-MERGE-JOIN (LEFT) TABLES 1258291200 6755218 1606324866213
SORT-MERGE-JOIN (LEFT) TABLES 1258291200 6755218 1606324866213
SORT-MERGE-JOIN (LEFT) TABLES 1258291200 6755218 1606324866213
CLIENT 13-CHUNK 2313035 ROWS 314572800 BYTES PARALLEL 13-WAY FULL SCAN OVER
LAKE:YD_LAKE_PLAN_S_INDEX 1258291200 6755218 1606324866213
SERVER FILTER BY FIRST KEY ONLY 1258291200 6755218 1606324866213
CLIENT MERGE SORT 1258291200 6755218 1606324866213
AND 1258291200 6755218 1606324866213
CLIENT 12-CHUNK 1895016 ROWS 314572800 BYTES PARALLEL 12-WAY FULL SCAN OVER
LAKE:YD_LAKE_PLAN_R_INDEX 1258291200 6755218 1606324866213
SERVER FILTER BY FIRST KEY ONLY 1258291200 6755218 1606324866213
CLIENT MERGE SORT 1258291200 6755218 1606324866213
AND 1258291200 6755218 1606324866213
CLIENT 6-CHUNK 2496609 ROWS 314572800 BYTES PARALLEL 6-WAY FULL SCAN OVER
LAKE:YD_LAKE_PLAN_RD_INDEX 1258291200 6755218 1606324866213
SERVER FILTER BY FIRST KEY ONLY 1258291200 6755218 1606324866213
CLIENT MERGE SORT 1258291200 6755218 1606324866213
AND 1258291200 6755218 1606324866213
CLIENT 24-CHUNK 50558 ROWS 314572800 BYTES PARALLEL 24-WAY FULL SCAN OVER
LAKE:YD_LAKE_LOAN 1258291200 6755218 1606324866213
CLIENT MERGE SORT 1258291200 6755218 1606324866213
CLIENT FILTER BY L.ORGCODE = '01G0' 1258291200 6755218 1606324866213
CLIENT 10 ROW LIMIT 1258291200 6755218 1606324866213{noformat}
Squirrel used query server connection.I guess the forced index was unexpectedly
escaped during the serialization pass, causing the query statement to become a
normal query.
> Using the Query Server automatically filters the forced index
> -------------------------------------------------------------
>
> Key: PHOENIX-6235
> URL: https://issues.apache.org/jira/browse/PHOENIX-6235
> Project: Phoenix
> Issue Type: Bug
> Components: queryserver
> Environment: SQuirreL SQL Client 4.0.0
> Phoenix 5.0
> sqlline version 1.2.0
> Reporter: wangMu
> Priority: Major
>
>
> I executed the same statement in different ways and got different results
> {code:java}
> explain select /*+USE_SORT_MERGE_JOIN */
> s.loanacno,s.term,s.paiddate,s.shouldprin,s.shouldinter,s.shouldfee,s.shouldprinpenalty,s.shouldinterpenalty,s.sotherkillamt,s.discoutinter,
> r.realprin,r.realinter,r.realfee,r.realprinpenalty,r.realinterpenalty,r.otherkillamt,r.billdate,r.overdueflag,r.paidflag,r.channel,r.subssource,
> d.paiddate,d.yd_smonthprofit,d.rd_sguafee,d.rd_sassushareamt,d.yd_rmonthprofit,d.rd_rguafee,d.rd_rassushareamt
> from lake.yd_lake_plan_s s
> left join lake.yd_lake_plan_r r on s.loanacno = r.loanacno and s.term = r.term
> left join lake.yd_lake_plan_rd d on s.loanacno = d.loanacno and s.term =
> d.term
> left join lake.yd_lake_loan l on s.loanacno = l.loanacno
> where l.orgcode = '01G0' limit 10;
> {code}
> Squirrel client:
> {noformat}
> CLIENT 13-CHUNK 10 ROWS 1360 BYTES SERIA 943718400
> 4442183 1606324866213
>
> SERVER FILTER BY FIRST KEY ONLY 943718400
> 4442183 1606324866213
>
> SERVER 10 ROW LIMIT 943718400
> 4442183 1606324866213
>
> CLIENT 10 ROW LIMIT 943718400
> 4442183 1606324866213
>
> PARALLEL LEFT-JOIN TABLE 0 943718400
> 4442183 1606324866213
>
> CLIENT 12-CHUNK 1895016 ROWS 314 943718400
> 4442183 1606324866213
>
> SERVER FILTER BY FIRST KEY O 943718400
> 4442183 1606324866213
>
> PARALLEL LEFT-JOIN TABLE 1 943718400
> 4442183 1606324866213
>
> CLIENT 6-CHUNK 2496609 ROWS 3145 943718400
> 4442183 1606324866213
>
> SERVER FILTER BY FIRST KEY O 943718400
> 4442183 1606324866213
>
> PARALLEL LEFT-JOIN TABLE 2 943718400
> 4442183 1606324866213
>
> CLIENT 24-CHUNK 50558 ROWS 31457 943718400
> 4442183 1606324866213
>
> AFTER-JOIN SERVER FILTER BY L.ORGCOD 943718400
> 4442183 1606324866213
>
> JOIN-SCANNER 10 ROW LIMIT 943718400
> 4442183 1606324866213 {noformat}
>
> shell sqlline:
> {noformat}
> SORT-MERGE-JOIN (LEFT) TABLES
> 1258291200 6755218
> 1606324866213
> SORT-MERGE-JOIN (LEFT) TABLES
> 1258291200 6755218
> 1606324866213
> SORT-MERGE-JOIN (LEFT) TABLES
> 1258291200 6755218
> 1606324866213
> CLIENT 13-CHUNK 2313035 ROWS 314572800 BYTES PARALLEL 13-WAY
> FULL SCAN OVER LAKE:YD_LAKE_PLAN_S_INDEX 1258291200 6755218
> 1606324866213
> SERVER FILTER BY FIRST KEY ONLY
> 1258291200 6755218
> 1606324866213
> CLIENT MERGE SORT
> 1258291200 6755218
> 1606324866213
> AND
> 1258291200 6755218
> 1606324866213
> CLIENT 12-CHUNK 1895016 ROWS 314572800 BYTES PARALLEL 12-WAY
> FULL SCAN OVER LAKE:YD_LAKE_PLAN_R_INDEX 1258291200 6755218
> 1606324866213
> SERVER FILTER BY FIRST KEY ONLY
> 1258291200 6755218
> 1606324866213
> CLIENT MERGE SORT
> 1258291200 6755218
> 1606324866213
> AND
> 1258291200 6755218
> 1606324866213
> CLIENT 6-CHUNK 2496609 ROWS 314572800 BYTES PARALLEL 6-WAY FULL SCAN
> OVER LAKE:YD_LAKE_PLAN_RD_INDEX 1258291200 6755218
> 1606324866213
> SERVER FILTER BY FIRST KEY ONLY
> 1258291200 6755218
> 1606324866213
> CLIENT MERGE SORT
> 1258291200 6755218
> 1606324866213
> AND
> 1258291200 6755218
> 1606324866213
> CLIENT 24-CHUNK 50558 ROWS 314572800 BYTES PARALLEL 24-WAY FULL SCAN
> OVER LAKE:YD_LAKE_LOAN 1258291200 6755218
> 1606324866213
> CLIENT MERGE SORT
> 1258291200 6755218
> 1606324866213
> CLIENT FILTER BY L.ORGCODE = '01G0'
> 1258291200 6755218
> 1606324866213
> CLIENT 10 ROW LIMIT
> 1258291200 6755218
> 1606324866213
> {noformat}
> Squirrel used query server connection.I guess the forced index was
> unexpectedly escaped during the serialization pass, causing the query
> statement to become a normal query.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
