SQLite

Check-in [29b48972b6]
Login

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

Overview
Comment:Report an error if a USING or ON clause is specified following a from-list element that is not to the right of a join operator. Fix for #3846. (CVS 6832)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 29b48972b65a17dab343b063a620cf8d456a923c
User & Date: danielk1977 2009-07-01 16:12:08.000
Context
2009-07-01
18:04
Fix a 1-byte buffer overwrite that can occur when a virtual table overloads an SQL function. (CVS 6833) (check-in: bfe336a899 user: danielk1977 tags: trunk)
16:12
Report an error if a USING or ON clause is specified following a from-list element that is not to the right of a join operator. Fix for #3846. (CVS 6832) (check-in: 29b48972b6 user: danielk1977 tags: trunk)
14:56
Do not ignore alias "a" in a query of the form "SELECT ... FROM (...) AS a" Fix for #3935. Also expand upon (6751) to fix some similar obscure memory leaks. (CVS 6831) (check-in: 42f9d1e564 user: danielk1977 tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/build.c.
︙︙
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

**     CREATE INDEX
**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**
** $Id: build.c,v 1.555 2009/07/01 14:56:40 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Initialize the pParse structure as needed.
*/








|








18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

**     CREATE INDEX
**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**
** $Id: build.c,v 1.556 2009/07/01 16:12:08 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Initialize the pParse structure as needed.
*/
︙︙
3204
3205
3206
3207
3208
3209
3210






3211
3212
3213
3214
3215
3216

3217
3218
3219
3220
3221
3222
3223
3224
3225
3226

3227


3228
3229
3230

3231
3232
3233
3234
3235
3236
3237
3238

  Token *pAlias,          /* The right-hand side of the AS subexpression */
  Select *pSubquery,      /* A subquery used in place of a table name */
  Expr *pOn,              /* The ON clause of a join */
  IdList *pUsing          /* The USING clause of a join */
){
  struct SrcList_item *pItem;
  sqlite3 *db = pParse->db;






  p = sqlite3SrcListAppend(db, p, pTable, pDatabase);
  if( p==0 || NEVER(p->nSrc==0) ){
    sqlite3ExprDelete(db, pOn);
    sqlite3IdListDelete(db, pUsing);
    sqlite3SelectDelete(db, pSubquery);
    return p;

  }
  pItem = &p->a[p->nSrc-1];
  assert( pAlias!=0 );
  if( pAlias->n ){
    pItem->zAlias = sqlite3NameFromToken(db, pAlias);
  }
  pItem->pSelect = pSubquery;
  if( p->nSrc>1 ){
    pItem->pOn = pOn;
    pItem->pUsing = pUsing;

  }else{


    sqlite3ExprDelete(db, pOn);
    sqlite3IdListDelete(db, pUsing);
  }

  return p;
}

/*
** Add an INDEXED BY or NOT INDEXED clause to the most recently added 
** element of the source-list passed as the second argument.
*/
void sqlite3SrcListIndexedBy(Parse *pParse, SrcList *p, Token *pIndexedBy){








>
>
>
>
>
>


<
<
<
<
>







<
|
|
>
|
>
>
|
|
<
>
|








3204
3205
3206
3207
3208
3209
3210
3211
3212
3213
3214
3215
3216
3217
3218




3219
3220
3221
3222
3223
3224
3225
3226

3227
3228
3229
3230
3231
3232
3233
3234

3235
3236
3237
3238
3239
3240
3241
3242
3243

  Token *pAlias,          /* The right-hand side of the AS subexpression */
  Select *pSubquery,      /* A subquery used in place of a table name */
  Expr *pOn,              /* The ON clause of a join */
  IdList *pUsing          /* The USING clause of a join */
){
  struct SrcList_item *pItem;
  sqlite3 *db = pParse->db;
  if( !p && (pOn || pUsing) ){
    sqlite3ErrorMsg(pParse, "a JOIN clause is required before %s", 
      (pOn ? "ON" : "USING")
    );
    goto append_from_error;
  }
  p = sqlite3SrcListAppend(db, p, pTable, pDatabase);
  if( p==0 || NEVER(p->nSrc==0) ){




    goto append_from_error;
  }
  pItem = &p->a[p->nSrc-1];
  assert( pAlias!=0 );
  if( pAlias->n ){
    pItem->zAlias = sqlite3NameFromToken(db, pAlias);
  }
  pItem->pSelect = pSubquery;

  pItem->pOn = pOn;
  pItem->pUsing = pUsing;
  return p;

 append_from_error:
  assert( p==0 );
  sqlite3ExprDelete(db, pOn);
  sqlite3IdListDelete(db, pUsing);

  sqlite3SelectDelete(db, pSubquery);
  return 0;
}

/*
** Add an INDEXED BY or NOT INDEXED clause to the most recently added 
** element of the source-list passed as the second argument.
*/
void sqlite3SrcListIndexedBy(Parse *pParse, SrcList *p, Token *pIndexedBy){
︙︙
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.26 2008/12/05 00:00:07 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.27 2009/07/01 16:12:08 danielk1977 Exp $

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

do_test join-1.1 {
  execsql {
    CREATE TABLE t1(a,b,c);
︙︙
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322

} {1 {cannot join using column a - column not present in both tables}}
do_test join-3.4.2 {
  catchsql {
    SELECT * FROM t1 JOIN t2 USING(d);
  }
} {1 {cannot join using column d - column not present in both tables}}
do_test join-3.5 {
  catchsql {
    SELECT * FROM t1 USING(a);
  }
} {0 {1 2 3 2 3 4 3 4 5}}
do_test join-3.6 {
  catchsql {
    SELECT * FROM t1 JOIN t2 ON t3.a=t2.b;
  }
} {1 {no such column: t3.a}}
do_test join-3.7 {
  catchsql {








<
|
<
|








305
306
307
308
309
310
311

312

313
314
315
316
317
318
319
320

} {1 {cannot join using column a - column not present in both tables}}
do_test join-3.4.2 {
  catchsql {
    SELECT * FROM t1 JOIN t2 USING(d);
  }
} {1 {cannot join using column d - column not present in both tables}}
do_test join-3.5 {

  catchsql { SELECT * FROM t1 USING(a) }

} {1 {a JOIN clause is required before USING}}
do_test join-3.6 {
  catchsql {
    SELECT * FROM t1 JOIN t2 ON t3.a=t2.b;
  }
} {1 {no such column: t3.a}}
do_test join-3.7 {
  catchsql {
︙︙
Changes to test/tkt3935.test.
︙︙
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52



53
54

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests to verify that ticket #3935 has been fixed.
#
# $Id: tkt3935.test,v 1.1 2009/07/01 14:56:41 danielk1977 Exp $

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

do_test tkt3935.1 {
  execsql {
    CREATE TABLE t1(a, b);
    CREATE TABLE t2(c, d);
  }
} {}

do_test tkt3935.2 {
  execsql { SELECT j1.b FROM ( SELECT * FROM t1 INNER JOIN t2 ON a=c ) AS j1 }
} {}
do_test tkt3935.3 {
  execsql { SELECT j1.b FROM (t1 INNER JOIN t2 ON a=c) AS j1 }
} {}


do_test tkt3935.4 {
  execsql { SELECT a FROM (t1) AS t ON b USING(a) }
} {}
do_test tkt3935.5 {
  execsql { SELECT a FROM (t1) AS t ON b }
} {}
do_test tkt3935.6 {
  execsql { SELECT a FROM (SELECT * FROM t1) AS t ON b USING(a) }
} {}
do_test tkt3935.7 {
  execsql { SELECT a FROM (SELECT * FROM t1) AS t ON b }
} {}
do_test tkt3935.8 {
  execsql { SELECT a FROM t1 AS t ON b }
} {}
do_test tkt3935.9 {
  execsql { SELECT a FROM t1 AS t ON b USING(a) }
} {}




finish_test








|




















|
|

|
|

|
|

|
|

|
|

|
|
>
>
>



8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests to verify that ticket #3935 has been fixed.
#
# $Id: tkt3935.test,v 1.2 2009/07/01 16:12:08 danielk1977 Exp $

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

do_test tkt3935.1 {
  execsql {
    CREATE TABLE t1(a, b);
    CREATE TABLE t2(c, d);
  }
} {}

do_test tkt3935.2 {
  execsql { SELECT j1.b FROM ( SELECT * FROM t1 INNER JOIN t2 ON a=c ) AS j1 }
} {}
do_test tkt3935.3 {
  execsql { SELECT j1.b FROM (t1 INNER JOIN t2 ON a=c) AS j1 }
} {}


do_test tkt3935.4 {
  catchsql { SELECT a FROM (t1) AS t ON b USING(a) }
} {1 {a JOIN clause is required before ON}}
do_test tkt3935.5 {
  catchsql { SELECT a FROM (t1) AS t ON b }
} {1 {a JOIN clause is required before ON}}
do_test tkt3935.6 {
  catchsql { SELECT a FROM (SELECT * FROM t1) AS t ON b USING(a) }
} {1 {a JOIN clause is required before ON}}
do_test tkt3935.7 {
  catchsql { SELECT a FROM (SELECT * FROM t1) AS t ON b }
} {1 {a JOIN clause is required before ON}}
do_test tkt3935.8 {
  catchsql { SELECT a FROM t1 AS t ON b }
} {1 {a JOIN clause is required before ON}}
do_test tkt3935.9 {
  catchsql { SELECT a FROM t1 AS t ON b USING(a) }
} {1 {a JOIN clause is required before ON}}
do_test tkt3935.10 {
  catchsql { SELECT a FROM t1 AS t USING(a) }
} {1 {a JOIN clause is required before USING}}

finish_test
Changes to test/vtab6.test.
︙︙
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for joins, including outer joins involving
# virtual tables. The test cases in this file are copied from the file
# join.test, and some of the comments still reflect that.
#
# $Id: vtab6.test,v 1.4 2008/07/12 14:52:21 drh Exp $

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

ifcapable !vtab {
  finish_test
  return








|








10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for joins, including outer joins involving
# virtual tables. The test cases in this file are copied from the file
# join.test, and some of the comments still reflect that.
#
# $Id: vtab6.test,v 1.5 2009/07/01 16:12:08 danielk1977 Exp $

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

ifcapable !vtab {
  finish_test
  return
︙︙
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278

} {1 {cannot have both ON and USING clauses in the same join}}
do_test vtab6-3.4 {
  catchsql {
    SELECT * FROM t1 JOIN t2 USING(a);
  }
} {1 {cannot join using column a - column not present in both tables}}
do_test vtab6-3.5 {
  catchsql {
    SELECT * FROM t1 USING(a);
  }
} {0 {1 2 3 2 3 4 3 4 5}}
do_test vtab6-3.6 {
  catchsql {
    SELECT * FROM t1 JOIN t2 ON t3.a=t2.b;
  }
} {1 {no such column: t3.a}}
do_test vtab6-3.7 {
  catchsql {








<
|
<
|








261
262
263
264
265
266
267

268

269
270
271
272
273
274
275
276

} {1 {cannot have both ON and USING clauses in the same join}}
do_test vtab6-3.4 {
  catchsql {
    SELECT * FROM t1 JOIN t2 USING(a);
  }
} {1 {cannot join using column a - column not present in both tables}}
do_test vtab6-3.5 {

  catchsql { SELECT * FROM t1 USING(a) }

} {1 {a JOIN clause is required before USING}}
do_test vtab6-3.6 {
  catchsql {
    SELECT * FROM t1 JOIN t2 ON t3.a=t2.b;
  }
} {1 {no such column: t3.a}}
do_test vtab6-3.7 {
  catchsql {
︙︙