/ Check-in [db27afc4]
Login

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

Overview
Comment:Disallow subqueries in CHECK constraints. (CVS 2756)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: db27afc4cdc5b51c1fa0e83dbd6d4a4a69c5b642
User & Date: drh 2005-11-03 02:03:13
Context
2005-11-03
02:15
Add the ignore_check_constraints pragma. VACUUM works even on a database that contains table entries that violate check constraints. (CVS 2757) check-in: be83bfee user: drh tags: trunk
02:03
Disallow subqueries in CHECK constraints. (CVS 2756) check-in: db27afc4 user: drh tags: trunk
01:22
CHECK constraints that evaluate to NULL pass. (CVS 2755) check-in: 55b314a2 user: drh 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
....
1303
1304
1305
1306
1307
1308
1309

1310
1311
1312
1313
1314
1315
1316
**     CREATE INDEX
**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**
** $Id: build.c,v 1.353 2005/11/03 00:41:17 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Initialize the pParse structure as needed.
................................................................................
    memset(&sSrc, 0, sizeof(sSrc));
    sSrc.nSrc = 1;
    sSrc.a[0].zName = p->zName;
    sSrc.a[0].pTab = p;
    sSrc.a[0].iCursor = -1;
    sNC.pParse = pParse;
    sNC.pSrcList = &sSrc;

    if( sqlite3ExprResolveNames(&sNC, p->pCheck) ){
      return;
    }
  }
#endif /* !defined(SQLITE_OMIT_CHECK) */

  /* If the db->init.busy is 1 it means we are reading the SQL off the







|







 







>







18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
....
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
**     CREATE INDEX
**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**
** $Id: build.c,v 1.354 2005/11/03 02:03:13 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Initialize the pParse structure as needed.
................................................................................
    memset(&sSrc, 0, sizeof(sSrc));
    sSrc.nSrc = 1;
    sSrc.a[0].zName = p->zName;
    sSrc.a[0].pTab = p;
    sSrc.a[0].iCursor = -1;
    sNC.pParse = pParse;
    sNC.pSrcList = &sSrc;
    sNC.isCheck = 1;
    if( sqlite3ExprResolveNames(&sNC, p->pCheck) ){
      return;
    }
  }
#endif /* !defined(SQLITE_OMIT_CHECK) */

  /* If the db->init.busy is 1 it means we are reading the SQL off the

Changes to src/expr.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
1184
1185
1186
1187
1188
1189
1190





1191
1192
1193
1194
1195
1196
1197
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.233 2005/11/03 00:41:17 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**
................................................................................
    }
#ifndef SQLITE_OMIT_SUBQUERY
    case TK_SELECT:
    case TK_EXISTS:
#endif
    case TK_IN: {
      if( pExpr->pSelect ){





        int nRef = pNC->nRef;
        sqlite3SelectResolve(pParse, pExpr->pSelect, pNC);
        assert( pNC->nRef>=nRef );
        if( nRef!=pNC->nRef ){
          ExprSetProperty(pExpr, EP_VarSelect);
        }
      }







|







 







>
>
>
>
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.234 2005/11/03 02:03:13 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**
................................................................................
    }
#ifndef SQLITE_OMIT_SUBQUERY
    case TK_SELECT:
    case TK_EXISTS:
#endif
    case TK_IN: {
      if( pExpr->pSelect ){
#ifndef SQLITE_OMIT_CHECK
        if( pNC->isCheck ){
          sqlite3ErrorMsg(pParse,"subqueries prohibited in CHECK constraints");
        }
#endif
        int nRef = pNC->nRef;
        sqlite3SelectResolve(pParse, pExpr->pSelect, pNC);
        assert( pNC->nRef>=nRef );
        if( nRef!=pNC->nRef ){
          ExprSetProperty(pExpr, EP_VarSelect);
        }
      }

Changes to src/parse.y.

10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
...
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.181 2005/11/03 00:41:17 drh Exp $
*/

// All token codes are small integers with #defines that begin with "TK_"
%token_prefix TK_

// The type of the data attached to each token is Token.  This is also the
// default type for non-terminals.
................................................................................
// UNIQUE constraints.
//
ccons ::= NULL onconf.
ccons ::= NOT NULL onconf(R).               {sqlite3AddNotNull(pParse, R);}
ccons ::= PRIMARY KEY sortorder onconf(R) autoinc(I).
                                     {sqlite3AddPrimaryKey(pParse,0,R,I);}
ccons ::= UNIQUE onconf(R).          {sqlite3CreateIndex(pParse,0,0,0,0,R,0,0);}
ccons ::= CHECK LP expr(X) RP.       {sqlite3AddCheckConstraint(pParse, X);}
ccons ::= REFERENCES nm(T) idxlist_opt(TA) refargs(R).
                                {sqlite3CreateForeignKey(pParse,0,&T,TA,R);}
ccons ::= defer_subclause(D).   {sqlite3DeferForeignKey(pParse,D);}
ccons ::= COLLATE id(C).  {sqlite3AddCollateType(pParse, C.z, C.n);}

// The optional AUTOINCREMENT keyword
%type autoinc {int}
................................................................................
conslist ::= conslist tcons.
conslist ::= tcons.
tcons ::= CONSTRAINT nm.
tcons ::= PRIMARY KEY LP idxlist(X) autoinc(I) RP onconf(R).
                                         {sqlite3AddPrimaryKey(pParse,X,R,I);}
tcons ::= UNIQUE LP idxlist(X) RP onconf(R).
                                       {sqlite3CreateIndex(pParse,0,0,0,X,R,0,0);}
tcons ::= CHECK expr onconf.
tcons ::= FOREIGN KEY LP idxlist(FA) RP
          REFERENCES nm(T) idxlist_opt(TA) refargs(R) defer_subclause_opt(D). {
    sqlite3CreateForeignKey(pParse, FA, &T, TA, R);
    sqlite3DeferForeignKey(pParse, D);
}
%type defer_subclause_opt {int}
defer_subclause_opt(A) ::= .                    {A = 0;}







|







 







|







 







|







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
...
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.182 2005/11/03 02:03:13 drh Exp $
*/

// All token codes are small integers with #defines that begin with "TK_"
%token_prefix TK_

// The type of the data attached to each token is Token.  This is also the
// default type for non-terminals.
................................................................................
// UNIQUE constraints.
//
ccons ::= NULL onconf.
ccons ::= NOT NULL onconf(R).               {sqlite3AddNotNull(pParse, R);}
ccons ::= PRIMARY KEY sortorder onconf(R) autoinc(I).
                                     {sqlite3AddPrimaryKey(pParse,0,R,I);}
ccons ::= UNIQUE onconf(R).          {sqlite3CreateIndex(pParse,0,0,0,0,R,0,0);}
ccons ::= CHECK LP expr(X) RP.       {sqlite3AddCheckConstraint(pParse,X);}
ccons ::= REFERENCES nm(T) idxlist_opt(TA) refargs(R).
                                {sqlite3CreateForeignKey(pParse,0,&T,TA,R);}
ccons ::= defer_subclause(D).   {sqlite3DeferForeignKey(pParse,D);}
ccons ::= COLLATE id(C).  {sqlite3AddCollateType(pParse, C.z, C.n);}

// The optional AUTOINCREMENT keyword
%type autoinc {int}
................................................................................
conslist ::= conslist tcons.
conslist ::= tcons.
tcons ::= CONSTRAINT nm.
tcons ::= PRIMARY KEY LP idxlist(X) autoinc(I) RP onconf(R).
                                         {sqlite3AddPrimaryKey(pParse,X,R,I);}
tcons ::= UNIQUE LP idxlist(X) RP onconf(R).
                                       {sqlite3CreateIndex(pParse,0,0,0,X,R,0,0);}
tcons ::= CHECK LP expr(E) RP onconf. {sqlite3AddCheckConstraint(pParse,E);}
tcons ::= FOREIGN KEY LP idxlist(FA) RP
          REFERENCES nm(T) idxlist_opt(TA) refargs(R) defer_subclause_opt(D). {
    sqlite3CreateForeignKey(pParse, FA, &T, TA, R);
    sqlite3DeferForeignKey(pParse, D);
}
%type defer_subclause_opt {int}
defer_subclause_opt(A) ::= .                    {A = 0;}

Changes to src/sqliteInt.h.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
....
1084
1085
1086
1087
1088
1089
1090

1091
1092
1093
1094
1095
1096
1097
**    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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.425 2005/11/03 00:41:17 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** Many people are failing to set -DNDEBUG=1 when compiling SQLite.
** Setting NDEBUG makes the code smaller and run faster.  So the following
................................................................................
  Parse *pParse;       /* The parser */
  SrcList *pSrcList;   /* One or more tables used to resolve names */
  ExprList *pEList;    /* Optional list of named expressions */
  int nRef;            /* Number of names resolved by this context */
  int nErr;            /* Number of errors encountered while resolving names */
  u8 allowAgg;         /* Aggregate functions allowed here */
  u8 hasAgg;           /* True if aggregates are seen */

  int nDepth;          /* Depth of subquery recursion. 1 for no recursion */
  AggInfo *pAggInfo;   /* Information about aggregates at this level */
  NameContext *pNext;  /* Next outer name context.  NULL for outermost */
};

/*
** An instance of the following structure contains all information







|







 







>







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
....
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
**    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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.426 2005/11/03 02:03:13 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** Many people are failing to set -DNDEBUG=1 when compiling SQLite.
** Setting NDEBUG makes the code smaller and run faster.  So the following
................................................................................
  Parse *pParse;       /* The parser */
  SrcList *pSrcList;   /* One or more tables used to resolve names */
  ExprList *pEList;    /* Optional list of named expressions */
  int nRef;            /* Number of names resolved by this context */
  int nErr;            /* Number of errors encountered while resolving names */
  u8 allowAgg;         /* Aggregate functions allowed here */
  u8 hasAgg;           /* True if aggregates are seen */
  u8 isCheck;          /* True if resolving names in a CHECK constraint */
  int nDepth;          /* Depth of subquery recursion. 1 for no recursion */
  AggInfo *pAggInfo;   /* Information about aggregates at this level */
  NameContext *pNext;  /* Next outer name context.  NULL for outermost */
};

/*
** An instance of the following structure contains all information

Changes to test/check.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
146
147
148
149
150
151
152
153










































































































154
#    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 CHECK constraints
#
# $Id: check.test,v 1.2 2005/11/03 01:22:31 drh Exp $

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

# Only run these tests if the build includes support for CHECK constraints
ifcapable !check {
  finish_test
................................................................................
  }
} {1 {constraint failed}}
do_test check-2.6 {
  catchsql {
    INSERT INTO t2 VALUES(NULL, NULL, 3.14159);
  }
} {1 {constraint failed}}











































































































finish_test







|







 








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

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
#    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 CHECK constraints
#
# $Id: check.test,v 1.3 2005/11/03 02:03:13 drh Exp $

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

# Only run these tests if the build includes support for CHECK constraints
ifcapable !check {
  finish_test
................................................................................
  }
} {1 {constraint failed}}
do_test check-2.6 {
  catchsql {
    INSERT INTO t2 VALUES(NULL, NULL, 3.14159);
  }
} {1 {constraint failed}}

do_test check-3.1 {
  catchsql {
    CREATE TABLE t3(
      x, y, z,
      CHECK( x<(SELECT min(x) FROM t1) )
    );
  }
} {1 {subqueries prohibited in CHECK constraints}}
do_test check-3.2 {
  execsql {
    SELECT name FROM sqlite_master ORDER BY name
  }
} {t1 t2}
do_test check-3.3 {
  catchsql {
    CREATE TABLE t3(
      x, y, z,
      CHECK( q<x )
    );
  }
} {1 {no such column: q}}
do_test check-3.4 {
  execsql {
    SELECT name FROM sqlite_master ORDER BY name
  }
} {t1 t2}
do_test check-3.5 {
  catchsql {
    CREATE TABLE t3(
      x, y, z,
      CHECK( t2.x<x )
    );
  }
} {1 {no such column: t2.x}}
do_test check-3.6 {
  execsql {
    SELECT name FROM sqlite_master ORDER BY name
  }
} {t1 t2}
do_test check-3.7 {
  catchsql {
    CREATE TABLE t3(
      x, y, z,
      CHECK( t3.x<25 )
    );
  }
} {0 {}}
do_test check-3.8 {
  execsql {
    INSERT INTO t3 VALUES(1,2,3);
    SELECT * FROM t3;
  }
} {1 2 3}
do_test check-3.9 {
  catchsql {
    INSERT INTO t3 VALUES(111,222,333);
  }
} {1 {constraint failed}}

do_test check-4.1 {
  execsql {
    CREATE TABLE t4(x, y,
      CHECK (
           x+y==11
        OR x*y==12
        OR x/y BETWEEN 5 AND 8
        OR -x==y+10
      )
    );
  }
} {}
do_test check-4.2 {
  execsql {
    INSERT INTO t4 VALUES(1,10);
    SELECT * FROM t4
  }
} {1 10}
do_test check-4.3 {
  execsql {
    UPDATE t4 SET x=4, y=3;
    SELECT * FROM t4
  }
} {4 3}
do_test check-4.3 {
  execsql {
    UPDATE t4 SET x=12, y=2;
    SELECT * FROM t4
  }
} {12 2}
do_test check-4.4 {
  execsql {
    UPDATE t4 SET x=12, y=-22;
    SELECT * FROM t4
  }
} {12 -22}
do_test check-4.5 {
  catchsql {
    UPDATE t4 SET x=0, y=1;
  }
} {1 {constraint failed}}
do_test check-4.6 {
  execsql {
    SELECT * FROM t4;
  }
} {12 -22}

finish_test