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