/ Check-in [5a9da62a]
Login

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

Overview
Comment:Fix a bug reported on the mailing list concerning a conflict between "INSERT INTO ... SELECT" statements and the "SELECT max(x) FROM tbl" optimization. (CVS 2227)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 5a9da62ae303800ded99942aed30eadeb3863da3
User & Date: danielk1977 2005-01-17 08:57:09
Context
2005-01-17
22:08
Infrastructure changes to handle name resolution differently. This is needed to fix various long-standing problems with column names in joins. It will also make the implementation of correlated subqueries easier. (CVS 2228) check-in: 4a753439 user: drh tags: trunk
08:57
Fix a bug reported on the mailing list concerning a conflict between "INSERT INTO ... SELECT" statements and the "SELECT max(x) FROM tbl" optimization. (CVS 2227) check-in: 5a9da62a user: danielk1977 tags: trunk
07:53
Fix some memory leak problems with corrupt.test and auto-vacuum databases. (CVS 2226) check-in: 62442529 user: danielk1977 tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
....
2126
2127
2128
2129
2130
2131
2132








2133
2134
2135
2136
2137
2138
2139
2140
2141
2142
2143
2144
2145
2146
2147
2148
2149
2150
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.221 2005/01/15 01:52:32 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
  int base;
  Vdbe *v;
  int seekOp;
  int cont;
  ExprList *pEList, *pList, eList;
  struct ExprList_item eListItem;
  SrcList *pSrc;
  

  /* Check to see if this query is a simple min() or max() query.  Return
  ** zero if it is  not.
  */
  if( p->pGroupBy || p->pHaving || p->pWhere ) return 0;
  pSrc = p->pSrc;
  if( pSrc->nSrc!=1 ) return 0;
................................................................................
  if( pSrc->a[0].pSelect==0 ){
    sqlite3OpenTableForReading(v, base, pTab);
  }
  cont = sqlite3VdbeMakeLabel(v);
  if( pIdx==0 ){
    sqlite3VdbeAddOp(v, seekOp, base, 0);
  }else{








    sqlite3VdbeAddOp(v, OP_Integer, pIdx->iDb, 0);
    sqlite3VdbeOp3(v, OP_OpenRead, base+1, pIdx->tnum,
                   (char*)&pIdx->keyInfo, P3_KEYINFO);
    if( seekOp==OP_Rewind ){
      sqlite3VdbeAddOp(v, OP_String, 0, 0);
      sqlite3VdbeAddOp(v, OP_MakeRecord, 1, 0);
      seekOp = OP_MoveGt;
    }
    sqlite3VdbeAddOp(v, seekOp, base+1, 0);
    sqlite3VdbeAddOp(v, OP_IdxRecno, base+1, 0);
    sqlite3VdbeAddOp(v, OP_Close, base+1, 0);
    sqlite3VdbeAddOp(v, OP_MoveGe, base, 0);
  }
  eList.nExpr = 1;
  memset(&eListItem, 0, sizeof(eListItem));
  eList.a = &eListItem;
  eList.a[0].pExpr = pExpr;
  selectInnerLoop(pParse, p, &eList, 0, 0, 0, -1, eDest, iParm, cont, cont, 0);







|







 







<







 







>
>
>
>
>
>
>
>

|






|
|
|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
2053
2054
2055
2056
2057
2058
2059

2060
2061
2062
2063
2064
2065
2066
....
2125
2126
2127
2128
2129
2130
2131
2132
2133
2134
2135
2136
2137
2138
2139
2140
2141
2142
2143
2144
2145
2146
2147
2148
2149
2150
2151
2152
2153
2154
2155
2156
2157
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.222 2005/01/17 08:57:09 danielk1977 Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
  int base;
  Vdbe *v;
  int seekOp;
  int cont;
  ExprList *pEList, *pList, eList;
  struct ExprList_item eListItem;
  SrcList *pSrc;


  /* Check to see if this query is a simple min() or max() query.  Return
  ** zero if it is  not.
  */
  if( p->pGroupBy || p->pHaving || p->pWhere ) return 0;
  pSrc = p->pSrc;
  if( pSrc->nSrc!=1 ) return 0;
................................................................................
  if( pSrc->a[0].pSelect==0 ){
    sqlite3OpenTableForReading(v, base, pTab);
  }
  cont = sqlite3VdbeMakeLabel(v);
  if( pIdx==0 ){
    sqlite3VdbeAddOp(v, seekOp, base, 0);
  }else{
    /* Even though the cursor used to open the index here is closed
    ** as soon as a single value has been read from it, allocate it
    ** using (pParse->nTab++) to prevent the cursor id from being 
    ** reused. This is important for statements of the form 
    ** "INSERT INTO x SELECT max() FROM x".
    */
    int iIdx;
    iIdx = pParse->nTab++;
    sqlite3VdbeAddOp(v, OP_Integer, pIdx->iDb, 0);
    sqlite3VdbeOp3(v, OP_OpenRead, iIdx, pIdx->tnum,
                   (char*)&pIdx->keyInfo, P3_KEYINFO);
    if( seekOp==OP_Rewind ){
      sqlite3VdbeAddOp(v, OP_String, 0, 0);
      sqlite3VdbeAddOp(v, OP_MakeRecord, 1, 0);
      seekOp = OP_MoveGt;
    }
    sqlite3VdbeAddOp(v, seekOp, iIdx, 0);
    sqlite3VdbeAddOp(v, OP_IdxRecno, iIdx, 0);
    sqlite3VdbeAddOp(v, OP_Close, iIdx, 0);
    sqlite3VdbeAddOp(v, OP_MoveGe, base, 0);
  }
  eList.nExpr = 1;
  memset(&eListItem, 0, sizeof(eListItem));
  eList.a = &eListItem;
  eList.a[0].pExpr = pExpr;
  selectInnerLoop(pParse, p, &eList, 0, 0, 0, -1, eDest, iParm, cont, cont, 0);

Changes to test/insert.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
299
300
301
302
303
304
305























306
307
308
309
#    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 statement.
#
# $Id: insert.test,v 1.21 2004/11/10 15:27:38 danielk1977 Exp $

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

# Try to insert into a non-existant table.
#
do_test insert-1.1 {
................................................................................
  }
} {}
ifcapable {reindex} {
  do_test insert-6.7 {
    execsql REINDEX
  } {}
}
























integrity_check insert-99.0

finish_test







|







 







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




7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
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
332
#    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 statement.
#
# $Id: insert.test,v 1.22 2005/01/17 08:57:09 danielk1977 Exp $

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

# Try to insert into a non-existant table.
#
do_test insert-1.1 {
................................................................................
  }
} {}
ifcapable {reindex} {
  do_test insert-6.7 {
    execsql REINDEX
  } {}
}

# Test that the special optimization for queries of the form 
# "SELECT max(x) FROM tbl" where there is an index on tbl(x) works with 
# INSERT statments.
do_test insert-7.1 {
  execsql {
    DROP TABLE t1;
    CREATE TABLE t1(a);
    INSERT INTO t1 VALUES(1);
    INSERT INTO t1 VALUES(2);
    CREATE INDEX i1 ON t1(a);
  }
} {}
do_test insert-7.2 {
  execsql {
    INSERT INTO t1 SELECT max(a) FROM t1;
  }
} {}
do_test insert-7.3 {
  execsql {
    SELECT a FROM t1;
  }
} {1 2 2}

integrity_check insert-99.0

finish_test