SQLite

      }
      pSubq = pSubq->pPrior;
    }
  }
  return nChng;
}
#endif /* !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) */

/*
** Check to see if a subquery contains result-set columns that are
** never used.  If it does, change the value of those result-set columns
** to NULL so that they do not cause unnecessary work to compute.
**
** Return the number of column that were changed to NULL.
*/
static int disableUnusedSubqueryResultColumns(SrcItem *pItem){
  int nCol;
  Select *pSub;      /* The subquery to be simplified */
  Select *pX;        /* For looping over compound elements of pSub */
  Table *pTab;       /* The table that describes the subquery */
  int j;             /* Column number */
  int nChng = 0;     /* Number of columns converted to NULL */
  Bitmask colUsed;   /* Columns that may not be NULLed out */

  assert( pItem!=0 );
  if( pItem->fg.isCorrelated || pItem->fg.isCte ){
    return 0;
  }
  assert( pItem->pTab!=0 );
  pTab = pItem->pTab;
  assert( pItem->pSelect!=0 );
  pSub = pItem->pSelect;
  assert( pSub->pEList->nExpr==pTab->nCol );
  if( (pSub->selFlags & (SF_Distinct|SF_Aggregate))!=0 ){
    testcase( pSub->selFlags & SF_Distinct );
    testcase( pSub->selFlags & SF_Aggregate );
    return 0;
  }
  for(pX=pSub; pX; pX=pX->pPrior){
    if( pX->pPrior && pX->op!=TK_ALL ){
      /* This optimization does not work for compound subqueries that
      ** use UNION, INTERSECT, or EXCEPT.  Only UNION ALL is allowed. */
      return 0;
    }
    if( pX->pWin ){
      /* This optimization does not work for subqueries that use window
      ** functions. */
      return 0;
    }
  }
 colUsed = pItem->colUsed;
  if( pSub->pOrderBy ){
    ExprList *pList = pSub->pOrderBy;
    for(j=0; j<pList->nExpr; j++){
      u16 iCol = pList->a[j].u.x.iOrderByCol;
      if( iCol>0 ){
        iCol--;
        colUsed |= ((Bitmask)1)<<(iCol>=BMS ? BMS-1 : iCol);
      }
    }
  }
  nCol = pTab->nCol;
  for(j=0; j<nCol; j++){
    Select *pX;
    Bitmask m = j<BMS-1 ? MASKBIT(j) : TOPBIT;
    if( (m & colUsed)!=0 ) continue;
    for(pX=pSub; pX; pX=pX->pPrior) {
      Expr *pY = pX->pEList->a[j].pExpr;
      if( pY->op==TK_NULL ) continue;
      pY->op = TK_NULL;
      pX->selFlags |= SF_PushDown;
      nChng++;
    }
  }
  return nChng;
}


/*
** The pFunc is the only aggregate function in the query.  Check to see
** if the query is a candidate for the min/max optimization. 
**
** If the query is a candidate for the min/max optimization, then set
** *ppMinMax to be an ORDER BY clause to be used for the optimization

        sqlite3TreeViewSelect(0, p, 0);
      }
#endif
      assert( pItem->pSelect && (pItem->pSelect->selFlags & SF_PushDown)!=0 );
    }else{
      TREETRACE(0x4000,pParse,p,("Push-down not possible\n"));
    }

    /* Convert unused result columns of the subquery into simple NULL
    ** expressions, to avoid unneeded searching and computation.
    */
    if( OptimizationEnabled(db, SQLITE_NullUnusedCols)
     && disableUnusedSubqueryResultColumns(pItem)
    ){
#if TREETRACE_ENABLED
      if( sqlite3TreeTrace & 0x4000 ){
        TREETRACE(0x4000,pParse,p,
            ("Change unused result columns to NULL for subquery %d:\n",
             pSub->selId));
        sqlite3TreeViewSelect(0, p, 0);
      }
#endif
    }

    zSavedAuthContext = pParse->zAuthContext;
    pParse->zAuthContext = pItem->zName;

    /* Generate code to implement the subquery
    */
    if( fromClauseTermCanBeCoroutine(pParse, pTabList, i, p->selFlags) ){

#define SQLITE_BloomPulldown  0x00100000 /* Run Bloom filters early */
#define SQLITE_BalancedMerge  0x00200000 /* Balance multi-way merges */
#define SQLITE_ReleaseReg     0x00400000 /* Use OP_ReleaseReg for testing */
#define SQLITE_FlttnUnionAll  0x00800000 /* Disable the UNION ALL flattener */
   /* TH3 expects this value  ^^^^^^^^^^ See flatten04.test */
#define SQLITE_IndexedExpr    0x01000000 /* Pull exprs from index when able */
#define SQLITE_Coroutines     0x02000000 /* Co-routines for subqueries */
#define SQLITE_NullUnusedCols 0x04000000 /* NULL unused columns in subqueries */
#define SQLITE_AllOpts        0xffffffff /* All optimizations */

/*
** Macros for testing whether or not optimizations are enabled or disabled.
*/
#define OptimizationDisabled(db, mask)  (((db)->dbOptFlags&(mask))!=0)
#define OptimizationEnabled(db, mask)   (((db)->dbOptFlags&(mask))==0)

  insert into t_distinct_bug values ('1', '2', 'e');
  insert into t_distinct_bug values ('1', '3', 'f');
} {}

do_execsql_test selectC-4.2 {
  select a from (select distinct a, b from t_distinct_bug)
} {1 1 1}

do_execsql_test selectC-4.2b {
  CREATE VIEW v42b AS SELECT DISTINCT a, b FROM t_distinct_bug;
  SELECT a FROM v42b;
} {1 1 1}

do_execsql_test selectC-4.3 {
  select a, udf() from (select distinct a, b from t_distinct_bug)
} {1 1 1 2 1 3}

#-------------------------------------------------------------------------
# Test that the problem in ticket #190c2507 has been fixed.

# 2023-02-16
#
# 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.
#
#***********************************************************************
#
# Test cases for the omit-unused-subquery-column optimization.
#

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

do_execsql_test 1.1 {
  CREATE TABLE t1(
     c0,  c1,  c2,  c3,  c4,  c5,  c6,  c7,  c8,  c9,
     c10, c11, c12, c13, c14, c15, c16, c17, c18, c19,
     c20, c21, c22, c23, c24, c25, c26, c27, c28, c29,
     c30, c31, c32, c33, c34, c35, c36, c37, c38, c39,
     c40, c41, c42, c43, c44, c45, c46, c47, c48, c49,
     c50, c51, c52, c53, c54, c55, c56, c57, c58, c59,
     c60, c61, c62, c63, c64, c65
  );
  INSERT INTO t1 VALUES(
     0,  1,  2,  3,  4,  5,  6,  7,  8,  9,
     10, 11, 12, 13, 14, 15, 16, 17, 18, 19,
     20, 21, 22, 23, 24, 25, 26, 27, 28, 29,
     30, 31, 32, 33, 34, 35, 36, 37, 38, 39,
     40, 41, 42, 43, 44, 45, 46, 47, 48, 49,
     50, 51, 52, 53, 54, 55, 56, 57, 58, 59,
     60, 61, 62, 63, 64, 65
  );
  CREATE INDEX t1c60 ON t1(c60);
}

# The SQL counter(N) function adjusts the value of the global
# TCL variable ::selectH_cnt by the value N and returns the new
# value.  By putting calls to counter(N) as unused columns in a
# view or subquery, we can check to see if the counter gets incremented,
# and if not that means that the unused column was omitted.
#
unset -nocomplain selectH_cnt
set selectH_cnt 0
proc selectH_counter {amt} {
  global selectH_cnt
  incr selectH_cnt $amt
  return $selectH_cnt
}
db func counter selectH_counter

do_execsql_test 1.2 {
  SELECT DISTINCT c44 FROM (
    SELECT c0 AS a, *, counter(1) FROM t1
    UNION ALL
    SELECT c1 AS a, *, counter(1) FROM t1
  ) WHERE c60=60;
} {44}
do_test 1.3 {
  set ::selectH_cnt
} {0}

do_execsql_test 2.1 {
  SELECT a FROM (
    SELECT counter(1) AS cnt, c15 AS a, *, c62 AS b FROM t1
    UNION ALL
    SELECT counter(1) AS cnt, c16 AS a, *, c61 AS b FROM t1
    ORDER BY b
  );
} {16 15}
do_test 2.2 {
  set ::selectH_cnt
} {0}

do_execsql_test 3.1 {
  CREATE VIEW v1 AS
    SELECT c16 AS a, *, counter(1) AS x FROM t1
    UNION ALL
    SELECT c17 AS a, *, counter(1) AS x FROM t1
    UNION ALL
    SELECT c18 AS a, *, counter(1) AS x FROM t1
    UNION ALL
    SELECT c19 AS a, *, counter(1) AS x FROM t1;
  SELECT count(*) FROM v1 WHERE c60=60;
} {4}
do_test 3.2 {
  set ::selectH_cnt
} {0}
do_execsql_test 3.3 {
  SELECT count(a) FROM v1 WHERE c60=60;
} {4}
do_execsql_test 3.4 {
  SELECT a FROM v1 WHERE c60=60;
} {16 17 18 19}
do_test 3.5 {
  set ::selectH_cnt
} {0}
do_execsql_test 3.6 {
  SELECT x FROM v1 WHERE c60=60;
} {1 2 3 4}
do_test 3.7 {
  set ::selectH_cnt
} {4}


finish_test

#-------------------------------------------------------------------------
reset_db
do_execsql_test 24.1 {
  CREATE TABLE t1(a, b, c);
  CREATE VIEW v1 AS SELECT max(a), min(b) FROM t1 GROUP BY c;
}
do_test 24.1 {
  set program [db eval {EXPLAIN SELECT * FROM v1 AS aa, v1 AS bb, v1 AS cc}]
  expr [lsearch $program OpenDup]>0
} {1}
do_execsql_test 24.2 {
  ATTACH "" AS aux;
  CREATE VIEW aux.v3 AS VALUES(1);
  CREATE VIEW main.v3 AS VALUES(3);