third_party/sqlite/src/test/autoindex1.test - cobalt - Git at Google

 # 2010 April 07
 #
 # The author disclaims copyright to this source code.  In place of
 # a legal notice, here is a blessing:
 #
 #    May you do good and not evil.
 #    May you find forgiveness for yourself and forgive others.
 #    May you share freely, never taking more than you give.
 #
 #*************************************************************************
 # This file implements regression tests for SQLite library.  The
 # focus of this script is testing automatic index creation logic.
 #

 set testdir [file dirname $argv0]
 source $testdir/tester.tcl

 # If the library is not compiled with automatic index support then
 # skip all tests in this file.
 #
 ifcapable {!autoindex} {
   finish_test
   return
 }

 # With automatic index turned off, we do a full scan of the T2 table
 do_test autoindex1-100 {
   db eval {
     CREATE TABLE t1(a,b);
     INSERT INTO t1 VALUES(1,11);
     INSERT INTO t1 VALUES(2,22);
     INSERT INTO t1 SELECT a+2, b+22 FROM t1;
     INSERT INTO t1 SELECT a+4, b+44 FROM t1;
     CREATE TABLE t2(c,d);
     INSERT INTO t2 SELECT a, 900+b FROM t1;
   }
   db eval {
     PRAGMA automatic_index=OFF;
     SELECT b, d FROM t1 JOIN t2 ON a=c ORDER BY b;
   }
 } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
 do_test autoindex1-101 {
   db status step
 } {63}
 do_test autoindex1-102 {
   db status autoindex
 } {0}

 # With autoindex turned on, we build an index once and then use that index
 # to find T2 values.
 do_test autoindex1-110 {
   db eval {
     PRAGMA automatic_index=ON;
     SELECT b, d FROM t1 JOIN t2 ON a=c ORDER BY b;
   }
 } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
 do_test autoindex1-111 {
   db status step
 } {7}
 do_test autoindex1-112 {
   db status autoindex
 } {7}

 # The same test as above, but this time the T2 query is a subquery rather
 # than a join.
 do_test autoindex1-200 {
   db eval {
     PRAGMA automatic_index=OFF;
     SELECT b, (SELECT d FROM t2 WHERE c=a) FROM t1;
   }
 } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
 do_test autoindex1-201 {
   db status step
 } {35}
 do_test autoindex1-202 {
   db status autoindex
 } {0}
 do_test autoindex1-210 {
   db eval {
     PRAGMA automatic_index=ON;
     SELECT b, (SELECT d FROM t2 WHERE c=a) FROM t1;
   }
 } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
 do_test autoindex1-211 {
   db status step
 } {7}
 do_test autoindex1-212 {
   db status autoindex
 } {7}


 # Modify the second table of the join while the join is in progress
 #
 do_test autoindex1-300 {
   set r {}
   db eval {SELECT b, d FROM t1 JOIN t2 ON (c=a)} {
     lappend r $b $d
     db eval {UPDATE t2 SET d=d+1}
   }
   set r
 } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
 do_test autoindex1-310 {
   db eval {SELECT d FROM t2 ORDER BY d}
 } {919 930 941 952 963 974 985 996}

 # The next test does a 10-way join on unindexed tables.  Without
 # automatic indices, the join will take a long time to complete.
 # With automatic indices, it should only take about a second.
 #
 do_test autoindex1-400 {
   db eval {
     CREATE TABLE t4(a, b);
     INSERT INTO t4 VALUES(1,2);
     INSERT INTO t4 VALUES(2,3);
   }
   for {set n 2} {$n<4096} {set n [expr {$n+$n}]} {
     db eval {INSERT INTO t4 SELECT a+$n, b+$n FROM t4}
   }
   db eval {
     SELECT count(*) FROM t4;
   }
 } {4096}
 do_test autoindex1-401 {
   db eval {
     SELECT count(*)
       FROM t4 AS x1
       JOIN t4 AS x2 ON x2.a=x1.b
       JOIN t4 AS x3 ON x3.a=x2.b
       JOIN t4 AS x4 ON x4.a=x3.b
       JOIN t4 AS x5 ON x5.a=x4.b
       JOIN t4 AS x6 ON x6.a=x5.b
       JOIN t4 AS x7 ON x7.a=x6.b
       JOIN t4 AS x8 ON x8.a=x7.b
       JOIN t4 AS x9 ON x9.a=x8.b
       JOIN t4 AS x10 ON x10.a=x9.b;
   }
 } {4087}

 # Ticket [8011086c85c6c404014c947fcf3eb9f42b184a0d] from 2010-07-08
 # Make sure automatic indices are not created for the RHS of an IN expression
 # that is not a correlated subquery.
 #
 do_execsql_test autoindex1-500 {
   CREATE TABLE t501(a INTEGER PRIMARY KEY, b);
   CREATE TABLE t502(x INTEGER PRIMARY KEY, y);
   EXPLAIN QUERY PLAN
   SELECT b FROM t501
    WHERE t501.a IN (SELECT x FROM t502 WHERE y=?);
 } {
   0 0 0 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?) (~25 rows)}
   0 0 0 {EXECUTE LIST SUBQUERY 1}
   1 0 0 {SCAN TABLE t502 (~100000 rows)}
 }
 do_execsql_test autoindex1-501 {
   EXPLAIN QUERY PLAN
   SELECT b FROM t501
    WHERE t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
 } {
   0 0 0 {SCAN TABLE t501 (~500000 rows)}
   0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1}
   1 0 0 {SEARCH TABLE t502 USING AUTOMATIC COVERING INDEX (y=?) (~7 rows)}
 }
 do_execsql_test autoindex1-502 {
   EXPLAIN QUERY PLAN
   SELECT b FROM t501
    WHERE t501.a=123
      AND t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
 } {
   0 0 0 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
   0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1}
   1 0 0 {SCAN TABLE t502 (~100000 rows)}
 }


 # The following code checks a performance regression reported on the
 # mailing list on 2010-10-19.  The problem is that the nRowEst field
 # of ephermeral tables was not being initialized correctly and so no
 # automatic index was being created for the emphemeral table when it was
 # used as part of a join.
 #
 do_execsql_test autoindex1-600 {
   CREATE TABLE flock_owner(
     owner_rec_id INTEGER CONSTRAINT flock_owner_key PRIMARY KEY,
     flock_no VARCHAR(6) NOT NULL REFERENCES flock (flock_no),
     owner_person_id INTEGER NOT NULL REFERENCES person (person_id),
     owner_change_date TEXT, last_changed TEXT NOT NULL,
     CONSTRAINT fo_owner_date UNIQUE (flock_no, owner_change_date)
   );
   CREATE TABLE sheep (
     Sheep_No char(7) NOT NULL,
     Date_of_Birth char(8),
     Sort_DoB text,
     Flock_Book_Vol char(2),
     Breeder_No char(6),
     Breeder_Person integer,
     Originating_Flock char(6),
     Registering_Flock char(6),
     Tag_Prefix char(9),
     Tag_No char(15),
     Sort_Tag_No integer,
     Breeders_Temp_Tag char(15),
     Sex char(1),
     Sheep_Name char(32),
     Sire_No char(7),
     Dam_No char(7),
     Register_Code char(1),
     Colour char(48),
     Colour_Code char(2),
     Pattern_Code char(8),
     Horns char(1),
     Litter_Size char(1),
     Coeff_of_Inbreeding real,
     Date_of_Registration text,
     Date_Last_Changed text,
     UNIQUE(Sheep_No));
   CREATE INDEX fo_flock_no_index
               ON flock_owner (flock_no);
   CREATE INDEX fo_owner_change_date_index
               ON flock_owner (owner_change_date);
   CREATE INDEX fo_owner_person_id_index
               ON flock_owner (owner_person_id);
   CREATE INDEX sheep_org_flock_index
            ON sheep (originating_flock);
   CREATE INDEX sheep_reg_flock_index
            ON sheep (registering_flock);
   EXPLAIN QUERY PLAN
   SELECT x.sheep_no, x.registering_flock, x.date_of_registration
    FROM sheep x LEFT JOIN
        (SELECT s.sheep_no, prev.flock_no, prev.owner_person_id,
        s.date_of_registration, prev.owner_change_date
        FROM sheep s JOIN flock_owner prev ON s.registering_flock =
    prev.flock_no
        AND (prev.owner_change_date <= s.date_of_registration || ' 00:00:00')
        WHERE NOT EXISTS
            (SELECT 'x' FROM flock_owner later
            WHERE prev.flock_no = later.flock_no
            AND later.owner_change_date > prev.owner_change_date
            AND later.owner_change_date <= s.date_of_registration||' 00:00:00')
        ) y ON x.sheep_no = y.sheep_no
    WHERE y.sheep_no IS NULL
    ORDER BY x.registering_flock;
 } {
   1 0 0 {SCAN TABLE sheep AS s (~1000000 rows)}
   1 1 1 {SEARCH TABLE flock_owner AS prev USING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date<?) (~2 rows)}
   1 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2}
   2 0 0 {SEARCH TABLE flock_owner AS later USING COVERING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date>? AND owner_change_date<?) (~1 rows)}
   0 0 0 {SCAN TABLE sheep AS x USING INDEX sheep_reg_flock_index (~1000000 rows)}
   0 1 1 {SEARCH SUBQUERY 1 AS y USING AUTOMATIC COVERING INDEX (sheep_no=?) (~8 rows)}
 }

 finish_test
	# 2010 April 07
	#
	# The author disclaims copyright to this source code. In place of
	# a legal notice, here is a blessing:
	#
	# May you do good and not evil.
	# May you find forgiveness for yourself and forgive others.
	# May you share freely, never taking more than you give.
	#
	#*************************************************************************
	# This file implements regression tests for SQLite library. The
	# focus of this script is testing automatic index creation logic.
	#

	set testdir [file dirname $argv0]
	source $testdir/tester.tcl

	# If the library is not compiled with automatic index support then
	# skip all tests in this file.
	#
	ifcapable {!autoindex} {
	finish_test
	return
	}

	# With automatic index turned off, we do a full scan of the T2 table
	do_test autoindex1-100 {
	db eval {
	CREATE TABLE t1(a,b);
	INSERT INTO t1 VALUES(1,11);
	INSERT INTO t1 VALUES(2,22);
	INSERT INTO t1 SELECT a+2, b+22 FROM t1;
	INSERT INTO t1 SELECT a+4, b+44 FROM t1;
	CREATE TABLE t2(c,d);
	INSERT INTO t2 SELECT a, 900+b FROM t1;
	}
	db eval {
	PRAGMA automatic_index=OFF;
	SELECT b, d FROM t1 JOIN t2 ON a=c ORDER BY b;
	}
	} {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
	do_test autoindex1-101 {
	db status step
	} {63}
	do_test autoindex1-102 {
	db status autoindex
	} {0}

	# With autoindex turned on, we build an index once and then use that index
	# to find T2 values.
	do_test autoindex1-110 {
	db eval {
	PRAGMA automatic_index=ON;
	SELECT b, d FROM t1 JOIN t2 ON a=c ORDER BY b;
	}
	} {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
	do_test autoindex1-111 {
	db status step
	} {7}
	do_test autoindex1-112 {
	db status autoindex
	} {7}

	# The same test as above, but this time the T2 query is a subquery rather
	# than a join.
	do_test autoindex1-200 {
	db eval {
	PRAGMA automatic_index=OFF;
	SELECT b, (SELECT d FROM t2 WHERE c=a) FROM t1;
	}
	} {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
	do_test autoindex1-201 {
	db status step
	} {35}
	do_test autoindex1-202 {
	db status autoindex
	} {0}
	do_test autoindex1-210 {
	db eval {
	PRAGMA automatic_index=ON;
	SELECT b, (SELECT d FROM t2 WHERE c=a) FROM t1;
	}
	} {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
	do_test autoindex1-211 {
	db status step
	} {7}
	do_test autoindex1-212 {
	db status autoindex
	} {7}


	# Modify the second table of the join while the join is in progress
	#
	do_test autoindex1-300 {
	set r {}
	db eval {SELECT b, d FROM t1 JOIN t2 ON (c=a)} {
	lappend r $b $d
	db eval {UPDATE t2 SET d=d+1}
	}
	set r
	} {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
	do_test autoindex1-310 {
	db eval {SELECT d FROM t2 ORDER BY d}
	} {919 930 941 952 963 974 985 996}

	# The next test does a 10-way join on unindexed tables. Without
	# automatic indices, the join will take a long time to complete.
	# With automatic indices, it should only take about a second.
	#
	do_test autoindex1-400 {
	db eval {
	CREATE TABLE t4(a, b);
	INSERT INTO t4 VALUES(1,2);
	INSERT INTO t4 VALUES(2,3);
	}
	for {set n 2} {$n<4096} {set n [expr {$n+$n}]} {
	db eval {INSERT INTO t4 SELECT a+$n, b+$n FROM t4}
	}
	db eval {
	SELECT count(*) FROM t4;
	}
	} {4096}
	do_test autoindex1-401 {
	db eval {
	SELECT count(*)
	FROM t4 AS x1
	JOIN t4 AS x2 ON x2.a=x1.b
	JOIN t4 AS x3 ON x3.a=x2.b
	JOIN t4 AS x4 ON x4.a=x3.b
	JOIN t4 AS x5 ON x5.a=x4.b
	JOIN t4 AS x6 ON x6.a=x5.b
	JOIN t4 AS x7 ON x7.a=x6.b
	JOIN t4 AS x8 ON x8.a=x7.b
	JOIN t4 AS x9 ON x9.a=x8.b
	JOIN t4 AS x10 ON x10.a=x9.b;
	}
	} {4087}

	# Ticket [8011086c85c6c404014c947fcf3eb9f42b184a0d] from 2010-07-08
	# Make sure automatic indices are not created for the RHS of an IN expression
	# that is not a correlated subquery.
	#
	do_execsql_test autoindex1-500 {
	CREATE TABLE t501(a INTEGER PRIMARY KEY, b);
	CREATE TABLE t502(x INTEGER PRIMARY KEY, y);
	EXPLAIN QUERY PLAN
	SELECT b FROM t501
	WHERE t501.a IN (SELECT x FROM t502 WHERE y=?);
	} {
	0 0 0 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?) (~25 rows)}
	0 0 0 {EXECUTE LIST SUBQUERY 1}
	1 0 0 {SCAN TABLE t502 (~100000 rows)}
	}
	do_execsql_test autoindex1-501 {
	EXPLAIN QUERY PLAN
	SELECT b FROM t501
	WHERE t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
	} {
	0 0 0 {SCAN TABLE t501 (~500000 rows)}
	0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1}
	1 0 0 {SEARCH TABLE t502 USING AUTOMATIC COVERING INDEX (y=?) (~7 rows)}
	}
	do_execsql_test autoindex1-502 {
	EXPLAIN QUERY PLAN
	SELECT b FROM t501
	WHERE t501.a=123
	AND t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
	} {
	0 0 0 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
	0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1}
	1 0 0 {SCAN TABLE t502 (~100000 rows)}
	}


	# The following code checks a performance regression reported on the
	# mailing list on 2010-10-19. The problem is that the nRowEst field
	# of ephermeral tables was not being initialized correctly and so no
	# automatic index was being created for the emphemeral table when it was
	# used as part of a join.
	#
	do_execsql_test autoindex1-600 {
	CREATE TABLE flock_owner(
	owner_rec_id INTEGER CONSTRAINT flock_owner_key PRIMARY KEY,
	flock_no VARCHAR(6) NOT NULL REFERENCES flock (flock_no),
	owner_person_id INTEGER NOT NULL REFERENCES person (person_id),
	owner_change_date TEXT, last_changed TEXT NOT NULL,
	CONSTRAINT fo_owner_date UNIQUE (flock_no, owner_change_date)
	);
	CREATE TABLE sheep (
	Sheep_No char(7) NOT NULL,
	Date_of_Birth char(8),
	Sort_DoB text,
	Flock_Book_Vol char(2),
	Breeder_No char(6),
	Breeder_Person integer,
	Originating_Flock char(6),
	Registering_Flock char(6),
	Tag_Prefix char(9),
	Tag_No char(15),
	Sort_Tag_No integer,
	Breeders_Temp_Tag char(15),
	Sex char(1),
	Sheep_Name char(32),
	Sire_No char(7),
	Dam_No char(7),
	Register_Code char(1),
	Colour char(48),
	Colour_Code char(2),
	Pattern_Code char(8),
	Horns char(1),
	Litter_Size char(1),
	Coeff_of_Inbreeding real,
	Date_of_Registration text,
	Date_Last_Changed text,
	UNIQUE(Sheep_No));
	CREATE INDEX fo_flock_no_index
	ON flock_owner (flock_no);
	CREATE INDEX fo_owner_change_date_index
	ON flock_owner (owner_change_date);
	CREATE INDEX fo_owner_person_id_index
	ON flock_owner (owner_person_id);
	CREATE INDEX sheep_org_flock_index
	ON sheep (originating_flock);
	CREATE INDEX sheep_reg_flock_index
	ON sheep (registering_flock);
	EXPLAIN QUERY PLAN
	SELECT x.sheep_no, x.registering_flock, x.date_of_registration
	FROM sheep x LEFT JOIN
	(SELECT s.sheep_no, prev.flock_no, prev.owner_person_id,
	s.date_of_registration, prev.owner_change_date
	FROM sheep s JOIN flock_owner prev ON s.registering_flock =
	prev.flock_no
	AND (prev.owner_change_date <= s.date_of_registration \|\| ' 00:00:00')
	WHERE NOT EXISTS
	(SELECT 'x' FROM flock_owner later
	WHERE prev.flock_no = later.flock_no
	AND later.owner_change_date > prev.owner_change_date
	AND later.owner_change_date <= s.date_of_registration\|\|' 00:00:00')
	) y ON x.sheep_no = y.sheep_no
	WHERE y.sheep_no IS NULL
	ORDER BY x.registering_flock;
	} {
	1 0 0 {SCAN TABLE sheep AS s (~1000000 rows)}
	1 1 1 {SEARCH TABLE flock_owner AS prev USING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date<?) (~2 rows)}
	1 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2}
	2 0 0 {SEARCH TABLE flock_owner AS later USING COVERING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date>? AND owner_change_date<?) (~1 rows)}
	0 0 0 {SCAN TABLE sheep AS x USING INDEX sheep_reg_flock_index (~1000000 rows)}
	0 1 1 {SEARCH SUBQUERY 1 AS y USING AUTOMATIC COVERING INDEX (sheep_no=?) (~8 rows)}
	}

	finish_test