/ Check-in [bce28975]
Login

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

Overview
Comment:Fix for #3022. Handle queries like "SELECT min(b) FROM T WHERE a = X AND b > X" when there is an index on (a,b). (CVS 4930)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: bce289753538ab504e55c4215d6cd4f5df137d9e
User & Date: danielk1977 2008-03-28 18:11:17
Context
2008-03-28
19:15
Do not incorrectly report a malloc() failure when allocating 0 bytes. Fixes a problem in bind.test. (CVS 4931) check-in: b99d19d6 user: danielk1977 tags: trunk
18:11
Fix for #3022. Handle queries like "SELECT min(b) FROM T WHERE a = X AND b > X" when there is an index on (a,b). (CVS 4930) check-in: bce28975 user: danielk1977 tags: trunk
17:41
Change the pager's sector size algorithm to the maximum of the size reported by xSectorSize() from the VFS and 512. It was using the maximum of xSectorSize() and the current page size, but that was adding an extra 512 bytes to the size of the journal file in the common case. (CVS 4929) check-in: 17ea0c97 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/vdbemem.c.

501
502
503
504
505
506
507



508
509
510
511
512
513
514
      n += p->u.i;
    }
    return n>p->db->aLimit[SQLITE_LIMIT_LENGTH];
  }
  return 0; 
}




#define MEMCELLSIZE (int)(&(((Mem *)0)->zMalloc))

/*
** Make an shallow copy of pFrom into pTo.  Prior contents of
** pTo are freed.  The pFrom->z field is not duplicated.  If
** pFrom->z is used, then pTo->z points to the same thing as pFrom->z
** and flags gets srcType (either MEM_Ephem or MEM_Static).







>
>
>







501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
      n += p->u.i;
    }
    return n>p->db->aLimit[SQLITE_LIMIT_LENGTH];
  }
  return 0; 
}

/*
** Size of struct Mem not including the Mem.zMalloc member.
*/
#define MEMCELLSIZE (int)(&(((Mem *)0)->zMalloc))

/*
** Make an shallow copy of pFrom into pTo.  Prior contents of
** pTo are freed.  The pFrom->z field is not duplicated.  If
** pFrom->z is used, then pTo->z points to the same thing as pFrom->z
** and flags gets srcType (either MEM_Ephem or MEM_Static).

Changes to src/where.c.

12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
....
2513
2514
2515
2516
2517
2518
2519
2520
2521
2522
2523
2524
2525
2526
2527
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is reponsible for
** generating the code that loops through a table looking for applicable
** rows.  Indices are selected and used to speed the search when doing
** so is applicable.  Because this module is responsible for selecting
** indices, you might also think of this module as the "query optimizer".
**
** $Id: where.c,v 1.292 2008/03/26 14:56:35 drh Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8)
................................................................................
        testOp = OP_IdxGE;
      }else{
        testOp = nEq>0 ? OP_IdxGE : OP_Noop;
        topEq = 1;
      }
      if( testOp!=OP_Noop || (isMinQuery&&bRev) ){
        int nCol = nEq + topLimit;
        if( isMinQuery && !topLimit ){
          sqlite3VdbeAddOp2(v, OP_Null, 0, regBase+nCol);
          nCol++;
          topEq = 0;
        }
        buildIndexProbe(v, nCol, pIdx, regBase, pLevel->iMem);
        if( bRev ){
          int op = topEq ? OP_MoveLe : OP_MoveLt;







|







 







|







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
....
2513
2514
2515
2516
2517
2518
2519
2520
2521
2522
2523
2524
2525
2526
2527
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is reponsible for
** generating the code that loops through a table looking for applicable
** rows.  Indices are selected and used to speed the search when doing
** so is applicable.  Because this module is responsible for selecting
** indices, you might also think of this module as the "query optimizer".
**
** $Id: where.c,v 1.293 2008/03/28 18:11:17 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8)
................................................................................
        testOp = OP_IdxGE;
      }else{
        testOp = nEq>0 ? OP_IdxGE : OP_Noop;
        topEq = 1;
      }
      if( testOp!=OP_Noop || (isMinQuery&&bRev) ){
        int nCol = nEq + topLimit;
        if( isMinQuery && bRev && !topLimit ){
          sqlite3VdbeAddOp2(v, OP_Null, 0, regBase+nCol);
          nCol++;
          topEq = 0;
        }
        buildIndexProbe(v, nCol, pIdx, regBase, pLevel->iMem);
        if( bRev ){
          int op = topEq ? OP_MoveLe : OP_MoveLt;

Changes to test/minmax3.test.

4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
...
131
132
133
134
135
136
137















138























139

# 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: minmax3.test,v 1.2 2008/03/19 00:21:31 drh Exp $

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

# Do an SQL statement.  Append the search count to the end of the result.
#
proc count sql {
................................................................................
  execsql   { SELECT y from t1}
  count   { SELECT max(y) FROM t1; }
} {VI 0}
do_test minmax3-1.4.4 {
  execsql { DROP INDEX i1 }
} {}








































finish_test








|







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

>
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
...
131
132
133
134
135
136
137
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
178
# 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: minmax3.test,v 1.3 2008/03/28 18:11:17 danielk1977 Exp $

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

# Do an SQL statement.  Append the search count to the end of the result.
#
proc count sql {
................................................................................
  execsql   { SELECT y from t1}
  count   { SELECT max(y) FROM t1; }
} {VI 0}
do_test minmax3-1.4.4 {
  execsql { DROP INDEX i1 }
} {}

do_test minmax3-2.1 {
  execsql {
    CREATE TABLE t2(a, b);
    CREATE INDEX i3 ON t2(a, b);
    INSERT INTO t2 VALUES(1, NULL);
    INSERT INTO t2 VALUES(1, 1);
    INSERT INTO t2 VALUES(1, 2);
    INSERT INTO t2 VALUES(1, 3);
    INSERT INTO t2 VALUES(2, NULL);
    INSERT INTO t2 VALUES(2, 1);
    INSERT INTO t2 VALUES(2, 2);
    INSERT INTO t2 VALUES(2, 3);
    INSERT INTO t2 VALUES(3, 1);
    INSERT INTO t2 VALUES(3, 2);
    INSERT INTO t2 VALUES(3, 3);
  }
} {}
do_test minmax3-2.2 {
  execsql { SELECT min(b) FROM t2 WHERE a = 1; }
} {1}
do_test minmax3-2.3 {
  execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b>1; }
} {2}
do_test minmax3-2.4 {
  execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b>-1; }
} {1}
do_test minmax3-2.5 {
  execsql { SELECT min(b) FROM t2 WHERE a = 1; }
} {1}
do_test minmax3-2.6 {
  execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b<2; }
} {1}
do_test minmax3-2.7 {
  execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b<1; }
} {{}}
do_test minmax3-2.8 {
  execsql { SELECT min(b) FROM t2 WHERE a = 3 AND b<1; }
} {{}}

finish_test