/ Check-in [9a514b50]
Login

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

Overview
Comment:Return an error if a CTE specifies a different number of columns than its SELECT statement returns.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | common-table-expr
Files: files | file ages | folders
SHA1: 9a514b50e4b01f109fbdb0aabcbfe1ddab129b44
User & Date: dan 2014-01-15 15:27:51
Context
2014-01-15
18:12
When resolving names, consider a reference to a recursive CTE column as equivalent to a reference to the outermost name-context. This ensures that correlated sub-queries are correctly identified as such. check-in: 61be2da0 user: dan tags: common-table-expr
15:27
Return an error if a CTE specifies a different number of columns than its SELECT statement returns. check-in: 9a514b50 user: dan tags: common-table-expr
14:40
Don't try to verify the schema of transient table (such as generated inside a WITH clause) when generating code for "IN table" operators. check-in: 860aa936 user: drh tags: common-table-expr
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

  3552   3552     if( pCte==pParse->pCte && (pTab = pCte->pTab) ){
  3553   3553       /* This is the recursive part of a recursive CTE */
  3554   3554       pFrom->pTab = pTab;
  3555   3555       pTab->nRef++;
  3556   3556     }else{
  3557   3557       ExprList *pEList;
  3558   3558       Select *pSel;
         3559  +    Select *pLeft;                /* Left-most SELECT statement */
  3559   3560       int bRecursive;
  3560   3561   
  3561   3562       pFrom->pTab = pTab = sqlite3DbMallocZero(db, sizeof(Table));
  3562   3563       if( pTab==0 ) return WRC_Abort;
  3563   3564       pTab->nRef = 1;
  3564   3565       pTab->zName = sqlite3MPrintf(db, "%s", pCte->zName);
  3565   3566       pTab->iPKey = -1;
................................................................................
  3575   3576       if( bRecursive ){
  3576   3577         assert( pSel->pPrior );
  3577   3578         sqlite3WalkSelect(pWalker, pSel->pPrior);
  3578   3579       }else{
  3579   3580         sqlite3WalkSelect(pWalker, pSel);
  3580   3581       }
  3581   3582   
         3583  +    for(pLeft=pSel; pLeft->pPrior; pLeft=pLeft->pPrior);
         3584  +    pEList = pLeft->pEList;
  3582   3585       if( pCte->pCols ){
         3586  +      if( pEList->nExpr!=pCte->pCols->nExpr ){
         3587  +        sqlite3ErrorMsg(pParse, "cte \"%s\" returns %d values for %d columns",
         3588  +            pCte->zName, pEList->nExpr, pCte->pCols->nExpr
         3589  +        );
         3590  +        return WRC_Abort;
         3591  +      }
  3583   3592         pEList = pCte->pCols;
  3584         -    }else{
  3585         -      Select *pLeft;
  3586         -      for(pLeft=pSel; pLeft->pPrior; pLeft=pLeft->pPrior);
  3587         -      pEList = pLeft->pEList;
  3588   3593       }
         3594  +
  3589   3595       selectColumnsFromExprList(pParse, pEList, &pTab->nCol, &pTab->aCol);
  3590   3596   
  3591   3597       if( bRecursive ){
  3592   3598         int nRef = pTab->nRef;
  3593   3599         pCte->pTab = pTab;
  3594   3600         sqlite3WalkSelect(pWalker, pSel);
  3595   3601         pCte->pTab = 0;

Changes to test/with1.test.

   160    160   } {1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0}
   161    161   
   162    162   do_execsql_test 5.5 {
   163    163     WITH i(x) AS ( VALUES(1) UNION SELECT (x+1)%10 FROM i)
   164    164     SELECT x FROM i LIMIT 20;
   165    165   } {1 2 3 4 5 6 7 8 9 0}
   166    166   
          167  +do_catchsql_test 5.6.1 {
          168  +  WITH i(x, y) AS ( VALUES(1) )
          169  +  SELECT * FROM i;
          170  +} {1 {cte "i" returns 1 values for 2 columns}}
          171  +
          172  +do_catchsql_test 5.6.2 {
          173  +  WITH i(x) AS ( VALUES(1,2) )
          174  +  SELECT * FROM i;
          175  +} {1 {cte "i" returns 2 values for 1 columns}}
          176  +
          177  +do_catchsql_test 5.6.3 {
          178  +  CREATE TABLE t5(a, b);
          179  +  WITH i(x) AS ( SELECT * FROM t5 )
          180  +  SELECT * FROM i;
          181  +} {1 {cte "i" returns 2 values for 1 columns}}
          182  +
          183  +do_catchsql_test 5.6.4 {
          184  +  WITH i(x) AS ( SELECT 1, 2 UNION ALL SELECT 1 )
          185  +  SELECT * FROM i;
          186  +} {1 {cte "i" returns 2 values for 1 columns}}
          187  +
          188  +do_catchsql_test 5.6.5 {
          189  +  WITH i(x) AS ( SELECT 1 UNION ALL SELECT 1, 2 )
          190  +  SELECT * FROM i;
          191  +} {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
          192  +
          193  +do_catchsql_test 5.6.6 {
          194  +  WITH i(x) AS ( SELECT 1 UNION ALL SELECT x+1, x*2 FROM i )
          195  +  SELECT * FROM i;
          196  +} {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
          197  +
          198  +do_catchsql_test 5.6.7 {
          199  +  WITH i(x) AS ( SELECT 1, 2 UNION SELECT x+1 FROM i )
          200  +  SELECT * FROM i;
          201  +} {1 {cte "i" returns 2 values for 1 columns}}
          202  +
   167    203   #-------------------------------------------------------------------------
   168    204   #
   169    205   do_execsql_test 6.1 {
   170    206     CREATE TABLE f(
   171    207         id INTEGER PRIMARY KEY, parentid REFERENCES f, name TEXT
   172    208     );
   173    209