SQLite

Check-in [59bd0ec7d4]
Login

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

Overview
Comment:Fix problems with INSERT INTO ... SELECT ... statements that write to tables with __hidden__ columns.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 59bd0ec7d4327852ee8c0206b2c59d0a12484db8
User & Date: dan 2015-11-19 16:46:46.075
Context
2015-11-19
17:55
When manifesting a view as part of an DELETE or UPDATE, be sure to include the hidden columns in the manifestation. (check-in: 28df5dc4a9 user: drh tags: trunk)
16:46
Fix problems with INSERT INTO ... SELECT ... statements that write to tables with __hidden__ columns. (check-in: 59bd0ec7d4 user: dan tags: trunk)
16:33
Create the new TK_ASTERISK token to represent the "*" in "SELECT *". Formerly that operator was TK_ALL, which was also used for UNION ALL. Less confusion if they operator symbols are distinct. (check-in: 201ac6d449 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/build.c.
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058


1059
1060
1061
1062
1063
1064
1065
  sqlite3DbFree(db, zName);
  return;
}

/* Set properties of a table column based on the (magical)
** name of the column.
*/
void sqlite3ColumnPropertiesFromName(Column *pCol){
#if SQLITE_ENABLE_HIDDEN_COLUMNS
  if( sqlite3_strnicmp(pCol->zName, "__hidden__", 10)==0 ){
    pCol->colFlags |= COLFLAG_HIDDEN;


  }
#endif
}


/*
** Add a new column to the table currently being constructed.







|



>
>







1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
  sqlite3DbFree(db, zName);
  return;
}

/* Set properties of a table column based on the (magical)
** name of the column.
*/
void sqlite3ColumnPropertiesFromName(Table *pTab, Column *pCol){
#if SQLITE_ENABLE_HIDDEN_COLUMNS
  if( sqlite3_strnicmp(pCol->zName, "__hidden__", 10)==0 ){
    pCol->colFlags |= COLFLAG_HIDDEN;
  }else if( pTab && pCol!=pTab->aCol && (pCol[-1].colFlags & COLFLAG_HIDDEN) ){
    pTab->tabFlags |= TF_OOOHidden;
  }
#endif
}


/*
** Add a new column to the table currently being constructed.
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
      return;
    }
    p->aCol = aNew;
  }
  pCol = &p->aCol[p->nCol];
  memset(pCol, 0, sizeof(p->aCol[0]));
  pCol->zName = z;
  sqlite3ColumnPropertiesFromName(pCol);
 
  /* If there is no type specified, columns have the default affinity
  ** 'BLOB'. If there is a type specified, then sqlite3AddColumnType() will
  ** be called next to set pCol->affinity correctly.
  */
  pCol->affinity = SQLITE_AFF_BLOB;
  pCol->szEst = 1;







|







1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
      return;
    }
    p->aCol = aNew;
  }
  pCol = &p->aCol[p->nCol];
  memset(pCol, 0, sizeof(p->aCol[0]));
  pCol->zName = z;
  sqlite3ColumnPropertiesFromName(p, pCol);
 
  /* If there is no type specified, columns have the default affinity
  ** 'BLOB'. If there is a type specified, then sqlite3AddColumnType() will
  ** be called next to set pCol->affinity correctly.
  */
  pCol->affinity = SQLITE_AFF_BLOB;
  pCol->szEst = 1;
Changes to src/insert.c.
1905
1906
1907
1908
1909
1910
1911





1912
1913
1914
1915
1916
1917
1918
  }
  if( pDest->iPKey!=pSrc->iPKey ){
    return 0;   /* Both tables must have the same INTEGER PRIMARY KEY */
  }
  for(i=0; i<pDest->nCol; i++){
    Column *pDestCol = &pDest->aCol[i];
    Column *pSrcCol = &pSrc->aCol[i];





    if( pDestCol->affinity!=pSrcCol->affinity ){
      return 0;    /* Affinity must be the same on all columns */
    }
    if( !xferCompatibleCollation(pDestCol->zColl, pSrcCol->zColl) ){
      return 0;    /* Collating sequence must be the same on all columns */
    }
    if( pDestCol->notNull && !pSrcCol->notNull ){







>
>
>
>
>







1905
1906
1907
1908
1909
1910
1911
1912
1913
1914
1915
1916
1917
1918
1919
1920
1921
1922
1923
  }
  if( pDest->iPKey!=pSrc->iPKey ){
    return 0;   /* Both tables must have the same INTEGER PRIMARY KEY */
  }
  for(i=0; i<pDest->nCol; i++){
    Column *pDestCol = &pDest->aCol[i];
    Column *pSrcCol = &pSrc->aCol[i];
#ifdef SQLITE_ENABLE_HIDDEN_COLUMNS
    if( (pDestCol->colFlags | pSrcCol->colFlags) & COLFLAG_HIDDEN ){
      return 0;    /* Neither table may have __hidden__ columns */
    }
#endif
    if( pDestCol->affinity!=pSrcCol->affinity ){
      return 0;    /* Affinity must be the same on all columns */
    }
    if( !xferCompatibleCollation(pDestCol->zColl, pSrcCol->zColl) ){
      return 0;    /* Collating sequence must be the same on all columns */
    }
    if( pDestCol->notNull && !pSrcCol->notNull ){
Changes to src/select.c.
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669
        for(j=nName-1; j>0 && sqlite3Isdigit(zName[j]); j--){}
        if( zName[j]==':' ) nName = j;
      }
      zName = sqlite3MPrintf(db, "%.*z:%u", nName, zName, ++cnt);
      if( cnt>3 ) sqlite3_randomness(sizeof(cnt), &cnt);
    }
    pCol->zName = zName;
    sqlite3ColumnPropertiesFromName(pCol);
    if( zName && sqlite3HashInsert(&ht, zName, pCol)==pCol ){
      db->mallocFailed = 1;
    }
  }
  sqlite3HashClear(&ht);
  if( db->mallocFailed ){
    for(j=0; j<i; j++){







|







1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669
        for(j=nName-1; j>0 && sqlite3Isdigit(zName[j]); j--){}
        if( zName[j]==':' ) nName = j;
      }
      zName = sqlite3MPrintf(db, "%.*z:%u", nName, zName, ++cnt);
      if( cnt>3 ) sqlite3_randomness(sizeof(cnt), &cnt);
    }
    pCol->zName = zName;
    sqlite3ColumnPropertiesFromName(0, pCol);
    if( zName && sqlite3HashInsert(&ht, zName, pCol)==pCol ){
      db->mallocFailed = 1;
    }
  }
  sqlite3HashClear(&ht);
  if( db->mallocFailed ){
    for(j=0; j<i; j++){
Changes to src/sqliteInt.h.
3327
3328
3329
3330
3331
3332
3333
3334
3335
3336
3337
3338
3339
3340
3341
void sqlite3DeleteColumnNames(sqlite3*,Table*);
int sqlite3ColumnsFromExprList(Parse*,ExprList*,i16*,Column**);
Table *sqlite3ResultSetOfSelect(Parse*,Select*);
void sqlite3OpenMasterTable(Parse *, int);
Index *sqlite3PrimaryKeyIndex(Table*);
i16 sqlite3ColumnOfIndex(Index*, i16);
void sqlite3StartTable(Parse*,Token*,Token*,int,int,int,int);
void sqlite3ColumnPropertiesFromName(Column*);
void sqlite3AddColumn(Parse*,Token*);
void sqlite3AddNotNull(Parse*, int);
void sqlite3AddPrimaryKey(Parse*, ExprList*, int, int, int);
void sqlite3AddCheckConstraint(Parse*, Expr*);
void sqlite3AddColumnType(Parse*,Token*);
void sqlite3AddDefaultValue(Parse*,ExprSpan*);
void sqlite3AddCollateType(Parse*, Token*);







|







3327
3328
3329
3330
3331
3332
3333
3334
3335
3336
3337
3338
3339
3340
3341
void sqlite3DeleteColumnNames(sqlite3*,Table*);
int sqlite3ColumnsFromExprList(Parse*,ExprList*,i16*,Column**);
Table *sqlite3ResultSetOfSelect(Parse*,Select*);
void sqlite3OpenMasterTable(Parse *, int);
Index *sqlite3PrimaryKeyIndex(Table*);
i16 sqlite3ColumnOfIndex(Index*, i16);
void sqlite3StartTable(Parse*,Token*,Token*,int,int,int,int);
void sqlite3ColumnPropertiesFromName(Table*, Column*);
void sqlite3AddColumn(Parse*,Token*);
void sqlite3AddNotNull(Parse*, int);
void sqlite3AddPrimaryKey(Parse*, ExprList*, int, int, int);
void sqlite3AddCheckConstraint(Parse*, Expr*);
void sqlite3AddColumnType(Parse*,Token*);
void sqlite3AddDefaultValue(Parse*,ExprSpan*);
void sqlite3AddCollateType(Parse*, Token*);
Changes to test/hidden.test.
66
67
68
69
70
71
72
73



























74
  } {1 2 3 4 5 {}}
  
  do_execsql_test 2.$tn.5 {
    INSERT INTO v1(a, b, __hidden__c) VALUES(7, 8, 9);
    SELECT * FROM x1;
  } {1 2 3 4 5 {} 7 8 9}
}
  



























finish_test







|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
  } {1 2 3 4 5 {}}
  
  do_execsql_test 2.$tn.5 {
    INSERT INTO v1(a, b, __hidden__c) VALUES(7, 8, 9);
    SELECT * FROM x1;
  } {1 2 3 4 5 {} 7 8 9}
}

#-------------------------------------------------------------------------
# Test INSERT INTO ... SELECT ... statements that write to tables with
# hidden columns.
#
do_execsql_test 3.1 {
  CREATE TABLE t4(a, __hidden__b, c);
  INSERT INTO t4 SELECT 1, 2;
  SELECT a, __hidden__b, c FROM t4;
} {1 {} 2}

do_execsql_test 3.2.1 {
  CREATE TABLE t5(__hidden__a, b, c);
  CREATE TABLE t6(__hidden__a, b, c);
  INSERT INTO t6(__hidden__a, b, c) VALUES(1, 2, 3);
  INSERT INTO t6(__hidden__a, b, c) VALUES(4, 5, 6);
  INSERT INTO t6(__hidden__a, b, c) VALUES(7, 8, 9);
}

do_execsql_test 3.2.2 {
  INSERT INTO t5 SELECT * FROM t6;
  SELECT * FROM t5;
} {2 3   5 6   8 9}

do_execsql_test 3.2.3 {
  SELECT __hidden__a FROM t5;
} {{} {} {}}

finish_test