test/analyzeG.test

   1 # 2020-02-23
   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 # Tests for functionality related to ANALYZE.
  12 #
  13
  14 set testdir [file dirname $argv0]
  15 source $testdir/tester.tcl
  16
  17 ifcapable !stat4 {
  18   finish_test
  19   return
  20 }
  21 set testprefix analyzeG
  22
  23 proc do_scan_order_test {tn sql expect} {
  24   uplevel [list do_test $tn [subst -nocommands {
  25     set res ""
  26     db eval "explain query plan $sql" {
  27       lappend res [set detail]
  28     }
  29     set res
  30   }] [list {*}$expect]]
  31 }
  32
  33 #-------------------------------------------------------------------------
  34 # Test cases 1.* seek to verify that even if an index is not used, its
  35 # stat4 data may be used by the planner to estimate the number of
  36 # rows that match an unindexed constraint on the same column.
  37 #
  38 do_execsql_test 1.0 {
  39   PRAGMA automatic_index = 0;
  40   CREATE TABLE t1(a, x);
  41   CREATE TABLE t2(b, y);
  42   WITH s(i) AS (
  43     SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<100
  44   )
  45   INSERT INTO t1 SELECT (i%50), NULL FROM s;
  46   WITH s(i) AS (
  47     SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<100
  48   )
  49   INSERT INTO t2 SELECT (CASE WHEN i<95 THEN 44 ELSE i END), NULL FROM s;
  50 }
  51
  52 # Join tables t1 and t2. Both contain 100 rows. (a=44) matches 2 rows
  53 # in "t1", (b=44) matches 95 rows in table "t2". But the planner doesn't
  54 # know this, so it has no preference as to which order the tables are
  55 # scanned in. In practice this means that tables are scanned in the order
  56 # they are specified in in the FROM clause.
  57 do_scan_order_test 1.1.1 {
  58   SELECT * FROM t1, t2 WHERE a=44 AND b=44;
  59 } {
  60   {SCAN TABLE t1} {SCAN TABLE t2}
  61 }
  62 do_scan_order_test 1.1.2 {
  63   SELECT * FROM t2, t1 WHERE a=44 AND b=44
  64 } {
  65   {SCAN TABLE t2} {SCAN TABLE t1}
  66 }
  67
  68 do_execsql_test 1.2 {
  69   CREATE INDEX t2b ON t2(b);
  70   ANALYZE;
  71 }
  72
  73 # Now, with the ANALYZE data, the planner knows that (b=44) matches a
  74 # large number of rows. So it elects to scan table "t1" first, regardless
  75 # of the order in which the tables are specified in the FROM clause.
  76 do_scan_order_test 1.3.1 {
  77   SELECT * FROM t1, t2 WHERE a=44 AND b=44;
  78 } {
  79   {SCAN TABLE t1} {SCAN TABLE t2}
  80 }
  81 do_scan_order_test 1.3.2 {
  82   SELECT * FROM t2, t1 WHERE a=44 AND b=44
  83 } {
  84   {SCAN TABLE t1} {SCAN TABLE t2}
  85 }
  86
  87
  88 finish_test