Debian Codegraph @

test/analyzeG.test

raw
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 #-------------------------------------------------------------------------
24 # Test cases 1.* seek to verify that even if an index is not used, its
25 # stat4 data may be used by the planner to estimate the number of
26 # rows that match an unindexed constraint on the same column.
27 #
28 do_execsql_test 1.0 {
29 PRAGMA automatic_index = 0;
30 CREATE TABLE t1(a, x);
31 CREATE TABLE t2(b, y);
32 WITH s(i) AS (
33 SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<100
34 )
35 INSERT INTO t1 SELECT (i%50), NULL FROM s;
36 WITH s(i) AS (
37 SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<100
38 )
39 INSERT INTO t2 SELECT (CASE WHEN i<95 THEN 44 ELSE i END), NULL FROM s;
40 }
41
42 # Join tables t1 and t2. Both contain 100 rows. (a=44) matches 2 rows
43 # in "t1", (b=44) matches 95 rows in table "t2". But the planner doesn't
44 # know this, so it has no preference as to which order the tables are
45 # scanned in. In practice this means that tables are scanned in the order
46 # they are specified in in the FROM clause.
47 do_eqp_test 1.1.1 {
48 SELECT * FROM t1, t2 WHERE a=44 AND b=44;
49 } {
50
51 }
52 do_eqp_test 1.1.2 {
53 SELECT * FROM t2, t1 WHERE a=44 AND b=44
54 } {
55 QUERY PLAN
56 |--SCAN t2
57 `--SCAN t1
58 }
59
60 do_execsql_test 1.2 {
61 CREATE INDEX t2b ON t2(b);
62 ANALYZE;
63 }
64
65 # Now, with the ANALYZE data, the planner knows that (b=44) matches a
66 # large number of rows. So it elects to scan table "t1" first, regardless
67 # of the order in which the tables are specified in the FROM clause.
68 do_eqp_test 1.3.1 {
69 SELECT * FROM t1, t2 WHERE a=44 AND b=44;
70 } {
71 QUERY PLAN
72 |--SCAN t1
73 `--SCAN t2
74 }
75 do_eqp_test 1.3.2 {
76 SELECT * FROM t2, t1 WHERE a=44 AND b=44
77 } {
78 QUERY PLAN
79 |--SCAN t1
80 `--SCAN t2
81 }
82
83
84 finish_test