test/widetab1.test

   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