SQLite

Check-in [340378c1e6]
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
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.670
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: 84a51a755c 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: 340378c1e6 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: 62274ff683 user: drh tags: wor-pk-dups)
Changes
Unified Diff Ignore Whitespace Patch
Changes to test/vtab1.test.
︙︙
870
871
872
873
874
875
876








877
878
879
880
881
882
883

  }
} {31429}
do_test vtab1.7-13 {
  execsql {
    SELECT rowid, a, b, c FROM real_abc
  }
} {}









ifcapable attach {
  do_test vtab1.8-1 {
    set echo_module ""
    execsql {
      ATTACH 'test2.db' AS aux;
      CREATE VIRTUAL TABLE aux.e2 USING echo(real_abc);








>
>
>
>
>
>
>
>








870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891

  }
} {31429}
do_test vtab1.7-13 {
  execsql {
    SELECT rowid, a, b, c FROM real_abc
  }
} {}

# PRAGMA index_info and index_xinfo are no-ops on a virtual table
do_test vtab1.7-14 {
  execsql {
    PRAGMA index_info('echo_abc');
    PRAGMA index_xinfo('echo_abc');
  }
} {}

ifcapable attach {
  do_test vtab1.8-1 {
    set echo_module ""
    execsql {
      ATTACH 'test2.db' AS aux;
      CREATE VIRTUAL TABLE aux.e2 USING echo(real_abc);
︙︙
Changes to test/without_rowid1.test.
︙︙
26
27
28
29
30
31
32




33
34
35
36
37
38
39

  INSERT INTO t1 VALUES('dynamic','juliet','flipper','command');
  INSERT INTO t1 VALUES('journal','sherman','gamma','patriot');
  INSERT INTO t1 VALUES('arctic','sleep','ammonia','helena');
  SELECT *, '|' FROM t1 ORDER BY c, a;
} {arctic sleep ammonia helena | journal sherman ammonia helena | dynamic juliet flipper command | journal sherman gamma patriot |}

integrity_check without_rowid1-1.0ic





do_execsql_test without_rowid1-1.1 {
  SELECT *, '|' FROM t1 ORDER BY +c, a;
} {arctic sleep ammonia helena | journal sherman ammonia helena | dynamic juliet flipper command | journal sherman gamma patriot |}

do_execsql_test without_rowid1-1.2 {
  SELECT *, '|' FROM t1 ORDER BY c DESC, a DESC;








>
>
>
>








26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

  INSERT INTO t1 VALUES('dynamic','juliet','flipper','command');
  INSERT INTO t1 VALUES('journal','sherman','gamma','patriot');
  INSERT INTO t1 VALUES('arctic','sleep','ammonia','helena');
  SELECT *, '|' FROM t1 ORDER BY c, a;
} {arctic sleep ammonia helena | journal sherman ammonia helena | dynamic juliet flipper command | journal sherman gamma patriot |}

integrity_check without_rowid1-1.0ic

do_execsql_test without_rowid1-1.0ixi {
  SELECT name, key FROM pragma_index_xinfo('t1');
} {c 1 a 1 b 0 d 0}

do_execsql_test without_rowid1-1.1 {
  SELECT *, '|' FROM t1 ORDER BY +c, a;
} {arctic sleep ammonia helena | journal sherman ammonia helena | dynamic juliet flipper command | journal sherman gamma patriot |}

do_execsql_test without_rowid1-1.2 {
  SELECT *, '|' FROM t1 ORDER BY c DESC, a DESC;
︙︙
116
117
118
119
120
121
122



123
124
125
126
127
128
129
130
131
132
133
134





135
136
137
138
139
140





141
142
143
144
145
146
147
148
149
150
151
152
153
154





155
156
157
158
159
160
161

  INSERT INTO t4 VALUES('abc', 'def');
  SELECT * FROM t4;
} {abc def}
do_execsql_test 2.1.2 {
  UPDATE t4 SET a = 'ABC';
  SELECT * FROM t4;
} {ABC def}




do_execsql_test 2.2.1 {
  DROP TABLE t4;
  CREATE TABLE t4 (b, a COLLATE nocase PRIMARY KEY) WITHOUT ROWID;
  INSERT INTO t4(a, b) VALUES('abc', 'def');
  SELECT * FROM t4;
} {def abc}

do_execsql_test 2.2.2 {
  UPDATE t4 SET a = 'ABC', b = 'xyz';
  SELECT * FROM t4;
} {xyz ABC}






do_execsql_test 2.3.1 {
  CREATE TABLE t5 (a, b, PRIMARY KEY(b, a)) WITHOUT ROWID;
  INSERT INTO t5(a, b) VALUES('abc', 'def');
  UPDATE t5 SET a='abc', b='def';
} {}






do_execsql_test 2.4.1 {
  CREATE TABLE t6 (
    a COLLATE nocase, b, c UNIQUE, PRIMARY KEY(b, a)
  ) WITHOUT ROWID;

  INSERT INTO t6(a, b, c) VALUES('abc', 'def', 'ghi');
  UPDATE t6 SET a='ABC', c='ghi';
} {}

do_execsql_test 2.4.2 {
  SELECT * FROM t6 ORDER BY b, a;
  SELECT * FROM t6 ORDER BY c;
} {ABC def ghi ABC def ghi}






#-------------------------------------------------------------------------
# Unless the destination table is completely empty, the xfer optimization 
# is disabled for WITHOUT ROWID tables. The following tests check for
# some problems that might occur if this were not the case.
#
reset_db








>
>
>












>
>
>
>
>






>
>
>
>
>














>
>
>
>
>








120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183

  INSERT INTO t4 VALUES('abc', 'def');
  SELECT * FROM t4;
} {abc def}
do_execsql_test 2.1.2 {
  UPDATE t4 SET a = 'ABC';
  SELECT * FROM t4;
} {ABC def}
do_execsql_test 2.1.3 {
  SELECT name, coll, key FROM pragma_index_xinfo('t4');
} {a nocase 1 b BINARY 0}

do_execsql_test 2.2.1 {
  DROP TABLE t4;
  CREATE TABLE t4 (b, a COLLATE nocase PRIMARY KEY) WITHOUT ROWID;
  INSERT INTO t4(a, b) VALUES('abc', 'def');
  SELECT * FROM t4;
} {def abc}

do_execsql_test 2.2.2 {
  UPDATE t4 SET a = 'ABC', b = 'xyz';
  SELECT * FROM t4;
} {xyz ABC}

do_execsql_test 2.2.3 {
  SELECT name, coll, key FROM pragma_index_xinfo('t4');
} {a nocase 1 b BINARY 0}


do_execsql_test 2.3.1 {
  CREATE TABLE t5 (a, b, PRIMARY KEY(b, a)) WITHOUT ROWID;
  INSERT INTO t5(a, b) VALUES('abc', 'def');
  UPDATE t5 SET a='abc', b='def';
} {}

do_execsql_test 2.3.2 {
  SELECT name, coll, key FROM pragma_index_xinfo('t5');
} {b BINARY 1 a BINARY 1}


do_execsql_test 2.4.1 {
  CREATE TABLE t6 (
    a COLLATE nocase, b, c UNIQUE, PRIMARY KEY(b, a)
  ) WITHOUT ROWID;

  INSERT INTO t6(a, b, c) VALUES('abc', 'def', 'ghi');
  UPDATE t6 SET a='ABC', c='ghi';
} {}

do_execsql_test 2.4.2 {
  SELECT * FROM t6 ORDER BY b, a;
  SELECT * FROM t6 ORDER BY c;
} {ABC def ghi ABC def ghi}

do_execsql_test 2.4.3 {
  SELECT name, coll, key FROM pragma_index_xinfo('t6');
} {b BINARY 1 a nocase 1 c BINARY 0}


#-------------------------------------------------------------------------
# Unless the destination table is completely empty, the xfer optimization 
# is disabled for WITHOUT ROWID tables. The following tests check for
# some problems that might occur if this were not the case.
#
reset_db
︙︙
Changes to test/without_rowid6.test.
︙︙
20
21
22
23
24
25
26



27
28
29
30
31
32
33

  CREATE TABLE t1(a,b,c,d,e, PRIMARY KEY(a,b,c,a,b,c,d,a,b,c)) WITHOUT ROWID;
  CREATE INDEX t1a ON t1(b, b);
  WITH RECURSIVE
    c(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM c WHERE i<1000)
  INSERT INTO t1(a,b,c,d,e) SELECT i, i+1000, printf('x%dy',i), 0, 0 FROM c;
  ANALYZE;
} {}



do_execsql_test without_rowid6-110 {
  SELECT c FROM t1 WHERE a=123;
} {x123y}
do_execsql_test without_rowid6-120 {
  SELECT c FROM t1 WHERE b=1123;
} {x123y}
do_execsql_test without_rowid6-130 {








>
>
>








20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

  CREATE TABLE t1(a,b,c,d,e, PRIMARY KEY(a,b,c,a,b,c,d,a,b,c)) WITHOUT ROWID;
  CREATE INDEX t1a ON t1(b, b);
  WITH RECURSIVE
    c(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM c WHERE i<1000)
  INSERT INTO t1(a,b,c,d,e) SELECT i, i+1000, printf('x%dy',i), 0, 0 FROM c;
  ANALYZE;
} {}
do_execsql_test without_rowid6-101 {
  SELECT name, key FROM pragma_index_xinfo('t1');
} {a 1 b 1 c 1 d 1 e 0}
do_execsql_test without_rowid6-110 {
  SELECT c FROM t1 WHERE a=123;
} {x123y}
do_execsql_test without_rowid6-120 {
  SELECT c FROM t1 WHERE b=1123;
} {x123y}
do_execsql_test without_rowid6-130 {
︙︙
47
48
49
50
51
52
53



54
55
56
57
58
59
60

    b UNIQUE,
    c UNIQUE,
    PRIMARY KEY(b)
  ) WITHOUT ROWID;
  INSERT INTO t1(a,b,c) VALUES(1,8,3),(4,5,6),(7,2,9);
  SELECT a FROM t1 WHERE b>3 ORDER BY b;
} {4 1}



do_execsql_test without_rowid6-210 {
  EXPLAIN QUERY PLAN
  SELECT a FROM t1 WHERE b>3 ORDER BY b;
} {/SEARCH TABLE t1 USING PRIMARY KEY .b>../}
do_execsql_test without_rowid6-220 {
  PRAGMA index_list(t1);
} {/sqlite_autoindex_t1_2 1 pk/}








>
>
>








50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66

    b UNIQUE,
    c UNIQUE,
    PRIMARY KEY(b)
  ) WITHOUT ROWID;
  INSERT INTO t1(a,b,c) VALUES(1,8,3),(4,5,6),(7,2,9);
  SELECT a FROM t1 WHERE b>3 ORDER BY b;
} {4 1}
do_execsql_test without_rowid6-201 {
  SELECT name, key FROM pragma_index_xinfo('t1');
} {b 1 a 0 c 0}
do_execsql_test without_rowid6-210 {
  EXPLAIN QUERY PLAN
  SELECT a FROM t1 WHERE b>3 ORDER BY b;
} {/SEARCH TABLE t1 USING PRIMARY KEY .b>../}
do_execsql_test without_rowid6-220 {
  PRAGMA index_list(t1);
} {/sqlite_autoindex_t1_2 1 pk/}
︙︙
101
102
103
104
105
106
107



108
109
110
111
112
113
114

  CREATE TABLE t1(a,b,c,
    UNIQUE(b,c),
    PRIMARY KEY(b,c)
  ) WITHOUT ROWID;
  INSERT INTO t1(a,b,c) VALUES(1,8,3),(4,5,6),(7,2,9);
  SELECT a FROM t1 WHERE b>3 ORDER BY b;
} {4 1}



do_execsql_test without_rowid6-510 {
  EXPLAIN QUERY PLAN
  SELECT a FROM t1 WHERE b>3 ORDER BY b;
} {/SEARCH TABLE t1 USING PRIMARY KEY .b>../}
do_execsql_test without_rowid6-520 {
  PRAGMA index_list(t1);
} {/sqlite_autoindex_t1_1 1 pk/}








>
>
>








107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123

  CREATE TABLE t1(a,b,c,
    UNIQUE(b,c),
    PRIMARY KEY(b,c)
  ) WITHOUT ROWID;
  INSERT INTO t1(a,b,c) VALUES(1,8,3),(4,5,6),(7,2,9);
  SELECT a FROM t1 WHERE b>3 ORDER BY b;
} {4 1}
do_execsql_test without_rowid6-501 {
  SELECT name, key FROM pragma_index_xinfo('t1');
} {b 1 c 1 a 0}
do_execsql_test without_rowid6-510 {
  EXPLAIN QUERY PLAN
  SELECT a FROM t1 WHERE b>3 ORDER BY b;
} {/SEARCH TABLE t1 USING PRIMARY KEY .b>../}
do_execsql_test without_rowid6-520 {
  PRAGMA index_list(t1);
} {/sqlite_autoindex_t1_1 1 pk/}
︙︙