/ Check-in [f3b39c71]
Login

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

Overview
Comment:Fix the NOT NULL verification logic in PRAGMA integrity_check so that it works for generated columns whose value is the result of a comparison operator. Ticket [bd8c280671ba44a7]
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: f3b39c71b88cb6721f443de56cdce4c08252453a5e340b00a2bd88dc10c42400
User & Date: drh 2019-12-09 15:52:07
Context
2019-12-09
17:14
Ensure that the SrcList_item.colUsed field is set correctly (set to have a 1 for all columns of the table) when a generated column appears in the USING clause of a join. (check-in: 1923efb2 user: drh tags: trunk)
15:52
Fix the NOT NULL verification logic in PRAGMA integrity_check so that it works for generated columns whose value is the result of a comparison operator. Ticket [bd8c280671ba44a7] (check-in: f3b39c71 user: drh tags: trunk)
14:34
Fix a gramfuzz find. If a partial index that does not reference any column of its table is used by an UPDATE statement in one-pass mode, then avoid the use of OP_DeferredSeek since the seek might not be resolved prior to the OP_Delete and OP_Insert that implement the UPDATE. (check-in: e3398c5f user: drh tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/pragma.c.

1592
1593
1594
1595
1596
1597
1598

1599

1600
1601
1602
1603
1604
1605
1606
        /* Verify that all NOT NULL columns really are NOT NULL */
        for(j=0; j<pTab->nCol; j++){
          char *zErr;
          int jmp2;
          if( j==pTab->iPKey ) continue;
          if( pTab->aCol[j].notNull==0 ) continue;
          sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, j, 3);

          sqlite3VdbeChangeP5(v, OPFLAG_TYPEOFARG);

          jmp2 = sqlite3VdbeAddOp1(v, OP_NotNull, 3); VdbeCoverage(v);
          zErr = sqlite3MPrintf(db, "NULL value in %s.%s", pTab->zName,
                              pTab->aCol[j].zName);
          sqlite3VdbeAddOp4(v, OP_String8, 0, 3, 0, zErr, P4_DYNAMIC);
          integrityCheckResultRow(v);
          sqlite3VdbeJumpHere(v, jmp2);
        }







>
|
>







1592
1593
1594
1595
1596
1597
1598
1599
1600
1601
1602
1603
1604
1605
1606
1607
1608
        /* Verify that all NOT NULL columns really are NOT NULL */
        for(j=0; j<pTab->nCol; j++){
          char *zErr;
          int jmp2;
          if( j==pTab->iPKey ) continue;
          if( pTab->aCol[j].notNull==0 ) continue;
          sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, j, 3);
          if( sqlite3VdbeGetOp(v,-1)->opcode==OP_Column ){
            sqlite3VdbeChangeP5(v, OPFLAG_TYPEOFARG);
          }
          jmp2 = sqlite3VdbeAddOp1(v, OP_NotNull, 3); VdbeCoverage(v);
          zErr = sqlite3MPrintf(db, "NULL value in %s.%s", pTab->zName,
                              pTab->aCol[j].zName);
          sqlite3VdbeAddOp4(v, OP_String8, 0, 3, 0, zErr, P4_DYNAMIC);
          integrityCheckResultRow(v);
          sqlite3VdbeJumpHere(v, jmp2);
        }

Changes to test/gencol1.test.

323
324
325
326
327
328
329
330














331
    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








>
>
>
>
>
>
>
>
>
>
>
>
>
>

323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
    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}}

# 2019-12-09 ticket bd8c280671ba44a7
# With generated columns, the sqlite3ExprGetColumnOfTable() routine might
# generate a code sequence that does not end with OP_Column.  So check to
# make sure that the last instruction generated is an OP_column prior to
# applying the OPFLAG_TYPEOFARG optimization to NOT NULL checks in the
# PRAGMA integrity_check code.
#
sqlite3 db :memory:
do_execsql_test gencol1-12.10 {
  CREATE TABLE t0 (c0, c1 NOT NULL AS (c0==0));
  INSERT INTO t0(c0) VALUES (0);
  PRAGMA integrity_check;
} {ok}

finish_test