test/analyze9.test

   1 # 2013 August 3
   2 #
   3 # The author disclaims copyright to this source code.  In place of
   4 # a legal notice, here is a blessing:
   5 #
   6 #    May you do good and not evil.
   7 #    May you find forgiveness for yourself and forgive others.
   8 #    May you share freely, never taking more than you give.
   9 #
  10 #***********************************************************************
  11 #
  12 # This file contains automated tests used to verify that the sqlite_stat4
  13 # functionality is working.
  14 #
  15
  16 set testdir [file dirname $argv0]
  17 source $testdir/tester.tcl
  18 set testprefix analyze9
  19
  20 ifcapable !stat4 {
  21   finish_test
  22   return
  23 }
  24
  25 proc s {blob} {
  26   set ret ""
  27   binary scan $blob c* bytes
  28   foreach b $bytes {
  29     set t [binary format c $b]
  30     if {[string is print $t]} {
  31       append ret $t
  32     } else {
  33       append ret .
  34     }
  35   }
  36   return $ret
  37 }
  38 db function s s
  39
  40 do_execsql_test 1.0 {
  41   CREATE TABLE t1(a TEXT, b TEXT);
  42   INSERT INTO t1 VALUES('(0)', '(0)');
  43   INSERT INTO t1 VALUES('(1)', '(1)');
  44   INSERT INTO t1 VALUES('(2)', '(2)');
  45   INSERT INTO t1 VALUES('(3)', '(3)');
  46   INSERT INTO t1 VALUES('(4)', '(4)');
  47   CREATE INDEX i1 ON t1(a, b);
  48 } {}
  49
  50
  51 do_execsql_test 1.1 {
  52   ANALYZE;
  53 } {}
  54
  55 do_execsql_test 1.2 {
  56   SELECT tbl,idx,nEq,nLt,nDLt,test_decode(sample) FROM sqlite_stat4;
  57 } {
  58   t1 i1 {1 1 1} {0 0 0} {0 0 0} {(0) (0) 1}
  59   t1 i1 {1 1 1} {1 1 1} {1 1 1} {(1) (1) 2}
  60   t1 i1 {1 1 1} {2 2 2} {2 2 2} {(2) (2) 3}
  61   t1 i1 {1 1 1} {3 3 3} {3 3 3} {(3) (3) 4}
  62   t1 i1 {1 1 1} {4 4 4} {4 4 4} {(4) (4) 5}
  63 }
  64
  65 if {[permutation] != "utf16"} {
  66   do_execsql_test 1.3 {
  67     SELECT tbl,idx,nEq,nLt,nDLt,s(sample) FROM sqlite_stat4;
  68   } {
  69     t1 i1 {1 1 1} {0 0 0} {0 0 0} ....(0)(0)
  70     t1 i1 {1 1 1} {1 1 1} {1 1 1} ....(1)(1).
  71     t1 i1 {1 1 1} {2 2 2} {2 2 2} ....(2)(2).
  72     t1 i1 {1 1 1} {3 3 3} {3 3 3} ....(3)(3).
  73     t1 i1 {1 1 1} {4 4 4} {4 4 4} ....(4)(4).
  74   }
  75 }
  76
  77
  78 #-------------------------------------------------------------------------
  79 # This is really just to test SQL user function "test_decode".
  80 #
  81 reset_db
  82 do_execsql_test 2.1 {
  83   CREATE TABLE t1(a, b, c);
  84   INSERT INTO t1 VALUES('some text', 14, NULL);
  85   INSERT INTO t1 VALUES(22.0, NULL, x'656667');
  86   CREATE INDEX i1 ON t1(a, b, c);
  87   ANALYZE;
  88   SELECT test_decode(sample) FROM sqlite_stat4;
  89 } {
  90   {22.0 NULL x'656667' 2}
  91   {{some text} 14 NULL 1}
  92 }
  93
  94 #-------------------------------------------------------------------------
  95 #
  96 reset_db
  97 do_execsql_test 3.1 {
  98   CREATE TABLE t2(a, b);
  99   CREATE INDEX i2 ON t2(a, b);
 100   BEGIN;
 101 }
 102
 103 do_test 3.2 {
 104   for {set i 0} {$i < 1000} {incr i} {
 105     set a [expr $i / 10]
 106     set b [expr int(rand() * 15.0)]
 107     execsql { INSERT INTO t2 VALUES($a, $b) }
 108   }
 109   execsql COMMIT
 110 } {}
 111
 112 db func lindex lindex
 113
 114 # Each value of "a" occurs exactly 10 times in the table.
 115 #
 116 do_execsql_test 3.3.1 {
 117   SELECT count(*) FROM t2 GROUP BY a;
 118 } [lrange [string repeat "10 " 100] 0 99]
 119
 120 # The first element in the "nEq" list of all samples should therefore be 10.
 121 #
 122 do_execsql_test 3.3.2 {
 123   ANALYZE;
 124   SELECT lindex(nEq, 0) FROM sqlite_stat4;
 125 } [lrange [string repeat "10 " 100] 0 23]
 126
 127 #-------------------------------------------------------------------------
 128 #
 129 do_execsql_test 3.4 {
 130   DROP TABLE IF EXISTS t1;
 131   CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
 132   INSERT INTO t1 VALUES(1, 1, 'one-a');
 133   INSERT INTO t1 VALUES(11, 1, 'one-b');
 134   INSERT INTO t1 VALUES(21, 1, 'one-c');
 135   INSERT INTO t1 VALUES(31, 1, 'one-d');
 136   INSERT INTO t1 VALUES(41, 1, 'one-e');
 137   INSERT INTO t1 VALUES(51, 1, 'one-f');
 138   INSERT INTO t1 VALUES(61, 1, 'one-g');
 139   INSERT INTO t1 VALUES(71, 1, 'one-h');
 140   INSERT INTO t1 VALUES(81, 1, 'one-i');
 141   INSERT INTO t1 VALUES(91, 1, 'one-j');
 142   INSERT INTO t1 SELECT a+1,2,'two' || substr(c,4) FROM t1;
 143   INSERT INTO t1 SELECT a+2,3,'three'||substr(c,4) FROM t1 WHERE c GLOB 'one-*';
 144   INSERT INTO t1 SELECT a+3,4,'four'||substr(c,4) FROM t1 WHERE c GLOB 'one-*';
 145   INSERT INTO t1 SELECT a+4,5,'five'||substr(c,4) FROM t1 WHERE c GLOB 'one-*';
 146   INSERT INTO t1 SELECT a+5,6,'six'||substr(c,4) FROM t1 WHERE c GLOB 'one-*';
 147   CREATE INDEX t1b ON t1(b);
 148   ANALYZE;
 149   SELECT c FROM t1 WHERE b=3 AND a BETWEEN 30 AND 60;
 150 } {three-d three-e three-f}
 151
 152
 153 #-------------------------------------------------------------------------
 154 # These tests verify that the sample selection for stat4 appears to be
 155 # working as designed.
 156 #
 157
 158 reset_db
 159 db func lindex lindex
 160 db func lrange lrange
 161
 162 do_execsql_test 4.0 {
 163   DROP TABLE IF EXISTS t1;
 164   CREATE TABLE t1(a, b, c);
 165   CREATE INDEX i1 ON t1(c, b, a);
 166 }
 167
 168
 169 proc insert_filler_rows_n {iStart args} {
 170   set A(-ncopy) 1
 171   set A(-nval) 1
 172
 173   foreach {k v} $args {
 174     if {[info exists A($k)]==0} { error "no such option: $k" }
 175     set A($k) $v
 176   }
 177   if {[llength $args] % 2} {
 178     error "option requires an argument: [lindex $args end]"
 179   }
 180
 181   for {set i 0} {$i < $A(-nval)} {incr i} {
 182     set iVal [expr $iStart+$i]
 183     for {set j 0} {$j < $A(-ncopy)} {incr j} {
 184       execsql { INSERT INTO t1 VALUES($iVal, $iVal, $iVal) }
 185     }
 186   }
 187 }
 188
 189 do_test 4.1 {
 190   execsql { BEGIN }
 191   insert_filler_rows_n  0  -ncopy 10 -nval 19
 192   insert_filler_rows_n 20  -ncopy  1 -nval 100
 193
 194   execsql {
 195     INSERT INTO t1(c, b, a) VALUES(200, 1, 'a');
 196     INSERT INTO t1(c, b, a) VALUES(200, 1, 'b');
 197     INSERT INTO t1(c, b, a) VALUES(200, 1, 'c');
 198
 199     INSERT INTO t1(c, b, a) VALUES(200, 2, 'e');
 200     INSERT INTO t1(c, b, a) VALUES(200, 2, 'f');
 201
 202     INSERT INTO t1(c, b, a) VALUES(201, 3, 'g');
 203     INSERT INTO t1(c, b, a) VALUES(201, 4, 'h');
 204
 205     ANALYZE;
 206     SELECT count(*) FROM sqlite_stat4;
 207     SELECT count(*) FROM t1;
 208   }
 209 } {24 297}
 210
 211 do_execsql_test 4.2 {
 212   SELECT
 213     neq,
 214     lrange(nlt, 0, 2),
 215     lrange(ndlt, 0, 2),
 216     lrange(test_decode(sample), 0, 2)
 217     FROM sqlite_stat4
 218   ORDER BY rowid LIMIT 16;
 219 } {
 220   {10 10 10 1} {0 0 0} {0 0 0} {0 0 0}
 221   {10 10 10 1} {10 10 10} {1 1 1} {1 1 1}
 222   {10 10 10 1} {20 20 20} {2 2 2} {2 2 2}
 223   {10 10 10 1} {30 30 30} {3 3 3} {3 3 3}
 224   {10 10 10 1} {40 40 40} {4 4 4} {4 4 4}
 225   {10 10 10 1} {50 50 50} {5 5 5} {5 5 5}
 226   {10 10 10 1} {60 60 60} {6 6 6} {6 6 6}
 227   {10 10 10 1} {70 70 70} {7 7 7} {7 7 7}
 228   {10 10 10 1} {80 80 80} {8 8 8} {8 8 8}
 229   {10 10 10 1} {90 90 90} {9 9 9} {9 9 9}
 230   {10 10 10 1} {100 100 100} {10 10 10} {10 10 10}
 231   {10 10 10 1} {110 110 110} {11 11 11} {11 11 11}
 232   {10 10 10 1} {120 120 120} {12 12 12} {12 12 12}
 233   {10 10 10 1} {130 130 130} {13 13 13} {13 13 13}
 234   {10 10 10 1} {140 140 140} {14 14 14} {14 14 14}
 235   {10 10 10 1} {150 150 150} {15 15 15} {15 15 15}
 236 }
 237
 238 do_execsql_test 4.3 {
 239   SELECT
 240     neq,
 241     lrange(nlt, 0, 2),
 242     lrange(ndlt, 0, 2),
 243     lrange(test_decode(sample), 0, 1)
 244     FROM sqlite_stat4
 245   ORDER BY rowid DESC LIMIT 2;
 246 } {
 247   {2 1 1 1} {295 296 296} {120 122 125} {201 4}
 248   {5 3 1 1} {290 290 290} {119 119 119} {200 1}
 249 }
 250
 251 do_execsql_test 4.4 { SELECT count(DISTINCT c) FROM t1 WHERE c<201 } 120
 252 do_execsql_test 4.5 { SELECT count(DISTINCT c) FROM t1 WHERE c<200 } 119
 253
 254 # Check that the perioidic samples are present.
 255 do_execsql_test 4.6 {
 256   SELECT count(*) FROM sqlite_stat4
 257   WHERE lindex(test_decode(sample), 3) IN
 258     ('34', '68', '102', '136', '170', '204', '238', '272')
 259 } {8}
 260
 261 reset_db
 262 do_test 4.7 {
 263   execsql {
 264     BEGIN;
 265     CREATE TABLE t1(o,t INTEGER PRIMARY KEY);
 266     CREATE INDEX i1 ON t1(o);
 267   }
 268   for {set i 0} {$i<10000} {incr i [expr (($i<1000)?1:10)]} {
 269     execsql { INSERT INTO t1 VALUES('x', $i) }
 270   }
 271   execsql {
 272     COMMIT;
 273     ANALYZE;
 274     SELECT count(*) FROM sqlite_stat4;
 275   }
 276 } {8}
 277 do_execsql_test 4.8 {
 278   SELECT test_decode(sample) FROM sqlite_stat4;
 279 } {
 280   {x 211} {x 423} {x 635} {x 847}
 281   {x 1590} {x 3710} {x 5830} {x 7950}
 282 }
 283
 284
 285 #-------------------------------------------------------------------------
 286 # The following would cause a crash at one point.
 287 #
 288 reset_db
 289 do_execsql_test 5.1 {
 290   PRAGMA encoding = 'utf-16';
 291   CREATE TABLE t0(v);
 292   ANALYZE;
 293 }
 294
 295 #-------------------------------------------------------------------------
 296 # This was also crashing (corrupt sqlite_stat4 table).
 297 #
 298 reset_db
 299 do_execsql_test 6.1 {
 300   CREATE TABLE t1(a, b);
 301   CREATE INDEX i1 ON t1(a);
 302   CREATE INDEX i2 ON t1(b);
 303   INSERT INTO t1 VALUES(1, 1);
 304   INSERT INTO t1 VALUES(2, 2);
 305   INSERT INTO t1 VALUES(3, 3);
 306   INSERT INTO t1 VALUES(4, 4);
 307   INSERT INTO t1 VALUES(5, 5);
 308   ANALYZE;
 309   PRAGMA writable_schema = 1;
 310   CREATE TEMP TABLE x1 AS
 311     SELECT tbl,idx,neq,nlt,ndlt,sample FROM sqlite_stat4
 312     ORDER BY (rowid%5), rowid;
 313   DELETE FROM sqlite_stat4;
 314   INSERT INTO sqlite_stat4 SELECT * FROM x1;
 315   PRAGMA writable_schema = 0;
 316   ANALYZE sqlite_master;
 317 }
 318 do_execsql_test 6.2 {
 319   SELECT * FROM t1 WHERE a = 'abc';
 320 }
 321
 322 #-------------------------------------------------------------------------
 323 # The following tests experiment with adding corrupted records to the
 324 # 'sample' column of the sqlite_stat4 table.
 325 #
 326 reset_db
 327 sqlite3_db_config_lookaside db 0 0 0
 328
 329 database_may_be_corrupt
 330 do_execsql_test 7.1 {
 331   CREATE TABLE t1(a, b);
 332   CREATE INDEX i1 ON t1(a, b);
 333   INSERT INTO t1 VALUES(1, 1);
 334   INSERT INTO t1 VALUES(2, 2);
 335   INSERT INTO t1 VALUES(3, 3);
 336   INSERT INTO t1 VALUES(4, 4);
 337   INSERT INTO t1 VALUES(5, 5);
 338   ANALYZE;
 339   UPDATE sqlite_stat4 SET sample = X'' WHERE rowid = 1;
 340   ANALYZE sqlite_master;
 341 }
 342
 343 do_execsql_test 7.2 {
 344   UPDATE sqlite_stat4 SET sample = X'FFFF';
 345   ANALYZE sqlite_master;
 346   SELECT * FROM t1 WHERE a = 1;
 347 } {1 1}
 348
 349 do_execsql_test 7.3 {
 350   ANALYZE;
 351   UPDATE sqlite_stat4 SET neq = '0 0 0';
 352   ANALYZE sqlite_master;
 353   SELECT * FROM t1 WHERE a = 1;
 354 } {1 1}
 355
 356 do_execsql_test 7.4 {
 357   ANALYZE;
 358   UPDATE sqlite_stat4 SET ndlt = '0 0 0';
 359   ANALYZE sqlite_master;
 360   SELECT * FROM t1 WHERE a = 3;
 361 } {3 3}
 362
 363 do_execsql_test 7.5 {
 364   ANALYZE;
 365   UPDATE sqlite_stat4 SET nlt = '0 0 0';
 366   ANALYZE sqlite_master;
 367   SELECT * FROM t1 WHERE a = 5;
 368 } {5 5}
 369
 370 database_never_corrupt
 371
 372 #-------------------------------------------------------------------------
 373 #
 374 reset_db
 375 do_execsql_test 8.1 {
 376   CREATE TABLE t1(x TEXT);
 377   CREATE INDEX i1 ON t1(x);
 378   INSERT INTO t1 VALUES('1');
 379   INSERT INTO t1 VALUES('2');
 380   INSERT INTO t1 VALUES('3');
 381   INSERT INTO t1 VALUES('4');
 382   ANALYZE;
 383 }
 384 do_execsql_test 8.2 {
 385   SELECT * FROM t1 WHERE x = 3;
 386 } {3}
 387
 388 #-------------------------------------------------------------------------
 389 # Check that the bug fixed by [91733bc485] really is fixed.
 390 #
 391 reset_db
 392 do_execsql_test 9.1 {
 393   CREATE TABLE t1(a, b, c, d, e);
 394   CREATE INDEX i1 ON t1(a, b, c, d);
 395   CREATE INDEX i2 ON t1(e);
 396 }
 397 do_test 9.2 {
 398   execsql BEGIN;
 399   for {set i 0} {$i < 100} {incr i} {
 400     execsql "INSERT INTO t1 VALUES('x', 'y', 'z', $i, [expr $i/2])"
 401   }
 402   for {set i 0} {$i < 20} {incr i} {
 403     execsql "INSERT INTO t1 VALUES('x', 'y', 'z', 101, $i)"
 404   }
 405   for {set i 102} {$i < 200} {incr i} {
 406     execsql "INSERT INTO t1 VALUES('x', 'y', 'z', $i, [expr $i/2])"
 407   }
 408   execsql COMMIT
 409   execsql ANALYZE
 410 } {}
 411
 412 do_eqp_test 9.3.1 {
 413   SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=101 AND e=5;
 414 } {/t1 USING INDEX i2/}
 415 do_eqp_test 9.3.2 {
 416   SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=99 AND e=5;
 417 } {/t1 USING INDEX i1/}
 418
 419 set value_d [expr 101]
 420 do_eqp_test 9.4.1 {
 421   SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=$value_d AND e=5
 422 } {/t1 USING INDEX i2/}
 423 set value_d [expr 99]
 424 do_eqp_test 9.4.2 {
 425   SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=$value_d AND e=5
 426 } {/t1 USING INDEX i1/}
 427
 428 #-------------------------------------------------------------------------
 429 # Check that the planner takes stat4 data into account when considering
 430 # "IS NULL" and "IS NOT NULL" constraints.
 431 #
 432 do_execsql_test 10.1.1 {
 433   DROP TABLE IF EXISTS t3;
 434   CREATE TABLE t3(a, b);
 435   CREATE INDEX t3a ON t3(a);
 436   CREATE INDEX t3b ON t3(b);
 437 }
 438 do_test 10.1.2 {
 439   for {set i 1} {$i < 100} {incr i} {
 440     if {$i>90} { set a $i } else { set a NULL }
 441     set b [expr $i % 5]
 442     execsql "INSERT INTO t3 VALUES($a, $b)"
 443   }
 444   execsql ANALYZE
 445 } {}
 446 do_eqp_test 10.1.3 {
 447   SELECT * FROM t3 WHERE a IS NULL AND b = 2
 448 } {/t3 USING INDEX t3b/}
 449 do_eqp_test 10.1.4 {
 450   SELECT * FROM t3 WHERE a IS NOT NULL AND b = 2
 451 } {/t3 USING INDEX t3a/}
 452
 453 do_execsql_test 10.2.1 {
 454   DROP TABLE IF EXISTS t3;
 455   CREATE TABLE t3(x, a, b);
 456   CREATE INDEX t3a ON t3(x, a);
 457   CREATE INDEX t3b ON t3(x, b);
 458 }
 459 do_test 10.2.2 {
 460   for {set i 1} {$i < 100} {incr i} {
 461     if {$i>90} { set a $i } else { set a NULL }
 462     set b [expr $i % 5]
 463     execsql "INSERT INTO t3 VALUES('xyz', $a, $b)"
 464   }
 465   execsql ANALYZE
 466 } {}
 467 do_eqp_test 10.2.3 {
 468   SELECT * FROM t3 WHERE x = 'xyz' AND a IS NULL AND b = 2
 469 } {/t3 USING INDEX t3b/}
 470 do_eqp_test 10.2.4 {
 471   SELECT * FROM t3 WHERE x = 'xyz' AND a IS NOT NULL AND b = 2
 472 } {/t3 USING INDEX t3a/}
 473
 474 #-------------------------------------------------------------------------
 475 # Check that stat4 data is used correctly with non-default collation
 476 # sequences.
 477 #
 478 foreach {tn schema} {
 479   1 {
 480     CREATE TABLE t4(a COLLATE nocase, b);
 481     CREATE INDEX t4a ON t4(a);
 482     CREATE INDEX t4b ON t4(b);
 483   }
 484   2 {
 485     CREATE TABLE t4(a, b);
 486     CREATE INDEX t4a ON t4(a COLLATE nocase);
 487     CREATE INDEX t4b ON t4(b);
 488   }
 489 } {
 490   drop_all_tables
 491   do_test 11.$tn.1 { execsql $schema } {}
 492
 493   do_test 11.$tn.2 {
 494     for {set i 0} {$i < 100} {incr i} {
 495       if { ($i % 10)==0 } { set a ABC } else { set a DEF }
 496       set b [expr $i % 5]
 497         execsql { INSERT INTO t4 VALUES($a, $b) }
 498     }
 499     execsql ANALYZE
 500   } {}
 501
 502   do_eqp_test 11.$tn.3 {
 503     SELECT * FROM t4 WHERE a = 'def' AND b = 3;
 504   } {/t4 USING INDEX t4b/}
 505
 506   if {$tn==1} {
 507     set sql "SELECT * FROM t4 WHERE a = 'abc' AND b = 3;"
 508     do_eqp_test 11.$tn.4 $sql {/t4 USING INDEX t4a/}
 509   } else {
 510
 511     set sql "SELECT * FROM t4 WHERE a = 'abc' COLLATE nocase AND b = 3;"
 512     do_eqp_test 11.$tn.5 $sql {/t4 USING INDEX t4a/}
 513
 514     set sql "SELECT * FROM t4 WHERE a COLLATE nocase = 'abc' AND b = 3;"
 515     do_eqp_test 11.$tn.6 $sql {/t4 USING INDEX t4a/}
 516   }
 517 }
 518
 519 foreach {tn schema} {
 520   1 {
 521     CREATE TABLE t4(x, a COLLATE nocase, b);
 522     CREATE INDEX t4a ON t4(x, a);
 523     CREATE INDEX t4b ON t4(x, b);
 524   }
 525   2 {
 526     CREATE TABLE t4(x, a, b);
 527     CREATE INDEX t4a ON t4(x, a COLLATE nocase);
 528     CREATE INDEX t4b ON t4(x, b);
 529   }
 530 } {
 531   drop_all_tables
 532   do_test 12.$tn.1 { execsql $schema } {}
 533
 534   do_test 12.$tn.2 {
 535     for {set i 0} {$i < 100} {incr i} {
 536       if { ($i % 10)==0 } { set a ABC } else { set a DEF }
 537       set b [expr $i % 5]
 538         execsql { INSERT INTO t4 VALUES(X'abcdef', $a, $b) }
 539     }
 540     execsql ANALYZE
 541   } {}
 542
 543   do_eqp_test 12.$tn.3 {
 544     SELECT * FROM t4 WHERE x=X'abcdef' AND a = 'def' AND b = 3;
 545   } {/t4 USING INDEX t4b/}
 546
 547   if {$tn==1} {
 548     set sql "SELECT * FROM t4 WHERE x=X'abcdef' AND a = 'abc' AND b = 3;"
 549     do_eqp_test 12.$tn.4 $sql {/t4 USING INDEX t4a/}
 550   } else {
 551     set sql {
 552       SELECT * FROM t4 WHERE x=X'abcdef' AND a = 'abc' COLLATE nocase AND b = 3
 553     }
 554     do_eqp_test 12.$tn.5 $sql {/t4 USING INDEX t4a/}
 555     set sql {
 556       SELECT * FROM t4 WHERE x=X'abcdef' AND a COLLATE nocase = 'abc' AND b = 3
 557     }
 558     do_eqp_test 12.$tn.6 $sql {/t4 USING INDEX t4a/}
 559   }
 560 }
 561
 562 #-------------------------------------------------------------------------
 563 # Check that affinities are taken into account when using stat4 data to
 564 # estimate the number of rows scanned by a rowid constraint.
 565 #
 566 drop_all_tables
 567 do_test 13.1 {
 568   execsql {
 569     CREATE TABLE t1(a, b, c, d);
 570     CREATE INDEX i1 ON t1(a);
 571     CREATE INDEX i2 ON t1(b, c);
 572   }
 573   for {set i 0} {$i<100} {incr i} {
 574     if {$i %2} {set a abc} else {set a def}
 575     execsql { INSERT INTO t1(rowid, a, b, c) VALUES($i, $a, $i, $i) }
 576   }
 577   execsql ANALYZE
 578 } {}
 579 do_eqp_test 13.2.1 {
 580   SELECT * FROM t1 WHERE a='abc' AND rowid<15 AND b<12
 581 } {/SEARCH TABLE t1 USING INDEX i1/}
 582 do_eqp_test 13.2.2 {
 583   SELECT * FROM t1 WHERE a='abc' AND rowid<'15' AND b<12
 584 } {/SEARCH TABLE t1 USING INDEX i1/}
 585 do_eqp_test 13.3.1 {
 586   SELECT * FROM t1 WHERE a='abc' AND rowid<100 AND b<12
 587 } {/SEARCH TABLE t1 USING INDEX i2/}
 588 do_eqp_test 13.3.2 {
 589   SELECT * FROM t1 WHERE a='abc' AND rowid<'100' AND b<12
 590 } {/SEARCH TABLE t1 USING INDEX i2/}
 591
 592 #-------------------------------------------------------------------------
 593 # Check also that affinities are taken into account when using stat4 data
 594 # to estimate the number of rows scanned by any other constraint on a
 595 # column other than the leftmost.
 596 #
 597 drop_all_tables
 598 do_test 14.1 {
 599   execsql { CREATE TABLE t1(a, b INTEGER, c) }
 600   for {set i 0} {$i<100} {incr i} {
 601     set c [expr $i % 3]
 602     execsql { INSERT INTO t1 VALUES('ott', $i, $c) }
 603   }
 604   execsql {
 605     CREATE INDEX i1 ON t1(a, b);
 606     CREATE INDEX i2 ON t1(c);
 607     ANALYZE;
 608   }
 609 } {}
 610 do_eqp_test 13.2.1 {
 611   SELECT * FROM t1 WHERE a='ott' AND b<10 AND c=1
 612 } {/SEARCH TABLE t1 USING INDEX i1/}
 613 do_eqp_test 13.2.2 {
 614   SELECT * FROM t1 WHERE a='ott' AND b<'10' AND c=1
 615 } {/SEARCH TABLE t1 USING INDEX i1/}
 616
 617 #-------------------------------------------------------------------------
 618 # By default, 16 non-periodic samples are collected for the stat4 table.
 619 # The following tests attempt to verify that the most common keys are
 620 # being collected.
 621 #
 622 proc check_stat4 {tn} {
 623   db eval ANALYZE
 624   db eval {SELECT a, b, c, d FROM t1} {
 625     incr k($a)
 626     incr k([list $a $b])
 627     incr k([list $a $b $c])
 628     if { [info exists k([list $a $b $c $d])]==0 } { incr nRow }
 629     incr k([list $a $b $c $d])
 630   }
 631
 632   set L [list]
 633   foreach key [array names k] {
 634     lappend L [list $k($key) $key]
 635   }
 636
 637   set nSample $nRow
 638   if {$nSample>16} {set nSample 16}
 639
 640   set nThreshold [lindex [lsort -decr -integer -index 0 $L] [expr $nSample-1] 0]
 641   foreach key [array names k] {
 642     if {$k($key)>$nThreshold} {
 643       set expect($key) 1
 644     }
 645     if {$k($key)==$nThreshold} {
 646       set possible($key) 1
 647     }
 648   }
 649
 650
 651   set nPossible [expr $nSample - [llength [array names expect]]]
 652
 653   #puts "EXPECT: [array names expect]"
 654   #puts "POSSIBLE($nPossible/[array size possible]): [array names possible]"
 655   #puts "HAVE: [db eval {SELECT test_decode(sample) FROM sqlite_stat4 WHERE idx='i1'}]"
 656
 657   db eval {SELECT test_decode(sample) AS s FROM sqlite_stat4 WHERE idx='i1'} {
 658     set seen 0
 659     for {set i 0} {$i<4} {incr i} {
 660       unset -nocomplain expect([lrange $s 0 $i])
 661       if {[info exists possible([lrange $s 0 $i])]} {
 662         set seen 1
 663         unset -nocomplain possible([lrange $s 0 $i])
 664       }
 665     }
 666     if {$seen} {incr nPossible -1}
 667   }
 668   if {$nPossible<0} {set nPossible 0}
 669
 670   set res [list [llength [array names expect]] $nPossible]
 671   uplevel [list do_test $tn [list set {} $res] {0 0}]
 672 }
 673
 674 drop_all_tables
 675 do_test 14.1.1 {
 676   execsql {
 677     CREATE TABLE t1(a,b,c,d);
 678     CREATE INDEX i1 ON t1(a,b,c,d);
 679   }
 680   for {set i 0} {$i < 160} {incr i} {
 681     execsql { INSERT INTO t1 VALUES($i,$i,$i,$i) }
 682     if {($i % 10)==0} { execsql { INSERT INTO t1 VALUES($i,$i,$i,$i) } }
 683   }
 684 } {}
 685 check_stat4 14.1.2
 686
 687 do_test 14.2.1 {
 688   execsql { DELETE FROM t1 }
 689   for {set i 0} {$i < 1600} {incr i} {
 690     execsql { INSERT INTO t1 VALUES($i/10,$i/17,$i/27,$i/37) }
 691   }
 692 } {}
 693 check_stat4 14.2.2
 694
 695 do_test 14.3.1 {
 696   for {set i 0} {$i < 10} {incr i} {
 697     execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) }
 698     execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) }
 699     execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) }
 700     execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) }
 701     execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) }
 702     execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) }
 703     execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) }
 704     execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) }
 705     execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) }
 706     execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) }
 707   }
 708 } {}
 709 check_stat4 14.3.2
 710
 711 do_test 14.4.1 {
 712   execsql {DELETE FROM t1}
 713   for {set i 1} {$i < 160} {incr i} {
 714     set b [expr $i % 10]
 715     if {$b==0 || $b==2} {set b 1}
 716     execsql { INSERT INTO t1 VALUES($i/10,$b,$i,$i) }
 717   }
 718 } {}
 719 check_stat4 14.4.2
 720 db func lrange lrange
 721 db func lindex lindex
 722 do_execsql_test 14.4.3 {
 723   SELECT lrange(test_decode(sample), 0, 1) AS s FROM sqlite_stat4
 724   WHERE lindex(s, 1)=='1' ORDER BY rowid
 725 } {
 726   {0 1} {1 1} {2 1} {3 1}
 727   {4 1} {5 1} {6 1} {7 1}
 728   {8 1} {9 1} {10 1} {11 1}
 729   {12 1} {13 1} {14 1} {15 1}
 730 }
 731
 732 #-------------------------------------------------------------------------
 733 # Test that nothing untoward happens if the stat4 table contains entries
 734 # for indexes that do not exist. Or NULL values in the idx column.
 735 # Or NULL values in any of the other columns.
 736 #
 737 drop_all_tables
 738 do_execsql_test 15.1 {
 739   CREATE TABLE x1(a, b, UNIQUE(a, b));
 740   INSERT INTO x1 VALUES(1, 2);
 741   INSERT INTO x1 VALUES(3, 4);
 742   INSERT INTO x1 VALUES(5, 6);
 743   ANALYZE;
 744   INSERT INTO sqlite_stat4 VALUES(NULL, NULL, NULL, NULL, NULL, NULL);
 745 }
 746 db close
 747 sqlite3 db test.db
 748 do_execsql_test 15.2 { SELECT * FROM x1 } {1 2 3 4 5 6}
 749
 750 do_execsql_test 15.3 {
 751   INSERT INTO sqlite_stat4 VALUES(42, 42, 42, 42, 42, 42);
 752 }
 753 db close
 754 sqlite3 db test.db
 755 do_execsql_test 15.4 { SELECT * FROM x1 } {1 2 3 4 5 6}
 756
 757 do_execsql_test 15.5 {
 758   UPDATE sqlite_stat1 SET stat = NULL;
 759 }
 760 db close
 761 sqlite3 db test.db
 762 do_execsql_test 15.6 { SELECT * FROM x1 } {1 2 3 4 5 6}
 763
 764 do_execsql_test 15.7 {
 765   ANALYZE;
 766   UPDATE sqlite_stat1 SET tbl = 'no such tbl';
 767 }
 768 db close
 769 sqlite3 db test.db
 770 do_execsql_test 15.8 { SELECT * FROM x1 } {1 2 3 4 5 6}
 771
 772 do_execsql_test 15.9 {
 773   ANALYZE;
 774   UPDATE sqlite_stat4 SET neq = NULL, nlt=NULL, ndlt=NULL;
 775 }
 776 db close
 777 sqlite3 db test.db
 778 do_execsql_test 15.10 { SELECT * FROM x1 } {1 2 3 4 5 6}
 779
 780 # This is just for coverage....
 781 do_execsql_test 15.11 {
 782   ANALYZE;
 783   UPDATE sqlite_stat1 SET stat = stat || ' unordered';
 784 }
 785 db close
 786 sqlite3 db test.db
 787 do_execsql_test 15.12 { SELECT * FROM x1 } {1 2 3 4 5 6}
 788
 789 #-------------------------------------------------------------------------
 790 # Test that allocations used for sqlite_stat4 samples are included in
 791 # the quantity returned by SQLITE_DBSTATUS_SCHEMA_USED.
 792 #
 793 set one [string repeat x 1000]
 794 set two [string repeat x 2000]
 795 do_test 16.1 {
 796   reset_db
 797   execsql {
 798     CREATE TABLE t1(a, UNIQUE(a));
 799     INSERT INTO t1 VALUES($one);
 800     ANALYZE;
 801   }
 802   set nByte [lindex [sqlite3_db_status db SCHEMA_USED 0] 1]
 803
 804   reset_db
 805   execsql {
 806     CREATE TABLE t1(a, UNIQUE(a));
 807     INSERT INTO t1 VALUES($two);
 808     ANALYZE;
 809   }
 810   set nByte2 [lindex [sqlite3_db_status db SCHEMA_USED 0] 1]
 811   puts -nonewline " (nByte=$nByte nByte2=$nByte2)"
 812
 813   expr {$nByte2 > $nByte+900 && $nByte2 < $nByte+1100}
 814 } {1}
 815
 816 #-------------------------------------------------------------------------
 817 # Test that stat4 data may be used with partial indexes.
 818 #
 819 do_test 17.1 {
 820   reset_db
 821   execsql {
 822     CREATE TABLE t1(a, b, c, d);
 823     CREATE INDEX i1 ON t1(a, b) WHERE d IS NOT NULL;
 824     INSERT INTO t1 VALUES(-1, -1, -1, NULL);
 825     INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1;
 826     INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1;
 827     INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1;
 828     INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1;
 829     INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1;
 830     INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1;
 831   }
 832
 833   for {set i 0} {$i < 32} {incr i} {
 834     if {$i<8} {set b 0} else { set b $i }
 835     execsql { INSERT INTO t1 VALUES($i%2, $b, $i/2, 'abc') }
 836   }
 837   execsql {ANALYZE main.t1}
 838 } {}
 839
 840 do_catchsql_test 17.1.2 {
 841   ANALYZE temp.t1;
 842 } {1 {no such table: temp.t1}}
 843
 844 do_eqp_test 17.2 {
 845   SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=10 AND c=10;
 846 } {/USING INDEX i1/}
 847 do_eqp_test 17.3 {
 848   SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=0 AND c=10;
 849 } {/USING INDEX i1/}
 850
 851 do_execsql_test 17.4 {
 852   CREATE INDEX i2 ON t1(c, d);
 853   ANALYZE main.i2;
 854 }
 855 do_eqp_test 17.5 {
 856   SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=10 AND c=10;
 857 } {/USING INDEX i1/}
 858 do_eqp_test 17.6 {
 859   SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=0 AND c=10;
 860 } {/USING INDEX i2/}
 861
 862 #-------------------------------------------------------------------------
 863 #
 864 do_test 18.1 {
 865   reset_db
 866   execsql {
 867     CREATE TABLE t1(a, b);
 868     CREATE INDEX i1 ON t1(a, b);
 869   }
 870   for {set i 0} {$i < 9} {incr i} {
 871     execsql {
 872       INSERT INTO t1 VALUES($i, 0);
 873       INSERT INTO t1 VALUES($i, 0);
 874       INSERT INTO t1 VALUES($i, 0);
 875       INSERT INTO t1 VALUES($i, 0);
 876       INSERT INTO t1 VALUES($i, 0);
 877       INSERT INTO t1 VALUES($i, 0);
 878       INSERT INTO t1 VALUES($i, 0);
 879       INSERT INTO t1 VALUES($i, 0);
 880       INSERT INTO t1 VALUES($i, 0);
 881       INSERT INTO t1 VALUES($i, 0);
 882       INSERT INTO t1 VALUES($i, 0);
 883       INSERT INTO t1 VALUES($i, 0);
 884       INSERT INTO t1 VALUES($i, 0);
 885       INSERT INTO t1 VALUES($i, 0);
 886       INSERT INTO t1 VALUES($i, 0);
 887     }
 888   }
 889   execsql ANALYZE
 890   execsql { SELECT count(*) FROM sqlite_stat4 }
 891 } {9}
 892
 893 #-------------------------------------------------------------------------
 894 # For coverage.
 895 #
 896 ifcapable view {
 897   do_test 19.1 {
 898     reset_db
 899     execsql {
 900       CREATE TABLE t1(x, y);
 901       CREATE INDEX i1 ON t1(x, y);
 902       CREATE VIEW v1 AS SELECT * FROM t1;
 903       ANALYZE;
 904     }
 905   } {}
 906 }
 907 ifcapable auth {
 908   proc authproc {op args} {
 909     if {$op == "SQLITE_ANALYZE"} { return "SQLITE_DENY" }
 910     return "SQLITE_OK"
 911   }
 912   do_test 19.2 {
 913     reset_db
 914     db auth authproc
 915     execsql {
 916       CREATE TABLE t1(x, y);
 917       CREATE VIEW v1 AS SELECT * FROM t1;
 918     }
 919     catchsql ANALYZE
 920   } {1 {not authorized}}
 921 }
 922
 923 #-------------------------------------------------------------------------
 924 #
 925 reset_db
 926 proc r {args} { expr rand() }
 927 db func r r
 928 db func lrange lrange
 929 do_test 20.1 {
 930   execsql {
 931     CREATE TABLE t1(a,b,c,d);
 932     CREATE INDEX i1 ON t1(a,b,c,d);
 933   }
 934   for {set i 0} {$i < 16} {incr i} {
 935     execsql {
 936       INSERT INTO t1 VALUES($i, r(), r(), r());
 937       INSERT INTO t1 VALUES($i, $i,  r(), r());
 938       INSERT INTO t1 VALUES($i, $i,  $i,  r());
 939       INSERT INTO t1 VALUES($i, $i,  $i,  $i);
 940       INSERT INTO t1 VALUES($i, $i,  $i,  $i);
 941       INSERT INTO t1 VALUES($i, $i,  $i,  r());
 942       INSERT INTO t1 VALUES($i, $i,  r(), r());
 943       INSERT INTO t1 VALUES($i, r(), r(), r());
 944     }
 945   }
 946 } {}
 947 do_execsql_test 20.2 { ANALYZE }
 948 for {set i 0} {$i<16} {incr i} {
 949     set val "$i $i $i $i"
 950     do_execsql_test 20.3.$i {
 951       SELECT count(*) FROM sqlite_stat4
 952       WHERE lrange(test_decode(sample), 0, 3)=$val
 953     } {1}
 954 }
 955
 956 #-------------------------------------------------------------------------
 957 #
 958 reset_db
 959
 960 do_execsql_test 21.0 {
 961   CREATE TABLE t2(a, b);
 962   CREATE INDEX i2 ON t2(a);
 963 }
 964
 965 do_test 21.1 {
 966   for {set i 1} {$i < 100} {incr i} {
 967     execsql {
 968       INSERT INTO t2 VALUES(CASE WHEN $i < 80 THEN 'one' ELSE 'two' END, $i)
 969     }
 970   }
 971   execsql ANALYZE
 972 } {}
 973
 974 # Condition (a='one') matches 80% of the table. (rowid<10) reduces this to
 975 # 10%, but (rowid<50) only reduces it to 50%. So in the first case below
 976 # the index is used. In the second, it is not.
 977 #
 978 do_eqp_test 21.2 {
 979   SELECT * FROM t2 WHERE a='one' AND rowid < 10
 980 } {/*USING INDEX i2 (a=? AND rowid<?)*/}
 981 do_eqp_test 21.3 {
 982   SELECT * FROM t2 WHERE a='one' AND rowid < 50
 983 } {/*USING INTEGER PRIMARY KEY*/}
 984
 985 #-------------------------------------------------------------------------
 986 #
 987 reset_db
 988 do_execsql_test 22.0 {
 989   CREATE TABLE t3(a, b, c, d, PRIMARY KEY(a, b)) WITHOUT ROWID;
 990   SELECT * FROM t3;
 991 } {}
 992 do_execsql_test 22.1 {
 993   WITH r(x) AS (
 994     SELECT 1
 995     UNION ALL
 996     SELECT x+1 FROM r WHERE x<=100
 997   )
 998
 999   INSERT INTO t3 SELECT
1000     CASE WHEN (x>45 AND x<96) THEN 'B' ELSE 'A' END,  /* Column "a" */
1001     x,                                                /* Column "b" */
1002     CASE WHEN (x<51) THEN 'one' ELSE 'two' END,       /* Column "c" */
1003     x                                                 /* Column "d" */
1004   FROM r;
1005
1006   CREATE INDEX i3 ON t3(c);
1007   CREATE INDEX i4 ON t3(d);
1008   ANALYZE;
1009 }
1010
1011 # Expression (c='one' AND a='B') matches 5 table rows. But (c='one' AND a=A')
1012 # matches 45. Expression (d<?) matches 20. Neither index is a covering index.
1013 #
1014 # Therefore, with stat4 data, SQLite prefers (c='one' AND a='B') over (d<20),
1015 # and (d<20) over (c='one' AND a='A').
1016 foreach {tn where res} {
1017   1 "c='one' AND a='B' AND d < 20"   {/*INDEX i3 (c=? AND a=?)*/}
1018   2 "c='one' AND a='A' AND d < 20"   {/*INDEX i4 (d<?)*/}
1019 } {
1020   do_eqp_test 22.2.$tn "SELECT * FROM t3 WHERE $where" $res
1021 }
1022
1023 proc int_to_char {i} {
1024   set ret ""
1025   set char [list a b c d e f g h i j]
1026   foreach {div} {1000 100 10 1} {
1027     append ret [lindex $char [expr ($i / $div) % 10]]
1028   }
1029   set ret
1030 }
1031 db func int_to_char int_to_char
1032
1033 do_execsql_test 23.0 {
1034   CREATE TABLE t4(
1035     a COLLATE nocase, b, c,
1036     d, e, f,
1037     PRIMARY KEY(c, b, a)
1038   ) WITHOUT ROWID;
1039   CREATE INDEX i41 ON t4(e);
1040   CREATE INDEX i42 ON t4(f);
1041
1042   WITH data(a, b, c, d, e, f) AS (
1043     SELECT int_to_char(0), 'xyz', 'zyx', '*', 0, 0
1044     UNION ALL
1045     SELECT
1046       int_to_char(f+1), b, c, d, (e+1) % 2, f+1
1047     FROM data WHERE f<1024
1048   )
1049   INSERT INTO t4 SELECT a, b, c, d, e, f FROM data;
1050   ANALYZE;
1051 } {}
1052
1053 do_eqp_test 23.1 {
1054   SELECT * FROM t4 WHERE
1055     (e=1 AND b='xyz' AND c='zyx' AND a<'AEA') AND f<300
1056   -- Formerly used index i41.  But i41 is not a covering index whereas
1057   -- the PRIMARY KEY is a covering index, and so as of 2017-10-15, the
1058   -- PRIMARY KEY is preferred.
1059 } {SEARCH TABLE t4 USING PRIMARY KEY (c=? AND b=? AND a<?)}
1060 do_eqp_test 23.2 {
1061   SELECT * FROM t4 WHERE
1062     (e=1 AND b='xyz' AND c='zyx' AND a<'JJJ') AND f<300
1063 } {SEARCH TABLE t4 USING INDEX i42 (f<?)}
1064
1065 do_execsql_test 24.0 {
1066   CREATE TABLE t5(c, d, b, e, a, PRIMARY KEY(a, b, c)) WITHOUT ROWID;
1067   WITH data(a, b, c, d, e) AS (
1068     SELECT 'z', 'y', 0, 0, 0
1069     UNION ALL
1070     SELECT
1071       a, CASE WHEN b='y' THEN 'n' ELSE 'y' END, c+1, e/250, e+1
1072     FROM data
1073     WHERE e<1000
1074   )
1075   INSERT INTO t5(a, b, c, d, e) SELECT * FROM data;
1076   CREATE INDEX t5d ON t5(d);
1077   CREATE INDEX t5e ON t5(e);
1078   ANALYZE;
1079 }
1080
1081 foreach {tn where eqp} {
1082   1 "d=0 AND a='z' AND b='n' AND e<200" {/*t5d (d=? AND a=? AND b=?)*/}
1083   2 "d=0 AND a='z' AND b='n' AND e<100" {/*t5e (e<?)*/}
1084
1085   3 "d=0 AND e<300"                     {/*t5d (d=?)*/}
1086   4 "d=0 AND e<200"                     {/*t5e (e<?)*/}
1087 } {
1088   do_eqp_test 24.$tn "SeLeCt * FROM t5 WHERE $where" $eqp
1089 }
1090
1091 #-------------------------------------------------------------------------
1092 # Test that if stat4 data is available but cannot be used because the
1093 # rhs of a range constraint is a complex expression, the default estimates
1094 # are used instead.
1095 ifcapable stat4&&cte {
1096   do_execsql_test 25.1 {
1097     CREATE TABLE t6(a, b);
1098     WITH ints(i,j) AS (
1099       SELECT 1,1 UNION ALL SELECT i+1,j+1 FROM ints WHERE i<100
1100     ) INSERT INTO t6 SELECT * FROM ints;
1101     CREATE INDEX aa ON t6(a);
1102     CREATE INDEX bb ON t6(b);
1103     ANALYZE;
1104   }
1105
1106   # Term (b<?) is estimated at 25%. Better than (a<30) but not as
1107   # good as (a<20).
1108   do_eqp_test 25.2.1 { SELECT * FROM t6 WHERE a<30 AND b<? } \
1109     {SEARCH TABLE t6 USING INDEX bb (b<?)}
1110   do_eqp_test 25.2.2 { SELECT * FROM t6 WHERE a<20 AND b<? } \
1111     {SEARCH TABLE t6 USING INDEX aa (a<?)}
1112
1113   # Term (b BETWEEN ? AND ?) is estimated at 1/64.
1114   do_eqp_test 25.3.1 {
1115     SELECT * FROM t6 WHERE a BETWEEN 5 AND 10 AND b BETWEEN ? AND ?
1116   } {SEARCH TABLE t6 USING INDEX bb (b>? AND b<?)}
1117
1118   # Term (b BETWEEN ? AND 60) is estimated to return roughly 15 rows -
1119   # 60 from (b<=60) multiplied by 0.25 for the b>=? term. Better than
1120   # (a<20) but not as good as (a<10).
1121   do_eqp_test 25.4.1 {
1122     SELECT * FROM t6 WHERE a < 10 AND (b BETWEEN ? AND 60)
1123   } {SEARCH TABLE t6 USING INDEX aa (a<?)}
1124
1125   do_eqp_test 25.4.2 {
1126     SELECT * FROM t6 WHERE a < 20 AND (b BETWEEN ? AND 60)
1127   } {SEARCH TABLE t6 USING INDEX bb (b>? AND b<?)}
1128 }
1129
1130 #-------------------------------------------------------------------------
1131 # Check that a problem in they way stat4 data is used has been
1132 # resolved (see below).
1133 #
1134 reset_db
1135 do_test 26.1.1 {
1136   db transaction {
1137     execsql {
1138       CREATE TABLE t1(x, y, z);
1139       CREATE INDEX t1xy ON t1(x, y);
1140       CREATE INDEX t1z ON t1(z);
1141     }
1142     for {set i 0} {$i < 10000} {incr i} {
1143       execsql { INSERT INTO t1(x, y) VALUES($i, $i) }
1144     }
1145     for {set i 0} {$i < 10} {incr i} {
1146       execsql {
1147         WITH cnt(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM cnt WHERE x<100)
1148         INSERT INTO t1(x, y) SELECT 10000+$i, x FROM cnt;
1149         INSERT INTO t1(x, y) SELECT 10000+$i, 100;
1150       }
1151     }
1152     execsql {
1153       UPDATE t1 SET z = rowid / 20;
1154       ANALYZE;
1155     }
1156   }
1157 } {}
1158
1159 do_execsql_test 26.1.2 {
1160   SELECT count(*) FROM t1 WHERE x = 10000 AND y < 50;
1161 } {49}
1162 do_execsql_test 26.1.3 {
1163   SELECT count(*) FROM t1 WHERE z = 444;
1164 } {20}
1165
1166 # The analyzer knows that any (z=?) expression matches 20 rows. So it
1167 # will use index "t1z" if the estimate of hits for (x=10000 AND y<50)
1168 # is greater than 20 rows.
1169 #
1170 # And it should be. The analyzer has a stat4 sample as follows:
1171 #
1172 #   sample=(x=10000, y=100) nLt=(10000 10099)
1173 #
1174 # There should be no other samples that start with (x=10000). So it knows
1175 # that (x=10000 AND y<50) must match somewhere between 0 and 99 rows, but
1176 # no more than that. Guessing less than 20 is therefore unreasonable.
1177 #
1178 # At one point though, due to a problem in whereKeyStats(), the planner was
1179 # estimating that (x=10000 AND y<50) would match only 2 rows.
1180 #
1181 do_eqp_test 26.1.4 {
1182   SELECT * FROM t1 WHERE x = 10000 AND y < 50 AND z = 444;
1183 } {SEARCH TABLE t1 USING INDEX t1z (z=?)}
1184
1185
1186 # This test - 26.2.* - tests that another manifestation of the same problem
1187 # is no longer present in the library. Assuming:
1188 #
1189 #   CREATE INDEX t1xy ON t1(x, y)
1190 #
1191 # and that have samples for index t1xy as follows:
1192 #
1193 #
1194 #   sample=('A', 70)        nEq=(100, 2)        nLt=(900, 970)
1195 #   sample=('B', 70)        nEq=(100, 2)        nLt=(1000, 1070)
1196 #
1197 # the planner should estimate that (x = 'B' AND y > 25) matches 76 rows
1198 # (70 * 2/3 + 30). Before, due to the problem, the planner was estimating
1199 # that this matched 100 rows.
1200 #
1201 reset_db
1202 do_execsql_test 26.2.1 {
1203   BEGIN;
1204     CREATE TABLE t1(x, y, z);
1205     CREATE INDEX i1 ON t1(x, y);
1206     CREATE INDEX i2 ON t1(z);
1207
1208     WITH
1209     cnt(y) AS (SELECT 0 UNION ALL SELECT y+1 FROM cnt WHERE y<99),
1210     letters(x) AS (
1211       SELECT 'A' UNION SELECT 'B' UNION SELECT 'C' UNION SELECT 'D'
1212     )
1213     INSERT INTO t1(x, y) SELECT x, y FROM letters, cnt;
1214
1215     WITH
1216     letters(x) AS (
1217       SELECT 'A' UNION SELECT 'B' UNION SELECT 'C' UNION SELECT 'D'
1218     )
1219     INSERT INTO t1(x, y) SELECT x, 70 FROM letters;
1220
1221     WITH
1222     cnt(i) AS (SELECT 0 UNION ALL SELECT i+1 FROM cnt WHERE i<9999)
1223     INSERT INTO t1(x, y) SELECT i, i FROM cnt;
1224
1225     UPDATE t1 SET z = (rowid / 95);
1226     ANALYZE;
1227   COMMIT;
1228 }
1229
1230 do_eqp_test 26.2.2 {
1231   SELECT * FROM t1 WHERE x='B' AND y>25 AND z=?;
1232 } {SEARCH TABLE t1 USING INDEX i1 (x=? AND y>?)}
1233
1234
1235 finish_test