0000: 23 20 32 30 31 32 20 4e 6f 76 65 6d 62 65 72 20 # 2012 November
0010: 39 0a 23 0a 23 20 54 68 65 20 61 75 74 68 6f 72 9.#.# The author
0020: 20 64 69 73 63 6c 61 69 6d 73 20 63 6f 70 79 72 disclaims copyr
0030: 69 67 68 74 20 74 6f 20 74 68 69 73 20 73 6f 75 ight to this sou
0040: 72 63 65 20 63 6f 64 65 2e 20 20 49 6e 20 70 6c rce code. In pl
0050: 61 63 65 20 6f 66 0a 23 20 61 20 6c 65 67 61 6c ace of.# a legal
0060: 20 6e 6f 74 69 63 65 2c 20 68 65 72 65 20 69 73 notice, here is
0070: 20 61 20 62 6c 65 73 73 69 6e 67 3a 0a 23 0a 23 a blessing:.#.#
0080: 20 20 20 20 4d 61 79 20 79 6f 75 20 64 6f 20 67 May you do g
0090: 6f 6f 64 20 61 6e 64 20 6e 6f 74 20 65 76 69 6c ood and not evil
00a0: 2e 0a 23 20 20 20 20 4d 61 79 20 79 6f 75 20 66 ..# May you f
00b0: 69 6e 64 20 66 6f 72 67 69 76 65 6e 65 73 73 20 ind forgiveness
00c0: 66 6f 72 20 79 6f 75 72 73 65 6c 66 20 61 6e 64 for yourself and
00d0: 20 66 6f 72 67 69 76 65 20 6f 74 68 65 72 73 2e forgive others.
00e0: 0a 23 20 20 20 20 4d 61 79 20 79 6f 75 20 73 68 .# May you sh
00f0: 61 72 65 20 66 72 65 65 6c 79 2c 20 6e 65 76 65 are freely, neve
0100: 72 20 74 61 6b 69 6e 67 20 6d 6f 72 65 20 74 68 r taking more th
0110: 61 6e 20 79 6f 75 20 67 69 76 65 2e 0a 23 0a 23 an you give..#.#
0120: 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a ****************
0130: 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a ****************
0140: 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a ****************
0150: 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a ****************
0160: 2a 2a 2a 2a 2a 2a 2a 0a 23 20 0a 23 20 54 65 73 *******.# .# Tes
0170: 74 20 63 61 73 65 73 20 66 6f 72 20 71 75 65 72 t cases for quer
0180: 79 20 70 6c 61 6e 6e 69 6e 67 20 64 65 63 69 73 y planning decis
0190: 69 6f 6e 73 2e 0a 0a 0a 23 0a 23 20 54 68 65 20 ions....#.# The
01a0: 74 65 73 74 73 20 69 6e 20 74 68 69 73 20 66 69 tests in this fi
01b0: 6c 65 20 64 65 6d 6f 6e 73 74 72 61 74 65 20 74 le demonstrate t
01c0: 68 65 20 62 65 68 61 76 69 6f 75 72 20 6f 66 20 he behaviour of
01d0: 74 68 65 20 71 75 65 72 79 20 70 6c 61 6e 6e 65 the query planne
01e0: 72 0a 23 20 69 6e 20 64 65 74 65 72 6d 69 6e 69 r.# in determini
01f0: 6e 67 20 74 68 65 20 6f 72 64 65 72 20 69 6e 20 ng the order in
0200: 77 68 69 63 68 20 6a 6f 69 6e 65 64 20 74 61 62 which joined tab
0210: 6c 65 73 20 61 72 65 20 73 63 61 6e 6e 65 64 2e les are scanned.
0220: 0a 23 0a 23 20 41 73 73 75 6d 65 20 74 68 65 72 .#.# Assume ther
0230: 65 20 61 72 65 20 74 77 6f 20 74 61 62 6c 65 73 e are two tables
0240: 20 62 65 69 6e 67 20 6a 6f 69 6e 65 64 20 2d 20 being joined -
0250: 74 31 20 61 6e 64 20 74 32 2e 20 45 61 63 68 20 t1 and t2. Each
0260: 68 61 73 20 61 20 63 6f 73 74 0a 23 20 69 66 20 has a cost.# if
0270: 69 74 20 69 73 20 74 68 65 20 6f 75 74 65 72 20 it is the outer
0280: 6c 6f 6f 70 2c 20 61 6e 64 20 61 20 63 6f 73 74 loop, and a cost
0290: 20 69 66 20 69 74 20 69 73 20 74 68 65 20 69 6e if it is the in
02a0: 6e 65 72 20 6c 6f 6f 70 2e 20 41 73 20 66 6f 6c ner loop. As fol
02b0: 6c 6f 77 73 3a 0a 23 0a 23 20 20 20 74 31 28 6f lows:.#.# t1(o
02c0: 75 74 65 72 29 20 2d 20 63 6f 73 74 20 6f 66 20 uter) - cost of
02d0: 73 63 61 6e 6e 69 6e 67 20 74 31 20 61 73 20 74 scanning t1 as t
02e0: 68 65 20 6f 75 74 65 72 20 6c 6f 6f 70 2e 0a 23 he outer loop..#
02f0: 20 20 20 74 31 28 69 6e 6e 65 72 29 20 2d 20 63 t1(inner) - c
0300: 6f 73 74 20 6f 66 20 73 63 61 6e 6e 69 6e 67 20 ost of scanning
0310: 74 31 20 61 73 20 74 68 65 20 69 6e 6e 65 72 20 t1 as the inner
0320: 6c 6f 6f 70 2e 0a 23 20 20 20 74 32 28 6f 75 74 loop..# t2(out
0330: 65 72 29 20 2d 20 63 6f 73 74 20 6f 66 20 73 63 er) - cost of sc
0340: 61 6e 6e 69 6e 67 20 74 32 20 61 73 20 74 68 65 anning t2 as the
0350: 20 6f 75 74 65 72 20 6c 6f 6f 70 2e 0a 23 20 20 outer loop..#
0360: 20 74 32 28 69 6e 6e 65 72 29 20 2d 20 63 6f 73 t2(inner) - cos
0370: 74 20 6f 66 20 73 63 61 6e 6e 69 6e 67 20 74 32 t of scanning t2
0380: 20 61 73 20 74 68 65 20 69 6e 6e 65 72 20 6c 6f as the inner lo
0390: 6f 70 2e 0a 23 0a 23 20 44 65 70 65 6e 64 69 6e op..#.# Dependin
03a0: 67 20 6f 6e 20 74 68 65 20 6f 72 64 65 72 20 69 g on the order i
03b0: 6e 20 77 68 69 63 68 20 74 68 65 20 70 6c 61 6e n which the plan
03c0: 6e 65 72 20 6e 65 73 74 73 20 74 68 65 20 73 63 ner nests the sc
03d0: 61 6e 73 2c 20 74 68 65 20 74 6f 74 61 6c 0a 23 ans, the total.#
03e0: 20 63 6f 73 74 20 6f 66 20 74 68 65 20 6a 6f 69 cost of the joi
03f0: 6e 20 71 75 65 72 79 20 69 73 20 6f 6e 65 20 6f n query is one o
0400: 66 3a 0a 23 0a 23 20 20 20 74 31 28 6f 75 74 65 f:.#.# t1(oute
0410: 72 29 20 2a 20 74 32 28 69 6e 6e 65 72 29 0a 23 r) * t2(inner).#
0420: 20 20 20 74 32 28 6f 75 74 65 72 29 20 2a 20 74 t2(outer) * t
0430: 31 28 69 6e 6e 65 72 29 0a 23 0a 23 20 54 68 65 1(inner).#.# The
0440: 20 74 65 73 74 73 20 69 6e 20 74 68 69 73 20 66 tests in this f
0450: 69 6c 65 20 61 74 74 65 6d 70 74 20 74 6f 20 76 ile attempt to v
0460: 65 72 69 66 79 20 74 68 61 74 20 74 68 65 20 70 erify that the p
0470: 6c 61 6e 6e 65 72 20 6e 65 73 74 73 20 6a 6f 69 lanner nests joi
0480: 6e 73 20 69 6e 0a 23 20 74 68 65 20 63 6f 72 72 ns in.# the corr
0490: 65 63 74 20 6f 72 64 65 72 20 77 68 65 6e 20 74 ect order when t
04a0: 68 65 20 66 6f 6c 6c 6f 77 69 6e 67 20 61 72 65 he following are
04b0: 20 74 72 75 65 3a 0a 23 0a 23 20 20 20 2b 20 28 true:.#.# + (
04c0: 74 31 28 6f 75 74 65 72 29 20 2a 20 74 32 28 69 t1(outer) * t2(i
04d0: 6e 6e 65 72 29 29 20 3e 20 28 74 31 28 69 6e 6e nner)) > (t1(inn
04e0: 65 72 29 20 2a 20 74 32 28 6f 75 74 65 72 29 0a er) * t2(outer).
04f0: 23 20 20 20 2b 20 20 74 31 28 6f 75 74 65 72 29 # + t1(outer)
0500: 20 3c 20 74 32 28 6f 75 74 65 72 29 0a 23 0a 23 < t2(outer).#.#
0510: 20 49 6e 20 6f 74 68 65 72 20 77 6f 72 64 73 2c In other words,
0520: 20 77 68 65 6e 20 74 68 65 20 62 65 73 74 20 6f when the best o
0530: 76 65 72 61 6c 6c 20 71 75 65 72 79 20 70 6c 61 verall query pla
0540: 6e 20 68 61 73 20 74 32 20 61 73 20 74 68 65 20 n has t2 as the
0550: 6f 75 74 65 72 20 6c 6f 6f 70 2c 0a 23 20 62 75 outer loop,.# bu
0560: 74 20 77 68 65 6e 20 74 68 65 20 6f 75 74 65 72 t when the outer
0570: 20 6c 6f 6f 70 20 69 73 20 63 6f 6e 73 69 64 65 loop is conside
0580: 72 65 64 20 69 6e 64 65 70 65 6e 64 65 6e 74 20 red independent
0590: 6f 66 20 74 68 65 20 69 6e 6e 65 72 2c 20 74 31 of the inner, t1
05a0: 20 69 73 20 74 68 65 0a 23 20 6d 6f 73 74 20 65 is the.# most e
05b0: 66 66 69 63 69 65 6e 74 20 63 68 6f 69 63 65 2e fficient choice.
05c0: 0a 23 0a 23 20 49 6e 20 6f 72 64 65 72 20 74 6f .#.# In order to
05d0: 20 6d 61 6b 65 20 74 68 65 6d 20 6d 6f 72 65 20 make them more
05e0: 70 72 65 64 69 63 74 61 62 6c 65 2c 20 61 75 74 predictable, aut
05f0: 6f 6d 61 74 69 63 20 69 6e 64 65 78 65 73 20 61 omatic indexes a
0600: 72 65 20 74 75 72 6e 65 64 20 6f 66 66 20 66 6f re turned off fo
0610: 72 0a 23 20 74 68 65 20 74 65 73 74 73 20 69 6e r.# the tests in
0620: 20 74 68 69 73 20 66 69 6c 65 2e 0a 23 0a 0a 73 this file..#..s
0630: 65 74 20 74 65 73 74 64 69 72 20 5b 66 69 6c 65 et testdir [file
0640: 20 64 69 72 6e 61 6d 65 20 24 61 72 67 76 30 5d dirname $argv0]
0650: 0a 73 6f 75 72 63 65 20 24 74 65 73 74 64 69 72 .source $testdir
0660: 2f 74 65 73 74 65 72 2e 74 63 6c 0a 73 65 74 20 /tester.tcl.set
0670: 74 65 73 74 70 72 65 66 69 78 20 77 68 65 72 65 testprefix where
0680: 46 0a 0a 64 6f 5f 65 78 65 63 73 71 6c 5f 74 65 F..do_execsql_te
0690: 73 74 20 31 2e 30 20 7b 0a 20 20 50 52 41 47 4d st 1.0 {. PRAGM
06a0: 41 20 61 75 74 6f 6d 61 74 69 63 5f 69 6e 64 65 A automatic_inde
06b0: 78 20 3d 20 30 3b 0a 20 20 43 52 45 41 54 45 20 x = 0;. CREATE
06c0: 54 41 42 4c 45 20 74 31 28 61 2c 20 62 2c 20 63 TABLE t1(a, b, c
06d0: 29 3b 0a 20 20 43 52 45 41 54 45 20 54 41 42 4c );. CREATE TABL
06e0: 45 20 74 32 28 64 2c 20 65 2c 20 66 29 3b 0a 20 E t2(d, e, f);.
06f0: 20 43 52 45 41 54 45 20 55 4e 49 51 55 45 20 49 CREATE UNIQUE I
0700: 4e 44 45 58 20 69 31 20 4f 4e 20 74 31 28 61 29 NDEX i1 ON t1(a)
0710: 3b 0a 20 20 43 52 45 41 54 45 20 55 4e 49 51 55 ;. CREATE UNIQU
0720: 45 20 49 4e 44 45 58 20 69 32 20 4f 4e 20 74 32 E INDEX i2 ON t2
0730: 28 64 29 3b 0a 7d 20 7b 7d 0a 0a 66 6f 72 65 61 (d);.} {}..forea
0740: 63 68 20 7b 74 6e 20 73 71 6c 7d 20 7b 0a 20 20 ch {tn sql} {.
0750: 31 20 22 53 45 4c 45 43 54 20 2a 20 46 52 4f 4d 1 "SELECT * FROM
0760: 20 74 31 2c 20 20 20 20 20 20 20 20 20 20 20 74 t1, t
0770: 32 20 57 48 45 52 45 20 74 31 2e 61 3d 74 32 2e 2 WHERE t1.a=t2.
0780: 65 20 41 4e 44 20 74 32 2e 64 3c 74 31 2e 62 20 e AND t2.d<t1.b
0790: 41 4e 44 20 74 31 2e 63 21 3d 31 30 22 0a 20 20 AND t1.c!=10".
07a0: 32 20 22 53 45 4c 45 43 54 20 2a 20 46 52 4f 4d 2 "SELECT * FROM
07b0: 20 74 32 2c 20 20 20 20 20 20 20 20 20 20 20 74 t2, t
07c0: 31 20 57 48 45 52 45 20 74 31 2e 61 3d 74 32 2e 1 WHERE t1.a=t2.
07d0: 65 20 41 4e 44 20 74 32 2e 64 3c 74 31 2e 62 20 e AND t2.d<t1.b
07e0: 41 4e 44 20 74 31 2e 63 21 3d 31 30 22 0a 20 20 AND t1.c!=10".
07f0: 33 20 22 53 45 4c 45 43 54 20 2a 20 46 52 4f 4d 3 "SELECT * FROM
0800: 20 74 32 20 43 52 4f 53 53 20 4a 4f 49 4e 20 74 t2 CROSS JOIN t
0810: 31 20 57 48 45 52 45 20 74 31 2e 61 3d 74 32 2e 1 WHERE t1.a=t2.
0820: 65 20 41 4e 44 20 74 32 2e 64 3c 74 31 2e 62 20 e AND t2.d<t1.b
0830: 41 4e 44 20 74 31 2e 63 21 3d 31 30 22 0a 7d 20 AND t1.c!=10".}
0840: 7b 0a 20 20 64 6f 5f 74 65 73 74 20 31 2e 24 74 {. do_test 1.$t
0850: 6e 20 7b 0a 20 20 20 20 64 62 20 65 76 61 6c 20 n {. db eval
0860: 22 45 58 50 4c 41 49 4e 20 51 55 45 52 59 20 50 "EXPLAIN QUERY P
0870: 4c 41 4e 20 24 73 71 6c 22 0a 20 20 20 7d 20 7b LAN $sql". } {
0880: 2f 2e 2a 53 43 41 4e 20 54 41 42 4c 45 20 74 32 /.*SCAN TABLE t2
0890: 5c 79 2e 2a 53 45 41 52 43 48 20 54 41 42 4c 45 \y.*SEARCH TABLE
08a0: 20 74 31 5c 79 2e 2a 2f 7d 0a 7d 0a 0a 64 6f 5f t1\y.*/}.}..do_
08b0: 65 78 65 63 73 71 6c 5f 74 65 73 74 20 32 2e 30 execsql_test 2.0
08c0: 20 7b 0a 20 20 44 52 4f 50 20 54 41 42 4c 45 20 {. DROP TABLE
08d0: 74 31 3b 0a 20 20 44 52 4f 50 20 54 41 42 4c 45 t1;. DROP TABLE
08e0: 20 74 32 3b 0a 20 20 43 52 45 41 54 45 20 54 41 t2;. CREATE TA
08f0: 42 4c 45 20 74 31 28 61 2c 20 62 2c 20 63 29 3b BLE t1(a, b, c);
0900: 0a 20 20 43 52 45 41 54 45 20 54 41 42 4c 45 20 . CREATE TABLE
0910: 74 32 28 64 2c 20 65 2c 20 66 29 3b 0a 0a 20 20 t2(d, e, f);..
0920: 43 52 45 41 54 45 20 55 4e 49 51 55 45 20 49 4e CREATE UNIQUE IN
0930: 44 45 58 20 69 31 20 4f 4e 20 74 31 28 61 29 3b DEX i1 ON t1(a);
0940: 0a 20 20 43 52 45 41 54 45 20 55 4e 49 51 55 45 . CREATE UNIQUE
0950: 20 49 4e 44 45 58 20 69 32 20 4f 4e 20 74 31 28 INDEX i2 ON t1(
0960: 62 29 3b 0a 20 20 43 52 45 41 54 45 20 55 4e 49 b);. CREATE UNI
0970: 51 55 45 20 49 4e 44 45 58 20 69 33 20 4f 4e 20 QUE INDEX i3 ON
0980: 74 32 28 64 29 3b 0a 7d 20 7b 7d 0a 0a 66 6f 72 t2(d);.} {}..for
0990: 65 61 63 68 20 7b 74 6e 20 73 71 6c 7d 20 7b 0a each {tn sql} {.
09a0: 20 20 31 20 22 53 45 4c 45 43 54 20 2a 20 46 52 1 "SELECT * FR
09b0: 4f 4d 20 74 31 2c 20 20 20 20 20 20 20 20 20 20 OM t1,
09c0: 20 74 32 20 57 48 45 52 45 20 74 31 2e 61 3e 3f t2 WHERE t1.a>?
09d0: 20 41 4e 44 20 74 32 2e 64 3e 74 31 2e 63 20 41 AND t2.d>t1.c A
09e0: 4e 44 20 74 31 2e 62 3d 74 32 2e 65 22 0a 20 20 ND t1.b=t2.e".
09f0: 32 20 22 53 45 4c 45 43 54 20 2a 20 46 52 4f 4d 2 "SELECT * FROM
0a00: 20 74 32 2c 20 20 20 20 20 20 20 20 20 20 20 74 t2, t
0a10: 31 20 57 48 45 52 45 20 74 31 2e 61 3e 3f 20 41 1 WHERE t1.a>? A
0a20: 4e 44 20 74 32 2e 64 3e 74 31 2e 63 20 41 4e 44 ND t2.d>t1.c AND
0a30: 20 74 31 2e 62 3d 74 32 2e 65 22 0a 20 20 33 20 t1.b=t2.e". 3
0a40: 22 53 45 4c 45 43 54 20 2a 20 46 52 4f 4d 20 74 "SELECT * FROM t
0a50: 32 20 43 52 4f 53 53 20 4a 4f 49 4e 20 74 31 20 2 CROSS JOIN t1
0a60: 57 48 45 52 45 20 74 31 2e 61 3e 3f 20 41 4e 44 WHERE t1.a>? AND
0a70: 20 74 32 2e 64 3e 74 31 2e 63 20 41 4e 44 20 74 t2.d>t1.c AND t
0a80: 31 2e 62 3d 74 32 2e 65 22 0a 7d 20 7b 0a 20 20 1.b=t2.e".} {.
0a90: 64 6f 5f 74 65 73 74 20 32 2e 24 74 6e 20 7b 0a do_test 2.$tn {.
0aa0: 20 20 20 20 64 62 20 65 76 61 6c 20 22 45 58 50 db eval "EXP
0ab0: 4c 41 49 4e 20 51 55 45 52 59 20 50 4c 41 4e 20 LAIN QUERY PLAN
0ac0: 24 73 71 6c 22 0a 20 20 20 7d 20 7b 2f 2e 2a 53 $sql". } {/.*S
0ad0: 43 41 4e 20 54 41 42 4c 45 20 74 32 5c 79 2e 2a CAN TABLE t2\y.*
0ae0: 53 45 41 52 43 48 20 54 41 42 4c 45 20 74 31 5c SEARCH TABLE t1\
0af0: 79 2e 2a 2f 7d 0a 7d 0a 0a 64 6f 5f 65 78 65 63 y.*/}.}..do_exec
0b00: 73 71 6c 5f 74 65 73 74 20 33 2e 30 20 7b 0a 20 sql_test 3.0 {.
0b10: 20 44 52 4f 50 20 54 41 42 4c 45 20 74 31 3b 0a DROP TABLE t1;.
0b20: 20 20 44 52 4f 50 20 54 41 42 4c 45 20 74 32 3b DROP TABLE t2;
0b30: 0a 20 20 43 52 45 41 54 45 20 54 41 42 4c 45 20 . CREATE TABLE
0b40: 74 31 28 61 2c 20 62 2c 20 63 29 3b 0a 20 20 43 t1(a, b, c);. C
0b50: 52 45 41 54 45 20 54 41 42 4c 45 20 74 32 28 64 REATE TABLE t2(d
0b60: 2c 20 65 2c 20 66 29 3b 0a 0a 20 20 43 52 45 41 , e, f);.. CREA
0b70: 54 45 20 55 4e 49 51 55 45 20 49 4e 44 45 58 20 TE UNIQUE INDEX
0b80: 69 31 20 4f 4e 20 74 31 28 61 2c 20 62 29 3b 0a i1 ON t1(a, b);.
0b90: 20 20 43 52 45 41 54 45 20 49 4e 44 45 58 20 69 CREATE INDEX i
0ba0: 32 20 4f 4e 20 74 32 28 64 29 3b 0a 7d 20 7b 7d 2 ON t2(d);.} {}
0bb0: 0a 0a 66 6f 72 65 61 63 68 20 7b 74 6e 20 73 71 ..foreach {tn sq
0bc0: 6c 7d 20 7b 0a 20 20 31 20 7b 53 45 4c 45 43 54 l} {. 1 {SELECT
0bd0: 20 74 31 2e 61 2c 20 74 31 2e 62 2c 20 74 32 2e t1.a, t1.b, t2.
0be0: 64 2c 20 74 32 2e 65 20 46 52 4f 4d 20 74 31 2c d, t2.e FROM t1,
0bf0: 20 74 32 20 0a 20 20 20 20 20 57 48 45 52 45 20 t2 . WHERE
0c00: 74 32 2e 64 3d 74 31 2e 62 20 41 4e 44 20 74 31 t2.d=t1.b AND t1
0c10: 2e 61 3d 28 74 32 2e 64 2b 31 29 20 41 4e 44 20 .a=(t2.d+1) AND
0c20: 74 31 2e 62 20 3d 20 28 74 32 2e 65 2b 31 29 7d t1.b = (t2.e+1)}
0c30: 0a 0a 20 20 32 20 7b 53 45 4c 45 43 54 20 74 31 .. 2 {SELECT t1
0c40: 2e 61 2c 20 74 31 2e 62 2c 20 74 32 2e 64 2c 20 .a, t1.b, t2.d,
0c50: 74 32 2e 65 20 46 52 4f 4d 20 74 32 2c 20 74 31 t2.e FROM t2, t1
0c60: 20 0a 20 20 20 20 20 57 48 45 52 45 20 74 32 2e . WHERE t2.
0c70: 64 3d 74 31 2e 62 20 41 4e 44 20 74 31 2e 61 3d d=t1.b AND t1.a=
0c80: 28 74 32 2e 64 2b 31 29 20 41 4e 44 20 74 31 2e (t2.d+1) AND t1.
0c90: 62 20 3d 20 28 74 32 2e 65 2b 31 29 7d 0a 0a 20 b = (t2.e+1)}..
0ca0: 20 33 20 7b 53 45 4c 45 43 54 20 74 31 2e 61 2c 3 {SELECT t1.a,
0cb0: 20 74 31 2e 62 2c 20 74 32 2e 64 2c 20 74 32 2e t1.b, t2.d, t2.
0cc0: 65 20 46 52 4f 4d 20 74 32 20 43 52 4f 53 53 20 e FROM t2 CROSS
0cd0: 4a 4f 49 4e 20 74 31 20 0a 20 20 20 20 20 57 48 JOIN t1 . WH
0ce0: 45 52 45 20 74 32 2e 64 3d 74 31 2e 62 20 41 4e ERE t2.d=t1.b AN
0cf0: 44 20 74 31 2e 61 3d 28 74 32 2e 64 2b 31 29 20 D t1.a=(t2.d+1)
0d00: 41 4e 44 20 74 31 2e 62 20 3d 20 28 74 32 2e 65 AND t1.b = (t2.e
0d10: 2b 31 29 7d 0a 7d 20 7b 0a 20 20 64 6f 5f 74 65 +1)}.} {. do_te
0d20: 73 74 20 33 2e 24 74 6e 20 7b 0a 20 20 20 20 64 st 3.$tn {. d
0d30: 62 20 65 76 61 6c 20 22 45 58 50 4c 41 49 4e 20 b eval "EXPLAIN
0d40: 51 55 45 52 59 20 50 4c 41 4e 20 24 73 71 6c 22 QUERY PLAN $sql"
0d50: 0a 20 20 20 7d 20 7b 2f 2e 2a 53 43 41 4e 20 54 . } {/.*SCAN T
0d60: 41 42 4c 45 20 74 32 5c 79 2e 2a 53 45 41 52 43 ABLE t2\y.*SEARC
0d70: 48 20 54 41 42 4c 45 20 74 31 5c 79 2e 2a 2f 7d H TABLE t1\y.*/}
0d80: 0a 7d 0a 0a 64 6f 5f 65 78 65 63 73 71 6c 5f 74 .}..do_execsql_t
0d90: 65 73 74 20 34 2e 30 20 7b 0a 20 20 43 52 45 41 est 4.0 {. CREA
0da0: 54 45 20 54 41 42 4c 45 20 74 34 28 61 2c 62 2c TE TABLE t4(a,b,
0db0: 63 2c 64 2c 65 2c 20 50 52 49 4d 41 52 59 20 4b c,d,e, PRIMARY K
0dc0: 45 59 28 61 2c 62 2c 63 29 29 3b 0a 20 20 43 52 EY(a,b,c));. CR
0dd0: 45 41 54 45 20 49 4e 44 45 58 20 74 34 61 64 63 EATE INDEX t4adc
0de0: 20 4f 4e 20 74 34 28 61 2c 64 2c 63 29 3b 0a 20 ON t4(a,d,c);.
0df0: 20 43 52 45 41 54 45 20 55 4e 49 51 55 45 20 49 CREATE UNIQUE I
0e00: 4e 44 45 58 20 74 34 61 65 62 63 20 4f 4e 20 74 NDEX t4aebc ON t
0e10: 34 28 61 2c 65 2c 62 2c 63 29 3b 0a 20 20 45 58 4(a,e,b,c);. EX
0e20: 50 4c 41 49 4e 20 51 55 45 52 59 20 50 4c 41 4e PLAIN QUERY PLAN
0e30: 20 53 45 4c 45 43 54 20 72 6f 77 69 64 20 46 52 SELECT rowid FR
0e40: 4f 4d 20 74 34 20 57 48 45 52 45 20 61 3d 3f 20 OM t4 WHERE a=?
0e50: 41 4e 44 20 62 3d 3f 3b 0a 7d 20 7b 2f 61 3d 2e AND b=?;.} {/a=.
0e60: 20 41 4e 44 20 62 3d 2e 2f 7d 0a 0a 23 2d 2d 2d AND b=./}..#---
0e70: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d ----------------
0e80: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d ----------------
0e90: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d ----------------
0ea0: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d ----------------
0eb0: 2d 2d 2d 2d 2d 2d 0a 23 20 54 65 73 74 20 74 68 ------.# Test th
0ec0: 65 20 66 6f 6c 6c 6f 77 69 6e 67 20 63 61 73 65 e following case
0ed0: 3a 0a 23 0a 23 20 20 20 2e 2e 2e 20 46 52 4f 4d :.#.# ... FROM
0ee0: 20 74 31 2c 20 74 32 20 57 48 45 52 45 20 28 0a t1, t2 WHERE (.
0ef0: 23 20 20 20 20 20 74 32 2e 72 6f 77 69 64 20 3d # t2.rowid =
0f00: 20 2b 74 31 2e 72 6f 77 69 64 20 4f 52 20 28 74 +t1.rowid OR (t
0f10: 32 2e 66 32 20 3d 20 74 31 2e 66 31 20 41 4e 44 2.f2 = t1.f1 AND
0f20: 20 74 31 2e 66 31 21 3d 2d 31 29 0a 23 20 20 20 t1.f1!=-1).#
0f30: 29 0a 23 0a 23 20 77 68 65 72 65 20 74 68 65 72 ).#.# where ther
0f40: 65 20 69 73 20 61 6e 20 69 6e 64 65 78 20 6f 6e e is an index on
0f50: 20 74 32 28 66 32 29 2e 20 54 68 65 20 70 6c 61 t2(f2). The pla
0f60: 6e 6e 65 72 20 73 68 6f 75 6c 64 20 75 73 65 20 nner should use
0f70: 22 74 31 22 20 61 73 20 74 68 65 0a 23 20 6f 75 "t1" as the.# ou
0f80: 74 65 72 20 6c 6f 6f 70 2e 20 54 68 65 20 69 6e ter loop. The in
0f90: 6e 65 72 20 6c 6f 6f 70 2c 20 6f 6e 20 22 74 32 ner loop, on "t2
0fa0: 22 2c 20 69 73 20 61 6e 20 4f 52 20 6f 70 74 69 ", is an OR opti
0fb0: 6d 69 7a 61 74 69 6f 6e 2e 20 4f 6e 65 20 70 61 mization. One pa
0fc0: 73 73 0a 23 20 66 6f 72 3a 0a 23 0a 23 20 20 20 ss.# for:.#.#
0fd0: 20 20 74 32 2e 72 6f 77 69 64 20 3d 20 24 31 0a t2.rowid = $1.
0fe0: 23 0a 23 20 61 6e 64 20 61 6e 6f 74 68 65 72 20 #.# and another
0ff0: 66 6f 72 3a 0a 23 0a 23 20 20 20 20 20 74 32 2e for:.#.# t2.
1000: 66 32 3d 24 31 20 41 4e 44 20 24 31 21 3d 2d 31 f2=$1 AND $1!=-1
1010: 0a 23 0a 23 20 74 68 65 20 74 65 73 74 20 69 73 .#.# the test is
1020: 20 74 6f 20 65 6e 73 75 72 65 20 74 68 61 74 20 to ensure that
1030: 6f 6e 20 74 68 65 20 73 65 63 6f 6e 64 20 70 61 on the second pa
1040: 73 73 2c 20 74 68 65 20 28 24 31 21 3d 2d 31 29 ss, the ($1!=-1)
1050: 20 63 6f 6e 64 69 74 69 6f 6e 0a 23 20 69 73 20 condition.# is
1060: 74 65 73 74 65 64 20 62 65 66 6f 72 65 20 61 6e tested before an
1070: 79 20 73 65 65 6b 20 6f 70 65 72 61 74 69 6f 6e y seek operation
1080: 73 20 61 72 65 20 70 65 72 66 6f 72 6d 65 64 20 s are performed
1090: 2d 20 69 2e 65 2e 20 6f 75 74 73 69 64 65 20 6f - i.e. outside o
10a0: 66 0a 23 20 74 68 65 20 6c 6f 6f 70 20 74 68 72 f.# the loop thr
10b0: 6f 75 67 68 20 74 68 65 20 66 32 3d 24 31 20 72 ough the f2=$1 r
10c0: 61 6e 67 65 20 6f 66 20 74 68 65 20 74 32 28 66 ange of the t2(f
10d0: 32 29 20 69 6e 64 65 78 2e 0a 23 0a 72 65 73 65 2) index..#.rese
10e0: 74 5f 64 62 0a 64 6f 5f 65 78 65 63 73 71 6c 5f t_db.do_execsql_
10f0: 74 65 73 74 20 35 2e 30 20 7b 0a 20 20 43 52 45 test 5.0 {. CRE
1100: 41 54 45 20 54 41 42 4c 45 20 74 31 28 66 31 29 ATE TABLE t1(f1)
1110: 3b 0a 20 20 43 52 45 41 54 45 20 54 41 42 4c 45 ;. CREATE TABLE
1120: 20 74 32 28 66 32 29 3b 0a 20 20 43 52 45 41 54 t2(f2);. CREAT
1130: 45 20 49 4e 44 45 58 20 74 32 66 20 4f 4e 20 74 E INDEX t2f ON t
1140: 32 28 66 32 29 3b 0a 0a 20 20 49 4e 53 45 52 54 2(f2);.. INSERT
1150: 20 49 4e 54 4f 20 74 31 20 56 41 4c 55 45 53 28 INTO t1 VALUES(
1160: 2d 31 29 3b 0a 20 20 49 4e 53 45 52 54 20 49 4e -1);. INSERT IN
1170: 54 4f 20 74 31 20 56 41 4c 55 45 53 28 2d 31 29 TO t1 VALUES(-1)
1180: 3b 0a 20 20 49 4e 53 45 52 54 20 49 4e 54 4f 20 ;. INSERT INTO
1190: 74 31 20 56 41 4c 55 45 53 28 2d 31 29 3b 0a 20 t1 VALUES(-1);.
11a0: 20 49 4e 53 45 52 54 20 49 4e 54 4f 20 74 31 20 INSERT INTO t1
11b0: 56 41 4c 55 45 53 28 2d 31 29 3b 0a 0a 20 20 57 VALUES(-1);.. W
11c0: 49 54 48 20 77 28 69 29 20 41 53 20 28 0a 20 20 ITH w(i) AS (.
11d0: 20 20 53 45 4c 45 43 54 20 31 20 55 4e 49 4f 4e SELECT 1 UNION
11e0: 20 41 4c 4c 20 53 45 4c 45 43 54 20 69 2b 31 20 ALL SELECT i+1
11f0: 46 52 4f 4d 20 77 20 57 48 45 52 45 20 69 3c 31 FROM w WHERE i<1
1200: 30 30 30 0a 20 20 29 0a 20 20 49 4e 53 45 52 54 000. ). INSERT
1210: 20 49 4e 54 4f 20 74 32 20 53 45 4c 45 43 54 20 INTO t2 SELECT
1220: 2d 31 20 46 52 4f 4d 20 77 3b 0a 7d 0a 0a 64 6f -1 FROM w;.}..do
1230: 5f 65 78 65 63 73 71 6c 5f 74 65 73 74 20 35 2e _execsql_test 5.
1240: 31 20 7b 0a 20 20 53 45 4c 45 43 54 20 63 6f 75 1 {. SELECT cou
1250: 6e 74 28 2a 29 20 46 52 4f 4d 20 74 31 2c 20 74 nt(*) FROM t1, t
1260: 32 20 57 48 45 52 45 20 74 32 2e 72 6f 77 69 64 2 WHERE t2.rowid
1270: 20 3d 20 2b 74 31 2e 72 6f 77 69 64 0a 7d 20 7b = +t1.rowid.} {
1280: 34 7d 0a 64 6f 5f 74 65 73 74 20 35 2e 32 20 7b 4}.do_test 5.2 {
1290: 20 65 78 70 72 20 5b 64 62 20 73 74 61 74 75 73 expr [db status
12a0: 20 76 6d 73 74 65 70 5d 3c 32 30 30 20 7d 20 31 vmstep]<200 } 1
12b0: 0a 0a 64 6f 5f 65 78 65 63 73 71 6c 5f 74 65 73 ..do_execsql_tes
12c0: 74 20 35 2e 33 20 7b 0a 20 20 53 45 4c 45 43 54 t 5.3 {. SELECT
12d0: 20 63 6f 75 6e 74 28 2a 29 20 46 52 4f 4d 20 74 count(*) FROM t
12e0: 31 2c 20 74 32 20 57 48 45 52 45 20 28 0a 20 20 1, t2 WHERE (.
12f0: 20 20 74 32 2e 72 6f 77 69 64 20 3d 20 2b 74 31 t2.rowid = +t1
1300: 2e 72 6f 77 69 64 20 4f 52 20 74 32 2e 66 32 20 .rowid OR t2.f2
1310: 3d 20 74 31 2e 66 31 0a 20 20 29 0a 7d 20 7b 34 = t1.f1. ).} {4
1320: 30 30 30 7d 0a 64 6f 5f 74 65 73 74 20 35 2e 34 000}.do_test 5.4
1330: 20 7b 20 65 78 70 72 20 5b 64 62 20 73 74 61 74 { expr [db stat
1340: 75 73 20 76 6d 73 74 65 70 5d 3e 31 30 30 30 20 us vmstep]>1000
1350: 7d 20 31 0a 0a 64 6f 5f 65 78 65 63 73 71 6c 5f } 1..do_execsql_
1360: 74 65 73 74 20 35 2e 35 20 7b 0a 20 20 53 45 4c test 5.5 {. SEL
1370: 45 43 54 20 63 6f 75 6e 74 28 2a 29 20 46 52 4f ECT count(*) FRO
1380: 4d 20 74 31 2c 20 74 32 20 57 48 45 52 45 20 28 M t1, t2 WHERE (
1390: 0a 20 20 20 20 74 32 2e 72 6f 77 69 64 20 3d 20 . t2.rowid =
13a0: 2b 74 31 2e 72 6f 77 69 64 20 4f 52 20 28 74 32 +t1.rowid OR (t2
13b0: 2e 66 32 20 3d 20 74 31 2e 66 31 20 41 4e 44 20 .f2 = t1.f1 AND
13c0: 74 31 2e 66 31 21 3d 2d 31 29 0a 20 20 29 0a 7d t1.f1!=-1). ).}
13d0: 20 7b 34 7d 0a 64 6f 5f 74 65 73 74 20 35 2e 36 {4}.do_test 5.6
13e0: 20 7b 20 65 78 70 72 20 5b 64 62 20 73 74 61 74 { expr [db stat
13f0: 75 73 20 76 6d 73 74 65 70 5d 3c 32 30 30 20 7d us vmstep]<200 }
1400: 20 31 0a 0a 23 20 32 30 31 37 2d 30 39 2d 30 34 1..# 2017-09-04
1410: 20 74 69 63 6b 65 74 20 62 38 39 39 62 36 30 34 ticket b899b604
1420: 32 66 39 37 66 35 32 64 0a 23 20 53 65 67 66 61 2f97f52d.# Segfa
1430: 75 6c 74 20 6f 6e 20 63 6f 72 72 65 6c 61 74 65 ult on correlate
1440: 64 20 73 75 62 71 75 65 72 79 2e 2e 2e 0a 23 0a d subquery....#.
1450: 69 66 63 61 70 61 62 6c 65 20 6a 73 6f 6e 31 20 ifcapable json1
1460: 7b 0a 20 20 64 6f 5f 65 78 65 63 73 71 6c 5f 74 {. do_execsql_t
1470: 65 73 74 20 36 2e 31 20 7b 0a 20 20 20 20 43 52 est 6.1 {. CR
1480: 45 41 54 45 20 54 41 42 4c 45 20 74 36 28 78 29 EATE TABLE t6(x)
1490: 3b 0a 20 20 20 20 53 45 4c 45 43 54 20 2a 20 46 ;. SELECT * F
14a0: 52 4f 4d 20 74 36 20 57 48 45 52 45 20 31 20 49 ROM t6 WHERE 1 I
14b0: 4e 20 28 53 45 4c 45 43 54 20 76 61 6c 75 65 20 N (SELECT value
14c0: 46 52 4f 4d 20 6a 73 6f 6e 5f 65 61 63 68 28 78 FROM json_each(x
14d0: 29 29 3b 0a 20 20 7d 20 7b 7d 0a 7d 0a 0a 66 69 ));. } {}.}..fi
14e0: 6e 69 73 68 5f 74 65 73 74 0a nish_test.