/ Check-in [340378c1]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:New test cases for PRAGMA index_xinfo on a WITHOUT ROWID table. And new testcases using index_xinfo to verify that WITHOUT ROWID tables are constructed correctly.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | wor-pk-dups
Files: files | file ages | folders
SHA3-256: 340378c1e60da80263523776f4b6366a9d332a7ee25986637e8b157f4e8e4bd3
User & Date: drh 2019-07-17 12:42:15
Context
2019-07-17
12:49
Fix the WITHOUT ROWID table logic so that it generates a correct KeyInfo object for tables that have a PRIMARY KEY containing the same column used more than once with different collating sequences. Enhance the index_xinfo pragma to assist in testing the above. Fix for ticket [fd3aec0c7e3e2998]. check-in: 84a51a75 user: drh tags: trunk
12:42
New test cases for PRAGMA index_xinfo on a WITHOUT ROWID table. And new testcases using index_xinfo to verify that WITHOUT ROWID tables are constructed correctly. Closed-Leaf check-in: 340378c1 user: drh tags: wor-pk-dups
11:01
Enhance the "PRAGMA index_info()" and "PRAGMA index_xinfo()" statements so that they allow a WITHOUT ROWID table as their argument, and in that case show the structure of the underlying index used to implement the WITHOUT ROWID table. check-in: 62274ff6 user: drh tags: wor-pk-dups
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/vtab1.test.

   870    870     }
   871    871   } {31429}
   872    872   do_test vtab1.7-13 {
   873    873     execsql {
   874    874       SELECT rowid, a, b, c FROM real_abc
   875    875     }
   876    876   } {}
          877  +
          878  +# PRAGMA index_info and index_xinfo are no-ops on a virtual table
          879  +do_test vtab1.7-14 {
          880  +  execsql {
          881  +    PRAGMA index_info('echo_abc');
          882  +    PRAGMA index_xinfo('echo_abc');
          883  +  }
          884  +} {}
   877    885   
   878    886   ifcapable attach {
   879    887     do_test vtab1.8-1 {
   880    888       set echo_module ""
   881    889       execsql {
   882    890         ATTACH 'test2.db' AS aux;
   883    891         CREATE VIRTUAL TABLE aux.e2 USING echo(real_abc);

Changes to test/without_rowid1.test.

    26     26     INSERT INTO t1 VALUES('dynamic','juliet','flipper','command');
    27     27     INSERT INTO t1 VALUES('journal','sherman','gamma','patriot');
    28     28     INSERT INTO t1 VALUES('arctic','sleep','ammonia','helena');
    29     29     SELECT *, '|' FROM t1 ORDER BY c, a;
    30     30   } {arctic sleep ammonia helena | journal sherman ammonia helena | dynamic juliet flipper command | journal sherman gamma patriot |}
    31     31   
    32     32   integrity_check without_rowid1-1.0ic
           33  +
           34  +do_execsql_test without_rowid1-1.0ixi {
           35  +  SELECT name, key FROM pragma_index_xinfo('t1');
           36  +} {c 1 a 1 b 0 d 0}
    33     37   
    34     38   do_execsql_test without_rowid1-1.1 {
    35     39     SELECT *, '|' FROM t1 ORDER BY +c, a;
    36     40   } {arctic sleep ammonia helena | journal sherman ammonia helena | dynamic juliet flipper command | journal sherman gamma patriot |}
    37     41   
    38     42   do_execsql_test without_rowid1-1.2 {
    39     43     SELECT *, '|' FROM t1 ORDER BY c DESC, a DESC;
................................................................................
   116    120     INSERT INTO t4 VALUES('abc', 'def');
   117    121     SELECT * FROM t4;
   118    122   } {abc def}
   119    123   do_execsql_test 2.1.2 {
   120    124     UPDATE t4 SET a = 'ABC';
   121    125     SELECT * FROM t4;
   122    126   } {ABC def}
          127  +do_execsql_test 2.1.3 {
          128  +  SELECT name, coll, key FROM pragma_index_xinfo('t4');
          129  +} {a nocase 1 b BINARY 0}
   123    130   
   124    131   do_execsql_test 2.2.1 {
   125    132     DROP TABLE t4;
   126    133     CREATE TABLE t4 (b, a COLLATE nocase PRIMARY KEY) WITHOUT ROWID;
   127    134     INSERT INTO t4(a, b) VALUES('abc', 'def');
   128    135     SELECT * FROM t4;
   129    136   } {def abc}
   130    137   
   131    138   do_execsql_test 2.2.2 {
   132    139     UPDATE t4 SET a = 'ABC', b = 'xyz';
   133    140     SELECT * FROM t4;
   134    141   } {xyz ABC}
          142  +
          143  +do_execsql_test 2.2.3 {
          144  +  SELECT name, coll, key FROM pragma_index_xinfo('t4');
          145  +} {a nocase 1 b BINARY 0}
          146  +
   135    147   
   136    148   do_execsql_test 2.3.1 {
   137    149     CREATE TABLE t5 (a, b, PRIMARY KEY(b, a)) WITHOUT ROWID;
   138    150     INSERT INTO t5(a, b) VALUES('abc', 'def');
   139    151     UPDATE t5 SET a='abc', b='def';
   140    152   } {}
          153  +
          154  +do_execsql_test 2.3.2 {
          155  +  SELECT name, coll, key FROM pragma_index_xinfo('t5');
          156  +} {b BINARY 1 a BINARY 1}
          157  +
   141    158   
   142    159   do_execsql_test 2.4.1 {
   143    160     CREATE TABLE t6 (
   144    161       a COLLATE nocase, b, c UNIQUE, PRIMARY KEY(b, a)
   145    162     ) WITHOUT ROWID;
   146    163   
   147    164     INSERT INTO t6(a, b, c) VALUES('abc', 'def', 'ghi');
................................................................................
   148    165     UPDATE t6 SET a='ABC', c='ghi';
   149    166   } {}
   150    167   
   151    168   do_execsql_test 2.4.2 {
   152    169     SELECT * FROM t6 ORDER BY b, a;
   153    170     SELECT * FROM t6 ORDER BY c;
   154    171   } {ABC def ghi ABC def ghi}
          172  +
          173  +do_execsql_test 2.4.3 {
          174  +  SELECT name, coll, key FROM pragma_index_xinfo('t6');
          175  +} {b BINARY 1 a nocase 1 c BINARY 0}
          176  +
   155    177   
   156    178   #-------------------------------------------------------------------------
   157    179   # Unless the destination table is completely empty, the xfer optimization 
   158    180   # is disabled for WITHOUT ROWID tables. The following tests check for
   159    181   # some problems that might occur if this were not the case.
   160    182   #
   161    183   reset_db

Changes to test/without_rowid6.test.

    20     20     CREATE TABLE t1(a,b,c,d,e, PRIMARY KEY(a,b,c,a,b,c,d,a,b,c)) WITHOUT ROWID;
    21     21     CREATE INDEX t1a ON t1(b, b);
    22     22     WITH RECURSIVE
    23     23       c(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM c WHERE i<1000)
    24     24     INSERT INTO t1(a,b,c,d,e) SELECT i, i+1000, printf('x%dy',i), 0, 0 FROM c;
    25     25     ANALYZE;
    26     26   } {}
           27  +do_execsql_test without_rowid6-101 {
           28  +  SELECT name, key FROM pragma_index_xinfo('t1');
           29  +} {a 1 b 1 c 1 d 1 e 0}
    27     30   do_execsql_test without_rowid6-110 {
    28     31     SELECT c FROM t1 WHERE a=123;
    29     32   } {x123y}
    30     33   do_execsql_test without_rowid6-120 {
    31     34     SELECT c FROM t1 WHERE b=1123;
    32     35   } {x123y}
    33     36   do_execsql_test without_rowid6-130 {
................................................................................
    47     50       b UNIQUE,
    48     51       c UNIQUE,
    49     52       PRIMARY KEY(b)
    50     53     ) WITHOUT ROWID;
    51     54     INSERT INTO t1(a,b,c) VALUES(1,8,3),(4,5,6),(7,2,9);
    52     55     SELECT a FROM t1 WHERE b>3 ORDER BY b;
    53     56   } {4 1}
           57  +do_execsql_test without_rowid6-201 {
           58  +  SELECT name, key FROM pragma_index_xinfo('t1');
           59  +} {b 1 a 0 c 0}
    54     60   do_execsql_test without_rowid6-210 {
    55     61     EXPLAIN QUERY PLAN
    56     62     SELECT a FROM t1 WHERE b>3 ORDER BY b;
    57     63   } {/SEARCH TABLE t1 USING PRIMARY KEY .b>../}
    58     64   do_execsql_test without_rowid6-220 {
    59     65     PRAGMA index_list(t1);
    60     66   } {/sqlite_autoindex_t1_2 1 pk/}
................................................................................
   101    107     CREATE TABLE t1(a,b,c,
   102    108       UNIQUE(b,c),
   103    109       PRIMARY KEY(b,c)
   104    110     ) WITHOUT ROWID;
   105    111     INSERT INTO t1(a,b,c) VALUES(1,8,3),(4,5,6),(7,2,9);
   106    112     SELECT a FROM t1 WHERE b>3 ORDER BY b;
   107    113   } {4 1}
          114  +do_execsql_test without_rowid6-501 {
          115  +  SELECT name, key FROM pragma_index_xinfo('t1');
          116  +} {b 1 c 1 a 0}
   108    117   do_execsql_test without_rowid6-510 {
   109    118     EXPLAIN QUERY PLAN
   110    119     SELECT a FROM t1 WHERE b>3 ORDER BY b;
   111    120   } {/SEARCH TABLE t1 USING PRIMARY KEY .b>../}
   112    121   do_execsql_test without_rowid6-520 {
   113    122     PRAGMA index_list(t1);
   114    123   } {/sqlite_autoindex_t1_1 1 pk/}