Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | NULLs compare equal to each other when computing GROUP BY categories. (CVS 2728) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
d9b0c9705379a8b3a28a83bb29fc1cd6 |
User & Date: | drh 2005-09-20 13:12:00.000 |
Context
2005-09-20
| ||
13:55 | Cleanup the implementation and the documentation of the new 0x200 P1 flag on the VDBE comparison operands. (CVS 2729) (check-in: f22d0f64a0 user: drh tags: trunk) | |
13:12 | NULLs compare equal to each other when computing GROUP BY categories. (CVS 2728) (check-in: d9b0c97053 user: drh tags: trunk) | |
08:47 | Update comments in where.c. No code changes. (CVS 2727) (check-in: bb84d27eda user: drh tags: trunk) | |
Changes
Changes to src/select.c.
︙ | ︙ | |||
8 9 10 11 12 13 14 | ** 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. ** | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | ** 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.273 2005/09/20 13:12:00 drh Exp $ */ #include "sqliteInt.h" /* ** Allocate a new Select structure and return a pointer to that ** structure. |
︙ | ︙ | |||
2848 2849 2850 2851 2852 2853 2854 2855 2856 2857 2858 2859 2860 2861 | int addrSetAbort; /* Set the abort flag and return */ int addrInitializeLoop; /* Start of code that initializes the input loop */ int addrTopOfLoop; /* Top of the input loop */ int addrGroupByChange; /* Code that runs when any GROUP BY term changes */ int addrProcessRow; /* Code to process a single input row */ int addrEnd; /* End of all processing */ int addrSortingIdx; /* The OP_OpenVirtual for the sorting index */ addrEnd = sqlite3VdbeMakeLabel(v); /* Convert TK_COLUMN nodes into TK_AGG_COLUMN and make entries in ** sAggInfo for all TK_AGG_FUNCTION nodes in expressions of the ** SELECT statement. */ | > | 2848 2849 2850 2851 2852 2853 2854 2855 2856 2857 2858 2859 2860 2861 2862 | int addrSetAbort; /* Set the abort flag and return */ int addrInitializeLoop; /* Start of code that initializes the input loop */ int addrTopOfLoop; /* Top of the input loop */ int addrGroupByChange; /* Code that runs when any GROUP BY term changes */ int addrProcessRow; /* Code to process a single input row */ int addrEnd; /* End of all processing */ int addrSortingIdx; /* The OP_OpenVirtual for the sorting index */ int addrReset; /* Subroutine for resetting the accumulator */ addrEnd = sqlite3VdbeMakeLabel(v); /* Convert TK_COLUMN nodes into TK_AGG_COLUMN and make entries in ** sAggInfo for all TK_AGG_FUNCTION nodes in expressions of the ** SELECT statement. */ |
︙ | ︙ | |||
2942 2943 2944 2945 2946 2947 2948 2949 2950 2951 2952 2953 2954 2955 2956 2957 2958 2959 2960 2961 2962 | rc = selectInnerLoop(pParse, p, p->pEList, 0, 0, pOrderBy, distinct, eDest, iParm, addrOutputRow+1, addrSetAbort, aff); if( rc ){ goto select_end; } sqlite3VdbeAddOp(v, OP_Return, 0, 0); /* Begin a loop that will extract all source rows in GROUP BY order. ** This might involve two separate loops with an OP_Sort in between, or ** it might be a single loop that uses an index to extract information ** in the right order to begin with. */ sqlite3VdbeResolveLabel(v, addrInitializeLoop); pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pGroupBy); if( pWInfo==0 ) goto select_end; if( pGroupBy==0 ){ /* The optimizer is able to deliver rows in group by order so ** we do not have to sort. The OP_OpenVirtual table will be ** cancelled later because we still need to use the pKeyInfo */ | > > > > > > > | 2943 2944 2945 2946 2947 2948 2949 2950 2951 2952 2953 2954 2955 2956 2957 2958 2959 2960 2961 2962 2963 2964 2965 2966 2967 2968 2969 2970 | rc = selectInnerLoop(pParse, p, p->pEList, 0, 0, pOrderBy, distinct, eDest, iParm, addrOutputRow+1, addrSetAbort, aff); if( rc ){ goto select_end; } sqlite3VdbeAddOp(v, OP_Return, 0, 0); /* Generate a subroutine that will reset the group-by accumulator */ addrReset = sqlite3VdbeCurrentAddr(v); resetAccumulator(pParse, &sAggInfo); sqlite3VdbeAddOp(v, OP_Return, 0, 0); /* Begin a loop that will extract all source rows in GROUP BY order. ** This might involve two separate loops with an OP_Sort in between, or ** it might be a single loop that uses an index to extract information ** in the right order to begin with. */ sqlite3VdbeResolveLabel(v, addrInitializeLoop); sqlite3VdbeAddOp(v, OP_Gosub, 0, addrReset); pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pGroupBy); if( pWInfo==0 ) goto select_end; if( pGroupBy==0 ){ /* The optimizer is able to deliver rows in group by order so ** we do not have to sort. The OP_OpenVirtual table will be ** cancelled later because we still need to use the pKeyInfo */ |
︙ | ︙ | |||
3006 3007 3008 3009 3010 3011 3012 | } for(j=pGroupBy->nExpr-1; j>=0; j--){ if( j<pGroupBy->nExpr-1 ){ sqlite3VdbeAddOp(v, OP_MemLoad, iBMem+j, 0); } sqlite3VdbeAddOp(v, OP_MemLoad, iAMem+j, 0); if( j==0 ){ | | | | | 3014 3015 3016 3017 3018 3019 3020 3021 3022 3023 3024 3025 3026 3027 3028 3029 3030 3031 3032 3033 3034 3035 3036 3037 3038 3039 3040 3041 3042 3043 3044 3045 3046 3047 3048 3049 3050 3051 | } for(j=pGroupBy->nExpr-1; j>=0; j--){ if( j<pGroupBy->nExpr-1 ){ sqlite3VdbeAddOp(v, OP_MemLoad, iBMem+j, 0); } sqlite3VdbeAddOp(v, OP_MemLoad, iAMem+j, 0); if( j==0 ){ sqlite3VdbeAddOp(v, OP_Eq, 0x200, addrProcessRow); }else{ sqlite3VdbeAddOp(v, OP_Ne, 0x300, addrGroupByChange); } sqlite3VdbeChangeP3(v, -1, (void*)pKeyInfo->aColl[j], P3_COLLSEQ); } /* Generate code that runs whenever the GROUP BY changes. ** Change in the GROUP BY are detected by the previous code ** block. If there were no changes, this block is skipped. ** ** This code copies current group by terms in b0,b1,b2,... ** over to a0,a1,a2. It then calls the output subroutine ** and resets the aggregate accumulator registers in preparation ** for the next GROUP BY batch. */ sqlite3VdbeResolveLabel(v, addrGroupByChange); for(j=0; j<pGroupBy->nExpr; j++){ sqlite3VdbeAddOp(v, OP_MemLoad, iBMem+j, 0); sqlite3VdbeAddOp(v, OP_MemStore, iAMem+j, 1); } sqlite3VdbeAddOp(v, OP_Gosub, 0, addrOutputRow); sqlite3VdbeAddOp(v, OP_IfMemPos, iAbortFlag, addrEnd); sqlite3VdbeAddOp(v, OP_Gosub, 0, addrReset); /* Update the aggregate accumulators based on the content of ** the current row */ sqlite3VdbeResolveLabel(v, addrProcessRow); updateAccumulator(pParse, &sAggInfo); sqlite3VdbeAddOp(v, OP_MemIncr, iUseFlag, 0); |
︙ | ︙ |
Changes to src/vdbe.c.
︙ | ︙ | |||
39 40 41 42 43 44 45 | ** ** 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. ** | | | 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 | ** ** 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.489 2005/09/20 13:12:00 drh Exp $ */ #include "sqliteInt.h" #include "os.h" #include <ctype.h> #include "vdbeInt.h" /* |
︙ | ︙ | |||
1407 1408 1409 1410 1411 1412 1413 | /* Opcode: Eq P1 P2 P3 ** ** Pop the top two elements from the stack. If they are equal, then ** jump to instruction P2. Otherwise, continue to the next instruction. ** ** If the 0x100 bit of P1 is true and either operand is NULL then take the | | | > | 1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 1422 1423 | /* Opcode: Eq P1 P2 P3 ** ** Pop the top two elements from the stack. If they are equal, then ** jump to instruction P2. Otherwise, continue to the next instruction. ** ** If the 0x100 bit of P1 is true and either operand is NULL then take the ** jump. If the 0x100 bit of P1 is clear then fall thru if either operand ** is NULL. If the 0x200 bit of P1 is set and both operands are NULL, then ** return true or take the jump - treat NULL values as just another number. ** ** The least significant byte of P1 (mask 0xff) must be an affinity character - ** 'n', 't', 'i' or 'o' - or 0x00. An attempt is made to coerce both values ** according to the affinity before the comparison is made. If the byte is ** 0x00, then numeric affinity is used. ** ** Once any conversions have taken place, and neither value is NULL, |
︙ | ︙ | |||
1477 1478 1479 1480 1481 1482 1483 | flags = pTos->flags|pNos->flags; /* If either value is a NULL P2 is not zero, take the jump if the least ** significant byte of P1 is true. If P2 is zero, then push a NULL onto ** the stack. */ if( flags&MEM_Null ){ | > > > > > > > > > > > > | | | > > | | | | | > | 1478 1479 1480 1481 1482 1483 1484 1485 1486 1487 1488 1489 1490 1491 1492 1493 1494 1495 1496 1497 1498 1499 1500 1501 1502 1503 1504 1505 1506 1507 1508 1509 1510 1511 1512 1513 1514 | flags = pTos->flags|pNos->flags; /* If either value is a NULL P2 is not zero, take the jump if the least ** significant byte of P1 is true. If P2 is zero, then push a NULL onto ** the stack. */ if( flags&MEM_Null ){ if( (pOp->p1 & 0x200)!=0 && (pTos->flags & pNos->flags & MEM_Null)!=0 ){ /* If the 0x200 bit of P1 is set and *both* operands are NULL, then ** pretend that both operands are integer 0. This will cause the the ** various comparison operators to threat NULL just like any other value. */ pTos->flags = pNos->flags = MEM_Int; pTos->i = pNos->i = 0; }else{ /* If the 0x200 bit of P1 is clear or only one of the operands is NULL, ** then the result is always NULL. The jump is taken if the 0x100 bit ** of P1 is set. */ popStack(&pTos, 2); if( pOp->p2 ){ if( pOp->p1 & 0x100 ){ pc = pOp->p2-1; } }else{ pTos++; pTos->flags = MEM_Null; } break; } } affinity = pOp->p1 & 0xFF; if( affinity ){ applyAffinity(pNos, affinity, db->enc); applyAffinity(pTos, affinity, db->enc); } |
︙ | ︙ |
Changes to test/select5.test.
︙ | ︙ | |||
8 9 10 11 12 13 14 | # 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. # | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | # 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.14 2005/09/20 13:12:00 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Build some test data # execsql { |
︙ | ︙ | |||
152 153 154 155 156 157 158 159 | } {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 | > > > > > > > > > > > > > | 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 | } {1 2 1 4 6 4} do_test select5-5.5 { execsql { SELECT a, b FROM t2 GROUP BY a; } } {1 4 6 4} # NULL compare equal to each other for the purposes of processing # the GROUP BY clause. # do_test select5-6.1 { execsql { CREATE TABLE t3(x,y); INSERT INTO t3 VALUES(1,NULL); INSERT INTO t3 VALUES(2,NULL); INSERT INTO t3 VALUES(3,4); SELECT count(x), y FROM t3 GROUP BY y ORDER BY 1 } } {1 4 2 {}} finish_test |