SQLite

Check-in [1d3760a5]
Login

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

Overview
Comment:Fix the Xfer-optimization on the INSERT statement so that it is omitted if there is a RETURNING clause, since that optimization is not able to deal with RETURNING. See forum thread 595e132f71 for details.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 1d3760a517b8bd2a6be82d2d5788945f49397cdc539fe28fd23e2c0c62a1dbe2
User & Date: drh 2022-02-28 16:44:58
Context
2022-03-05
19:39
Cherry-pick all bug fix changes since the 3.38.0 release. (check-in: 45a5d5ac user: drh tags: branch-3.38)
2022-03-01
14:13
Increase the max_page_count on ROLLBACK, if necessary, so that it is sufficient to cover the entire database. Fix for the problem identified by forum post 3b9e894312. (check-in: 12c01216 user: drh tags: trunk)
2022-02-28
16:44
Fix the Xfer-optimization on the INSERT statement so that it is omitted if there is a RETURNING clause, since that optimization is not able to deal with RETURNING. See forum thread 595e132f71 for details. (check-in: 1d3760a5 user: drh tags: trunk)
14:26
Reorganize the bits in Mem.flags. Free up one bit for reuse. (check-in: fe454291 user: drh tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/insert.c.

791
792
793
794
795
796
797



798

799
800
801
802
803
804
805
  **       INSERT INTO <table1> SELECT * FROM <table2>;
  **
  ** Then special optimizations can be applied that make the transfer
  ** very fast and which reduce fragmentation of indices.
  **
  ** This is the 2nd template.
  */



  if( pColumn==0 && xferOptimization(pParse, pTab, pSelect, onError, iDb) ){

    assert( !pTrigger );
    assert( pList==0 );
    goto insert_end;
  }
#endif /* SQLITE_OMIT_XFER_OPT */

  /* If this is an AUTOINCREMENT table, look up the sequence number in the







>
>
>
|
>







791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
  **       INSERT INTO <table1> SELECT * FROM <table2>;
  **
  ** Then special optimizations can be applied that make the transfer
  ** very fast and which reduce fragmentation of indices.
  **
  ** This is the 2nd template.
  */
  if( pColumn==0 
   && pSelect!=0
   && pTrigger==0
   && xferOptimization(pParse, pTab, pSelect, onError, iDb)
  ){
    assert( !pTrigger );
    assert( pList==0 );
    goto insert_end;
  }
#endif /* SQLITE_OMIT_XFER_OPT */

  /* If this is an AUTOINCREMENT table, look up the sequence number in the
2762
2763
2764
2765
2766
2767
2768
2769
2770
2771
2772
2773
2774
2775
2776
2777
2778
2779
2780
2781
2782
2783
2784
2785
2786
2787
  int emptyDestTest = 0;           /* Address of test for empty pDest */
  int emptySrcTest = 0;            /* Address of test for empty pSrc */
  Vdbe *v;                         /* The VDBE we are building */
  int regAutoinc;                  /* Memory register used by AUTOINC */
  int destHasUniqueIdx = 0;        /* True if pDest has a UNIQUE index */
  int regData, regRowid;           /* Registers holding data and rowid */

  if( pSelect==0 ){
    return 0;   /* Must be of the form  INSERT INTO ... SELECT ... */
  }
  if( pParse->pWith || pSelect->pWith ){
    /* Do not attempt to process this query if there are an WITH clauses
    ** attached to it. Proceeding may generate a false "no such table: xxx"
    ** error if pSelect reads from a CTE named "xxx".  */
    return 0;
  }
  if( sqlite3TriggerList(pParse, pDest) ){
    return 0;   /* tab1 must not have triggers */
  }
#ifndef SQLITE_OMIT_VIRTUALTABLE
  if( IsVirtual(pDest) ){
    return 0;   /* tab1 must not be a virtual table */
  }
#endif
  if( onError==OE_Default ){
    if( pDest->iPKey>=0 ) onError = pDest->keyConf;







|
<
<






<
<
<







2766
2767
2768
2769
2770
2771
2772
2773


2774
2775
2776
2777
2778
2779



2780
2781
2782
2783
2784
2785
2786
  int emptyDestTest = 0;           /* Address of test for empty pDest */
  int emptySrcTest = 0;            /* Address of test for empty pSrc */
  Vdbe *v;                         /* The VDBE we are building */
  int regAutoinc;                  /* Memory register used by AUTOINC */
  int destHasUniqueIdx = 0;        /* True if pDest has a UNIQUE index */
  int regData, regRowid;           /* Registers holding data and rowid */

  assert( pSelect!=0 );


  if( pParse->pWith || pSelect->pWith ){
    /* Do not attempt to process this query if there are an WITH clauses
    ** attached to it. Proceeding may generate a false "no such table: xxx"
    ** error if pSelect reads from a CTE named "xxx".  */
    return 0;
  }



#ifndef SQLITE_OMIT_VIRTUALTABLE
  if( IsVirtual(pDest) ){
    return 0;   /* tab1 must not be a virtual table */
  }
#endif
  if( onError==OE_Default ){
    if( pDest->iPKey>=0 ) onError = pDest->keyConf;

Changes to test/insert4.test.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2007 January 24
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the INSERT transfer optimization.
#
# $Id: insert4.test,v 1.10 2008/01/21 16:22:46 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix insert4

ifcapable !view||!subquery {
  finish_test













<







1
2
3
4
5
6
7
8
9
10
11
12
13

14
15
16
17
18
19
20
# 2007 January 24
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the INSERT transfer optimization.
#


set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix insert4

ifcapable !view||!subquery {
  finish_test
593
594
595
596
597
598
599







600
601
602
603
604
605
606

do_test 10.3 {
  execsql { PRAGMA integrity_check }
  set sqlite3_xferopt_count 0
  execsql { INSERT INTO x     SELECT * FROM t8 }
  set sqlite3_xferopt_count
} {1}








#-------------------------------------------------------------------------
# xfer transfer between tables where the source has an empty partial index.
#
do_execsql_test 11.0 {
  CREATE TABLE t9(a, b, c);
  CREATE INDEX t9a ON t9(a);







>
>
>
>
>
>
>







592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612

do_test 10.3 {
  execsql { PRAGMA integrity_check }
  set sqlite3_xferopt_count 0
  execsql { INSERT INTO x     SELECT * FROM t8 }
  set sqlite3_xferopt_count
} {1}

do_test 10.4 {
  execsql { PRAGMA integrity_check }
  set sqlite3_xferopt_count 0
  execsql { INSERT INTO x     SELECT * FROM t8  RETURNING * }
  set sqlite3_xferopt_count
} {0}

#-------------------------------------------------------------------------
# xfer transfer between tables where the source has an empty partial index.
#
do_execsql_test 11.0 {
  CREATE TABLE t9(a, b, c);
  CREATE INDEX t9a ON t9(a);

Changes to test/returning1.test.

357
358
359
360
361
362
363














364
365
} {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







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


357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
} {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}

# 2022-02-28 Forum post https://sqlite.org/forum/forumpost/595e132f71
# RETURNING with the xfer optimization
#
reset_db
do_execsql_test 16.0 {
  CREATE TABLE t1(a,b,c);
  INSERT INTO t1 VALUES(1,2,3),('a','b','c');
  CREATE TEMP TABLE t2(x,y,z);
  INSERT INTO t2 SELECT * FROM t1 RETURNING *;
} {1 2 3 a b c}
do_execsql_test 16.1 {
  SELECT * FROM t2;
} {1 2 3 a b c}

finish_test