SQLite

Check-in [d9b0c97053]
Login

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: d9b0c9705379a8b3a28a83bb29fc1cd688fe184e
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
Unified Diff Ignore Whitespace Patch
Changes to src/select.c.
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.272 2005/09/19 21:05:49 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.







|







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
3013
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
      }
      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, 0, addrProcessRow);
        }else{
          sqlite3VdbeAddOp(v, OP_Ne, 0x100, 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);
      resetAccumulator(pParse, &sAggInfo);

      /* Update the aggregate accumulators based on the content of
      ** the current row
      */
      sqlite3VdbeResolveLabel(v, addrProcessRow);
      updateAccumulator(pParse, &sAggInfo);
      sqlite3VdbeAddOp(v, OP_MemIncr, iUseFlag, 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
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.488 2005/09/17 15:20:28 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*







|







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
1414
1415

1416
1417
1418
1419
1420
1421
1422

/* 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 false then fall thru if either operand
** is NULL.

**
** 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, 







|
|
>







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












1484
1485
1486


1487
1488
1489
1490
1491

1492
1493
1494
1495
1496
1497
1498
  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 ){












    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);
  }







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







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
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.13 2005/09/08 20:37:44 drh Exp $

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

# Build some test data
#
execsql {







|







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