/ Check-in [98ef6110]
Login

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

Overview
Comment:Make sure the min() and max() optimizations work on empty indexed tables. Ticket #296. (CVS 914)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 98ef6110068e5ed3cd77a14b004f890b79b731f7
User & Date: drh 2003-04-17 12:44:24
Context
2003-04-17
22:57
Fix triggers to work in an ATTACHed database. Ticket #295. (CVS 915) check-in: 1e5e00fb user: drh tags: trunk
12:44
Make sure the min() and max() optimizations work on empty indexed tables. Ticket #296. (CVS 914) check-in: 98ef6110 user: drh tags: trunk
02:54
Change the shell to use the sqliteIsNumber() routine for determining if values are numeric. Modified os.c so that it should now work with DJGPP - though I have no way of testing this. (CVS 913) check-in: 35caefe3 user: drh 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
....
1816
1817
1818
1819
1820
1821
1822

1823
1824
1825
1826
1827
1828
1829
....
1831
1832
1833
1834
1835
1836
1837
1838
1839
1840
1841
1842
1843
1844
1845
**    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.130 2003/03/31 13:36:09 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
  if( !pParse->schemaVerified && (pParse->db->flags & SQLITE_InTrans)==0 ){
    sqliteCodeVerifySchema(pParse);
  }
  base = p->base;
  sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0);
  sqliteVdbeAddOp(v, OP_OpenRead, base, pTab->tnum);
  sqliteVdbeChangeP3(v, -1, pTab->zName, P3_STATIC);

  if( pIdx==0 ){
    sqliteVdbeAddOp(v, seekOp, base, 0);
  }else{
    sqliteVdbeAddOp(v, OP_Integer, pIdx->iDb, 0);
    sqliteVdbeAddOp(v, OP_OpenRead, base+1, pIdx->tnum);
    sqliteVdbeChangeP3(v, -1, pIdx->zName, P3_STATIC);
    sqliteVdbeAddOp(v, seekOp, base+1, 0);
................................................................................
    sqliteVdbeAddOp(v, OP_Close, base+1, 0);
    sqliteVdbeAddOp(v, OP_MoveTo, base, 0);
  }
  eList.nExpr = 1;
  memset(&eListItem, 0, sizeof(eListItem));
  eList.a = &eListItem;
  eList.a[0].pExpr = pExpr;
  cont = sqliteVdbeMakeLabel(v);
  selectInnerLoop(pParse, p, &eList, 0, 0, 0, -1, eDest, iParm, cont, cont);
  sqliteVdbeResolveLabel(v, cont);
  sqliteVdbeAddOp(v, OP_Close, base, 0);
  return 1;
}

/*







|







 







>







 







<







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
1816
1817
1818
1819
1820
1821
1822
1823
1824
1825
1826
1827
1828
1829
1830
....
1832
1833
1834
1835
1836
1837
1838

1839
1840
1841
1842
1843
1844
1845
**    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.131 2003/04/17 12:44:24 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
  if( !pParse->schemaVerified && (pParse->db->flags & SQLITE_InTrans)==0 ){
    sqliteCodeVerifySchema(pParse);
  }
  base = p->base;
  sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0);
  sqliteVdbeAddOp(v, OP_OpenRead, base, pTab->tnum);
  sqliteVdbeChangeP3(v, -1, pTab->zName, P3_STATIC);
  cont = sqliteVdbeMakeLabel(v);
  if( pIdx==0 ){
    sqliteVdbeAddOp(v, seekOp, base, 0);
  }else{
    sqliteVdbeAddOp(v, OP_Integer, pIdx->iDb, 0);
    sqliteVdbeAddOp(v, OP_OpenRead, base+1, pIdx->tnum);
    sqliteVdbeChangeP3(v, -1, pIdx->zName, P3_STATIC);
    sqliteVdbeAddOp(v, seekOp, base+1, 0);
................................................................................
    sqliteVdbeAddOp(v, OP_Close, base+1, 0);
    sqliteVdbeAddOp(v, OP_MoveTo, 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);
  sqliteVdbeResolveLabel(v, cont);
  sqliteVdbeAddOp(v, OP_Close, base, 0);
  return 1;
}

/*

Changes to src/vdbe.c.

32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
....
4501
4502
4503
4504
4505
4506
4507



4508
4509
4510
4511

4512
4513
4514
4515
4516
4517
4518
**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** in this file for details.  If in doubt, do not deviate from existing
** commenting and indentation practices when changing or adding code.
**
** $Id: vdbe.c,v 1.217 2003/04/16 21:03:14 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** The makefile scans this source file and creates the following
** array of string constants which are the names of all VDBE opcodes.
................................................................................
  int tos = ++p->tos;
  BtCursor *pCrsr;

  if( VERIFY( i>=0 && i<p->nCursor && ) (pCrsr = p->aCsr[i].pCursor)!=0 ){
    int v;
    int sz;
    sqliteBtreeKeySize(pCrsr, &sz);



    sqliteBtreeKey(pCrsr, sz - sizeof(u32), sizeof(u32), (char*)&v);
    v = keyToInt(v);
    aStack[tos].i = v;
    aStack[tos].flags = STK_Int;

  }
  break;
}

/* Opcode: IdxGT P1 P2 *
**
** Compare the top of the stack against the key on the index entry that







|







 







>
>
>
|
|
|
|
>







32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
....
4501
4502
4503
4504
4505
4506
4507
4508
4509
4510
4511
4512
4513
4514
4515
4516
4517
4518
4519
4520
4521
4522
**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** in this file for details.  If in doubt, do not deviate from existing
** commenting and indentation practices when changing or adding code.
**
** $Id: vdbe.c,v 1.218 2003/04/17 12:44:25 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** The makefile scans this source file and creates the following
** array of string constants which are the names of all VDBE opcodes.
................................................................................
  int tos = ++p->tos;
  BtCursor *pCrsr;

  if( VERIFY( i>=0 && i<p->nCursor && ) (pCrsr = p->aCsr[i].pCursor)!=0 ){
    int v;
    int sz;
    sqliteBtreeKeySize(pCrsr, &sz);
    if( sz<sizeof(u32) ){
      aStack[tos].flags = STK_Null;
    }else{
      sqliteBtreeKey(pCrsr, sz - sizeof(u32), sizeof(u32), (char*)&v);
      v = keyToInt(v);
      aStack[tos].i = v;
      aStack[tos].flags = STK_Int;
    }
  }
  break;
}

/* Opcode: IdxGT P1 P2 *
**
** Compare the top of the stack against the key on the index entry that

Changes to test/minmax.test.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
138
139
140
141
142
143
144
145































146
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing SELECT statements that contain
# aggregate min() and max() functions and which are handled as
# as a special case.
#
# $Id: minmax.test,v 1.4 2002/05/31 15:51:26 drh Exp $

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

do_test minmax-1.0 {
  execsql {
    BEGIN;
................................................................................
do_test minmax-4.3 {
  execsql {
    SELECT y, count(x), count(*) FROM
      (SELECT null, y+1 FROM t1 UNION SELECT * FROM t1)
    GROUP BY y ORDER BY y;
  }
} {1 1 1 2 2 3 3 4 5 4 8 9 5 5 6 6 0 1}
































finish_test







|







 








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

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing SELECT statements that contain
# aggregate min() and max() functions and which are handled as
# as a special case.
#
# $Id: minmax.test,v 1.5 2003/04/17 12:44:25 drh Exp $

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

do_test minmax-1.0 {
  execsql {
    BEGIN;
................................................................................
do_test minmax-4.3 {
  execsql {
    SELECT y, count(x), count(*) FROM
      (SELECT null, y+1 FROM t1 UNION SELECT * FROM t1)
    GROUP BY y ORDER BY y;
  }
} {1 1 1 2 2 3 3 4 5 4 8 9 5 5 6 6 0 1}

# Make sure the min(x) and max(x) optimizations work on empty tables
# including empty tables with indices. Ticket #296.
#
do_test minmax-5.1 {
  execsql {
    CREATE TABLE t3(x INTEGER UNIQUE NOT NULL);
    SELECT coalesce(min(x),999) FROM t3;
  }
} {999}
do_test minmax-5.2 {
  execsql {
    SELECT coalesce(min(rowid),999) FROM t3;
  }
} {999}
do_test minmax-5.3 {
  execsql {
    SELECT coalesce(max(x),999) FROM t3;
  }
} {999}
do_test minmax-5.4 {
  execsql {
    SELECT coalesce(max(rowid),999) FROM t3;
  }
} {999}
do_test minmax-5.5 {
  execsql {
    SELECT coalesce(max(rowid),999) FROM t3 WHERE rowid<25;
  }
} {999}


finish_test

Changes to test/where.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
682
683
684
685
686
687
688




















689
690
691
#    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 use of indices in WHERE clases.
#
# $Id: where.test,v 1.13 2002/12/04 22:29:29 drh Exp $

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

# Build some test data
#
do_test where-1.0 {
................................................................................
  execsql {
    DELETE FROM t4;
  }
  cksort {
    SELECT w FROM t4 WHERE x=4 and y<1000 ORDER BY y DESC limit 3;
  }
} {nosort}






















finish_test







|







 







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



7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
#    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 use of indices in WHERE clases.
#
# $Id: where.test,v 1.14 2003/04/17 12:44:25 drh Exp $

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

# Build some test data
#
do_test where-1.0 {
................................................................................
  execsql {
    DELETE FROM t4;
  }
  cksort {
    SELECT w FROM t4 WHERE x=4 and y<1000 ORDER BY y DESC limit 3;
  }
} {nosort}

# Make sure searches with an index work with an empty table.
#
do_test where-9.1 {
  execsql {
    CREATE TABLE t5(x PRIMARY KEY);
    SELECT * FROM t5 WHERE x<10;
  }
} {}
do_test where-9.2 {
  execsql {
    SELECT * FROM t5 WHERE x<10 ORDER BY x DESC;
  }
} {}
do_test where-9.3 {
  execsql {
    SELECT * FROM t5 WHERE x=10;
  }
} {}



finish_test