SQLite

      ** better.
      */
#ifdef SQLITE_ENABLE_STAT4
      pNew->rRun = rSize + 16 - 2*((pTab->tabFlags & TF_HasStat4)!=0);
#else
      pNew->rRun = rSize + 16;
#endif



      ApplyCostMultiplier(pNew->rRun, pTab->costMult);
      whereLoopOutputAdjust(pWC, pNew, rSize);
      rc = whereLoopInsert(pBuilder, pNew);
      pNew->nOut = rSize;
      if( rc ) break;
    }else{
      Bitmask m;

               aSortCost[isOrdered], (nOrderBy-isOrdered), nOrderBy,
               rUnsorted, rCost));
        }else{
          rCost = rUnsorted;
          rUnsorted -= 2;  /* TUNING:  Slight bias in favor of no-sort plans */
        }









        /* Check to see if pWLoop should be added to the set of
        ** mxChoice best-so-far paths.
        **
        ** First look for an existing path among best-so-far paths
        ** that covers the same set of loops and has the same isOrdered
        ** setting as the current path candidate.
        **

 
    wherePathSolver(pWInfo, 0);
    if( db->mallocFailed ) goto whereBeginError;
    if( pWInfo->pOrderBy ){
       wherePathSolver(pWInfo, pWInfo->nRowOut+1);
       if( db->mallocFailed ) goto whereBeginError;
    }

    /* TUNING:  Assume that a DISTINCT clause on a subquery reduces
    ** the output size by a factor of 8 (LogEst -30).
    */
    if( (pWInfo->wctrlFlags & WHERE_WANT_DISTINCT)!=0 ){
      WHERETRACE(0x0080,("nRowOut reduced from %d to %d due to DISTINCT\n",
                         pWInfo->nRowOut, pWInfo->nRowOut-30));
      pWInfo->nRowOut -= 30;
    }

  }
  assert( pWInfo->pTabList!=0 );
  if( pWInfo->pOrderBy==0 && (db->flags & SQLITE_ReverseOrder)!=0 ){
    whereReverseScanOrder(pWInfo);
  }
  if( pParse->nErr ){
    goto whereBeginError;

#define WHERE_IN_EARLYOUT  0x00040000  /* Perhaps quit IN loops early */
#define WHERE_BIGNULL_SORT 0x00080000  /* Column nEq of index is BIGNULL */
#define WHERE_IN_SEEKSCAN  0x00100000  /* Seek-scan optimization for IN */
#define WHERE_TRANSCONS    0x00200000  /* Uses a transitive constraint */
#define WHERE_BLOOMFILTER  0x00400000  /* Consider using a Bloom-filter */
#define WHERE_SELFCULL     0x00800000  /* nOut reduced by extra WHERE terms */
#define WHERE_OMIT_OFFSET  0x01000000  /* Set offset counter to zero */
                      /*   0x02000000  -- available for reuse */
#define WHERE_EXPRIDX      0x04000000  /* Uses an index-on-expressions */

#endif /* !defined(SQLITE_WHEREINT_H) */

# 2005 January 19
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#*************************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing correlated subqueries
#



set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable !subquery {
  finish_test
  return

do_execsql_test subquery-9.3 {
  INSERT INTO t1 VALUES(2);
  SELECT (SELECT DISTINCT x FROM t1 ORDER BY +x LIMIT 1 OFFSET 1) FROM t1;
} {2 2 2 2}
do_execsql_test subquery-9.4 {
  SELECT (SELECT DISTINCT x FROM t1 ORDER BY +x LIMIT 1 OFFSET 2) FROM t1;
} {{} {} {} {}}

# 2023-09-15
# Query planner performance regression reported by private email 
# on 2023-09-14, caused by VIEWSCAN optimization of check-in 609fbb94b8f01d67
# from 2022-09-01.
#
reset_db
do_execsql_test subquery-10.1 {
  CREATE TABLE t1(aa TEXT, bb INT, cc TEXT);
  CREATE INDEX x11 on t1(bb);
  CREATE INDEX x12 on t1(aa);
  CREATE TABLE t2(aa TEXT, xx INT);
  ANALYZE sqlite_master;
  INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES('t1', 'x11', '156789 28');
  INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES('t1', 'x12', '156789 1');
  ANALYZE sqlite_master;
}
do_eqp_test subquery-10.2 {
  WITH v1(aa,cc,bb) AS (SELECT aa, cc, bb FROM t1 WHERE bb=12345),
       v2(aa,mx)    AS (SELECT aa, max(xx) FROM t2 GROUP BY aa)
  SELECT * FROM v1 JOIN v2 ON v1.aa=v2.aa;
} {
  QUERY PLAN
  |--CO-ROUTINE v2
  |  |--SCAN t2
  |  `--USE TEMP B-TREE FOR GROUP BY
  |--SEARCH t1 USING INDEX x11 (bb=?)
  `--SEARCH v2 USING AUTOMATIC COVERING INDEX (aa=?)
}
# ^^^^^^^^^^^^^
# Prior to the fix the incorrect (slow) plan caused by the
# VIEWSCAN optimization was:
#
# QUERY PLAN
# |--CO-ROUTINE v2
# |  |--SCAN t2
# |  `--USE TEMP B-TREE FOR GROUP BY
# |--SCAN v2
# `--SEARCH t1 USING INDEX x12 (aa=?)
#


finish_test

  QUERY PLAN
  |--CO-ROUTINE c
  |  |--SETUP
  |  |  |--SCAN CONSTANT ROW
  |  |  `--SCALAR SUBQUERY xxxxxx
  |  |     `--SCAN w2
  |  `--RECURSIVE STEP
  |     |--SCAN w1
  |     `--SCAN c
  |--SCAN c
  |--SEARCH w2 USING INTEGER PRIMARY KEY (rowid=?)
  `--SEARCH w1 USING INTEGER PRIMARY KEY (rowid=?)
}

do_execsql_test 4.0 {
  WITH t5(t5col1) AS (