/ Check-in [f7858d88]
Login

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

Overview
Comment:Fix a problem with sub-queries and the flattening optimization. Also handle an extra case of database corruption. (CVS 2324)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: f7858d8830cdd0f57b8f9bc73068d29a7062b8ac
User & Date: danielk1977 2005-02-12 08:59:56
Context
2005-02-13
23:34
Update the version number in preparation for the release of 3.1.2. (CVS 2325) check-in: f3c51de7 user: drh tags: trunk
2005-02-12
08:59
Fix a problem with sub-queries and the flattening optimization. Also handle an extra case of database corruption. (CVS 2324) check-in: f7858d88 user: danielk1977 tags: trunk
00:19
Fix a problem with VACUUM that can lead to database corruption. (CVS 2323) check-in: 63894baf user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/btree.c.

5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
....
3218
3219
3220
3221
3222
3223
3224



3225
3226
3227
3228
3229
3230
3231
....
4761
4762
4763
4764
4765
4766
4767




4768
4769
4770
4771
4772
4773
4774
** 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.
**
*************************************************************************
** $Id: btree.c,v 1.246 2005/02/06 02:45:42 drh Exp $
**
** This file implements a external (disk-based) database using BTrees.
** For a detailed discussion of BTrees, refer to
**
**     Donald E. Knuth, THE ART OF COMPUTER PROGRAMMING, Volume 3:
**     "Sorting And Searching", pages 473-480. Addison-Wesley
**     Publishing Company, Reading, Massachusetts.
................................................................................
  parseCellPtr(pPage, pCell, &info);
  if( info.iOverflow==0 ){
    return SQLITE_OK;  /* No overflow pages. Return without doing anything */
  }
  ovflPgno = get4byte(&pCell[info.iOverflow]);
  while( ovflPgno!=0 ){
    MemPage *pOvfl;



    rc = getPage(pBt, ovflPgno, &pOvfl);
    if( rc ) return rc;
    ovflPgno = get4byte(pOvfl->aData);
    rc = freePage(pOvfl);
    if( rc ) return rc;
    sqlite3pager_unref(pOvfl->aData);
  }
................................................................................
  MemPage *pParent,     /* Parent page.  NULL for the root */
  int freePageFlag      /* Deallocate page if true */
){
  MemPage *pPage;
  int rc;
  unsigned char *pCell;
  int i;





  rc = getAndInitPage(pBt, pgno, &pPage, pParent);
  if( rc ) return rc;
  rc = sqlite3pager_write(pPage->aData);
  if( rc ) return rc;
  for(i=0; i<pPage->nCell; i++){
    pCell = findCell(pPage, i);







|







 







>
>
>







 







>
>
>
>







5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
....
3218
3219
3220
3221
3222
3223
3224
3225
3226
3227
3228
3229
3230
3231
3232
3233
3234
....
4764
4765
4766
4767
4768
4769
4770
4771
4772
4773
4774
4775
4776
4777
4778
4779
4780
4781
** 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.
**
*************************************************************************
** $Id: btree.c,v 1.247 2005/02/12 08:59:56 danielk1977 Exp $
**
** This file implements a external (disk-based) database using BTrees.
** For a detailed discussion of BTrees, refer to
**
**     Donald E. Knuth, THE ART OF COMPUTER PROGRAMMING, Volume 3:
**     "Sorting And Searching", pages 473-480. Addison-Wesley
**     Publishing Company, Reading, Massachusetts.
................................................................................
  parseCellPtr(pPage, pCell, &info);
  if( info.iOverflow==0 ){
    return SQLITE_OK;  /* No overflow pages. Return without doing anything */
  }
  ovflPgno = get4byte(&pCell[info.iOverflow]);
  while( ovflPgno!=0 ){
    MemPage *pOvfl;
    if( ovflPgno>sqlite3pager_pagecount(pBt->pPager) ){
      return SQLITE_CORRUPT;
    }
    rc = getPage(pBt, ovflPgno, &pOvfl);
    if( rc ) return rc;
    ovflPgno = get4byte(pOvfl->aData);
    rc = freePage(pOvfl);
    if( rc ) return rc;
    sqlite3pager_unref(pOvfl->aData);
  }
................................................................................
  MemPage *pParent,     /* Parent page.  NULL for the root */
  int freePageFlag      /* Deallocate page if true */
){
  MemPage *pPage;
  int rc;
  unsigned char *pCell;
  int i;

  if( pgno>sqlite3pager_pagecount(pBt->pPager) ){
    return SQLITE_CORRUPT;
  }

  rc = getAndInitPage(pBt, pgno, &pPage, pParent);
  if( rc ) return rc;
  rc = sqlite3pager_write(pPage->aData);
  if( rc ) return rc;
  for(i=0; i<pPage->nCell; i++){
    pCell = findCell(pPage, i);

Changes to src/expr.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
469
470
471
472
473
474
475

476


477
478
479
480
481
482
483
...
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.193 2005/02/08 07:50:41 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**
................................................................................
    struct SrcList_item *pNewItem = &pNew->a[i];
    struct SrcList_item *pOldItem = &p->a[i];
    pNewItem->zDatabase = sqliteStrDup(pOldItem->zDatabase);
    pNewItem->zName = sqliteStrDup(pOldItem->zName);
    pNewItem->zAlias = sqliteStrDup(pOldItem->zAlias);
    pNewItem->jointype = pOldItem->jointype;
    pNewItem->iCursor = pOldItem->iCursor;

    pNewItem->pTab = 0;


    pNewItem->pSelect = sqlite3SelectDup(pOldItem->pSelect);
    pNewItem->pOn = sqlite3ExprDup(pOldItem->pOn);
    pNewItem->pUsing = sqlite3IdListDup(pOldItem->pUsing);
    pNewItem->colUsed = pOldItem->colUsed;
  }
  return pNew;
}
................................................................................
  pNew->pPrior = sqlite3SelectDup(p->pPrior);
  pNew->pLimit = sqlite3ExprDup(p->pLimit);
  pNew->pOffset = sqlite3ExprDup(p->pOffset);
  pNew->iLimit = -1;
  pNew->iOffset = -1;
  pNew->ppOpenTemp = 0;
  pNew->pFetch = 0;
  pNew->isResolved = 0;
  pNew->isAgg = 0;
  return pNew;
}
#else
Select *sqlite3SelectDup(Select *p){
  assert( p==0 );
  return 0;
}







|







 







>
|
>
>







 







|
|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
...
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.194 2005/02/12 08:59:57 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**
................................................................................
    struct SrcList_item *pNewItem = &pNew->a[i];
    struct SrcList_item *pOldItem = &p->a[i];
    pNewItem->zDatabase = sqliteStrDup(pOldItem->zDatabase);
    pNewItem->zName = sqliteStrDup(pOldItem->zName);
    pNewItem->zAlias = sqliteStrDup(pOldItem->zAlias);
    pNewItem->jointype = pOldItem->jointype;
    pNewItem->iCursor = pOldItem->iCursor;
    pNewItem->pTab = pOldItem->pTab;
    if( pNewItem->pTab ){
      pNewItem->pTab->isTransient = 0;
    }
    pNewItem->pSelect = sqlite3SelectDup(pOldItem->pSelect);
    pNewItem->pOn = sqlite3ExprDup(pOldItem->pOn);
    pNewItem->pUsing = sqlite3IdListDup(pOldItem->pUsing);
    pNewItem->colUsed = pOldItem->colUsed;
  }
  return pNew;
}
................................................................................
  pNew->pPrior = sqlite3SelectDup(p->pPrior);
  pNew->pLimit = sqlite3ExprDup(p->pLimit);
  pNew->pOffset = sqlite3ExprDup(p->pOffset);
  pNew->iLimit = -1;
  pNew->iOffset = -1;
  pNew->ppOpenTemp = 0;
  pNew->pFetch = 0;
  pNew->isResolved = p->isResolved;
  pNew->isAgg = p->isAgg;
  return pNew;
}
#else
Select *sqlite3SelectDup(Select *p){
  assert( p==0 );
  return 0;
}

Changes to src/select.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
1831
1832
1833
1834
1835
1836
1837


1838
1839
1840
1841
1842
1843
1844
**    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.240 2005/02/08 07:50:41 danielk1977 Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
      assert( pExpr->pList==0 );
      pExpr->pList = sqlite3ExprListDup(pNew->pList);
      pExpr->iTable = pNew->iTable;
      pExpr->iColumn = pNew->iColumn;
      pExpr->iAgg = pNew->iAgg;
      sqlite3TokenCopy(&pExpr->token, &pNew->token);
      sqlite3TokenCopy(&pExpr->span, &pNew->span);


    }
  }else{
    substExpr(pExpr->pLeft, iTable, pEList);
    substExpr(pExpr->pRight, iTable, pEList);
    substSelect(pExpr->pSelect, iTable, pEList);
    substExprList(pExpr->pList, iTable, pEList);
  }







|







 







>
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
1831
1832
1833
1834
1835
1836
1837
1838
1839
1840
1841
1842
1843
1844
1845
1846
**    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.241 2005/02/12 08:59:57 danielk1977 Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
      assert( pExpr->pList==0 );
      pExpr->pList = sqlite3ExprListDup(pNew->pList);
      pExpr->iTable = pNew->iTable;
      pExpr->iColumn = pNew->iColumn;
      pExpr->iAgg = pNew->iAgg;
      sqlite3TokenCopy(&pExpr->token, &pNew->token);
      sqlite3TokenCopy(&pExpr->span, &pNew->span);
      pExpr->pSelect = sqlite3SelectDup(pNew->pSelect);
      pExpr->flags = pNew->flags;
    }
  }else{
    substExpr(pExpr->pLeft, iTable, pEList);
    substExpr(pExpr->pRight, iTable, pEList);
    substSelect(pExpr->pSelect, iTable, pEList);
    substExprList(pExpr->pList, iTable, pEList);
  }

Changes to test/bind.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
#    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 script testing the sqlite_bind API.
#
# $Id: bind.test,v 1.29 2005/01/29 09:00:49 danielk1977 Exp $
#

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

proc sqlite_step {stmt N VALS COLS} {
  upvar VALS vals
................................................................................
    sqlite3_bind_int $VM 1 1
  } msg]
  lappend rc $msg
} {1 {}}
do_test bind-10.9 {
  sqlite3_finalize $VM
} SQLITE_OK
breakpoint
do_test bind-10.10 {
  execsql {SELECT * FROM t2}
} {1 999 1000 1001 {} {} 1 2 1 3 2 1}

# Ticket #918
#
do_test bind-10.11 {







|







 







<







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
439
440
441
442
443
444
445

446
447
448
449
450
451
452
#    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 script testing the sqlite_bind API.
#
# $Id: bind.test,v 1.30 2005/02/12 08:59:59 danielk1977 Exp $
#

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

proc sqlite_step {stmt N VALS COLS} {
  upvar VALS vals
................................................................................
    sqlite3_bind_int $VM 1 1
  } msg]
  lappend rc $msg
} {1 {}}
do_test bind-10.9 {
  sqlite3_finalize $VM
} SQLITE_OK

do_test bind-10.10 {
  execsql {SELECT * FROM t2}
} {1 999 1000 1001 {} {} 1 2 1 3 2 1}

# Ticket #918
#
do_test bind-10.11 {

Changes to test/subquery.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
92
93
94
95
96
97
98













































99
100
101
102
103
104
105
#    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 script is testing correlated subqueries
#
# $Id: subquery.test,v 1.6 2005/02/09 01:40:25 danielk1977 Exp $
#

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

ifcapable !subquery {
  finish_test
................................................................................
  }
} {0 1 1 1}
do_test subquery-1.9.2 {
  execsql {
    SELECT a FROM t1 WHERE (SELECT (y*2)>b FROM t2 WHERE a=x); 
  }
} {3 5 7}















































#------------------------------------------------------------------
# The following test cases - subquery-2.* - are not logically
# organized. They're here largely because they were failing during
# one stage of development of sub-queries.
#







|







 







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







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
#    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 script is testing correlated subqueries
#
# $Id: subquery.test,v 1.7 2005/02/12 08:59:59 danielk1977 Exp $
#

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

ifcapable !subquery {
  finish_test
................................................................................
  }
} {0 1 1 1}
do_test subquery-1.9.2 {
  execsql {
    SELECT a FROM t1 WHERE (SELECT (y*2)>b FROM t2 WHERE a=x); 
  }
} {3 5 7}

# Test that the flattening optimization works with subquery expressions.
do_test subquery-1.10.1 {
  execsql {
    SELECT (SELECT a), b FROM t1;
  }
} {1 3 3 13 5 31 7 57}
do_test subquery-1.10.2 {
  execsql {
    SELECT * FROM (SELECT (SELECT a), b FROM t1);
  }
} {1 3 3 13 5 31 7 57}
do_test subquery-1.10.3 {
  execsql {
    SELECT * FROM (SELECT (SELECT sum(a) FROM t1));
  }
} {16.0}
do_test subquery-1.10.4 {
  execsql {
    CREATE TABLE t5 (val int, period text PRIMARY KEY);
    INSERT INTO t5 VALUES(5, '2001-3');
    INSERT INTO t5 VALUES(10, '2001-4');
    INSERT INTO t5 VALUES(15, '2002-1');
    INSERT INTO t5 VALUES(5, '2002-2');
    INSERT INTO t5 VALUES(10, '2002-3');
    INSERT INTO t5 VALUES(15, '2002-4');
    INSERT INTO t5 VALUES(10, '2003-1');
    INSERT INTO t5 VALUES(5, '2003-2');
    INSERT INTO t5 VALUES(25, '2003-3');
    INSERT INTO t5 VALUES(5, '2003-4');

    SELECT "a.period", vsum
    FROM (SELECT 
      a.period,
      (select sum(val) from t5 where period between a.period and '2002-4') vsum
      FROM t5 a where a.period between '2002-1' and '2002-4')
    WHERE vsum < 45 ;
  }
} {2002-2 30.0 2002-3 25.0 2002-4 15.0}
do_test subquery-1.10.5 {
  execsql {
    DROP TABLE t5;
  }
} {}



#------------------------------------------------------------------
# The following test cases - subquery-2.* - are not logically
# organized. They're here largely because they were failing during
# one stage of development of sub-queries.
#

Changes to test/tester.tcl.

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
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements some common TCL routines used for regression
# testing the SQLite library
#
# $Id: tester.tcl,v 1.47 2005/01/24 10:26:00 danielk1977 Exp $

# Make sure tclsqlite3 was compiled correctly.  Abort now with an
# error message if not.
#
if {[sqlite3 -tcl-uses-utf]} {
  if {"\u1234"=="u1234"} {
    puts stderr "***** BUILD PROBLEM *****"
................................................................................
#     -start            Value of 'N' to begin with (default 1)
#
#     -cksum            Boolean. If true, test that the database does
#                       not change during the execution of the test case.
#
proc do_ioerr_test {testname args} {

  if {$testname=="ioerr-2"} {
    breakpoint
  }
  set ::ioerropts(-start) 1
  set ::ioerropts(-cksum) 0

  array set ::ioerropts $args

  set ::go 1
  for {set n $::ioerropts(-start)} {$::go} {incr n} {







|







 







<
<
<







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
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements some common TCL routines used for regression
# testing the SQLite library
#
# $Id: tester.tcl,v 1.48 2005/02/12 08:59:59 danielk1977 Exp $

# Make sure tclsqlite3 was compiled correctly.  Abort now with an
# error message if not.
#
if {[sqlite3 -tcl-uses-utf]} {
  if {"\u1234"=="u1234"} {
    puts stderr "***** BUILD PROBLEM *****"
................................................................................
#     -start            Value of 'N' to begin with (default 1)
#
#     -cksum            Boolean. If true, test that the database does
#                       not change during the execution of the test case.
#
proc do_ioerr_test {testname args} {




  set ::ioerropts(-start) 1
  set ::ioerropts(-cksum) 0

  array set ::ioerropts $args

  set ::go 1
  for {set n $::ioerropts(-start)} {$::go} {incr n} {