/ Check-in [7e85a162]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:More bug fixes. But there are still tests that fail. (CVS 2664)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 7e85a162d0ecf0acdbffcacc62b60d97fe123881
User & Date: drh 2005-09-07 22:48:16
Context
2005-09-07
23:05
More bug fixes. All of the "quick" tests pass. The full test suite still shows problems. (CVS 2665) check-in: a1b6d910 user: drh tags: trunk
22:48
More bug fixes. But there are still tests that fail. (CVS 2664) check-in: 7e85a162 user: drh tags: trunk
22:09
Bug fixes in aggregate processing. Fewer tests fail. (CVS 2663) check-in: c3ac5859 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Show Whitespace Changes Patch

Changes to src/expr.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
2127
2128
2129
2130
2131
2132
2133

2134
2135
2136
2137
2138
2139
2140
**    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.225 2005/09/07 21:22:46 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**
................................................................................
            }
            if( i>=pAggInfo->nColumn && (i = addAggInfoColumn(pAggInfo))>=0 ){
              pCol = &pAggInfo->aCol[i];
              pCol->iTable = pExpr->iTable;
              pCol->iColumn = pExpr->iColumn;
              pCol->iMem = pParse->nMem++;
              pCol->iSorterColumn = -1;

              if( pAggInfo->pGroupBy ){
                int j, n;
                ExprList *pGB = pAggInfo->pGroupBy;
                struct ExprList_item *pTerm = pGB->a;
                n = pGB->nExpr;
                for(j=0; j<n; j++, pTerm++){
                  Expr *pE = pTerm->pExpr;







|







 







>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
2127
2128
2129
2130
2131
2132
2133
2134
2135
2136
2137
2138
2139
2140
2141
**    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.226 2005/09/07 22:48:16 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**
................................................................................
            }
            if( i>=pAggInfo->nColumn && (i = addAggInfoColumn(pAggInfo))>=0 ){
              pCol = &pAggInfo->aCol[i];
              pCol->iTable = pExpr->iTable;
              pCol->iColumn = pExpr->iColumn;
              pCol->iMem = pParse->nMem++;
              pCol->iSorterColumn = -1;
              pCol->pExpr = pExpr;
              if( pAggInfo->pGroupBy ){
                int j, n;
                ExprList *pGB = pAggInfo->pGroupBy;
                struct ExprList_item *pTerm = pGB->a;
                n = pGB->nExpr;
                for(j=0; j<n; j++, pTerm++){
                  Expr *pE = pTerm->pExpr;

Changes to src/select.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
2442
2443
2444
2445
2446
2447
2448
2449
2450
2451
2452
2453
2454
2455
2456
....
2468
2469
2470
2471
2472
2473
2474
2475
2476
2477
2478
2479
2480
2481
2482
2483
2484
2485
2486
2487
....
2986
2987
2988
2989
2990
2991
2992



2993
2994
2995
2996
2997
2998
2999
**    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.262 2005/09/07 22:09:48 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
** the current cursor position.
*/
static void updateAccumulator(Parse *pParse, AggInfo *pAggInfo){
  Vdbe *v = pParse->pVdbe;
  int i;
  struct AggInfo_func *pF;
  struct AggInfo_col *pC;
  Expr fauxExpr;

  pAggInfo->directMode = 1;
  for(i=0, pF=pAggInfo->aFunc; i<pAggInfo->nFunc; i++, pF++){
    int nArg;
    ExprList *pList = pF->pExpr->pList;
    if( pList ){
      nArg = pList->nExpr;
................................................................................
      if( !pColl ){
        pColl = pParse->db->pDfltColl;
      }
      sqlite3VdbeOp3(v, OP_CollSeq, 0, 0, (char *)pColl, P3_COLLSEQ);
    }
    sqlite3VdbeOp3(v, OP_AggStep, pF->iMem, nArg, (void*)pF->pFunc, P3_FUNCDEF);
  }
  memset(&fauxExpr, 0, sizeof(fauxExpr));
  fauxExpr.op = TK_AGG_COLUMN;
  fauxExpr.pAggInfo = pAggInfo;
  for(i=0, pC=pAggInfo->aCol; i<pAggInfo->nAccumulator; i++, pC++){
    fauxExpr.iAgg = i;
    sqlite3ExprCode(pParse, &fauxExpr);
    sqlite3VdbeAddOp(v, OP_MemStore, pC->iMem, 1);
  }
  pAggInfo->directMode = 0;
}


/*
................................................................................
      */
      resetAccumulator(pParse, &sAggInfo);
      pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0);
      updateAccumulator(pParse, &sAggInfo);
      sqlite3WhereEnd(pWInfo);
      finalizeAggFunctions(pParse, &sAggInfo);
      pOrderBy = 0;



      selectInnerLoop(pParse, p, p->pEList, 0, 0, 0, -1, 
                      eDest, iParm, addrEnd, addrEnd, aff);
    }
    sqlite3VdbeResolveLabel(v, addrEnd);
    
  } /* endif aggregate query */








|







 







<







 







<
<
<

<
|







 







>
>
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
2442
2443
2444
2445
2446
2447
2448

2449
2450
2451
2452
2453
2454
2455
....
2467
2468
2469
2470
2471
2472
2473



2474

2475
2476
2477
2478
2479
2480
2481
2482
....
2981
2982
2983
2984
2985
2986
2987
2988
2989
2990
2991
2992
2993
2994
2995
2996
2997
**    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.263 2005/09/07 22:48:16 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
** the current cursor position.
*/
static void updateAccumulator(Parse *pParse, AggInfo *pAggInfo){
  Vdbe *v = pParse->pVdbe;
  int i;
  struct AggInfo_func *pF;
  struct AggInfo_col *pC;


  pAggInfo->directMode = 1;
  for(i=0, pF=pAggInfo->aFunc; i<pAggInfo->nFunc; i++, pF++){
    int nArg;
    ExprList *pList = pF->pExpr->pList;
    if( pList ){
      nArg = pList->nExpr;
................................................................................
      if( !pColl ){
        pColl = pParse->db->pDfltColl;
      }
      sqlite3VdbeOp3(v, OP_CollSeq, 0, 0, (char *)pColl, P3_COLLSEQ);
    }
    sqlite3VdbeOp3(v, OP_AggStep, pF->iMem, nArg, (void*)pF->pFunc, P3_FUNCDEF);
  }



  for(i=0, pC=pAggInfo->aCol; i<pAggInfo->nAccumulator; i++, pC++){

    sqlite3ExprCode(pParse, pC->pExpr);
    sqlite3VdbeAddOp(v, OP_MemStore, pC->iMem, 1);
  }
  pAggInfo->directMode = 0;
}


/*
................................................................................
      */
      resetAccumulator(pParse, &sAggInfo);
      pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0);
      updateAccumulator(pParse, &sAggInfo);
      sqlite3WhereEnd(pWInfo);
      finalizeAggFunctions(pParse, &sAggInfo);
      pOrderBy = 0;
      if( pHaving ){
        sqlite3ExprIfFalse(pParse, pHaving, addrEnd, 1);
      }
      selectInnerLoop(pParse, p, p->pEList, 0, 0, 0, -1, 
                      eDest, iParm, addrEnd, addrEnd, aff);
    }
    sqlite3VdbeResolveLabel(v, addrEnd);
    
  } /* endif aggregate query */

Changes to src/sqliteInt.h.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
810
811
812
813
814
815
816

817
818
819
820
821
822
823
**    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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.408 2005/09/07 21:22:47 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** These #defines should enable >2GB file support on Posix if the
** underlying operating system supports it.  If the OS lacks
................................................................................
  ExprList *pGroupBy;     /* The group by clause */
  int nSortingColumn;     /* Number of columns in the sorting index */
  struct AggInfo_col {    /* For each column used in source tables */
    int iTable;              /* Cursor number of the source table */
    int iColumn;             /* Column number within the source table */
    int iSorterColumn;       /* Column number in the sorting index */
    int iMem;                /* Memory location that acts as accumulator */

  } *aCol;
  int nColumn;            /* Number of used entries in aCol[] */
  int nColumnAlloc;       /* Number of slots allocated for aCol[] */
  int nAccumulator;       /* Number of columns that show through to the output.
                          ** Additional columns are used only as parameters to
                          ** aggregate functions */
  struct AggInfo_func {   /* For each aggregate function */







|







 







>







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
**    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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.409 2005/09/07 22:48:16 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** These #defines should enable >2GB file support on Posix if the
** underlying operating system supports it.  If the OS lacks
................................................................................
  ExprList *pGroupBy;     /* The group by clause */
  int nSortingColumn;     /* Number of columns in the sorting index */
  struct AggInfo_col {    /* For each column used in source tables */
    int iTable;              /* Cursor number of the source table */
    int iColumn;             /* Column number within the source table */
    int iSorterColumn;       /* Column number in the sorting index */
    int iMem;                /* Memory location that acts as accumulator */
    Expr *pExpr;             /* The original expression */
  } *aCol;
  int nColumn;            /* Number of used entries in aCol[] */
  int nColumnAlloc;       /* Number of slots allocated for aCol[] */
  int nAccumulator;       /* Number of columns that show through to the output.
                          ** Additional columns are used only as parameters to
                          ** aggregate functions */
  struct AggInfo_func {   /* For each aggregate function */

Changes to test/collate5.test.

10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
#
#*************************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing DISTINCT, UNION, INTERSECT and EXCEPT
# SELECT statements that use user-defined collation sequences. Also
# GROUP BY clauses that use user-defined collation sequences.
#
# $Id: collate5.test,v 1.4 2005/04/01 10:47:40 drh Exp $

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


#
# Tests are organised as follows:
................................................................................
    INSERT INTO collate5t1 VALUES('a', '1');
    INSERT INTO collate5t1 VALUES('A', '1.0');
    INSERT INTO collate5t1 VALUES('b', '2');
    INSERT INTO collate5t1 VALUES('B', '3');
  }
} {}
do_test collate5-4.1 {
  execsql {
    SELECT a, count(*) FROM collate5t1 GROUP BY a;
  }
} {a 2 b 2}
do_test collate5-4.2 {
  execsql {
    SELECT a, b, count(*) FROM collate5t1 GROUP BY a, b;
  }
} {a 1 2 b 2 1 B 3 1}
do_test collate5-4.3 {
  execsql {
    DROP TABLE collate5t1;
  }
} {}

finish_test







|







 







|

|



|

|







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
#
#*************************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing DISTINCT, UNION, INTERSECT and EXCEPT
# SELECT statements that use user-defined collation sequences. Also
# GROUP BY clauses that use user-defined collation sequences.
#
# $Id: collate5.test,v 1.5 2005/09/07 22:48:16 drh Exp $

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


#
# Tests are organised as follows:
................................................................................
    INSERT INTO collate5t1 VALUES('a', '1');
    INSERT INTO collate5t1 VALUES('A', '1.0');
    INSERT INTO collate5t1 VALUES('b', '2');
    INSERT INTO collate5t1 VALUES('B', '3');
  }
} {}
do_test collate5-4.1 {
  string tolower [execsql {
    SELECT a, count(*) FROM collate5t1 GROUP BY a;
  }]
} {a 2 b 2}
do_test collate5-4.2 {
  execsql {
    SELECT a, b, count(*) FROM collate5t1 GROUP BY a, b ORDER BY a, b;
  }
} {A 1.0 2 b 2 1 B 3 1}
do_test collate5-4.3 {
  execsql {
    DROP TABLE collate5t1;
  }
} {}

finish_test

Changes to test/misc4.test.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
..
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc4.test,v 1.17 2005/09/07 21:22:47 drh Exp $

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

# Prepare a statement that will create a temporary table.  Then do
# a rollback.  Then try to execute the prepared statement.
#
................................................................................
    CREATE TABLE Table2(ID integer NOT NULL, Value TEXT);
    INSERT INTO Table2 VALUES(1, 'z');
    INSERT INTO Table2 VALUES (1, 'a');
    SELECT ID, Value FROM Table1
       UNION SELECT ID, max(Value) FROM Table2 GROUP BY 1
    ORDER BY 1, 2;
  }
} {{} {} 1 x 1 z}
} ;# ifcapable compound

# Ticket #1047.  Make sure column types are preserved in subqueries.
#
ifcapable subquery {
  do_test misc4-4.1 {
    execsql {







|







 







|







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
..
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc4.test,v 1.18 2005/09/07 22:48:16 drh Exp $

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

# Prepare a statement that will create a temporary table.  Then do
# a rollback.  Then try to execute the prepared statement.
#
................................................................................
    CREATE TABLE Table2(ID integer NOT NULL, Value TEXT);
    INSERT INTO Table2 VALUES(1, 'z');
    INSERT INTO Table2 VALUES (1, 'a');
    SELECT ID, Value FROM Table1
       UNION SELECT ID, max(Value) FROM Table2 GROUP BY 1
    ORDER BY 1, 2;
  }
} {1 x 1 z}
} ;# ifcapable compound

# Ticket #1047.  Make sure column types are preserved in subqueries.
#
ifcapable subquery {
  do_test misc4-4.1 {
    execsql {

Changes to test/select5.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
150
151
152
153
154
155
156
157
158
159
#    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 aggregate functions and the
# GROUP BY and HAVING clauses of SELECT statements.
#
# $Id: select5.test,v 1.10 2005/08/19 03:03:52 drh Exp $

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

# Build some test data
#
execsql {
................................................................................
    SELECT a, b FROM t2 GROUP BY a, b;
  } 
} {1 2 1 4 6 4}
do_test select5-5.5 {
  execsql {
    SELECT a, b FROM t2 GROUP BY a;
  } 
} {1 2 6 4}

finish_test







|







 







|


8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
150
151
152
153
154
155
156
157
158
159
#    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 aggregate functions and the
# GROUP BY and HAVING clauses of SELECT statements.
#
# $Id: select5.test,v 1.11 2005/09/07 22:48:16 drh Exp $

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

# Build some test data
#
execsql {
................................................................................
    SELECT a, b FROM t2 GROUP BY a, b;
  } 
} {1 2 1 4 6 4}
do_test select5-5.5 {
  execsql {
    SELECT a, b FROM t2 GROUP BY a;
  } 
} {1 4 6 4}

finish_test

Changes to test/select6.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
190
191
192
193
194
195
196

197
198
199
200
201
202
203
#    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 SELECT statements that contain
# subqueries in their FROM clause.
#
# $Id: select6.test,v 1.18 2005/07/08 17:13:47 drh Exp $

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

# Omit this whole file if the library is build without subquery support.
ifcapable !subquery {
  finish_test
................................................................................
do_test select6-3.6 {
  execsql {
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1)
    WHERE a>10
  }
} {10.5 3.7 14.2}
do_test select6-3.7 {

  execsql {
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1)
    WHERE a<10
  }
} {}
do_test select6-3.8 {
  execsql {







|







 







>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
#    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 SELECT statements that contain
# subqueries in their FROM clause.
#
# $Id: select6.test,v 1.19 2005/09/07 22:48:16 drh Exp $

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

# Omit this whole file if the library is build without subquery support.
ifcapable !subquery {
  finish_test
................................................................................
do_test select6-3.6 {
  execsql {
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1)
    WHERE a>10
  }
} {10.5 3.7 14.2}
do_test select6-3.7 {
btree_breakpoint
  execsql {
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1)
    WHERE a<10
  }
} {}
do_test select6-3.8 {
  execsql {