SQLite

Check-in [c9e7996fb9]
Login

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

Overview
Comment:Ignore NULLs in a subquery as the right operand of IN. Ticket #565. (CVS 1175)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: c9e7996fb9080b715e9b273a3ac3ed3744e10a77
User & Date: drh 2004-01-14 13:38:54.000
Context
2004-01-14
13:43
Bump the version number for the next release. (CVS 1176) (check-in: 2812dd5fc9 user: drh tags: trunk)
13:38
Ignore NULLs in a subquery as the right operand of IN. Ticket #565. (CVS 1175) (check-in: c9e7996fb9 user: drh tags: trunk)
03:49
Version 2.8.10 (CVS 1174) (check-in: 8bef75ab85 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.146 2003/09/27 13:39:39 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.147 2004/01/14 13:38:54 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
475
476
477
478
479
480
481
482

483

484

485
486
487
488
489
490
491
492
493
494
495
496
497
498
    }

    /* If we are creating a set for an "expr IN (SELECT ...)" construct,
    ** then there should be a single item on the stack.  Write this
    ** item into the set table with bogus data.
    */
    case SRT_Set: {
      int lbl = sqliteVdbeMakeLabel(v);

      assert( nColumn==1 );

      sqliteVdbeAddOp(v, OP_IsNull, -1, lbl);

      if( pOrderBy ){
        pushOntoSorter(pParse, v, pOrderBy);
      }else{
        sqliteVdbeAddOp(v, OP_String, 0, 0);
        sqliteVdbeAddOp(v, OP_PutStrKey, iParm, 0);
      }
      sqliteVdbeResolveLabel(v, lbl);
      break;
    }

    /* If this is a scalar select that is part of an expression, then
    ** store the results in the appropriate memory cell and break out
    ** of the scan loop.
    */







|
>

>
|
>






|







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
    }

    /* If we are creating a set for an "expr IN (SELECT ...)" construct,
    ** then there should be a single item on the stack.  Write this
    ** item into the set table with bogus data.
    */
    case SRT_Set: {
      int addr1 = sqliteVdbeCurrentAddr(v);
      int addr2;
      assert( nColumn==1 );
      sqliteVdbeAddOp(v, OP_NotNull, -1, addr1+3);
      sqliteVdbeAddOp(v, OP_Pop, 1, 0);
      addr2 = sqliteVdbeAddOp(v, OP_Goto, 0, 0);
      if( pOrderBy ){
        pushOntoSorter(pParse, v, pOrderBy);
      }else{
        sqliteVdbeAddOp(v, OP_String, 0, 0);
        sqliteVdbeAddOp(v, OP_PutStrKey, iParm, 0);
      }
      sqliteVdbeChangeP2(v, addr2, sqliteVdbeCurrentAddr(v));
      break;
    }

    /* If this is a scalar select that is part of an expression, then
    ** store the results in the appropriate memory cell and break out
    ** of the scan loop.
    */
584
585
586
587
588
589
590

591

592
593
594
595
596
597
598
      sqliteVdbeAddOp(v, OP_NewRecno, iParm, 0);
      sqliteVdbeAddOp(v, OP_Pull, 1, 0);
      sqliteVdbeAddOp(v, OP_PutIntKey, iParm, 0);
      break;
    }
    case SRT_Set: {
      assert( nColumn==1 );

      sqliteVdbeAddOp(v, OP_IsNull, -1, sqliteVdbeCurrentAddr(v)+3);

      sqliteVdbeAddOp(v, OP_String, 0, 0);
      sqliteVdbeAddOp(v, OP_PutStrKey, iParm, 0);
      break;
    }
    case SRT_Mem: {
      assert( nColumn==1 );
      sqliteVdbeAddOp(v, OP_MemStore, iParm, 1);







>
|
>







587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
      sqliteVdbeAddOp(v, OP_NewRecno, iParm, 0);
      sqliteVdbeAddOp(v, OP_Pull, 1, 0);
      sqliteVdbeAddOp(v, OP_PutIntKey, iParm, 0);
      break;
    }
    case SRT_Set: {
      assert( nColumn==1 );
      sqliteVdbeAddOp(v, OP_NotNull, -1, sqliteVdbeCurrentAddr(v)+3);
      sqliteVdbeAddOp(v, OP_Pop, 1, 0);
      sqliteVdbeAddOp(v, OP_Goto, 0, sqliteVdbeCurrentAddr(v)+3);
      sqliteVdbeAddOp(v, OP_String, 0, 0);
      sqliteVdbeAddOp(v, OP_PutStrKey, iParm, 0);
      break;
    }
    case SRT_Mem: {
      assert( nColumn==1 );
      sqliteVdbeAddOp(v, OP_MemStore, iParm, 1);
Changes to test/misc3.test.
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc3.test,v 1.4 2004/01/06 01:52:34 drh Exp $

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

# Ticket #529.  Make sure an ABORT does not damage the in-memory cache
# that will be used by subsequent statements in the same transaction.
#







|







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc3.test,v 1.5 2004/01/14 13:38:54 drh Exp $

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

# Ticket #529.  Make sure an ABORT does not damage the in-memory cache
# that will be used by subsequent statements in the same transaction.
#
174
175
176
177
178
179
180


181


























182
} {2147483647 2147483646}
do_test misc3-3.14 {
  execsql {
    SELECT * FROM t2 WHERE a>=0 AND a<2147483647 ORDER BY a DESC;
  }
} {2147483646}






























finish_test







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

174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
} {2147483647 2147483646}
do_test misc3-3.14 {
  execsql {
    SELECT * FROM t2 WHERE a>=0 AND a<2147483647 ORDER BY a DESC;
  }
} {2147483646}

# Ticket #565.  A stack overflow is occurring when the subquery to the
# right of an IN operator contains many NULLs
#
do_test misc3-4.1 {
  execsql {
    CREATE TABLE t3(a INTEGER PRIMARY KEY, b);
    INSERT INTO t3(b) VALUES('abc');
    INSERT INTO t3(b) VALUES('xyz');
    INSERT INTO t3(b) VALUES(NULL);
    INSERT INTO t3(b) VALUES(NULL);
    INSERT INTO t3(b) SELECT b||'d' FROM t3;
    INSERT INTO t3(b) SELECT b||'e' FROM t3;
    INSERT INTO t3(b) SELECT b||'f' FROM t3;
    INSERT INTO t3(b) SELECT b||'g' FROM t3;
    INSERT INTO t3(b) SELECT b||'h' FROM t3;
    SELECT count(a), count(b) FROM t3;
  }
} {128 64}
do_test misc3-4.2 {
  execsql {
    SELECT count(a) FROM t3 WHERE b IN (SELECT b FROM t3);
  }
} {64}
do_test misc3-4.3 {
  execsql {
    SELECT count(a) FROM t3 WHERE b IN (SELECT b FROM t3 ORDER BY a+1);
  }
} {64}

finish_test