Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Changes to src/where.c.

  1881   1881     }
  1882   1882   }
  1883   1883   
  1884   1884   /*
  1885   1885   ** Return TRUE if all of the following are true:
  1886   1886   **
  1887   1887   **   (1)  X has the same or lower cost that Y
  1888         -**   (2)  X is a proper subset of Y
  1889         -**   (3)  X skips at least as many columns as Y
         1888  +**   (2)  X users fewer WHERE clause terms than Y
         1889  +**   (3)  Every WHERE clause term used by X is also used by Y
         1890  +**   (4)  X skips at least as many columns as Y
         1891  +**   (5)  If X is a covering index, than Y is too
  1890   1892   **
  1891         -** By "proper subset" we mean that X uses fewer WHERE clause terms
  1892         -** than Y and that every WHERE clause term used by X is also used
  1893         -** by Y.
  1894         -**
         1893  +** Conditions (2) and (3) mean that X is a "proper subset" of Y.
  1895   1894   ** If X is a proper subset of Y then Y is a better choice and ought
  1896   1895   ** to have a lower cost.  This routine returns TRUE when that cost 
  1897         -** relationship is inverted and needs to be adjusted.  The third rule
         1896  +** relationship is inverted and needs to be adjusted.  Constraint (4)
  1898   1897   ** was added because if X uses skip-scan less than Y it still might
  1899   1898   ** deserve a lower cost even if it is a proper subset of Y.
  1900   1899   */
  1901   1900   static int whereLoopCheaperProperSubset(
  1902   1901     const WhereLoop *pX,       /* First WhereLoop to compare */
  1903   1902     const WhereLoop *pY        /* Compare against this WhereLoop */
  1904   1903   ){
................................................................................
  1913   1912     }
  1914   1913     for(i=pX->nLTerm-1; i>=0; i--){
  1915   1914       if( pX->aLTerm[i]==0 ) continue;
  1916   1915       for(j=pY->nLTerm-1; j>=0; j--){
  1917   1916         if( pY->aLTerm[j]==pX->aLTerm[i] ) break;
  1918   1917       }
  1919   1918       if( j<0 ) return 0;  /* X not a subset of Y since term X[i] not used by Y */
         1919  +  }
         1920  +  if( (pX->wsFlags&WHERE_IDX_ONLY)!=0 
         1921  +   && (pY->wsFlags&WHERE_IDX_ONLY)==0 ){
         1922  +    return 0;  /* Constraint (5) */
  1920   1923     }
  1921   1924     return 1;  /* All conditions meet */
  1922   1925   }
  1923   1926   
  1924   1927   /*
  1925   1928   ** Try to adjust the cost of WhereLoop pTemplate upwards or downwards so
  1926   1929   ** that:

Changes to test/analyze9.test.

  1048   1048     INSERT INTO t4 SELECT a, b, c, d, e, f FROM data;
  1049   1049     ANALYZE;
  1050   1050   } {}
  1051   1051   
  1052   1052   do_eqp_test 23.1 {
  1053   1053     SELECT * FROM t4 WHERE 
  1054   1054       (e=1 AND b='xyz' AND c='zyx' AND a<'AEA') AND f<300
         1055  +  -- Formerly used index i41.  But i41 is not a covering index whereas
         1056  +  -- the PRIMARY KEY is a covering index, and so as of 2017-10-15, the
         1057  +  -- PRIMARY KEY is preferred.
  1055   1058   } {
  1056         -  0 0 0 {SEARCH TABLE t4 USING INDEX i41 (e=? AND c=? AND b=? AND a<?)}
         1059  +  0 0 0 {SEARCH TABLE t4 USING PRIMARY KEY (c=? AND b=? AND a<?)}
  1057   1060   }
  1058   1061   do_eqp_test 23.2 {
  1059   1062     SELECT * FROM t4 WHERE 
  1060   1063       (e=1 AND b='xyz' AND c='zyx' AND a<'JJJ') AND f<300
  1061   1064   } {
  1062   1065     0 0 0 {SEARCH TABLE t4 USING INDEX i42 (f<?)}
  1063   1066   }