/ Check-in [29b48972]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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 | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 29b48972b65a17dab343b063a620cf8d456a923c
User & Date: danielk1977 2009-07-01 16:12:08
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: bfe336a8 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: 29b48972 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: 42f9d1e5 user: danielk1977 tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/build.c.

18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
....
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
**     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.
*/
................................................................................
  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){







|







 







>
>
>
>
>
>


<
<
<
<
>







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







18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
....
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
**     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.
*/
................................................................................
  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
...
305
306
307
308
309
310
311
312
313
314
315

316
317
318
319
320
321
322
#    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);
................................................................................
} {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 {







|







 







<
|
<
<
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
305
306
307
308
309
310
311

312


313
314
315
316
317
318
319
320
#    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);
................................................................................
} {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
..
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);
................................................................................
} {}
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
..
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);
................................................................................
} {}
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
...
261
262
263
264
265
266
267
268
269
270
271

272
273
274
275
276
277
278
#***********************************************************************
# 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
................................................................................
} {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 {







|







 







<
|
<
<
>







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
261
262
263
264
265
266
267

268


269
270
271
272
273
274
275
276
#***********************************************************************
# 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
................................................................................
} {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 {