1# 2022-10-24 2# 3# The author disclaims copyright to this source code. In place of 4# 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# This file implements test cases for wide table (tables with more than 12# 64 columns) and indexes that reference columns beyond the 63rd or 64th 13# column. 14# 15 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18set testprefix widetab1 19 20 21# In order to pick the better index in the following query, SQLite needs to 22# be able to detect when an index that references later columns in a wide 23# table is a covering index. 24# 25do_execsql_test 100 { 26 CREATE TABLE a( 27 a00, a01, a02, a03, a04, a05, a06, a07, a08, a09, 28 a10, a11, a12, a13, a14, a15, a16, a17, a18, a19, 29 a20, a21, a22, a23, a24, a25, a26, a27, a28, a29, 30 a30, a31, a32, a33, a34, a35, a36, a37, a38, a39, 31 a40, a41, a42, a43, a44, a45, a46, a47, a48, a49, 32 a50, a51, a52, a53, a54, a55, a56, a57, a58, a59, 33 pd, bn, vb, bc, cn, ie, qm); 34 CREATE INDEX a1 on a(pd, bn, vb, bc, cn); -- preferred index 35 CREATE INDEX a2 on a(pd, bc, ie, qm); -- suboptimal index 36 CREATE TABLE b(bg, bc, bn, iv, ln, mg); 37 CREATE INDEX b1 on b(bn, iv, bg); 38} 39do_eqp_test 110 { 40 SELECT dc, count(cn) 41 FROM (SELECT coalesce(b.bg, a.bc) as dc, cn 42 FROM a LEFT JOIN b 43 ON a.bn = b.bn 44 AND CASE WHEN a.vb IS NOT NULL THEN 1 ELSE 0 END = b.iv 45 WHERE pd BETWEEN 0 AND 10) 46 GROUP BY dc; 47} { 48 QUERY PLAN 49 |--SEARCH a USING COVERING INDEX a1 (pd>? AND pd<?) 50 |--SEARCH b USING COVERING INDEX b1 (bn=? AND iv=?) LEFT-JOIN 51 `--USE TEMP B-TREE FOR GROUP BY 52} 53 54reset_db 55do_execsql_test 200 { 56 CREATE TABLE t1( 57 c00,c01,c02,c03,c04,c05,c06,c07,c08,c09, 58 c10,c11,c12,c13,c14,c15,c16,c17,c18,c19, 59 c20,c21,c22,c23,c24,c25,c26,c27,c28,c29, 60 c30,c31,c32,c33,c34,c35,c36,c37,c38,c39, 61 c40,c41,c42,c43,c44,c45,c46,c47,c48,c49, 62 c50,c51,c52,c53,c54,c55,c56,c57,c58,c59, 63 c60,c61,c62,c63,c64,c65,c66,c67,c68,c69, 64 c70,c71,c72,c73,c74,c75,c76,c77,c78,c79, 65 c80,c81,c82,c83,c84,c85,c86,c87,c88,c89, 66 c90,c91,c92,c93,c94,c95,c96,c97,c98,c99, 67 a,b,c,d,e 68 ); 69 CREATE INDEX t1x1 on t1(c00,a,b, 70 c01,c02,c03,c04,c05,c06,c07,c08,c09, 71 c10,c11,c12,c13,c14,c15,c16,c17,c18,c19, 72 c20,c21,c22,c23,c24,c25,c26,c27,c28,c29, 73 c30,c31,c32,c33,c34,c35,c36,c37,c38,c39, 74 c40,c41,c42,c43,c44,c45,c46,c47,c48,c49, 75 c50,c51,c52,c53,c54,c55,c56,c57,c58,c59, 76 c60,c61,c62,c63,c64,c65,c66,c67,c68,c69, 77 c70,c71,c72,c73,c74,c75,c76,c77,c78,c79, 78 c80,c81,c82,c83,c84,c85,c86,c87,c88,c89, 79 c90,c91,c92,c93,c94,c00,c96,c97,c98,c99 80 ); 81 CREATE INDEX t1cd ON t1(c,d); 82 CREATE INDEX t1x2 ON t1(c01,c02,c03,a,b); 83 WITH RECURSIVE c(x) AS (VALUES(0) UNION ALL SELECT x+1000 FROM c WHERE x<9000) 84 INSERT INTO t1 SELECT 85 x+00, x+01, x+02, x+03, x+04, x+05, x+06, x+07, x+08, x+09, 86 x+10, x+11, x+12, x+13, x+14, x+15, x+16, x+17, x+18, x+19, 87 x+20, x+21, x+22, x+23, x+24, x+25, x+26, x+27, x+28, x+29, 88 x+30, x+31, x+32, x+33, x+34, x+35, x+36, x+37, x+38, x+39, 89 x+40, x+41, x+42, x+43, x+44, x+45, x+46, x+47, x+48, x+49, 90 x+50, x+51, x+52, x+53, x+54, x+55, x+56, x+57, x+58, x+59, 91 x+60, x+61, x+62, x+63, x+64, x+65, x+66, x+67, x+68, x+69, 92 x+70, x+71, x+72, x+73, x+74, x+75, x+76, x+77, x+78, x+79, 93 x+80, x+81, x+82, x+83, x+84, x+85, x+86, x+87, x+88, x+89, 94 x+90, x+91, x+92, x+93, x+94, x+95, x+96, x+97, x+98, x+99, 95 x+100, x+101, x+102, x+103, x+104 FROM c; 96} 97 98do_execsql_test 210 {SELECT sum(c62) FROM t1;} 45620 99do_execsql_test 220 {SELECT sum(c63) FROM t1;} 45630 100do_execsql_test 230 {SELECT sum(c64) FROM t1;} 45640 101do_execsql_test 240 {SELECT sum(c65) FROM t1;} 45650 102 103do_execsql_test 300 { 104 BEGIN; 105 SELECT sum(c62) FROM t1; 106 UPDATE t1 SET c62=c62+1 WHERE c00=1000; 107 SELECT sum(c62) FROM t1; 108} {45620 45621} 109do_execsql_test 310 { 110 SELECT sum(c65) FROM t1; 111 UPDATE t1 SET c65=c65+1 WHERE c00=1000; 112 SELECT sum(c65) FROM t1; 113 ROLLBACK; 114} {45650 45651} 115 116do_execsql_test 320 { 117 BEGIN; 118 SELECT count(*) FROM t1; 119 DELETE FROM t1 WHERE c=3102; 120 SELECT COUNT(*) FROM t1; 121 ROLLBACK; 122} {10 9} 123do_execsql_test 330 { 124 BEGIN; 125 SELECT count(*) FROM t1; 126 DELETE FROM t1 WHERE c=3102 AND d=3103; 127 SELECT COUNT(*) FROM t1; 128 ROLLBACK; 129} {10 9} 130do_execsql_test 340 { 131 BEGIN; 132 DELETE FROM t1 WHERE (c,d) IN (VALUES(3102,3103),(4102,4103),(5102,5103),(1,2)); 133 SELECT count(*) FROM t1; 134 ROLLBACK; 135} {7} 136 137do_execsql_test 400 { 138 DROP INDEX t1cd; 139 DROP INDEX t1x1; 140 DROP INDEX t1x2; 141 CREATE INDEX t1x3 ON t1(c00,c05,c08); 142} 143do_execsql_test 410 {SELECT sum(c08) FROM t1 WHERE c00 IN (1000,5000);} 6016 144do_execsql_test 420 {SELECT sum(c63) FROM t1 WHERE c00 IN (1000,5000);} 6126 145do_execsql_test 430 {SELECT sum(c64) FROM t1 WHERE c00 IN (1000,5000);} 6128 146 147do_execsql_test 500 { 148 DROP INDEX t1x3; 149 CREATE TABLE t2 AS SELECT * FROM t1; 150 CREATE INDEX t1x4 ON t1(c00, c62, a, b); 151 CREATE INDEX t2x4 ON t2(c01, c62, c63, b, c); 152 SELECT t1.b, t2.b FROM t1 JOIN t2 ON t2.c01=t1.c00+1 WHERE +t1.b<7000 153 ORDER BY +t1.b; 154} {101 101 1101 1101 2101 2101 3101 3101 4101 4101 5101 5101 6101 6101} 155 156finish_test 157