/ Check-in [2504bcfb]
Login

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

Overview
Comment:Update some test cases to work with sqlite_stat3 instead of sqlite_stat2.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | stat3-enhancement
Files: files | file ages | folders
SHA1:2504bcfb0cf14b5ce51db0af1269ac28384714e0
User & Date: drh 2011-08-15 12:58:23
Context
2011-08-16
01:15
Add the analyze8.test test module for sqlite_stat3. check-in: 2c83ac89 user: drh tags: stat3-enhancement
2011-08-15
12:58
Update some test cases to work with sqlite_stat3 instead of sqlite_stat2. check-in: 2504bcfb user: drh tags: stat3-enhancement
12:02
Fix a couple of typos in comments in analyze.c. check-in: ae31dc67 user: dan tags: stat3-enhancement
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/alter.test.

   843    843   #-------------------------------------------------------------------------
   844    844   # Test that it is not possible to use ALTER TABLE on any system table.
   845    845   #
   846    846   set system_table_list {1 sqlite_master}
   847    847   catchsql ANALYZE
   848    848   ifcapable analyze { lappend system_table_list 2 sqlite_stat1 }
   849    849   ifcapable stat2   { lappend system_table_list 3 sqlite_stat2 }
          850  +ifcapable stat3   { lappend system_table_list 4 sqlite_stat3 }
   850    851   
   851    852   foreach {tn tbl} $system_table_list {
   852    853     do_test alter-15.$tn.1 {
   853    854       catchsql "ALTER TABLE $tbl RENAME TO xyz"
   854    855     } [list 1 "table $tbl may not be altered"]
   855    856   
   856    857     do_test alter-15.$tn.2 {
   857    858       catchsql "ALTER TABLE $tbl ADD COLUMN xyz"
   858    859     } [list 1 "table $tbl may not be altered"]
   859    860   }
   860    861   
   861    862   
   862    863   finish_test

Changes to test/analyze3.test.

    13     13   # implements tests for range and LIKE constraints that use bound variables
    14     14   # instead of literal constant arguments.
    15     15   #
    16     16   
    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
    19     19   
    20         -ifcapable !stat2 {
           20  +ifcapable !stat3 {
    21     21     finish_test
    22     22     return
    23     23   }
    24     24   
    25     25   #----------------------------------------------------------------------
    26     26   # Test Organization:
    27     27   #
................................................................................
    96     96   } {}
    97     97   
    98     98   do_eqp_test analyze3-1.1.2 {
    99     99     SELECT sum(y) FROM t1 WHERE x>200 AND x<300
   100    100   } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x<?) (~179 rows)}}
   101    101   do_eqp_test analyze3-1.1.3 {
   102    102     SELECT sum(y) FROM t1 WHERE x>0 AND x<1100 
   103         -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? and x<?) {~959 rows)}}
          103  +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x<?) (~959 rows)}}
   104    104   
   105    105   do_test analyze3-1.1.4 {
   106    106     sf_execsql { SELECT sum(y) FROM t1 WHERE x>200 AND x<300 }
   107    107   } {199 0 14850}
   108    108   do_test analyze3-1.1.5 {
   109    109     set l [string range "200" 0 end]
   110    110     set u [string range "300" 0 end]
................................................................................
   113    113   do_test analyze3-1.1.6 {
   114    114     set l [expr int(200)]
   115    115     set u [expr int(300)]
   116    116     sf_execsql { SELECT sum(y) FROM t1 WHERE x>$l AND x<$u }
   117    117   } {199 0 14850}
   118    118   do_test analyze3-1.1.7 {
   119    119     sf_execsql { SELECT sum(y) FROM t1 WHERE x>0 AND x<1100 }
   120         -} {999 999 499500}
          120  +} {2000 0 499500}
   121    121   do_test analyze3-1.1.8 {
   122    122     set l [string range "0" 0 end]
   123    123     set u [string range "1100" 0 end]
   124    124     sf_execsql { SELECT sum(y) FROM t1 WHERE x>$l AND x<$u }
   125         -} {999 999 499500}
          125  +} {2000 0 499500}
   126    126   do_test analyze3-1.1.9 {
   127    127     set l [expr int(0)]
   128    128     set u [expr int(1100)]
   129    129     sf_execsql { SELECT sum(y) FROM t1 WHERE x>$l AND x<$u }
   130         -} {999 999 499500}
          130  +} {2000 0 499500}
   131    131   
   132    132   
   133    133   # The following tests are similar to the block above. The difference is
   134    134   # that the indexed column has TEXT affinity in this case. In the tests
   135    135   # above the affinity is INTEGER.
   136    136   #
   137    137   do_test analyze3-1.2.1 {
................................................................................
   142    142         CREATE INDEX i2 ON t2(x);
   143    143       COMMIT;
   144    144       ANALYZE;
   145    145     }
   146    146   } {}
   147    147   do_eqp_test analyze3-1.2.2 {
   148    148     SELECT sum(y) FROM t2 WHERE x>1 AND x<2
   149         -} {0 0 0 {SEARCH TABLE t2 USING INDEX i2 (x>? AND x<?) (~200 rows)}}
          149  +} {0 0 0 {SEARCH TABLE t2 USING INDEX i2 (x>? AND x<?) (~196 rows)}}
   150    150   do_eqp_test analyze3-1.2.3 {
   151    151     SELECT sum(y) FROM t2 WHERE x>0 AND x<99
   152         -} {0 0 0 {SCAN TABLE t2 (~111 rows)}}
          152  +} {0 0 0 {SEARCH TABLE t2 USING INDEX i2 (x>? AND x<?) (~982 rows)}}
   153    153   do_test analyze3-1.2.4 {
   154    154     sf_execsql { SELECT sum(y) FROM t2 WHERE x>12 AND x<20 }
   155    155   } {161 0 4760}
   156    156   do_test analyze3-1.2.5 {
   157    157     set l [string range "12" 0 end]
   158    158     set u [string range "20" 0 end]
   159    159     sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u}
................................................................................
   161    161   do_test analyze3-1.2.6 {
   162    162     set l [expr int(12)]
   163    163     set u [expr int(20)]
   164    164     sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u}
   165    165   } {161 0 integer integer 4760}
   166    166   do_test analyze3-1.2.7 {
   167    167     sf_execsql { SELECT sum(y) FROM t2 WHERE x>0 AND x<99 }
   168         -} {999 999 490555}
          168  +} {1981 0 490555}
   169    169   do_test analyze3-1.2.8 {
   170    170     set l [string range "0" 0 end]
   171    171     set u [string range "99" 0 end]
   172    172     sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u}
   173         -} {999 999 text text 490555}
          173  +} {1981 0 text text 490555}
   174    174   do_test analyze3-1.2.9 {
   175    175     set l [expr int(0)]
   176    176     set u [expr int(99)]
   177    177     sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u}
   178         -} {999 999 integer integer 490555}
          178  +} {1981 0 integer integer 490555}
   179    179   
   180    180   # Same tests a third time. This time, column x has INTEGER affinity and
   181    181   # is not the leftmost column of the table. This triggered a bug causing
   182    182   # SQLite to use sub-optimal query plans in 3.6.18 and earlier.
   183    183   #
   184    184   do_test analyze3-1.3.1 {
   185    185     execsql {
................................................................................
   189    189         CREATE INDEX i3 ON t3(x);
   190    190       COMMIT;
   191    191       ANALYZE;
   192    192     }
   193    193   } {}
   194    194   do_eqp_test analyze3-1.3.2 {
   195    195     SELECT sum(y) FROM t3 WHERE x>200 AND x<300
   196         -} {0 0 0 {SEARCH TABLE t3 USING INDEX i3 (x>? AND x<?) (~166 rows)}}
          196  +} {0 0 0 {SEARCH TABLE t3 USING INDEX i3 (x>? AND x<?) (~156 rows)}}
   197    197   do_eqp_test analyze3-1.3.3 {
   198    198     SELECT sum(y) FROM t3 WHERE x>0 AND x<1100
   199         -} {0 0 0 {SCAN TABLE t3 (~111 rows)}}
          199  +} {0 0 0 {SEARCH TABLE t3 USING INDEX i3 (x>? AND x<?) (~989 rows)}}
   200    200   
   201    201   do_test analyze3-1.3.4 {
   202    202     sf_execsql { SELECT sum(y) FROM t3 WHERE x>200 AND x<300 }
   203    203   } {199 0 14850}
   204    204   do_test analyze3-1.3.5 {
   205    205     set l [string range "200" 0 end]
   206    206     set u [string range "300" 0 end]
................................................................................
   209    209   do_test analyze3-1.3.6 {
   210    210     set l [expr int(200)]
   211    211     set u [expr int(300)]
   212    212     sf_execsql { SELECT sum(y) FROM t3 WHERE x>$l AND x<$u }
   213    213   } {199 0 14850}
   214    214   do_test analyze3-1.3.7 {
   215    215     sf_execsql { SELECT sum(y) FROM t3 WHERE x>0 AND x<1100 }
   216         -} {999 999 499500}
          216  +} {2000 0 499500}
   217    217   do_test analyze3-1.3.8 {
   218    218     set l [string range "0" 0 end]
   219    219     set u [string range "1100" 0 end]
   220    220     sf_execsql { SELECT sum(y) FROM t3 WHERE x>$l AND x<$u }
   221         -} {999 999 499500}
          221  +} {2000 0 499500}
   222    222   do_test analyze3-1.3.9 {
   223    223     set l [expr int(0)]
   224    224     set u [expr int(1100)]
   225    225     sf_execsql { SELECT sum(y) FROM t3 WHERE x>$l AND x<$u }
   226         -} {999 999 499500}
          226  +} {2000 0 499500}
   227    227   
   228    228   #-------------------------------------------------------------------------
   229    229   # Test that the values of bound SQL variables may be used for the LIKE
   230    230   # optimization.
   231    231   #
   232    232   drop_all_tables
   233    233   do_test analyze3-2.1 {

Changes to test/analyze6.test.

    13     13   # in this file a corner-case query planner optimization involving the
    14     14   # join order of two tables of different sizes.
    15     15   #
    16     16   
    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
    19     19   
    20         -ifcapable !stat2 {
           20  +ifcapable !stat3 {
    21     21     finish_test
    22     22     return
    23     23   }
    24     24   
    25     25   set testprefix analyze6
    26     26   
    27     27   proc eqp {sql {db db}} {

Changes to test/tkt-cbd054fa6b.test.

    12     12   # This file implements tests to verify that ticket [cbd054fa6b] has been
    13     13   # fixed.  
    14     14   #
    15     15   
    16     16   set testdir [file dirname $argv0]
    17     17   source $testdir/tester.tcl
    18     18   
    19         -ifcapable !stat2 {
           19  +ifcapable !stat3 {
    20     20     finish_test
    21     21     return
    22     22   }
    23     23   
    24     24   do_test tkt-cbd05-1.1 {
    25     25     db eval {
    26     26       CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT UNIQUE NOT NULL);
................................................................................
    42     42     db eval {
    43     43       ANALYZE;
    44     44     }
    45     45   } {}
    46     46   do_test tkt-cbd05-1.3 {
    47     47     execsql { 
    48     48       SELECT tbl,idx,group_concat(sample,' ') 
    49         -    FROM sqlite_stat2 
           49  +    FROM sqlite_stat3 
    50     50       WHERE idx = 't1_x' 
    51     51       GROUP BY tbl,idx
    52     52     }
    53     53   } {t1 t1_x { A B C D E F G H I}}
    54     54   
    55     55   do_test tkt-cbd05-2.1 {
    56     56     db eval {
................................................................................
    74     74     db eval {
    75     75       ANALYZE;
    76     76     }
    77     77   } {}
    78     78   do_test tkt-cbd05-2.3 {
    79     79     execsql { 
    80     80       SELECT tbl,idx,group_concat(sample,' ') 
    81         -    FROM sqlite_stat2 
           81  +    FROM sqlite_stat3 
    82     82       WHERE idx = 't1_x' 
    83     83       GROUP BY tbl,idx
    84     84     }
    85     85   } {t1 t1_x { A B C D E F G H I}}
    86     86   
    87     87   finish_test