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