/ Check-in [8767f7b8]
Login

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

Overview
Comment:Ensure the "unique-not-null" flag is set for automatic indexes on columns declared with "col UNIQUE NOT NULL" (where the NOT NULL comes after the UNIQUE).
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 8767f7b880f2e4112f75f0b6ef7be3f50ab1ae20e103e7d03d8bfe77e6c79438
User & Date: dan 2018-01-29 16:22:39
Context
2018-01-29
18:41
Add aggregate function zipfile() to the zipfile extension. For composing new zip archives in memory. check-in: e364eeac user: dan tags: trunk
17:08
Update the omit-left-join optimization so that it works in some cases when the RHS is subject to a UNIQUE but not NOT NULL constraint. check-in: 88411a40 user: dan tags: omit-left-join-fix
16:22
Ensure the "unique-not-null" flag is set for automatic indexes on columns declared with "col UNIQUE NOT NULL" (where the NOT NULL comes after the UNIQUE). check-in: 8767f7b8 user: dan tags: trunk
2018-01-27
18:55
Fix missing header comments and other code issues in zipfile.c. check-in: 6ea8ba31 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/build.c.

  1114   1114   ** This routine is called by the parser while in the middle of
  1115   1115   ** parsing a CREATE TABLE statement.  A "NOT NULL" constraint has
  1116   1116   ** been seen on a column.  This routine sets the notNull flag on
  1117   1117   ** the column currently under construction.
  1118   1118   */
  1119   1119   void sqlite3AddNotNull(Parse *pParse, int onError){
  1120   1120     Table *p;
         1121  +  Column *pCol;
  1121   1122     p = pParse->pNewTable;
  1122   1123     if( p==0 || NEVER(p->nCol<1) ) return;
  1123         -  p->aCol[p->nCol-1].notNull = (u8)onError;
         1124  +  pCol = &p->aCol[p->nCol-1];
         1125  +  pCol->notNull = (u8)onError;
  1124   1126     p->tabFlags |= TF_HasNotNull;
         1127  +
         1128  +  /* Set the uniqNotNull flag on any UNIQUE or PK indexes already created
         1129  +  ** on this column.  */
         1130  +  if( pCol->colFlags & COLFLAG_UNIQUE ){
         1131  +    Index *pIdx;
         1132  +    for(pIdx=p->pIndex; pIdx; pIdx=pIdx->pNext){
         1133  +      assert( pIdx->nKeyCol==1 && pIdx->onError!=OE_None );
         1134  +      if( pIdx->aiColumn[0]==p->nCol-1 ){
         1135  +        pIdx->uniqNotNull = 1;
         1136  +      }
         1137  +    }
         1138  +  }
  1125   1139   }
  1126   1140   
  1127   1141   /*
  1128   1142   ** Scan the column type name zType (length nType) and return the
  1129   1143   ** associated affinity type.
  1130   1144   **
  1131   1145   ** This routine does a case-independent search of zType for the 
................................................................................
  3081   3095   
  3082   3096     /* If pList==0, it means this routine was called to make a primary
  3083   3097     ** key out of the last column added to the table under construction.
  3084   3098     ** So create a fake list to simulate this.
  3085   3099     */
  3086   3100     if( pList==0 ){
  3087   3101       Token prevCol;
  3088         -    sqlite3TokenInit(&prevCol, pTab->aCol[pTab->nCol-1].zName);
         3102  +    Column *pCol = &pTab->aCol[pTab->nCol-1];
         3103  +    pCol->colFlags |= COLFLAG_UNIQUE;
         3104  +    sqlite3TokenInit(&prevCol, pCol->zName);
  3089   3105       pList = sqlite3ExprListAppend(pParse, 0,
  3090   3106                 sqlite3ExprAlloc(db, TK_ID, &prevCol, 0));
  3091   3107       if( pList==0 ) goto exit_create_index;
  3092   3108       assert( pList->nExpr==1 );
  3093   3109       sqlite3ExprListSetSortOrder(pList, sortOrder);
  3094   3110     }else{
  3095   3111       sqlite3ExprListCheckLength(pParse, pList, "index");

Changes to src/sqliteInt.h.

  1750   1750   };
  1751   1751   
  1752   1752   /* Allowed values for Column.colFlags:
  1753   1753   */
  1754   1754   #define COLFLAG_PRIMKEY  0x0001    /* Column is part of the primary key */
  1755   1755   #define COLFLAG_HIDDEN   0x0002    /* A hidden column in a virtual table */
  1756   1756   #define COLFLAG_HASTYPE  0x0004    /* Type name follows column name */
         1757  +#define COLFLAG_UNIQUE   0x0008    /* Column def contains "UNIQUE" or "PK" */
  1757   1758   
  1758   1759   /*
  1759   1760   ** A "Collating Sequence" is defined by an instance of the following
  1760   1761   ** structure. Conceptually, a collating sequence consists of a name and
  1761   1762   ** a comparison routine that defines the order of that sequence.
  1762   1763   **
  1763   1764   ** If CollSeq.xCmp is NULL, it means that the

Changes to test/notnull.test.

   557    557     }
   558    558   } {1 {NOT NULL constraint failed: t1.b}}
   559    559   verify_ex_errcode notnull-5.4b SQLITE_CONSTRAINT_NOTNULL
   560    560   do_test notnull-5.5 {
   561    561     execsql { SELECT * FROM t1 }
   562    562   } {1 2}
   563    563   
          564  +#-------------------------------------------------------------------------
          565  +# Check that UNIQUE NOT NULL indexes are always recognized as such.
          566  +#
          567  +proc uses_op_next {sql} {
          568  +  db eval "EXPLAIN $sql" a {
          569  +    if {$a(opcode)=="Next"} { return 1 }
          570  +  }
          571  +  return 0
          572  +}
          573  +
          574  +proc do_uses_op_next_test {tn sql res} {
          575  +  uplevel [list do_test $tn [list uses_op_next $sql] $res]
          576  +}
          577  +
          578  +reset_db
          579  +do_execsql_test notnull-6.0 {
          580  +  CREATE TABLE t1(a UNIQUE);
          581  +  CREATE TABLE t2(a NOT NULL UNIQUE);
          582  +  CREATE TABLE t3(a UNIQUE NOT NULL);
          583  +  CREATE TABLE t4(a NOT NULL);
          584  +  CREATE UNIQUE INDEX t4a ON t4(a);
          585  +
          586  +  CREATE TABLE t5(a PRIMARY KEY);
          587  +  CREATE TABLE t6(a PRIMARY KEY NOT NULL);
          588  +  CREATE TABLE t7(a NOT NULL PRIMARY KEY);
          589  +  CREATE TABLE t8(a PRIMARY KEY) WITHOUT ROWID;
          590  +
          591  +  CREATE TABLE t9(a PRIMARY KEY UNIQUE NOT NULL);
          592  +  CREATE TABLE t10(a UNIQUE PRIMARY KEY NOT NULL);
          593  +}
          594  +
          595  +do_uses_op_next_test notnull-6.1 "SELECT * FROM t1 WHERE a IS ?" 1
          596  +do_uses_op_next_test notnull-6.2 "SELECT * FROM t2 WHERE a IS ?" 0
          597  +do_uses_op_next_test notnull-6.3 "SELECT * FROM t3 WHERE a IS ?" 0
          598  +do_uses_op_next_test notnull-6.4 "SELECT * FROM t4 WHERE a IS ?" 0
          599  +
          600  +do_uses_op_next_test notnull-6.5 "SELECT * FROM t5 WHERE a IS ?" 1
          601  +do_uses_op_next_test notnull-6.6 "SELECT * FROM t6 WHERE a IS ?" 0
          602  +do_uses_op_next_test notnull-6.7 "SELECT * FROM t7 WHERE a IS ?" 0
          603  +do_uses_op_next_test notnull-6.8 "SELECT * FROM t8 WHERE a IS ?" 0
          604  +
          605  +do_uses_op_next_test notnull-6.9 "SELECT * FROM t8 WHERE a IS ?" 0
          606  +do_uses_op_next_test notnull-6.10 "SELECT * FROM t8 WHERE a IS ?" 0
          607  +
   564    608   finish_test
          609  +