SQLite

  const char *zOpt;
  int onoff;
  int mask = 0;
  static const struct {
    const char *zOptName;
    int mask;
  } aOpt[] = {
    { "all",                 SQLITE_AllOpts        },
    { "none",                0                     },
    { "query-flattener",     SQLITE_QueryFlattener },
    { "column-cache",        SQLITE_ColumnCache    },
    { "groupby-order",       SQLITE_GroupByOrder   },
    { "factor-constants",    SQLITE_FactorOutConst },
    { "real-as-int",         SQLITE_IdxRealAsInt   },
    { "distinct-opt",        SQLITE_DistinctOpt    },
    { "cover-idx-scan",      SQLITE_CoverIdxScan   },
    { "order-by-idx-join",   SQLITE_OrderByIdxJoin },
    { "transitive",          SQLITE_Transitive     },
    { "subquery-coroutine",  SQLITE_SubqCoroutine  },
    { "omit-noop-join",      SQLITE_OmitNoopJoin   },
  };

  if( objc!=4 ){
    Tcl_WrongNumArgs(interp, 1, objv, "DB OPT BOOLEAN");
    return TCL_ERROR;
  }
  if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR;
  if( Tcl_GetBooleanFromObj(interp, objv[3], &onoff) ) return TCL_ERROR;
  zOpt = Tcl_GetString(objv[2]);
  for(i=0; i<sizeof(aOpt)/sizeof(aOpt[0]); i++){
    if( strcmp(zOpt, aOpt[i].zOptName)==0 ){
      mask = aOpt[i].mask;
      break;
    }
  }
  if( onoff ) mask = ~mask;
  if( i>=sizeof(aOpt)/sizeof(aOpt[0]) ){
    Tcl_AppendResult(interp, "unknown optimization - should be one of:",
                     (char*)0);
    for(i=0; i<sizeof(aOpt)/sizeof(aOpt[0]); i++){
      Tcl_AppendResult(interp, " ", aOpt[i].zOptName, (char*)0);
    }
    return TCL_ERROR;
  }
  sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS, db, mask);
  return TCL_OK;
}

  if( pWInfo->nLevel>=2
   && pResultSet!=0
   && OptimizationEnabled(db, SQLITE_OmitNoopJoin)
  ){
    Bitmask tabUsed = exprListTableUsage(pMaskSet, pResultSet);
    if( pOrderBy ) tabUsed |= exprListTableUsage(pMaskSet, pOrderBy);
    while( pWInfo->nLevel>=2 ){
      WhereTerm *pTerm, *pEnd;
      pLoop = pWInfo->a[pWInfo->nLevel-1].pWLoop;
      if( (pWInfo->pTabList->a[pLoop->iTab].jointype & JT_LEFT)==0 ) break;
      if( (wctrlFlags & WHERE_WANT_DISTINCT)==0
       && (pLoop->wsFlags & WHERE_ONEROW)==0
      ){
        break;
      }
      if( (tabUsed & pLoop->maskSelf)!=0 ) break;
      pEnd = sWLB.pWC->a + sWLB.pWC->nTerm;
      for(pTerm=sWLB.pWC->a; pTerm<pEnd; pTerm++){
        if( (pTerm->prereqAll & pLoop->maskSelf)!=0
         && !ExprHasProperty(pTerm->pExpr, EP_FromJoin)
        ){
          break;
        }
      }
      if( pTerm<pEnd ) break;
      WHERETRACE(0xffff, ("-> drop loop %c not used\n", pLoop->cId));
      pWInfo->nLevel--;
      nTabList--;
    }
  }
  WHERETRACE(0xffff,("*** Optimizer Finished ***\n"));
  pWInfo->pParse->nQueryLoop += pWInfo->nRowOut;

  do_test where3-6.$cnt.8 {
    set sql "SELECT * FROM t6w NATURAL JOIN t6x NATURAL JOIN t6y"
    append sql " JOIN t6z USING(a) "
    append sql $::predicate
    db eval $sql
  } {1 w-one x-one y-one z-one 9 w-nine x-nine y-nine z-nine}
}

do_execsql_test where3-7-setup {
  CREATE TABLE t71(x1 INTEGER PRIMARY KEY, y1);
  CREATE TABLE t72(x2 INTEGER PRIMARY KEY, y2);
  CREATE TABLE t73(x3, y3);
  CREATE TABLE t74(x4, y4);
  INSERT INTO t71 VALUES(123,234);
  INSERT INTO t72 VALUES(234,345);
  INSERT INTO t73 VALUES(123,234);
  INSERT INTO t74 VALUES(234,345);
  INSERT INTO t74 VALUES(234,678);
} {}
foreach disabled_opt {none omit-noop-join all} {
  optimization_control db all 1
  optimization_control db $disabled_opt 0
  do_execsql_test where3-7.$disabled_opt.1 {
    SELECT x1 FROM t71 LEFT JOIN t72 ON x2=y1;
  } {123}
  do_execsql_test where3-7.$disabled_opt.2 {
    SELECT x1 FROM t71 LEFT JOIN t72 ON x2=y1 WHERE y2 IS NULL;
  } {}
  do_execsql_test where3-7.$disabled_opt.3 {
    SELECT x1 FROM t71 LEFT JOIN t72 ON x2=y1 WHERE y2 IS NOT NULL;
  } {123}
  do_execsql_test where3-7.$disabled_opt.4 {
    SELECT x1 FROM t71 LEFT JOIN t72 ON x2=y1 AND y2 IS NULL;
  } {123}
  do_execsql_test where3-7.$disabled_opt.5 {
    SELECT x1 FROM t71 LEFT JOIN t72 ON x2=y1 AND y2 IS NOT NULL;
  } {123}
  do_execsql_test where3-7.$disabled_opt.6 {
    SELECT x3 FROM t73 LEFT JOIN t72 ON x2=y3;
  } {123}
  do_execsql_test where3-7.$disabled_opt.7 {
    SELECT DISTINCT x3 FROM t73 LEFT JOIN t72 ON x2=y3;
  } {123}
  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}
}


finish_test