SQLite

Check-in [4711fb69]
Login

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

Overview
Comment:Ensure that the affinity of columns is honored in the RETURNING clause. See forum post e0c7574ab2 for the bug report.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 4711fb69547f4f17653ab116030c32fdcc2c836410349d1d025866ffc15704da
User & Date: drh 2021-12-29 04:31:54
Context
2021-12-29
13:32
Minor changes to shell1.test test results to account for the new feature of the shell that points to the specific part of the input line where the error occurs. (check-in: d4870c08 user: drh tags: trunk)
04:31
Ensure that the affinity of columns is honored in the RETURNING clause. See forum post e0c7574ab2 for the bug report. (check-in: 4711fb69 user: drh tags: trunk)
04:13
Add the sqlite3_error_offset() interface. Use it to enhance error reporting in the CLI. (check-in: 416602a8 user: drh tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/resolve.c.

︙︙
449
450
451
452
453
454
455

456
457
458
459
460
461
462

          }else
#endif /* SQLITE_OMIT_UPSERT */
          {
            assert( ExprUseYTab(pExpr) );
            pExpr->y.pTab = pTab;
            if( pParse->bReturning ){
              eNewExprOp = TK_REGISTER;

              pExpr->iTable = pNC->uNC.iBaseReg + (pTab->nCol+1)*pExpr->iTable +
                 sqlite3TableColumnToStorage(pTab, iCol) + 1;
            }else{
              pExpr->iColumn = (i16)iCol;
              eNewExprOp = TK_TRIGGER;
#ifndef SQLITE_OMIT_TRIGGER
              if( iCol<0 ){








>








449
450
451
452
453
454
455
456
457
458
459
460
461
462
463

          }else
#endif /* SQLITE_OMIT_UPSERT */
          {
            assert( ExprUseYTab(pExpr) );
            pExpr->y.pTab = pTab;
            if( pParse->bReturning ){
              eNewExprOp = TK_REGISTER;
              pExpr->op2 = TK_COLUMN;
              pExpr->iTable = pNC->uNC.iBaseReg + (pTab->nCol+1)*pExpr->iTable +
                 sqlite3TableColumnToStorage(pTab, iCol) + 1;
            }else{
              pExpr->iColumn = (i16)iCol;
              eNewExprOp = TK_TRIGGER;
#ifndef SQLITE_OMIT_TRIGGER
              if( iCol<0 ){
︙︙

Changes to src/trigger.c.

︙︙
949
950
951
952
953
954
955


956



957
958
959
960
961
962
963

      int i;
      int nCol = pNew->nExpr;
      int reg = pParse->nMem+1;
      pParse->nMem += nCol+2;
      pReturning->iRetReg = reg;
      for(i=0; i<nCol; i++){
        Expr *pCol = pNew->a[i].pExpr;


        sqlite3ExprCodeFactorable(pParse, pCol, reg+i);



      }
      sqlite3VdbeAddOp3(v, OP_MakeRecord, reg, i, reg+i);
      sqlite3VdbeAddOp2(v, OP_NewRowid, pReturning->iRetCur, reg+i+1);
      sqlite3VdbeAddOp3(v, OP_Insert, pReturning->iRetCur, reg+i, reg+i+1);
    }
    sqlite3ExprListDelete(db, pNew);
    pParse->eTriggerOp = 0;








>
>

>
>
>








949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968

      int i;
      int nCol = pNew->nExpr;
      int reg = pParse->nMem+1;
      pParse->nMem += nCol+2;
      pReturning->iRetReg = reg;
      for(i=0; i<nCol; i++){
        Expr *pCol = pNew->a[i].pExpr;
        assert( pCol!=0 || pParse->db->mallocFailed );
        if( pCol==0 ) continue;
        sqlite3ExprCodeFactorable(pParse, pCol, reg+i);
        if( sqlite3ExprAffinity(pCol)==SQLITE_AFF_REAL ){
          sqlite3VdbeAddOp1(v, OP_RealAffinity, reg+i);
        }
      }
      sqlite3VdbeAddOp3(v, OP_MakeRecord, reg, i, reg+i);
      sqlite3VdbeAddOp2(v, OP_NewRowid, pReturning->iRetCur, reg+i+1);
      sqlite3VdbeAddOp3(v, OP_Insert, pReturning->iRetCur, reg+i, reg+i+1);
    }
    sqlite3ExprListDelete(db, pNew);
    pParse->eTriggerOp = 0;
︙︙

Changes to test/returning1.test.

︙︙
341
342
343
344
345
346
347
















348
349

  PRAGMA foreign_keys(1);
  CREATE TABLE Parent(id INTEGER PRIMARY KEY);
  CREATE TABLE Child(id INTEGER PRIMARY KEY, parent_id INTEGER REFERENCES Parent(id));
} {}
do_catchsql_test 14.1 {
  INSERT INTO child(parent_id) VALUES(123) RETURNING id;
} {1 {FOREIGN KEY constraint failed}}

















finish_test








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



341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365

  PRAGMA foreign_keys(1);
  CREATE TABLE Parent(id INTEGER PRIMARY KEY);
  CREATE TABLE Child(id INTEGER PRIMARY KEY, parent_id INTEGER REFERENCES Parent(id));
} {}
do_catchsql_test 14.1 {
  INSERT INTO child(parent_id) VALUES(123) RETURNING id;
} {1 {FOREIGN KEY constraint failed}}

# 2021-12-28 Forum post https://sqlite.org/forum/forumpost/e0c7574ab2
# Incorrect affinity for REAL values that can be represented as integers.
#
reset_db
sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS db
do_execsql_test 15.0 {
  CREATE TABLE t1(x REAL);
  INSERT INTO t1(x) VALUES(5.0) RETURNING x, affinity(x);
} {5.0 real}
do_execsql_test 15.1 {
  UPDATE t1 SET x=x+1 RETURNING x, affinity(x);
} {6.0 real}
do_execsql_test 15.2 {
  DELETE FROM t1 RETURNING x, affinity(x);
} {6.0 real}

finish_test