/ Check-in [1ad2bc1e]
Login

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

Overview
Comment:Do not allow parameters in a DEFAULT clause of a CREATE TABLE statement. Ticket [78c0c8c3c9f7c1].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 1ad2bc1ed4c4ac81ac67a9660761f0eeb47c7fef
User & Date: drh 2014-09-24 13:20:22
Context
2014-09-24
18:31
Small performance and size optimization for btreeUnlockIfUnused(). check-in: 13c746f8 user: drh tags: trunk
13:20
Do not allow parameters in a DEFAULT clause of a CREATE TABLE statement. Ticket [78c0c8c3c9f7c1]. check-in: 1ad2bc1e user: drh tags: trunk
02:05
Have the clearCell() routine return the cell size to the caller, rather than have the caller make a separate call to cellSizePtr(). check-in: f21d2175 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/build.c.

  1232   1232   void sqlite3AddDefaultValue(Parse *pParse, ExprSpan *pSpan){
  1233   1233     Table *p;
  1234   1234     Column *pCol;
  1235   1235     sqlite3 *db = pParse->db;
  1236   1236     p = pParse->pNewTable;
  1237   1237     if( p!=0 ){
  1238   1238       pCol = &(p->aCol[p->nCol-1]);
  1239         -    if( !sqlite3ExprIsConstantOrFunction(pSpan->pExpr) ){
         1239  +    if( !sqlite3ExprIsConstantOrFunction(pSpan->pExpr, db->init.busy) ){
  1240   1240         sqlite3ErrorMsg(pParse, "default value of column [%s] is not constant",
  1241   1241             pCol->zName);
  1242   1242       }else{
  1243   1243         /* A copy of pExpr is used instead of the original, as pExpr contains
  1244   1244         ** tokens that point to volatile memory. The 'span' of the expression
  1245   1245         ** is required by pragma table_info.
  1246   1246         */

Changes to src/expr.c.

  1208   1208     sqlite3DbFree(db, pList->a);
  1209   1209     sqlite3DbFree(db, pList);
  1210   1210   }
  1211   1211   
  1212   1212   /*
  1213   1213   ** These routines are Walker callbacks.  Walker.u.pi is a pointer
  1214   1214   ** to an integer.  These routines are checking an expression to see
  1215         -** if it is a constant.  Set *Walker.u.pi to 0 if the expression is
         1215  +** if it is a constant.  Set *Walker.u.i to 0 if the expression is
  1216   1216   ** not constant.
  1217   1217   **
  1218   1218   ** These callback routines are used to implement the following:
  1219   1219   **
  1220         -**     sqlite3ExprIsConstant()
  1221         -**     sqlite3ExprIsConstantNotJoin()
  1222         -**     sqlite3ExprIsConstantOrFunction()
         1220  +**     sqlite3ExprIsConstant()                  pWalker->u.i==1
         1221  +**     sqlite3ExprIsConstantNotJoin()           pWalker->u.i==2
         1222  +**     sqlite3ExprIsConstantOrFunction()        pWalker->u.i==3 or 4
  1223   1223   **
         1224  +** The sqlite3ExprIsConstantOrFunction() is used for evaluating expressions
         1225  +** in a CREATE TABLE statement.  The Walker.u.i value is 4 when parsing
         1226  +** an existing schema and 3 when processing a new statement.  A bound
         1227  +** parameter raises an error for new statements, but is silently converted
         1228  +** to NULL for existing schemas.  This allows sqlite_master tables that 
         1229  +** contain a bound parameter because they were generated by older versions
         1230  +** of SQLite to be parsed by newer versions of SQLite without raising a
         1231  +** malformed schema error.
  1224   1232   */
  1225   1233   static int exprNodeIsConstant(Walker *pWalker, Expr *pExpr){
  1226   1234   
  1227         -  /* If pWalker->u.i is 3 then any term of the expression that comes from
         1235  +  /* If pWalker->u.i is 2 then any term of the expression that comes from
  1228   1236     ** the ON or USING clauses of a join disqualifies the expression
  1229   1237     ** from being considered constant. */
  1230         -  if( pWalker->u.i==3 && ExprHasProperty(pExpr, EP_FromJoin) ){
         1238  +  if( pWalker->u.i==2 && ExprHasProperty(pExpr, EP_FromJoin) ){
  1231   1239       pWalker->u.i = 0;
  1232   1240       return WRC_Abort;
  1233   1241     }
  1234   1242   
  1235   1243     switch( pExpr->op ){
  1236   1244       /* Consider functions to be constant if all their arguments are constant
  1237         -    ** and either pWalker->u.i==2 or the function as the SQLITE_FUNC_CONST
         1245  +    ** and either pWalker->u.i==3 or 4 or the function as the SQLITE_FUNC_CONST
  1238   1246       ** flag. */
  1239   1247       case TK_FUNCTION:
  1240         -      if( pWalker->u.i==2 || ExprHasProperty(pExpr,EP_Constant) ){
         1248  +      if( pWalker->u.i>=3 || ExprHasProperty(pExpr,EP_Constant) ){
  1241   1249           return WRC_Continue;
  1242   1250         }
  1243   1251         /* Fall through */
  1244   1252       case TK_ID:
  1245   1253       case TK_COLUMN:
  1246   1254       case TK_AGG_FUNCTION:
  1247   1255       case TK_AGG_COLUMN:
  1248   1256         testcase( pExpr->op==TK_ID );
  1249   1257         testcase( pExpr->op==TK_COLUMN );
  1250   1258         testcase( pExpr->op==TK_AGG_FUNCTION );
  1251   1259         testcase( pExpr->op==TK_AGG_COLUMN );
  1252   1260         pWalker->u.i = 0;
  1253   1261         return WRC_Abort;
         1262  +    case TK_VARIABLE:
         1263  +      if( pWalker->u.i==4 ){
         1264  +        /* Silently convert bound parameters that appear inside of CREATE
         1265  +        ** statements into a NULL when parsing the CREATE statement text out
         1266  +        ** of the sqlite_master table */
         1267  +        pExpr->op = TK_NULL;
         1268  +      }else if( pWalker->u.i==3 ){
         1269  +        /* A bound parameter in a CREATE statement that originates from
         1270  +        ** sqlite3_prepare() causes an error */
         1271  +        pWalker->u.i = 0;
         1272  +        return WRC_Abort;
         1273  +      }
         1274  +      /* Fall through */
  1254   1275       default:
  1255   1276         testcase( pExpr->op==TK_SELECT ); /* selectNodeIsConstant will disallow */
  1256   1277         testcase( pExpr->op==TK_EXISTS ); /* selectNodeIsConstant will disallow */
  1257   1278         return WRC_Continue;
  1258   1279     }
  1259   1280   }
  1260   1281   static int selectNodeIsConstant(Walker *pWalker, Select *NotUsed){
................................................................................
  1287   1308   /*
  1288   1309   ** Walk an expression tree.  Return 1 if the expression is constant
  1289   1310   ** that does no originate from the ON or USING clauses of a join.
  1290   1311   ** Return 0 if it involves variables or function calls or terms from
  1291   1312   ** an ON or USING clause.
  1292   1313   */
  1293   1314   int sqlite3ExprIsConstantNotJoin(Expr *p){
  1294         -  return exprIsConst(p, 3);
         1315  +  return exprIsConst(p, 2);
  1295   1316   }
  1296   1317   
  1297   1318   /*
  1298   1319   ** Walk an expression tree.  Return 1 if the expression is constant
  1299   1320   ** or a function call with constant arguments.  Return and 0 if there
  1300   1321   ** are any variables.
  1301   1322   **
  1302   1323   ** For the purposes of this function, a double-quoted string (ex: "abc")
  1303   1324   ** is considered a variable but a single-quoted string (ex: 'abc') is
  1304   1325   ** a constant.
  1305   1326   */
  1306         -int sqlite3ExprIsConstantOrFunction(Expr *p){
  1307         -  return exprIsConst(p, 2);
         1327  +int sqlite3ExprIsConstantOrFunction(Expr *p, u8 isInit){
         1328  +  assert( isInit==0 || isInit==1 );
         1329  +  return exprIsConst(p, 3+isInit);
  1308   1330   }
  1309   1331   
  1310   1332   /*
  1311   1333   ** If the expression p codes a constant integer that is small enough
  1312   1334   ** to fit in a 32-bit integer, return 1 and put the value of the integer
  1313   1335   ** in *pValue.  If the expression is not an integer or if it is too big
  1314   1336   ** to fit in a signed 32-bit integer, return 0 and leave *pValue unchanged.

Changes to src/sqliteInt.h.

  3281   3281   void sqlite3CommitTransaction(Parse*);
  3282   3282   void sqlite3RollbackTransaction(Parse*);
  3283   3283   void sqlite3Savepoint(Parse*, int, Token*);
  3284   3284   void sqlite3CloseSavepoints(sqlite3 *);
  3285   3285   void sqlite3LeaveMutexAndCloseZombie(sqlite3*);
  3286   3286   int sqlite3ExprIsConstant(Expr*);
  3287   3287   int sqlite3ExprIsConstantNotJoin(Expr*);
  3288         -int sqlite3ExprIsConstantOrFunction(Expr*);
         3288  +int sqlite3ExprIsConstantOrFunction(Expr*, u8);
  3289   3289   int sqlite3ExprIsInteger(Expr*, int*);
  3290   3290   int sqlite3ExprCanBeNull(const Expr*);
  3291   3291   int sqlite3ExprNeedsNoAffinityChange(const Expr*, char);
  3292   3292   int sqlite3IsRowid(const char*);
  3293   3293   void sqlite3GenerateRowDelete(Parse*,Table*,Trigger*,int,int,int,i16,u8,u8,u8);
  3294   3294   void sqlite3GenerateRowIndexDelete(Parse*, Table*, int, int, int*);
  3295   3295   int sqlite3GenerateIndexKey(Parse*, Index*, int, int, int, int*,Index*,int);

Changes to test/default.test.

    94     94       f INT DEFAULT -9223372036854775808,
    95     95       g INT DEFAULT (-(-9223372036854775808)),
    96     96       h INT DEFAULT (-(-9223372036854775807))
    97     97     );
    98     98     INSERT INTO t300 DEFAULT VALUES;
    99     99     SELECT * FROM t300;
   100    100   } {2147483647 2147483648 9223372036854775807 -2147483647 -2147483648 -9223372036854775808 9.22337203685478e+18 9223372036854775807}
          101  +
          102  +# Do now allow bound parameters in new DEFAULT values. 
          103  +# Silently convert bound parameters to NULL in DEFAULT causes
          104  +# in the sqlite_master table, for backwards compatibility.
          105  +#
          106  +db close
          107  +forcedelete test.db
          108  +sqlite3 db test.db
          109  +do_execsql_test default-4.0 {
          110  +  CREATE TABLE t1(a TEXT, b TEXT DEFAULT(99));
          111  +  PRAGMA writable_schema=ON;
          112  +  UPDATE sqlite_master SET sql='CREATE TABLE t1(a TEXT, b TEXT DEFAULT(:xyz))';
          113  +} {}
          114  +db close 
          115  +sqlite3 db test.db
          116  +do_execsql_test default-4.1 {
          117  +  INSERT INTO t1(a) VALUES('xyzzy');
          118  +  SELECT a, quote(b) FROM t1;
          119  +} {xyzzy NULL}
          120  +do_catchsql_test default-4.2 {
          121  +  CREATE TABLE t2(a TEXT, b TEXT DEFAULT(:xyz));
          122  +} {1 {default value of column [b] is not constant}}
          123  +do_catchsql_test default-4.3 {
          124  +  CREATE TABLE t2(a TEXT, b TEXT DEFAULT(abs(:xyz)));
          125  +} {1 {default value of column [b] is not constant}}
          126  +do_catchsql_test default-4.4 {
          127  +  CREATE TABLE t2(a TEXT, b TEXT DEFAULT(98+coalesce(5,:xyz)));
          128  +} {1 {default value of column [b] is not constant}}
   101    129   
   102    130   finish_test