SQLite

    sqlite3DbNNFreeNN(db, pWInfo->pMemToFree);
    pWInfo->pMemToFree = pNext;
  }
  sqlite3DbNNFreeNN(db, pWInfo);
}

/*
** Return TRUE if X is a proper subset of Y but is of equal or less cost.
** In other words, return true if all constraints of X are also part of Y
** and Y has additional constraints that might speed the search that X lacks
** but the cost of running X is not more than the cost of running Y.
**
** In other words, return true if the cost relationwship between X and Y
** is inverted and needs to be adjusted.
**
** Case 1:
**
**   (1a)  X and Y use the same index.
**   (1b)  X has fewer == terms than Y
**   (1c)  Neither X nor Y use skip-scan
**   (1d)  X does not have a a greater cost than Y
**
** Case 2:
**
**   (2a)  X has the same or lower cost, or returns the same or fewer rows,
**         than Y.
**   (2b)  X uses fewer WHERE clause terms than Y
**   (2c)  Every WHERE clause term used by X is also used by Y
**   (2d)  X skips at least as many columns as Y
**   (2e)  If X is a covering index, than Y is too









*/
static int whereLoopCheaperProperSubset(
  const WhereLoop *pX,       /* First WhereLoop to compare */
  const WhereLoop *pY        /* Compare against this WhereLoop */
){
  int i, j;
  if( pX->rRun>pY->rRun && pX->nOut>pY->nOut ) return 0; /* (1d) and (2a) */
  assert( (pX->wsFlags & WHERE_VIRTUALTABLE)==0 );
  assert( (pY->wsFlags & WHERE_VIRTUALTABLE)==0 );
  if( pX->u.btree.nEq < pY->u.btree.nEq                  /* (1b) */
   && pX->u.btree.pIndex==pY->u.btree.pIndex             /* (1a) */
   && pX->nSkip==0 && pY->nSkip==0                       /* (1c) */
  ){
    return 1;  /* Case 1 is true */
  }
  if( pX->nLTerm-pX->nSkip >= pY->nLTerm-pY->nSkip ){
    return 0;                                            /* (2b) */
  }

  if( pY->nSkip > pX->nSkip ) return 0;                  /* (2d) */
  for(i=pX->nLTerm-1; i>=0; i--){
    if( pX->aLTerm[i]==0 ) continue;
    for(j=pY->nLTerm-1; j>=0; j--){
      if( pY->aLTerm[j]==pX->aLTerm[i] ) break;
    }
    if( j<0 ) return 0;                                  /* (2c) */
  }
  if( (pX->wsFlags&WHERE_IDX_ONLY)!=0
   && (pY->wsFlags&WHERE_IDX_ONLY)==0 ){
    return 0;                                            /* (2e) */
  }
  return 1;  /* Case 2 is true */
}

/*
** Try to adjust the cost and number of output rows of WhereLoop pTemplate
** upwards or downwards so that:
**
**   (1) pTemplate costs less than any other WhereLoops that are a proper

  do_execsql_test where3-7.$disabled_opt.8 {
    SELECT x3 FROM t73 LEFT JOIN t74 ON x4=y3;
  } {123 123}
  do_execsql_test where3-7.$disabled_opt.9 {
    SELECT DISTINCT x3 FROM t73 LEFT JOIN t74 ON x4=y3;
  } {123}
}

# 2023-12-23
# https://sqlite.org/forum/forumpost/2568d1f6e6
#
# Index usage should be "x=? and y=?" - equality on both values.
# Not: "x=? AND y>?" - inequality on "y"
# 
reset_db
do_execsql_test where3-8.1 {
  CREATE TABLE t1(a,b,c,d);  INSERT INTO t1 VALUES(1,2,3,4);
  CREATE TABLE t2(x,y);      INSERT INTO t2 VALUES(3,4);
  CREATE INDEX t2xy ON t2(x,y);
  SELECT 1 FROM t1 JOIN t2 ON x=c AND y=d WHERE d>0;
} 1
do_eqp_test where3-8.2 {
  SELECT 1 FROM t1 JOIN t2 ON x=c AND y=d WHERE d>0;
} {
  QUERY PLAN
  |--SCAN t1
  `--SEARCH t2 USING COVERING INDEX t2xy (x=? AND y=?)
}



finish_test