| 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 |
|
| 16 |
set testdir [file dirname $argv0] |
| 17 |
source $testdir/tester.tcl |
| 18 |
set 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 |
# |
| 25 |
do_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 |
} |
| 39 |
do_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 |
|
| 54 |
reset_db |
| 55 |
do_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 |
|
| 98 |
do_execsql_test 210 {SELECT sum(c62) FROM t1;} 45620 |
| 99 |
do_execsql_test 220 {SELECT sum(c63) FROM t1;} 45630 |
| 100 |
do_execsql_test 230 {SELECT sum(c64) FROM t1;} 45640 |
| 101 |
do_execsql_test 240 {SELECT sum(c65) FROM t1;} 45650 |
| 102 |
|
| 103 |
do_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} |
| 109 |
do_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 |
|
| 116 |
do_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} |
| 123 |
do_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} |
| 130 |
do_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 |
|
| 137 |
do_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 |
} |
| 143 |
do_execsql_test 410 {SELECT sum(c08) FROM t1 WHERE c00 IN (1000,5000);} 6016 |
| 144 |
do_execsql_test 420 {SELECT sum(c63) FROM t1 WHERE c00 IN (1000,5000);} 6126 |
| 145 |
do_execsql_test 430 {SELECT sum(c64) FROM t1 WHERE c00 IN (1000,5000);} 6128 |
| 146 |
|
| 147 |
do_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 |
|
| 156 |
finish_test |