Uploaded image for project: 'Phoenix'
  1. Phoenix
  2. PHOENIX-6235

Using the Query Server automatically filters the forced index

Attach filesAttach ScreenshotVotersWatch issueWatchersCreate sub-taskLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Cannot Reproduce
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: queryserver
    • Labels:
      None
    • Environment:

      SQuirreL SQL Client 4.0.0

      Phoenix 5.0

      sqlline version 1.2.0

      Description

       
      I executed the same statement in different ways and got different results

      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;
      

      Squirrel client:

      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   

       

      shell sqlline:

      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  
      

      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.

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              muWang wangMu

              Dates

              • Created:
                Updated:
                Resolved:

                Issue deployment