AryaWu/sqlite

1# 2020-02-232#3# The author disclaims copyright to this source code.  In place of4# 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.tcl16 17ifcapable !stat4 {18  finish_test19  return20}21set testprefix analyzeG22 23#-------------------------------------------------------------------------24# Test cases 1.* seek to verify that even if an index is not used, its25# stat4 data may be used by the planner to estimate the number of26# rows that match an unindexed constraint on the same column.27#28do_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<10034  )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<10038  )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 rows43# in "t1", (b=44) matches 95 rows in table "t2". But the planner doesn't44# know this, so it has no preference as to which order the tables are45# scanned in. In practice this means that tables are scanned in the order46# they are specified in in the FROM clause.47do_eqp_test 1.1.1 {48  SELECT * FROM t1, t2 WHERE a=44 AND b=44;49} {50 51}52do_eqp_test 1.1.2 {53  SELECT * FROM t2, t1 WHERE a=44 AND b=44 54} {55  QUERY PLAN56  |--SCAN t257  `--SCAN t158}59 60do_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, regardless67# of the order in which the tables are specified in the FROM clause.68do_eqp_test 1.3.1 {69  SELECT * FROM t1, t2 WHERE a=44 AND b=44;70} {71  QUERY PLAN72  |--SCAN t173  `--SCAN t274}75do_eqp_test 1.3.2 {76  SELECT * FROM t2, t1 WHERE a=44 AND b=44 77} {78  QUERY PLAN79  |--SCAN t180  `--SCAN t281}82 83 84finish_test85