SQLite

Check-in [8922be1a3e]
Login

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

Overview
Comment:Merge updates from trunk.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | toTypeFuncs
Files: files | file ages | folders
SHA1: 8922be1a3e0269552e12b87fe1e5141c38a8d3f8
User & Date: mistachkin 2013-09-06 22:27:55.935
Context
2013-09-24
19:07
Merge updates from trunk. (check-in: 435ce3b3fc user: mistachkin tags: toTypeFuncs)
2013-09-06
22:27
Merge updates from trunk. (check-in: 8922be1a3e user: mistachkin tags: toTypeFuncs)
21:41
Add the ability to generate assembly listing files using the MSVC makefile. (check-in: 6caa2cd104 user: mistachkin tags: toTypeFuncs)
13:10
Combine the FuncDef.iPrefEnc and FuncDef.flags fields into a single new FuncDef.funcFlags field. (check-in: 97b10e66e9 user: drh tags: trunk)
Changes
Unified Diff Show Whitespace Changes Patch
Changes to src/analyze.c.
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385





























386
387
388



389
390




391
392
393
394
395
396
397

398
399
400
401
402
403



404
405
406
407
408
409
410
#endif

  /* Return a pointer to the allocated object to the caller */
  sqlite3_result_blob(context, p, sizeof(p), sqlite3_free);
}
static const FuncDef statInitFuncdef = {
  1+IsStat34,      /* nArg */
  SQLITE_UTF8,     /* iPrefEnc */
  0,               /* flags */
  0,               /* pUserData */
  0,               /* pNext */
  statInit,        /* xFunc */
  0,               /* xStep */
  0,               /* xFinalize */
  "stat_init",     /* zName */
  0,               /* pHash */
  0                /* pDestructor */
};






























#ifdef SQLITE_ENABLE_STAT3_OR_STAT4
/*
** Return true if pNew is to be preferred over pOld.



*/
static int sampleIsBetter(Stat4Sample *pNew, Stat4Sample *pOld){




  tRowcnt nEqNew = pNew->anEq[pNew->iCol];
  tRowcnt nEqOld = pOld->anEq[pOld->iCol];

  assert( pOld->isPSample==0 && pNew->isPSample==0 );
  assert( IsStat4 || (pNew->iCol==0 && pOld->iCol==0) );

  if( (nEqNew>nEqOld)

   || (nEqNew==nEqOld && pNew->iCol<pOld->iCol)
   || (nEqNew==nEqOld && pNew->iCol==pOld->iCol && pNew->iHash>pOld->iHash)
  ){
    return 1;
  }
  return 0;



}

/*
** Copy the contents of object (*pFrom) into (*pTo).
*/
void sampleCopy(Stat4Accum *p, Stat4Sample *pTo, Stat4Sample *pFrom){
  pTo->iRowid = pFrom->iRowid;







|
<










>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



>
>
>

|
>
>
>
>






|
>
|
|
<
|


>
>
>







367
368
369
370
371
372
373
374

375
376
377
378
379
380
381
382
383
384
385
386
387
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
434
435

436
437
438
439
440
441
442
443
444
445
446
447
448
#endif

  /* Return a pointer to the allocated object to the caller */
  sqlite3_result_blob(context, p, sizeof(p), sqlite3_free);
}
static const FuncDef statInitFuncdef = {
  1+IsStat34,      /* nArg */
  SQLITE_UTF8,     /* funcFlags */

  0,               /* pUserData */
  0,               /* pNext */
  statInit,        /* xFunc */
  0,               /* xStep */
  0,               /* xFinalize */
  "stat_init",     /* zName */
  0,               /* pHash */
  0                /* pDestructor */
};

#ifdef SQLITE_ENABLE_STAT4
/*
** pNew and pOld are both candidate non-periodic samples selected for 
** the same column (pNew->iCol==pOld->iCol). Ignoring this column and 
** considering only any trailing columns and the sample hash value, this
** function returns true if sample pNew is to be preferred over pOld.
** In other words, if we assume that the cardinalities of the selected
** column for pNew and pOld are equal, is pNew to be preferred over pOld.
**
** This function assumes that for each argument sample, the contents of
** the anEq[] array from pSample->anEq[pSample->iCol+1] onwards are valid. 
*/
static int sampleIsBetterPost(
  Stat4Accum *pAccum, 
  Stat4Sample *pNew, 
  Stat4Sample *pOld
){
  int nCol = pAccum->nCol;
  int i;
  assert( pNew->iCol==pOld->iCol );
  for(i=pNew->iCol+1; i<nCol; i++){
    if( pNew->anEq[i]>pOld->anEq[i] ) return 1;
    if( pNew->anEq[i]<pOld->anEq[i] ) return 0;
  }
  if( pNew->iHash>pOld->iHash ) return 1;
  return 0;
}
#endif

#ifdef SQLITE_ENABLE_STAT3_OR_STAT4
/*
** Return true if pNew is to be preferred over pOld.
**
** This function assumes that for each argument sample, the contents of
** the anEq[] array from pSample->anEq[pSample->iCol] onwards are valid. 
*/
static int sampleIsBetter(
  Stat4Accum *pAccum, 
  Stat4Sample *pNew, 
  Stat4Sample *pOld
){
  tRowcnt nEqNew = pNew->anEq[pNew->iCol];
  tRowcnt nEqOld = pOld->anEq[pOld->iCol];

  assert( pOld->isPSample==0 && pNew->isPSample==0 );
  assert( IsStat4 || (pNew->iCol==0 && pOld->iCol==0) );

  if( (nEqNew>nEqOld) ) return 1;
#ifdef SQLITE_ENABLE_STAT4
  if( nEqNew==nEqOld ){
    if( pNew->iCol<pOld->iCol ) return 1;

    return (pNew->iCol==pOld->iCol && sampleIsBetterPost(pAccum, pNew, pOld));
  }
  return 0;
#else
  return (nEqNew==nEqOld && pNew->iHash>pOld->iHash);
#endif
}

/*
** Copy the contents of object (*pFrom) into (*pTo).
*/
void sampleCopy(Stat4Accum *p, Stat4Sample *pTo, Stat4Sample *pFrom){
  pTo->iRowid = pFrom->iRowid;
419
420
421
422
423
424
425
426
427
428
429
430

431
432
433
434
435
436
437
438
439
440
441
442
443

444
445
446
447
448
449
450
451
452
453
454
455

456
457
458
459
460
461
462
463
464
465
466
467
468
469
470


471
472

473
474

475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494

495

496
497
498
499
500
501
502
503
504
505
506
507
/*
** Copy the contents of sample *pNew into the p->a[] array. If necessary,
** remove the least desirable sample from p->a[] to make room.
*/
static void sampleInsert(Stat4Accum *p, Stat4Sample *pNew, int nEqZero){
  Stat4Sample *pSample;
  int i;
  i64 iSeq;
  int iPos;

  assert( IsStat4 || nEqZero==0 );


  if( pNew->isPSample==0 ){
    Stat4Sample *pUpgrade = 0;
    assert( pNew->anEq[pNew->iCol]>0 );

    /* This sample is being added because the prefix that ends in column 
    ** iCol occurs many times in the table. However, if we have already
    ** added a sample that shares this prefix, there is no need to add
    ** this one. Instead, upgrade the priority of the highest priority
    ** existing sample that shares this prefix.  */
    for(i=p->nSample-1; i>=0; i--){
      Stat4Sample *pOld = &p->a[i];
      if( pOld->anEq[pNew->iCol]==0 ){
        if( pOld->isPSample ) return;

        assert( sampleIsBetter(pNew, pOld) );
        if( pUpgrade==0 || sampleIsBetter(pOld, pUpgrade) ){
          pUpgrade = pOld;
        }
      }
    }
    if( pUpgrade ){
      pUpgrade->iCol = pNew->iCol;
      pUpgrade->anEq[pUpgrade->iCol] = pNew->anEq[pUpgrade->iCol];
      goto find_new_min;
    }
  }


  /* If necessary, remove sample iMin to make room for the new sample. */
  if( p->nSample>=p->mxSample ){
    Stat4Sample *pMin = &p->a[p->iMin];
    tRowcnt *anEq = pMin->anEq;
    tRowcnt *anLt = pMin->anLt;
    tRowcnt *anDLt = pMin->anDLt;
    memmove(pMin, &pMin[1], sizeof(p->a[0])*(p->nSample-p->iMin-1));
    pSample = &p->a[p->nSample-1];
    pSample->anEq = anEq;
    pSample->anDLt = anDLt;
    pSample->anLt = anLt;
    p->nSample = p->mxSample-1;
  }



  /* Figure out where in the a[] array the new sample should be inserted. */
  iSeq = pNew->anLt[p->nCol-1];

  for(iPos=p->nSample; iPos>0; iPos--){
    if( iSeq>p->a[iPos-1].anLt[p->nCol-1] ) break;

  }

  /* Insert the new sample */
  pSample = &p->a[iPos];
  if( iPos!=p->nSample ){
    Stat4Sample *pEnd = &p->a[p->nSample];
    tRowcnt *anEq = pEnd->anEq;
    tRowcnt *anLt = pEnd->anLt;
    tRowcnt *anDLt = pEnd->anDLt;
    memmove(&p->a[iPos], &p->a[iPos+1], (p->nSample-iPos)*sizeof(p->a[0]));
    pSample->anEq = anEq;
    pSample->anDLt = anDLt;
    pSample->anLt = anLt;
  }
  p->nSample++;
  sampleCopy(p, pSample, pNew);

  /* Zero the first nEqZero entries in the anEq[] array. */
  memset(pSample->anEq, 0, sizeof(tRowcnt)*nEqZero);


 find_new_min:

  if( p->nSample>=p->mxSample ){
    int iMin = -1;
    for(i=0; i<p->mxSample; i++){
      if( p->a[i].isPSample ) continue;
      if( iMin<0 || sampleIsBetter(&p->a[iMin], &p->a[i]) ){
        iMin = i;
      }
    }
    assert( iMin>=0 );
    p->iMin = iMin;
  }
}







<
<



>













>
|
|










>















>
>
|
<
>
|
|
>
|
<

|
<
<
<
<
<
<
|
<
<
<

<




>

>




|







457
458
459
460
461
462
463


464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512

513
514
515
516
517

518
519






520



521

522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
/*
** Copy the contents of sample *pNew into the p->a[] array. If necessary,
** remove the least desirable sample from p->a[] to make room.
*/
static void sampleInsert(Stat4Accum *p, Stat4Sample *pNew, int nEqZero){
  Stat4Sample *pSample;
  int i;



  assert( IsStat4 || nEqZero==0 );

#ifdef SQLITE_ENABLE_STAT4
  if( pNew->isPSample==0 ){
    Stat4Sample *pUpgrade = 0;
    assert( pNew->anEq[pNew->iCol]>0 );

    /* This sample is being added because the prefix that ends in column 
    ** iCol occurs many times in the table. However, if we have already
    ** added a sample that shares this prefix, there is no need to add
    ** this one. Instead, upgrade the priority of the highest priority
    ** existing sample that shares this prefix.  */
    for(i=p->nSample-1; i>=0; i--){
      Stat4Sample *pOld = &p->a[i];
      if( pOld->anEq[pNew->iCol]==0 ){
        if( pOld->isPSample ) return;
        assert( pOld->iCol>pNew->iCol );
        assert( sampleIsBetter(p, pNew, pOld) );
        if( pUpgrade==0 || sampleIsBetter(p, pOld, pUpgrade) ){
          pUpgrade = pOld;
        }
      }
    }
    if( pUpgrade ){
      pUpgrade->iCol = pNew->iCol;
      pUpgrade->anEq[pUpgrade->iCol] = pNew->anEq[pUpgrade->iCol];
      goto find_new_min;
    }
  }
#endif

  /* If necessary, remove sample iMin to make room for the new sample. */
  if( p->nSample>=p->mxSample ){
    Stat4Sample *pMin = &p->a[p->iMin];
    tRowcnt *anEq = pMin->anEq;
    tRowcnt *anLt = pMin->anLt;
    tRowcnt *anDLt = pMin->anDLt;
    memmove(pMin, &pMin[1], sizeof(p->a[0])*(p->nSample-p->iMin-1));
    pSample = &p->a[p->nSample-1];
    pSample->anEq = anEq;
    pSample->anDLt = anDLt;
    pSample->anLt = anLt;
    p->nSample = p->mxSample-1;
  }

  /* The "rows less-than" for the rowid column must be greater than that
  ** for the last sample in the p->a[] array. Otherwise, the samples would
  ** be out of order. */

#ifdef SQLITE_ENABLE_STAT4
  assert( p->nSample==0 
       || pNew->anLt[p->nCol-1] > p->a[p->nSample-1].anLt[p->nCol-1] );
#endif


  /* Insert the new sample */
  pSample = &p->a[p->nSample];






  sampleCopy(p, pSample, pNew);



  p->nSample++;


  /* Zero the first nEqZero entries in the anEq[] array. */
  memset(pSample->anEq, 0, sizeof(tRowcnt)*nEqZero);

#ifdef SQLITE_ENABLE_STAT4
 find_new_min:
#endif
  if( p->nSample>=p->mxSample ){
    int iMin = -1;
    for(i=0; i<p->mxSample; i++){
      if( p->a[i].isPSample ) continue;
      if( iMin<0 || sampleIsBetter(p, &p->a[iMin], &p->a[i]) ){
        iMin = i;
      }
    }
    assert( iMin>=0 );
    p->iMin = iMin;
  }
}
517
518
519
520
521
522
523

524
525
526
527
528
529
530
531
532
533
#ifdef SQLITE_ENABLE_STAT4
  int i;

  /* Check if any samples from the aBest[] array should be pushed
  ** into IndexSample.a[] at this point.  */
  for(i=(p->nCol-2); i>=iChng; i--){
    Stat4Sample *pBest = &p->aBest[i];

    if( p->nSample<p->mxSample
     || sampleIsBetter(pBest, &p->a[p->iMin])
    ){
      sampleInsert(p, pBest, i);
    }
  }

  /* Update the anEq[] fields of any samples already collected. */
  for(i=p->nSample-1; i>=0; i--){
    int j;







>
|
<
<







550
551
552
553
554
555
556
557
558


559
560
561
562
563
564
565
#ifdef SQLITE_ENABLE_STAT4
  int i;

  /* Check if any samples from the aBest[] array should be pushed
  ** into IndexSample.a[] at this point.  */
  for(i=(p->nCol-2); i>=iChng; i--){
    Stat4Sample *pBest = &p->aBest[i];
    pBest->anEq[i] = p->current.anEq[i];
    if( p->nSample<p->mxSample || sampleIsBetter(p, pBest, &p->a[p->iMin]) ){


      sampleInsert(p, pBest, i);
    }
  }

  /* Update the anEq[] fields of any samples already collected. */
  for(i=p->nSample-1; i>=0; i--){
    int j;
546
547
548
549
550
551
552
553


554
555
556
557
558
559
560
    if( (nLt/p->nPSample)!=(nLt+nEq)/p->nPSample ){
      p->current.isPSample = 1;
      sampleInsert(p, &p->current, 0);
      p->current.isPSample = 0;
    }else 

    /* Or if it is a non-periodic sample. Add it in this case too. */
    if( p->nSample<p->mxSample || sampleIsBetter(&p->current, &p->a[p->iMin]) ){


      sampleInsert(p, &p->current, 0);
    }
  }
#endif
}

/*







|
>
>







578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
    if( (nLt/p->nPSample)!=(nLt+nEq)/p->nPSample ){
      p->current.isPSample = 1;
      sampleInsert(p, &p->current, 0);
      p->current.isPSample = 0;
    }else 

    /* Or if it is a non-periodic sample. Add it in this case too. */
    if( p->nSample<p->mxSample 
     || sampleIsBetter(p, &p->current, &p->a[p->iMin]) 
    ){
      sampleInsert(p, &p->current, 0);
    }
  }
#endif
}

/*
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
  Stat4Accum *p = (Stat4Accum*)sqlite3_value_blob(argv[0]);
  int iChng = sqlite3_value_int(argv[1]);

  assert( p->nCol>1 );        /* Includes rowid field */
  assert( iChng<p->nCol );

  if( p->nRow==0 ){
    /* anEq[0] is only zero for the very first call to this function.  Do
    ** appropriate initialization */
    for(i=0; i<p->nCol; i++) p->current.anEq[i] = 1;
  }else{
    /* Second and subsequent calls get processed here */
    samplePushPrevious(p, iChng);

    /* Update anDLt[], anLt[] and anEq[] to reflect the values that apply
    ** to the current row of the index. */







|
<







614
615
616
617
618
619
620
621

622
623
624
625
626
627
628
  Stat4Accum *p = (Stat4Accum*)sqlite3_value_blob(argv[0]);
  int iChng = sqlite3_value_int(argv[1]);

  assert( p->nCol>1 );        /* Includes rowid field */
  assert( iChng<p->nCol );

  if( p->nRow==0 ){
    /* This is the first call to this function. Do initialization. */

    for(i=0; i<p->nCol; i++) p->current.anEq[i] = 1;
  }else{
    /* Second and subsequent calls get processed here */
    samplePushPrevious(p, iChng);

    /* Update anDLt[], anLt[] and anEq[] to reflect the values that apply
    ** to the current row of the index. */
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
      sampleInsert(p, &p->current, p->nCol-1);
      p->current.isPSample = 0;
    }

    /* Update the aBest[] array. */
    for(i=0; i<(p->nCol-1); i++){
      p->current.iCol = i;
      if( i>=iChng || sampleIsBetter(&p->current, &p->aBest[i]) ){
        sampleCopy(p, &p->aBest[i], &p->current);
      }
    }
  }
#endif
}
static const FuncDef statPushFuncdef = {
  2+IsStat34,      /* nArg */
  SQLITE_UTF8,     /* iPrefEnc */
  0,               /* flags */
  0,               /* pUserData */
  0,               /* pNext */
  statPush,        /* xFunc */
  0,               /* xStep */
  0,               /* xFinalize */
  "stat_push",     /* zName */
  0,               /* pHash */







|








|
<







654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670

671
672
673
674
675
676
677
      sampleInsert(p, &p->current, p->nCol-1);
      p->current.isPSample = 0;
    }

    /* Update the aBest[] array. */
    for(i=0; i<(p->nCol-1); i++){
      p->current.iCol = i;
      if( i>=iChng || sampleIsBetterPost(p, &p->current, &p->aBest[i]) ){
        sampleCopy(p, &p->aBest[i], &p->current);
      }
    }
  }
#endif
}
static const FuncDef statPushFuncdef = {
  2+IsStat34,      /* nArg */
  SQLITE_UTF8,     /* funcFlags */

  0,               /* pUserData */
  0,               /* pNext */
  statPush,        /* xFunc */
  0,               /* xStep */
  0,               /* xFinalize */
  "stat_push",     /* zName */
  0,               /* pHash */
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
      }
    }
  }
#endif /* SQLITE_ENABLE_STAT3_OR_STAT4 */
}
static const FuncDef statGetFuncdef = {
  1+IsStat34,      /* nArg */
  SQLITE_UTF8,     /* iPrefEnc */
  0,               /* flags */
  0,               /* pUserData */
  0,               /* pNext */
  statGet,         /* xFunc */
  0,               /* xStep */
  0,               /* xFinalize */
  "stat_get",      /* zName */
  0,               /* pHash */







|
<







798
799
800
801
802
803
804
805

806
807
808
809
810
811
812
      }
    }
  }
#endif /* SQLITE_ENABLE_STAT3_OR_STAT4 */
}
static const FuncDef statGetFuncdef = {
  1+IsStat34,      /* nArg */
  SQLITE_UTF8,     /* funcFlags */

  0,               /* pUserData */
  0,               /* pNext */
  statGet,         /* xFunc */
  0,               /* xStep */
  0,               /* xFinalize */
  "stat_get",      /* zName */
  0,               /* pHash */
Changes to src/attach.c.
375
376
377
378
379
380
381
382
383
384
385
386
387
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
** Called by the parser to compile a DETACH statement.
**
**     DETACH pDbname
*/
void sqlite3Detach(Parse *pParse, Expr *pDbname){
  static const FuncDef detach_func = {
    1,                /* nArg */
    SQLITE_UTF8,      /* iPrefEnc */
    0,                /* flags */
    0,                /* pUserData */
    0,                /* pNext */
    detachFunc,       /* xFunc */
    0,                /* xStep */
    0,                /* xFinalize */
    "sqlite_detach",  /* zName */
    0,                /* pHash */
    0                 /* pDestructor */
  };
  codeAttach(pParse, SQLITE_DETACH, &detach_func, pDbname, 0, 0, pDbname);
}

/*
** Called by the parser to compile an ATTACH statement.
**
**     ATTACH p AS pDbname KEY pKey
*/
void sqlite3Attach(Parse *pParse, Expr *p, Expr *pDbname, Expr *pKey){
  static const FuncDef attach_func = {
    3,                /* nArg */
    SQLITE_UTF8,      /* iPrefEnc */
    0,                /* flags */
    0,                /* pUserData */
    0,                /* pNext */
    attachFunc,       /* xFunc */
    0,                /* xStep */
    0,                /* xFinalize */
    "sqlite_attach",  /* zName */
    0,                /* pHash */







|
<




















|
<







375
376
377
378
379
380
381
382

383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403

404
405
406
407
408
409
410
** Called by the parser to compile a DETACH statement.
**
**     DETACH pDbname
*/
void sqlite3Detach(Parse *pParse, Expr *pDbname){
  static const FuncDef detach_func = {
    1,                /* nArg */
    SQLITE_UTF8,      /* funcFlags */

    0,                /* pUserData */
    0,                /* pNext */
    detachFunc,       /* xFunc */
    0,                /* xStep */
    0,                /* xFinalize */
    "sqlite_detach",  /* zName */
    0,                /* pHash */
    0                 /* pDestructor */
  };
  codeAttach(pParse, SQLITE_DETACH, &detach_func, pDbname, 0, 0, pDbname);
}

/*
** Called by the parser to compile an ATTACH statement.
**
**     ATTACH p AS pDbname KEY pKey
*/
void sqlite3Attach(Parse *pParse, Expr *p, Expr *pDbname, Expr *pKey){
  static const FuncDef attach_func = {
    3,                /* nArg */
    SQLITE_UTF8,      /* funcFlags */

    0,                /* pUserData */
    0,                /* pNext */
    attachFunc,       /* xFunc */
    0,                /* xStep */
    0,                /* xFinalize */
    "sqlite_attach",  /* zName */
    0,                /* pHash */
Changes to src/callback.c.
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
  if( p->nArg==nArg ){
    match = 4;
  }else{
    match = 1;
  }

  /* Bonus points if the text encoding matches */
  if( enc==p->iPrefEnc ){
    match += 2;  /* Exact encoding match */
  }else if( (enc & p->iPrefEnc & 2)!=0 ){
    match += 1;  /* Both are UTF16, but with different byte orders */
  }

  return match;
}

/*







|

|







266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
  if( p->nArg==nArg ){
    match = 4;
  }else{
    match = 1;
  }

  /* Bonus points if the text encoding matches */
  if( enc==(p->funcFlags & SQLITE_FUNC_ENCMASK) ){
    match += 2;  /* Exact encoding match */
  }else if( (enc & p->funcFlags & 2)!=0 ){
    match += 1;  /* Both are UTF16, but with different byte orders */
  }

  return match;
}

/*
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
  ** exact match for the name, number of arguments and encoding, then add a
  ** new entry to the hash table and return it.
  */
  if( createFlag && bestScore<FUNC_PERFECT_MATCH && 
      (pBest = sqlite3DbMallocZero(db, sizeof(*pBest)+nName+1))!=0 ){
    pBest->zName = (char *)&pBest[1];
    pBest->nArg = (u16)nArg;
    pBest->iPrefEnc = enc;
    memcpy(pBest->zName, zName, nName);
    pBest->zName[nName] = 0;
    sqlite3FuncDefInsert(&db->aFunc, pBest);
  }

  if( pBest && (pBest->xStep || pBest->xFunc || createFlag) ){
    return pBest;







|







402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
  ** exact match for the name, number of arguments and encoding, then add a
  ** new entry to the hash table and return it.
  */
  if( createFlag && bestScore<FUNC_PERFECT_MATCH && 
      (pBest = sqlite3DbMallocZero(db, sizeof(*pBest)+nName+1))!=0 ){
    pBest->zName = (char *)&pBest[1];
    pBest->nArg = (u16)nArg;
    pBest->funcFlags = enc;
    memcpy(pBest->zName, zName, nName);
    pBest->zName[nName] = 0;
    sqlite3FuncDefInsert(&db->aFunc, pBest);
  }

  if( pBest && (pBest->xStep || pBest->xFunc || createFlag) ){
    return pBest;
Changes to src/delete.c.
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
    ** being deleted. Do not attempt to delete the row a second time, and 
    ** do not fire AFTER triggers.  */
    sqlite3VdbeAddOp3(v, OP_NotExists, iCur, iLabel, iRowid);

    /* Do FK processing. This call checks that any FK constraints that
    ** refer to this table (i.e. constraints attached to other tables) 
    ** are not violated by deleting this row.  */
    sqlite3FkCheck(pParse, pTab, iOld, 0);
  }

  /* Delete the index and table entries. Skip this step if pTab is really
  ** a view (in which case the only effect of the DELETE statement is to
  ** fire the INSTEAD OF triggers).  */ 
  if( pTab->pSelect==0 ){
    sqlite3GenerateRowIndexDelete(pParse, pTab, iCur, 0);
    sqlite3VdbeAddOp2(v, OP_Delete, iCur, (count?OPFLAG_NCHANGE:0));
    if( count ){
      sqlite3VdbeChangeP4(v, -1, pTab->zName, P4_TRANSIENT);
    }
  }

  /* Do any ON CASCADE, SET NULL or SET DEFAULT operations required to
  ** handle rows (possibly in other tables) that refer via a foreign key
  ** to the row just deleted. */ 
  sqlite3FkActions(pParse, pTab, 0, iOld);

  /* Invoke AFTER DELETE trigger programs. */
  sqlite3CodeRowTrigger(pParse, pTrigger, 
      TK_DELETE, 0, TRIGGER_AFTER, pTab, iOld, onconf, iLabel
  );

  /* Jump here if the row had already been deleted before any BEFORE







|
















|







532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
    ** being deleted. Do not attempt to delete the row a second time, and 
    ** do not fire AFTER triggers.  */
    sqlite3VdbeAddOp3(v, OP_NotExists, iCur, iLabel, iRowid);

    /* Do FK processing. This call checks that any FK constraints that
    ** refer to this table (i.e. constraints attached to other tables) 
    ** are not violated by deleting this row.  */
    sqlite3FkCheck(pParse, pTab, iOld, 0, 0, 0);
  }

  /* Delete the index and table entries. Skip this step if pTab is really
  ** a view (in which case the only effect of the DELETE statement is to
  ** fire the INSTEAD OF triggers).  */ 
  if( pTab->pSelect==0 ){
    sqlite3GenerateRowIndexDelete(pParse, pTab, iCur, 0);
    sqlite3VdbeAddOp2(v, OP_Delete, iCur, (count?OPFLAG_NCHANGE:0));
    if( count ){
      sqlite3VdbeChangeP4(v, -1, pTab->zName, P4_TRANSIENT);
    }
  }

  /* Do any ON CASCADE, SET NULL or SET DEFAULT operations required to
  ** handle rows (possibly in other tables) that refer via a foreign key
  ** to the row just deleted. */ 
  sqlite3FkActions(pParse, pTab, 0, iOld, 0, 0);

  /* Invoke AFTER DELETE trigger programs. */
  sqlite3CodeRowTrigger(pParse, pTrigger, 
      TK_DELETE, 0, TRIGGER_AFTER, pTab, iOld, onconf, iLabel
  );

  /* Jump here if the row had already been deleted before any BEFORE
Changes to src/expr.c.
2630
2631
2632
2633
2634
2635
2636
2637
2638
2639
2640
2641
2642
2643
2644
        break;
      }

      /* Attempt a direct implementation of the built-in COALESCE() and
      ** IFNULL() functions.  This avoids unnecessary evalation of
      ** arguments past the first non-NULL argument.
      */
      if( pDef->flags & SQLITE_FUNC_COALESCE ){
        int endCoalesce = sqlite3VdbeMakeLabel(v);
        assert( nFarg>=2 );
        sqlite3ExprCode(pParse, pFarg->a[0].pExpr, target);
        for(i=1; i<nFarg; i++){
          sqlite3VdbeAddOp2(v, OP_NotNull, target, endCoalesce);
          sqlite3ExprCacheRemove(pParse, target, 1);
          sqlite3ExprCachePush(pParse);







|







2630
2631
2632
2633
2634
2635
2636
2637
2638
2639
2640
2641
2642
2643
2644
        break;
      }

      /* Attempt a direct implementation of the built-in COALESCE() and
      ** IFNULL() functions.  This avoids unnecessary evalation of
      ** arguments past the first non-NULL argument.
      */
      if( pDef->funcFlags & SQLITE_FUNC_COALESCE ){
        int endCoalesce = sqlite3VdbeMakeLabel(v);
        assert( nFarg>=2 );
        sqlite3ExprCode(pParse, pFarg->a[0].pExpr, target);
        for(i=1; i<nFarg; i++){
          sqlite3VdbeAddOp2(v, OP_NotNull, target, endCoalesce);
          sqlite3ExprCacheRemove(pParse, target, 1);
          sqlite3ExprCachePush(pParse);
2654
2655
2656
2657
2658
2659
2660
2661
2662
2663
2664
2665
2666
2667
2668

2669
2670
2671
2672
2673
2674
2675
2676
2677
        r1 = sqlite3GetTempRange(pParse, nFarg);

        /* For length() and typeof() functions with a column argument,
        ** set the P5 parameter to the OP_Column opcode to OPFLAG_LENGTHARG
        ** or OPFLAG_TYPEOFARG respectively, to avoid unnecessary data
        ** loading.
        */
        if( (pDef->flags & (SQLITE_FUNC_LENGTH|SQLITE_FUNC_TYPEOF))!=0 ){
          u8 exprOp;
          assert( nFarg==1 );
          assert( pFarg->a[0].pExpr!=0 );
          exprOp = pFarg->a[0].pExpr->op;
          if( exprOp==TK_COLUMN || exprOp==TK_AGG_COLUMN ){
            assert( SQLITE_FUNC_LENGTH==OPFLAG_LENGTHARG );
            assert( SQLITE_FUNC_TYPEOF==OPFLAG_TYPEOFARG );

            testcase( pDef->flags==SQLITE_FUNC_LENGTH );
            pFarg->a[0].pExpr->op2 = pDef->flags;
          }
        }

        sqlite3ExprCachePush(pParse);     /* Ticket 2ea2425d34be */
        sqlite3ExprCodeExprList(pParse, pFarg, r1, 1);
        sqlite3ExprCachePop(pParse, 1);   /* Ticket 2ea2425d34be */
      }else{







|







>
|
|







2654
2655
2656
2657
2658
2659
2660
2661
2662
2663
2664
2665
2666
2667
2668
2669
2670
2671
2672
2673
2674
2675
2676
2677
2678
        r1 = sqlite3GetTempRange(pParse, nFarg);

        /* For length() and typeof() functions with a column argument,
        ** set the P5 parameter to the OP_Column opcode to OPFLAG_LENGTHARG
        ** or OPFLAG_TYPEOFARG respectively, to avoid unnecessary data
        ** loading.
        */
        if( (pDef->funcFlags & (SQLITE_FUNC_LENGTH|SQLITE_FUNC_TYPEOF))!=0 ){
          u8 exprOp;
          assert( nFarg==1 );
          assert( pFarg->a[0].pExpr!=0 );
          exprOp = pFarg->a[0].pExpr->op;
          if( exprOp==TK_COLUMN || exprOp==TK_AGG_COLUMN ){
            assert( SQLITE_FUNC_LENGTH==OPFLAG_LENGTHARG );
            assert( SQLITE_FUNC_TYPEOF==OPFLAG_TYPEOFARG );
            testcase( (pDef->funcFlags&~SQLITE_FUNC_ENCMASK)
                       ==SQLITE_FUNC_LENGTH );
            pFarg->a[0].pExpr->op2 = pDef->funcFlags&~SQLITE_FUNC_ENCMASK;
          }
        }

        sqlite3ExprCachePush(pParse);     /* Ticket 2ea2425d34be */
        sqlite3ExprCodeExprList(pParse, pFarg, r1, 1);
        sqlite3ExprCachePop(pParse, 1);   /* Ticket 2ea2425d34be */
      }else{
2696
2697
2698
2699
2700
2701
2702
2703
2704
2705
2706
2707
2708
2709
2710
2711
2712
2713
2714
        pDef = sqlite3VtabOverloadFunction(db, pDef, nFarg, pFarg->a[0].pExpr);
      }
#endif
      for(i=0; i<nFarg; i++){
        if( i<32 && sqlite3ExprIsConstant(pFarg->a[i].pExpr) ){
          constMask |= (1<<i);
        }
        if( (pDef->flags & SQLITE_FUNC_NEEDCOLL)!=0 && !pColl ){
          pColl = sqlite3ExprCollSeq(pParse, pFarg->a[i].pExpr);
        }
      }
      if( pDef->flags & SQLITE_FUNC_NEEDCOLL ){
        if( !pColl ) pColl = db->pDfltColl; 
        sqlite3VdbeAddOp4(v, OP_CollSeq, 0, 0, 0, (char *)pColl, P4_COLLSEQ);
      }
      sqlite3VdbeAddOp4(v, OP_Function, constMask, r1, target,
                        (char*)pDef, P4_FUNCDEF);
      sqlite3VdbeChangeP5(v, (u8)nFarg);
      if( nFarg ){







|



|







2697
2698
2699
2700
2701
2702
2703
2704
2705
2706
2707
2708
2709
2710
2711
2712
2713
2714
2715
        pDef = sqlite3VtabOverloadFunction(db, pDef, nFarg, pFarg->a[0].pExpr);
      }
#endif
      for(i=0; i<nFarg; i++){
        if( i<32 && sqlite3ExprIsConstant(pFarg->a[i].pExpr) ){
          constMask |= (1<<i);
        }
        if( (pDef->funcFlags & SQLITE_FUNC_NEEDCOLL)!=0 && !pColl ){
          pColl = sqlite3ExprCollSeq(pParse, pFarg->a[i].pExpr);
        }
      }
      if( pDef->funcFlags & SQLITE_FUNC_NEEDCOLL ){
        if( !pColl ) pColl = db->pDfltColl; 
        sqlite3VdbeAddOp4(v, OP_CollSeq, 0, 0, 0, (char *)pColl, P4_COLLSEQ);
      }
      sqlite3VdbeAddOp4(v, OP_Function, constMask, r1, target,
                        (char*)pDef, P4_FUNCDEF);
      sqlite3VdbeChangeP5(v, (u8)nFarg);
      if( nFarg ){
Changes to src/fkey.c.
678
679
680
681
682
683
684
































































685
686
687
688
689
690
691

    if( iSkip ){
      sqlite3VdbeResolveLabel(v, iSkip);
    }
  }
}

































































/*
** This function is called when inserting, deleting or updating a row of
** table pTab to generate VDBE code to perform foreign key constraint 
** processing for the operation.
**
** For a DELETE operation, parameter regOld is passed the index of the
** first register in an array of (pTab->nCol+1) registers containing the







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
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
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755

    if( iSkip ){
      sqlite3VdbeResolveLabel(v, iSkip);
    }
  }
}


/*
** The second argument points to an FKey object representing a foreign key
** for which pTab is the child table. An UPDATE statement against pTab
** is currently being processed. For each column of the table that is 
** actually updated, the corresponding element in the aChange[] array
** is zero or greater (if a column is unmodified the corresponding element
** is set to -1). If the rowid column is modified by the UPDATE statement
** the bChngRowid argument is non-zero.
**
** This function returns true if any of the columns that are part of the
** child key for FK constraint *p are modified.
*/
static int fkChildIsModified(
  Table *pTab,                    /* Table being updated */
  FKey *p,                        /* Foreign key for which pTab is the child */
  int *aChange,                   /* Array indicating modified columns */
  int bChngRowid                  /* True if rowid is modified by this update */
){
  int i;
  for(i=0; i<p->nCol; i++){
    int iChildKey = p->aCol[i].iFrom;
    if( aChange[iChildKey]>=0 ) return 1;
    if( iChildKey==pTab->iPKey && bChngRowid ) return 1;
  }
  return 0;
}

/*
** The second argument points to an FKey object representing a foreign key
** for which pTab is the parent table. An UPDATE statement against pTab
** is currently being processed. For each column of the table that is 
** actually updated, the corresponding element in the aChange[] array
** is zero or greater (if a column is unmodified the corresponding element
** is set to -1). If the rowid column is modified by the UPDATE statement
** the bChngRowid argument is non-zero.
**
** This function returns true if any of the columns that are part of the
** parent key for FK constraint *p are modified.
*/
static int fkParentIsModified(
  Table *pTab, 
  FKey *p, 
  int *aChange, 
  int bChngRowid
){
  int i;
  for(i=0; i<p->nCol; i++){
    char *zKey = p->aCol[i].zCol;
    int iKey;
    for(iKey=0; iKey<pTab->nCol; iKey++){
      if( aChange[iKey]>=0 || (iKey==pTab->iPKey && bChngRowid) ){
        Column *pCol = &pTab->aCol[iKey];
        if( zKey ){
          if( 0==sqlite3StrICmp(pCol->zName, zKey) ) return 1;
        }else if( pCol->colFlags & COLFLAG_PRIMKEY ){
          return 1;
        }
      }
    }
  }
  return 0;
}

/*
** This function is called when inserting, deleting or updating a row of
** table pTab to generate VDBE code to perform foreign key constraint 
** processing for the operation.
**
** For a DELETE operation, parameter regOld is passed the index of the
** first register in an array of (pTab->nCol+1) registers containing the
702
703
704
705
706
707
708
709


710
711
712
713
714
715
716
** described for DELETE. Then again after the original record is deleted
** but before the new record is inserted using the INSERT convention. 
*/
void sqlite3FkCheck(
  Parse *pParse,                  /* Parse context */
  Table *pTab,                    /* Row is being deleted from this table */ 
  int regOld,                     /* Previous row data is stored here */
  int regNew                      /* New row data is stored here */


){
  sqlite3 *db = pParse->db;       /* Database handle */
  FKey *pFKey;                    /* Used to iterate through FKs */
  int iDb;                        /* Index of database containing pTab */
  const char *zDb;                /* Name of database containing pTab */
  int isIgnoreErrors = pParse->disableTriggers;








|
>
>







766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
** described for DELETE. Then again after the original record is deleted
** but before the new record is inserted using the INSERT convention. 
*/
void sqlite3FkCheck(
  Parse *pParse,                  /* Parse context */
  Table *pTab,                    /* Row is being deleted from this table */ 
  int regOld,                     /* Previous row data is stored here */
  int regNew,                     /* New row data is stored here */
  int *aChange,                   /* Array indicating UPDATEd columns (or 0) */
  int bChngRowid                  /* True if rowid is UPDATEd */
){
  sqlite3 *db = pParse->db;       /* Database handle */
  FKey *pFKey;                    /* Used to iterate through FKs */
  int iDb;                        /* Index of database containing pTab */
  const char *zDb;                /* Name of database containing pTab */
  int isIgnoreErrors = pParse->disableTriggers;

729
730
731
732
733
734
735







736
737
738
739
740
741
742
    Table *pTo;                   /* Parent table of foreign key pFKey */
    Index *pIdx = 0;              /* Index on key columns in pTo */
    int *aiFree = 0;
    int *aiCol;
    int iCol;
    int i;
    int isIgnore = 0;








    /* Find the parent table of this foreign key. Also find a unique index 
    ** on the parent key columns in the parent table. If either of these 
    ** schema items cannot be located, set an error in pParse and return 
    ** early.  */
    if( pParse->disableTriggers ){
      pTo = sqlite3FindTable(db, pFKey->zTo, zDb);







>
>
>
>
>
>
>







795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
    Table *pTo;                   /* Parent table of foreign key pFKey */
    Index *pIdx = 0;              /* Index on key columns in pTo */
    int *aiFree = 0;
    int *aiCol;
    int iCol;
    int i;
    int isIgnore = 0;

    if( aChange 
     && sqlite3_stricmp(pTab->zName, pFKey->zTo)!=0
     && fkChildIsModified(pTab, pFKey, aChange, bChngRowid)==0 
    ){
      continue;
    }

    /* Find the parent table of this foreign key. Also find a unique index 
    ** on the parent key columns in the parent table. If either of these 
    ** schema items cannot be located, set an error in pParse and return 
    ** early.  */
    if( pParse->disableTriggers ){
      pTo = sqlite3FindTable(db, pFKey->zTo, zDb);
811
812
813
814
815
816
817




818
819
820
821
822
823
824
  }

  /* Loop through all the foreign key constraints that refer to this table */
  for(pFKey = sqlite3FkReferences(pTab); pFKey; pFKey=pFKey->pNextTo){
    Index *pIdx = 0;              /* Foreign key index for pFKey */
    SrcList *pSrc;
    int *aiCol = 0;





    if( !pFKey->isDeferred && !(db->flags & SQLITE_DeferFKs) 
     && !pParse->pToplevel && !pParse->isMultiWrite 
    ){
      assert( regOld==0 && regNew!=0 );
      /* Inserting a single row into a parent table cannot cause an immediate
      ** foreign key violation. So do nothing in this case.  */







>
>
>
>







884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
  }

  /* Loop through all the foreign key constraints that refer to this table */
  for(pFKey = sqlite3FkReferences(pTab); pFKey; pFKey=pFKey->pNextTo){
    Index *pIdx = 0;              /* Foreign key index for pFKey */
    SrcList *pSrc;
    int *aiCol = 0;

    if( aChange && fkParentIsModified(pTab, pFKey, aChange, bChngRowid)==0 ){
      continue;
    }

    if( !pFKey->isDeferred && !(db->flags & SQLITE_DeferFKs) 
     && !pParse->pToplevel && !pParse->isMultiWrite 
    ){
      assert( regOld==0 && regNew!=0 );
      /* Inserting a single row into a parent table cannot cause an immediate
      ** foreign key violation. So do nothing in this case.  */
884
885
886
887
888
889
890

891
892
893
894
895
896
897
      if( pIdx ){
        for(i=0; i<pIdx->nColumn; i++) mask |= COLUMN_MASK(pIdx->aiColumn[i]);
      }
    }
  }
  return mask;
}


/*
** This function is called before generating code to update or delete a 
** row contained in table pTab. If the operation is a DELETE, then
** parameter aChange is passed a NULL value. For an UPDATE, aChange points
** to an array of size N, where N is the number of columns in table pTab.
** If the i'th column is not modified by the UPDATE, then the corresponding 







>







961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
      if( pIdx ){
        for(i=0; i<pIdx->nColumn; i++) mask |= COLUMN_MASK(pIdx->aiColumn[i]);
      }
    }
  }
  return mask;
}


/*
** This function is called before generating code to update or delete a 
** row contained in table pTab. If the operation is a DELETE, then
** parameter aChange is passed a NULL value. For an UPDATE, aChange points
** to an array of size N, where N is the number of columns in table pTab.
** If the i'th column is not modified by the UPDATE, then the corresponding 
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
      /* A DELETE operation. Foreign key processing is required if the 
      ** table in question is either the child or parent table for any 
      ** foreign key constraint.  */
      return (sqlite3FkReferences(pTab) || pTab->pFKey);
    }else{
      /* This is an UPDATE. Foreign key processing is only required if the
      ** operation modifies one or more child or parent key columns. */
      int i;
      FKey *p;

      /* Check if any child key columns are being modified. */
      for(p=pTab->pFKey; p; p=p->pNextFrom){
        for(i=0; i<p->nCol; i++){
          int iChildKey = p->aCol[i].iFrom;
          if( aChange[iChildKey]>=0 ) return 1;
          if( iChildKey==pTab->iPKey && chngRowid ) return 1;
        }
      }

      /* Check if any parent key columns are being modified. */
      for(p=sqlite3FkReferences(pTab); p; p=p->pNextTo){
        for(i=0; i<p->nCol; i++){
          char *zKey = p->aCol[i].zCol;
          int iKey;
          for(iKey=0; iKey<pTab->nCol; iKey++){
            Column *pCol = &pTab->aCol[iKey];
            if( (zKey ? !sqlite3StrICmp(pCol->zName, zKey)
                      : (pCol->colFlags & COLFLAG_PRIMKEY)!=0) ){
              if( aChange[iKey]>=0 ) return 1;
              if( iKey==pTab->iPKey && chngRowid ) return 1;
            }
          }
        }
      }
    }
  }
  return 0;
}

/*







<




<
<
|
<
<




<
<
<
<
<
<
<
<
|
<
<
<







992
993
994
995
996
997
998

999
1000
1001
1002


1003


1004
1005
1006
1007








1008



1009
1010
1011
1012
1013
1014
1015
      /* A DELETE operation. Foreign key processing is required if the 
      ** table in question is either the child or parent table for any 
      ** foreign key constraint.  */
      return (sqlite3FkReferences(pTab) || pTab->pFKey);
    }else{
      /* This is an UPDATE. Foreign key processing is only required if the
      ** operation modifies one or more child or parent key columns. */

      FKey *p;

      /* Check if any child key columns are being modified. */
      for(p=pTab->pFKey; p; p=p->pNextFrom){


        if( fkChildIsModified(pTab, p, aChange, chngRowid) ) return 1;


      }

      /* Check if any parent key columns are being modified. */
      for(p=sqlite3FkReferences(pTab); p; p=p->pNextTo){








        if( fkParentIsModified(pTab, p, aChange, chngRowid) ) return 1;



      }
    }
  }
  return 0;
}

/*
1165
1166
1167
1168
1169
1170
1171
1172


1173
1174
1175
1176
1177
1178
1179
1180

1181
1182
1183

1184
1185
1186
1187
1188
1189
1190
** This function is called when deleting or updating a row to implement
** any required CASCADE, SET NULL or SET DEFAULT actions.
*/
void sqlite3FkActions(
  Parse *pParse,                  /* Parse context */
  Table *pTab,                    /* Table being updated or deleted from */
  ExprList *pChanges,             /* Change-list for UPDATE, NULL for DELETE */
  int regOld                      /* Address of array containing old row */


){
  /* If foreign-key support is enabled, iterate through all FKs that 
  ** refer to table pTab. If there is an action associated with the FK 
  ** for this operation (either update or delete), invoke the associated 
  ** trigger sub-program.  */
  if( pParse->db->flags&SQLITE_ForeignKeys ){
    FKey *pFKey;                  /* Iterator variable */
    for(pFKey = sqlite3FkReferences(pTab); pFKey; pFKey=pFKey->pNextTo){

      Trigger *pAction = fkActionTrigger(pParse, pTab, pFKey, pChanges);
      if( pAction ){
        sqlite3CodeRowTriggerDirect(pParse, pAction, pTab, regOld, OE_Abort, 0);

      }
    }
  }
}

#endif /* ifndef SQLITE_OMIT_TRIGGER */








|
>
>








>
|
|
|
>







1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
** This function is called when deleting or updating a row to implement
** any required CASCADE, SET NULL or SET DEFAULT actions.
*/
void sqlite3FkActions(
  Parse *pParse,                  /* Parse context */
  Table *pTab,                    /* Table being updated or deleted from */
  ExprList *pChanges,             /* Change-list for UPDATE, NULL for DELETE */
  int regOld,                     /* Address of array containing old row */
  int *aChange,                   /* Array indicating UPDATEd columns (or 0) */
  int bChngRowid                  /* True if rowid is UPDATEd */
){
  /* If foreign-key support is enabled, iterate through all FKs that 
  ** refer to table pTab. If there is an action associated with the FK 
  ** for this operation (either update or delete), invoke the associated 
  ** trigger sub-program.  */
  if( pParse->db->flags&SQLITE_ForeignKeys ){
    FKey *pFKey;                  /* Iterator variable */
    for(pFKey = sqlite3FkReferences(pTab); pFKey; pFKey=pFKey->pNextTo){
      if( aChange==0 || fkParentIsModified(pTab, pFKey, aChange, bChngRowid) ){
        Trigger *pAct = fkActionTrigger(pParse, pTab, pFKey, pChanges);
        if( pAct ){
          sqlite3CodeRowTriggerDirect(pParse, pAct, pTab, regOld, OE_Abort, 0);
        }
      }
    }
  }
}

#endif /* ifndef SQLITE_OMIT_TRIGGER */

Changes to src/func.c.
1689
1690
1691
1692
1693
1694
1695
1696
1697
1698
1699
1700
1701
1702
1703
** Set the LIKEOPT flag on the 2-argument function with the given name.
*/
static void setLikeOptFlag(sqlite3 *db, const char *zName, u8 flagVal){
  FuncDef *pDef;
  pDef = sqlite3FindFunction(db, zName, sqlite3Strlen30(zName),
                             2, SQLITE_UTF8, 0);
  if( ALWAYS(pDef) ){
    pDef->flags = flagVal;
  }
}

/*
** Register the built-in LIKE and GLOB functions.  The caseSensitive
** parameter determines whether or not the LIKE operator is case
** sensitive.  GLOB is always case sensitive.







|







1689
1690
1691
1692
1693
1694
1695
1696
1697
1698
1699
1700
1701
1702
1703
** Set the LIKEOPT flag on the 2-argument function with the given name.
*/
static void setLikeOptFlag(sqlite3 *db, const char *zName, u8 flagVal){
  FuncDef *pDef;
  pDef = sqlite3FindFunction(db, zName, sqlite3Strlen30(zName),
                             2, SQLITE_UTF8, 0);
  if( ALWAYS(pDef) ){
    pDef->funcFlags |= flagVal;
  }
}

/*
** Register the built-in LIKE and GLOB functions.  The caseSensitive
** parameter determines whether or not the LIKE operator is case
** sensitive.  GLOB is always case sensitive.
1733
1734
1735
1736
1737
1738
1739
1740
1741
1742
1743
1744
1745
1746
1747
1748
1749
1750
1751
1752
1753
1754
1755
1756
1757
1758
1759
  ){
    return 0;
  }
  assert( !ExprHasProperty(pExpr, EP_xIsSelect) );
  pDef = sqlite3FindFunction(db, pExpr->u.zToken, 
                             sqlite3Strlen30(pExpr->u.zToken),
                             2, SQLITE_UTF8, 0);
  if( NEVER(pDef==0) || (pDef->flags & SQLITE_FUNC_LIKE)==0 ){
    return 0;
  }

  /* The memcpy() statement assumes that the wildcard characters are
  ** the first three statements in the compareInfo structure.  The
  ** asserts() that follow verify that assumption
  */
  memcpy(aWc, pDef->pUserData, 3);
  assert( (char*)&likeInfoAlt == (char*)&likeInfoAlt.matchAll );
  assert( &((char*)&likeInfoAlt)[1] == (char*)&likeInfoAlt.matchOne );
  assert( &((char*)&likeInfoAlt)[2] == (char*)&likeInfoAlt.matchSet );
  *pIsNocase = (pDef->flags & SQLITE_FUNC_CASE)==0;
  return 1;
}

/*
** All all of the FuncDef structures in the aBuiltinFunc[] array above
** to the global function hash table.  This occurs at start-time (as
** a consequence of calling sqlite3_initialize()).







|











|







1733
1734
1735
1736
1737
1738
1739
1740
1741
1742
1743
1744
1745
1746
1747
1748
1749
1750
1751
1752
1753
1754
1755
1756
1757
1758
1759
  ){
    return 0;
  }
  assert( !ExprHasProperty(pExpr, EP_xIsSelect) );
  pDef = sqlite3FindFunction(db, pExpr->u.zToken, 
                             sqlite3Strlen30(pExpr->u.zToken),
                             2, SQLITE_UTF8, 0);
  if( NEVER(pDef==0) || (pDef->funcFlags & SQLITE_FUNC_LIKE)==0 ){
    return 0;
  }

  /* The memcpy() statement assumes that the wildcard characters are
  ** the first three statements in the compareInfo structure.  The
  ** asserts() that follow verify that assumption
  */
  memcpy(aWc, pDef->pUserData, 3);
  assert( (char*)&likeInfoAlt == (char*)&likeInfoAlt.matchAll );
  assert( &((char*)&likeInfoAlt)[1] == (char*)&likeInfoAlt.matchOne );
  assert( &((char*)&likeInfoAlt)[2] == (char*)&likeInfoAlt.matchSet );
  *pIsNocase = (pDef->funcFlags & SQLITE_FUNC_CASE)==0;
  return 1;
}

/*
** All all of the FuncDef structures in the aBuiltinFunc[] array above
** to the global function hash table.  This occurs at start-time (as
** a consequence of calling sqlite3_initialize()).
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835
1836
1837
1838
1839
1840
    FUNCTION(load_extension,     1, 0, 0, loadExt          ),
    FUNCTION(load_extension,     2, 0, 0, loadExt          ),
  #endif
    AGGREGATE(sum,               1, 0, 0, sumStep,         sumFinalize    ),
    AGGREGATE(total,             1, 0, 0, sumStep,         totalFinalize    ),
    AGGREGATE(avg,               1, 0, 0, sumStep,         avgFinalize    ),
 /* AGGREGATE(count,             0, 0, 0, countStep,       countFinalize  ), */
    {0,SQLITE_UTF8,SQLITE_FUNC_COUNT,0,0,0,countStep,countFinalize,"count",0,0},
    AGGREGATE(count,             1, 0, 0, countStep,       countFinalize  ),
    AGGREGATE(group_concat,      1, 0, 0, groupConcatStep, groupConcatFinalize),
    AGGREGATE(group_concat,      2, 0, 0, groupConcatStep, groupConcatFinalize),
  
    LIKEFUNC(glob, 2, &globInfo, SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE),
  #ifdef SQLITE_CASE_SENSITIVE_LIKE
    LIKEFUNC(like, 2, &likeInfoAlt, SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE),







|







1826
1827
1828
1829
1830
1831
1832
1833
1834
1835
1836
1837
1838
1839
1840
    FUNCTION(load_extension,     1, 0, 0, loadExt          ),
    FUNCTION(load_extension,     2, 0, 0, loadExt          ),
  #endif
    AGGREGATE(sum,               1, 0, 0, sumStep,         sumFinalize    ),
    AGGREGATE(total,             1, 0, 0, sumStep,         totalFinalize    ),
    AGGREGATE(avg,               1, 0, 0, sumStep,         avgFinalize    ),
 /* AGGREGATE(count,             0, 0, 0, countStep,       countFinalize  ), */
    {0,SQLITE_UTF8|SQLITE_FUNC_COUNT,0,0,0,countStep,countFinalize,"count",0,0},
    AGGREGATE(count,             1, 0, 0, countStep,       countFinalize  ),
    AGGREGATE(group_concat,      1, 0, 0, groupConcatStep, groupConcatFinalize),
    AGGREGATE(group_concat,      2, 0, 0, groupConcatStep, groupConcatFinalize),
  
    LIKEFUNC(glob, 2, &globInfo, SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE),
  #ifdef SQLITE_CASE_SENSITIVE_LIKE
    LIKEFUNC(like, 2, &likeInfoAlt, SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE),
Changes to src/insert.c.
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
    }else
#endif
    {
      int isReplace;    /* Set to true if constraints may cause a replace */
      sqlite3GenerateConstraintChecks(pParse, pTab, baseCur, regIns, aRegIdx,
          keyColumn>=0, 0, onError, endOfLoop, &isReplace
      );
      sqlite3FkCheck(pParse, pTab, 0, regIns);
      sqlite3CompleteInsertion(
          pParse, pTab, baseCur, regIns, aRegIdx, 0, appendFlag, isReplace==0
      );
    }
  }

  /* Update the count of rows that are inserted







|







1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
    }else
#endif
    {
      int isReplace;    /* Set to true if constraints may cause a replace */
      sqlite3GenerateConstraintChecks(pParse, pTab, baseCur, regIns, aRegIdx,
          keyColumn>=0, 0, onError, endOfLoop, &isReplace
      );
      sqlite3FkCheck(pParse, pTab, 0, regIns, 0, 0);
      sqlite3CompleteInsertion(
          pParse, pTab, baseCur, regIns, aRegIdx, 0, appendFlag, isReplace==0
      );
    }
  }

  /* Update the count of rows that are inserted
Changes to src/main.c.
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
  
  /* Check if an existing function is being overridden or deleted. If so,
  ** and there are active VMs, then return SQLITE_BUSY. If a function
  ** is being overridden/deleted but there are no active VMs, allow the
  ** operation to continue but invalidate all precompiled statements.
  */
  p = sqlite3FindFunction(db, zFunctionName, nName, nArg, (u8)enc, 0);
  if( p && p->iPrefEnc==enc && p->nArg==nArg ){
    if( db->nVdbeActive ){
      sqlite3Error(db, SQLITE_BUSY, 
        "unable to delete/modify user-function due to active statements");
      assert( !db->mallocFailed );
      return SQLITE_BUSY;
    }else{
      sqlite3ExpirePreparedStatements(db);







|







1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
  
  /* Check if an existing function is being overridden or deleted. If so,
  ** and there are active VMs, then return SQLITE_BUSY. If a function
  ** is being overridden/deleted but there are no active VMs, allow the
  ** operation to continue but invalidate all precompiled statements.
  */
  p = sqlite3FindFunction(db, zFunctionName, nName, nArg, (u8)enc, 0);
  if( p && (p->funcFlags & SQLITE_FUNC_ENCMASK)==enc && p->nArg==nArg ){
    if( db->nVdbeActive ){
      sqlite3Error(db, SQLITE_BUSY, 
        "unable to delete/modify user-function due to active statements");
      assert( !db->mallocFailed );
      return SQLITE_BUSY;
    }else{
      sqlite3ExpirePreparedStatements(db);
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
  ** being replaced invoke the destructor function here. */
  functionDestroy(db, p);

  if( pDestructor ){
    pDestructor->nRef++;
  }
  p->pDestructor = pDestructor;
  p->flags = 0;
  p->xFunc = xFunc;
  p->xStep = xStep;
  p->xFinalize = xFinal;
  p->pUserData = pUserData;
  p->nArg = (u16)nArg;
  return SQLITE_OK;
}







|







1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
  ** being replaced invoke the destructor function here. */
  functionDestroy(db, p);

  if( pDestructor ){
    pDestructor->nRef++;
  }
  p->pDestructor = pDestructor;
  p->funcFlags &= SQLITE_FUNC_ENCMASK;
  p->xFunc = xFunc;
  p->xStep = xStep;
  p->xFinalize = xFinal;
  p->pUserData = pUserData;
  p->nArg = (u16)nArg;
  return SQLITE_OK;
}
Changes to src/select.c.
3224
3225
3226
3227
3228
3229
3230
3231
3232
3233
3234
3235
3236
3237
3238
  pTab = p->pSrc->a[0].pTab;
  pExpr = p->pEList->a[0].pExpr;
  assert( pTab && !pTab->pSelect && pExpr );

  if( IsVirtual(pTab) ) return 0;
  if( pExpr->op!=TK_AGG_FUNCTION ) return 0;
  if( NEVER(pAggInfo->nFunc==0) ) return 0;
  if( (pAggInfo->aFunc[0].pFunc->flags&SQLITE_FUNC_COUNT)==0 ) return 0;
  if( pExpr->flags&EP_Distinct ) return 0;

  return pTab;
}

/*
** If the source-list item passed as an argument was augmented with an







|







3224
3225
3226
3227
3228
3229
3230
3231
3232
3233
3234
3235
3236
3237
3238
  pTab = p->pSrc->a[0].pTab;
  pExpr = p->pEList->a[0].pExpr;
  assert( pTab && !pTab->pSelect && pExpr );

  if( IsVirtual(pTab) ) return 0;
  if( pExpr->op!=TK_AGG_FUNCTION ) return 0;
  if( NEVER(pAggInfo->nFunc==0) ) return 0;
  if( (pAggInfo->aFunc[0].pFunc->funcFlags&SQLITE_FUNC_COUNT)==0 ) return 0;
  if( pExpr->flags&EP_Distinct ) return 0;

  return pTab;
}

/*
** If the source-list item passed as an argument was augmented with an
3821
3822
3823
3824
3825
3826
3827
3828
3829
3830
3831
3832
3833
3834
3835
      regAgg = 0;
    }
    if( pF->iDistinct>=0 ){
      addrNext = sqlite3VdbeMakeLabel(v);
      assert( nArg==1 );
      codeDistinct(pParse, pF->iDistinct, addrNext, 1, regAgg);
    }
    if( pF->pFunc->flags & SQLITE_FUNC_NEEDCOLL ){
      CollSeq *pColl = 0;
      struct ExprList_item *pItem;
      int j;
      assert( pList!=0 );  /* pList!=0 if pF->pFunc has NEEDCOLL */
      for(j=0, pItem=pList->a; !pColl && j<nArg; j++, pItem++){
        pColl = sqlite3ExprCollSeq(pParse, pItem->pExpr);
      }







|







3821
3822
3823
3824
3825
3826
3827
3828
3829
3830
3831
3832
3833
3834
3835
      regAgg = 0;
    }
    if( pF->iDistinct>=0 ){
      addrNext = sqlite3VdbeMakeLabel(v);
      assert( nArg==1 );
      codeDistinct(pParse, pF->iDistinct, addrNext, 1, regAgg);
    }
    if( pF->pFunc->funcFlags & SQLITE_FUNC_NEEDCOLL ){
      CollSeq *pColl = 0;
      struct ExprList_item *pItem;
      int j;
      assert( pList!=0 );  /* pList!=0 if pF->pFunc has NEEDCOLL */
      for(j=0, pItem=pList->a; !pColl && j<nArg; j++, pItem++){
        pColl = sqlite3ExprCollSeq(pParse, pItem->pExpr);
      }
Changes to src/shell.c.
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205


1206

1207

1208
1209
1210
1211
1212
1213
1214
          void *pData = sqlite3_malloc(3*nCol*sizeof(const char*) + 1);
          if( !pData ){
            rc = SQLITE_NOMEM;
          }else{
            char **azCols = (char **)pData;      /* Names of result columns */
            char **azVals = &azCols[nCol];       /* Results */
            int *aiTypes = (int *)&azVals[nCol]; /* Result types */
            int i;
            assert(sizeof(int) <= sizeof(char *)); 
            /* save off ptrs to column names */
            for(i=0; i<nCol; i++){
              azCols[i] = (char *)sqlite3_column_name(pStmt, i);
            }
            do{
              /* extract the data and data types */
              for(i=0; i<nCol; i++){


                azVals[i] = (char *)sqlite3_column_text(pStmt, i);

                aiTypes[i] = sqlite3_column_type(pStmt, i);

                if( !azVals[i] && (aiTypes[i]!=SQLITE_NULL) ){
                  rc = SQLITE_NOMEM;
                  break; /* from for */
                }
              } /* end for */

              /* if data and types extracted successfully... */







|








>
>
|
>
|
>







1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
          void *pData = sqlite3_malloc(3*nCol*sizeof(const char*) + 1);
          if( !pData ){
            rc = SQLITE_NOMEM;
          }else{
            char **azCols = (char **)pData;      /* Names of result columns */
            char **azVals = &azCols[nCol];       /* Results */
            int *aiTypes = (int *)&azVals[nCol]; /* Result types */
            int i, x;
            assert(sizeof(int) <= sizeof(char *)); 
            /* save off ptrs to column names */
            for(i=0; i<nCol; i++){
              azCols[i] = (char *)sqlite3_column_name(pStmt, i);
            }
            do{
              /* extract the data and data types */
              for(i=0; i<nCol; i++){
                aiTypes[i] = x = sqlite3_column_type(pStmt, i);
                if( x==SQLITE_BLOB && pArg->mode==MODE_Insert ){
                  azVals[i] = "";
                }else{
                  azVals[i] = (char*)sqlite3_column_text(pStmt, i);
                }
                if( !azVals[i] && (aiTypes[i]!=SQLITE_NULL) ){
                  rc = SQLITE_NOMEM;
                  break; /* from for */
                }
              } /* end for */

              /* if data and types extracted successfully... */
Changes to src/sqliteInt.h.
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
** Each SQL function is defined by an instance of the following
** structure.  A pointer to this structure is stored in the sqlite.aFunc
** hash table.  When multiple functions have the same name, the hash table
** points to a linked list of these structures.
*/
struct FuncDef {
  i16 nArg;            /* Number of arguments.  -1 means unlimited */
  u8 iPrefEnc;         /* Preferred text encoding (SQLITE_UTF8, 16LE, 16BE) */
  u8 flags;            /* Some combination of SQLITE_FUNC_* */
  void *pUserData;     /* User data parameter */
  FuncDef *pNext;      /* Next function with same name */
  void (*xFunc)(sqlite3_context*,int,sqlite3_value**); /* Regular function */
  void (*xStep)(sqlite3_context*,int,sqlite3_value**); /* Aggregate step */
  void (*xFinalize)(sqlite3_context*);                /* Aggregate finalizer */
  char *zName;         /* SQL name of the function. */
  FuncDef *pHash;      /* Next with a different name but the same hash */







<
|







1061
1062
1063
1064
1065
1066
1067

1068
1069
1070
1071
1072
1073
1074
1075
** Each SQL function is defined by an instance of the following
** structure.  A pointer to this structure is stored in the sqlite.aFunc
** hash table.  When multiple functions have the same name, the hash table
** points to a linked list of these structures.
*/
struct FuncDef {
  i16 nArg;            /* Number of arguments.  -1 means unlimited */

  u16 funcFlags;       /* Some combination of SQLITE_FUNC_* */
  void *pUserData;     /* User data parameter */
  FuncDef *pNext;      /* Next function with same name */
  void (*xFunc)(sqlite3_context*,int,sqlite3_value**); /* Regular function */
  void (*xStep)(sqlite3_context*,int,sqlite3_value**); /* Aggregate step */
  void (*xFinalize)(sqlite3_context*);                /* Aggregate finalizer */
  char *zName;         /* SQL name of the function. */
  FuncDef *pHash;      /* Next with a different name but the same hash */
1098
1099
1100
1101
1102
1103
1104

1105
1106
1107
1108


1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
};

/*
** Possible values for FuncDef.flags.  Note that the _LENGTH and _TYPEOF
** values must correspond to OPFLAG_LENGTHARG and OPFLAG_TYPEOFARG.  There
** are assert() statements in the code to verify this.
*/

#define SQLITE_FUNC_LIKE     0x01 /* Candidate for the LIKE optimization */
#define SQLITE_FUNC_CASE     0x02 /* Case-sensitive LIKE-type function */
#define SQLITE_FUNC_EPHEM    0x04 /* Ephemeral.  Delete with VDBE */
#define SQLITE_FUNC_NEEDCOLL 0x08 /* sqlite3GetFuncCollSeq() might be called */


#define SQLITE_FUNC_COUNT    0x10 /* Built-in count(*) aggregate */
#define SQLITE_FUNC_COALESCE 0x20 /* Built-in coalesce() or ifnull() function */
#define SQLITE_FUNC_LENGTH   0x40 /* Built-in length() function */
#define SQLITE_FUNC_TYPEOF   0x80 /* Built-in typeof() function */

/*
** The following three macros, FUNCTION(), LIKEFUNC() and AGGREGATE() are
** used to create the initializers for the FuncDef structures.
**
**   FUNCTION(zName, nArg, iArg, bNC, xFunc)
**     Used to create a scalar function definition of a function zName 







>
|
|
|
|
>
>
|
|
|
<







1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113

1114
1115
1116
1117
1118
1119
1120
};

/*
** Possible values for FuncDef.flags.  Note that the _LENGTH and _TYPEOF
** values must correspond to OPFLAG_LENGTHARG and OPFLAG_TYPEOFARG.  There
** are assert() statements in the code to verify this.
*/
#define SQLITE_FUNC_ENCMASK  0x003 /* SQLITE_UTF8, SQLITE_UTF16BE or UTF16LE */
#define SQLITE_FUNC_LIKE     0x004 /* Candidate for the LIKE optimization */
#define SQLITE_FUNC_CASE     0x008 /* Case-sensitive LIKE-type function */
#define SQLITE_FUNC_EPHEM    0x010 /* Ephemeral.  Delete with VDBE */
#define SQLITE_FUNC_NEEDCOLL 0x020 /* sqlite3GetFuncCollSeq() might be called */
#define SQLITE_FUNC_LENGTH   0x040 /* Built-in length() function */
#define SQLITE_FUNC_TYPEOF   0x080 /* Built-in typeof() function */
#define SQLITE_FUNC_COUNT    0x100 /* Built-in count(*) aggregate */
#define SQLITE_FUNC_COALESCE 0x200 /* Built-in coalesce() or ifnull() */
#define SQLITE_FUNC_UNLIKELY 0x400 /* Built-in unlikely() function */


/*
** The following three macros, FUNCTION(), LIKEFUNC() and AGGREGATE() are
** used to create the initializers for the FuncDef structures.
**
**   FUNCTION(zName, nArg, iArg, bNC, xFunc)
**     Used to create a scalar function definition of a function zName 
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
**     that accepts nArg arguments and is implemented by a call to C 
**     function likeFunc. Argument pArg is cast to a (void *) and made
**     available as the function user-data (sqlite3_user_data()). The
**     FuncDef.flags variable is set to the value passed as the flags
**     parameter.
*/
#define FUNCTION(zName, nArg, iArg, bNC, xFunc) \
  {nArg, SQLITE_UTF8, (bNC*SQLITE_FUNC_NEEDCOLL), \
   SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, 0, #zName, 0, 0}
#define FUNCTION2(zName, nArg, iArg, bNC, xFunc, extraFlags) \
  {nArg, SQLITE_UTF8, (bNC*SQLITE_FUNC_NEEDCOLL)|extraFlags, \
   SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, 0, #zName, 0, 0}
#define STR_FUNCTION(zName, nArg, pArg, bNC, xFunc) \
  {nArg, SQLITE_UTF8, bNC*SQLITE_FUNC_NEEDCOLL, \
   pArg, 0, xFunc, 0, 0, #zName, 0, 0}
#define LIKEFUNC(zName, nArg, arg, flags) \
  {nArg, SQLITE_UTF8, flags, (void *)arg, 0, likeFunc, 0, 0, #zName, 0, 0}
#define AGGREGATE(zName, nArg, arg, nc, xStep, xFinal) \
  {nArg, SQLITE_UTF8, nc*SQLITE_FUNC_NEEDCOLL, \
   SQLITE_INT_TO_PTR(arg), 0, 0, xStep,xFinal,#zName,0,0}

/*
** All current savepoints are stored in a linked list starting at
** sqlite3.pSavepoint. The first element in the list is the most recently
** opened savepoint. Savepoints are added to the list by the vdbe
** OP_Savepoint instruction.







|


|


|


|

|







1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
**     that accepts nArg arguments and is implemented by a call to C 
**     function likeFunc. Argument pArg is cast to a (void *) and made
**     available as the function user-data (sqlite3_user_data()). The
**     FuncDef.flags variable is set to the value passed as the flags
**     parameter.
*/
#define FUNCTION(zName, nArg, iArg, bNC, xFunc) \
  {nArg, SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL), \
   SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, 0, #zName, 0, 0}
#define FUNCTION2(zName, nArg, iArg, bNC, xFunc, extraFlags) \
  {nArg, SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL)|extraFlags, \
   SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, 0, #zName, 0, 0}
#define STR_FUNCTION(zName, nArg, pArg, bNC, xFunc) \
  {nArg, SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL), \
   pArg, 0, xFunc, 0, 0, #zName, 0, 0}
#define LIKEFUNC(zName, nArg, arg, flags) \
  {nArg, SQLITE_UTF8|flags, (void *)arg, 0, likeFunc, 0, 0, #zName, 0, 0}
#define AGGREGATE(zName, nArg, arg, nc, xStep, xFinal) \
  {nArg, SQLITE_UTF8|(nc*SQLITE_FUNC_NEEDCOLL), \
   SQLITE_INT_TO_PTR(arg), 0, 0, xStep,xFinal,#zName,0,0}

/*
** All current savepoints are stored in a linked list starting at
** sqlite3.pSavepoint. The first element in the list is the most recently
** opened savepoint. Savepoints are added to the list by the vdbe
** OP_Savepoint instruction.
3205
3206
3207
3208
3209
3210
3211
3212
3213
3214
3215
3216
3217
3218
3219
3220
3221
3222
3223
3224
3225
3226
3227
3228
3229
3230
** no-op macros if OMIT_FOREIGN_KEY is defined. In this case no foreign
** key functionality is available. If OMIT_TRIGGER is defined but
** OMIT_FOREIGN_KEY is not, only some of the functions are no-oped. In
** this case foreign keys are parsed, but no other functionality is 
** provided (enforcement of FK constraints requires the triggers sub-system).
*/
#if !defined(SQLITE_OMIT_FOREIGN_KEY) && !defined(SQLITE_OMIT_TRIGGER)
  void sqlite3FkCheck(Parse*, Table*, int, int);
  void sqlite3FkDropTable(Parse*, SrcList *, Table*);
  void sqlite3FkActions(Parse*, Table*, ExprList*, int);
  int sqlite3FkRequired(Parse*, Table*, int*, int);
  u32 sqlite3FkOldmask(Parse*, Table*);
  FKey *sqlite3FkReferences(Table *);
#else
  #define sqlite3FkActions(a,b,c,d)
  #define sqlite3FkCheck(a,b,c,d)
  #define sqlite3FkDropTable(a,b,c)
  #define sqlite3FkOldmask(a,b)      0
  #define sqlite3FkRequired(a,b,c,d) 0
#endif
#ifndef SQLITE_OMIT_FOREIGN_KEY
  void sqlite3FkDelete(sqlite3 *, Table*);
  int sqlite3FkLocateIndex(Parse*,Table*,FKey*,Index**,int**);
#else
  #define sqlite3FkDelete(a,b)
  #define sqlite3FkLocateIndex(a,b,c,d,e)







|

|




|



|







3206
3207
3208
3209
3210
3211
3212
3213
3214
3215
3216
3217
3218
3219
3220
3221
3222
3223
3224
3225
3226
3227
3228
3229
3230
3231
** no-op macros if OMIT_FOREIGN_KEY is defined. In this case no foreign
** key functionality is available. If OMIT_TRIGGER is defined but
** OMIT_FOREIGN_KEY is not, only some of the functions are no-oped. In
** this case foreign keys are parsed, but no other functionality is 
** provided (enforcement of FK constraints requires the triggers sub-system).
*/
#if !defined(SQLITE_OMIT_FOREIGN_KEY) && !defined(SQLITE_OMIT_TRIGGER)
  void sqlite3FkCheck(Parse*, Table*, int, int, int*, int);
  void sqlite3FkDropTable(Parse*, SrcList *, Table*);
  void sqlite3FkActions(Parse*, Table*, ExprList*, int, int*, int);
  int sqlite3FkRequired(Parse*, Table*, int*, int);
  u32 sqlite3FkOldmask(Parse*, Table*);
  FKey *sqlite3FkReferences(Table *);
#else
  #define sqlite3FkActions(a,b,c,d,e,f)
  #define sqlite3FkCheck(a,b,c,d)
  #define sqlite3FkDropTable(a,b,c)
  #define sqlite3FkOldmask(a,b)      0
  #define sqlite3FkRequired(a,b,c,d,e,f) 0
#endif
#ifndef SQLITE_OMIT_FOREIGN_KEY
  void sqlite3FkDelete(sqlite3 *, Table*);
  int sqlite3FkLocateIndex(Parse*,Table*,FKey*,Index**,int**);
#else
  #define sqlite3FkDelete(a,b)
  #define sqlite3FkLocateIndex(a,b,c,d,e)
Changes to src/update.c.
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522

    /* Do constraint checks. */
    sqlite3GenerateConstraintChecks(pParse, pTab, iCur, regNewRowid,
        aRegIdx, (chngRowid?regOldRowid:0), 1, onError, addr, 0);

    /* Do FK constraint checks. */
    if( hasFK ){
      sqlite3FkCheck(pParse, pTab, regOldRowid, 0);
    }

    /* Delete the index entries associated with the current record.  */
    j1 = sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, regOldRowid);
    sqlite3GenerateRowIndexDelete(pParse, pTab, iCur, aRegIdx);
  
    /* If changing the record number, delete the old record.  */
    if( hasFK || chngRowid ){
      sqlite3VdbeAddOp2(v, OP_Delete, iCur, 0);
    }
    sqlite3VdbeJumpHere(v, j1);

    if( hasFK ){
      sqlite3FkCheck(pParse, pTab, 0, regNewRowid);
    }
  
    /* Insert the new index entries and the new record. */
    sqlite3CompleteInsertion(pParse, pTab, iCur, regNewRowid, aRegIdx, 1, 0, 0);

    /* Do any ON CASCADE, SET NULL or SET DEFAULT operations required to
    ** handle rows (possibly in other tables) that refer via a foreign key
    ** to the row just updated. */ 
    if( hasFK ){
      sqlite3FkActions(pParse, pTab, pChanges, regOldRowid);
    }
  }

  /* Increment the row counter 
  */
  if( (db->flags & SQLITE_CountRows) && !pParse->pTriggerTab){
    sqlite3VdbeAddOp2(v, OP_AddImm, regRowCount, 1);







|













|









|







484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522

    /* Do constraint checks. */
    sqlite3GenerateConstraintChecks(pParse, pTab, iCur, regNewRowid,
        aRegIdx, (chngRowid?regOldRowid:0), 1, onError, addr, 0);

    /* Do FK constraint checks. */
    if( hasFK ){
      sqlite3FkCheck(pParse, pTab, regOldRowid, 0, aXRef, chngRowid);
    }

    /* Delete the index entries associated with the current record.  */
    j1 = sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, regOldRowid);
    sqlite3GenerateRowIndexDelete(pParse, pTab, iCur, aRegIdx);
  
    /* If changing the record number, delete the old record.  */
    if( hasFK || chngRowid ){
      sqlite3VdbeAddOp2(v, OP_Delete, iCur, 0);
    }
    sqlite3VdbeJumpHere(v, j1);

    if( hasFK ){
      sqlite3FkCheck(pParse, pTab, 0, regNewRowid, aXRef, chngRowid);
    }
  
    /* Insert the new index entries and the new record. */
    sqlite3CompleteInsertion(pParse, pTab, iCur, regNewRowid, aRegIdx, 1, 0, 0);

    /* Do any ON CASCADE, SET NULL or SET DEFAULT operations required to
    ** handle rows (possibly in other tables) that refer via a foreign key
    ** to the row just updated. */ 
    if( hasFK ){
      sqlite3FkActions(pParse, pTab, pChanges, regOldRowid, aXRef, chngRowid);
    }
  }

  /* Increment the row counter 
  */
  if( (db->flags & SQLITE_CountRows) && !pParse->pTriggerTab){
    sqlite3VdbeAddOp2(v, OP_AddImm, regRowCount, 1);
Changes to src/vdbe.c.
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
  ** the pointer to ctx.s so in case the user-function can use
  ** the already allocated buffer instead of allocating a new one.
  */
  sqlite3VdbeMemMove(&ctx.s, pOut);
  MemSetTypeFlag(&ctx.s, MEM_Null);

  ctx.fErrorOrAux = 0;
  if( ctx.pFunc->flags & SQLITE_FUNC_NEEDCOLL ){
    assert( pOp>aOp );
    assert( pOp[-1].p4type==P4_COLLSEQ );
    assert( pOp[-1].opcode==OP_CollSeq );
    ctx.pColl = pOp[-1].p4.pColl;
  }
  db->lastRowid = lastRowid;
  (*ctx.pFunc->xFunc)(&ctx, n, apVal); /* IMP: R-24505-23230 */







|







1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
  ** the pointer to ctx.s so in case the user-function can use
  ** the already allocated buffer instead of allocating a new one.
  */
  sqlite3VdbeMemMove(&ctx.s, pOut);
  MemSetTypeFlag(&ctx.s, MEM_Null);

  ctx.fErrorOrAux = 0;
  if( ctx.pFunc->funcFlags & SQLITE_FUNC_NEEDCOLL ){
    assert( pOp>aOp );
    assert( pOp[-1].p4type==P4_COLLSEQ );
    assert( pOp[-1].opcode==OP_CollSeq );
    ctx.pColl = pOp[-1].p4.pColl;
  }
  db->lastRowid = lastRowid;
  (*ctx.pFunc->xFunc)(&ctx, n, apVal); /* IMP: R-24505-23230 */
5433
5434
5435
5436
5437
5438
5439
5440
5441
5442
5443
5444
5445
5446
5447
  ctx.s.z = 0;
  ctx.s.zMalloc = 0;
  ctx.s.xDel = 0;
  ctx.s.db = db;
  ctx.isError = 0;
  ctx.pColl = 0;
  ctx.skipFlag = 0;
  if( ctx.pFunc->flags & SQLITE_FUNC_NEEDCOLL ){
    assert( pOp>p->aOp );
    assert( pOp[-1].p4type==P4_COLLSEQ );
    assert( pOp[-1].opcode==OP_CollSeq );
    ctx.pColl = pOp[-1].p4.pColl;
  }
  (ctx.pFunc->xStep)(&ctx, n, apVal); /* IMP: R-24505-23230 */
  if( ctx.isError ){







|







5433
5434
5435
5436
5437
5438
5439
5440
5441
5442
5443
5444
5445
5446
5447
  ctx.s.z = 0;
  ctx.s.zMalloc = 0;
  ctx.s.xDel = 0;
  ctx.s.db = db;
  ctx.isError = 0;
  ctx.pColl = 0;
  ctx.skipFlag = 0;
  if( ctx.pFunc->funcFlags & SQLITE_FUNC_NEEDCOLL ){
    assert( pOp>p->aOp );
    assert( pOp[-1].p4type==P4_COLLSEQ );
    assert( pOp[-1].opcode==OP_CollSeq );
    ctx.pColl = pOp[-1].p4.pColl;
  }
  (ctx.pFunc->xStep)(&ctx, n, apVal); /* IMP: R-24505-23230 */
  if( ctx.isError ){
Changes to src/vdbeaux.c.
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614


/*
** If the input FuncDef structure is ephemeral, then free it.  If
** the FuncDef is not ephermal, then do nothing.
*/
static void freeEphemeralFunction(sqlite3 *db, FuncDef *pDef){
  if( ALWAYS(pDef) && (pDef->flags & SQLITE_FUNC_EPHEM)!=0 ){
    sqlite3DbFree(db, pDef);
  }
}

static void vdbeFreeOpArray(sqlite3 *, Op *, int);

/*







|







600
601
602
603
604
605
606
607
608
609
610
611
612
613
614


/*
** If the input FuncDef structure is ephemeral, then free it.  If
** the FuncDef is not ephermal, then do nothing.
*/
static void freeEphemeralFunction(sqlite3 *db, FuncDef *pDef){
  if( ALWAYS(pDef) && (pDef->funcFlags & SQLITE_FUNC_EPHEM)!=0 ){
    sqlite3DbFree(db, pDef);
  }
}

static void vdbeFreeOpArray(sqlite3 *, Op *, int);

/*
Changes to src/vdbemem.c.
1300
1301
1302
1303
1304
1305
1306


1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
  Expr *pExpr,                    /* The expression to extract a value from */
  u8 affinity,                    /* Affinity to use */
  int iVal,                       /* Array element to populate */
  int *pbOk                       /* OUT: True if value was extracted */
){
  int rc = SQLITE_OK;
  sqlite3_value *pVal = 0;



  struct ValueNewStat4Ctx alloc;
  alloc.pParse = pParse;
  alloc.pIdx = pIdx;
  alloc.ppRec = ppRec;
  alloc.iVal = iVal;

  /* Skip over any TK_COLLATE nodes */
  pExpr = sqlite3ExprSkipCollate(pExpr);

  if( !pExpr ){
    pVal = valueNew(pParse->db, &alloc);
    if( pVal ){
      sqlite3VdbeMemSetNull((Mem*)pVal);
      *pbOk = 1;
    }
  }else if( pExpr->op==TK_VARIABLE
        || (pExpr->op==TK_REGISTER && pExpr->op2==TK_VARIABLE)
  ){
    Vdbe *v;
    int iBindVar = pExpr->iColumn;
    sqlite3VdbeSetVarmask(pParse->pVdbe, iBindVar);
    if( (v = pParse->pReprepare)!=0 ){
      pVal = valueNew(pParse->db, &alloc);
      if( pVal ){
        rc = sqlite3VdbeMemCopy((Mem*)pVal, &v->aVar[iBindVar-1]);
        if( rc==SQLITE_OK ){
          sqlite3ValueApplyAffinity(pVal, affinity, SQLITE_UTF8);
        }
        pVal->db = pParse->db;
        *pbOk = 1;
        sqlite3VdbeMemStoreType((Mem*)pVal);
      }
    }else{
      *pbOk = 0;
    }
  }else{
    sqlite3 *db = pParse->db;
    rc = valueFromExpr(db, pExpr, ENC(db), affinity, &pVal, &alloc);
    *pbOk = (pVal!=0);
  }

  assert( pVal==0 || pVal->db==pParse->db );
  return rc;
}

/*
** Unless it is NULL, the argument must be an UnpackedRecord object returned
** by an earlier call to sqlite3Stat4ProbeSetValue(). This call deletes
** the object.







>
>











|











|



|









<




|







1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345

1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
  Expr *pExpr,                    /* The expression to extract a value from */
  u8 affinity,                    /* Affinity to use */
  int iVal,                       /* Array element to populate */
  int *pbOk                       /* OUT: True if value was extracted */
){
  int rc = SQLITE_OK;
  sqlite3_value *pVal = 0;
  sqlite3 *db = pParse->db;


  struct ValueNewStat4Ctx alloc;
  alloc.pParse = pParse;
  alloc.pIdx = pIdx;
  alloc.ppRec = ppRec;
  alloc.iVal = iVal;

  /* Skip over any TK_COLLATE nodes */
  pExpr = sqlite3ExprSkipCollate(pExpr);

  if( !pExpr ){
    pVal = valueNew(db, &alloc);
    if( pVal ){
      sqlite3VdbeMemSetNull((Mem*)pVal);
      *pbOk = 1;
    }
  }else if( pExpr->op==TK_VARIABLE
        || (pExpr->op==TK_REGISTER && pExpr->op2==TK_VARIABLE)
  ){
    Vdbe *v;
    int iBindVar = pExpr->iColumn;
    sqlite3VdbeSetVarmask(pParse->pVdbe, iBindVar);
    if( (v = pParse->pReprepare)!=0 ){
      pVal = valueNew(db, &alloc);
      if( pVal ){
        rc = sqlite3VdbeMemCopy((Mem*)pVal, &v->aVar[iBindVar-1]);
        if( rc==SQLITE_OK ){
          sqlite3ValueApplyAffinity(pVal, affinity, ENC(db));
        }
        pVal->db = pParse->db;
        *pbOk = 1;
        sqlite3VdbeMemStoreType((Mem*)pVal);
      }
    }else{
      *pbOk = 0;
    }
  }else{

    rc = valueFromExpr(db, pExpr, ENC(db), affinity, &pVal, &alloc);
    *pbOk = (pVal!=0);
  }

  assert( pVal==0 || pVal->db==db );
  return rc;
}

/*
** Unless it is NULL, the argument must be an UnpackedRecord object returned
** by an earlier call to sqlite3Stat4ProbeSetValue(). This call deletes
** the object.
Changes to src/vtab.c.
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
    return pDef;
  }
  *pNew = *pDef;
  pNew->zName = (char *)&pNew[1];
  memcpy(pNew->zName, pDef->zName, sqlite3Strlen30(pDef->zName)+1);
  pNew->xFunc = xFunc;
  pNew->pUserData = pArg;
  pNew->flags |= SQLITE_FUNC_EPHEM;
  return pNew;
}

/*
** Make sure virtual table pTab is contained in the pParse->apVirtualLock[]
** array so that an OP_VBegin will get generated for it.  Add pTab to the
** array if it is missing.  If pTab is already in the array, this routine







|







1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
    return pDef;
  }
  *pNew = *pDef;
  pNew->zName = (char *)&pNew[1];
  memcpy(pNew->zName, pDef->zName, sqlite3Strlen30(pDef->zName)+1);
  pNew->xFunc = xFunc;
  pNew->pUserData = pArg;
  pNew->funcFlags |= SQLITE_FUNC_EPHEM;
  return pNew;
}

/*
** Make sure virtual table pTab is contained in the pParse->apVirtualLock[]
** array so that an OP_VBegin will get generated for it.  Add pTab to the
** array if it is missing.  If pTab is already in the array, this routine
Changes to src/where.c.
2417
2418
2419
2420
2421
2422
2423
2424
2425
2426
2427
2428
2429



2430
2431
2432
2433
2434
2435
2436
  Parse *pParse,              /* Database connection */
  Index *pIdx,                /* Index to consider domain of */
  UnpackedRecord *pRec,       /* Vector of values to consider */
  int roundUp,                /* Round up if true.  Round down if false */
  tRowcnt *aStat              /* OUT: stats written here */
){
  IndexSample *aSample = pIdx->aSample;
  int iCol = pRec->nField-1;  /* Index of required stats in anEq[] etc. */
  int iMin = 0;               /* Smallest sample not yet tested */
  int i = pIdx->nSample;      /* Smallest sample larger than or equal to pRec */
  int iTest;                  /* Next sample to test */
  int res;                    /* Result of comparison operation */




  assert( pIdx->nSample>0 );
  assert( pRec->nField>0 && iCol<pIdx->nSampleCol );
  do{
    iTest = (iMin+i)/2;
    res = sqlite3VdbeRecordCompare(aSample[iTest].n, aSample[iTest].p, pRec);
    if( res<0 ){
      iMin = iTest+1;







|





>
>
>







2417
2418
2419
2420
2421
2422
2423
2424
2425
2426
2427
2428
2429
2430
2431
2432
2433
2434
2435
2436
2437
2438
2439
  Parse *pParse,              /* Database connection */
  Index *pIdx,                /* Index to consider domain of */
  UnpackedRecord *pRec,       /* Vector of values to consider */
  int roundUp,                /* Round up if true.  Round down if false */
  tRowcnt *aStat              /* OUT: stats written here */
){
  IndexSample *aSample = pIdx->aSample;
  int iCol;                   /* Index of required stats in anEq[] etc. */
  int iMin = 0;               /* Smallest sample not yet tested */
  int i = pIdx->nSample;      /* Smallest sample larger than or equal to pRec */
  int iTest;                  /* Next sample to test */
  int res;                    /* Result of comparison operation */

  assert( pRec!=0 || pParse->db->mallocFailed );
  if( pRec==0 ) return;
  iCol = pRec->nField - 1;
  assert( pIdx->nSample>0 );
  assert( pRec->nField>0 && iCol<pIdx->nSampleCol );
  do{
    iTest = (iMin+i)/2;
    res = sqlite3VdbeRecordCompare(aSample[iTest].n, aSample[iTest].p, pRec);
    if( res<0 ){
      iMin = iTest+1;
2542
2543
2544
2545
2546
2547
2548

2549
2550
2551
2552
2553
2554
2555
2556
2557
2558
2559
2560
2561
2562
2563
  int rc = SQLITE_OK;
  int nOut = (int)*pnOut;

#ifdef SQLITE_ENABLE_STAT3_OR_STAT4
  Index *p = pBuilder->pNew->u.btree.pIndex;
  int nEq = pBuilder->pNew->u.btree.nEq;


  if( nEq==pBuilder->nRecValid
   && nEq<p->nSampleCol
   && p->nSample 
   && OptimizationEnabled(pParse->db, SQLITE_Stat3) 
  ){
    UnpackedRecord *pRec = pBuilder->pRec;
    tRowcnt a[2];
    u8 aff = p->pTable->aCol[p->aiColumn[0]].affinity;

    /* Variable iLower will be set to the estimate of the number of rows in 
    ** the index that are less than the lower bound of the range query. The
    ** lower bound being the concatenation of $P and $L, where $P is the
    ** key-prefix formed by the nEq values matched against the nEq left-most
    ** columns of the index, and $L is the value in pLower.
    **







>
|

<




|







2545
2546
2547
2548
2549
2550
2551
2552
2553
2554

2555
2556
2557
2558
2559
2560
2561
2562
2563
2564
2565
2566
  int rc = SQLITE_OK;
  int nOut = (int)*pnOut;

#ifdef SQLITE_ENABLE_STAT3_OR_STAT4
  Index *p = pBuilder->pNew->u.btree.pIndex;
  int nEq = pBuilder->pNew->u.btree.nEq;

  if( p->nSample>0
   && nEq==pBuilder->nRecValid
   && nEq<p->nSampleCol

   && OptimizationEnabled(pParse->db, SQLITE_Stat3) 
  ){
    UnpackedRecord *pRec = pBuilder->pRec;
    tRowcnt a[2];
    u8 aff;

    /* Variable iLower will be set to the estimate of the number of rows in 
    ** the index that are less than the lower bound of the range query. The
    ** lower bound being the concatenation of $P and $L, where $P is the
    ** key-prefix formed by the nEq values matched against the nEq left-most
    ** columns of the index, and $L is the value in pLower.
    **
2571
2572
2573
2574
2575
2576
2577





2578
2579
2580
2581
2582
2583
2584
    ** less than the upper bound of the range query. Where the upper bound
    ** is either ($P) or ($P:$U). Again, even if $U is available, both values
    ** of iUpper are requested of whereKeyStats() and the smaller used.
    */
    tRowcnt iLower;
    tRowcnt iUpper;






    /* Determine iLower and iUpper using ($P) only. */
    if( nEq==0 ){
      iLower = 0;
      iUpper = p->aiRowEst[0];
    }else{
      /* Note: this call could be optimized away - since the same values must 
      ** have been requested when testing key $P in whereEqualScanEst().  */







>
>
>
>
>







2574
2575
2576
2577
2578
2579
2580
2581
2582
2583
2584
2585
2586
2587
2588
2589
2590
2591
2592
    ** less than the upper bound of the range query. Where the upper bound
    ** is either ($P) or ($P:$U). Again, even if $U is available, both values
    ** of iUpper are requested of whereKeyStats() and the smaller used.
    */
    tRowcnt iLower;
    tRowcnt iUpper;

    if( nEq==p->nColumn ){
      aff = SQLITE_AFF_INTEGER;
    }else{
      aff = p->pTable->aCol[p->aiColumn[nEq]].affinity;
    }
    /* Determine iLower and iUpper using ($P) only. */
    if( nEq==0 ){
      iLower = 0;
      iUpper = p->aiRowEst[0];
    }else{
      /* Note: this call could be optimized away - since the same values must 
      ** have been requested when testing key $P in whereEqualScanEst().  */
4056
4057
4058
4059
4060
4061
4062
4063
4064




4065
4066
4067
4068
4069
4070
4071
  return SQLITE_OK;
}

/*
** Transfer content from the second pLoop into the first.
*/
static int whereLoopXfer(sqlite3 *db, WhereLoop *pTo, WhereLoop *pFrom){
  if( whereLoopResize(db, pTo, pFrom->nLTerm) ) return SQLITE_NOMEM;
  whereLoopClearUnion(db, pTo);




  memcpy(pTo, pFrom, WHERE_LOOP_XFER_SZ);
  memcpy(pTo->aLTerm, pFrom->aLTerm, pTo->nLTerm*sizeof(pTo->aLTerm[0]));
  if( pFrom->wsFlags & WHERE_VIRTUALTABLE ){
    pFrom->u.vtab.needFree = 0;
  }else if( (pFrom->wsFlags & WHERE_AUTO_INDEX)!=0 ){
    pFrom->u.btree.pIndex = 0;
  }







<

>
>
>
>







4064
4065
4066
4067
4068
4069
4070

4071
4072
4073
4074
4075
4076
4077
4078
4079
4080
4081
4082
  return SQLITE_OK;
}

/*
** Transfer content from the second pLoop into the first.
*/
static int whereLoopXfer(sqlite3 *db, WhereLoop *pTo, WhereLoop *pFrom){

  whereLoopClearUnion(db, pTo);
  if( whereLoopResize(db, pTo, pFrom->nLTerm) ){
    memset(&pTo->u, 0, sizeof(pTo->u));
    return SQLITE_NOMEM;
  }
  memcpy(pTo, pFrom, WHERE_LOOP_XFER_SZ);
  memcpy(pTo->aLTerm, pFrom->aLTerm, pTo->nLTerm*sizeof(pTo->aLTerm[0]));
  if( pFrom->wsFlags & WHERE_VIRTUALTABLE ){
    pFrom->u.vtab.needFree = 0;
  }else if( (pFrom->wsFlags & WHERE_AUTO_INDEX)!=0 ){
    pFrom->u.btree.pIndex = 0;
  }
4171
4172
4173
4174
4175
4176
4177

4178
4179
4180
4181
4182
4183
4184
4185
4186
4187
4188
4189
     && p->rRun<=pTemplate->rRun
     && p->nOut<=pTemplate->nOut
    ){
      /* This branch taken when p is equal or better than pTemplate in 
      ** all of (1) dependencies (2) setup-cost, (3) run-cost, and
      ** (4) number of output rows. */
      assert( p->rSetup==pTemplate->rSetup );

      if( p->nLTerm<pTemplate->nLTerm
       && (p->wsFlags & WHERE_INDEXED)!=0
       && (pTemplate->wsFlags & WHERE_INDEXED)!=0
       && p->u.btree.pIndex==pTemplate->u.btree.pIndex
       && p->prereq==pTemplate->prereq
      ){
        /* Overwrite an existing WhereLoop with an similar one that uses
        ** more terms of the index */
        pNext = p->pNextLoop;
        break;
      }else{
        /* pTemplate is not helpful.







>
|



<







4182
4183
4184
4185
4186
4187
4188
4189
4190
4191
4192
4193

4194
4195
4196
4197
4198
4199
4200
     && p->rRun<=pTemplate->rRun
     && p->nOut<=pTemplate->nOut
    ){
      /* This branch taken when p is equal or better than pTemplate in 
      ** all of (1) dependencies (2) setup-cost, (3) run-cost, and
      ** (4) number of output rows. */
      assert( p->rSetup==pTemplate->rSetup );
      if( p->prereq==pTemplate->prereq
       && p->nLTerm<pTemplate->nLTerm
       && (p->wsFlags & WHERE_INDEXED)!=0
       && (pTemplate->wsFlags & WHERE_INDEXED)!=0
       && p->u.btree.pIndex==pTemplate->u.btree.pIndex

      ){
        /* Overwrite an existing WhereLoop with an similar one that uses
        ** more terms of the index */
        pNext = p->pNextLoop;
        break;
      }else{
        /* pTemplate is not helpful.
5900
5901
5902
5903
5904
5905
5906
5907
5908
5909
5910
5911
5912
5913
5914
#endif
  /* Attempt to omit tables from the join that do not effect the result */
  if( pWInfo->nLevel>=2
   && pResultSet!=0
   && OptimizationEnabled(db, SQLITE_OmitNoopJoin)
  ){
    Bitmask tabUsed = exprListTableUsage(pMaskSet, pResultSet);
    if( pOrderBy ) tabUsed |= exprListTableUsage(pMaskSet, pOrderBy);
    while( pWInfo->nLevel>=2 ){
      WhereTerm *pTerm, *pEnd;
      pLoop = pWInfo->a[pWInfo->nLevel-1].pWLoop;
      if( (pWInfo->pTabList->a[pLoop->iTab].jointype & JT_LEFT)==0 ) break;
      if( (wctrlFlags & WHERE_WANT_DISTINCT)==0
       && (pLoop->wsFlags & WHERE_ONEROW)==0
      ){







|







5911
5912
5913
5914
5915
5916
5917
5918
5919
5920
5921
5922
5923
5924
5925
#endif
  /* Attempt to omit tables from the join that do not effect the result */
  if( pWInfo->nLevel>=2
   && pResultSet!=0
   && OptimizationEnabled(db, SQLITE_OmitNoopJoin)
  ){
    Bitmask tabUsed = exprListTableUsage(pMaskSet, pResultSet);
    if( sWLB.pOrderBy ) tabUsed |= exprListTableUsage(pMaskSet, sWLB.pOrderBy);
    while( pWInfo->nLevel>=2 ){
      WhereTerm *pTerm, *pEnd;
      pLoop = pWInfo->a[pWInfo->nLevel-1].pWLoop;
      if( (pWInfo->pTabList->a[pLoop->iTab].jointype & JT_LEFT)==0 ) break;
      if( (wctrlFlags & WHERE_WANT_DISTINCT)==0
       && (pLoop->wsFlags & WHERE_ONEROW)==0
      ){
Changes to test/analyze9.test.
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
    neq,
    lrange(nlt, 0, 2),
    lrange(ndlt, 0, 2),
    lrange(test_decode(sample), 0, 1)
    FROM sqlite_stat4
  ORDER BY rowid DESC LIMIT 2;
} {
  {2 1 1 1} {295 296 296} {120 122 125} {201 4} 
  {5 3 1 1} {290 290 292} {119 119 121} {200 1}
}

do_execsql_test 4.4 { SELECT count(DISTINCT c) FROM t1 WHERE c<201 } 120
do_execsql_test 4.5 { SELECT count(DISTINCT c) FROM t1 WHERE c<200 } 119

# Check that the perioidic samples are present.







|







240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
    neq,
    lrange(nlt, 0, 2),
    lrange(ndlt, 0, 2),
    lrange(test_decode(sample), 0, 1)
    FROM sqlite_stat4
  ORDER BY rowid DESC LIMIT 2;
} {
  {2 1 1 1} {295 295 295} {120 121 124} {201 3} 
  {5 3 1 1} {290 290 292} {119 119 121} {200 1}
}

do_execsql_test 4.4 { SELECT count(DISTINCT c) FROM t1 WHERE c<201 } 120
do_execsql_test 4.5 { SELECT count(DISTINCT c) FROM t1 WHERE c<200 } 119

# Check that the perioidic samples are present.
552
553
554
555
556
557
558





559




560



561




























































































































































































































































































































































































    set sql {
      SELECT * FROM t4 WHERE x=X'abcdef' AND a COLLATE nocase = 'abc' AND b = 3
    }
    do_eqp_test 12.$tn.6 $sql {/t4 USING INDEX t4a/}
  }
}






finish_test












































































































































































































































































































































































































>
>
>
>
>
|
>
>
>
>
|
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
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
640
641
642
643
644
645
646
647
648
649
650
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
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
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
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
    set sql {
      SELECT * FROM t4 WHERE x=X'abcdef' AND a COLLATE nocase = 'abc' AND b = 3
    }
    do_eqp_test 12.$tn.6 $sql {/t4 USING INDEX t4a/}
  }
}

#-------------------------------------------------------------------------
# Check that affinities are taken into account when using stat4 data to
# estimate the number of rows scanned by a rowid constraint.
#
drop_all_tables
do_test 13.1 {
  execsql {
    CREATE TABLE t1(a, b, c);
    CREATE INDEX i1 ON t1(a);
    CREATE INDEX i2 ON t1(b, c);
  }
  for {set i 0} {$i<100} {incr i} {
    if {$i %2} {set a abc} else {set a def}
    execsql { INSERT INTO t1(rowid, a, b, c) VALUES($i, $a, $i, $i) }
  }
  execsql ANALYZE
} {}
do_eqp_test 13.2.1 {
  SELECT * FROM t1 WHERE a='abc' AND rowid<15 AND b<20
} {/SEARCH TABLE t1 USING INDEX i1/}
do_eqp_test 13.2.2 {
  SELECT * FROM t1 WHERE a='abc' AND rowid<'15' AND b<20
} {/SEARCH TABLE t1 USING INDEX i1/}
do_eqp_test 13.3.1 {
  SELECT * FROM t1 WHERE a='abc' AND rowid<100 AND b<20
} {/SEARCH TABLE t1 USING INDEX i2/}
do_eqp_test 13.3.2 {
  SELECT * FROM t1 WHERE a='abc' AND rowid<'100' AND b<20
} {/SEARCH TABLE t1 USING INDEX i2/}

#-------------------------------------------------------------------------
# Check also that affinities are taken into account when using stat4 data 
# to estimate the number of rows scanned by any other constraint on a 
# column other than the leftmost.
#
drop_all_tables
do_test 14.1 {
  execsql { CREATE TABLE t1(a, b INTEGER, c) }
  for {set i 0} {$i<100} {incr i} {
    set c [expr $i % 3]
    execsql { INSERT INTO t1 VALUES('ott', $i, $c) }
  }
  execsql {
    CREATE INDEX i1 ON t1(a, b);
    CREATE INDEX i2 ON t1(c);
    ANALYZE;
  }
} {}
do_eqp_test 13.2.1 {
  SELECT * FROM t1 WHERE a='ott' AND b<10 AND c=1
} {/SEARCH TABLE t1 USING INDEX i1/}
do_eqp_test 13.2.2 {
  SELECT * FROM t1 WHERE a='ott' AND b<'10' AND c=1
} {/SEARCH TABLE t1 USING INDEX i1/}

#-------------------------------------------------------------------------
# By default, 16 non-periodic samples are collected for the stat4 table.
# The following tests attempt to verify that the most common keys are
# being collected.
#
proc check_stat4 {tn} {
  db eval ANALYZE
  db eval {SELECT a, b, c, d FROM t1} {
    incr k($a)
    incr k([list $a $b])
    incr k([list $a $b $c])
    if { [info exists k([list $a $b $c $d])]==0 } { incr nRow }
    incr k([list $a $b $c $d])
  }

  set L [list]
  foreach key [array names k] {
    lappend L [list $k($key) $key]
  }

  set nSample $nRow
  if {$nSample>16} {set nSample 16}

  set nThreshold [lindex [lsort -decr -integer -index 0 $L] [expr $nSample-1] 0]
  foreach key [array names k] {
    if {$k($key)>$nThreshold} {
      set expect($key) 1
    }
    if {$k($key)==$nThreshold} {
      set possible($key) 1
    }
  }


  set nPossible [expr $nSample - [llength [array names expect]]]

  #puts "EXPECT: [array names expect]"
  #puts "POSSIBLE($nPossible/[array size possible]): [array names possible]"
  #puts "HAVE: [db eval {SELECT test_decode(sample) FROM sqlite_stat4 WHERE idx='i1'}]"

  db eval {SELECT test_decode(sample) AS s FROM sqlite_stat4 WHERE idx='i1'} {
    set seen 0
    for {set i 0} {$i<4} {incr i} {
      unset -nocomplain expect([lrange $s 0 $i])
      if {[info exists possible([lrange $s 0 $i])]} {
        set seen 1
        unset -nocomplain possible([lrange $s 0 $i])
      }
    }
    if {$seen} {incr nPossible -1}
  }
  if {$nPossible<0} {set nPossible 0}

  set res [list [llength [array names expect]] $nPossible]
  uplevel [list do_test $tn [list set {} $res] {0 0}]
}

drop_all_tables
do_test 14.1.1 {
  execsql {
    CREATE TABLE t1(a,b,c,d);
    CREATE INDEX i1 ON t1(a,b,c,d);
  }
  for {set i 0} {$i < 160} {incr i} {
    execsql { INSERT INTO t1 VALUES($i,$i,$i,$i) }
    if {($i % 10)==0} { execsql { INSERT INTO t1 VALUES($i,$i,$i,$i) } }
  }
} {}
check_stat4 14.1.2

do_test 14.2.1 {
  execsql { DELETE FROM t1 }
  for {set i 0} {$i < 1600} {incr i} {
    execsql { INSERT INTO t1 VALUES($i/10,$i/17,$i/27,$i/37) }
  }
} {}
check_stat4 14.2.2

do_test 14.3.1 {
  for {set i 0} {$i < 10} {incr i} {
    execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) }
    execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) }
    execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) }
    execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) }
    execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) }
    execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) }
    execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) }
    execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) }
    execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) }
    execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) }
  }
} {}
check_stat4 14.3.2

do_test 14.4.1 {
  execsql {DELETE FROM t1}
  for {set i 1} {$i < 160} {incr i} {
    set b [expr $i % 10]
    if {$b==0 || $b==2} {set b 1}
    execsql { INSERT INTO t1 VALUES($i/10,$b,$i,$i) }
  }
} {}
check_stat4 14.4.2
db func lrange lrange
db func lindex lindex
do_execsql_test 14.4.3 {
  SELECT lrange(test_decode(sample), 0, 1) AS s FROM sqlite_stat4
  WHERE lindex(s, 1)=='1' ORDER BY rowid
} {
  {0 1} {1 1} {2 1} {3 1} 
  {4 1} {5 1} {6 1} {7 1} 
  {8 1} {9 1} {10 1} {11 1} 
  {12 1} {13 1} {14 1} {15 1}
}

#-------------------------------------------------------------------------
# Test that nothing untoward happens if the stat4 table contains entries
# for indexes that do not exist. Or NULL values in the idx column.
# Or NULL values in any of the other columns.
#
drop_all_tables
do_execsql_test 15.1 {
  CREATE TABLE x1(a, b, UNIQUE(a, b));
  INSERT INTO x1 VALUES(1, 2);
  INSERT INTO x1 VALUES(3, 4);
  INSERT INTO x1 VALUES(5, 6);
  ANALYZE;
  INSERT INTO sqlite_stat4 VALUES(NULL, NULL, NULL, NULL, NULL, NULL);
}
db close
sqlite3 db test.db
do_execsql_test 15.2 { SELECT * FROM x1 } {1 2 3 4 5 6}

do_execsql_test 15.3 {
  INSERT INTO sqlite_stat4 VALUES(42, 42, 42, 42, 42, 42);
}
db close
sqlite3 db test.db
do_execsql_test 15.4 { SELECT * FROM x1 } {1 2 3 4 5 6}

do_execsql_test 15.5 {
  UPDATE sqlite_stat1 SET stat = NULL;
}
db close
sqlite3 db test.db
do_execsql_test 15.6 { SELECT * FROM x1 } {1 2 3 4 5 6}

do_execsql_test 15.7 {
  ANALYZE;
  UPDATE sqlite_stat1 SET tbl = 'no such tbl';
}
db close
sqlite3 db test.db
do_execsql_test 15.8 { SELECT * FROM x1 } {1 2 3 4 5 6}

do_execsql_test 15.9 {
  ANALYZE;
  UPDATE sqlite_stat4 SET neq = NULL, nlt=NULL, ndlt=NULL;
}
db close
sqlite3 db test.db
do_execsql_test 15.10 { SELECT * FROM x1 } {1 2 3 4 5 6}

# This is just for coverage....
do_execsql_test 15.11 {
  ANALYZE;
  UPDATE sqlite_stat1 SET stat = stat || ' unordered';
}
db close
sqlite3 db test.db
do_execsql_test 15.12 { SELECT * FROM x1 } {1 2 3 4 5 6}

#-------------------------------------------------------------------------
# Test that allocations used for sqlite_stat4 samples are included in
# the quantity returned by SQLITE_DBSTATUS_SCHEMA_USED.
#
set one [string repeat x 1000]
set two [string repeat x 2000]
do_test 16.1 {
  reset_db
  execsql {
    CREATE TABLE t1(a, UNIQUE(a));
    INSERT INTO t1 VALUES($one);
    ANALYZE;
  }
  set nByte [lindex [sqlite3_db_status db SCHEMA_USED 0] 1]

  reset_db
  execsql {
    CREATE TABLE t1(a, UNIQUE(a));
    INSERT INTO t1 VALUES($two);
    ANALYZE;
  }
  set nByte2 [lindex [sqlite3_db_status db SCHEMA_USED 0] 1]

  expr {$nByte2 > $nByte+950 && $nByte2 < $nByte+1050}
} {1}

#-------------------------------------------------------------------------
# Test that stat4 data may be used with partial indexes.
#
do_test 17.1 {
  reset_db
  execsql {
    CREATE TABLE t1(a, b, c, d);
    CREATE INDEX i1 ON t1(a, b) WHERE d IS NOT NULL;
    INSERT INTO t1 VALUES(-1, -1, -1, NULL);
    INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1;
    INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1;
    INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1;
    INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1;
    INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1;
    INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1;
  }

  for {set i 0} {$i < 32} {incr i} {
    if {$i<8} {set b 0} else { set b $i }
    execsql { INSERT INTO t1 VALUES($i%2, $b, $i/2, 'abc') }
  }
  execsql {ANALYZE main.t1}
} {}

do_catchsql_test 17.1.2 {
  ANALYZE temp.t1;
} {1 {no such table: temp.t1}}

do_eqp_test 17.2 {
  SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=10 AND c=10;
} {/USING INDEX i1/}
do_eqp_test 17.3 {
  SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=0 AND c=10;
} {/USING INDEX i1/}

do_execsql_test 17.4 {
  CREATE INDEX i2 ON t1(c);
  ANALYZE main.i2;
}
do_eqp_test 17.5 {
  SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=10 AND c=10;
} {/USING INDEX i1/}
do_eqp_test 17.6 {
  SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=0 AND c=10;
} {/USING INDEX i2/}

#-------------------------------------------------------------------------
#
do_test 18.1 {
  reset_db
  execsql {
    CREATE TABLE t1(a, b);
    CREATE INDEX i1 ON t1(a, b);
  }
  for {set i 0} {$i < 9} {incr i} {
    execsql {
      INSERT INTO t1 VALUES($i, 0);
      INSERT INTO t1 VALUES($i, 0);
      INSERT INTO t1 VALUES($i, 0);
      INSERT INTO t1 VALUES($i, 0);
      INSERT INTO t1 VALUES($i, 0);
      INSERT INTO t1 VALUES($i, 0);
      INSERT INTO t1 VALUES($i, 0);
      INSERT INTO t1 VALUES($i, 0);
      INSERT INTO t1 VALUES($i, 0);
      INSERT INTO t1 VALUES($i, 0);
      INSERT INTO t1 VALUES($i, 0);
      INSERT INTO t1 VALUES($i, 0);
      INSERT INTO t1 VALUES($i, 0);
      INSERT INTO t1 VALUES($i, 0);
      INSERT INTO t1 VALUES($i, 0);
    }
  }
  execsql ANALYZE
  execsql { SELECT count(*) FROM sqlite_stat4 }
} {9}

#-------------------------------------------------------------------------
# For coverage.
#
ifcapable view {
  do_test 19.1 {
    reset_db 
    execsql {
      CREATE TABLE t1(x, y);
      CREATE INDEX i1 ON t1(x, y);
      CREATE VIEW v1 AS SELECT * FROM t1;
      ANALYZE;
    }
  } {}
}
ifcapable auth {
  proc authproc {op args} {
    if {$op == "SQLITE_ANALYZE"} { return "SQLITE_DENY" }
    return "SQLITE_OK"
  }
  do_test 19.2 {
    reset_db 
    db auth authproc
    execsql {
      CREATE TABLE t1(x, y);
      CREATE VIEW v1 AS SELECT * FROM t1;
    }
    catchsql ANALYZE
  } {1 {not authorized}}
}

#-------------------------------------------------------------------------
#
reset_db
proc r {args} { expr rand() }
db func r r
db func lrange lrange
do_test 20.1 {
  execsql {
    CREATE TABLE t1(a,b,c,d);
    CREATE INDEX i1 ON t1(a,b,c,d);
  }
  for {set i 0} {$i < 16} {incr i} {
    execsql {
      INSERT INTO t1 VALUES($i, r(), r(), r());
      INSERT INTO t1 VALUES($i, $i,  r(), r());
      INSERT INTO t1 VALUES($i, $i,  $i,  r());
      INSERT INTO t1 VALUES($i, $i,  $i,  $i);
      INSERT INTO t1 VALUES($i, $i,  $i,  $i);
      INSERT INTO t1 VALUES($i, $i,  $i,  r());
      INSERT INTO t1 VALUES($i, $i,  r(), r());
      INSERT INTO t1 VALUES($i, r(), r(), r());
    }
  }
} {}
do_execsql_test 20.2 { ANALYZE }
for {set i 0} {$i<16} {incr i} {
    set val "$i $i $i $i"
    do_execsql_test 20.3.$i {
      SELECT count(*) FROM sqlite_stat4 
      WHERE lrange(test_decode(sample), 0, 3)=$val
    } {1}
}

finish_test

Added test/analyzeB.test.






















































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
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
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
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
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
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
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
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
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
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
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
640
641
642
643
644
645
646
647
648
649
650
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
# 2013 August 3
#
# 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 contains automated tests used to verify that the sqlite_stat3
# functionality is working. The tests in this file are based on a subset
# of the sqlite_stat4 tests in analyze9.test.
#

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

ifcapable !stat3 {
  finish_test
  return
}

do_execsql_test 1.0 {
  CREATE TABLE t1(a TEXT, b TEXT); 
  INSERT INTO t1 VALUES('(0)', '(0)');
  INSERT INTO t1 VALUES('(1)', '(1)');
  INSERT INTO t1 VALUES('(2)', '(2)');
  INSERT INTO t1 VALUES('(3)', '(3)');
  INSERT INTO t1 VALUES('(4)', '(4)');
  CREATE INDEX i1 ON t1(a, b);
} {}


do_execsql_test 1.1 {
  ANALYZE;
} {}

do_execsql_test 1.2 {
  SELECT tbl,idx,nEq,nLt,nDLt,quote(sample) FROM sqlite_stat3;
} {
  t1 i1 1 0 0 '(0)'
  t1 i1 1 1 1 '(1)'
  t1 i1 1 2 2 '(2)'
  t1 i1 1 3 3 '(3)'
  t1 i1 1 4 4 '(4)'
}

if {[permutation] != "utf16"} {
  do_execsql_test 1.3 {
    SELECT tbl,idx,nEq,nLt,nDLt,quote(sample) FROM sqlite_stat3;
  } {
    t1 i1 1 0 0 '(0)'
    t1 i1 1 1 1 '(1)'
    t1 i1 1 2 2 '(2)'
    t1 i1 1 3 3 '(3)'
    t1 i1 1 4 4 '(4)'
  }
}


#-------------------------------------------------------------------------
# This is really just to test SQL user function "test_decode".
#
reset_db
do_execsql_test 2.1 {
  CREATE TABLE t1(a, b, c);
  INSERT INTO t1(a) VALUES('some text');
  INSERT INTO t1(a) VALUES(14);
  INSERT INTO t1(a) VALUES(NULL);
  INSERT INTO t1(a) VALUES(22.0);
  INSERT INTO t1(a) VALUES(x'656667');
  CREATE INDEX i1 ON t1(a, b, c);
  ANALYZE;
  SELECT quote(sample) FROM sqlite_stat3;
} {
  NULL 14 22.0 {'some text'} X'656667' 
}

#-------------------------------------------------------------------------
# 
reset_db
do_execsql_test 3.1 {
  CREATE TABLE t2(a, b);
  CREATE INDEX i2 ON t2(a, b);
  BEGIN;
}

do_test 3.2 {
  for {set i 0} {$i < 1000} {incr i} {
    set a [expr $i / 10]
    set b [expr int(rand() * 15.0)]
    execsql { INSERT INTO t2 VALUES($a, $b) }
  }
  execsql COMMIT
} {}

db func lindex lindex

# Each value of "a" occurs exactly 10 times in the table.
#
do_execsql_test 3.3.1 {
  SELECT count(*) FROM t2 GROUP BY a;
} [lrange [string repeat "10 " 100] 0 99]

# The first element in the "nEq" list of all samples should therefore be 10.
#
do_execsql_test 3.3.2 {
  ANALYZE;
  SELECT nEq FROM sqlite_stat3;
} [lrange [string repeat "10 " 100] 0 23]

#-------------------------------------------------------------------------
# 
do_execsql_test 3.4 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
  INSERT INTO t1 VALUES(1, 1, 'one-a');
  INSERT INTO t1 VALUES(11, 1, 'one-b');
  INSERT INTO t1 VALUES(21, 1, 'one-c');
  INSERT INTO t1 VALUES(31, 1, 'one-d');
  INSERT INTO t1 VALUES(41, 1, 'one-e');
  INSERT INTO t1 VALUES(51, 1, 'one-f');
  INSERT INTO t1 VALUES(61, 1, 'one-g');
  INSERT INTO t1 VALUES(71, 1, 'one-h');
  INSERT INTO t1 VALUES(81, 1, 'one-i');
  INSERT INTO t1 VALUES(91, 1, 'one-j');
  INSERT INTO t1 SELECT a+1,2,'two' || substr(c,4) FROM t1;
  INSERT INTO t1 SELECT a+2,3,'three'||substr(c,4) FROM t1 WHERE c GLOB 'one-*';
  INSERT INTO t1 SELECT a+3,4,'four'||substr(c,4) FROM t1 WHERE c GLOB 'one-*';
  INSERT INTO t1 SELECT a+4,5,'five'||substr(c,4) FROM t1 WHERE c GLOB 'one-*';
  INSERT INTO t1 SELECT a+5,6,'six'||substr(c,4) FROM t1 WHERE c GLOB 'one-*';	
  CREATE INDEX t1b ON t1(b);
  ANALYZE;
  SELECT c FROM t1 WHERE b=3 AND a BETWEEN 30 AND 60;
} {three-d three-e three-f}


#-------------------------------------------------------------------------
# These tests verify that the sample selection for stat3 appears to be 
# working as designed.
#

reset_db
db func lindex lindex
db func lrange lrange

do_execsql_test 4.0 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a, b, c);
  CREATE INDEX i1 ON t1(c, b, a);
}


proc insert_filler_rows_n {iStart args} {
  set A(-ncopy) 1
  set A(-nval) 1

  foreach {k v} $args {
    if {[info exists A($k)]==0} { error "no such option: $k" }
    set A($k) $v
  }
  if {[llength $args] % 2} {
    error "option requires an argument: [lindex $args end]"
  }

  for {set i 0} {$i < $A(-nval)} {incr i} {
    set iVal [expr $iStart+$i]
    for {set j 0} {$j < $A(-ncopy)} {incr j} {
      execsql { INSERT INTO t1 VALUES($iVal, $iVal, $iVal) }
    }
  }
}

do_test 4.1 {
  execsql { BEGIN }
  insert_filler_rows_n  0  -ncopy 10 -nval 19
  insert_filler_rows_n 20  -ncopy  1 -nval 100

  execsql {
    INSERT INTO t1(c, b, a) VALUES(200, 1, 'a');
    INSERT INTO t1(c, b, a) VALUES(200, 1, 'b');
    INSERT INTO t1(c, b, a) VALUES(200, 1, 'c');

    INSERT INTO t1(c, b, a) VALUES(200, 2, 'e');
    INSERT INTO t1(c, b, a) VALUES(200, 2, 'f');

    INSERT INTO t1(c, b, a) VALUES(201, 3, 'g');
    INSERT INTO t1(c, b, a) VALUES(201, 4, 'h');

    ANALYZE;
    SELECT count(*) FROM sqlite_stat3;
    SELECT count(*) FROM t1;
  }
} {24 297}

do_execsql_test 4.2 {
  SELECT neq, nlt, ndlt, sample FROM sqlite_stat3 ORDER BY rowid LIMIT 16;
} {
  10 0 0 0
  10 10 1 1
  10 20 2 2
  10 30 3 3
  10 40 4 4
  10 50 5 5
  10 60 6 6
  10 70 7 7
  10 80 8 8
  10 90 9 9
  10 100 10 10
  10 110 11 11
  10 120 12 12
  10 130 13 13
  10 140 14 14
  10 150 15 15
}

do_execsql_test 4.3 {
  SELECT neq, nlt, ndlt, sample FROM sqlite_stat3
  ORDER BY rowid DESC LIMIT 2;
} {
  2 295 120 201
  5 290 119 200
}

do_execsql_test 4.4 { SELECT count(DISTINCT c) FROM t1 WHERE c<201 } 120
do_execsql_test 4.5 { SELECT count(DISTINCT c) FROM t1 WHERE c<200 } 119

reset_db
do_test 4.7 {
  execsql { 
    BEGIN;
    CREATE TABLE t1(o,t INTEGER PRIMARY KEY);
    CREATE INDEX i1 ON t1(o);
  }
  for {set i 0} {$i<10000} {incr i [expr (($i<1000)?1:10)]} {
    execsql { INSERT INTO t1 VALUES('x', $i) }
  }
  execsql {
    COMMIT;
    ANALYZE;
    SELECT count(*) FROM sqlite_stat3;
  }
} {1}
do_execsql_test 4.8 {
  SELECT sample FROM sqlite_stat3;
} {x}


#-------------------------------------------------------------------------
# The following would cause a crash at one point.
#
reset_db
do_execsql_test 5.1 {
  PRAGMA encoding = 'utf-16';
  CREATE TABLE t0(v);
  ANALYZE;
}

#-------------------------------------------------------------------------
# This was also crashing (corrupt sqlite_stat3 table).
#
reset_db
do_execsql_test 6.1 {
  CREATE TABLE t1(a, b);
  CREATE INDEX i1 ON t1(a);
  CREATE INDEX i2 ON t1(b);
  INSERT INTO t1 VALUES(1, 1);
  INSERT INTO t1 VALUES(2, 2);
  INSERT INTO t1 VALUES(3, 3);
  INSERT INTO t1 VALUES(4, 4);
  INSERT INTO t1 VALUES(5, 5);
  ANALYZE;
  PRAGMA writable_schema = 1;
  CREATE TEMP TABLE x1 AS
    SELECT tbl,idx,neq,nlt,ndlt,sample FROM sqlite_stat3
    ORDER BY (rowid%5), rowid;
  DELETE FROM sqlite_stat3;
  INSERT INTO sqlite_stat3 SELECT * FROM x1;
  PRAGMA writable_schema = 0;
  ANALYZE sqlite_master;
}
do_execsql_test 6.2 {
  SELECT * FROM t1 WHERE a = 'abc';
}

#-------------------------------------------------------------------------
# The following tests experiment with adding corrupted records to the
# 'sample' column of the sqlite_stat3 table.
#
reset_db
sqlite3_db_config_lookaside db 0 0 0

do_execsql_test 7.1 {
  CREATE TABLE t1(a, b);
  CREATE INDEX i1 ON t1(a, b);
  INSERT INTO t1 VALUES(1, 1);
  INSERT INTO t1 VALUES(2, 2);
  INSERT INTO t1 VALUES(3, 3);
  INSERT INTO t1 VALUES(4, 4);
  INSERT INTO t1 VALUES(5, 5);
  ANALYZE;
  UPDATE sqlite_stat3 SET sample = X'' WHERE rowid = 1;
  ANALYZE sqlite_master;
}

do_execsql_test 7.2 {
  UPDATE sqlite_stat3 SET sample = X'FFFF';
  ANALYZE sqlite_master;
  SELECT * FROM t1 WHERE a = 1;
} {1 1}

do_execsql_test 7.3 {
  ANALYZE;
  UPDATE sqlite_stat3 SET neq = '0 0 0';
  ANALYZE sqlite_master;
  SELECT * FROM t1 WHERE a = 1;
} {1 1}

do_execsql_test 7.4 {
  ANALYZE;
  UPDATE sqlite_stat3 SET ndlt = '0 0 0';
  ANALYZE sqlite_master;
  SELECT * FROM t1 WHERE a = 3;
} {3 3}

do_execsql_test 7.5 {
  ANALYZE;
  UPDATE sqlite_stat3 SET nlt = '0 0 0';
  ANALYZE sqlite_master;
  SELECT * FROM t1 WHERE a = 5;
} {5 5}

#-------------------------------------------------------------------------
#
reset_db
do_execsql_test 8.1 {
  CREATE TABLE t1(x TEXT);
  CREATE INDEX i1 ON t1(x);
  INSERT INTO t1 VALUES('1');
  INSERT INTO t1 VALUES('2');
  INSERT INTO t1 VALUES('3');
  INSERT INTO t1 VALUES('4');
  ANALYZE;
}
do_execsql_test 8.2 {
  SELECT * FROM t1 WHERE x = 3;
} {3}

#-------------------------------------------------------------------------
#
reset_db
do_execsql_test 9.1 {
  CREATE TABLE t1(a, b, c, d, e);
  CREATE INDEX i1 ON t1(a, b, c, d);
  CREATE INDEX i2 ON t1(e);
}
do_test 9.2 {
  execsql BEGIN;
  for {set i 0} {$i < 100} {incr i} {
    execsql "INSERT INTO t1 VALUES('x', 'y', 'z', $i, [expr $i/2])"
  }
  for {set i 0} {$i < 20} {incr i} {
    execsql "INSERT INTO t1 VALUES('x', 'y', 'z', 101, $i)"
  }
  for {set i 102} {$i < 200} {incr i} {
    execsql "INSERT INTO t1 VALUES('x', 'y', 'z', $i, [expr $i/2])"
  }
  execsql COMMIT
  execsql ANALYZE
} {}

do_eqp_test 9.3.1 {
  SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=101 AND e=5;
} {/t1 USING INDEX i1/}
do_eqp_test 9.3.2 {
  SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=99 AND e=5;
} {/t1 USING INDEX i1/}

set value_d [expr 101]
do_eqp_test 9.4.1 {
  SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=$value_d AND e=5
} {/t1 USING INDEX i1/}
set value_d [expr 99]
do_eqp_test 9.4.2 {
  SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=$value_d AND e=5
} {/t1 USING INDEX i1/}

#-------------------------------------------------------------------------
# Check that the planner takes stat3 data into account when considering
# "IS NULL" and "IS NOT NULL" constraints.
#
do_execsql_test 10.1.1 {
  DROP TABLE IF EXISTS t3;
  CREATE TABLE t3(a, b);
  CREATE INDEX t3a ON t3(a);
  CREATE INDEX t3b ON t3(b);
}
do_test 10.1.2 {
  for {set i 1} {$i < 100} {incr i} {
    if {$i>90} { set a $i } else { set a NULL }
    set b [expr $i % 5]
    execsql "INSERT INTO t3 VALUES($a, $b)"
  }
  execsql ANALYZE
} {}
do_eqp_test 10.1.3 {
  SELECT * FROM t3 WHERE a IS NULL AND b = 2
} {/t3 USING INDEX t3b/}
do_eqp_test 10.1.4 {
  SELECT * FROM t3 WHERE a IS NOT NULL AND b = 2
} {/t3 USING INDEX t3a/}

#-------------------------------------------------------------------------
# Check that stat3 data is used correctly with non-default collation
# sequences.
#
foreach {tn schema} {
  1 {
    CREATE TABLE t4(a COLLATE nocase, b);
    CREATE INDEX t4a ON t4(a);
    CREATE INDEX t4b ON t4(b);
  }
  2 {
    CREATE TABLE t4(a, b);
    CREATE INDEX t4a ON t4(a COLLATE nocase);
    CREATE INDEX t4b ON t4(b);
  }
} {
  drop_all_tables
  do_test 11.$tn.1 { execsql $schema } {}

  do_test 11.$tn.2 {
    for {set i 0} {$i < 100} {incr i} {
      if { ($i % 10)==0 } { set a ABC } else { set a DEF }
      set b [expr $i % 5]
        execsql { INSERT INTO t4 VALUES($a, $b) }
    }
    execsql ANALYZE
  } {}

  do_eqp_test 11.$tn.3 {
    SELECT * FROM t4 WHERE a = 'def' AND b = 3;
  } {/t4 USING INDEX t4b/}

  if {$tn==1} {
    set sql "SELECT * FROM t4 WHERE a = 'abc' AND b = 3;"
    do_eqp_test 11.$tn.4 $sql {/t4 USING INDEX t4a/}
  } else {

    set sql "SELECT * FROM t4 WHERE a = 'abc' COLLATE nocase AND b = 3;"
    do_eqp_test 11.$tn.5 $sql {/t4 USING INDEX t4a/}

    set sql "SELECT * FROM t4 WHERE a COLLATE nocase = 'abc' AND b = 3;"
    do_eqp_test 11.$tn.6 $sql {/t4 USING INDEX t4a/}
  }
}

#-------------------------------------------------------------------------
# Test that nothing untoward happens if the stat3 table contains entries
# for indexes that do not exist. Or NULL values in the idx column.
# Or NULL values in any of the other columns.
#
drop_all_tables
do_execsql_test 15.1 {
  CREATE TABLE x1(a, b, UNIQUE(a, b));
  INSERT INTO x1 VALUES(1, 2);
  INSERT INTO x1 VALUES(3, 4);
  INSERT INTO x1 VALUES(5, 6);
  ANALYZE;
  INSERT INTO sqlite_stat3 VALUES(NULL, NULL, NULL, NULL, NULL, NULL);
}
db close
sqlite3 db test.db
do_execsql_test 15.2 { SELECT * FROM x1 } {1 2 3 4 5 6}

do_execsql_test 15.3 {
  INSERT INTO sqlite_stat3 VALUES(42, 42, 42, 42, 42, 42);
}
db close
sqlite3 db test.db
do_execsql_test 15.4 { SELECT * FROM x1 } {1 2 3 4 5 6}

do_execsql_test 15.5 {
  UPDATE sqlite_stat1 SET stat = NULL;
}
db close
sqlite3 db test.db
do_execsql_test 15.6 { SELECT * FROM x1 } {1 2 3 4 5 6}

do_execsql_test 15.7 {
  ANALYZE;
  UPDATE sqlite_stat1 SET tbl = 'no such tbl';
}
db close
sqlite3 db test.db
do_execsql_test 15.8 { SELECT * FROM x1 } {1 2 3 4 5 6}

do_execsql_test 15.9 {
  ANALYZE;
  UPDATE sqlite_stat3 SET neq = NULL, nlt=NULL, ndlt=NULL;
}
db close
sqlite3 db test.db
do_execsql_test 15.10 { SELECT * FROM x1 } {1 2 3 4 5 6}

# This is just for coverage....
do_execsql_test 15.11 {
  ANALYZE;
  UPDATE sqlite_stat1 SET stat = stat || ' unordered';
}
db close
sqlite3 db test.db
do_execsql_test 15.12 { SELECT * FROM x1 } {1 2 3 4 5 6}

#-------------------------------------------------------------------------
# Test that allocations used for sqlite_stat3 samples are included in
# the quantity returned by SQLITE_DBSTATUS_SCHEMA_USED.
#
set one [string repeat x 1000]
set two [string repeat x 2000]
do_test 16.1 {
  reset_db
  execsql {
    CREATE TABLE t1(a, UNIQUE(a));
    INSERT INTO t1 VALUES($one);
    ANALYZE;
  }
  set nByte [lindex [sqlite3_db_status db SCHEMA_USED 0] 1]

  reset_db
  execsql {
    CREATE TABLE t1(a, UNIQUE(a));
    INSERT INTO t1 VALUES($two);
    ANALYZE;
  }
  set nByte2 [lindex [sqlite3_db_status db SCHEMA_USED 0] 1]

  expr {$nByte2 > $nByte+950 && $nByte2 < $nByte+1050}
} {1}

#-------------------------------------------------------------------------
# Test that stat3 data may be used with partial indexes.
#
do_test 17.1 {
  reset_db
  execsql {
    CREATE TABLE t1(a, b, c, d);
    CREATE INDEX i1 ON t1(a, b) WHERE d IS NOT NULL;
    INSERT INTO t1 VALUES(-1, -1, -1, NULL);
    INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1;
    INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1;
    INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1;
    INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1;
    INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1;
    INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1;
  }

  for {set i 0} {$i < 32} {incr i} {
    execsql { INSERT INTO t1 VALUES($i%2, $b, $i/2, 'abc') }
  }
  execsql {ANALYZE main.t1}
} {}

do_catchsql_test 17.1.2 {
  ANALYZE temp.t1;
} {1 {no such table: temp.t1}}

do_eqp_test 17.2 {
  SELECT * FROM t1 WHERE d IS NOT NULL AND a=0;
} {/USING INDEX i1/}
do_eqp_test 17.3 {
  SELECT * FROM t1 WHERE d IS NOT NULL AND a=0;
} {/USING INDEX i1/}

do_execsql_test 17.4 {
  CREATE INDEX i2 ON t1(c) WHERE d IS NOT NULL;
  ANALYZE main.i2;
}
do_eqp_test 17.5 {
  SELECT * FROM t1 WHERE d IS NOT NULL AND a=0;
} {/USING INDEX i1/}
do_eqp_test 17.6 {
  SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=0 AND c=10;
} {/USING INDEX i2/}

#-------------------------------------------------------------------------
#
do_test 18.1 {
  reset_db
  execsql {
    CREATE TABLE t1(a, b);
    CREATE INDEX i1 ON t1(a, b);
  }
  for {set i 0} {$i < 9} {incr i} {
    execsql {
      INSERT INTO t1 VALUES($i, 0);
      INSERT INTO t1 VALUES($i, 0);
      INSERT INTO t1 VALUES($i, 0);
      INSERT INTO t1 VALUES($i, 0);
      INSERT INTO t1 VALUES($i, 0);
      INSERT INTO t1 VALUES($i, 0);
      INSERT INTO t1 VALUES($i, 0);
      INSERT INTO t1 VALUES($i, 0);
      INSERT INTO t1 VALUES($i, 0);
      INSERT INTO t1 VALUES($i, 0);
      INSERT INTO t1 VALUES($i, 0);
      INSERT INTO t1 VALUES($i, 0);
      INSERT INTO t1 VALUES($i, 0);
      INSERT INTO t1 VALUES($i, 0);
      INSERT INTO t1 VALUES($i, 0);
    }
  }
  execsql ANALYZE
  execsql { SELECT count(*) FROM sqlite_stat3 }
} {9}

#-------------------------------------------------------------------------
# For coverage.
#
ifcapable view {
  do_test 19.1 {
    reset_db 
    execsql {
      CREATE TABLE t1(x, y);
      CREATE INDEX i1 ON t1(x, y);
      CREATE VIEW v1 AS SELECT * FROM t1;
      ANALYZE;
    }
  } {}
}
ifcapable auth {
  proc authproc {op args} {
    if {$op == "SQLITE_ANALYZE"} { return "SQLITE_DENY" }
    return "SQLITE_OK"
  }
  do_test 19.2 {
    reset_db 
    db auth authproc
    execsql {
      CREATE TABLE t1(x, y);
      CREATE VIEW v1 AS SELECT * FROM t1;
    }
    catchsql ANALYZE
  } {1 {not authorized}}
}

#-------------------------------------------------------------------------
#
reset_db
proc r {args} { expr rand() }
db func r r
db func lrange lrange
do_test 20.1 {
  execsql {
    CREATE TABLE t1(a,b,c,d);
    CREATE INDEX i1 ON t1(a,b,c,d);
  }
  for {set i 0} {$i < 16} {incr i} {
    execsql {
      INSERT INTO t1 VALUES($i, r(), r(), r());
      INSERT INTO t1 VALUES($i, $i,  r(), r());
      INSERT INTO t1 VALUES($i, $i,  $i,  r());
      INSERT INTO t1 VALUES($i, $i,  $i,  $i);
      INSERT INTO t1 VALUES($i, $i,  $i,  $i);
      INSERT INTO t1 VALUES($i, $i,  $i,  r());
      INSERT INTO t1 VALUES($i, $i,  r(), r());
      INSERT INTO t1 VALUES($i, r(), r(), r());
    }
  }
} {}
do_execsql_test 20.2 { ANALYZE }
for {set i 0} {$i<16} {incr i} {
    set val $i
    do_execsql_test 20.3.$i {
      SELECT count(*) FROM sqlite_stat3 WHERE sample=$val
    } {1}
}

finish_test

Added test/fkey7.test.












































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
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
# 2001 September 15
#
# 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.
#
# This file implements tests for foreign keys.
#

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

ifcapable {!foreignkey} {
  finish_test
  return
}

do_execsql_test 1.1 {
  PRAGMA foreign_keys = 1;

  CREATE TABLE s1(a PRIMARY KEY, b);
  CREATE TABLE par(a, b REFERENCES s1, c UNIQUE, PRIMARY KEY(a));

  CREATE TABLE c1(a, b REFERENCES par);
  CREATE TABLE c2(a, b REFERENCES par);
  CREATE TABLE c3(a, b REFERENCES par(c));
}

proc auth {op tbl args} {
  if {$op == "SQLITE_READ"} { set ::tbls($tbl) 1 }
  return "SQLITE_OK"
}
db auth auth
db cache size 0
proc do_tblsread_test {tn sql tbllist} {
  array unset ::tbls
  uplevel [list execsql $sql]
  uplevel [list do_test $tn {lsort [array names ::tbls]} $tbllist]
}

do_tblsread_test 1.2 { UPDATE par SET b=? WHERE a=? } {par s1}
do_tblsread_test 1.3 { UPDATE par SET a=? WHERE b=? } {c1 c2 par}
do_tblsread_test 1.4 { UPDATE par SET c=? WHERE b=? } {c3 par}
do_tblsread_test 1.5 { UPDATE par SET a=?,b=?,c=? WHERE b=? } {c1 c2 c3 par s1}


finish_test
Changes to test/mallocA.test.
92
93
94
95
96
97
98


















99
100
101
102
103
104
105
} -test {
  faultsim_test_result [list 0 2]
}
do_faultsim_test 6.2 -faults oom* -body {
  execsql { SELECT rowid FROM t1 WHERE a='abc' AND b<'y' }
} -test {
  faultsim_test_result [list 0 {1 2}]


















}

# Ensure that no file descriptors were leaked.
do_test malloc-99.X {
  catch {db close}
  set sqlite_open_file_count
} {0}







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







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
} -test {
  faultsim_test_result [list 0 2]
}
do_faultsim_test 6.2 -faults oom* -body {
  execsql { SELECT rowid FROM t1 WHERE a='abc' AND b<'y' }
} -test {
  faultsim_test_result [list 0 {1 2}]
}
ifcapable stat3 {
  do_test 6.3-prep {
    execsql {
      PRAGMA writable_schema = 1;
      CREATE TABLE sqlite_stat4 AS 
      SELECT tbl, idx, neq, nlt, ndlt, sqlite_record(sample) AS sample 
      FROM sqlite_stat3;
    }
  } {}
  do_faultsim_test 6.3 -faults oom* -body {
    execsql { 
      ANALYZE sqlite_master;
      SELECT rowid FROM t1 WHERE a='abc' AND b<'y';
    }
  } -test {
    faultsim_test_result [list 0 {1 2}]
  }
}

# Ensure that no file descriptors were leaked.
do_test malloc-99.X {
  catch {db close}
  set sqlite_open_file_count
} {0}
Changes to test/permutations.test.
307
308
309
310
311
312
313








314
315
316
317
318
319
320
test_suite "coverage-pager" -description {
  Coverage tests for file pager.c.
} -files {
  pager1.test    pager2.test  pagerfault.test  pagerfault2.test
  walfault.test  walbak.test  journal2.test    tkt-9d68c883.test
} 










lappend ::testsuitelist xxx
#-------------------------------------------------------------------------
# Define the permutation test suites:
#

# Run some tests using pre-allocated page and scratch blocks.







>
>
>
>
>
>
>
>







307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
test_suite "coverage-pager" -description {
  Coverage tests for file pager.c.
} -files {
  pager1.test    pager2.test  pagerfault.test  pagerfault2.test
  walfault.test  walbak.test  journal2.test    tkt-9d68c883.test
} 

test_suite "coverage-analyze" -description {
  Coverage tests for file analyze.c.
} -files {
  analyze3.test analyze4.test analyze5.test analyze6.test
  analyze7.test analyze8.test analyze9.test analyzeA.test
  analyze.test analyzeB.test mallocA.test
} 


lappend ::testsuitelist xxx
#-------------------------------------------------------------------------
# Define the permutation test suites:
#

# Run some tests using pre-allocated page and scratch blocks.
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
test_suite "utf16" -description {
  Run tests using UTF-16 databases
} -presql {
  pragma encoding = 'UTF-16'
} -files {
    alter.test alter3.test
    analyze.test analyze3.test analyze4.test analyze5.test analyze6.test
    analyze7.test analyze8.test analyze9.test analyzeA.test
    auth.test bind.test blob.test capi2.test capi3.test collate1.test
    collate2.test collate3.test collate4.test collate5.test collate6.test
    conflict.test date.test delete.test expr.test fkey1.test func.test
    hook.test index.test insert2.test insert.test interrupt.test in.test
    intpkey.test ioerr.test join2.test join.test lastinsert.test
    laststmtchanges.test limit.test lock2.test lock.test main.test 
    memdb.test minmax.test misc1.test misc2.test misc3.test notnull.test







|







505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
test_suite "utf16" -description {
  Run tests using UTF-16 databases
} -presql {
  pragma encoding = 'UTF-16'
} -files {
    alter.test alter3.test
    analyze.test analyze3.test analyze4.test analyze5.test analyze6.test
    analyze7.test analyze8.test analyze9.test analyzeA.test analyzeB.test
    auth.test bind.test blob.test capi2.test capi3.test collate1.test
    collate2.test collate3.test collate4.test collate5.test collate6.test
    conflict.test date.test delete.test expr.test fkey1.test func.test
    hook.test index.test insert2.test insert.test interrupt.test in.test
    intpkey.test ioerr.test join2.test join.test lastinsert.test
    laststmtchanges.test limit.test lock2.test lock.test main.test 
    memdb.test minmax.test misc1.test misc2.test misc3.test notnull.test
Changes to test/shell1.test.
718
719
720
721
722
723
724



725

726
727
728
729
730
731
732
  catchcmd {test.db} {.print "this\nis\ta\\test" 'this\nis\ta\\test'}
} [list 0 "this\nis\ta\\test this\\nis\\ta\\\\test"]


# Test the output of the ".dump" command
#
do_test shell1-4.1 {



  db eval {

    CREATE TABLE t1(x);
    INSERT INTO t1 VALUES(null), (''), (1), (2.25), ('hello'), (x'807f');
  }
  catchcmd test.db {.dump}
} {0 {PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE t1(x);







>
>
>

>







718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
  catchcmd {test.db} {.print "this\nis\ta\\test" 'this\nis\ta\\test'}
} [list 0 "this\nis\ta\\test this\\nis\\ta\\\\test"]


# Test the output of the ".dump" command
#
do_test shell1-4.1 {
  db close
  forcedelete test.db
  sqlite3 db test.db
  db eval {
    PRAGMA encoding=UTF16;
    CREATE TABLE t1(x);
    INSERT INTO t1 VALUES(null), (''), (1), (2.25), ('hello'), (x'807f');
  }
  catchcmd test.db {.dump}
} {0 {PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE t1(x);
748
749
750
751
752
753
754








755
756
757
758
759
760
761
INSERT INTO t1 VALUES(2.25);
INSERT INTO t1 VALUES('hello');
INSERT INTO t1 VALUES(X'807f');}}

# Test the output of ".mode tcl"
#
do_test shell1-4.3 {








  catchcmd test.db ".mode tcl\nselect * from t1;"
} {0 {""
""
"1"
"2.25"
"hello"
"\200\177"}}







>
>
>
>
>
>
>
>







752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
INSERT INTO t1 VALUES(2.25);
INSERT INTO t1 VALUES('hello');
INSERT INTO t1 VALUES(X'807f');}}

# Test the output of ".mode tcl"
#
do_test shell1-4.3 {
  db close
  forcedelete test.db
  sqlite3 db test.db
  db eval {
    PRAGMA encoding=UTF8;
    CREATE TABLE t1(x);
    INSERT INTO t1 VALUES(null), (''), (1), (2.25), ('hello'), (x'807f');
  }
  catchcmd test.db ".mode tcl\nselect * from t1;"
} {0 {""
""
"1"
"2.25"
"hello"
"\200\177"}}
Changes to test/where.test.
1299
1300
1301
1302
1303
1304
1305





























1306
1307
do_test where-17.5 {
  execsql {
    CREATE TABLE tother(a, b);
    INSERT INTO tother VALUES(1, 3.7);
    SELECT id, a FROM tbooking, tother WHERE id>a;
  }
} {42 1 43 1}






























finish_test







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
do_test where-17.5 {
  execsql {
    CREATE TABLE tother(a, b);
    INSERT INTO tother VALUES(1, 3.7);
    SELECT id, a FROM tbooking, tother WHERE id>a;
  }
} {42 1 43 1}

# Ticket [be84e357c035d068135f20bcfe82761bbf95006b]  2013-09-03
# Segfault during query involving LEFT JOIN column in the ORDER BY clause.
#
do_execsql_test where-18.1 {
  CREATE TABLE t181(a);
  CREATE TABLE t182(b,c);
  INSERT INTO t181 VALUES(1);
  SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c IS NULL;
} {1}
do_execsql_test where-18.2 {
  SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +c;
} {1}
do_execsql_test where-18.3 {
  SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c;
} {1}
do_execsql_test where-18.4 {
  INSERT INTO t181 VALUES(1),(1),(1),(1);
  SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +c;
} {1}
do_execsql_test where-18.5 {
  INSERT INTO t181 VALUES(2);
  SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c IS NULL, +a;
} {1 2}
do_execsql_test where-18.6 {
  INSERT INTO t181 VALUES(2);
  SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +a, +c IS NULL;
} {1 2}


finish_test