SQLite

Check-in [8767f7b880]
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
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 8767f7b880f2e4112f75f0b6ef7be3f50ab1ae20e103e7d03d8bfe77e6c79438
User & Date: dan 2018-01-29 16:22:39.280
Context
2018-01-29
18:41
Add aggregate function zipfile() to the zipfile extension. For composing new zip archives in memory. (check-in: e364eeac76 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: 88411a402b 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: 8767f7b880 user: dan tags: trunk)
2018-01-27
18:55
Fix missing header comments and other code issues in zipfile.c. (check-in: 6ea8ba312c user: dan tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/build.c.
1114
1115
1116
1117
1118
1119
1120

1121
1122

1123
1124












1125
1126
1127
1128
1129
1130
1131
** This routine is called by the parser while in the middle of
** parsing a CREATE TABLE statement.  A "NOT NULL" constraint has
** been seen on a column.  This routine sets the notNull flag on
** the column currently under construction.
*/
void sqlite3AddNotNull(Parse *pParse, int onError){
  Table *p;

  p = pParse->pNewTable;
  if( p==0 || NEVER(p->nCol<1) ) return;

  p->aCol[p->nCol-1].notNull = (u8)onError;
  p->tabFlags |= TF_HasNotNull;












}

/*
** Scan the column type name zType (length nType) and return the
** associated affinity type.
**
** This routine does a case-independent search of zType for the 







>


>
|

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







1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
** This routine is called by the parser while in the middle of
** parsing a CREATE TABLE statement.  A "NOT NULL" constraint has
** been seen on a column.  This routine sets the notNull flag on
** the column currently under construction.
*/
void sqlite3AddNotNull(Parse *pParse, int onError){
  Table *p;
  Column *pCol;
  p = pParse->pNewTable;
  if( p==0 || NEVER(p->nCol<1) ) return;
  pCol = &p->aCol[p->nCol-1];
  pCol->notNull = (u8)onError;
  p->tabFlags |= TF_HasNotNull;

  /* Set the uniqNotNull flag on any UNIQUE or PK indexes already created
  ** on this column.  */
  if( pCol->colFlags & COLFLAG_UNIQUE ){
    Index *pIdx;
    for(pIdx=p->pIndex; pIdx; pIdx=pIdx->pNext){
      assert( pIdx->nKeyCol==1 && pIdx->onError!=OE_None );
      if( pIdx->aiColumn[0]==p->nCol-1 ){
        pIdx->uniqNotNull = 1;
      }
    }
  }
}

/*
** Scan the column type name zType (length nType) and return the
** associated affinity type.
**
** This routine does a case-independent search of zType for the 
3081
3082
3083
3084
3085
3086
3087


3088
3089
3090
3091
3092
3093
3094
3095

  /* If pList==0, it means this routine was called to make a primary
  ** key out of the last column added to the table under construction.
  ** So create a fake list to simulate this.
  */
  if( pList==0 ){
    Token prevCol;


    sqlite3TokenInit(&prevCol, pTab->aCol[pTab->nCol-1].zName);
    pList = sqlite3ExprListAppend(pParse, 0,
              sqlite3ExprAlloc(db, TK_ID, &prevCol, 0));
    if( pList==0 ) goto exit_create_index;
    assert( pList->nExpr==1 );
    sqlite3ExprListSetSortOrder(pList, sortOrder);
  }else{
    sqlite3ExprListCheckLength(pParse, pList, "index");







>
>
|







3095
3096
3097
3098
3099
3100
3101
3102
3103
3104
3105
3106
3107
3108
3109
3110
3111

  /* If pList==0, it means this routine was called to make a primary
  ** key out of the last column added to the table under construction.
  ** So create a fake list to simulate this.
  */
  if( pList==0 ){
    Token prevCol;
    Column *pCol = &pTab->aCol[pTab->nCol-1];
    pCol->colFlags |= COLFLAG_UNIQUE;
    sqlite3TokenInit(&prevCol, pCol->zName);
    pList = sqlite3ExprListAppend(pParse, 0,
              sqlite3ExprAlloc(db, TK_ID, &prevCol, 0));
    if( pList==0 ) goto exit_create_index;
    assert( pList->nExpr==1 );
    sqlite3ExprListSetSortOrder(pList, sortOrder);
  }else{
    sqlite3ExprListCheckLength(pParse, pList, "index");
Changes to src/sqliteInt.h.
1750
1751
1752
1753
1754
1755
1756

1757
1758
1759
1760
1761
1762
1763
};

/* Allowed values for Column.colFlags:
*/
#define COLFLAG_PRIMKEY  0x0001    /* Column is part of the primary key */
#define COLFLAG_HIDDEN   0x0002    /* A hidden column in a virtual table */
#define COLFLAG_HASTYPE  0x0004    /* Type name follows column name */


/*
** A "Collating Sequence" is defined by an instance of the following
** structure. Conceptually, a collating sequence consists of a name and
** a comparison routine that defines the order of that sequence.
**
** If CollSeq.xCmp is NULL, it means that the







>







1750
1751
1752
1753
1754
1755
1756
1757
1758
1759
1760
1761
1762
1763
1764
};

/* Allowed values for Column.colFlags:
*/
#define COLFLAG_PRIMKEY  0x0001    /* Column is part of the primary key */
#define COLFLAG_HIDDEN   0x0002    /* A hidden column in a virtual table */
#define COLFLAG_HASTYPE  0x0004    /* Type name follows column name */
#define COLFLAG_UNIQUE   0x0008    /* Column def contains "UNIQUE" or "PK" */

/*
** A "Collating Sequence" is defined by an instance of the following
** structure. Conceptually, a collating sequence consists of a name and
** a comparison routine that defines the order of that sequence.
**
** If CollSeq.xCmp is NULL, it means that the
Changes to test/notnull.test.
557
558
559
560
561
562
563












































564

  }
} {1 {NOT NULL constraint failed: t1.b}}
verify_ex_errcode notnull-5.4b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-5.5 {
  execsql { SELECT * FROM t1 }
} {1 2}













































finish_test








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

>
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
  }
} {1 {NOT NULL constraint failed: t1.b}}
verify_ex_errcode notnull-5.4b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-5.5 {
  execsql { SELECT * FROM t1 }
} {1 2}

#-------------------------------------------------------------------------
# Check that UNIQUE NOT NULL indexes are always recognized as such.
#
proc uses_op_next {sql} {
  db eval "EXPLAIN $sql" a {
    if {$a(opcode)=="Next"} { return 1 }
  }
  return 0
}

proc do_uses_op_next_test {tn sql res} {
  uplevel [list do_test $tn [list uses_op_next $sql] $res]
}

reset_db
do_execsql_test notnull-6.0 {
  CREATE TABLE t1(a UNIQUE);
  CREATE TABLE t2(a NOT NULL UNIQUE);
  CREATE TABLE t3(a UNIQUE NOT NULL);
  CREATE TABLE t4(a NOT NULL);
  CREATE UNIQUE INDEX t4a ON t4(a);

  CREATE TABLE t5(a PRIMARY KEY);
  CREATE TABLE t6(a PRIMARY KEY NOT NULL);
  CREATE TABLE t7(a NOT NULL PRIMARY KEY);
  CREATE TABLE t8(a PRIMARY KEY) WITHOUT ROWID;

  CREATE TABLE t9(a PRIMARY KEY UNIQUE NOT NULL);
  CREATE TABLE t10(a UNIQUE PRIMARY KEY NOT NULL);
}

do_uses_op_next_test notnull-6.1 "SELECT * FROM t1 WHERE a IS ?" 1
do_uses_op_next_test notnull-6.2 "SELECT * FROM t2 WHERE a IS ?" 0
do_uses_op_next_test notnull-6.3 "SELECT * FROM t3 WHERE a IS ?" 0
do_uses_op_next_test notnull-6.4 "SELECT * FROM t4 WHERE a IS ?" 0

do_uses_op_next_test notnull-6.5 "SELECT * FROM t5 WHERE a IS ?" 1
do_uses_op_next_test notnull-6.6 "SELECT * FROM t6 WHERE a IS ?" 0
do_uses_op_next_test notnull-6.7 "SELECT * FROM t7 WHERE a IS ?" 0
do_uses_op_next_test notnull-6.8 "SELECT * FROM t8 WHERE a IS ?" 0

do_uses_op_next_test notnull-6.9 "SELECT * FROM t8 WHERE a IS ?" 0
do_uses_op_next_test notnull-6.10 "SELECT * FROM t8 WHERE a IS ?" 0

finish_test