/ Check-in [0884ff1d]
Login

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

Overview
Comment:Changes to allow indexes to be recommended for queries on SQL views.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | schemalint
Files: files | file ages | folders
SHA3-256: 0884ff1da2e27b146c764b73cf299a1f2cfe213c4a79bde34dec02d1fc946e70
User & Date: dan 2017-04-06 18:44:18
Context
2017-04-07
20:14
Refactor code to suggest indexes from the shell tool into an extension in ext/expert. Unfinished. check-in: 305e19f9 user: dan tags: schemalint
2017-04-06
18:44
Changes to allow indexes to be recommended for queries on SQL views. check-in: 0884ff1d user: dan tags: schemalint
2017-04-04
17:50
Add some support for OR terms to sqlite3_whereinfo_hook(). check-in: 5cd07000 user: dan tags: schemalint
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/shell_indexes.c.

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
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
...
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
...
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
...
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
...
388
389
390
391
392
393
394

395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416

417
418
419
420
421
422
423
424
425
426



427
428
429
430
431
432
433
...
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
...
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
...
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
...
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
...
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
...
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
  IdxConstraint *pNext;           /* Next constraint in pEq or pRange list */
  IdxConstraint *pLink;           /* See above */
};

/*
** A WHERE clause. Made up of IdxConstraint objects. Example WHERE clause:
**
**   a=? AND b=? AND ((c=? AND d=?) OR e=?) AND (f=? OR g=?) AND h>?
**
** The above is decomposed into 5 AND connected clauses. The first two are
** added to the IdxWhere.pEq linked list, the following two into 
** IdxWhere.pOr and the last into IdxWhere.pRange.
**
** IdxWhere.pEq and IdxWhere.pRange are simple linked lists of IdxConstraint
** objects linked by the IdxConstraint.pNext field.
**
** The list headed at IdxWhere.pOr and linked by IdxWhere.pNextOr contains
** all "OR" terms that belong to the current WHERE clause. In the example
** above, there are two OR terms:
**
**   ((c=? AND d=?) OR e=?)
**   (f=? OR g=?)
**
** Within an OR term, the OR connected sub-expressions are termed siblings.
** These are connected into a linked list by the pSibling pointers. Each OR
** term above consists of two siblings.
**
**   pOr -> (c=? AND d=?) -> pNextOr -> (f=?)
**               |                        |
**            pSibling                 pSibling
**               |                        |
**               V                        V
**             (e=?)                    (g=?)
**
** IdxWhere.pParent is only used while constructing a tree of IdxWhere 
** structures. It is NULL for the root IdxWhere. For all others, the parent
** WHERE clause.
*/
struct IdxWhere {
  IdxConstraint *pEq;             /* List of == constraints */
  IdxConstraint *pRange;          /* List of < constraints */
  IdxWhere *pOr;                  /* List of OR constraints */
  IdxWhere *pNextOr;              /* Next in OR constraints of same IdxWhere */
  IdxWhere *pSibling;             /* Next branch in single OR constraint */
  IdxWhere *pParent;              /* Parent object (or NULL) */
};

/*
** A single scan of a single table.
*/
struct IdxScan {
  IdxTable *pTable;               /* Table-info */
................................................................................
static void idxDatabaseError(
  sqlite3 *db,                    /* Database handle */
  char **pzErrmsg                 /* Write error here */
){
  *pzErrmsg = sqlite3_mprintf("%s", sqlite3_errmsg(db));
}

static char *idxQueryToList(
  sqlite3 *db, 
  const char *zBind,
  int *pRc,
  char **pzErrmsg,
  const char *zSql
){
  char *zRet = 0;
  if( *pRc==SQLITE_OK ){
    sqlite3_stmt *pStmt = 0;
    int rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0);
    if( rc==SQLITE_OK ){
      sqlite3_bind_text(pStmt, 1, zBind, -1, SQLITE_TRANSIENT);
      while( rc==SQLITE_OK && sqlite3_step(pStmt)==SQLITE_ROW ){
        const char *z = (const char*)sqlite3_column_text(pStmt, 0);
        zRet = sqlite3_mprintf("%z%s%Q", zRet, zRet?", ":"", z);
        if( zRet==0 ){
          rc = SQLITE_NOMEM;
        }
      }
      rc = sqlite3_finalize(pStmt);
    }

    if( rc ){
      idxDatabaseError(db, pzErrmsg);
      sqlite3_free(zRet);
      zRet = 0;
    }
    *pRc = rc;
  }

  return zRet;
}

static int idxPrepareStmt(
  sqlite3 *db,                    /* Database handle to compile against */
  sqlite3_stmt **ppStmt,          /* OUT: Compiled SQL statement */
  char **pzErrmsg,                /* OUT: sqlite3_malloc()ed error message */
  const char *zSql                /* SQL statement to compile */
){
  int rc = sqlite3_prepare_v2(db, zSql, -1, ppStmt, 0);
................................................................................
  int nByte = sizeof(IdxTable);
  IdxTable *pNew = 0;
  int rc, rc2;
  char *pCsr;

  rc = idxPrintfPrepareStmt(db, &p1, pzErrmsg, "PRAGMA table_info=%Q", zTbl);
  while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(p1) ){
    const char *zCol = sqlite3_column_text(p1, 1);
    nByte += 1 + strlen(zCol);
    rc = sqlite3_table_column_metadata(
        db, "main", zTbl, zCol, 0, &zCol, 0, 0, 0
    );
    nByte += 1 + strlen(zCol);
    nCol++;
  }
................................................................................
    pNew->aCol = (IdxColumn*)&pNew[1];
    pNew->nCol = nCol;
    pCsr = (char*)&pNew->aCol[nCol];
  }

  nCol = 0;
  while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(p1) ){
    const char *zCol = sqlite3_column_text(p1, 1);
    int nCopy = strlen(zCol) + 1;
    pNew->aCol[nCol].zName = pCsr;
    pNew->aCol[nCol].iPk = sqlite3_column_int(p1, 5);
    memcpy(pCsr, zCol, nCopy);
    pCsr += nCopy;

    rc = sqlite3_table_column_metadata(
................................................................................
static int idxCreateTables(
  sqlite3 *db,                    /* User database */
  sqlite3 *dbm,                   /* In-memory database to create tables in */
  IdxScan *pScan,                 /* List of scans */
  char **pzErrmsg                 /* OUT: Error message */
){
  int rc = SQLITE_OK;

  IdxScan *pIter;
  for(pIter=pScan; pIter && rc==SQLITE_OK; pIter=pIter->pNextScan){
    rc = idxGetTableInfo(db, pIter, pzErrmsg);

    /* Test if table has already been created. If so, jump to the next
    ** iteration of the loop.  */
    if( rc==SQLITE_OK ){
      sqlite3_stmt *pSql = 0;
      rc = idxPrintfPrepareStmt(dbm, &pSql, pzErrmsg, 
          "SELECT 1 FROM sqlite_master WHERE tbl_name = %Q", pIter->zTable
      );
      if( rc==SQLITE_OK ){
        int bSkip = 0;
        if( sqlite3_step(pSql)==SQLITE_ROW ) bSkip = 1;
        rc = sqlite3_finalize(pSql);
        if( bSkip ) continue;
      }
    }

    if( rc==SQLITE_OK ){
      int rc2;
      sqlite3_stmt *pSql = 0;

      rc = idxPrintfPrepareStmt(db, &pSql, pzErrmsg, 
          "SELECT sql FROM sqlite_master WHERE tbl_name = %Q", pIter->zTable
      );
      while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pSql) ){
        const char *zSql = (const char*)sqlite3_column_text(pSql, 0);
        rc = sqlite3_exec(dbm, zSql, 0, 0, pzErrmsg);
      }
      rc2 = sqlite3_finalize(pSql);
      if( rc==SQLITE_OK ) rc = rc2;
    }



  }
  return rc;
}

/*
** This function is a no-op if *pRc is set to anything other than 
** SQLITE_OK when it is called.
................................................................................
  return rc;
}

static int idxCreateFromWhere(
    IdxContext*, i64, IdxScan*, IdxWhere*, IdxConstraint*, IdxConstraint*
);

static int idxCreateForeachOr(
  IdxContext *pCtx, 
  i64 mask,                       /* Consider only these constraints */
  IdxScan *pScan,                 /* Create indexes for this scan */
  IdxWhere *pWhere,               /* Read constraints from here */
  IdxConstraint *pEq,             /* == constraints for inclusion */
  IdxConstraint *pTail            /* range/ORDER BY constraints for inclusion */
){
  int rc = SQLITE_OK;
  IdxWhere *p1;
  IdxWhere *p2;
  for(p1=pWhere->pOr; p1 && rc==SQLITE_OK; p1=p1->pNextOr){
    rc = idxCreateFromWhere(pCtx, mask, pScan, p1, pEq, pTail);
    for(p2=p1->pSibling; p2 && rc==SQLITE_OK; p2=p2->pSibling){
      rc = idxCreateFromWhere(pCtx, mask, pScan, p2, pEq, pTail);
    }
  }
  return rc;
}

/*
** Return true if list pList (linked by IdxConstraint.pLink) contains
** a constraint compatible with *p. Otherwise return false.
*/
static int idxFindConstraint(IdxConstraint *pList, IdxConstraint *p){
  IdxConstraint *pCmp;
  for(pCmp=pList; pCmp; pCmp=pCmp->pLink){
................................................................................
  IdxContext *pCtx, 
  i64 mask,                       /* Consider only these constraints */
  IdxScan *pScan,                 /* Create indexes for this scan */
  IdxWhere *pWhere,               /* Read constraints from here */
  IdxConstraint *pEq,             /* == constraints for inclusion */
  IdxConstraint *pTail            /* range/ORDER BY constraints for inclusion */
){
  sqlite3 *dbm = pCtx->dbm;
  IdxConstraint *p1 = pEq;
  IdxConstraint *pCon;
  int rc;

  /* Gather up all the == constraints that match the mask. */
  for(pCon=pWhere->pEq; pCon; pCon=pCon->pNext){
    if( (mask & pCon->depmask)==pCon->depmask 
................................................................................
      p1 = pCon;
    }
  }

  /* Create an index using the == constraints collected above. And the
  ** range constraint/ORDER BY terms passed in by the caller, if any. */
  rc = idxCreateFromCons(pCtx, pScan, p1, pTail);
  if( rc==SQLITE_OK ){
    rc = idxCreateForeachOr(pCtx, mask, pScan, pWhere, p1, pTail);
  }

  /* If no range/ORDER BY passed by the caller, create a version of the
  ** index for each range constraint that matches the mask. */
  if( pTail==0 ){
    for(pCon=pWhere->pRange; rc==SQLITE_OK && pCon; pCon=pCon->pNext){
      assert( pCon->pLink==0 );
      if( (mask & pCon->depmask)==pCon->depmask
        && idxFindConstraint(pEq, pCon)==0
        && idxFindConstraint(pTail, pCon)==0
      ){
        rc = idxCreateFromCons(pCtx, pScan, p1, pCon);
        if( rc==SQLITE_OK ){
          rc = idxCreateForeachOr(pCtx, mask, pScan, pWhere, p1, pCon);
        }
      }
    }
  }

  return rc;
}

................................................................................
static int idxCreateCandidates(IdxContext *pCtx){
  sqlite3 *dbm = pCtx->dbm;
  int rc2;
  int rc = SQLITE_OK;
  sqlite3_stmt *pDepmask;         /* Foreach depmask */
  IdxScan *pIter;

  rc = idxPrepareStmt(pCtx->dbm, &pDepmask, pCtx->pzErrmsg, 
      "SELECT mask FROM depmask"
  );

  for(pIter=pCtx->pScan; pIter && rc==SQLITE_OK; pIter=pIter->pNextScan){
    IdxWhere *pWhere = &pIter->where;
    while( SQLITE_ROW==sqlite3_step(pDepmask) && rc==SQLITE_OK ){
      i64 mask = sqlite3_column_int64(pDepmask, 0);
................................................................................
}

static void idxScanFree(IdxScan *pScan){
  IdxScan *pIter;
  IdxScan *pNext;
  for(pIter=pScan; pIter; pIter=pNext){
    pNext = pIter->pNextScan;

  }
}

int idxFindIndexes(
  IdxContext *pCtx,
  const char *zSql,                    /* SQL to find indexes for */
  void (*xOut)(void*, const char*),    /* Output callback */
................................................................................
      }
    }
  }
  rc2 = sqlite3_reset(pExplain);
  if( rc==SQLITE_OK ) rc = rc2;
  if( rc==SQLITE_OK ){
    sqlite3_stmt *pLoop = 0;
    rc = idxPrepareStmt(dbm, &pLoop, pzErr, "SELECT name FROM aux.indexes");
    if( rc==SQLITE_OK ){
      while( SQLITE_ROW==sqlite3_step(pLoop) ){
        bFound = 1;
        xOut(pOutCtx, sqlite3_column_text(pLoop, 0));
      }
      rc = sqlite3_finalize(pLoop);
    }
    if( rc==SQLITE_OK ){
      if( bFound==0 ) xOut(pOutCtx, "(no new indexes)");
      xOut(pOutCtx, "");
    }







|

|

|



<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<




<
<
<
<







 







<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<







 







|







 







|







 







>

<
<
<
<
<
<
|
<
<
<
<
<
<
<
<
|
<
<
<
<
<
>
|
|
|
|
|
|
|
|
|
|
>
>
>







 







<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<







 







<







 







<
<
<











<
<
<







 







|







 







<







 







|



|







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
...
216
217
218
219
220
221
222


































223
224
225
226
227
228
229
...
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
...
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
...
328
329
330
331
332
333
334
335
336






337








338





339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
...
577
578
579
580
581
582
583




















584
585
586
587
588
589
590
...
597
598
599
600
601
602
603

604
605
606
607
608
609
610
...
615
616
617
618
619
620
621



622
623
624
625
626
627
628
629
630
631
632



633
634
635
636
637
638
639
...
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
...
669
670
671
672
673
674
675

676
677
678
679
680
681
682
...
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
  IdxConstraint *pNext;           /* Next constraint in pEq or pRange list */
  IdxConstraint *pLink;           /* See above */
};

/*
** A WHERE clause. Made up of IdxConstraint objects. Example WHERE clause:
**
**   a=? AND b=? AND c=? AND d=? AND e>? AND f<?
**
** The above is decomposed into 6 AND connected clauses. The first four are
** added to the IdxWhere.pEq linked list, the following two into 
** IdxWhere.pRange.
**
** IdxWhere.pEq and IdxWhere.pRange are simple linked lists of IdxConstraint
** objects linked by the IdxConstraint.pNext field.






















*/
struct IdxWhere {
  IdxConstraint *pEq;             /* List of == constraints */
  IdxConstraint *pRange;          /* List of < constraints */




};

/*
** A single scan of a single table.
*/
struct IdxScan {
  IdxTable *pTable;               /* Table-info */
................................................................................
static void idxDatabaseError(
  sqlite3 *db,                    /* Database handle */
  char **pzErrmsg                 /* Write error here */
){
  *pzErrmsg = sqlite3_mprintf("%s", sqlite3_errmsg(db));
}



































static int idxPrepareStmt(
  sqlite3 *db,                    /* Database handle to compile against */
  sqlite3_stmt **ppStmt,          /* OUT: Compiled SQL statement */
  char **pzErrmsg,                /* OUT: sqlite3_malloc()ed error message */
  const char *zSql                /* SQL statement to compile */
){
  int rc = sqlite3_prepare_v2(db, zSql, -1, ppStmt, 0);
................................................................................
  int nByte = sizeof(IdxTable);
  IdxTable *pNew = 0;
  int rc, rc2;
  char *pCsr;

  rc = idxPrintfPrepareStmt(db, &p1, pzErrmsg, "PRAGMA table_info=%Q", zTbl);
  while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(p1) ){
    const char *zCol = (const char*)sqlite3_column_text(p1, 1);
    nByte += 1 + strlen(zCol);
    rc = sqlite3_table_column_metadata(
        db, "main", zTbl, zCol, 0, &zCol, 0, 0, 0
    );
    nByte += 1 + strlen(zCol);
    nCol++;
  }
................................................................................
    pNew->aCol = (IdxColumn*)&pNew[1];
    pNew->nCol = nCol;
    pCsr = (char*)&pNew->aCol[nCol];
  }

  nCol = 0;
  while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(p1) ){
    const char *zCol = (const char*)sqlite3_column_text(p1, 1);
    int nCopy = strlen(zCol) + 1;
    pNew->aCol[nCol].zName = pCsr;
    pNew->aCol[nCol].iPk = sqlite3_column_int(p1, 5);
    memcpy(pCsr, zCol, nCopy);
    pCsr += nCopy;

    rc = sqlite3_table_column_metadata(
................................................................................
static int idxCreateTables(
  sqlite3 *db,                    /* User database */
  sqlite3 *dbm,                   /* In-memory database to create tables in */
  IdxScan *pScan,                 /* List of scans */
  char **pzErrmsg                 /* OUT: Error message */
){
  int rc = SQLITE_OK;
  int rc2;
  IdxScan *pIter;






  sqlite3_stmt *pSql = 0;














  /* Copy the entire schema of database [db] into [dbm]. */
  rc = idxPrintfPrepareStmt(db, &pSql, pzErrmsg, 
      "SELECT sql FROM sqlite_master WHERE name NOT LIKE 'sqlite_%%'"
  );
  while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pSql) ){
    const char *zSql = (const char*)sqlite3_column_text(pSql, 0);
    rc = sqlite3_exec(dbm, zSql, 0, 0, pzErrmsg);
  }
  rc2 = sqlite3_finalize(pSql);
  if( rc==SQLITE_OK ) rc = rc2;

  /* Load IdxTable objects */
  for(pIter=pScan; pIter && rc==SQLITE_OK; pIter=pIter->pNextScan){
    rc = idxGetTableInfo(db, pIter, pzErrmsg);
  }
  return rc;
}

/*
** This function is a no-op if *pRc is set to anything other than 
** SQLITE_OK when it is called.
................................................................................
  return rc;
}

static int idxCreateFromWhere(
    IdxContext*, i64, IdxScan*, IdxWhere*, IdxConstraint*, IdxConstraint*
);





















/*
** Return true if list pList (linked by IdxConstraint.pLink) contains
** a constraint compatible with *p. Otherwise return false.
*/
static int idxFindConstraint(IdxConstraint *pList, IdxConstraint *p){
  IdxConstraint *pCmp;
  for(pCmp=pList; pCmp; pCmp=pCmp->pLink){
................................................................................
  IdxContext *pCtx, 
  i64 mask,                       /* Consider only these constraints */
  IdxScan *pScan,                 /* Create indexes for this scan */
  IdxWhere *pWhere,               /* Read constraints from here */
  IdxConstraint *pEq,             /* == constraints for inclusion */
  IdxConstraint *pTail            /* range/ORDER BY constraints for inclusion */
){

  IdxConstraint *p1 = pEq;
  IdxConstraint *pCon;
  int rc;

  /* Gather up all the == constraints that match the mask. */
  for(pCon=pWhere->pEq; pCon; pCon=pCon->pNext){
    if( (mask & pCon->depmask)==pCon->depmask 
................................................................................
      p1 = pCon;
    }
  }

  /* Create an index using the == constraints collected above. And the
  ** range constraint/ORDER BY terms passed in by the caller, if any. */
  rc = idxCreateFromCons(pCtx, pScan, p1, pTail);




  /* If no range/ORDER BY passed by the caller, create a version of the
  ** index for each range constraint that matches the mask. */
  if( pTail==0 ){
    for(pCon=pWhere->pRange; rc==SQLITE_OK && pCon; pCon=pCon->pNext){
      assert( pCon->pLink==0 );
      if( (mask & pCon->depmask)==pCon->depmask
        && idxFindConstraint(pEq, pCon)==0
        && idxFindConstraint(pTail, pCon)==0
      ){
        rc = idxCreateFromCons(pCtx, pScan, p1, pCon);



      }
    }
  }

  return rc;
}

................................................................................
static int idxCreateCandidates(IdxContext *pCtx){
  sqlite3 *dbm = pCtx->dbm;
  int rc2;
  int rc = SQLITE_OK;
  sqlite3_stmt *pDepmask;         /* Foreach depmask */
  IdxScan *pIter;

  rc = idxPrepareStmt(dbm, &pDepmask, pCtx->pzErrmsg, 
      "SELECT mask FROM depmask"
  );

  for(pIter=pCtx->pScan; pIter && rc==SQLITE_OK; pIter=pIter->pNextScan){
    IdxWhere *pWhere = &pIter->where;
    while( SQLITE_ROW==sqlite3_step(pDepmask) && rc==SQLITE_OK ){
      i64 mask = sqlite3_column_int64(pDepmask, 0);
................................................................................
}

static void idxScanFree(IdxScan *pScan){
  IdxScan *pIter;
  IdxScan *pNext;
  for(pIter=pScan; pIter; pIter=pNext){
    pNext = pIter->pNextScan;

  }
}

int idxFindIndexes(
  IdxContext *pCtx,
  const char *zSql,                    /* SQL to find indexes for */
  void (*xOut)(void*, const char*),    /* Output callback */
................................................................................
      }
    }
  }
  rc2 = sqlite3_reset(pExplain);
  if( rc==SQLITE_OK ) rc = rc2;
  if( rc==SQLITE_OK ){
    sqlite3_stmt *pLoop = 0;
    rc = idxPrepareStmt(dbm, &pLoop, pzErr,"SELECT name||';' FROM aux.indexes");
    if( rc==SQLITE_OK ){
      while( SQLITE_ROW==sqlite3_step(pLoop) ){
        bFound = 1;
        xOut(pOutCtx, (const char*)sqlite3_column_text(pLoop, 0));
      }
      rc = sqlite3_finalize(pLoop);
    }
    if( rc==SQLITE_OK ){
      if( bFound==0 ) xOut(pOutCtx, "(no new indexes)");
      xOut(pOutCtx, "");
    }

Changes to test/shell6.test.

62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
}

do_setup_rec_test 1.2 {
  CREATE TABLE t1(a, b, c);
} {
  SELECT * FROM t1 WHERE b>?;
} {
  CREATE INDEX t1_idx_00000062 ON t1(b) 
  0|0|0|SEARCH TABLE t1 USING INDEX t1_idx_00000062 (b>?)
}

do_setup_rec_test 1.3 {
  CREATE TABLE t1(a, b, c);
} {
  SELECT * FROM t1 WHERE b COLLATE nocase BETWEEN ? AND ?
} {
  CREATE INDEX t1_idx_3e094c27 ON t1(b COLLATE NOCASE) 
  0|0|0|SEARCH TABLE t1 USING INDEX t1_idx_3e094c27 (b>? AND b<?)
}

do_setup_rec_test 1.4 {
  CREATE TABLE t1(a, b, c);
} {
  SELECT a FROM t1 ORDER BY b;
} {
  CREATE INDEX t1_idx_00000062 ON t1(b) 
  0|0|0|SCAN TABLE t1 USING INDEX t1_idx_00000062
}

do_setup_rec_test 1.5 {
  CREATE TABLE t1(a, b, c);
} {
  SELECT a FROM t1 WHERE a=? ORDER BY b;
} {
  CREATE INDEX t1_idx_000123a7 ON t1(a, b) 
  0|0|0|SEARCH TABLE t1 USING COVERING INDEX t1_idx_000123a7 (a=?)
}

do_setup_rec_test 1.6 {
  CREATE TABLE t1(a, b, c);
} {
  SELECT min(a) FROM t1
} {
  CREATE INDEX t1_idx_00000061 ON t1(a) 
  0|0|0|SEARCH TABLE t1 USING COVERING INDEX t1_idx_00000061
}

do_setup_rec_test 1.7 {
  CREATE TABLE t1(a, b, c);
} {
  SELECT * FROM t1 ORDER BY a, b, c;
} {
  CREATE INDEX t1_idx_033e95fe ON t1(a, b, c) 
  0|0|0|SCAN TABLE t1 USING COVERING INDEX t1_idx_033e95fe
}

do_setup_rec_test 1.8 {
  CREATE TABLE t1(a, b, c);
} {
  SELECT * FROM t1 ORDER BY a ASC, b COLLATE nocase DESC, c ASC;
} {
  CREATE INDEX t1_idx_5be6e222 ON t1(a, b COLLATE NOCASE DESC, c)
  0|0|0|SCAN TABLE t1 USING COVERING INDEX t1_idx_5be6e222
}

do_setup_rec_test 1.9 {
  CREATE TABLE t1(a COLLATE NOCase, b, c);
} {
  SELECT * FROM t1 WHERE a=?
} {
  CREATE INDEX t1_idx_00000061 ON t1(a) 
  0|0|0|SEARCH TABLE t1 USING INDEX t1_idx_00000061 (a=?)
}


# Tables with names that require quotes.
#
do_setup_rec_test 8.1 {
  CREATE TABLE "t t"(a, b, c);
} {
  SELECT * FROM "t t" WHERE a=?
} {
  CREATE INDEX 't t_idx_00000061' ON 't t'(a)
  0|0|0|SEARCH TABLE t t USING INDEX t t_idx_00000061 (a=?) 
}

do_setup_rec_test 8.2 {
  CREATE TABLE "t t"(a, b, c);
} {
  SELECT * FROM "t t" WHERE b BETWEEN ? AND ?
} {
  CREATE INDEX 't t_idx_00000062' ON 't t'(b) 
  0|0|0|SEARCH TABLE t t USING INDEX t t_idx_00000062 (b>? AND b<?)
}

# Columns with names that require quotes.
#
do_setup_rec_test 9.1 {
  CREATE TABLE t3(a, "b b", c);
} {
  SELECT * FROM t3 WHERE "b b" = ?
} {
  CREATE INDEX t3_idx_00050c52 ON t3('b b') 
  0|0|0|SEARCH TABLE t3 USING INDEX t3_idx_00050c52 (b b=?)
}

do_setup_rec_test 9.2 {
  CREATE TABLE t3(a, "b b", c);
} {
  SELECT * FROM t3 ORDER BY "b b"
} {
  CREATE INDEX t3_idx_00050c52 ON t3('b b') 
  0|0|0|SCAN TABLE t3 USING INDEX t3_idx_00050c52
}

# Transitive constraints
#
do_setup_rec_test 10.1 {
  CREATE TABLE t5(a, b);
  CREATE TABLE t6(c, d);
} {
  SELECT * FROM t5, t6 WHERE a=? AND b=c AND c=?
} {
  CREATE INDEX t5_idx_000123a7 ON t5(a, b) 
  CREATE INDEX t6_idx_00000063 ON t6(c) 
  0|0|1|SEARCH TABLE t6 USING INDEX t6_idx_00000063 (c=?) 
  0|1|0|SEARCH TABLE t5 USING COVERING INDEX t5_idx_000123a7 (a=? AND b=?)
}

# OR terms.
#
do_setup_rec_test 11.1 {
  CREATE TABLE t7(a, b);
} {
  SELECT * FROM t7 WHERE a=? OR b=?
} {
  CREATE INDEX t7_idx_00000061 ON t7(a) 
  CREATE INDEX t7_idx_00000062 ON t7(b)
  0|0|0|SEARCH TABLE t7 USING INDEX t7_idx_00000061 (a=?) 
  0|0|0|SEARCH TABLE t7 USING INDEX t7_idx_00000062 (b=?)
}

finish_test








|








|








|








|








|








|








|








|











|








|










|








|











|
|











|
|






62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
}

do_setup_rec_test 1.2 {
  CREATE TABLE t1(a, b, c);
} {
  SELECT * FROM t1 WHERE b>?;
} {
  CREATE INDEX t1_idx_00000062 ON t1(b);
  0|0|0|SEARCH TABLE t1 USING INDEX t1_idx_00000062 (b>?)
}

do_setup_rec_test 1.3 {
  CREATE TABLE t1(a, b, c);
} {
  SELECT * FROM t1 WHERE b COLLATE nocase BETWEEN ? AND ?
} {
  CREATE INDEX t1_idx_3e094c27 ON t1(b COLLATE NOCASE);
  0|0|0|SEARCH TABLE t1 USING INDEX t1_idx_3e094c27 (b>? AND b<?)
}

do_setup_rec_test 1.4 {
  CREATE TABLE t1(a, b, c);
} {
  SELECT a FROM t1 ORDER BY b;
} {
  CREATE INDEX t1_idx_00000062 ON t1(b);
  0|0|0|SCAN TABLE t1 USING INDEX t1_idx_00000062
}

do_setup_rec_test 1.5 {
  CREATE TABLE t1(a, b, c);
} {
  SELECT a FROM t1 WHERE a=? ORDER BY b;
} {
  CREATE INDEX t1_idx_000123a7 ON t1(a, b);
  0|0|0|SEARCH TABLE t1 USING COVERING INDEX t1_idx_000123a7 (a=?)
}

do_setup_rec_test 1.6 {
  CREATE TABLE t1(a, b, c);
} {
  SELECT min(a) FROM t1
} {
  CREATE INDEX t1_idx_00000061 ON t1(a);
  0|0|0|SEARCH TABLE t1 USING COVERING INDEX t1_idx_00000061
}

do_setup_rec_test 1.7 {
  CREATE TABLE t1(a, b, c);
} {
  SELECT * FROM t1 ORDER BY a, b, c;
} {
  CREATE INDEX t1_idx_033e95fe ON t1(a, b, c);
  0|0|0|SCAN TABLE t1 USING COVERING INDEX t1_idx_033e95fe
}

do_setup_rec_test 1.8 {
  CREATE TABLE t1(a, b, c);
} {
  SELECT * FROM t1 ORDER BY a ASC, b COLLATE nocase DESC, c ASC;
} {
  CREATE INDEX t1_idx_5be6e222 ON t1(a, b COLLATE NOCASE DESC, c);
  0|0|0|SCAN TABLE t1 USING COVERING INDEX t1_idx_5be6e222
}

do_setup_rec_test 1.9 {
  CREATE TABLE t1(a COLLATE NOCase, b, c);
} {
  SELECT * FROM t1 WHERE a=?
} {
  CREATE INDEX t1_idx_00000061 ON t1(a);
  0|0|0|SEARCH TABLE t1 USING INDEX t1_idx_00000061 (a=?)
}


# Tables with names that require quotes.
#
do_setup_rec_test 8.1 {
  CREATE TABLE "t t"(a, b, c);
} {
  SELECT * FROM "t t" WHERE a=?
} {
  CREATE INDEX 't t_idx_00000061' ON 't t'(a);
  0|0|0|SEARCH TABLE t t USING INDEX t t_idx_00000061 (a=?) 
}

do_setup_rec_test 8.2 {
  CREATE TABLE "t t"(a, b, c);
} {
  SELECT * FROM "t t" WHERE b BETWEEN ? AND ?
} {
  CREATE INDEX 't t_idx_00000062' ON 't t'(b);
  0|0|0|SEARCH TABLE t t USING INDEX t t_idx_00000062 (b>? AND b<?)
}

# Columns with names that require quotes.
#
do_setup_rec_test 9.1 {
  CREATE TABLE t3(a, "b b", c);
} {
  SELECT * FROM t3 WHERE "b b" = ?
} {
  CREATE INDEX t3_idx_00050c52 ON t3('b b');
  0|0|0|SEARCH TABLE t3 USING INDEX t3_idx_00050c52 (b b=?)
}

do_setup_rec_test 9.2 {
  CREATE TABLE t3(a, "b b", c);
} {
  SELECT * FROM t3 ORDER BY "b b"
} {
  CREATE INDEX t3_idx_00050c52 ON t3('b b');
  0|0|0|SCAN TABLE t3 USING INDEX t3_idx_00050c52
}

# Transitive constraints
#
do_setup_rec_test 10.1 {
  CREATE TABLE t5(a, b);
  CREATE TABLE t6(c, d);
} {
  SELECT * FROM t5, t6 WHERE a=? AND b=c AND c=?
} {
  CREATE INDEX t5_idx_000123a7 ON t5(a, b);
  CREATE INDEX t6_idx_00000063 ON t6(c);
  0|0|1|SEARCH TABLE t6 USING INDEX t6_idx_00000063 (c=?) 
  0|1|0|SEARCH TABLE t5 USING COVERING INDEX t5_idx_000123a7 (a=? AND b=?)
}

# OR terms.
#
do_setup_rec_test 11.1 {
  CREATE TABLE t7(a, b);
} {
  SELECT * FROM t7 WHERE a=? OR b=?
} {
  CREATE INDEX t7_idx_00000061 ON t7(a);
  CREATE INDEX t7_idx_00000062 ON t7(b);
  0|0|0|SEARCH TABLE t7 USING INDEX t7_idx_00000061 (a=?) 
  0|0|0|SEARCH TABLE t7 USING INDEX t7_idx_00000062 (b=?)
}

finish_test