Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix a problem with foreign keys and generated columns discovered by Manuel Rigger. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
27c0fdab1ba4d4993b164eb4b777c63e |
| User & Date: | drh 2019-12-07 00:22:18 |
Context
|
2019-12-07
| ||
| 12:13 | Ignore type affinity for auxiliary columns in RTREE as it is too error-prone to try to parse out the type from other constraints. Ticket [8bf76328ac940d52] (check-in: 7fa664ea user: drh tags: trunk) | |
| 00:22 | Fix a problem with foreign keys and generated columns discovered by Manuel Rigger. (check-in: 27c0fdab user: drh tags: trunk) | |
|
2019-12-06
| ||
| 19:07 | Early detection and reporting of too few columns in an rtree CREATE VIRTUAL TABLE statement. (check-in: ef73107f user: drh tags: trunk) | |
Changes
Changes to src/build.c.
| ︙ | ︙ | |||
943 944 945 946 947 948 949 | ** INPUTS: 0 1 2 3 4 5 6 7 8 ** OUTPUTS: 0 1 6 2 3 7 4 5 8 ** ** So, in other words, this routine shifts all the virtual columns to ** the end. ** ** If SQLITE_OMIT_GENERATED_COLUMNS then there are no virtual columns and | | > > | | 943 944 945 946 947 948 949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 |
** INPUTS: 0 1 2 3 4 5 6 7 8
** OUTPUTS: 0 1 6 2 3 7 4 5 8
**
** So, in other words, this routine shifts all the virtual columns to
** the end.
**
** If SQLITE_OMIT_GENERATED_COLUMNS then there are no virtual columns and
** this routine is a no-op macro. If the pTab does not have any virtual
** columns, then this routine is no-op that always return iCol. If iCol
** is negative (indicating the ROWID column) then this routine return iCol.
*/
i16 sqlite3TableColumnToStorage(Table *pTab, i16 iCol){
int i;
i16 n;
assert( iCol<pTab->nCol );
if( (pTab->tabFlags & TF_HasVirtual)==0 || iCol<0 ) return iCol;
for(i=0, n=0; i<iCol; i++){
if( (pTab->aCol[i].colFlags & COLFLAG_VIRTUAL)==0 ) n++;
}
if( pTab->aCol[i].colFlags & COLFLAG_VIRTUAL ){
/* iCol is a virtual column itself */
return pTab->nNVCol + i - n;
}else{
|
| ︙ | ︙ |
Changes to src/expr.c.
| ︙ | ︙ | |||
4132 4133 4134 4135 4136 4137 4138 |
** p1==0 -> old.rowid p1==3 -> new.rowid
** p1==1 -> old.a p1==4 -> new.a
** p1==2 -> old.b p1==5 -> new.b
*/
Table *pTab = pExpr->y.pTab;
int iCol = pExpr->iColumn;
int p1 = pExpr->iTable * (pTab->nCol+1) + 1
| | | 4132 4133 4134 4135 4136 4137 4138 4139 4140 4141 4142 4143 4144 4145 4146 |
** p1==0 -> old.rowid p1==3 -> new.rowid
** p1==1 -> old.a p1==4 -> new.a
** p1==2 -> old.b p1==5 -> new.b
*/
Table *pTab = pExpr->y.pTab;
int iCol = pExpr->iColumn;
int p1 = pExpr->iTable * (pTab->nCol+1) + 1
+ sqlite3TableColumnToStorage(pTab, iCol);
assert( pExpr->iTable==0 || pExpr->iTable==1 );
assert( iCol>=-1 && iCol<pTab->nCol );
assert( pTab->iPKey<0 || iCol!=pTab->iPKey );
assert( p1>=0 && p1<(pTab->nCol*2+2) );
sqlite3VdbeAddOp2(v, OP_Param, p1, target);
|
| ︙ | ︙ |
Changes to test/gencol1.test.
| ︙ | ︙ | |||
268 269 270 271 272 273 274 275 |
sqlite3 db :memory:
do_execsql_test gencol1-10.10 {
CREATE TABLE t1(aa,bb);
CREATE TABLE IF NOT EXISTS t1(aa, bb AS (aa+1));
PRAGMA integrity_check;
} {ok}
finish_test
| > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 |
sqlite3 db :memory:
do_execsql_test gencol1-10.10 {
CREATE TABLE t1(aa,bb);
CREATE TABLE IF NOT EXISTS t1(aa, bb AS (aa+1));
PRAGMA integrity_check;
} {ok}
# 2019-12-06 Found by mrigger
#
sqlite3 db :memory:
do_execsql_test gencol1-11.10 {
PRAGMA foreign_keys = true;
CREATE TABLE t0(
c0,
c1 INTEGER PRIMARY KEY,
c2 BLOB UNIQUE DEFAULT x'00',
c3 BLOB GENERATED ALWAYS AS (1),
FOREIGN KEY(c1) REFERENCES t0(c2)
);
}
do_catchsql_test gencol1-11.20 {
INSERT OR REPLACE INTO t0(c0, c1) VALUES (2, 1), (1, 0)
} {1 {FOREIGN KEY constraint failed}}
do_execsql_test gencol1-11.30 {
DROP TABLE t0;
CREATE TABLE t0(
c0,
c1 INTEGER PRIMARY KEY,
c3 BLOB GENERATED ALWAYS AS (1),
c2 BLOB UNIQUE DEFAULT x'00',
FOREIGN KEY(c1) REFERENCES t0(c2)
);
}
do_catchsql_test gencol1-11.40 {
INSERT OR REPLACE INTO t0(c0, c1) VALUES (2, 1), (1, 0)
} {1 {FOREIGN KEY constraint failed}}
do_execsql_test gencol1-11.50 {
DROP TABLE t0;
CREATE TABLE t0(
c0,
c3 BLOB GENERATED ALWAYS AS (1),
c1 INTEGER PRIMARY KEY,
c2 BLOB UNIQUE DEFAULT x'00',
FOREIGN KEY(c1) REFERENCES t0(c2)
);
}
do_catchsql_test gencol1-11.60 {
INSERT OR REPLACE INTO t0(c0, c1) VALUES (2, 1), (1, 0)
} {1 {FOREIGN KEY constraint failed}}
do_execsql_test gencol1-11.70 {
DROP TABLE t0;
CREATE TABLE t0(
c3 BLOB GENERATED ALWAYS AS (1),
c0,
c1 INTEGER PRIMARY KEY,
c2 BLOB UNIQUE DEFAULT x'00',
FOREIGN KEY(c1) REFERENCES t0(c2)
);
}
do_catchsql_test gencol1-11.80 {
INSERT OR REPLACE INTO t0(c0, c1) VALUES (2, 1), (1, 0)
} {1 {FOREIGN KEY constraint failed}}
finish_test
|