SQLite

  bestOrClauseIndex(pParse, pWC, pSrc, notReady, notValid, pOrderBy, pCost);
}
#endif /* SQLITE_OMIT_VIRTUALTABLE */

/*
** Argument pIdx is a pointer to an index structure that has an array of
** SQLITE_INDEX_SAMPLES evenly spaced samples of the first indexed column
** stored in Index.aSample. These samples divide the domain of values stored
** the index into (SQLITE_INDEX_SAMPLES+1) regions.
** Region 0 contains all values less than the first sample value. Region
** 1 contains values between the first and second samples.  Region 2 contains
** values between samples 2 and 3.  And so on.  Region SQLITE_INDEX_SAMPLES
** contains values larger than the last sample.
**
** If the index contains many duplicates of a single value, then it is
** possible that two or more adjacent samples can hold the same value.
** When that is the case, the smallest possible region code is returned
** when roundUp is false and the largest possible region code is returned
** when roundUp is true.
**
** If successful, this function determines which of the regions value 
** pVal lies in, sets *piRegion to the region index (a value between 0
** and SQLITE_INDEX_SAMPLES+1, inclusive) and returns SQLITE_OK.
** Or, if an OOM occurs while converting text values between encodings,
** SQLITE_NOMEM is returned and *piRegion is undefined.
*/
#ifdef SQLITE_ENABLE_STAT2
static int whereRangeRegion(
  Parse *pParse,              /* Database connection */
  Index *pIdx,                /* Index to consider domain of */
  sqlite3_value *pVal,        /* Value to consider */
  int roundUp,                /* Return largest valid region if true */
  int *piRegion               /* OUT: Region of domain in which value lies */
){
  assert( roundUp==0 || roundUp==1 );
  if( ALWAYS(pVal) ){
    IndexSample *aSample = pIdx->aSample;
    int i = 0;
    int eType = sqlite3_value_type(pVal);

    if( eType==SQLITE_INTEGER || eType==SQLITE_FLOAT ){
      double r = sqlite3_value_double(pVal);
      for(i=0; i<SQLITE_INDEX_SAMPLES; i++){
        if( aSample[i].eType==SQLITE_NULL ) continue;
        if( aSample[i].eType>=SQLITE_TEXT ) break;
        if( roundUp ){
          if( aSample[i].u.r>r ) break;
        }else{
          if( aSample[i].u.r>=r ) break;
        }
      }
    }else{ 
      sqlite3 *db = pParse->db;
      CollSeq *pColl;
      const u8 *z;
      int n;

          return SQLITE_NOMEM;
        }
        assert( z && pColl && pColl->xCmp );
      }
      n = sqlite3ValueBytes(pVal, pColl->enc);

      for(i=0; i<SQLITE_INDEX_SAMPLES; i++){
        int c;
        int eSampletype = aSample[i].eType;
        if( eSampletype==SQLITE_NULL || eSampletype<eType ) continue;
        if( (eSampletype!=eType) ) break;
#ifndef SQLITE_OMIT_UTF16
        if( pColl->enc!=SQLITE_UTF8 ){
          int nSample;
          char *zSample = sqlite3Utf8to16(
              db, pColl->enc, aSample[i].u.z, aSample[i].nByte, &nSample
          );
          if( !zSample ){
            assert( db->mallocFailed );
            return SQLITE_NOMEM;
          }
          c = pColl->xCmp(pColl->pUser, nSample, zSample, n, z);
          sqlite3DbFree(db, zSample);
        }else
#endif
        {
          c = pColl->xCmp(pColl->pUser, aSample[i].nByte, aSample[i].u.z, n, z);
        }
        if( c-roundUp>=0 ) break;
      }
    }

    assert( i>=0 && i<=SQLITE_INDEX_SAMPLES );
    *piRegion = i;
  }
  return SQLITE_OK;

  if( nEq==0 && p->aSample ){
    sqlite3_value *pLowerVal = 0;
    sqlite3_value *pUpperVal = 0;
    int iEst;
    int iLower = 0;
    int iUpper = SQLITE_INDEX_SAMPLES;
    int roundUpUpper;
    int roundUpLower;
    u8 aff = p->pTable->aCol[p->aiColumn[0]].affinity;

    if( pLower ){
      Expr *pExpr = pLower->pExpr->pRight;
      rc = valueFromExpr(pParse, pExpr, aff, &pLowerVal);
      assert( pLower->eOperator==WO_GT || pLower->eOperator==WO_GE );
      roundUpLower = (pLower->eOperator==WO_GT) ?1:0;
    }
    if( rc==SQLITE_OK && pUpper ){
      Expr *pExpr = pUpper->pExpr->pRight;
      rc = valueFromExpr(pParse, pExpr, aff, &pUpperVal);
      assert( pUpper->eOperator==WO_LT || pUpper->eOperator==WO_LE );
      roundUpUpper = (pUpper->eOperator==WO_LE) ?1:0;
    }

    if( rc!=SQLITE_OK || (pLowerVal==0 && pUpperVal==0) ){
      sqlite3ValueFree(pLowerVal);
      sqlite3ValueFree(pUpperVal);
      goto range_est_fallback;
    }else if( pLowerVal==0 ){
      rc = whereRangeRegion(pParse, p, pUpperVal, roundUpUpper, &iUpper);
      if( pLower ) iLower = iUpper/2;
    }else if( pUpperVal==0 ){
      rc = whereRangeRegion(pParse, p, pLowerVal, roundUpLower, &iLower);
      if( pUpper ) iUpper = (iLower + SQLITE_INDEX_SAMPLES + 1)/2;
    }else{
      rc = whereRangeRegion(pParse, p, pUpperVal, roundUpUpper, &iUpper);
      if( rc==SQLITE_OK ){
        rc = whereRangeRegion(pParse, p, pLowerVal, roundUpLower, &iLower);
      }
    }

    iEst = iUpper - iLower;
    testcase( iEst==SQLITE_INDEX_SAMPLES );
    assert( iEst<=SQLITE_INDEX_SAMPLES );
    if( iEst<1 ){
      *piEst = 50/SQLITE_INDEX_SAMPLES;
    }else{
      *piEst = (iEst*100)/SQLITE_INDEX_SAMPLES;
    }
    sqlite3ValueFree(pLowerVal);
    sqlite3ValueFree(pUpperVal);

    return rc;
  }
range_est_fallback:
#else
  UNUSED_PARAMETER(pParse);
  UNUSED_PARAMETER(p);
  UNUSED_PARAMETER(nEq);

  SELECT * FROM t1 WHERE x BETWEEN 100 AND 500 AND y BETWEEN 400 AND 700
} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>? AND y<?) (~75 rows)}
}
do_eqp_test 2.7 {
  SELECT * FROM t1 WHERE x BETWEEN -400 AND -300 AND y BETWEEN 100 AND 300
} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_x (x>? AND x<?) (~12 rows)}
}
do_eqp_test 2.8 {
  SELECT * FROM t1 WHERE x BETWEEN 100 AND 300 AND y BETWEEN -400 AND -300
} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>? AND y<?) (~12 rows)}
}
do_eqp_test 2.9 {
  SELECT * FROM t1 WHERE x BETWEEN 500 AND 100 AND y BETWEEN 100 AND 300
} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_x (x>? AND x<?) (~12 rows)}
}
do_eqp_test 2.10 {
  SELECT * FROM t1 WHERE x BETWEEN 100 AND 300 AND y BETWEEN 500 AND 100
} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>? AND y<?) (~12 rows)}
}

do_test analyze2-3.1 {
  set alphabet [list a b c d e f g h i j]
  execsql BEGIN
  for {set i 0} {$i < 1000} {incr i} {
    set str    [lindex $alphabet [expr ($i/100)%10]] 

  SELECT * FROM t1 WHERE x BETWEEN 100 AND 500 AND y BETWEEN 'a' AND 'b'
} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>? AND y<?) (~50 rows)}
}
do_eqp_test 3.4 {
  SELECT * FROM t1 WHERE x BETWEEN 100 AND 400 AND y BETWEEN 'a' AND 'h'
} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_x (x>? AND x<?) (~100 rows)}
}
do_eqp_test 3.5 {
  SELECT * FROM t1 WHERE x<'a' AND y>'h'
} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>?) (~66 rows)}
}
do_eqp_test 3.6 {

do_test analyze2-6.2.2 {
  db cache flush
  execsql ANALYZE
  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
        t5.a>1 AND t5.a<15 AND
        t6.a>1
  }
} {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~1 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
do_test analyze2-6.2.3 {
  sqlite3 db test.db
  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
        t5.a>1 AND t5.a<15 AND
        t6.a>1
  }
} {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~1 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
do_test analyze2-6.2.4 {
  execsql { 
    PRAGMA writable_schema = 1;
    DELETE FROM sqlite_master WHERE tbl_name = 'sqlite_stat1';
  }
  sqlite3 db test.db
  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 

  }
  sqlite3 db test.db
  execsql ANALYZE
  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
        t5.a>1 AND t5.a<15 AND
        t6.a>1
  }
} {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~1 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}

#--------------------------------------------------------------------
# These tests, analyze2-7.*, test that the sqlite_stat2 functionality
# works in shared-cache mode. Note that these tests reuse the database
# created for the analyze2-6.* tests.
#
ifcapable shared_cache {

  } {20}

  do_test analyze2-7.5 {
    eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
          t5.a>1 AND t5.a<15 AND
          t6.a>1
    } db1
  } {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~1 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
  do_test analyze2-7.6 {
    incr_schema_cookie test.db
    execsql { SELECT * FROM sqlite_master } db2
    eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
          t5.a>1 AND t5.a<15 AND
          t6.a>1
    } db2
  } {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~1 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
  do_test analyze2-7.7 {
    incr_schema_cookie test.db
    execsql { SELECT * FROM sqlite_master } db1
    eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
          t5.a>1 AND t5.a<15 AND
          t6.a>1
    } db1
  } {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~1 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}

  do_test analyze2-7.8 {
    execsql { DELETE FROM sqlite_stat2 } db2
    execsql { SELECT * FROM sqlite_master } db1
    eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
          t5.a>1 AND t5.a<15 AND
          t6.a>1
    } db1
  } {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~1 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
  do_test analyze2-7.9 {
    execsql { SELECT * FROM sqlite_master } db2
    eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
          t5.a>1 AND t5.a<15 AND
          t6.a>1
    } db2
  } {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~1 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}

  do_test analyze2-7.10 {
    incr_schema_cookie test.db
    execsql { SELECT * FROM sqlite_master } db1
    eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
          t5.a>1 AND t5.a<15 AND
          t6.a>1

# 2011 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 tests for SQLite library.  The focus of the tests
# in this file is the use of the sqlite_stat2 histogram data on tables
# with many repeated values and only a few distinct values.
#

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

ifcapable !stat2 {
  finish_test
  return
}

set testprefix analyze5

proc eqp {sql {db db}} {
  uplevel execsql [list "EXPLAIN QUERY PLAN $sql"] $db
}

do_test analyze5-1.0 {
  execsql { CREATE TABLE t1(x INTEGER PRIMARY KEY, y, z) }
  for {set i 0} {$i < 1000} {incr i} {
    set j [expr {$i>=25 && $i<=50}]
    set k [expr {($i>=400) + ($i>=700) + ($i>=875)}]
    execsql { INSERT INTO t1 VALUES($i,$j,$k) }
  }
  execsql { 
    CREATE INDEX t1y ON t1(y);
    CREATE INDEX t1z ON t1(z);
    ANALYZE;
    SELECT * FROM sqlite_stat2 ORDER BY 1, 2, 3;
  }
} [list t1 t1y 0 0 \
        t1 t1y 1 0 \
        t1 t1y 2 0 \
        t1 t1y 3 0 \
        t1 t1y 4 0 \
        t1 t1y 5 0 \
        t1 t1y 6 0 \
        t1 t1y 7 0 \
        t1 t1y 8 0 \
        t1 t1y 9 0 \
        t1 t1z 0 0 \
        t1 t1z 1 0 \
        t1 t1z 2 0 \
        t1 t1z 3 0 \
        t1 t1z 4 1 \
        t1 t1z 5 1 \
        t1 t1z 6 1 \
        t1 t1z 7 2 \
        t1 t1z 8 2 \
        t1 t1z 9 3]

# Verify that range queries generate the correct row count estimates
#
foreach {testid where rows} {
  1  {z>=0 AND z<=0}     400
  2  {z>=1 AND z<=1}     300
  3  {z>=2 AND z<=2}     200
  4  {z>=3 AND z<=3}     100
  5  {z>=4 AND z<=4}      50
  6  {z>=-1 AND z<=-1}    50
  7  {z>1 AND z<3}       200
  8  {z>0 AND z<100}     600
  9  {z>=1 AND z<100}    600
 10  {z>1 AND z<100}     300
 11  {z>=2 AND z<100}    300
 12  {z>2 AND z<100}     100
 13  {z>=3 AND z<100}    100
 14  {z>3 AND z<100}      50
 15  {z>=4 AND z<100}     50
 16  {z>=-100 AND z<=-1}  50
 17  {z>=-100 AND z<=0}  400
 18  {z>=-100 AND z<0}    50
 19  {z>=-100 AND z<=1}  700
 20  {z>=-100 AND z<2}   700
 21  {z>=-100 AND z<=2}  900
 22  {z>=-100 AND z<3}   900

 31  {z>=0.0 AND z<=0.0}   400
 32  {z>=1.0 AND z<=1.0}   300
 33  {z>=2.0 AND z<=2.0}   200
 34  {z>=3.0 AND z<=3.0}   100
 35  {z>=4.0 AND z<=4.0}    50
 36  {z>=-1.0 AND z<=-1.0}  50
 37  {z>1.5 AND z<3.0}     200
 38  {z>0.5 AND z<100}     600
 39  {z>=1.0 AND z<100}    600
 40  {z>1.5 AND z<100}     300
 41  {z>=2.0 AND z<100}    300
 42  {z>2.1 AND z<100}     100
 43  {z>=3.0 AND z<100}    100
 44  {z>3.2 AND z<100}      50
 45  {z>=4.0 AND z<100}     50
 46  {z>=-100 AND z<=-1.0}  50
 47  {z>=-100 AND z<=0.0}  400
 48  {z>=-100 AND z<0.0}    50
 49  {z>=-100 AND z<=1.0}  700
 50  {z>=-100 AND z<2.0}   700
 51  {z>=-100 AND z<=2.0}  900
 52  {z>=-100 AND z<3.0}   900

} {
  do_test analyze5-1.$testid {
    eqp "SELECT * FROM t1 WHERE $where"
  } [format {0 0 0 {SEARCH TABLE t1 USING INDEX t1z (z>? AND z<?) (~%d rows)}} \
       $rows]
}

# Change the table values from integer to floating point and then
# repeat the same sequence of tests.  We should get the same results.
#
do_test analyze5-2.0 {
  db eval {
    UPDATE t1 SET z=z+0.0;
    ANALYZE;
    SELECT sample FROM sqlite_stat2 WHERE idx='t1z' ORDER BY sampleno;
  }
} {0.0 0.0 0.0 0.0 1.0 1.0 1.0 2.0 2.0 3.0}
foreach {testid where rows} {
  1  {z>=0 AND z<=0}     400
  2  {z>=1 AND z<=1}     300
  3  {z>=2 AND z<=2}     200
  4  {z>=3 AND z<=3}     100
  5  {z>=4 AND z<=4}      50
  6  {z>=-1 AND z<=-1}    50
  7  {z>1 AND z<3}       200
  8  {z>0 AND z<100}     600
  9  {z>=1 AND z<100}    600
 10  {z>1 AND z<100}     300
 11  {z>=2 AND z<100}    300
 12  {z>2 AND z<100}     100
 13  {z>=3 AND z<100}    100
 14  {z>3 AND z<100}      50
 15  {z>=4 AND z<100}     50
 16  {z>=-100 AND z<=-1}  50
 17  {z>=-100 AND z<=0}  400
 18  {z>=-100 AND z<0}    50
 19  {z>=-100 AND z<=1}  700
 20  {z>=-100 AND z<2}   700
 21  {z>=-100 AND z<=2}  900
 22  {z>=-100 AND z<3}   900

 31  {z>=0.0 AND z<=0.0}   400
 32  {z>=1.0 AND z<=1.0}   300
 33  {z>=2.0 AND z<=2.0}   200
 34  {z>=3.0 AND z<=3.0}   100
 35  {z>=4.0 AND z<=4.0}    50
 36  {z>=-1.0 AND z<=-1.0}  50
 37  {z>1.5 AND z<3.0}     200
 38  {z>0.5 AND z<100}     600
 39  {z>=1.0 AND z<100}    600
 40  {z>1.5 AND z<100}     300
 41  {z>=2.0 AND z<100}    300
 42  {z>2.1 AND z<100}     100
 43  {z>=3.0 AND z<100}    100
 44  {z>3.2 AND z<100}      50
 45  {z>=4.0 AND z<100}     50
 46  {z>=-100 AND z<=-1.0}  50
 47  {z>=-100 AND z<=0.0}  400
 48  {z>=-100 AND z<0.0}    50
 49  {z>=-100 AND z<=1.0}  700
 50  {z>=-100 AND z<2.0}   700
 51  {z>=-100 AND z<=2.0}  900
 52  {z>=-100 AND z<3.0}   900
} {
  do_test analyze5-2.$testid {
    eqp "SELECT * FROM t1 WHERE $where"
  } [format {0 0 0 {SEARCH TABLE t1 USING INDEX t1z (z>? AND z<?) (~%d rows)}} \
       $rows]
}

# Repeat the same range query tests using TEXT columns.
#
do_test analyze5-3.0 {
  db eval {
    UPDATE t1 SET y=CASE z WHEN 0 THEN 'alpha' WHEN 1 THEN 'bravo'
                           WHEN 2 THEN 'charlie' ELSE 'delta' END;
    ANALYZE;
    SELECT sample FROM sqlite_stat2 WHERE idx='t1y' ORDER BY sampleno;
  }
} {alpha alpha alpha alpha bravo bravo bravo charlie charlie delta}
foreach {testid where rows} {
  1  {y>='alpha' AND y<='alpha'}     400
  2  {y>='bravo' AND y<='bravo'}     300
  3  {y>='charlie' AND y<='charlie'} 200
  4  {y>='delta' AND y<='delta'}     100
  5  {y>='echo' AND y<='echo'}        50
  6  {y>='' AND y<=''}                50
  7  {y>'bravo' AND y<'delta'}       200
  8  {y>'alpha' AND y<'zzz'}         600
  9  {y>='bravo' AND y<'zzz'}        600
 10  {y>'bravo' AND y<'zzz'}         300
 11  {y>='charlie' AND y<'zzz'}      300
 12  {y>'charlie' AND y<'zzz'}       100
 13  {y>='delta' AND y<'zzz'}        100
 14  {y>'delta' AND y<'zzz'}          50
 15  {y>='echo' AND y<'zzz'}          50
 16  {y>=0 AND y<=''}                 50
 17  {y>=0 AND y<='alpha'}           400
 18  {y>=0 AND y<'alpha'}             50
 19  {y>=0 AND y<='bravo'}           700
 20  {y>=0 AND y<'charlie'}          700
 21  {y>=0 AND y<='charlie'}         900
 22  {y>=0 AND y<'delta'}            900
} {
  do_test analyze5-3.$testid {
    eqp "SELECT * FROM t1 WHERE $where"
  } [format {0 0 0 {SEARCH TABLE t1 USING INDEX t1y (y>? AND y<?) (~%d rows)}} \
       $rows]
}


finish_test