/ Check-in [fa07c360]
Login

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

Overview
Comment:Improved testing of the min/max optimization. (CVS 5373)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: fa07c360b708324c47c8e9931f1e2b1b24e4faf8
User & Date: drh 2008-07-08 18:05:26
References
2019-08-03
14:30 Ticket [71e183ca] MIN() malfunctions for a query with ISNULL condition status still Open with 4 other changes artifact: afd8a0b2 user: drh
Context
2008-07-08
19:34
Completely rework the sqlite3SetString() primitive so that it honors the SQLITE_LIMIT_LENGTH and avoids the use of strlen(). (CVS 5374) check-in: 8ed04b1e user: drh tags: trunk
18:05
Improved testing of the min/max optimization. (CVS 5373) check-in: fa07c360 user: drh tags: trunk
17:43
Prevent the flattening optimization from transforming a to a query with an illegal ORDER BY clause. (CVS 5372) check-in: 6c2adade 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
....
4237
4238
4239
4240
4241
4242
4243
4244
4245
4246
4247
4248
4249
4250
4251
**    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.448 2008/07/08 17:43:57 danielk1977 Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
      **     satisfying the 'ORDER BY' clause than it does in other cases.
      **     Refer to code and comments in where.c for details.
      */
      flag = minMaxQuery(pParse, p);
      if( flag ){
        pDel = pMinMax = sqlite3ExprListDup(db, p->pEList->a[0].pExpr->pList);
        if( pMinMax && !db->mallocFailed ){
          pMinMax->a[0].sortOrder = ((flag==WHERE_ORDERBY_MIN)?0:1);
          pMinMax->a[0].pExpr->op = TK_COLUMN;
        }
      }

      /* This case runs if the aggregate has no GROUP BY clause.  The
      ** processing is much simpler since there is only a single row
      ** of output.







|







 







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
4237
4238
4239
4240
4241
4242
4243
4244
4245
4246
4247
4248
4249
4250
4251
**    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.449 2008/07/08 18:05:26 drh Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
      **     satisfying the 'ORDER BY' clause than it does in other cases.
      **     Refer to code and comments in where.c for details.
      */
      flag = minMaxQuery(pParse, p);
      if( flag ){
        pDel = pMinMax = sqlite3ExprListDup(db, p->pEList->a[0].pExpr->pList);
        if( pMinMax && !db->mallocFailed ){
          pMinMax->a[0].sortOrder = flag!=WHERE_ORDERBY_MIN;
          pMinMax->a[0].pExpr->op = TK_COLUMN;
        }
      }

      /* This case runs if the aggregate has no GROUP BY clause.  The
      ** processing is much simpler since there is only a single row
      ** of output.

Changes to src/where.c.

12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
....
2153
2154
2155
2156
2157
2158
2159

2160
2161
2162
2163
2164
2165
2166
2167
....
2542
2543
2544
2545
2546
2547
2548
2549
2550


2551
2552
2553
2554
2555
2556
2557
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is responsible 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.311 2008/06/26 18:04:03 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8)
................................................................................
      if( IsVirtual(pTabItem->pTab) ){
        sqlite3_index_info **ppIdxInfo = &pWInfo->a[j].pIdxInfo;
        cost = bestVirtualIndex(pParse, &wc, pTabItem, notReady,
                                ppOrderBy ? *ppOrderBy : 0, i==0,
                                ppIdxInfo);
        flags = WHERE_VIRTUALTABLE;
        pIndex = *ppIdxInfo;

        if( pIndex && pIndex->orderByConsumed ){
          flags = WHERE_VIRTUALTABLE | WHERE_ORDERBY;
        }
        pIdx = 0;
        nEq = 0;
        if( (SQLITE_BIG_DBL/2.0)<cost ){
          /* The cost is not allowed to be larger than SQLITE_BIG_DBL (the
          ** inital value of lowestCost in this loop. If it is, then
................................................................................
      ** should not have a NULL value stored in 'x'. If column 'x' is
      ** the first one after the nEq equality constraints in the index,
      ** this requires some special handling.
      */
      if( (wflags&WHERE_ORDERBY_MIN)!=0
       && (pLevel->flags&WHERE_ORDERBY)
       && (pIdx->nColumn>nEq)
       && (pOrderBy->a[0].pExpr->iColumn==pIdx->aiColumn[nEq])
      ){


        isMinQuery = 1;
      }

      /* Find any inequality constraint terms for the start and end 
      ** of the range. 
      */
      if( pLevel->flags & WHERE_TOP_LIMIT ){







|







 







>
|







 







<

>
>







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
....
2153
2154
2155
2156
2157
2158
2159
2160
2161
2162
2163
2164
2165
2166
2167
2168
....
2543
2544
2545
2546
2547
2548
2549

2550
2551
2552
2553
2554
2555
2556
2557
2558
2559
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is responsible 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.312 2008/07/08 18:05:26 drh Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8)
................................................................................
      if( IsVirtual(pTabItem->pTab) ){
        sqlite3_index_info **ppIdxInfo = &pWInfo->a[j].pIdxInfo;
        cost = bestVirtualIndex(pParse, &wc, pTabItem, notReady,
                                ppOrderBy ? *ppOrderBy : 0, i==0,
                                ppIdxInfo);
        flags = WHERE_VIRTUALTABLE;
        pIndex = *ppIdxInfo;
        assert( pIndex!=0 );
        if( pIndex->orderByConsumed ){
          flags = WHERE_VIRTUALTABLE | WHERE_ORDERBY;
        }
        pIdx = 0;
        nEq = 0;
        if( (SQLITE_BIG_DBL/2.0)<cost ){
          /* The cost is not allowed to be larger than SQLITE_BIG_DBL (the
          ** inital value of lowestCost in this loop. If it is, then
................................................................................
      ** should not have a NULL value stored in 'x'. If column 'x' is
      ** the first one after the nEq equality constraints in the index,
      ** this requires some special handling.
      */
      if( (wflags&WHERE_ORDERBY_MIN)!=0
       && (pLevel->flags&WHERE_ORDERBY)
       && (pIdx->nColumn>nEq)

      ){
        assert( pOrderBy->nExpr==1 );
        assert( pOrderBy->a[0].pExpr->iColumn==pIdx->aiColumn[nEq] );
        isMinQuery = 1;
      }

      /* Find any inequality constraint terms for the start and end 
      ** of the range. 
      */
      if( pLevel->flags & WHERE_TOP_LIMIT ){

Changes to test/minmax.test.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
..
79
80
81
82
83
84
85













86
87
88
89
90
91
92
...
375
376
377
378
379
380
381
382
383

















































































































































384
#
#***********************************************************************
# 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.20 2008/01/05 17:39:30 danielk1977 Exp $

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

do_test minmax-1.0 {
  execsql {
    BEGIN;
................................................................................
do_test minmax-1.9 {
  set sqlite_search_count 0
  execsql {SELECT max(y) FROM t1}
} {5}
do_test minmax-1.10 {
  set sqlite_search_count
} {19}














do_test minmax-2.0 {
  execsql {
    CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
    INSERT INTO t2 SELECT * FROM t1;
  }
  set sqlite_search_count 0
................................................................................
  } {{} {}}
}
do_test minmax-10.12 {
  execsql {
    SELECT min(x), max(x) FROM t6;
  }
} {{} {}}



















































































































































finish_test







|







 







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







 









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

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
..
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
...
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
#
#***********************************************************************
# 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.21 2008/07/08 18:05:26 drh Exp $

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

do_test minmax-1.0 {
  execsql {
    BEGIN;
................................................................................
do_test minmax-1.9 {
  set sqlite_search_count 0
  execsql {SELECT max(y) FROM t1}
} {5}
do_test minmax-1.10 {
  set sqlite_search_count
} {19}

do_test minmax-1.21 {
  execsql {SELECT min(x) FROM t1 WHERE x=5}
} {5}
do_test minmax-1.22 {
  execsql {SELECT min(x) FROM t1 WHERE x>=5}
} {5}
do_test minmax-1.23 {
  execsql {SELECT min(x) FROM t1 WHERE x>=4.5}
} {5}
do_test minmax-1.24 {
  execsql {SELECT min(x) FROM t1 WHERE x<4.5}
} {1}

do_test minmax-2.0 {
  execsql {
    CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
    INSERT INTO t2 SELECT * FROM t1;
  }
  set sqlite_search_count 0
................................................................................
  } {{} {}}
}
do_test minmax-10.12 {
  execsql {
    SELECT min(x), max(x) FROM t6;
  }
} {{} {}}


do_test minmax-11.1 {
  execsql {
    CREATE INDEX t1i2 ON t1(y,x);
    SELECT min(x) FROM t1 WHERE y=5;
  }
} {16}
do_test minmax-11.2 {
  execsql {
    SELECT max(x) FROM t1 WHERE y=5;
  }
} {20}
do_test minmax-11.3 {
  execsql {
    SELECT min(x) FROM t1 WHERE y=6;
  }
} {{}}
do_test minmax-11.4 {
  execsql {
    SELECT max(x) FROM t1 WHERE y=6;
  }
} {{}}
do_test minmax-11.5 {
  execsql {
    SELECT min(x) FROM t1 WHERE y=1;
  }
} {1}
do_test minmax-11.6 {
  execsql {
    SELECT max(x) FROM t1 WHERE y=1;
  }
} {1}
do_test minmax-11.7 {
  execsql {
    SELECT min(x) FROM t1 WHERE y=0;
  }
} {{}}
do_test minmax-11.8 {
  execsql {
    SELECT max(x) FROM t1 WHERE y=0;
  }
} {{}}
do_test minmax-11.9 {
  execsql {
    SELECT min(x) FROM t1 WHERE y=5 AND x>=17.5;
  }
} {18}
do_test minmax-11.10 {
  execsql {
    SELECT max(x) FROM t1 WHERE y=5 AND x>=17.5;
  }
} {20}

do_test minmax-12.1 {
  execsql {
    CREATE TABLE t7(a,b,c);
    INSERT INTO t7 SELECT y, x, x*y FROM t1;
    INSERT INTO t7 SELECT y, x, x*y+1000 FROM t1;
    CREATE INDEX t7i1 ON t7(a,b,c);
    SELECT min(a) FROM t7;
  }
} {1}
do_test minmax-12.2 {
  execsql {
    SELECT max(a) FROM t7;
  }
} {5}
do_test minmax-12.3 {
  execsql {
    SELECT max(a) FROM t7 WHERE a=5;
  }
} {5}
do_test minmax-12.4 {
  execsql {
    SELECT min(b) FROM t7 WHERE a=5;
  }
} {16}
do_test minmax-12.5 {
  execsql {
    SELECT max(b) FROM t7 WHERE a=5;
  }
} {20}
do_test minmax-12.6 {
  execsql {
    SELECT min(b) FROM t7 WHERE a=4;
  }
} {8}
do_test minmax-12.7 {
  execsql {
    SELECT max(b) FROM t7 WHERE a=4;
  }
} {15}
do_test minmax-12.8 {
  execsql {
    SELECT min(c) FROM t7 WHERE a=4 AND b=10;
  }
} {40}
do_test minmax-12.9 {
  execsql {
    SELECT max(c) FROM t7 WHERE a=4 AND b=10;
  }
} {1040}
do_test minmax-12.10 {
  execsql {
    SELECT min(rowid) FROM t7;
  }
} {1}
do_test minmax-12.11 {
  execsql {
    SELECT max(rowid) FROM t7;
  }
} {40}
do_test minmax-12.12 {
  execsql {
    SELECT min(rowid) FROM t7 WHERE a=3;
  }
} {4}
do_test minmax-12.13 {
  execsql {
    SELECT max(rowid) FROM t7 WHERE a=3;
  }
} {27}
do_test minmax-12.14 {
  execsql {
    SELECT min(rowid) FROM t7 WHERE a=3 AND b=5;
  }
} {5}
do_test minmax-12.15 {
  execsql {
    SELECT max(rowid) FROM t7 WHERE a=3 AND b=5;
  }
} {25}
do_test minmax-12.16 {
  execsql {
    SELECT min(rowid) FROM t7 WHERE a=3 AND b=5 AND c=1015;
  }
} {25}
do_test minmax-12.17 {
  execsql {
    SELECT max(rowid) FROM t7 WHERE a=3 AND b=5 AND c=15;
  }
} {5}




finish_test