SQLite

Check-in [6a45fe3bd7]
Login

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

Overview
Comment:When the right table in a LEFT OUTER JOIN contains an INTEGER PRIMARY KEY make sure that key is NULL if there is no row in the right table that matches the current row in the left table. Tickets #246 and #247. (CVS 873)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 6a45fe3bd7e19cf9c20fc6cb65b0269cdd704490
User & Date: drh 2003-02-20 01:48:13.000
Context
2003-02-26
13:52
Fix a memory leak associated with PRIMARY KEY in a CREATE TABLE statement that fails. Ticket #249. (CVS 1730) (check-in: 66158843df user: drh tags: trunk)
2003-02-20
01:48
When the right table in a LEFT OUTER JOIN contains an INTEGER PRIMARY KEY make sure that key is NULL if there is no row in the right table that matches the current row in the left table. Tickets #246 and #247. (CVS 873) (check-in: 6a45fe3bd7 user: drh tags: trunk)
00:44
Fix the parsing of the LIMIT clause when the limit and offset are separated by a comma. The offset comes before the limit in that case. Ticket #245. (CVS 872) (check-in: 6ef91a364b user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/vdbe.c.
︙︙
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

**
** 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.204 2003/02/16 22:21:32 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** The makefile scans this source file and creates the following
** array of string constants which are the names of all VDBE opcodes.








|








32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

**
** 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.205 2003/02/20 01:48:13 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** The makefile scans this source file and creates the following
** array of string constants which are the names of all VDBE opcodes.
︙︙
4187
4188
4189
4190
4191
4192
4193

4194
4195
4196
4197
4198
4199
4200

*/
case OP_NullRow: {
  int i = pOp->p1;
  BtCursor *pCrsr;

  if( VERIFY( i>=0 && i<p->nCursor && ) (pCrsr = p->aCsr[i].pCursor)!=0 ){
    p->aCsr[i].nullRow = 1;

  }
  break;
}

/* Opcode: Last P1 P2 *
**
** The next use of the Recno or Column or Next instruction for P1 








>








4187
4188
4189
4190
4191
4192
4193
4194
4195
4196
4197
4198
4199
4200
4201

*/
case OP_NullRow: {
  int i = pOp->p1;
  BtCursor *pCrsr;

  if( VERIFY( i>=0 && i<p->nCursor && ) (pCrsr = p->aCsr[i].pCursor)!=0 ){
    p->aCsr[i].nullRow = 1;
    p->aCsr[i].recnoIsValid = 0;
  }
  break;
}

/* Opcode: Last P1 P2 *
**
** The next use of the Recno or Column or Next instruction for P1
︙︙
Changes to test/join.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.
#
# This file implements tests for joins, including outer joins.
#
# $Id: join.test,v 1.7 2002/10/27 19:35:35 drh Exp $

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

do_test join-1.1 {
  execsql {
    CREATE TABLE t1(a,b,c);








|








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.
#
# This file implements tests for joins, including outer joins.
#
# $Id: join.test,v 1.8 2003/02/20 01:48:13 drh Exp $

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

do_test join-1.1 {
  execsql {
    CREATE TABLE t1(a,b,c);
︙︙
348
349
350
351
352
353
354

























355

do_test join-6.8 {
  execsql {
    SELECT * FROM t1, t2 WHERE t1.b=t2.b(+) AND t1.c=t2.c(+);
  }
} {1 2 3 2 3 4 2 3 4 3 4 5 3 4 5 {} {} {}}



























finish_test








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


348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380

do_test join-6.8 {
  execsql {
    SELECT * FROM t1, t2 WHERE t1.b=t2.b(+) AND t1.c=t2.c(+);
  }
} {1 2 3 2 3 4 2 3 4 3 4 5 3 4 5 {} {} {}}


# A test for ticket #247.
#
do_test join-7.1 {
  execsql {
    CREATE TABLE t7 (x, y);
    INSERT INTO t7 VALUES ("pa1", 1);
    INSERT INTO t7 VALUES ("pa2", NULL);
    INSERT INTO t7 VALUES ("pa3", NULL);
    INSERT INTO t7 VALUES ("pa4", 2);
    INSERT INTO t7 VALUES ("pa30", 131);
    INSERT INTO t7 VALUES ("pa31", 130);
    INSERT INTO t7 VALUES ("pa28", NULL);

    CREATE TABLE t8 (a integer primary key, b);
    INSERT INTO t8 VALUES (1, "pa1");
    INSERT INTO t8 VALUES (2, "pa4");
    INSERT INTO t8 VALUES (3, NULL);
    INSERT INTO t8 VALUES (4, NULL);
    INSERT INTO t8 VALUES (130, "pa31");
    INSERT INTO t8 VALUES (131, "pa30");

    SELECT coalesce(t8.a,999) from t7 LEFT JOIN t8 on y=a;
  }
} {1 999 999 2 131 130 999}

finish_test