/ Check-in [fb555c3c]
Login

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

Overview
Comment:Carry table column types through into VIEW definitions, where possible.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: fb555c3c2af7f5e62ff839658f4fba7b645d3a68
User & Date: drh 2016-04-05 20:59:12
Context
2016-04-07
21:29
Carry table column types through into VIEW definitions, where possible. check-in: 3360ab09 user: drh tags: branch-3.12.0
2016-04-05
23:39
Remove an unnecessary branch in the sqlite3LogEstToInt() routine. check-in: da81d7af user: drh tags: trunk
21:07
Defer opening the file used for the temp database (where CREATE TEMP TABLE tables are stored) until the database is too large to reside entirely within the cache. There are likely still problems on this branch. check-in: be5a549e user: dan tags: tempfiles-lazy-open
20:59
Carry table column types through into VIEW definitions, where possible. check-in: fb555c3c user: drh tags: trunk
19:46
Remove superfluous directories from the Makefile clean targets. check-in: 0bf9926c user: mistachkin tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/build.c.

  2172   2172     ** Note that the call to sqlite3ResultSetOfSelect() will expand any
  2173   2173     ** "*" elements in the results set of the view and will assign cursors
  2174   2174     ** to the elements of the FROM clause.  But we do not want these changes
  2175   2175     ** to be permanent.  So the computation is done on a copy of the SELECT
  2176   2176     ** statement that defines the view.
  2177   2177     */
  2178   2178     assert( pTable->pSelect );
  2179         -  if( pTable->pCheck ){
         2179  +  pSel = sqlite3SelectDup(db, pTable->pSelect, 0);
         2180  +  if( pSel ){
         2181  +    n = pParse->nTab;
         2182  +    sqlite3SrcListAssignCursors(pParse, pSel->pSrc);
         2183  +    pTable->nCol = -1;
  2180   2184       db->lookaside.bDisable++;
  2181         -    sqlite3ColumnsFromExprList(pParse, pTable->pCheck, 
  2182         -                               &pTable->nCol, &pTable->aCol);
  2183         -    db->lookaside.bDisable--;
  2184         -  }else{
  2185         -    pSel = sqlite3SelectDup(db, pTable->pSelect, 0);
  2186         -    if( pSel ){
  2187         -      n = pParse->nTab;
  2188         -      sqlite3SrcListAssignCursors(pParse, pSel->pSrc);
  2189         -      pTable->nCol = -1;
  2190         -      db->lookaside.bDisable++;
  2191   2185   #ifndef SQLITE_OMIT_AUTHORIZATION
  2192         -      xAuth = db->xAuth;
  2193         -      db->xAuth = 0;
  2194         -      pSelTab = sqlite3ResultSetOfSelect(pParse, pSel);
  2195         -      db->xAuth = xAuth;
         2186  +    xAuth = db->xAuth;
         2187  +    db->xAuth = 0;
         2188  +    pSelTab = sqlite3ResultSetOfSelect(pParse, pSel);
         2189  +    db->xAuth = xAuth;
  2196   2190   #else
  2197         -      pSelTab = sqlite3ResultSetOfSelect(pParse, pSel);
  2198         -#endif
  2199         -      db->lookaside.bDisable--;
  2200         -      pParse->nTab = n;
  2201         -      if( pSelTab ){
  2202         -        assert( pTable->aCol==0 );
  2203         -        pTable->nCol = pSelTab->nCol;
  2204         -        pTable->aCol = pSelTab->aCol;
  2205         -        pSelTab->nCol = 0;
  2206         -        pSelTab->aCol = 0;
  2207         -        sqlite3DeleteTable(db, pSelTab);
  2208         -        assert( sqlite3SchemaMutexHeld(db, 0, pTable->pSchema) );
  2209         -      }else{
  2210         -        pTable->nCol = 0;
  2211         -        nErr++;
  2212         -      }
  2213         -      sqlite3SelectDelete(db, pSel);
  2214         -    } else {
         2191  +    pSelTab = sqlite3ResultSetOfSelect(pParse, pSel);
         2192  +#endif
         2193  +    pParse->nTab = n;
         2194  +    if( pTable->pCheck ){
         2195  +      /* CREATE VIEW name(arglist) AS ...
         2196  +      ** The names of the columns in the table are taken from
         2197  +      ** arglist which is stored in pTable->pCheck.  The pCheck field
         2198  +      ** normally holds CHECK constraints on an ordinary table, but for
         2199  +      ** a VIEW it holds the list of column names.
         2200  +      */
         2201  +      sqlite3ColumnsFromExprList(pParse, pTable->pCheck, 
         2202  +                                 &pTable->nCol, &pTable->aCol);
         2203  +      if( db->mallocFailed==0 
         2204  +       && pParse->nErr==0
         2205  +       && pTable->nCol==pSel->pEList->nExpr
         2206  +      ){
         2207  +        sqlite3SelectAddColumnTypeAndCollation(pParse, pTable, pSel);
         2208  +      }
         2209  +    }else if( pSelTab ){
         2210  +      /* CREATE VIEW name AS...  without an argument list.  Construct
         2211  +      ** the column names from the SELECT statement that defines the view.
         2212  +      */
         2213  +      assert( pTable->aCol==0 );
         2214  +      pTable->nCol = pSelTab->nCol;
         2215  +      pTable->aCol = pSelTab->aCol;
         2216  +      pSelTab->nCol = 0;
         2217  +      pSelTab->aCol = 0;
         2218  +      assert( sqlite3SchemaMutexHeld(db, 0, pTable->pSchema) );
         2219  +    }else{
         2220  +      pTable->nCol = 0;
  2215   2221         nErr++;
  2216   2222       }
         2223  +    if( pSelTab ) sqlite3DeleteTable(db, pSelTab);
         2224  +    sqlite3SelectDelete(db, pSel);
         2225  +    db->lookaside.bDisable--;
         2226  +  } else {
         2227  +    nErr++;
  2217   2228     }
  2218   2229     pTable->pSchema->schemaFlags |= DB_UnresetViews;
  2219   2230   #endif /* SQLITE_OMIT_VIEW */
  2220   2231     return nErr;  
  2221   2232   }
  2222   2233   #endif /* !defined(SQLITE_OMIT_VIEW) || !defined(SQLITE_OMIT_VIRTUALTABLE) */
  2223   2234   

Changes to src/select.c.

  1700   1700   ** The column list presumably came from selectColumnNamesFromExprList().
  1701   1701   ** The column list has only names, not types or collations.  This
  1702   1702   ** routine goes through and adds the types and collations.
  1703   1703   **
  1704   1704   ** This routine requires that all identifiers in the SELECT
  1705   1705   ** statement be resolved.
  1706   1706   */
  1707         -static void selectAddColumnTypeAndCollation(
         1707  +void sqlite3SelectAddColumnTypeAndCollation(
  1708   1708     Parse *pParse,        /* Parsing contexts */
  1709   1709     Table *pTab,          /* Add column type information to this table */
  1710   1710     Select *pSelect       /* SELECT used to determine types and collations */
  1711   1711   ){
  1712   1712     sqlite3 *db = pParse->db;
  1713   1713     NameContext sNC;
  1714   1714     Column *pCol;
................................................................................
  1722   1722     assert( (pSelect->selFlags & SF_Resolved)!=0 );
  1723   1723     assert( pTab->nCol==pSelect->pEList->nExpr || db->mallocFailed );
  1724   1724     if( db->mallocFailed ) return;
  1725   1725     memset(&sNC, 0, sizeof(sNC));
  1726   1726     sNC.pSrcList = pSelect->pSrc;
  1727   1727     a = pSelect->pEList->a;
  1728   1728     for(i=0, pCol=pTab->aCol; i<pTab->nCol; i++, pCol++){
         1729  +    const char *zType;
         1730  +    int n, m;
  1729   1731       p = a[i].pExpr;
  1730         -    columnType(&sNC, p, 0, 0, 0, &pCol->szEst);
         1732  +    zType = columnType(&sNC, p, 0, 0, 0, &pCol->szEst);
  1731   1733       szAll += pCol->szEst;
  1732   1734       pCol->affinity = sqlite3ExprAffinity(p);
         1735  +    if( zType && (m = sqlite3Strlen30(zType))>0 ){
         1736  +      n = sqlite3Strlen30(pCol->zName);
         1737  +      pCol->zName = sqlite3DbReallocOrFree(db, pCol->zName, n+m+2);
         1738  +      if( pCol->zName ){
         1739  +        memcpy(&pCol->zName[n+1], zType, m+1);
         1740  +        pCol->colFlags |= COLFLAG_HASTYPE;
         1741  +      }
         1742  +    }
  1733   1743       if( pCol->affinity==0 ) pCol->affinity = SQLITE_AFF_BLOB;
  1734   1744       pColl = sqlite3ExprCollSeq(pParse, p);
  1735   1745       if( pColl && pCol->zColl==0 ){
  1736   1746         pCol->zColl = sqlite3DbStrDup(db, pColl->zName);
  1737   1747       }
  1738   1748     }
  1739   1749     pTab->szTabRow = sqlite3LogEst(szAll*4);
................................................................................
  1762   1772     /* The sqlite3ResultSetOfSelect() is only used n contexts where lookaside
  1763   1773     ** is disabled */
  1764   1774     assert( db->lookaside.bDisable );
  1765   1775     pTab->nRef = 1;
  1766   1776     pTab->zName = 0;
  1767   1777     pTab->nRowLogEst = 200; assert( 200==sqlite3LogEst(1048576) );
  1768   1778     sqlite3ColumnsFromExprList(pParse, pSelect->pEList, &pTab->nCol, &pTab->aCol);
  1769         -  selectAddColumnTypeAndCollation(pParse, pTab, pSelect);
         1779  +  sqlite3SelectAddColumnTypeAndCollation(pParse, pTab, pSelect);
  1770   1780     pTab->iPKey = -1;
  1771   1781     if( db->mallocFailed ){
  1772   1782       sqlite3DeleteTable(db, pTab);
  1773   1783       return 0;
  1774   1784     }
  1775   1785     return pTab;
  1776   1786   }
................................................................................
  4546   4556       Table *pTab = pFrom->pTab;
  4547   4557       assert( pTab!=0 );
  4548   4558       if( (pTab->tabFlags & TF_Ephemeral)!=0 ){
  4549   4559         /* A sub-query in the FROM clause of a SELECT */
  4550   4560         Select *pSel = pFrom->pSelect;
  4551   4561         if( pSel ){
  4552   4562           while( pSel->pPrior ) pSel = pSel->pPrior;
  4553         -        selectAddColumnTypeAndCollation(pParse, pTab, pSel);
         4563  +        sqlite3SelectAddColumnTypeAndCollation(pParse, pTab, pSel);
  4554   4564         }
  4555   4565       }
  4556   4566     }
  4557   4567   }
  4558   4568   #endif
  4559   4569   
  4560   4570   

Changes to src/sqliteInt.h.

  3464   3464   void sqlite3Pragma(Parse*,Token*,Token*,Token*,int);
  3465   3465   void sqlite3ResetAllSchemasOfConnection(sqlite3*);
  3466   3466   void sqlite3ResetOneSchema(sqlite3*,int);
  3467   3467   void sqlite3CollapseDatabaseArray(sqlite3*);
  3468   3468   void sqlite3CommitInternalChanges(sqlite3*);
  3469   3469   void sqlite3DeleteColumnNames(sqlite3*,Table*);
  3470   3470   int sqlite3ColumnsFromExprList(Parse*,ExprList*,i16*,Column**);
         3471  +void sqlite3SelectAddColumnTypeAndCollation(Parse*,Table*,Select*);
  3471   3472   Table *sqlite3ResultSetOfSelect(Parse*,Select*);
  3472   3473   void sqlite3OpenMasterTable(Parse *, int);
  3473   3474   Index *sqlite3PrimaryKeyIndex(Table*);
  3474   3475   i16 sqlite3ColumnOfIndex(Index*, i16);
  3475   3476   void sqlite3StartTable(Parse*,Token*,Token*,int,int,int,int);
  3476   3477   #if SQLITE_ENABLE_HIDDEN_COLUMNS
  3477   3478     void sqlite3ColumnPropertiesFromName(Table*, Column*);

Changes to test/view.test.

    87     87   do_test view-1.8 {
    88     88     db close
    89     89     sqlite3 db test.db
    90     90     execsql {
    91     91       SELECT * FROM v1 ORDER BY a;
    92     92     }
    93     93   } {2 3 5 6 8 9}
           94  +
           95  +do_execsql_test view-1.10 {
           96  +  CREATE TABLE t9(x INTEGER);
           97  +  CREATE VIEW v9a AS SELECT x FROM t9;
           98  +  CREATE VIEW v9b AS SELECT * FROM t9;
           99  +  CREATE VIEW v9c(x) AS SELECT x FROM t9;
          100  +  CREATE VIEW v9d(x) AS SELECT * FROM t9;
          101  +} {}
          102  +do_execsql_test view-1.11 {
          103  +  PRAGMA table_info(v9a);
          104  +} {0 x INTEGER 0 {} 0}
          105  +do_execsql_test view-1.12 {
          106  +  PRAGMA table_info(v9b);
          107  +} {0 x INTEGER 0 {} 0}
          108  +do_execsql_test view-1.13 {
          109  +  PRAGMA table_info(v9c);
          110  +} {0 x INTEGER 0 {} 0}
          111  +do_execsql_test view-1.14 {
          112  +  PRAGMA table_info(v9d);
          113  +} {0 x INTEGER 0 {} 0}
    94    114   
    95    115   do_test view-2.1 {
    96    116     execsql {
    97    117       CREATE VIEW v2 AS SELECT * FROM t1 WHERE a>5
    98    118     };  # No semicolon
    99    119     execsql2 {
   100    120       SELECT * FROM v2;