Index: src/build.c ================================================================== --- src/build.c +++ src/build.c @@ -2158,11 +2158,11 @@ sqlite3VdbeChangeP5(v, OPFLAG_P2ISREG); pParse->nTab = 2; addrTop = sqlite3VdbeCurrentAddr(v) + 1; sqlite3VdbeAddOp3(v, OP_InitCoroutine, regYield, 0, addrTop); if( pParse->nErr ) return; - pSelTab = sqlite3ResultSetOfSelect(pParse, pSelect); + pSelTab = sqlite3ResultSetOfSelect(pParse, pSelect, SQLITE_AFF_BLOB); if( pSelTab==0 ) return; assert( p->aCol==0 ); p->nCol = pSelTab->nCol; p->aCol = pSelTab->aCol; pSelTab->nCol = 0; @@ -2422,14 +2422,14 @@ pTable->nCol = -1; db->lookaside.bDisable++; #ifndef SQLITE_OMIT_AUTHORIZATION xAuth = db->xAuth; db->xAuth = 0; - pSelTab = sqlite3ResultSetOfSelect(pParse, pSel); + pSelTab = sqlite3ResultSetOfSelect(pParse, pSel, 0); db->xAuth = xAuth; #else - pSelTab = sqlite3ResultSetOfSelect(pParse, pSel); + pSelTab = sqlite3ResultSetOfSelect(pParse, pSel, 0); #endif pParse->nTab = n; if( pTable->pCheck ){ /* CREATE VIEW name(arglist) AS ... ** The names of the columns in the table are taken from @@ -2441,11 +2441,11 @@ &pTable->nCol, &pTable->aCol); if( db->mallocFailed==0 && pParse->nErr==0 && pTable->nCol==pSel->pEList->nExpr ){ - sqlite3SelectAddColumnTypeAndCollation(pParse, pTable, pSel); + sqlite3SelectAddColumnTypeAndCollation(pParse, pTable, pSel, 0); } }else if( pSelTab ){ /* CREATE VIEW name AS... without an argument list. Construct ** the column names from the SELECT statement that defines the view. */ Index: src/insert.c ================================================================== --- src/insert.c +++ src/insert.c @@ -86,22 +86,22 @@ sqlite3OomFault(db); return 0; } for(n=0; nnColumn; n++){ i16 x = pIdx->aiColumn[n]; + char aff; if( x>=0 ){ - pIdx->zColAff[n] = pTab->aCol[x].affinity; + aff = pTab->aCol[x].affinity; }else if( x==XN_ROWID ){ - pIdx->zColAff[n] = SQLITE_AFF_INTEGER; + aff = SQLITE_AFF_INTEGER; }else{ - char aff; assert( x==XN_EXPR ); assert( pIdx->aColExpr!=0 ); aff = sqlite3ExprAffinity(pIdx->aColExpr->a[n].pExpr); - if( aff==0 ) aff = SQLITE_AFF_BLOB; - pIdx->zColAff[n] = aff; } + if( aff==0 ) aff = SQLITE_AFF_BLOB; + pIdx->zColAff[n] = aff; } pIdx->zColAff[n] = 0; } return pIdx->zColAff; Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -2033,11 +2033,12 @@ ** statement be resolved. */ void sqlite3SelectAddColumnTypeAndCollation( Parse *pParse, /* Parsing contexts */ Table *pTab, /* Add column type information to this table */ - Select *pSelect /* SELECT used to determine types and collations */ + Select *pSelect, /* SELECT used to determine types and collations */ + char aff /* Default affinity for columns */ ){ sqlite3 *db = pParse->db; NameContext sNC; Column *pCol; CollSeq *pColl; @@ -2066,11 +2067,11 @@ if( pCol->zName ){ memcpy(&pCol->zName[n+1], zType, m+1); pCol->colFlags |= COLFLAG_HASTYPE; } } - if( pCol->affinity==0 ) pCol->affinity = SQLITE_AFF_BLOB; + if( pCol->affinity==0 ) pCol->affinity = aff; pColl = sqlite3ExprCollSeq(pParse, p); if( pColl && pCol->zColl==0 ){ pCol->zColl = sqlite3DbStrDup(db, pColl->zName); } } @@ -2079,11 +2080,11 @@ /* ** Given a SELECT statement, generate a Table structure that describes ** the result set of that SELECT. */ -Table *sqlite3ResultSetOfSelect(Parse *pParse, Select *pSelect){ +Table *sqlite3ResultSetOfSelect(Parse *pParse, Select *pSelect, char aff){ Table *pTab; sqlite3 *db = pParse->db; u64 savedFlags; savedFlags = db->flags; @@ -2099,11 +2100,11 @@ } pTab->nTabRef = 1; pTab->zName = 0; pTab->nRowLogEst = 200; assert( 200==sqlite3LogEst(1048576) ); sqlite3ColumnsFromExprList(pParse, pSelect->pEList, &pTab->nCol, &pTab->aCol); - sqlite3SelectAddColumnTypeAndCollation(pParse, pTab, pSelect); + sqlite3SelectAddColumnTypeAndCollation(pParse, pTab, pSelect, aff); pTab->iPKey = -1; if( db->mallocFailed ){ sqlite3DeleteTable(db, pTab); return 0; } @@ -5193,11 +5194,11 @@ if( (pTab->tabFlags & TF_Ephemeral)!=0 ){ /* A sub-query in the FROM clause of a SELECT */ Select *pSel = pFrom->pSelect; if( pSel ){ while( pSel->pPrior ) pSel = pSel->pPrior; - sqlite3SelectAddColumnTypeAndCollation(pParse, pTab, pSel); + sqlite3SelectAddColumnTypeAndCollation(pParse, pTab, pSel, 0); } } } } #endif Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -3909,12 +3909,12 @@ void sqlite3ResetOneSchema(sqlite3*,int); void sqlite3CollapseDatabaseArray(sqlite3*); void sqlite3CommitInternalChanges(sqlite3*); void sqlite3DeleteColumnNames(sqlite3*,Table*); int sqlite3ColumnsFromExprList(Parse*,ExprList*,i16*,Column**); -void sqlite3SelectAddColumnTypeAndCollation(Parse*,Table*,Select*); -Table *sqlite3ResultSetOfSelect(Parse*,Select*); +void sqlite3SelectAddColumnTypeAndCollation(Parse*,Table*,Select*,char); +Table *sqlite3ResultSetOfSelect(Parse*,Select*,char); void sqlite3OpenMasterTable(Parse *, int); Index *sqlite3PrimaryKeyIndex(Table*); i16 sqlite3ColumnOfIndex(Index*, i16); void sqlite3StartTable(Parse*,Token*,Token*,int,int,int,int); #if SQLITE_ENABLE_HIDDEN_COLUMNS Index: src/window.c ================================================================== --- src/window.c +++ src/window.c @@ -992,11 +992,11 @@ if( p->pSrc ){ Table *pTab2; p->pSrc->a[0].pSelect = pSub; sqlite3SrcListAssignCursors(pParse, p->pSrc); pSub->selFlags |= SF_Expanded; - pTab2 = sqlite3ResultSetOfSelect(pParse, pSub); + pTab2 = sqlite3ResultSetOfSelect(pParse, pSub, 0); if( pTab2==0 ){ rc = SQLITE_NOMEM; }else{ memcpy(pTab, pTab2, sizeof(Table)); pTab->tabFlags |= TF_Ephemeral; Index: test/view.test ================================================================== --- test/view.test +++ test/view.test @@ -721,7 +721,44 @@ } { 1 1 1 1 1 1 2 2 1 1 3 3 } + +#------------------------------------------------------------------------- +reset_db +do_execsql_test view-27.0 { + CREATE TABLE t0(c0 TEXT, c1); + INSERT INTO t0(c0, c1) VALUES (-1, 0); + CREATE VIEW v0(c0, c1) AS SELECT t0.c0, AVG(t0.c1) FROM t0; +} + +do_execsql_test view-27.1 { + SELECT c0, typeof(c0), affinity(c0), c1, typeof(c1), affinity(c1) FROM v0; +} { + -1 text text + 0.0 real none +} + +do_execsql_test view-27.2 { SELECT c0