/ Check-in [7e914aa9]
Login

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

Overview
Comment:Fix a compiler warning when STAT2 is off. More test cases. Fix legacy tests to deal with the new STAT2 logic.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | query-planner-tweaks
Files: files | file ages | folders
SHA1: 7e914aa999d9f3f7be58a4494d33a7876af56603
User & Date: drh 2011-08-06 01:22:50
Context
2011-08-06
02:03
Merge together the fork in the query-planner-tweaks branch. check-in: 2daab6bd user: drh tags: query-planner-tweaks
01:22
Fix a compiler warning when STAT2 is off. More test cases. Fix legacy tests to deal with the new STAT2 logic. check-in: 7e914aa9 user: drh tags: query-planner-tweaks
2011-08-05
22:31
Bug fixes to the sample-count logic for STAT2. A few test cases added. check-in: e93c248c user: drh tags: query-planner-tweaks
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/analyze.c.

   129    129     int regNext = iMem++;        /* Index of next sample to record */
   130    130     int regSampleIdx = iMem++;   /* Index of next sample */
   131    131     int regReady = iMem++;       /* True if ready to store a stat2 entry */
   132    132     int regGosub = iMem++;       /* Register holding subroutine return addr */
   133    133     int regSample2 = iMem++;     /* Number of samples to acquire times 2 */
   134    134     int regCount = iMem++;       /* Number of rows in the table */
   135    135     int regCount2 = iMem++;      /* regCount*2 */
          136  +  int once = 1;                /* One-time initialization */
   136    137   #endif
   137    138     int regCol = iMem++;         /* Content of a column in analyzed table */
   138    139     int regRec = iMem++;         /* Register holding completed record */
   139    140     int regTemp = iMem++;        /* Temporary use register */
   140    141     int regRowid = iMem++;       /* Rowid for the inserted record */
   141         -  int once = 1;                /* One-time initialization */
   142    142   
   143    143   
   144    144     v = sqlite3GetVdbe(pParse);
   145    145     if( v==0 || NEVER(pTab==0) ){
   146    146       return;
   147    147     }
   148    148     if( pTab->tnum==0 ){

Changes to test/analyze2.test.

    69     69     for {set i 0} {$i < 1000} {incr i} {
    70     70       execsql { INSERT INTO t1 VALUES($i) }
    71     71     }
    72     72     execsql { 
    73     73       ANALYZE;
    74     74       SELECT tbl, idx, sampleno, sample FROM sqlite_stat2;
    75     75     }
    76         -} [list t1 sqlite_autoindex_t1_1 0 49  \
    77         -        t1 sqlite_autoindex_t1_1 1 149 \
    78         -        t1 sqlite_autoindex_t1_1 2 249 \
    79         -        t1 sqlite_autoindex_t1_1 3 349 \
    80         -        t1 sqlite_autoindex_t1_1 4 449 \
    81         -        t1 sqlite_autoindex_t1_1 5 549 \
    82         -        t1 sqlite_autoindex_t1_1 6 649 \
    83         -        t1 sqlite_autoindex_t1_1 7 749 \
    84         -        t1 sqlite_autoindex_t1_1 8 849 \
    85         -        t1 sqlite_autoindex_t1_1 9 949 \
           76  +} [list t1 sqlite_autoindex_t1_1 0 50  \
           77  +        t1 sqlite_autoindex_t1_1 1 150 \
           78  +        t1 sqlite_autoindex_t1_1 2 250 \
           79  +        t1 sqlite_autoindex_t1_1 3 350 \
           80  +        t1 sqlite_autoindex_t1_1 4 450 \
           81  +        t1 sqlite_autoindex_t1_1 5 550 \
           82  +        t1 sqlite_autoindex_t1_1 6 650 \
           83  +        t1 sqlite_autoindex_t1_1 7 750 \
           84  +        t1 sqlite_autoindex_t1_1 8 850 \
           85  +        t1 sqlite_autoindex_t1_1 9 950 \
    86     86   ]
    87     87   
    88     88   do_test analyze2-1.2 {
    89     89     execsql {
    90         -    DELETE FROM t1 WHERE x>9;
           90  +    DELETE FROM t1 WHERE x>20;
    91     91       ANALYZE;
    92     92       SELECT tbl, idx, group_concat(sample, ' ') FROM sqlite_stat2;
    93     93     }
    94         -} {t1 sqlite_autoindex_t1_1 {0 1 2 3 4 5 6 7 8 9}}
           94  +} {t1 sqlite_autoindex_t1_1 {1 3 5 7 9 11 13 15 17 19}}
    95     95   do_test analyze2-1.3 {
    96     96     execsql {
    97     97       DELETE FROM t1 WHERE x>8;
    98     98       ANALYZE;
    99     99       SELECT * FROM sqlite_stat2;
   100    100     }
   101    101   } {}
................................................................................
   185    185     execsql ANALYZE
   186    186     execsql { 
   187    187       SELECT tbl,idx,group_concat(sample,' ') 
   188    188       FROM sqlite_stat2 
   189    189       WHERE idx = 't1_x' 
   190    190       GROUP BY tbl,idx
   191    191     }
   192         -} {t1 t1_x {99 299 499 699 899 ajj cjj ejj gjj ijj}}
          192  +} {t1 t1_x {100 300 500 700 900 baa daa faa haa jaa}}
   193    193   do_test analyze2-3.2 {
   194    194     execsql { 
   195    195       SELECT tbl,idx,group_concat(sample,' ') 
   196    196       FROM sqlite_stat2 
   197    197       WHERE idx = 't1_y' 
   198    198       GROUP BY tbl,idx
   199    199     }
   200         -} {t1 t1_y {99 299 499 699 899 ajj cjj ejj gjj ijj}}
          200  +} {t1 t1_y {100 300 500 700 900 baa daa faa haa jaa}}
   201    201   
   202    202   do_eqp_test 3.3 {
   203    203     SELECT * FROM t1 WHERE x BETWEEN 100 AND 500 AND y BETWEEN 'a' AND 'b'
   204    204   } {
   205         -  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>? AND y<?) (~50 rows)}
          205  +  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>? AND y<?) (~25 rows)}
   206    206   }
   207    207   do_eqp_test 3.4 {
   208    208     SELECT * FROM t1 WHERE x BETWEEN 100 AND 400 AND y BETWEEN 'a' AND 'h'
   209    209   } {
   210    210     0 0 0 {SEARCH TABLE t1 USING INDEX t1_x (x>? AND x<?) (~100 rows)}
   211    211   }
   212    212   do_eqp_test 3.5 {
   213    213     SELECT * FROM t1 WHERE x<'a' AND y>'h'
   214    214   } {
   215         -  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>?) (~66 rows)}
          215  +  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>?) (~133 rows)}
   216    216   }
   217    217   do_eqp_test 3.6 {
   218    218     SELECT * FROM t1 WHERE x<444 AND y>'h'
   219    219   } {
   220         -  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>?) (~66 rows)}
          220  +  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>?) (~133 rows)}
   221    221   }
   222    222   do_eqp_test 3.7 {
   223    223     SELECT * FROM t1 WHERE x<221 AND y>'g'
   224    224   } {
   225    225     0 0 0 {SEARCH TABLE t1 USING INDEX t1_x (x<?) (~66 rows)}
   226    226   }
   227    227   
................................................................................
   245    245       PRAGMA automatic_index=OFF;
   246    246       SELECT tbl,idx,group_concat(sample,' ') 
   247    247       FROM sqlite_stat2 
   248    248       WHERE idx = 't3a' 
   249    249       GROUP BY tbl,idx;
   250    250       PRAGMA automatic_index=ON;
   251    251     }
   252         -} {t3 t3a {AfA bEj CEj dEj EEj fEj GEj hEj IEj jEj}}
          252  +} {t3 t3a {AfA bfA CfA dfA EfA ffA GfA hfA IfA jfA}}
   253    253   do_test analyze2-4.3 {
   254    254     execsql { 
   255    255       SELECT tbl,idx,group_concat(sample,' ') 
   256    256       FROM sqlite_stat2 
   257    257       WHERE idx = 't3b' 
   258    258       GROUP BY tbl,idx
   259    259     }
   260         -} {t3 t3b {AbA CIj EIj GIj IIj bIj dIj fIj hIj jIj}}
          260  +} {t3 t3b {AbA CbA EbA GbA IbA bbA dbA fbA hbA jbA}}
   261    261   
   262    262   do_eqp_test 4.4 {
   263    263     SELECT * FROM t3 WHERE a > 'A' AND a < 'C' AND b > 'A' AND b < 'C'
   264    264   } {
   265    265     0 0 0 {SEARCH TABLE t3 USING INDEX t3b (b>? AND b<?) (~11 rows)}
   266    266   }
   267    267   do_eqp_test 4.5 {
................................................................................
   293    293     do_test analyze2-5.2 {
   294    294       execsql { 
   295    295         SELECT tbl,idx,group_concat(sample,' ') 
   296    296         FROM sqlite_stat2 
   297    297         WHERE tbl = 't4' 
   298    298         GROUP BY tbl,idx
   299    299       }
   300         -  } {t4 t4x {afa bej cej dej eej fej gej hej iej jej}}
          300  +  } {t4 t4x {afa bfa cfa dfa efa ffa gfa hfa ifa jfa}}
   301    301     do_eqp_test 5.3 {
   302    302       SELECT * FROM t4 WHERE x>'ccc'
   303    303     } {0 0 0 {SEARCH TABLE t4 USING COVERING INDEX t4x (x>?) (~800 rows)}}
   304    304     do_eqp_test 5.4 {
   305    305       SELECT * FROM t4 AS t41, t4 AS t42 WHERE t41.x>'ccc' AND t42.x>'ggg'
   306    306     } {
   307    307       0 0 1 {SEARCH TABLE t4 AS t42 USING COVERING INDEX t4x (x>?) (~300 rows)} 

Changes to test/analyze8.test.

    84     84       execsql {INSERT INTO t1 VALUES(0,999)}
    85     85     }
    86     86     execsql {
    87     87       ANALYZE;
    88     88       SELECT sample, cnt FROM sqlite_stat2 WHERE idx='t1x' ORDER BY sampleno;
    89     89     }
    90     90   } {0 200 0 200 0 200 10 1 70 1 130 1 190 1 999 200 999 200 999 200}
           91  +
           92  +do_test analyze8-3.0 {
           93  +  execsql {
           94  +    BEGIN;
           95  +    DROP TABLE t1;
           96  +    CREATE TABLE t1(a,b);
           97  +    CREATE INDEX t1all ON t1(a,b);
           98  +    INSERT INTO t1 VALUES(0,1);
           99  +    INSERT INTO t1 VALUES(0,2);
          100  +    INSERT INTO t1 VALUES(0,3);
          101  +    INSERT INTO t1 VALUES(1,4);
          102  +    INSERT INTO t1 SELECT a+2, b+4 FROM t1;
          103  +    INSERT INTO t1 SELECT a+4, b+8 FROM t1;
          104  +    INSERT INTO t1 SELECT a+8, b+16 FROM t1;
          105  +    COMMIT;
          106  +    ANALYZE;
          107  +    SELECT sample, cnt FROM sqlite_stat2 WHERE idx='t1all' ORDER BY sampleno;
          108  +  }
          109  +} {0 3 2 3 4 3 5 1 6 3 8 3 10 3 12 3 13 1 14 3}
          110  +do_test analyze8-3.1 {
          111  +  execsql {
          112  +    DELETE FROM t1;
          113  +    INSERT INTO t1 VALUES(1,1);
          114  +    INSERT INTO t1 VALUES(2,2);
          115  +    INSERT INTO t1 SELECT a+2, b+2 FROM t1;
          116  +    INSERT INTO t1 SELECT a+4, b+4 FROM t1;
          117  +    INSERT INTO t1 SELECT a+8, b+8 FROM t1;
          118  +    INSERT INTO t1 SELECT a+16, b+16 FROM t1;
          119  +    DELETE FROM t1 WHERE a>21;
          120  +    ANALYZE;
          121  +    SELECT sample, cnt FROM sqlite_stat2 WHERE idx='t1all' ORDER BY sampleno;
          122  +  }
          123  +} {2 1 4 1 6 1 8 1 10 1 12 1 14 1 16 1 18 1 20 1}
          124  +do_test analyze8-3.2 {
          125  +  execsql {
          126  +    UPDATE t1 SET a=123;
          127  +    ANALYZE;
          128  +    SELECT sample, cnt FROM sqlite_stat2 WHERE idx='t1all' ORDER BY sampleno;
          129  +  }
          130  +} {123 21 123 21 123 21 123 21 123 21 123 21 123 21 123 21 123 21 123 21}
          131  +do_test analyze8-3.3 {
          132  +  execsql {
          133  +    DELETE FROM t1 WHERE b=1 OR b=2;
          134  +    ANALYZE;
          135  +    SELECT count(*) FROM t1;
          136  +    SELECT sample, cnt FROM sqlite_stat2 WHERE idx='t1all' ORDER BY sampleno;
          137  +  }
          138  +} {19}
          139  +do_test analyze8-3.4 {
          140  +  execsql {
          141  +    UPDATE t1 SET a=b;
          142  +    INSERT INTO t1 VALUES(1,1);
          143  +    INSERT INTO t1 VALUES(2,2);
          144  +    INSERT INTO t1 SELECT a, b FROM t1;
          145  +    ANALYZE;
          146  +    SELECT sample, cnt FROM sqlite_stat2 WHERE idx='t1all' ORDER BY sampleno;
          147  +  }
          148  +} {2 2 4 2 6 2 8 2 10 2 12 2 14 2 16 2 18 2 20 2}
          149  +do_test analyze8-3.5 {
          150  +  execsql {
          151  +    UPDATE t1 SET a=1 WHERE b<20;
          152  +    ANALYZE;
          153  +    SELECT sample, cnt FROM sqlite_stat2 WHERE idx='t1all' ORDER BY sampleno;
          154  +  }
          155  +} {1 38 1 38 1 38 1 38 1 38 1 38 1 38 1 38 1 38 20 2}
          156  +do_test analyze8-3.6 {
          157  +  execsql {
          158  +    UPDATE t1 SET a=b;
          159  +    UPDATE t1 SET a=20 WHERE b>2;
          160  +    ANALYZE;
          161  +    SELECT sample, cnt FROM sqlite_stat2 WHERE idx='t1all' ORDER BY sampleno;
          162  +  }
          163  +} {2 2 20 38 20 38 20 38 20 38 20 38 20 38 20 38 20 38 20 38}

Changes to test/tkt-cbd054fa6b.test.

    31     31       INSERT INTO t1 VALUES (NULL, 'C');
    32     32       INSERT INTO t1 VALUES (NULL, 'D');
    33     33       INSERT INTO t1 VALUES (NULL, 'E');
    34     34       INSERT INTO t1 VALUES (NULL, 'F');
    35     35       INSERT INTO t1 VALUES (NULL, 'G');
    36     36       INSERT INTO t1 VALUES (NULL, 'H');
    37     37       INSERT INTO t1 VALUES (NULL, 'I');
           38  +    INSERT INTO t1 VALUES (NULL, 'J');
           39  +    INSERT INTO t1 VALUES (NULL, 'K');
           40  +    INSERT INTO t1 VALUES (NULL, 'L');
           41  +    INSERT INTO t1 VALUES (NULL, 'M');
           42  +    INSERT INTO t1 VALUES (NULL, 'N');
           43  +    INSERT INTO t1 VALUES (NULL, 'O');
           44  +    INSERT INTO t1 VALUES (NULL, 'P');
           45  +    INSERT INTO t1 VALUES (NULL, 'Q');
           46  +    INSERT INTO t1 VALUES (NULL, 'R');
           47  +    INSERT INTO t1 VALUES (NULL, 'S');
    38     48       SELECT count(*) FROM t1;
    39     49     }
    40         -} {10}
           50  +} {20}
    41     51   do_test tkt-cbd05-1.2 {
    42     52     db eval {
    43     53       ANALYZE;
    44     54     }
    45     55   } {}
    46     56   do_test tkt-cbd05-1.3 {
    47     57     execsql { 
    48     58       SELECT tbl,idx,group_concat(sample,' ') 
    49     59       FROM sqlite_stat2 
    50     60       WHERE idx = 't1_x' 
    51     61       GROUP BY tbl,idx
    52     62     }
    53         -} {t1 t1_x { A B C D E F G H I}}
           63  +} {t1 t1_x {A C E G I K M O Q S}}
    54     64   
    55     65   do_test tkt-cbd05-2.1 {
    56     66     db eval {
    57     67       DROP TABLE t1;
    58     68       CREATE TABLE t1(a INTEGER PRIMARY KEY, b BLOB UNIQUE NOT NULL);
    59     69       CREATE INDEX t1_x ON t1(b);
    60     70       INSERT INTO t1 VALUES(NULL, X'');
................................................................................
    63     73       INSERT INTO t1 VALUES(NULL, X'43');
    64     74       INSERT INTO t1 VALUES(NULL, X'44');
    65     75       INSERT INTO t1 VALUES(NULL, X'45');
    66     76       INSERT INTO t1 VALUES(NULL, X'46');
    67     77       INSERT INTO t1 VALUES(NULL, X'47');
    68     78       INSERT INTO t1 VALUES(NULL, X'48');
    69     79       INSERT INTO t1 VALUES(NULL, X'49');
           80  +    INSERT INTO t1 VALUES(NULL, X'4A');
           81  +    INSERT INTO t1 VALUES(NULL, X'4B');
           82  +    INSERT INTO t1 VALUES(NULL, X'4C');
           83  +    INSERT INTO t1 VALUES(NULL, X'4D');
           84  +    INSERT INTO t1 VALUES(NULL, X'4E');
           85  +    INSERT INTO t1 VALUES(NULL, X'4F');
           86  +    INSERT INTO t1 VALUES(NULL, X'50');
           87  +    INSERT INTO t1 VALUES(NULL, X'51');
           88  +    INSERT INTO t1 VALUES(NULL, X'52');
           89  +    INSERT INTO t1 VALUES(NULL, X'53');
    70     90       SELECT count(*) FROM t1;
    71     91     }
    72         -} {10}
           92  +} {20}
    73     93   do_test tkt-cbd05-2.2 {
    74     94     db eval {
    75     95       ANALYZE;
    76     96     }
    77     97   } {}
    78     98   do_test tkt-cbd05-2.3 {
    79     99     execsql { 
    80    100       SELECT tbl,idx,group_concat(sample,' ') 
    81    101       FROM sqlite_stat2 
    82    102       WHERE idx = 't1_x' 
    83    103       GROUP BY tbl,idx
    84    104     }
    85         -} {t1 t1_x { A B C D E F G H I}}
          105  +} {t1 t1_x {A C E G I K M O Q S}}
    86    106   
    87    107   finish_test