drh
badc42fd74
the config=prepare permutation. FossilOrigin-Name: 6aaeeffc2c7abbe32050da40bcbffc5f8d596e6df4603b8f8f23294f2465f25e
84 lines
1.9 KiB
Plaintext
84 lines
1.9 KiB
Plaintext
# 2024 March 20
|
|
#
|
|
# 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.
|
|
#
|
|
#***********************************************************************
|
|
#
|
|
|
|
set testdir [file dirname $argv0]
|
|
source $testdir/tester.tcl
|
|
|
|
set testprefix eqp2
|
|
|
|
do_execsql_test 1.0 {
|
|
CREATE TABLE t1(a, b, c, d);
|
|
CREATE INDEX i1 ON t1(a, b, c);
|
|
}
|
|
|
|
do_eqp_test 1.1 {
|
|
SELECT * FROM t1 ORDER BY a, b, c
|
|
} {
|
|
QUERY PLAN
|
|
`--SCAN t1 USING INDEX i1
|
|
}
|
|
|
|
|
|
do_eqp_test 1.2 {
|
|
SELECT * FROM t1 ORDER BY a, b, +c
|
|
} {
|
|
QUERY PLAN
|
|
|--SCAN t1 USING INDEX i1
|
|
`--USE TEMP B-TREE FOR LAST TERM OF ORDER BY
|
|
}
|
|
|
|
do_eqp_test 1.3 {
|
|
SELECT * FROM t1 ORDER BY a, +b, +c
|
|
} {
|
|
QUERY PLAN
|
|
|--SCAN t1 USING INDEX i1
|
|
`--USE TEMP B-TREE FOR LAST 2 TERMS OF ORDER BY
|
|
}
|
|
|
|
#-------------------------------------------------------------------------
|
|
# Test that a bound value of 0 is handled in the same way as a literal
|
|
# 0 when estimating the probability of (x=<some-integer>).
|
|
#
|
|
reset_db
|
|
do_execsql_test 2.0 {
|
|
CREATE TABLE t1(a INT, b INT);
|
|
CREATE TABLE t2(x INT, y INT);
|
|
CREATE INDEX t1_a ON t1(a);
|
|
CREATE INDEX t1_b ON t1(b);
|
|
CREATE INDEX t2_x ON t2(x);
|
|
|
|
WITH s(i) AS ( SELECT 0 UNION ALL SELECT i+1 FROM s WHERE i<999)
|
|
INSERT INTO t1 SELECT i%33, i FROM s;
|
|
|
|
WITH s(i) AS ( SELECT 0 UNION ALL SELECT i+1 FROM s WHERE i<59)
|
|
INSERT INTO t2 SELECT i, i%3 FROM s;
|
|
|
|
ANALYZE;
|
|
}
|
|
|
|
set zero [expr 0]
|
|
|
|
if {[permutation] != "prepare"} {
|
|
do_eqp_test 2.1 {
|
|
SELECT * FROM t1 JOIN t2 ON t1.b = t2.x
|
|
WHERE t1.a = 5 AND t2.y = $zero
|
|
} {SEARCH t1 USING INDEX t1_a (a=?)*SEARCH t2 USING INDEX t2_x (x=?)}
|
|
}
|
|
|
|
do_eqp_test 2.2 {
|
|
SELECT * FROM t1 JOIN t2 ON t1.b = t2.x
|
|
WHERE t1.a = 5 AND t2.y = 0
|
|
} {SEARCH t1 USING INDEX t1_a (a=?)*SEARCH t2 USING INDEX t2_x (x=?)}
|
|
|
|
|
|
finish_test
|