SQLite

Check-in [fa9d93cf]
Login

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

Overview
Comment:Fix generated columns so that they play well with upsert. See the forum post by "iffycan" for details.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: fa9d93cf32fac4b86044acf5d1b9ea2f36e964ed7142cf1d270986c9ef3fb766
User & Date: drh 2020-06-29 20:26:50
Context
2020-06-30
15:32
Avoid a potential buffer overread in fts3 when processing corrupt records. (check-in: 4d0cfb12 user: dan tags: trunk)
2020-06-29
20:26
Fix generated columns so that they play well with upsert. See the forum post by "iffycan" for details. (check-in: fa9d93cf user: drh tags: trunk)
20:20
Change the magic number used to identify the "excluded" pseudo-table in an UPSERT statement into a #define constant. (check-in: e96c2ac9 user: drh tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/resolve.c.

︙︙
419
420
421
422
423
424
425
426

427
428
429
430
431
432
433

          if( pExpr->iTable==EXCLUDED_TABLE_NUMBER ){
            testcase( iCol==(-1) );
            if( IN_RENAME_OBJECT ){
              pExpr->iColumn = iCol;
              pExpr->y.pTab = pTab;
              eNewExprOp = TK_COLUMN;
            }else{
              pExpr->iTable = pNC->uNC.pUpsert->regData + iCol;

              eNewExprOp = TK_REGISTER;
              ExprSetProperty(pExpr, EP_Alias);
            }
          }else
#endif /* SQLITE_OMIT_UPSERT */
          {
#ifndef SQLITE_OMIT_TRIGGER








|
>








419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434

          if( pExpr->iTable==EXCLUDED_TABLE_NUMBER ){
            testcase( iCol==(-1) );
            if( IN_RENAME_OBJECT ){
              pExpr->iColumn = iCol;
              pExpr->y.pTab = pTab;
              eNewExprOp = TK_COLUMN;
            }else{
              pExpr->iTable = pNC->uNC.pUpsert->regData +
                 sqlite3TableColumnToStorage(pTab, iCol);
              eNewExprOp = TK_REGISTER;
              ExprSetProperty(pExpr, EP_Alias);
            }
          }else
#endif /* SQLITE_OMIT_UPSERT */
          {
#ifndef SQLITE_OMIT_TRIGGER
︙︙

Changes to test/gencol1.test.

︙︙
555
556
557
558
559
560
561


























562
563

  CREATE TABLE t0(
    c0 INT AS(2) UNIQUE,
    c1 TEXT UNIQUE,
    FOREIGN KEY(c0) REFERENCES t0(c1)
  );
  INSERT INTO t0(c1) VALUES(0.16334143182538696), (0);
} {1 {UNIQUE constraint failed: t0.c0}}



























finish_test








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



555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589

  CREATE TABLE t0(
    c0 INT AS(2) UNIQUE,
    c1 TEXT UNIQUE,
    FOREIGN KEY(c0) REFERENCES t0(c1)
  );
  INSERT INTO t0(c1) VALUES(0.16334143182538696), (0);
} {1 {UNIQUE constraint failed: t0.c0}}

# 2020-06-29 forum bug report.
# https://sqlite.org/forum/forumpost/73b9a8ccfb
#
do_execsql_test gencol1-20.1 {
  CREATE TEMPORARY TABLE tab (
    prim DATE PRIMARY KEY,
    a INTEGER,
    comp INTEGER AS (a),
    b INTEGER,
    x INTEGER
  );
  -- Add some data
  INSERT INTO tab (prim, a, b) VALUES ('2001-01-01', 0, 0);
  -- Check that each column is 0 like I expect
  SELECT * FROM tab;
} {2001-01-01 0 0 0 {}}
do_execsql_test gencol1-20.2 {
  -- Do an UPSERT on the b column
  INSERT INTO tab (prim, b)
  VALUES ('2001-01-01',5)
      ON CONFLICT(prim) DO UPDATE SET  b=excluded.b;
  -- Now b is NULL rather than 5
  SELECT * FROM tab;
} {2001-01-01 0 0 5 {}}


finish_test