/ Check-in [83cbc4d8]
Login

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

Overview
Comment:Disallow the use of COLLATE clauses and the ASC and DESC keywords within foreign key constraints and in the argument list to common table expressions.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 83cbc4d8761498647794affffa961a4fca311be7
User & Date: drh 2015-08-24 15:39:42
Context
2015-08-24
17:18
Enhancements to the batch build tool for MSVC. check-in: a1ae20cd user: mistachkin tags: trunk
15:39
Disallow the use of COLLATE clauses and the ASC and DESC keywords within foreign key constraints and in the argument list to common table expressions. check-in: 83cbc4d8 user: drh tags: trunk
12:42
Improvements to JSON string dequoting. check-in: 196d66d3 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/build.c.

1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
....
2596
2597
2598
2599
2600
2601
2602


2603
2604
2605
2606
2607
2608
2609
....
3034
3035
3036
3037
3038
3039
3040
3041

3042
3043
3044
3045
3046
3047
3048
....
4278
4279
4280
4281
4282
4283
4284


























4285
4286
4287
4288
4289
4290
4291
....
4294
4295
4296
4297
4298
4299
4300


4301
4302
4303
4304
4305
4306
4307
          break;
        }
      }
    }
  }
  if( nTerm==1
   && zType && sqlite3StrICmp(zType, "INTEGER")==0
   && sortOrder==SQLITE_SO_ASC
  ){
    pTab->iPKey = iCol;
    pTab->keyConf = (u8)onError;
    assert( autoInc==0 || autoInc==1 );
    pTab->tabFlags |= autoInc*TF_Autoincrement;
    if( pList ) pParse->iPkSortOrder = pList->a[0].sortOrder;
  }else if( autoInc ){
................................................................................
  int nByte;
  int i;
  int nCol;
  char *z;

  assert( pTo!=0 );
  if( p==0 || IN_DECLARE_VTAB ) goto fk_end;


  if( pFromCol==0 ){
    int iCol = p->nCol-1;
    if( NEVER(iCol<0) ) goto fk_end;
    if( pToCol && pToCol->nExpr!=1 ){
      sqlite3ErrorMsg(pParse, "foreign key on %s"
         " should reference only one column of table %T",
         p->aCol[iCol].zName, pTo);
................................................................................
  ** So create a fake list to simulate this.
  */
  if( pList==0 ){
    pList = sqlite3ExprListAppend(pParse, 0, 0);
    if( pList==0 ) goto exit_create_index;
    pList->a[0].zName = sqlite3DbStrDup(pParse->db,
                                        pTab->aCol[pTab->nCol-1].zName);
    pList->a[0].sortOrder = (u8)sortOrder;

  }

  /* Figure out how many bytes of space are required to store explicitly
  ** specified collation sequence names.
  */
  for(i=0; i<pList->nExpr; i++){
    Expr *pExpr = pList->a[i].pExpr;
................................................................................
    if( pParse->nErr ){
      sqlite3KeyInfoUnref(pKey);
      pKey = 0;
    }
  }
  return pKey;
}



























#ifndef SQLITE_OMIT_CTE
/* 
** This routine is invoked once per CTE by the parser while parsing a 
** WITH clause. 
*/
With *sqlite3WithAdd(
................................................................................
  Token *pName,           /* Name of the common-table */
  ExprList *pArglist,     /* Optional column name list for the table */
  Select *pQuery          /* Query used to initialize the table */
){
  sqlite3 *db = pParse->db;
  With *pNew;
  char *zName;



  /* Check that the CTE name is unique within this WITH clause. If
  ** not, store an error in the Parse structure. */
  zName = sqlite3NameFromToken(pParse->db, pName);
  if( zName && pWith ){
    int i;
    for(i=0; i<pWith->nCte; i++){







|







 







>
>







 







|
>







 







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







 







>
>







1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
....
2596
2597
2598
2599
2600
2601
2602
2603
2604
2605
2606
2607
2608
2609
2610
2611
....
3036
3037
3038
3039
3040
3041
3042
3043
3044
3045
3046
3047
3048
3049
3050
3051
....
4281
4282
4283
4284
4285
4286
4287
4288
4289
4290
4291
4292
4293
4294
4295
4296
4297
4298
4299
4300
4301
4302
4303
4304
4305
4306
4307
4308
4309
4310
4311
4312
4313
4314
4315
4316
4317
4318
4319
4320
....
4323
4324
4325
4326
4327
4328
4329
4330
4331
4332
4333
4334
4335
4336
4337
4338
          break;
        }
      }
    }
  }
  if( nTerm==1
   && zType && sqlite3StrICmp(zType, "INTEGER")==0
   && sortOrder!=SQLITE_SO_DESC
  ){
    pTab->iPKey = iCol;
    pTab->keyConf = (u8)onError;
    assert( autoInc==0 || autoInc==1 );
    pTab->tabFlags |= autoInc*TF_Autoincrement;
    if( pList ) pParse->iPkSortOrder = pList->a[0].sortOrder;
  }else if( autoInc ){
................................................................................
  int nByte;
  int i;
  int nCol;
  char *z;

  assert( pTo!=0 );
  if( p==0 || IN_DECLARE_VTAB ) goto fk_end;
  sqlite3RestrictColumnListSyntax(pParse, pFromCol);
  sqlite3RestrictColumnListSyntax(pParse, pToCol);
  if( pFromCol==0 ){
    int iCol = p->nCol-1;
    if( NEVER(iCol<0) ) goto fk_end;
    if( pToCol && pToCol->nExpr!=1 ){
      sqlite3ErrorMsg(pParse, "foreign key on %s"
         " should reference only one column of table %T",
         p->aCol[iCol].zName, pTo);
................................................................................
  ** So create a fake list to simulate this.
  */
  if( pList==0 ){
    pList = sqlite3ExprListAppend(pParse, 0, 0);
    if( pList==0 ) goto exit_create_index;
    pList->a[0].zName = sqlite3DbStrDup(pParse->db,
                                        pTab->aCol[pTab->nCol-1].zName);
    assert( pList->nExpr==1 );
    sqlite3ExprListSetSortOrder(pList, sortOrder);
  }

  /* Figure out how many bytes of space are required to store explicitly
  ** specified collation sequence names.
  */
  for(i=0; i<pList->nExpr; i++){
    Expr *pExpr = pList->a[i].pExpr;
................................................................................
    if( pParse->nErr ){
      sqlite3KeyInfoUnref(pKey);
      pKey = 0;
    }
  }
  return pKey;
}

/*
** Generate a syntax error if the expression list provided contains
** any COLLATE or ASC or DESC keywords.
**
** Some legacy versions of SQLite allowed constructs like:
**
**      CREATE TABLE x(..., FOREIGN KEY(x COLLATE binary DESC) REFERENCES...);
**                                        ^^^^^^^^^^^^^^^^^^^
**
** The COLLATE and sort order terms were ignored.  To prevent compatibility
** problems in case something like this appears in a legacy sqlite_master
** table, only enforce the restriction on new SQL statements, not when
** parsing the schema out of the sqlite_master table.
*/
void sqlite3RestrictColumnListSyntax(Parse *pParse, ExprList *p){
  int i;
  if( p==0 || pParse->db->init.busy ) return;
  for(i=0; i<p->nExpr; i++){
    if( p->a[i].pExpr!=0 || p->a[i].bDefinedSO ){
      sqlite3ErrorMsg(pParse, "syntax error after column name \"%w\"",
                      p->a[i].zName);
      return;
    }
  }
}

#ifndef SQLITE_OMIT_CTE
/* 
** This routine is invoked once per CTE by the parser while parsing a 
** WITH clause. 
*/
With *sqlite3WithAdd(
................................................................................
  Token *pName,           /* Name of the common-table */
  ExprList *pArglist,     /* Optional column name list for the table */
  Select *pQuery          /* Query used to initialize the table */
){
  sqlite3 *db = pParse->db;
  With *pNew;
  char *zName;

  sqlite3RestrictColumnListSyntax(pParse, pArglist);

  /* Check that the CTE name is unique within this WITH clause. If
  ** not, store an error in the Parse structure. */
  zName = sqlite3NameFromToken(pParse->db, pName);
  if( zName && pWith ){
    int i;
    for(i=0; i<pWith->nCte; i++){

Changes to src/expr.c.

1155
1156
1157
1158
1159
1160
1161















1162
1163
1164
1165
1166
1167
1168

no_mem:     
  /* Avoid leaking memory if malloc has failed. */
  sqlite3ExprDelete(db, pExpr);
  sqlite3ExprListDelete(db, pList);
  return 0;
}
















/*
** Set the ExprList.a[].zName element of the most recently added item
** on the expression list.
**
** pList might be NULL following an OOM error.  But pName should never be
** NULL.  If a memory allocation fails, the pParse->db->mallocFailed flag







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







1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183

no_mem:     
  /* Avoid leaking memory if malloc has failed. */
  sqlite3ExprDelete(db, pExpr);
  sqlite3ExprListDelete(db, pList);
  return 0;
}

/*
** Set the sort order for the last element on the given ExprList.
*/
void sqlite3ExprListSetSortOrder(ExprList *p, int iSortOrder){
  if( p==0 ) return;
  assert( SQLITE_SO_UNDEFINED<0 && SQLITE_SO_ASC>=0 && SQLITE_SO_DESC>0 );
  assert( p->nExpr>0 );
  if( iSortOrder<0 ){
    assert( p->a[p->nExpr-1].sortOrder==SQLITE_SO_ASC );
    return;
  }
  p->a[p->nExpr-1].sortOrder = (u8)iSortOrder;
  p->a[p->nExpr-1].bDefinedSO = 1;
}

/*
** Set the ExprList.a[].zName element of the most recently added item
** on the expression list.
**
** pList might be NULL following an OOM error.  But pName should never be
** NULL.  If a memory allocation fails, the pParse->db->mallocFailed flag

Changes to src/parse.y.

676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
....
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
%type sortlist {ExprList*}
%destructor sortlist {sqlite3ExprListDelete(pParse->db, $$);}

orderby_opt(A) ::= .                          {A = 0;}
orderby_opt(A) ::= ORDER BY sortlist(X).      {A = X;}
sortlist(A) ::= sortlist(X) COMMA expr(Y) sortorder(Z). {
  A = sqlite3ExprListAppend(pParse,X,Y.pExpr);
  if( A ) A->a[A->nExpr-1].sortOrder = (u8)Z;
}
sortlist(A) ::= expr(Y) sortorder(Z). {
  A = sqlite3ExprListAppend(pParse,0,Y.pExpr);
  if( A && ALWAYS(A->a) ) A->a[0].sortOrder = (u8)Z;
}

%type sortorder {int}

sortorder(A) ::= ASC.           {A = SQLITE_SO_ASC;}
sortorder(A) ::= DESC.          {A = SQLITE_SO_DESC;}
sortorder(A) ::= .              {A = SQLITE_SO_ASC;}

%type groupby_opt {ExprList*}
%destructor groupby_opt {sqlite3ExprListDelete(pParse->db, $$);}
groupby_opt(A) ::= .                      {A = 0;}
groupby_opt(A) ::= GROUP BY nexprlist(X). {A = X;}

%type having_opt {Expr*}
................................................................................
idxlist_opt(A) ::= .                         {A = 0;}
idxlist_opt(A) ::= LP idxlist(X) RP.         {A = X;}
idxlist(A) ::= idxlist(X) COMMA nm(Y) collate(C) sortorder(Z).  {
  Expr *p = sqlite3ExprAddCollateToken(pParse, 0, &C, 1);
  A = sqlite3ExprListAppend(pParse,X, p);
  sqlite3ExprListSetName(pParse,A,&Y,1);
  sqlite3ExprListCheckLength(pParse, A, "index");
  if( A ) A->a[A->nExpr-1].sortOrder = (u8)Z;
}
idxlist(A) ::= nm(Y) collate(C) sortorder(Z). {
  Expr *p = sqlite3ExprAddCollateToken(pParse, 0, &C, 1);
  A = sqlite3ExprListAppend(pParse,0, p);
  sqlite3ExprListSetName(pParse, A, &Y, 1);
  sqlite3ExprListCheckLength(pParse, A, "index");
  if( A ) A->a[A->nExpr-1].sortOrder = (u8)Z;
}

%type collate {Token}
collate(C) ::= .                 {C.z = 0; C.n = 0;}
collate(C) ::= COLLATE ids(X).   {C = X;}









|



|






|







 







|






|







676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
....
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
%type sortlist {ExprList*}
%destructor sortlist {sqlite3ExprListDelete(pParse->db, $$);}

orderby_opt(A) ::= .                          {A = 0;}
orderby_opt(A) ::= ORDER BY sortlist(X).      {A = X;}
sortlist(A) ::= sortlist(X) COMMA expr(Y) sortorder(Z). {
  A = sqlite3ExprListAppend(pParse,X,Y.pExpr);
  sqlite3ExprListSetSortOrder(A,Z);
}
sortlist(A) ::= expr(Y) sortorder(Z). {
  A = sqlite3ExprListAppend(pParse,0,Y.pExpr);
  sqlite3ExprListSetSortOrder(A,Z);
}

%type sortorder {int}

sortorder(A) ::= ASC.           {A = SQLITE_SO_ASC;}
sortorder(A) ::= DESC.          {A = SQLITE_SO_DESC;}
sortorder(A) ::= .              {A = SQLITE_SO_UNDEFINED;}

%type groupby_opt {ExprList*}
%destructor groupby_opt {sqlite3ExprListDelete(pParse->db, $$);}
groupby_opt(A) ::= .                      {A = 0;}
groupby_opt(A) ::= GROUP BY nexprlist(X). {A = X;}

%type having_opt {Expr*}
................................................................................
idxlist_opt(A) ::= .                         {A = 0;}
idxlist_opt(A) ::= LP idxlist(X) RP.         {A = X;}
idxlist(A) ::= idxlist(X) COMMA nm(Y) collate(C) sortorder(Z).  {
  Expr *p = sqlite3ExprAddCollateToken(pParse, 0, &C, 1);
  A = sqlite3ExprListAppend(pParse,X, p);
  sqlite3ExprListSetName(pParse,A,&Y,1);
  sqlite3ExprListCheckLength(pParse, A, "index");
  sqlite3ExprListSetSortOrder(A,Z);
}
idxlist(A) ::= nm(Y) collate(C) sortorder(Z). {
  Expr *p = sqlite3ExprAddCollateToken(pParse, 0, &C, 1);
  A = sqlite3ExprListAppend(pParse,0, p);
  sqlite3ExprListSetName(pParse, A, &Y, 1);
  sqlite3ExprListCheckLength(pParse, A, "index");
  sqlite3ExprListSetSortOrder(A,Z);
}

%type collate {Token}
collate(C) ::= .                 {C.z = 0; C.n = 0;}
collate(C) ::= COLLATE ids(X).   {C = X;}


Changes to src/sqliteInt.h.

1521
1522
1523
1524
1525
1526
1527

1528
1529
1530
1531
1532
1533
1534
....
2185
2186
2187
2188
2189
2190
2191

2192
2193
2194
2195
2196
2197
2198
....
3240
3241
3242
3243
3244
3245
3246

3247
3248
3249
3250
3251
3252
3253
....
3751
3752
3753
3754
3755
3756
3757

3758
3759
3760
3761
3762
3763
3764
};

/*
** A sort order can be either ASC or DESC.
*/
#define SQLITE_SO_ASC       0  /* Sort in ascending order */
#define SQLITE_SO_DESC      1  /* Sort in ascending order */


/*
** Column affinity types.
**
** These used to have mnemonic name like 'i' for SQLITE_AFF_INTEGER and
** 't' for SQLITE_AFF_TEXT.  But we can save a little space and improve
** the speed a little by numbering the values consecutively.  
................................................................................
    Expr *pExpr;            /* The list of expressions */
    char *zName;            /* Token associated with this expression */
    char *zSpan;            /* Original text of the expression */
    u8 sortOrder;           /* 1 for DESC or 0 for ASC */
    unsigned done :1;       /* A flag to indicate when processing is finished */
    unsigned bSpanIsTab :1; /* zSpan holds DB.TABLE.COLUMN */
    unsigned reusable :1;   /* Constant expression is reusable */

    union {
      struct {
        u16 iOrderByCol;      /* For ORDER BY, column number in result set */
        u16 iAlias;           /* Index into Parse.aAlias[] for zName */
      } x;
      int iConstExprReg;      /* Register in which Expr value is cached */
    } u;
................................................................................
void sqlite3ExprAttachSubtrees(sqlite3*,Expr*,Expr*,Expr*);
Expr *sqlite3PExpr(Parse*, int, Expr*, Expr*, const Token*);
Expr *sqlite3ExprAnd(sqlite3*,Expr*, Expr*);
Expr *sqlite3ExprFunction(Parse*,ExprList*, Token*);
void sqlite3ExprAssignVarNumber(Parse*, Expr*);
void sqlite3ExprDelete(sqlite3*, Expr*);
ExprList *sqlite3ExprListAppend(Parse*,ExprList*,Expr*);

void sqlite3ExprListSetName(Parse*,ExprList*,Token*,int);
void sqlite3ExprListSetSpan(Parse*,ExprList*,ExprSpan*);
void sqlite3ExprListDelete(sqlite3*, ExprList*);
u32 sqlite3ExprListFlags(const ExprList*);
int sqlite3Init(sqlite3*, char**);
int sqlite3InitCallback(void*, int, char**, char**);
void sqlite3Pragma(Parse*,Token*,Token*,Token*,int);
................................................................................
CollSeq *sqlite3BinaryCompareCollSeq(Parse *, Expr *, Expr *);
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*,ExprList*,Select*);
  void sqlite3WithDelete(sqlite3*,With*);
  void sqlite3WithPush(Parse*, With*, u8);
#else
#define sqlite3WithPush(x,y,z)
#define sqlite3WithDelete(x,y)







>







 







>







 







>







 







>







1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
....
2186
2187
2188
2189
2190
2191
2192
2193
2194
2195
2196
2197
2198
2199
2200
....
3242
3243
3244
3245
3246
3247
3248
3249
3250
3251
3252
3253
3254
3255
3256
....
3754
3755
3756
3757
3758
3759
3760
3761
3762
3763
3764
3765
3766
3767
3768
};

/*
** A sort order can be either ASC or DESC.
*/
#define SQLITE_SO_ASC       0  /* Sort in ascending order */
#define SQLITE_SO_DESC      1  /* Sort in ascending order */
#define SQLITE_SO_UNDEFINED -1 /* No sort order specified */

/*
** Column affinity types.
**
** These used to have mnemonic name like 'i' for SQLITE_AFF_INTEGER and
** 't' for SQLITE_AFF_TEXT.  But we can save a little space and improve
** the speed a little by numbering the values consecutively.  
................................................................................
    Expr *pExpr;            /* The list of expressions */
    char *zName;            /* Token associated with this expression */
    char *zSpan;            /* Original text of the expression */
    u8 sortOrder;           /* 1 for DESC or 0 for ASC */
    unsigned done :1;       /* A flag to indicate when processing is finished */
    unsigned bSpanIsTab :1; /* zSpan holds DB.TABLE.COLUMN */
    unsigned reusable :1;   /* Constant expression is reusable */
    unsigned bDefinedSO :1; /* True if either DESC or ASC keywords present */
    union {
      struct {
        u16 iOrderByCol;      /* For ORDER BY, column number in result set */
        u16 iAlias;           /* Index into Parse.aAlias[] for zName */
      } x;
      int iConstExprReg;      /* Register in which Expr value is cached */
    } u;
................................................................................
void sqlite3ExprAttachSubtrees(sqlite3*,Expr*,Expr*,Expr*);
Expr *sqlite3PExpr(Parse*, int, Expr*, Expr*, const Token*);
Expr *sqlite3ExprAnd(sqlite3*,Expr*, Expr*);
Expr *sqlite3ExprFunction(Parse*,ExprList*, Token*);
void sqlite3ExprAssignVarNumber(Parse*, Expr*);
void sqlite3ExprDelete(sqlite3*, Expr*);
ExprList *sqlite3ExprListAppend(Parse*,ExprList*,Expr*);
void sqlite3ExprListSetSortOrder(ExprList*,int);
void sqlite3ExprListSetName(Parse*,ExprList*,Token*,int);
void sqlite3ExprListSetSpan(Parse*,ExprList*,ExprSpan*);
void sqlite3ExprListDelete(sqlite3*, ExprList*);
u32 sqlite3ExprListFlags(const ExprList*);
int sqlite3Init(sqlite3*, char**);
int sqlite3InitCallback(void*, int, char**, char**);
void sqlite3Pragma(Parse*,Token*,Token*,Token*,int);
................................................................................
CollSeq *sqlite3BinaryCompareCollSeq(Parse *, Expr *, Expr *);
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
void sqlite3RestrictColumnListSyntax(Parse*,ExprList*);
#ifndef SQLITE_OMIT_CTE
  With *sqlite3WithAdd(Parse*,With*,Token*,ExprList*,Select*);
  void sqlite3WithDelete(sqlite3*,With*);
  void sqlite3WithPush(Parse*, With*, u8);
#else
#define sqlite3WithPush(x,y,z)
#define sqlite3WithDelete(x,y)

Added test/parser1.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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
# 2014-08-24
#
# 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 script is testing details of the SQL language parser.
# 

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

do_catchsql_test parser1-1.1 {
  CREATE TABLE t1(
    a TEXT PRIMARY KEY,
    b TEXT,
    FOREIGN KEY(b COLLATE nocase DESC) REFERENCES t1(a COLLATE binary ASC)
  );
} {1 {syntax error after column name "a"}}
do_execsql_test parser1-1.2 {
  CREATE TABLE t1(
    a TEXT PRIMARY KEY,
    b TEXT,
    FOREIGN KEY(b) REFERENCES t1(a)
  );
  INSERT INTO t1 VALUES('abc',NULL),('xyz','abc');
  PRAGMA writable_schema=on;
  UPDATE sqlite_master SET sql='CREATE TABLE t1(
    a TEXT PRIMARY KEY,
    b TEXT,
    FOREIGN KEY(b COLLATE nocase) REFERENCES t1(a)
  )' WHERE name='t1';
  SELECT name FROM sqlite_master WHERE sql LIKE '%collate%';
} {t1}
sqlite3 db2 test.db
do_test parser1-1.3 {
  sqlite3 db2 test.db
  db2 eval {SELECT * FROM t1 ORDER BY 1}
} {abc {} xyz abc}


do_catchsql_test parser1-2.1 {
  WITH RECURSIVE
    c(x COLLATE binary) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<5)
  SELECT x FROM c;
} {1 {syntax error after column name "x"}}
do_catchsql_test parser1-2.2 {
  WITH RECURSIVE
    c(x ASC) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<5)
  SELECT x FROM c;
} {1 {syntax error after column name "x"}}

finish_test