Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Fix a problem with NULL handling in aggregate min/max when returning values from the row containing the min or max. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | output-minmax-row |
Files: | files | file ages | folders |
SHA1: |
f27c7b4fb193126548e6a620ac89664d |
User & Date: | drh 2012-02-02 18:42:09.724 |
Context
2012-02-02
| ||
18:46 | When non-aggregate columns occur in an aggregate query with a single min() or max(), then the values of the non-aggregate columns are taken from one of the rows that was the min() or max(). (check-in: fa13edd39c user: drh tags: trunk) | |
18:42 | Fix a problem with NULL handling in aggregate min/max when returning values from the row containing the min or max. (Closed-Leaf check-in: f27c7b4fb1 user: drh tags: output-minmax-row) | |
17:35 | For queries of the form "SELECT p, max(q) FROM t1", the value of column p returned is the one on the same row that holds the maximum value of q. (check-in: adb29232b6 user: drh tags: output-minmax-row) | |
Changes
Changes to src/func.c.
︙ | ︙ | |||
1338 1339 1340 1341 1342 1343 1344 | int NotUsed, sqlite3_value **argv ){ Mem *pArg = (Mem *)argv[0]; Mem *pBest; UNUSED_PARAMETER(NotUsed); | < > > | | 1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 | int NotUsed, sqlite3_value **argv ){ Mem *pArg = (Mem *)argv[0]; Mem *pBest; UNUSED_PARAMETER(NotUsed); pBest = (Mem *)sqlite3_aggregate_context(context, sizeof(*pBest)); if( !pBest ) return; if( sqlite3_value_type(argv[0])==SQLITE_NULL ){ if( pBest->flags ) sqlite3SkipAccumulatorLoad(context); }else if( pBest->flags ){ int max; int cmp; CollSeq *pColl = sqlite3GetFuncCollSeq(context); /* This step function is used for both the min() and max() aggregates, ** the only difference between the two being that the sense of the ** comparison is inverted. For the max() aggregate, the ** sqlite3_user_data() function returns (void *)-1. For min() it |
︙ | ︙ | |||
1369 1370 1371 1372 1373 1374 1375 | sqlite3VdbeMemCopy(pBest, pArg); } } static void minMaxFinalize(sqlite3_context *context){ sqlite3_value *pRes; pRes = (sqlite3_value *)sqlite3_aggregate_context(context, 0); if( pRes ){ | | | 1370 1371 1372 1373 1374 1375 1376 1377 1378 1379 1380 1381 1382 1383 1384 | sqlite3VdbeMemCopy(pBest, pArg); } } static void minMaxFinalize(sqlite3_context *context){ sqlite3_value *pRes; pRes = (sqlite3_value *)sqlite3_aggregate_context(context, 0); if( pRes ){ if( pRes->flags ){ sqlite3_result_value(context, pRes); } sqlite3VdbeMemRelease(pRes); } } /* |
︙ | ︙ |
Changes to test/e_select.test.
︙ | ︙ | |||
796 797 798 799 800 801 802 | 2 "SELECT * FROM z1,z2 LIMIT 1" {51.65 -59.58 belfries {} 21} 3 "SELECT z1.* FROM z1,z2 LIMIT 1" {51.65 -59.58 belfries} 4 "SELECT z2.* FROM z1,z2 LIMIT 1" {{} 21} 5 "SELECT z2.*, z1.* FROM z1,z2 LIMIT 1" {{} 21 51.65 -59.58 belfries} 6 "SELECT count(*), * FROM z1" {6 63 born -26} 7 "SELECT max(a), * FROM z1" {63 63 born -26} | | | 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 | 2 "SELECT * FROM z1,z2 LIMIT 1" {51.65 -59.58 belfries {} 21} 3 "SELECT z1.* FROM z1,z2 LIMIT 1" {51.65 -59.58 belfries} 4 "SELECT z2.* FROM z1,z2 LIMIT 1" {{} 21} 5 "SELECT z2.*, z1.* FROM z1,z2 LIMIT 1" {{} 21 51.65 -59.58 belfries} 6 "SELECT count(*), * FROM z1" {6 63 born -26} 7 "SELECT max(a), * FROM z1" {63 63 born -26} 8 "SELECT *, min(a) FROM z1" {-5 {} 75 -5} 9 "SELECT *,* FROM z1,z2 LIMIT 1" { 51.65 -59.58 belfries {} 21 51.65 -59.58 belfries {} 21 } 10 "SELECT z1.*,z1.* FROM z2,z1 LIMIT 1" { 51.65 -59.58 belfries 51.65 -59.58 belfries } |
︙ | ︙ |
Added test/minmax4.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 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 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 | # 2012 February 02 # # The author disclaims copyright to this source code. In place of # 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. # #*********************************************************************** # # Test for queries of the form: # # SELECT p, max(q) FROM t1; # # Demonstration that the value returned for p is on the same row as # the maximum q. # set testdir [file dirname $argv0] source $testdir/tester.tcl do_test minmax4-1.1 { db eval { CREATE TABLE t1(p,q); SELECT p, max(q) FROM t1; } } {{} {}} do_test minmax4-1.2 { db eval { SELECT p, min(q) FROM t1; } } {{} {}} do_test minmax4-1.3 { db eval { INSERT INTO t1 VALUES(1,2); SELECT p, max(q) FROM t1; } } {1 2} do_test minmax4-1.4 { db eval { SELECT p, min(q) FROM t1; } } {1 2} do_test minmax4-1.5 { db eval { INSERT INTO t1 VALUES(3,4); SELECT p, max(q) FROM t1; } } {3 4} do_test minmax4-1.6 { db eval { SELECT p, min(q) FROM t1; } } {1 2} do_test minmax4-1.7 { db eval { INSERT INTO t1 VALUES(5,0); SELECT p, max(q) FROM t1; } } {3 4} do_test minmax4-1.8 { db eval { SELECT p, min(q) FROM t1; } } {5 0} do_test minmax4-1.9 { db eval { INSERT INTO t1 VALUES(6,1); SELECT p, max(q) FROM t1; } } {3 4} do_test minmax4-1.10 { db eval { SELECT p, min(q) FROM t1; } } {5 0} do_test minmax4-1.11 { db eval { INSERT INTO t1 VALUES(7,NULL); SELECT p, max(q) FROM t1; } } {3 4} do_test minmax4-1.12 { db eval { SELECT p, min(q) FROM t1; } } {5 0} do_test minmax4-1.13 { db eval { DELETE FROM t1 WHERE q IS NOT NULL; SELECT p, max(q) FROM t1; } } {7 {}} do_test minmax4-1.14 { db eval { SELECT p, min(q) FROM t1; } } {7 {}} do_test minmax4-2.1 { db eval { CREATE TABLE t2(a,b,c); INSERT INTO t2 VALUES (1,null,2), (1,2,3), (1,1,4), (2,3,5); SELECT a, max(b), c FROM t2 GROUP BY a ORDER BY a; } } {1 2 3 2 3 5} do_test minmax4-2.2 { db eval { SELECT a, min(b), c FROM t2 GROUP BY a ORDER BY a; } } {1 1 4 2 3 5} do_test minmax4-2.3 { db eval { SELECT a, min(b), avg(b), count(b), c FROM t2 GROUP BY a ORDER BY a DESC; } } {2 3 3.0 1 5 1 1 1.5 2 4} do_test minmax4-2.4 { db eval { SELECT a, min(b), max(b), c FROM t2 GROUP BY a ORDER BY a; } } {1 1 2 3 2 3 3 5} do_test minmax4-2.5 { db eval { SELECT a, max(b), min(b), c FROM t2 GROUP BY a ORDER BY a; } } {1 2 1 4 2 3 3 5} do_test minmax4-2.6 { db eval { SELECT a, max(b), b, max(c), c FROM t2 GROUP BY a ORDER BY a; } } {1 2 1 4 4 2 3 3 5 5} do_test minmax4-2.7 { db eval { SELECT a, min(b), b, min(c), c FROM t2 GROUP BY a ORDER BY a; } } {1 1 {} 2 2 2 3 3 5 5} finish_test |