Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Update the file-format document to indicate that de-duplication of index columns in WITHOUT ROWID tables does not occur if the columns have different collating sequences. |
|---|---|
| Downloads: | Tarball | ZIP archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
3047c99f8a503163d8bd5959e341fe6e |
| User & Date: | drh 2019-04-29 13:51:47.224 |
Context
|
2019-04-29
| ||
| 16:21 | Merge changes from the 3.28 branch. (check-in: d220b2f30c user: drh tags: trunk) | |
| 13:51 | Update the file-format document to indicate that de-duplication of index columns in WITHOUT ROWID tables does not occur if the columns have different collating sequences. (check-in: 3047c99f8a user: drh tags: trunk) | |
|
2019-04-28
| ||
| 00:46 | Fix althttpd.c so that it builds on non-linux machines. (check-in: 02b0d4d406 user: drh tags: trunk) | |
Changes
Changes to pages/fileformat2.in.
| ︙ | ︙ | |||
1117 1118 1119 1120 1121 1122 1123 | CREATE INDEX statement is true. ^Corresponding rows in the index and table b-trees share the same rowid or primary key values and contain the same value for all indexed columns.</p> <h3>Suppression of redundant columns in WITHOUT ROWID secondary indexes </h3> | | | | | > > > > > > > > > > | 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 |
CREATE INDEX statement is true.
^Corresponding rows in the index and table b-trees share the same rowid
or primary key values and contain the same value for all indexed columns.</p>
<h3>Suppression of redundant columns in WITHOUT ROWID secondary indexes
</h3>
<p> ^In an index on a WITHOUT ROWID table, if a column of the PRIMARY KEY
is also a column in the index and has a matching collating sequence, then the
indexed column is not repeated in the table-key suffix on the
end of the index record. ^(As an example, consider the following SQL:
<blockquote><pre>
CREATE TABLE ex25(a,b,c,d,e,PRIMARY KEY(d,c,a)) WITHOUT rowid;
CREATE INDEX ex25ce ON ex25(c,e);
CREATE INDEX ex25acde ON ex25(a,c,d,e);
CREATE INDEX ex25ae ON ex25(a COLLATE nocase,e);
</pre></blockquote>
<p>Each row in the ex25ce index is a record
with these columns: c, e, d, a. The first two columns are
the columns being indexed, c and e. The remaining columns are the primary
key of the corresponding table row. Normally, the primary key would be
columns d, c, and a, but because column c already appears earlier in the
index, it is omitted from the key suffix.)^</p>
<p>^(In the extreme case where the columns being indexed cover all columns
of the PRIMARY KEY, the index will consist of only the columns being
indexed. The ex25acde example above demonstrates this.)^ ^Each entry in
the ex25acde index consists of only the columns a, c, d, and e, in that
order.</p>
<p>Each row in ex25ae contains five columns: c, e, d, c, a. The c
column is repeated since the first occurrance of c has a collating
function of "nocase" and the second has a collating sequence of "binary".
If the c column is not repeated and if the table contains two or more
entries with the same e value and where c differs only in case, then
all of those table entries would correspond to a single entry in the
index, which would break the one-to-one correspondence between the table
and the index.
<p> ^The suppression of redundant columns in the key suffix of an index
entry only occurs in WITHOUT ROWID tables. ^In an ordinary rowid table,
the index entry always ends with the rowid even if the [INTEGER PRIMARY KEY]
column is one of the columns being indexed.</p>
<tcl>hd_fragment sqlite_master {sqlite_master} {sqlite_master table}</tcl>
|
| ︙ | ︙ |