Imported from /home/lorry/working-area/delta_berkeleydb/db-6.1.23.tar.gz.HEADdb-6.1.23master

1 files changed, 116 insertions, 92 deletions

diff --git a/lang/sql/sqlite/test/analyze5.test b/lang/sql/sqlite/test/analyze5.test
index a0469da1..ac175c07 100644
--- a/lang/sql/sqlite/test/analyze5.test
+++ b/lang/sql/sqlite/test/analyze5.test
@@ -10,14 +10,14 @@
 #***********************************************************************
 #
 # This file implements tests for SQLite library.  The focus of the tests
-# in this file is the use of the sqlite_stat2 histogram data on tables
+# in this file is the use of the sqlite_stat4 histogram data on tables
 # with many repeated values and only a few distinct values.
 #
 
 set testdir [file dirname $argv0]
 source $testdir/tester.tcl
 
-ifcapable !stat2 {
+ifcapable !stat4&&!stat3 {
   finish_test
   return
 }
@@ -28,6 +28,17 @@ proc eqp {sql {db db}} {
   uplevel execsql [list "EXPLAIN QUERY PLAN $sql"] $db
 }
 
+proc alpha {blob} {
+  set ret ""
+  foreach c [split $blob {}] {
+    if {[string is alpha $c]} {append ret $c}
+  }
+  return $ret
+}
+db func alpha alpha
+
+db func lindex lindex
+
 unset -nocomplain i t u v w x y z
 do_test analyze5-1.0 {
   db eval {CREATE TABLE t1(t,u,v TEXT COLLATE nocase,w,x,y,z)}
@@ -55,126 +66,138 @@ do_test analyze5-1.0 {
     CREATE INDEX t1y ON t1(y);  -- integers 0 and very few 1s
     CREATE INDEX t1z ON t1(z);  -- integers 0, 1, 2, and 3
     ANALYZE;
-    SELECT sample FROM sqlite_stat2 WHERE idx='t1u' ORDER BY sampleno;
   }
-} {alpha alpha alpha alpha bravo bravo bravo charlie charlie delta}
-do_test analyze5-1.1 {
-  string tolower \
-   [db eval {SELECT sample from sqlite_stat2 WHERE idx='t1v' ORDER BY sampleno}]
-} {alpha alpha alpha alpha bravo bravo bravo charlie charlie delta}
-do_test analyze5-1.2 {
-  db eval {SELECT sample from sqlite_stat2 WHERE idx='t1w' ORDER BY sampleno}
-} {{} 0 0 0 0 1 1 1 2 2}
-do_test analyze5-1.3 {
-  db eval {SELECT sample from sqlite_stat2 WHERE idx='t1x' ORDER BY sampleno}
-} {{} {} {} {} 1 1 1 2 2 3}
-do_test analyze5-1.4 {
-  db eval {SELECT sample from sqlite_stat2 WHERE idx='t1y' ORDER BY sampleno}
-} {0 0 0 0 0 0 0 0 0 0}
-do_test analyze5-1.5 {
-  db eval {SELECT sample from sqlite_stat2 WHERE idx='t1z' ORDER BY sampleno}
-} {0 0 0 0 1 1 1 2 2 3}
-do_test analyze5-1.6 {
-  db eval {SELECT sample from sqlite_stat2 WHERE idx='t1t' ORDER BY sampleno}
-} {0.5 0.5 0.5 0.5 1.5 1.5 1.5 2.5 2.5 3.5}
+  ifcapable stat4 {
+    db eval {
+      SELECT DISTINCT lindex(test_decode(sample),0) 
+        FROM sqlite_stat4 WHERE idx='t1u' ORDER BY nlt;
+    }
+  } else {
+    db eval {
+      SELECT sample FROM sqlite_stat3 WHERE idx='t1u' ORDER BY nlt;
+    }
+  }
+} {alpha bravo charlie delta}
 
+do_test analyze5-1.1 {
+  ifcapable stat4 {
+    db eval {
+      SELECT DISTINCT lower(lindex(test_decode(sample), 0)) 
+        FROM sqlite_stat4 WHERE idx='t1v' ORDER BY 1
+    }
+  } else {
+    db eval {
+      SELECT lower(sample) FROM sqlite_stat3 WHERE idx='t1v' ORDER BY 1
+    }
+  }
+} {alpha bravo charlie delta}
+ifcapable stat4 {
+  do_test analyze5-1.2 {
+    db eval {SELECT idx, count(*) FROM sqlite_stat4 GROUP BY 1 ORDER BY 1}
+  } {t1t 8 t1u 8 t1v 8 t1w 8 t1x 8 t1y 9 t1z 8}
+} else {
+  do_test analyze5-1.2 {
+    db eval {SELECT idx, count(*) FROM sqlite_stat3 GROUP BY 1 ORDER BY 1}
+  } {t1t 4 t1u 4 t1v 4 t1w 4 t1x 4 t1y 2 t1z 4}
+}
 
 # Verify that range queries generate the correct row count estimates
 #
 foreach {testid where index rows} {
     1  {z>=0 AND z<=0}       t1z  400
     2  {z>=1 AND z<=1}       t1z  300
-    3  {z>=2 AND z<=2}       t1z  200
-    4  {z>=3 AND z<=3}       t1z  100
-    5  {z>=4 AND z<=4}       t1z   50
-    6  {z>=-1 AND z<=-1}     t1z   50
-    7  {z>1 AND z<3}         t1z  200
+    3  {z>=2 AND z<=2}       t1z  175
+    4  {z>=3 AND z<=3}       t1z  125
+    5  {z>=4 AND z<=4}       t1z    1
+    6  {z>=-1 AND z<=-1}     t1z    1
+    7  {z>1 AND z<3}         t1z  175
     8  {z>0 AND z<100}       t1z  600
     9  {z>=1 AND z<100}      t1z  600
    10  {z>1 AND z<100}       t1z  300
    11  {z>=2 AND z<100}      t1z  300
-   12  {z>2 AND z<100}       t1z  100
-   13  {z>=3 AND z<100}      t1z  100
-   14  {z>3 AND z<100}       t1z   50
-   15  {z>=4 AND z<100}      t1z   50
-   16  {z>=-100 AND z<=-1}   t1z   50
+   12  {z>2 AND z<100}       t1z  125
+   13  {z>=3 AND z<100}      t1z  125
+   14  {z>3 AND z<100}       t1z    1
+   15  {z>=4 AND z<100}      t1z    1
+   16  {z>=-100 AND z<=-1}   t1z    1
    17  {z>=-100 AND z<=0}    t1z  400
-   18  {z>=-100 AND z<0}     t1z   50
+   18  {z>=-100 AND z<0}     t1z    1
    19  {z>=-100 AND z<=1}    t1z  700
    20  {z>=-100 AND z<2}     t1z  700
-   21  {z>=-100 AND z<=2}    t1z  900
-   22  {z>=-100 AND z<3}     t1z  900
+   21  {z>=-100 AND z<=2}    t1z  875
+   22  {z>=-100 AND z<3}     t1z  875
   
    31  {z>=0.0 AND z<=0.0}   t1z  400
    32  {z>=1.0 AND z<=1.0}   t1z  300
-   33  {z>=2.0 AND z<=2.0}   t1z  200
-   34  {z>=3.0 AND z<=3.0}   t1z  100
-   35  {z>=4.0 AND z<=4.0}   t1z   50
-   36  {z>=-1.0 AND z<=-1.0} t1z   50
-   37  {z>1.5 AND z<3.0}     t1z  200
-   38  {z>0.5 AND z<100}     t1z  600
+   33  {z>=2.0 AND z<=2.0}   t1z  175
+   34  {z>=3.0 AND z<=3.0}   t1z  125
+   35  {z>=4.0 AND z<=4.0}   t1z    1
+   36  {z>=-1.0 AND z<=-1.0} t1z    1
+   37  {z>1.5 AND z<3.0}     t1z  174
+   38  {z>0.5 AND z<100}     t1z  599
    39  {z>=1.0 AND z<100}    t1z  600
-   40  {z>1.5 AND z<100}     t1z  300
+   40  {z>1.5 AND z<100}     t1z  299
    41  {z>=2.0 AND z<100}    t1z  300
-   42  {z>2.1 AND z<100}     t1z  100
-   43  {z>=3.0 AND z<100}    t1z  100
-   44  {z>3.2 AND z<100}     t1z   50
-   45  {z>=4.0 AND z<100}    t1z   50
-   46  {z>=-100 AND z<=-1.0} t1z   50
+   42  {z>2.1 AND z<100}     t1z  124
+   43  {z>=3.0 AND z<100}    t1z  125
+   44  {z>3.2 AND z<100}     t1z    1
+   45  {z>=4.0 AND z<100}    t1z    1
+   46  {z>=-100 AND z<=-1.0} t1z    1
    47  {z>=-100 AND z<=0.0}  t1z  400
-   48  {z>=-100 AND z<0.0}   t1z   50
+   48  {z>=-100 AND z<0.0}   t1z    1
    49  {z>=-100 AND z<=1.0}  t1z  700
    50  {z>=-100 AND z<2.0}   t1z  700
-   51  {z>=-100 AND z<=2.0}  t1z  900
-   52  {z>=-100 AND z<3.0}   t1z  900
+   51  {z>=-100 AND z<=2.0}  t1z  875
+   52  {z>=-100 AND z<3.0}   t1z  875
   
-  101  {z=-1}                t1z   50
+  101  {z=-1}                t1z    1
   102  {z=0}                 t1z  400
   103  {z=1}                 t1z  300
-  104  {z=2}                 t1z  200
-  105  {z=3}                 t1z  100
-  106  {z=4}                 t1z   50
-  107  {z=-10.0}             t1z   50
+  104  {z=2}                 t1z  175
+  105  {z=3}                 t1z  125
+  106  {z=4}                 t1z    1
+  107  {z=-10.0}             t1z    1
   108  {z=0.0}               t1z  400
   109  {z=1.0}               t1z  300
-  110  {z=2.0}               t1z  200
-  111  {z=3.0}               t1z  100
-  112  {z=4.0}               t1z   50
-  113  {z=1.5}               t1z   50
-  114  {z=2.5}               t1z   50
+  110  {z=2.0}               t1z  175
+  111  {z=3.0}               t1z  125
+  112  {z=4.0}               t1z    1
+  113  {z=1.5}               t1z    1
+  114  {z=2.5}               t1z    1
   
-  201  {z IN (-1)}           t1z   50
+  201  {z IN (-1)}           t1z    1
   202  {z IN (0)}            t1z  400
   203  {z IN (1)}            t1z  300
-  204  {z IN (2)}            t1z  200
-  205  {z IN (3)}            t1z  100
-  206  {z IN (4)}            t1z   50
-  207  {z IN (0.5)}          t1z   50
+  204  {z IN (2)}            t1z  175
+  205  {z IN (3)}            t1z  125
+  206  {z IN (4)}            t1z    1
+  207  {z IN (0.5)}          t1z    1
   208  {z IN (0,1)}          t1z  700
-  209  {z IN (0,1,2)}        t1z  900
+  209  {z IN (0,1,2)}        t1z  875
   210  {z IN (0,1,2,3)}      {}   100
   211  {z IN (0,1,2,3,4,5)}  {}   100
-  212  {z IN (1,2)}          t1z  500
+  212  {z IN (1,2)}          t1z  475
   213  {z IN (2,3)}          t1z  300
   214  {z=3 OR z=2}          t1z  300
-  215  {z IN (-1,3)}         t1z  150
-  216  {z=-1 OR z=3}         t1z  150
+  215  {z IN (-1,3)}         t1z  126
+  216  {z=-1 OR z=3}         t1z  126
 
-  300  {y=0}                 {}   100
-  301  {y=1}                 t1y   50
-  302  {y=0.1}               t1y   50
+  300  {y=0}                 t1y  974
+  301  {y=1}                 t1y   26
+  302  {y=0.1}               t1y    1
 
   400  {x IS NULL}           t1x  400
 
 } {
   # Verify that the expected index is used with the expected row count
-  do_test analyze5-1.${testid}a {
-    set x [lindex [eqp "SELECT * FROM t1 WHERE $where"] 3]
-    set idx {}
-    regexp {INDEX (t1.) } $x all idx
-    regexp {~([0-9]+) rows} $x all nrow
-    list $idx $nrow
-  } [list $index $rows]
+  # No longer valid due to an EXPLAIN QUERY PLAN output format change
+  # do_test analyze5-1.${testid}a {
+  #   set x [lindex [eqp "SELECT * FROM t1 WHERE $where"] 3]
+  #   set idx {}
+  #   regexp {INDEX (t1.) } $x all idx
+  #   regexp {~([0-9]+) rows} $x all nrow
+  #   list $idx $nrow
+  # } [list $index $rows]
 
   # Verify that the same result is achieved regardless of whether or not
   # the index is used
@@ -204,23 +227,24 @@ db eval {
 # Verify that range queries generate the correct row count estimates
 #
 foreach {testid where index rows} {
-  500  {x IS NULL AND u='charlie'}         t1u  20
-  501  {x=1 AND u='charlie'}               t1x   5
-  502  {x IS NULL}                          {} 100
-  503  {x=1}                               t1x  50
-  504  {x IS NOT NULL}                     t1x  25
+  500  {x IS NULL AND u='charlie'}         t1u  17
+  501  {x=1 AND u='charlie'}               t1x   1
+  502  {x IS NULL}                         t1x 995
+  503  {x=1}                               t1x   1
+  504  {x IS NOT NULL}                     t1x   2
   505  {+x IS NOT NULL}                     {} 500
   506  {upper(x) IS NOT NULL}               {} 500
 
 } {
   # Verify that the expected index is used with the expected row count
-  do_test analyze5-1.${testid}a {
-    set x [lindex [eqp "SELECT * FROM t1 WHERE $where"] 3]
-    set idx {}
-    regexp {INDEX (t1.) } $x all idx
-    regexp {~([0-9]+) rows} $x all nrow
-    list $idx $nrow
-  } [list $index $rows]
+  # No longer valid due to an EXPLAIN QUERY PLAN format change
+  # do_test analyze5-1.${testid}a {
+  #   set x [lindex [eqp "SELECT * FROM t1 WHERE $where"] 3]
+  #   set idx {}
+  #   regexp {INDEX (t1.) } $x all idx
+  #   regexp {~([0-9]+) rows} $x all nrow
+  #   list $idx $nrow
+  # } [list $index $rows]
 
   # Verify that the same result is achieved regardless of whether or not
   # the index is used