/ Check-in [704d3931]
Login

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

Overview
Comment:Update the parser so that sub-queries and CTEs may have WITH clauses.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | common-table-expr
Files: files | file ages | folders
SHA1: 704d3931b855562a619769955969d439c42ca406
User & Date: dan 2014-01-11 19:19:36
Context
2014-01-13
15:12
Add code to handle non-recursive CTEs in the same way as SQL views. check-in: a26f399b user: dan tags: common-table-expr
2014-01-11
19:19
Update the parser so that sub-queries and CTEs may have WITH clauses. check-in: 704d3931 user: dan tags: common-table-expr
13:22
Parse common table expressions. But do not do anything with them (yet). check-in: da98b720 user: drh tags: common-table-expr
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/build.c.

4196
4197
4198
4199
4200
4201
4202

4203
4204

4205
4206

4207

4208
4209
4210
4211
4212
4213

4214
4215
4216


4217
4218

4219
4220
4221
      }
    }
  }
  return sqlite3KeyInfoRef(pIdx->pKeyInfo);
}

#ifndef SQLITE_OMIT_CTE

/* This routine is invoked when a single with_query of a
** common-table-expression has been parsed.  Record the query.

*/
void sqlite3CteAdd(

  Parse *pParse,          /* Parsing context */

  Token *pName,           /* Name of the common-table */
  ExprList *pArgList,     /* Optional column name list for the table */
  Select *pQuery          /* Query used to initialize the table */
){
  sqlite3ExprListDelete(pParse->db, pArgList);
  sqlite3SelectDelete(pParse->db, pQuery);

}

/* This routine is invoked at the end of the entire WITH clause.


*/
void sqlite3CteFinish(Parse *pParse, int isRecursive){

  /* TBD */
}
#endif /* !defined(SQLITE_OMIT_CTE) */







>
|
<
>

<
>

>

|


|

>


<
>
>

<
>



4196
4197
4198
4199
4200
4201
4202
4203
4204

4205
4206

4207
4208
4209
4210
4211
4212
4213
4214
4215
4216
4217
4218

4219
4220
4221

4222
4223
4224
4225
      }
    }
  }
  return sqlite3KeyInfoRef(pIdx->pKeyInfo);
}

#ifndef SQLITE_OMIT_CTE
/* 
** This routine is invoked once per CTE by the parser while parsing a 

** WITH clause. 
*/

With *sqlite3WithAdd(
  Parse *pParse,          /* Parsing context */
  With *pWith,            /* Existing WITH clause, or NULL */
  Token *pName,           /* Name of the common-table */
  IdList *pArglist,       /* Optional column name list for the table */
  Select *pQuery          /* Query used to initialize the table */
){
  sqlite3IdListDelete(pParse->db, pArglist);
  sqlite3SelectDelete(pParse->db, pQuery);
  return 0;
}


/*
** Free the contents of the With object passed as the second argument.
*/

void sqlite3WithDelete(sqlite3 *db, With *pWith){
  /* TBD */
}
#endif /* !defined(SQLITE_OMIT_CTE) */

Changes to src/parse.y.

392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409


410
411
412


413
414
415
416
417
418
419
420
421
422
....
1361
1362
1363
1364
1365
1366
1367




1368
1369
1370

1371
1372
1373

1374
1375

1376



1377
cmd ::= DROP VIEW ifexists(E) fullname(X). {
  sqlite3DropTable(pParse, X, 1, E);
}
%endif  SQLITE_OMIT_VIEW

//////////////////////// The SELECT statement /////////////////////////////////
//
cmd ::= with select(X).  {
  SelectDest dest = {SRT_Output, 0, 0, 0, 0};
  sqlite3Select(pParse, X, &dest);
  sqlite3ExplainBegin(pParse->pVdbe);
  sqlite3ExplainSelect(pParse->pVdbe, X);
  sqlite3ExplainFinish(pParse->pVdbe);
  sqlite3SelectDelete(pParse->db, X);
}

%type select {Select*}
%destructor select {sqlite3SelectDelete(pParse->db, $$);}


%type oneselect {Select*}
%destructor oneselect {sqlite3SelectDelete(pParse->db, $$);}



select(A) ::= oneselect(X).                      {A = X;}
%ifndef SQLITE_OMIT_COMPOUND_SELECT
select(A) ::= select(X) multiselect_op(Y) oneselect(Z).  {
  if( Z ){
    Z->op = (u8)Y;
    Z->pPrior = X;
    if( Y!=TK_ALL ) pParse->hasCompound = 1;
  }else{
    sqlite3SelectDelete(pParse->db, X);
  }
................................................................................
anylist ::= .
anylist ::= anylist LP anylist RP.
anylist ::= anylist ANY.
%endif  SQLITE_OMIT_VIRTUALTABLE


//////////////////////// COMMON TABLE EXPRESSIONS ////////////////////////////




with ::= .
%ifndef SQLITE_OMIT_CTE
with ::= WITH wqlist.              {sqlite3CteFinish(pParse, 0);}

with ::= WITH RECURSIVE wqlist.    {sqlite3CteFinish(pParse, 1);}
wqlist ::= with_query.
wqlist ::= wqlist COMMA with_query.

with_query ::= nm(X) idxlist_opt(Y) AS LP select(Z) RP. {
  sqlite3CteAdd(pParse, &X, Y, Z);

}



%endif  SQLITE_OMIT_CTE







|










>
>



>
>
|

|







 







>
>
>
>
|

<
>
|
<
<
>
|
<
>

>
>
>

392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
....
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377

1378
1379


1380
1381

1382
1383
1384
1385
1386
1387
cmd ::= DROP VIEW ifexists(E) fullname(X). {
  sqlite3DropTable(pParse, X, 1, E);
}
%endif  SQLITE_OMIT_VIEW

//////////////////////// The SELECT statement /////////////////////////////////
//
cmd ::= select(X).  {
  SelectDest dest = {SRT_Output, 0, 0, 0, 0};
  sqlite3Select(pParse, X, &dest);
  sqlite3ExplainBegin(pParse->pVdbe);
  sqlite3ExplainSelect(pParse->pVdbe, X);
  sqlite3ExplainFinish(pParse->pVdbe);
  sqlite3SelectDelete(pParse->db, X);
}

%type select {Select*}
%destructor select {sqlite3SelectDelete(pParse->db, $$);}
%type selectnowith {Select*}
%destructor selectnowith {sqlite3SelectDelete(pParse->db, $$);}
%type oneselect {Select*}
%destructor oneselect {sqlite3SelectDelete(pParse->db, $$);}

select(A) ::= with selectnowith(X). {A = X;}

selectnowith(A) ::= oneselect(X).                      {A = X;}
%ifndef SQLITE_OMIT_COMPOUND_SELECT
selectnowith(A) ::= selectnowith(X) multiselect_op(Y) oneselect(Z).  {
  if( Z ){
    Z->op = (u8)Y;
    Z->pPrior = X;
    if( Y!=TK_ALL ) pParse->hasCompound = 1;
  }else{
    sqlite3SelectDelete(pParse->db, X);
  }
................................................................................
anylist ::= .
anylist ::= anylist LP anylist RP.
anylist ::= anylist ANY.
%endif  SQLITE_OMIT_VIRTUALTABLE


//////////////////////// COMMON TABLE EXPRESSIONS ////////////////////////////
%type with {With*}
%type wqlist {With*}
%destructor with {sqlite3WithDelete(pParse->db, $$);}

with(A) ::= . {A = 0;}
%ifndef SQLITE_OMIT_CTE

with(A) ::= WITH wqlist(W).              { A = W; }
with(A) ::= WITH RECURSIVE wqlist(W).    { A = W; }



wqlist(A) ::= nm(X) inscollist_opt(Y) AS LP select(Z) RP. {

  A = sqlite3WithAdd(pParse, 0, &X, Y, Z);
}
wqlist(A) ::= wqlist(W) COMMA nm(X) inscollist_opt(Y) AS LP select(Z) RP. {
  A = sqlite3WithAdd(pParse, W, &X, Y, Z);
}
%endif  SQLITE_OMIT_CTE

Changes to src/sqliteInt.h.

756
757
758
759
760
761
762

763
764
765
766
767
768
769
....
2627
2628
2629
2630
2631
2632
2633













2634
2635
2636
2637
2638
2639
2640
....
3326
3327
3328
3329
3330
3331
3332
3333
3334
3335
3336
3337
3338
3339
3340
3341
typedef struct TriggerPrg TriggerPrg;
typedef struct TriggerStep TriggerStep;
typedef struct UnpackedRecord UnpackedRecord;
typedef struct VTable VTable;
typedef struct VtabCtx VtabCtx;
typedef struct Walker Walker;
typedef struct WhereInfo WhereInfo;


/*
** Defer sourcing vdbe.h and btree.h until after the "u8" and 
** "BusyHandler" typedefs. vdbe.h also requires a few of the opaque
** pointer types (i.e. FuncDef) defined above.
*/
#include "btree.h"
................................................................................
** Return code from the parse-tree walking primitives and their
** callbacks.
*/
#define WRC_Continue    0   /* Continue down into children */
#define WRC_Prune       1   /* Omit children but continue walking siblings */
#define WRC_Abort       2   /* Abandon the tree walk */














/*
** Assuming zIn points to the first byte of a UTF-8 character,
** advance zIn to point to the first byte of the next UTF-8 character.
*/
#define SQLITE_SKIP_UTF8(zIn) {                        \
  if( (*(zIn++))>=0xc0 ){                              \
    while( (*zIn & 0xc0)==0x80 ){ zIn++; }             \
................................................................................
int sqlite3TempInMemory(const sqlite3*);
const char *sqlite3JournalModename(int);
#ifndef SQLITE_OMIT_WAL
  int sqlite3Checkpoint(sqlite3*, int, int, int*, int*);
  int sqlite3WalDefaultHook(void*,sqlite3*,const char*,int);
#endif
#ifndef SQLITE_OMIT_CTE
  void sqlite3CteAdd(Parse*,Token*,ExprList*,Select*);
  void sqlite3CteFinish(Parse*,int);
#endif

/* Declarations for functions in fkey.c. All of these are replaced by
** no-op macros if OMIT_FOREIGN_KEY is defined. In this case no foreign
** key functionality is available. If OMIT_TRIGGER is defined but
** OMIT_FOREIGN_KEY is not, only some of the functions are no-oped. In
** this case foreign keys are parsed, but no other functionality is 







>







 







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







 







|
|







756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
....
2628
2629
2630
2631
2632
2633
2634
2635
2636
2637
2638
2639
2640
2641
2642
2643
2644
2645
2646
2647
2648
2649
2650
2651
2652
2653
2654
....
3340
3341
3342
3343
3344
3345
3346
3347
3348
3349
3350
3351
3352
3353
3354
3355
typedef struct TriggerPrg TriggerPrg;
typedef struct TriggerStep TriggerStep;
typedef struct UnpackedRecord UnpackedRecord;
typedef struct VTable VTable;
typedef struct VtabCtx VtabCtx;
typedef struct Walker Walker;
typedef struct WhereInfo WhereInfo;
typedef struct With With;

/*
** Defer sourcing vdbe.h and btree.h until after the "u8" and 
** "BusyHandler" typedefs. vdbe.h also requires a few of the opaque
** pointer types (i.e. FuncDef) defined above.
*/
#include "btree.h"
................................................................................
** Return code from the parse-tree walking primitives and their
** callbacks.
*/
#define WRC_Continue    0   /* Continue down into children */
#define WRC_Prune       1   /* Omit children but continue walking siblings */
#define WRC_Abort       2   /* Abandon the tree walk */

/*
** An instance of this structure represents a set of CTEs (common table 
** expressions) created by a single WITH clause.
*/
struct With {
  int nCte;                       /* Number of CTEs */
  struct Cte {
    const char *zName;            /* Name of this CTE */
    IdList *pCol;                 /* List of explicit column names, or NULL */
    Select *pSelect;              /* The contents of the CTE */
  } a[1];
};

/*
** Assuming zIn points to the first byte of a UTF-8 character,
** advance zIn to point to the first byte of the next UTF-8 character.
*/
#define SQLITE_SKIP_UTF8(zIn) {                        \
  if( (*(zIn++))>=0xc0 ){                              \
    while( (*zIn & 0xc0)==0x80 ){ zIn++; }             \
................................................................................
int sqlite3TempInMemory(const sqlite3*);
const char *sqlite3JournalModename(int);
#ifndef SQLITE_OMIT_WAL
  int sqlite3Checkpoint(sqlite3*, int, int, int*, int*);
  int sqlite3WalDefaultHook(void*,sqlite3*,const char*,int);
#endif
#ifndef SQLITE_OMIT_CTE
  With *sqlite3WithAdd(Parse*,With*,Token*,IdList*,Select*);
  void sqlite3WithDelete(sqlite3*,With*);
#endif

/* Declarations for functions in fkey.c. All of these are replaced by
** no-op macros if OMIT_FOREIGN_KEY is defined. In this case no foreign
** key functionality is available. If OMIT_TRIGGER is defined but
** OMIT_FOREIGN_KEY is not, only some of the functions are no-oped. In
** this case foreign keys are parsed, but no other functionality is 

Added test/with1.test.

















































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
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
# 2014 January 11
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the WITH clause.
#

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

do_execsql_test 1.0 {
  CREATE TABLE t1(x INTEGER, y INTEGER);
  WITH x(a) AS ( SELECT * FROM t1) SELECT 10
} {10}

do_execsql_test 1.1 {
  SELECT * FROM ( WITH x AS ( SELECT * FROM t1) SELECT 10 );
} {10}

do_execsql_test 1.2 {
  WITH x(a) AS ( SELECT * FROM t1) INSERT INTO t1 VALUES(1,2);
} {}

do_execsql_test 1.3 {
  WITH x(a) AS ( SELECT * FROM t1) DELETE FROM t1;
} {}

do_execsql_test 1.4 {
  WITH x(a) AS ( SELECT * FROM t1) UPDATE t1 SET x = y;
} {}
 
finish_test