AryaWu/sqlite
0
1# 2024-05-282#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# 12# Test cases for the ability of the query planner to cope with13# star-schema queries.14#15 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18set ::testprefix starschema119 20do_execsql_test 1.1 {21CREATE TABLE t1(22 a01 INT, a02 INT, a03 INT, a04 INT, a05 INT, a06 INT, a07 INT, a08 INT,23 a09 INT, a10 INT, a11 INT, a12 INT, a13 INT, a14 INT, a15 INT, a16 INT,24 a17 INT, a18 INT, a19 INT, a20 INT, a21 INT, a22 INT, a23 INT, a24 INT,25 a25 INT, a26 INT, a27 INT, a28 INT, a29 INT, a30 INT, a31 INT, a32 INT,26 a33 INT, a34 INT, a35 INT, a36 INT, a37 INT, a38 INT, a39 INT, a40 INT,27 a41 INT, a42 INT, a43 INT, a44 INT, a45 INT, a46 INT, a47 INT, a48 INT,28 a49 INT, a50 INT, a51 INT, a52 INT, a53 INT, a54 INT, a55 INT, a56 INT,29 a57 INT, a58 INT, a59 INT, a60 INT, a61 INT, a62 INT, a63 INT, d TEXT);30CREATE TABLE x01(b01 INT, c01 TEXT);31CREATE TABLE x02(b02 INT, c02 TEXT);32CREATE TABLE x03(b03 INT, c03 TEXT);33CREATE TABLE x04(b04 INT, c04 TEXT);34CREATE TABLE x05(b05 INT, c05 TEXT);35CREATE TABLE x06(b06 INT, c06 TEXT);36CREATE TABLE x07(b07 INT, c07 TEXT);37CREATE TABLE x08(b08 INT, c08 TEXT);38CREATE TABLE x09(b09 INT, c09 TEXT);39CREATE TABLE x10(b10 INT, c10 TEXT);40CREATE TABLE x11(b11 INT, c11 TEXT);41CREATE TABLE x12(b12 INT, c12 TEXT);42CREATE TABLE x13(b13 INT, c13 TEXT);43CREATE TABLE x14(b14 INT, c14 TEXT);44CREATE TABLE x15(b15 INT, c15 TEXT);45CREATE TABLE x16(b16 INT, c16 TEXT);46CREATE TABLE x17(b17 INT, c17 TEXT);47CREATE TABLE x18(b18 INT, c18 TEXT);48CREATE TABLE x19(b19 INT, c19 TEXT);49CREATE TABLE x20(b20 INT, c20 TEXT);50CREATE TABLE x21(b21 INT, c21 TEXT);51CREATE TABLE x22(b22 INT, c22 TEXT);52CREATE TABLE x23(b23 INT, c23 TEXT);53CREATE TABLE x24(b24 INT, c24 TEXT);54CREATE TABLE x25(b25 INT, c25 TEXT);55CREATE TABLE x26(b26 INT, c26 TEXT);56CREATE TABLE x27(b27 INT, c27 TEXT);57CREATE TABLE x28(b28 INT, c28 TEXT);58CREATE TABLE x29(b29 INT, c29 TEXT);59CREATE TABLE x30(b30 INT, c30 TEXT);60CREATE TABLE x31(b31 INT, c31 TEXT);61CREATE TABLE x32(b32 INT, c32 TEXT);62CREATE TABLE x33(b33 INT, c33 TEXT);63CREATE TABLE x34(b34 INT, c34 TEXT);64CREATE TABLE x35(b35 INT, c35 TEXT);65CREATE TABLE x36(b36 INT, c36 TEXT);66CREATE TABLE x37(b37 INT, c37 TEXT);67CREATE TABLE x38(b38 INT, c38 TEXT);68CREATE TABLE x39(b39 INT, c39 TEXT);69CREATE TABLE x40(b40 INT, c40 TEXT);70CREATE TABLE x41(b41 INT, c41 TEXT);71CREATE TABLE x42(b42 INT, c42 TEXT);72CREATE TABLE x43(b43 INT, c43 TEXT);73CREATE TABLE x44(b44 INT, c44 TEXT);74CREATE TABLE x45(b45 INT, c45 TEXT);75CREATE TABLE x46(b46 INT, c46 TEXT);76CREATE TABLE x47(b47 INT, c47 TEXT);77CREATE TABLE x48(b48 INT, c48 TEXT);78CREATE TABLE x49(b49 INT, c49 TEXT);79CREATE TABLE x50(b50 INT, c50 TEXT);80CREATE TABLE x51(b51 INT, c51 TEXT);81CREATE TABLE x52(b52 INT, c52 TEXT);82CREATE TABLE x53(b53 INT, c53 TEXT);83CREATE TABLE x54(b54 INT, c54 TEXT);84CREATE TABLE x55(b55 INT, c55 TEXT);85CREATE TABLE x56(b56 INT, c56 TEXT);86CREATE TABLE x57(b57 INT, c57 TEXT);87CREATE TABLE x58(b58 INT, c58 TEXT);88CREATE TABLE x59(b59 INT, c59 TEXT);89CREATE TABLE x60(b60 INT, c60 TEXT);90CREATE TABLE x61(b61 INT, c61 TEXT);91CREATE TABLE x62(b62 INT, c62 TEXT);92CREATE TABLE x63(b63 INT, c63 TEXT);93/**** Uncomment to generate actual data ************************************94WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<172800)95 INSERT INTO t196 SELECT stmtrand()%12, stmtrand()%13, stmtrand()%14, stmtrand()%15,97 stmtrand()%16, stmtrand()%17, stmtrand()%18, stmtrand()%19,98 stmtrand()%20, stmtrand()%21, stmtrand()%22, stmtrand()%23,99 stmtrand()%24, stmtrand()%25, stmtrand()%26, stmtrand()%27,100 stmtrand()%28, stmtrand()%29, stmtrand()%30, stmtrand()%31,101 stmtrand()%32, stmtrand()%33, stmtrand()%34, stmtrand()%35,102 stmtrand()%36, stmtrand()%37, stmtrand()%38, stmtrand()%39,103 stmtrand()%40, stmtrand()%41, stmtrand()%42, stmtrand()%43,104 stmtrand()%28, stmtrand()%29, stmtrand()%30, stmtrand()%31,105 stmtrand()%32, stmtrand()%33, stmtrand()%34, stmtrand()%35,106 stmtrand()%36, stmtrand()%37, stmtrand()%38, stmtrand()%39,107 stmtrand()%40, stmtrand()%41, stmtrand()%42, stmtrand()%43,108 stmtrand()%28, stmtrand()%29, stmtrand()%30, stmtrand()%31,109 stmtrand()%32, stmtrand()%33, stmtrand()%34, stmtrand()%35,110 stmtrand()%36, stmtrand()%37, stmtrand()%38, stmtrand()%39,111 stmtrand()%40, stmtrand()%41, stmtrand()%42, stmtrand() FROM c;112WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<8)113 INSERT INTO x01 SELECT n%4, format('%d-or-0x%04x',n,n) FROM c;114WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<12)115 INSERT INTO x02 SELECT n%6, format('%d-or-0x%04x',n,n) FROM c;116WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<16)117 INSERT INTO x03 SELECT n%8, format('%d-or-0x%04x',n,n) FROM c;118WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<20)119 INSERT INTO x04 SELECT n%10, format('%d-or-0x%04x',n,n) FROM c;120WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<24)121 INSERT INTO x05 SELECT n%12, format('%d-or-0x%04x',n,n) FROM c;122WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<32)123 INSERT INTO x06 SELECT n%16, format('%d-or-0x%04x',n,n) FROM c;124WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<36)125 INSERT INTO x07 SELECT n%18, format('%d-or-0x%04x',n,n) FROM c;126WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<40)127 INSERT INTO x08 SELECT n%20, format('%d-or-0x%04x',n,n) FROM c;128WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<44)129 INSERT INTO x09 SELECT n%22, format('%d-or-0x%04x',n,n) FROM c;130WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<48)131 INSERT INTO x10 SELECT n%24, format('%d-or-0x%04x',n,n) FROM c;132WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<52)133 INSERT INTO x11 SELECT n%26, format('%d-or-0x%04x',n,n) FROM c;134WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<56)135 INSERT INTO x12 SELECT n%28, format('%d-or-0x%04x',n,n) FROM c;136WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<60)137 INSERT INTO x13 SELECT n%30, format('%d-or-0x%04x',n,n) FROM c;138WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<64)139 INSERT INTO x14 SELECT n%32, format('%d-or-0x%04x',n,n) FROM c;140WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<72)141 INSERT INTO x15 SELECT n%36, format('%d-or-0x%04x',n,n) FROM c;142WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<80)143 INSERT INTO x16 SELECT n%40, format('%d-or-0x%04x',n,n) FROM c;144WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<8)145 INSERT INTO x17 SELECT n%4, format('%d-or-0x%04x',n,n) FROM c;146WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<12)147 INSERT INTO x18 SELECT n%6, format('%d-or-0x%04x',n,n) FROM c;148WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<16)149 INSERT INTO x19 SELECT n%8, format('%d-or-0x%04x',n,n) FROM c;150WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<20)151 INSERT INTO x20 SELECT n%10, format('%d-or-0x%04x',n,n) FROM c;152WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<24)153 INSERT INTO x21 SELECT n%12, format('%d-or-0x%04x',n,n) FROM c;154WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<32)155 INSERT INTO x22 SELECT n%16, format('%d-or-0x%04x',n,n) FROM c;156WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<36)157 INSERT INTO x23 SELECT n%18, format('%d-or-0x%04x',n,n) FROM c;158WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<40)159 INSERT INTO x24 SELECT n%20, format('%d-or-0x%04x',n,n) FROM c;160WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<44)161 INSERT INTO x25 SELECT n%22, format('%d-or-0x%04x',n,n) FROM c;162WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<48)163 INSERT INTO x26 SELECT n%24, format('%d-or-0x%04x',n,n) FROM c;164WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<52)165 INSERT INTO x27 SELECT n%26, format('%d-or-0x%04x',n,n) FROM c;166WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<56)167 INSERT INTO x28 SELECT n%28, format('%d-or-0x%04x',n,n) FROM c;168WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<60)169 INSERT INTO x29 SELECT n%30, format('%d-or-0x%04x',n,n) FROM c;170WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<64)171 INSERT INTO x30 SELECT n%32, format('%d-or-0x%04x',n,n) FROM c;172WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<72)173 INSERT INTO x31 SELECT n%36, format('%d-or-0x%04x',n,n) FROM c;174WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<80)175 INSERT INTO x32 SELECT n%40, format('%d-or-0x%04x',n,n) FROM c;176WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<8)177 INSERT INTO x33 SELECT n%4, format('%d-or-0x%04x',n,n) FROM c;178WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<12)179 INSERT INTO x34 SELECT n%6, format('%d-or-0x%04x',n,n) FROM c;180WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<16)181 INSERT INTO x35 SELECT n%8, format('%d-or-0x%04x',n,n) FROM c;182WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<20)183 INSERT INTO x36 SELECT n%10, format('%d-or-0x%04x',n,n) FROM c;184WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<24)185 INSERT INTO x37 SELECT n%12, format('%d-or-0x%04x',n,n) FROM c;186WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<32)187 INSERT INTO x38 SELECT n%16, format('%d-or-0x%04x',n,n) FROM c;188WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<36)189 INSERT INTO x39 SELECT n%18, format('%d-or-0x%04x',n,n) FROM c;190WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<40)191 INSERT INTO x40 SELECT n%20, format('%d-or-0x%04x',n,n) FROM c;192WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<44)193 INSERT INTO x41 SELECT n%22, format('%d-or-0x%04x',n,n) FROM c;194WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<48)195 INSERT INTO x42 SELECT n%24, format('%d-or-0x%04x',n,n) FROM c;196WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<52)197 INSERT INTO x43 SELECT n%26, format('%d-or-0x%04x',n,n) FROM c;198WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<56)199 INSERT INTO x44 SELECT n%28, format('%d-or-0x%04x',n,n) FROM c;200WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<60)201 INSERT INTO x45 SELECT n%30, format('%d-or-0x%04x',n,n) FROM c;202WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<64)203 INSERT INTO x46 SELECT n%32, format('%d-or-0x%04x',n,n) FROM c;204WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<72)205 INSERT INTO x47 SELECT n%36, format('%d-or-0x%04x',n,n) FROM c;206WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<80)207 INSERT INTO x48 SELECT n%40, format('%d-or-0x%04x',n,n) FROM c;208WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<8)209 INSERT INTO x49 SELECT n%4, format('%d-or-0x%04x',n,n) FROM c;210WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<12)211 INSERT INTO x50 SELECT n%6, format('%d-or-0x%04x',n,n) FROM c;212WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<16)213 INSERT INTO x51 SELECT n%8, format('%d-or-0x%04x',n,n) FROM c;214WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<20)215 INSERT INTO x52 SELECT n%10, format('%d-or-0x%04x',n,n) FROM c;216WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<24)217 INSERT INTO x53 SELECT n%12, format('%d-or-0x%04x',n,n) FROM c;218WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<32)219 INSERT INTO x54 SELECT n%16, format('%d-or-0x%04x',n,n) FROM c;220WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<36)221 INSERT INTO x55 SELECT n%18, format('%d-or-0x%04x',n,n) FROM c;222WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<40)223 INSERT INTO x56 SELECT n%20, format('%d-or-0x%04x',n,n) FROM c;224WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<44)225 INSERT INTO x57 SELECT n%22, format('%d-or-0x%04x',n,n) FROM c;226WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<48)227 INSERT INTO x58 SELECT n%24, format('%d-or-0x%04x',n,n) FROM c;228WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<52)229 INSERT INTO x59 SELECT n%26, format('%d-or-0x%04x',n,n) FROM c;230WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<56)231 INSERT INTO x60 SELECT n%28, format('%d-or-0x%04x',n,n) FROM c;232WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<60)233 INSERT INTO x61 SELECT n%30, format('%d-or-0x%04x',n,n) FROM c;234WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<64)235 INSERT INTO x62 SELECT n%32, format('%d-or-0x%04x',n,n) FROM c;236WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<72)237 INSERT INTO x63 SELECT n%36, format('%d-or-0x%04x',n,n) FROM c;238****************************************************************************/239CREATE INDEX t1a01 ON t1(a01);240CREATE INDEX t1a02 ON t1(a02);241CREATE INDEX t1a03 ON t1(a03);242CREATE INDEX t1a04 ON t1(a04);243CREATE INDEX t1a05 ON t1(a05);244CREATE INDEX t1a06 ON t1(a06);245CREATE INDEX t1a07 ON t1(a07);246CREATE INDEX t1a08 ON t1(a08);247CREATE INDEX t1a09 ON t1(a09);248CREATE INDEX t1a10 ON t1(a10);249CREATE INDEX t1a11 ON t1(a11);250CREATE INDEX t1a12 ON t1(a12);251CREATE INDEX t1a13 ON t1(a13);252CREATE INDEX t1a14 ON t1(a14);253CREATE INDEX t1a15 ON t1(a15);254CREATE INDEX t1a16 ON t1(a16);255CREATE INDEX t1a17 ON t1(a17);256CREATE INDEX t1a18 ON t1(a18);257CREATE INDEX t1a19 ON t1(a19);258CREATE INDEX t1a20 ON t1(a20);259CREATE INDEX t1a21 ON t1(a21);260CREATE INDEX t1a22 ON t1(a22);261CREATE INDEX t1a23 ON t1(a23);262CREATE INDEX t1a24 ON t1(a24);263CREATE INDEX t1a25 ON t1(a25);264CREATE INDEX t1a26 ON t1(a26);265CREATE INDEX t1a27 ON t1(a27);266CREATE INDEX t1a28 ON t1(a28);267CREATE INDEX t1a29 ON t1(a29);268CREATE INDEX t1a30 ON t1(a30);269CREATE INDEX t1a31 ON t1(a31);270CREATE INDEX t1a32 ON t1(a32);271CREATE INDEX t1a33 ON t1(a33);272CREATE INDEX t1a34 ON t1(a34);273CREATE INDEX t1a35 ON t1(a35);274CREATE INDEX t1a36 ON t1(a36);275CREATE INDEX t1a37 ON t1(a37);276CREATE INDEX t1a38 ON t1(a38);277CREATE INDEX t1a39 ON t1(a39);278CREATE INDEX t1a40 ON t1(a40);279CREATE INDEX t1a41 ON t1(a41);280CREATE INDEX t1a42 ON t1(a42);281CREATE INDEX t1a43 ON t1(a43);282CREATE INDEX t1a44 ON t1(a44);283CREATE INDEX t1a45 ON t1(a45);284CREATE INDEX t1a46 ON t1(a46);285CREATE INDEX t1a47 ON t1(a47);286CREATE INDEX t1a48 ON t1(a48);287CREATE INDEX t1a49 ON t1(a49);288CREATE INDEX t1a50 ON t1(a50);289CREATE INDEX t1a51 ON t1(a51);290CREATE INDEX t1a52 ON t1(a52);291CREATE INDEX t1a53 ON t1(a53);292CREATE INDEX t1a54 ON t1(a54);293CREATE INDEX t1a55 ON t1(a55);294CREATE INDEX t1a56 ON t1(a56);295CREATE INDEX t1a57 ON t1(a57);296CREATE INDEX t1a58 ON t1(a58);297CREATE INDEX t1a59 ON t1(a59);298CREATE INDEX t1a60 ON t1(a60);299CREATE INDEX t1a61 ON t1(a61);300CREATE INDEX t1a62 ON t1(a62);301CREATE INDEX t1a63 ON t1(a63);302CREATE INDEX x01x ON x01(b01);303CREATE INDEX x02x ON x02(b02);304CREATE INDEX x03x ON x03(b03);305CREATE INDEX x04x ON x04(b04);306CREATE INDEX x05x ON x05(b05);307CREATE INDEX x06x ON x06(b06);308CREATE INDEX x07x ON x07(b07);309CREATE INDEX x08x ON x08(b08);310CREATE INDEX x09x ON x09(b09);311CREATE INDEX x10x ON x10(b10);312CREATE INDEX x11x ON x11(b11);313CREATE INDEX x12x ON x12(b12);314CREATE INDEX x13x ON x13(b13);315CREATE INDEX x14x ON x14(b14);316CREATE INDEX x15x ON x15(b15);317CREATE INDEX x16x ON x16(b16);318CREATE INDEX x17x ON x17(b17);319CREATE INDEX x18x ON x18(b18);320CREATE INDEX x19x ON x19(b19);321CREATE INDEX x20x ON x20(b20);322CREATE INDEX x21x ON x21(b21);323CREATE INDEX x22x ON x22(b22);324CREATE INDEX x23x ON x23(b23);325CREATE INDEX x24x ON x24(b24);326CREATE INDEX x25x ON x25(b25);327CREATE INDEX x26x ON x26(b26);328CREATE INDEX x27x ON x27(b27);329CREATE INDEX x28x ON x28(b28);330CREATE INDEX x29x ON x29(b29);331CREATE INDEX x30x ON x30(b30);332CREATE INDEX x31x ON x31(b31);333CREATE INDEX x32x ON x32(b32);334CREATE INDEX x33x ON x33(b33);335CREATE INDEX x34x ON x34(b34);336CREATE INDEX x35x ON x35(b35);337CREATE INDEX x36x ON x36(b36);338CREATE INDEX x37x ON x37(b37);339CREATE INDEX x38x ON x38(b38);340CREATE INDEX x39x ON x39(b39);341CREATE INDEX x40x ON x40(b40);342CREATE INDEX x41x ON x41(b41);343CREATE INDEX x42x ON x42(b42);344CREATE INDEX x43x ON x43(b43);345CREATE INDEX x44x ON x44(b44);346CREATE INDEX x45x ON x45(b45);347CREATE INDEX x46x ON x46(b46);348CREATE INDEX x47x ON x47(b47);349CREATE INDEX x48x ON x48(b48);350CREATE INDEX x49x ON x49(b49);351CREATE INDEX x50x ON x50(b50);352CREATE INDEX x51x ON x51(b51);353CREATE INDEX x52x ON x52(b52);354CREATE INDEX x53x ON x53(b53);355CREATE INDEX x54x ON x54(b54);356CREATE INDEX x55x ON x55(b55);357CREATE INDEX x56x ON x56(b56);358CREATE INDEX x57x ON x57(b57);359CREATE INDEX x58x ON x58(b58);360CREATE INDEX x59x ON x59(b59);361CREATE INDEX x60x ON x60(b60);362CREATE INDEX x61x ON x61(b61);363CREATE INDEX x62x ON x62(b62);364CREATE INDEX x63x ON x63(b63);365ANALYZE sqlite_schema;366INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES367 ('t1','t1a01','172800 14400'),368 ('t1','t1a02','172800 13293'),369 ('t1','t1a03','172800 12343'),370 ('t1','t1a04','172800 11520'),371 ('t1','t1a05','172800 10800'),372 ('t1','t1a06','172800 10165'),373 ('t1','t1a07','172800 9600'),374 ('t1','t1a08','172800 9095'),375 ('t1','t1a09','172800 8640'),376 ('t1','t1a10','172800 8229'),377 ('t1','t1a11','172800 7855'),378 ('t1','t1a12','172800 7514'),379 ('t1','t1a13','172800 7200'),380 ('t1','t1a14','172800 6912'),381 ('t1','t1a15','172800 6647'),382 ('t1','t1a16','172800 6400'),383 ('t1','t1a17','172800 6172'),384 ('t1','t1a18','172800 5959'),385 ('t1','t1a19','172800 5760'),386 ('t1','t1a20','172800 5575'),387 ('t1','t1a21','172800 5400'),388 ('t1','t1a22','172800 5237'),389 ('t1','t1a23','172800 5083'),390 ('t1','t1a24','172800 4938'),391 ('t1','t1a25','172800 4800'),392 ('t1','t1a26','172800 4671'),393 ('t1','t1a27','172800 4548'),394 ('t1','t1a28','172800 4431'),395 ('t1','t1a29','172800 4320'),396 ('t1','t1a30','172800 4215'),397 ('t1','t1a31','172800 4115'),398 ('t1','t1a32','172800 4019'),399 ('t1','t1a33','172800 6172'),400 ('t1','t1a34','172800 5959'),401 ('t1','t1a35','172800 5760'),402 ('t1','t1a36','172800 5575'),403 ('t1','t1a37','172800 5400'),404 ('t1','t1a38','172800 5237'),405 ('t1','t1a39','172800 5083'),406 ('t1','t1a40','172800 4938'),407 ('t1','t1a41','172800 4800'),408 ('t1','t1a42','172800 4671'),409 ('t1','t1a43','172800 4548'),410 ('t1','t1a44','172800 4431'),411 ('t1','t1a45','172800 4320'),412 ('t1','t1a46','172800 4215'),413 ('t1','t1a47','172800 4115'),414 ('t1','t1a48','172800 4019'),415 ('t1','t1a49','172800 6172'),416 ('t1','t1a50','172800 5959'),417 ('t1','t1a51','172800 5760'),418 ('t1','t1a52','172800 5575'),419 ('t1','t1a53','172800 5400'),420 ('t1','t1a54','172800 5237'),421 ('t1','t1a55','172800 5083'),422 ('t1','t1a56','172800 4938'),423 ('t1','t1a57','172800 4800'),424 ('t1','t1a58','172800 4671'),425 ('t1','t1a59','172800 4548'),426 ('t1','t1a60','172800 4431'),427 ('t1','t1a61','172800 4320'),428 ('t1','t1a62','172800 4215'),429 ('t1','t1a63','172800 4115'),430 ('x01','x01x','80 2'),431 ('x02','x02x','120 2'),432 ('x03','x03x','160 2'),433 ('x04','x04x','20 2'),434 ('x05','x05x','24 2'),435 ('x06','x06x','32 2'),436 ('x07','x07x','36 2'),437 ('x08','x08x','40 2'),438 ('x09','x09x','44 2'),439 ('x10','x10x','48 2'),440 ('x11','x11x','52 2'),441 ('x12','x12x','56 2'),442 ('x13','x13x','60 2'),443 ('x14','x14x','64 2'),444 ('x15','x15x','72 2'),445 ('x16','x16x','80 2'),446 ('x17','x17x','80 2'),447 ('x18','x18x','120 2'),448 ('x19','x19x','160 2'),449 ('x20','x20x','20 2'),450 ('x21','x21x','24 2'),451 ('x22','x22x','32 2'),452 ('x23','x23x','36 2'),453 ('x24','x24x','40 2'),454 ('x25','x25x','44 2'),455 ('x26','x26x','48 2'),456 ('x27','x27x','52 2'),457 ('x28','x28x','56 2'),458 ('x29','x29x','60 2'),459 ('x30','x30x','64 2'),460 ('x31','x31x','72 2'),461 ('x32','x32x','80 2'),462 ('x33','x33x','80 2'),463 ('x34','x34x','120 2'),464 ('x35','x35x','160 2'),465 ('x36','x36x','20 2'),466 ('x37','x37x','24 2'),467 ('x38','x38x','32 2'),468 ('x39','x39x','36 2'),469 ('x40','x40x','40 2'),470 ('x41','x41x','44 2'),471 ('x42','x42x','48 2'),472 ('x43','x43x','52 2'),473 ('x44','x44x','56 2'),474 ('x45','x45x','60 2'),475 ('x46','x46x','64 2'),476 ('x47','x47x','72 2'),477 ('x48','x48x','80 2'),478 ('x49','x49x','80 2'),479 ('x50','x50x','120 2'),480 ('x51','x51x','160 2'),481 ('x52','x52x','20 2'),482 ('x53','x53x','24 2'),483 ('x54','x54x','32 2'),484 ('x55','x55x','36 2'),485 ('x56','x56x','40 2'),486 ('x57','x57x','44 2'),487 ('x58','x58x','48 2'),488 ('x59','x59x','52 2'),489 ('x60','x60x','56 2'),490 ('x61','x61x','60 2'),491 ('x62','x62x','64 2'),492 ('x63','x63x','72 2');493ANALYZE sqlite_schema;494}495do_execsql_test 1.2 {496 EXPLAIN QUERY PLAN497 SELECT c01, c02, c03498 FROM t1, x01, x02, x03499 WHERE a01=b01 AND a02=b02 AND a03=b03;500} {/SCAN t1.*SEARCH.*SEARCH.*SEARCH/} 501do_execsql_test 1.3 {502 EXPLAIN QUERY PLAN503 SELECT c01, c02, c03, c04504 FROM t1, x01, x02, x03, x04505 WHERE a01=b01 AND a02=b02 AND a03=b03 AND a04=b04;506} {/SCAN .*SEARCH .*SEARCH .*SEARCH .*SEARCH /} 507do_execsql_test 1.4 {508 EXPLAIN QUERY PLAN509 SELECT c01, c02, c03, c04, c05510 FROM t1, x01, x02, x03, x04, x05511 WHERE a01=b01 AND a02=b02 AND a03=b03 AND a04=b04 AND a05=b05;512} {/SCAN .*SEARCH .*SEARCH .*SEARCH .*SEARCH .*SEARCH/} 513do_execsql_test 1.5 {514 EXPLAIN QUERY PLAN515 SELECT c01, c02, c03, c04, c05, c06516 FROM t1, x01, x02, x03, x04, x05, x06517 WHERE a01=b01 AND a02=b02 AND a03=b03 AND a04=b04 AND a05=b05518 AND a06=b06;519} {/SCAN .*SEARCH .*SEARCH .*SEARCH .*SEARCH .*SEARCH .*SEARCH/} 520do_execsql_test 1.6 {521 EXPLAIN QUERY PLAN522 SELECT c01, c02, c03, c04, c05, c06, c07523 FROM t1, x01, x02, x03, x04, x05, x06, x07524 WHERE a01=b01 AND a02=b02 AND a03=b03 AND a04=b04 AND a05=b05525 AND a06=b06 AND a07=b07;526} {/SCAN .*SEARCH .*SEARCH .*SEARCH .*SEARCH .*SEARCH .*SEARCH .*SEARCH/} 527do_execsql_test 1.7 {528 EXPLAIN QUERY PLAN529 SELECT c01, c02, c03, c04, c05, c06, c07, c08530 FROM t1, x01, x02, x03, x04, x05, x06, x07, x08531 WHERE a01=b01 AND a02=b02 AND a03=b03 AND a04=b04 AND a05=b05532 AND a06=b06 AND a07=b07 AND a08=b08;533} {~/SCAN.*SCAN/}534do_execsql_test 1.8 {535 EXPLAIN QUERY PLAN536 SELECT c01, c02, c03, c04, c05, c06, c07, c08,537 c09, c10, c11, c12, c13, c14, c15, c16,538 c17, c18, c19, c20, c21, c22, c23, c24,539 c25, c26, c27, c28, c29, c30, c31, c32540 FROM t1, x01, x02, x03, x04, x05, x06, x07, x08,541 x09, x10, x11, x12, x13, x14, x15, x16,542 x17, x18, x19, x20, x21, x22, x23, x24,543 x25, x26, x27, x28, x29, x30, x31, x32544 WHERE a01=b01 AND a02=b02 AND a03=b03 AND a04=b04 AND a05=b05 AND a06=b06545 AND a07=b07 AND a08=b08 AND a09=b09 AND a10=b10 AND a11=b11 AND a12=b12546 AND a13=b13 AND a14=b14 AND a15=b15 AND a16=b16 AND a17=b17 AND a18=b18547 AND a19=b19 AND a20=b20 AND a21=b21 AND a22=b22 AND a23=b23 AND a24=b24548 AND a25=b25 AND a26=b26 AND a27=b27 AND a28=b28 AND a29=b29 AND a30=b30549 AND a31=b31 AND a32=b32;550} {~/SCAN.*SCAN/}551do_execsql_test 1.9 {552 EXPLAIN QUERY PLAN553 SELECT c01, c02, c03, c04, c05, c06, c07, c08,554 c09, c10, c11, c12, c13, c14, c15, c16,555 c17, c18, c19, c20, c21, c22, c23, c24,556 c25, c26, c27, c28, c29, c30, c31, c32,557 c33, c34, c35, c36, c37, c38, c39, c40,558 c41, c42, c43, c44, c45, c46, c47, c48,559 c49, c50, c51, c52, c53, c54, c55, c56,560 c57, c58, c59, c60, c61, c62, c63561 FROM t1, x01, x02, x03, x04, x05, x06, x07, x08,562 x09, x10, x11, x12, x13, x14, x15, x16,563 x17, x18, x19, x20, x21, x22, x23, x24,564 x25, x26, x27, x28, x29, x30, x31, x32,565 x33, x34, x35, x36, x37, x38, x39, x40,566 x41, x42, x43, x44, x45, x46, x47, x48,567 x49, x50, x51, x52, x53, x54, x55, x56,568 x57, x58, x59, x60, x61, x62, x63569 WHERE a01=b01 AND a02=b02 AND a03=b03 AND a04=b04 AND a05=b05 AND a06=b06570 AND a07=b07 AND a08=b08 AND a09=b09 AND a10=b10 AND a11=b11 AND a12=b12571 AND a13=b13 AND a14=b14 AND a15=b15 AND a16=b16 AND a17=b17 AND a18=b18572 AND a19=b19 AND a20=b20 AND a21=b21 AND a22=b22 AND a23=b23 AND a24=b24573 AND a25=b25 AND a26=b26 AND a27=b27 AND a28=b28 AND a29=b29 AND a30=b30574 AND a31=b31 AND a32=b32 AND a33=b33 AND a34=b34 AND a35=b35 AND a36=b36575 AND a37=b37 AND a38=b38 AND a39=b39 AND a40=b40 AND a41=b41 AND a42=b42576 AND a43=b43 AND a44=b44 AND a45=b45 AND a46=b46 AND a47=b47 AND a48=b48577 AND a49=b49 AND a50=b50 AND a51=b51 AND a52=b52 AND a53=b53 AND a54=b54578 AND a55=b55 AND a56=b56 AND a57=b57 AND a58=b58 AND a59=b59 AND a60=b60579 AND a61=b61 AND a62=b62 AND a63=b63;580} {~/SCAN.*SCAN/}581 582 583 584finish_test585