Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Further stat4 related tests. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
0a702c4b4c35fdbcb62e3ab88b9e57d7 |
| User & Date: | dan 2013-09-02 18:58:11 |
Context
|
2013-09-02
| ||
| 20:22 | Simplify branch coverage testing by interchanging the order of two tests in the whereLoopInsert() function. check-in: f7079b53 user: drh tags: trunk | |
| 18:58 | Further stat4 related tests. check-in: 0a702c4b user: dan tags: trunk | |
| 11:52 | Add tests to check if ANALYZE is choosing common non-periodic samples for the stat4 table. check-in: 17584299 user: dan tags: trunk | |
Changes
Changes to src/analyze.c.
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
...
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
|
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;
................................................................................
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. */
|
|
>
>
>
>
>
>
>
>
>
>
>
>
>
<
<
|
|
|
<
|
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
...
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
|
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++;
#if 0
iSeq = pNew->anLt[p->nCol-1];
for(iPos=p->nSample; iPos>0; iPos--){
if( iSeq>p->a[iPos-1].anLt[p->nCol-1] ) break;
}
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;
}
#endif
/* 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;
................................................................................
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. */
|
Changes to test/analyze9.test.
700 701 702 703 704 705 706 707 708 709 |
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
finish_test
|
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
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 |
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}}
}
finish_test
|
Changes to test/permutations.test.
306 307 308 309 310 311 312 313 314 315 316 317 318 319 |
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:
#
|
> > > > > > > > |
306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 |
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 mallocA.test
}
lappend ::testsuitelist xxx
#-------------------------------------------------------------------------
# Define the permutation test suites:
#
|