Documentation Source Text

Check-in [3047c99f8a]
Login

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.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256:3047c99f8a503163d8bd5959e341fe6e7d8677fd6aff8f4416d25518d449d69e
User & Date: drh 2019-04-29 13:51:47
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
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/fileformat2.in.

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
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 one or more of the columns
of the table PRIMARY KEY are also columns of the index, 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);

</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> ^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>







|
|
|
|





>







 







>
>
>
>
>
>
>
>
>







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>