/ Check-in [6a45fe3b]
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 | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 6a45fe3bd7e19cf9c20fc6cb65b0269cdd704490
User & Date: drh 2003-02-20 01:48:13
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: 66158843 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: 6a45fe3b 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: 6ef91a36 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/vdbe.c.

32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
....
4187
4188
4189
4190
4191
4192
4193

4194
4195
4196
4197
4198
4199
4200
**
** 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.
................................................................................
*/
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 







|







 







>







32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
....
4187
4188
4189
4190
4191
4192
4193
4194
4195
4196
4197
4198
4199
4200
4201
**
** 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.
................................................................................
*/
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
...
348
349
350
351
352
353
354

























355
#    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);
................................................................................
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







|







 







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

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
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
#    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);
................................................................................
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