SQLite: Check-in [e2c1af78]

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview

Comment:	Adjust the xBestIndex methods on both the fuzzer and transitive_closure virtual tables so that an unused MATCH operator gets a really large cost. Remove ambiguities from the fuzzer test cases.
Downloads:	Tarball \| ZIP archive \| SQL archive
Timelines:	family \| ancestors \| descendants \| both \| nextgen-query-plan-exp
Files:	files \| file ages \| folders
SHA1:	e2c1af78b65a8ace976fa6c035db212e1ffc79b8
User & Date:	drh 2013-06-03 21:25:28

Context

2013-06-03
22:08		Remove more vestiges of sqlite_query_plan from the test cases. (check-in: eb27086e user: drh tags: nextgen-query-plan-exp)
21:25		Adjust the xBestIndex methods on both the fuzzer and transitive_closure virtual tables so that an unused MATCH operator gets a really large cost. Remove ambiguities from the fuzzer test cases. (check-in: e2c1af78 user: drh tags: nextgen-query-plan-exp)
20:46		Pull in recent trunk changes. Fix the ORDER BY optimizer so that it is better able to deal with COLLATE clauses. Clean up ambiguities in the descidx1.test script. (check-in: 6bc71dfc user: drh tags: nextgen-query-plan-exp)

Changes

Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to ext/misc/closure.c.

static int closureBestIndex(
  sqlite3_vtab *pTab,             /* The virtual table */
  sqlite3_index_info *pIdxInfo    /* Information about the query */
){
  int iPlan = 0;
  int i;
  int idx = 1;

  const struct sqlite3_index_constraint *pConstraint;
  closure_vtab *pVtab = (closure_vtab*)pTab;
  double rCost = 10000000.0;

  pConstraint = pIdxInfo->aConstraint;
  for(i=0; i<pIdxInfo->nConstraint; i++, pConstraint++){




    if( pConstraint->usable==0 ) continue;
    if( (iPlan & 1)==0 
     && pConstraint->iColumn==CLOSURE_COL_ROOT
     && pConstraint->op==SQLITE_INDEX_CONSTRAINT_EQ
    ){
      iPlan |= 1;
      pIdxInfo->aConstraintUsage[i].argvIndex = 1;
................................................................................
  pIdxInfo->idxNum = iPlan;
  if( pIdxInfo->nOrderBy==1
   && pIdxInfo->aOrderBy[0].iColumn==CLOSURE_COL_ID
   && pIdxInfo->aOrderBy[0].desc==0
  ){
    pIdxInfo->orderByConsumed = 1;
  }

  pIdxInfo->estimatedCost = rCost;
   
  return SQLITE_OK;
}

/*
** A virtual table module that implements the "approximate_match".

static int closureBestIndex(
  sqlite3_vtab *pTab,             /* The virtual table */
  sqlite3_index_info *pIdxInfo    /* Information about the query */
){
  int iPlan = 0;
  int i;
  int idx = 1;
  int seenMatch = 0;
  const struct sqlite3_index_constraint *pConstraint;
  closure_vtab *pVtab = (closure_vtab*)pTab;
  double rCost = 10000000.0;

  pConstraint = pIdxInfo->aConstraint;
  for(i=0; i<pIdxInfo->nConstraint; i++, pConstraint++){
    if( pConstraint->iColumn==CLOSURE_COL_ROOT
     && pConstraint->op==SQLITE_INDEX_CONSTRAINT_EQ ){
      seenMatch = 1;
    }
    if( pConstraint->usable==0 ) continue;
    if( (iPlan & 1)==0 
     && pConstraint->iColumn==CLOSURE_COL_ROOT
     && pConstraint->op==SQLITE_INDEX_CONSTRAINT_EQ
    ){
      iPlan |= 1;
      pIdxInfo->aConstraintUsage[i].argvIndex = 1;
................................................................................
  pIdxInfo->idxNum = iPlan;
  if( pIdxInfo->nOrderBy==1
   && pIdxInfo->aOrderBy[0].iColumn==CLOSURE_COL_ID
   && pIdxInfo->aOrderBy[0].desc==0
  ){
    pIdxInfo->orderByConsumed = 1;
  }
  if( seenMatch && (iPlan&1)==0 ) rCost *= 1e30;
  pIdxInfo->estimatedCost = rCost;
   
  return SQLITE_OK;
}

/*
** A virtual table module that implements the "approximate_match".

Changes to ext/misc/fuzzer.c.

** filter.argv[2] if both bit-1 and bit-2 are set.
*/
static int fuzzerBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){
  int iPlan = 0;
  int iDistTerm = -1;
  int iRulesetTerm = -1;
  int i;

  const struct sqlite3_index_constraint *pConstraint;


  pConstraint = pIdxInfo->aConstraint;
  for(i=0; i<pIdxInfo->nConstraint; i++, pConstraint++){




    if( pConstraint->usable==0 ) continue;
    if( (iPlan & 1)==0 
     && pConstraint->iColumn==0
     && pConstraint->op==SQLITE_INDEX_CONSTRAINT_MATCH
    ){
      iPlan |= 1;
      pIdxInfo->aConstraintUsage[i].argvIndex = 1;
      pIdxInfo->aConstraintUsage[i].omit = 1;

    }
    if( (iPlan & 2)==0
     && pConstraint->iColumn==1
     && (pConstraint->op==SQLITE_INDEX_CONSTRAINT_LT
           || pConstraint->op==SQLITE_INDEX_CONSTRAINT_LE)
    ){
      iPlan |= 2;
      iDistTerm = i;

    }
    if( (iPlan & 4)==0
     && pConstraint->iColumn==2
     && pConstraint->op==SQLITE_INDEX_CONSTRAINT_EQ
    ){
      iPlan |= 4;
      pIdxInfo->aConstraintUsage[i].omit = 1;
      iRulesetTerm = i;

    }
  }
  if( iPlan & 2 ){
    pIdxInfo->aConstraintUsage[iDistTerm].argvIndex = 1+((iPlan&1)!=0);
  }
  if( iPlan & 4 ){
    int idx = 1;
................................................................................
  pIdxInfo->idxNum = iPlan;
  if( pIdxInfo->nOrderBy==1
   && pIdxInfo->aOrderBy[0].iColumn==1
   && pIdxInfo->aOrderBy[0].desc==0
  ){
    pIdxInfo->orderByConsumed = 1;
  }

  pIdxInfo->estimatedCost = (double)10000;
   
  return SQLITE_OK;
}

/*
** A virtual table module that implements the "fuzzer".
*/

** filter.argv[2] if both bit-1 and bit-2 are set.
*/
static int fuzzerBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){
  int iPlan = 0;
  int iDistTerm = -1;
  int iRulesetTerm = -1;
  int i;
  int seenMatch = 0;
  const struct sqlite3_index_constraint *pConstraint;
  double rCost = 100000;

  pConstraint = pIdxInfo->aConstraint;
  for(i=0; i<pIdxInfo->nConstraint; i++, pConstraint++){
    if( pConstraint->iColumn==0
     && pConstraint->op==SQLITE_INDEX_CONSTRAINT_MATCH ){
      seenMatch = 1;
    }
    if( pConstraint->usable==0 ) continue;
    if( (iPlan & 1)==0 
     && pConstraint->iColumn==0
     && pConstraint->op==SQLITE_INDEX_CONSTRAINT_MATCH
    ){
      iPlan |= 1;
      pIdxInfo->aConstraintUsage[i].argvIndex = 1;
      pIdxInfo->aConstraintUsage[i].omit = 1;
      rCost /= 1000000.0;
    }
    if( (iPlan & 2)==0
     && pConstraint->iColumn==1
     && (pConstraint->op==SQLITE_INDEX_CONSTRAINT_LT
           || pConstraint->op==SQLITE_INDEX_CONSTRAINT_LE)
    ){
      iPlan |= 2;
      iDistTerm = i;
      rCost /= 10.0;
    }
    if( (iPlan & 4)==0
     && pConstraint->iColumn==2
     && pConstraint->op==SQLITE_INDEX_CONSTRAINT_EQ
    ){
      iPlan |= 4;
      pIdxInfo->aConstraintUsage[i].omit = 1;
      iRulesetTerm = i;
      rCost /= 10.0;
    }
  }
  if( iPlan & 2 ){
    pIdxInfo->aConstraintUsage[iDistTerm].argvIndex = 1+((iPlan&1)!=0);
  }
  if( iPlan & 4 ){
    int idx = 1;
................................................................................
  pIdxInfo->idxNum = iPlan;
  if( pIdxInfo->nOrderBy==1
   && pIdxInfo->aOrderBy[0].iColumn==1
   && pIdxInfo->aOrderBy[0].desc==0
  ){
    pIdxInfo->orderByConsumed = 1;
  }
  if( seenMatch && (iPlan&1)==0 ) rCost *= 1e30;
  pIdxInfo->estimatedCost = rCost;
   
  return SQLITE_OK;
}

/*
** A virtual table module that implements the "fuzzer".
*/

Changes to test/fuzzer1.test.

  INSERT INTO x3_rules VALUES(2, 'a', 'z',  8);
  CREATE VIRTUAL TABLE x3 USING fuzzer(x3_rules);
}

do_execsql_test 8.2.1 {
  SELECT cFrom, cTo, word 
    FROM x3_rules CROSS JOIN x3 
    WHERE word MATCH 'a' AND cost=distance AND ruleset=2;

} {a x x a y y a z z}

do_execsql_test 8.2.2 {
  SELECT cFrom, cTo, word 
    FROM x3 CROSS JOIN x3_rules
    WHERE word MATCH 'a' AND cost=distance AND ruleset=2;

} {a z z a y y a x x}

do_execsql_test 8.2.3 {
  SELECT cFrom, cTo, word 
    FROM x3_rules, x3 
    WHERE word MATCH 'a' AND cost=distance AND ruleset=2;

} {a z z a y y a x x}

do_execsql_test 8.2.4 {
  SELECT cFrom, cTo, word 
    FROM x3, x3_rules
    WHERE word MATCH 'a' AND cost=distance AND ruleset=2;

} {a z z a y y a x x}

do_execsql_test 8.2.5 {
  CREATE INDEX i1 ON x3_rules(cost);
  SELECT cFrom, cTo, word 
    FROM x3_rules, x3 
    WHERE word MATCH 'a' AND cost=distance AND ruleset=2;

} {a z z a y y a x x}

do_execsql_test 8.2.5 {
  SELECT word FROM x3_rules, x3 WHERE word MATCH x3_rules.cFrom AND ruleset=2;
} {a z y x a z y x a z y x}

do_execsql_test 8.2.6 {
  SELECT word FROM x3_rules, x3 
  WHERE word MATCH x3_rules.cFrom 
    AND ruleset=2 
    AND x3_rules.cost=8;








|
>





|
>





|
>





|
>






|
>



|

  INSERT INTO x3_rules VALUES(2, 'a', 'z',  8);
  CREATE VIRTUAL TABLE x3 USING fuzzer(x3_rules);
}

do_execsql_test 8.2.1 {
  SELECT cFrom, cTo, word 
    FROM x3_rules CROSS JOIN x3 
    WHERE word MATCH 'a' AND cost=distance AND ruleset=2
    ORDER BY +cTo;
} {a x x a y y a z z}

do_execsql_test 8.2.2 {
  SELECT cFrom, cTo, word 
    FROM x3 CROSS JOIN x3_rules
    WHERE word MATCH 'a' AND cost=distance AND ruleset=2
    ORDER BY +cTo DESC
} {a z z a y y a x x}

do_execsql_test 8.2.3 {
  SELECT cFrom, cTo, word 
    FROM x3_rules, x3 
    WHERE word MATCH 'a' AND cost=distance AND ruleset=2
    ORDER BY +cTo DESC;
} {a z z a y y a x x}

do_execsql_test 8.2.4 {
  SELECT cFrom, cTo, word 
    FROM x3, x3_rules
    WHERE word MATCH 'a' AND cost=distance AND ruleset=2
    ORDER BY +cTo DESC;
} {a z z a y y a x x}

do_execsql_test 8.2.5 {
  CREATE INDEX i1 ON x3_rules(cost);
  SELECT cFrom, cTo, word 
    FROM x3_rules, x3 
    WHERE word MATCH 'a' AND cost=distance AND ruleset=2
    ORDER BY +cTo DESC;
} {a z z a y y a x x}

do_execsql_test 8.2.5 {
  SELECT word FROM x3_rules, x3 WHERE word MATCH x3_rules.cFrom AND ruleset=2
} {a z y x a z y x a z y x}

do_execsql_test 8.2.6 {
  SELECT word FROM x3_rules, x3 
  WHERE word MATCH x3_rules.cFrom 
    AND ruleset=2 
    AND x3_rules.cost=8;