/ Check-in [9a514b50]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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 Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

3552
3553
3554
3555
3556
3557
3558

3559
3560
3561
3562
3563
3564
3565
....
3575
3576
3577
3578
3579
3580
3581
3582
3583
3584
3585
3586
3587






3588



3589
3590
3591
3592
3593
3594
3595
  if( pCte==pParse->pCte && (pTab = pCte->pTab) ){
    /* This is the recursive part of a recursive CTE */
    pFrom->pTab = pTab;
    pTab->nRef++;
  }else{
    ExprList *pEList;
    Select *pSel;

    int bRecursive;

    pFrom->pTab = pTab = sqlite3DbMallocZero(db, sizeof(Table));
    if( pTab==0 ) return WRC_Abort;
    pTab->nRef = 1;
    pTab->zName = sqlite3MPrintf(db, "%s", pCte->zName);
    pTab->iPKey = -1;
................................................................................
    if( bRecursive ){
      assert( pSel->pPrior );
      sqlite3WalkSelect(pWalker, pSel->pPrior);
    }else{
      sqlite3WalkSelect(pWalker, pSel);
    }

    if( pCte->pCols ){
      pEList = pCte->pCols;
    }else{
      Select *pLeft;
      for(pLeft=pSel; pLeft->pPrior; pLeft=pLeft->pPrior);
      pEList = pLeft->pEList;






    }



    selectColumnsFromExprList(pParse, pEList, &pTab->nCol, &pTab->aCol);

    if( bRecursive ){
      int nRef = pTab->nRef;
      pCte->pTab = pTab;
      sqlite3WalkSelect(pWalker, pSel);
      pCte->pTab = 0;







>







 







<
<
<
<
|
|
>
>
>
>
>
>
|
>
>
>







3552
3553
3554
3555
3556
3557
3558
3559
3560
3561
3562
3563
3564
3565
3566
....
3576
3577
3578
3579
3580
3581
3582




3583
3584
3585
3586
3587
3588
3589
3590
3591
3592
3593
3594
3595
3596
3597
3598
3599
3600
3601
  if( pCte==pParse->pCte && (pTab = pCte->pTab) ){
    /* This is the recursive part of a recursive CTE */
    pFrom->pTab = pTab;
    pTab->nRef++;
  }else{
    ExprList *pEList;
    Select *pSel;
    Select *pLeft;                /* Left-most SELECT statement */
    int bRecursive;

    pFrom->pTab = pTab = sqlite3DbMallocZero(db, sizeof(Table));
    if( pTab==0 ) return WRC_Abort;
    pTab->nRef = 1;
    pTab->zName = sqlite3MPrintf(db, "%s", pCte->zName);
    pTab->iPKey = -1;
................................................................................
    if( bRecursive ){
      assert( pSel->pPrior );
      sqlite3WalkSelect(pWalker, pSel->pPrior);
    }else{
      sqlite3WalkSelect(pWalker, pSel);
    }





    for(pLeft=pSel; pLeft->pPrior; pLeft=pLeft->pPrior);
    pEList = pLeft->pEList;
    if( pCte->pCols ){
      if( pEList->nExpr!=pCte->pCols->nExpr ){
        sqlite3ErrorMsg(pParse, "cte \"%s\" returns %d values for %d columns",
            pCte->zName, pEList->nExpr, pCte->pCols->nExpr
        );
        return WRC_Abort;
      }
      pEList = pCte->pCols;
    }

    selectColumnsFromExprList(pParse, pEList, &pTab->nCol, &pTab->aCol);

    if( bRecursive ){
      int nRef = pTab->nRef;
      pCte->pTab = pTab;
      sqlite3WalkSelect(pWalker, pSel);
      pCte->pTab = 0;

Changes to test/with1.test.

160
161
162
163
164
165
166




































167
168
169
170
171
172
173
} {1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0}

do_execsql_test 5.5 {
  WITH i(x) AS ( VALUES(1) UNION SELECT (x+1)%10 FROM i)
  SELECT x FROM i LIMIT 20;
} {1 2 3 4 5 6 7 8 9 0}





































#-------------------------------------------------------------------------
#
do_execsql_test 6.1 {
  CREATE TABLE f(
      id INTEGER PRIMARY KEY, parentid REFERENCES f, name TEXT
  );








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







160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
} {1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0}

do_execsql_test 5.5 {
  WITH i(x) AS ( VALUES(1) UNION SELECT (x+1)%10 FROM i)
  SELECT x FROM i LIMIT 20;
} {1 2 3 4 5 6 7 8 9 0}

do_catchsql_test 5.6.1 {
  WITH i(x, y) AS ( VALUES(1) )
  SELECT * FROM i;
} {1 {cte "i" returns 1 values for 2 columns}}

do_catchsql_test 5.6.2 {
  WITH i(x) AS ( VALUES(1,2) )
  SELECT * FROM i;
} {1 {cte "i" returns 2 values for 1 columns}}

do_catchsql_test 5.6.3 {
  CREATE TABLE t5(a, b);
  WITH i(x) AS ( SELECT * FROM t5 )
  SELECT * FROM i;
} {1 {cte "i" returns 2 values for 1 columns}}

do_catchsql_test 5.6.4 {
  WITH i(x) AS ( SELECT 1, 2 UNION ALL SELECT 1 )
  SELECT * FROM i;
} {1 {cte "i" returns 2 values for 1 columns}}

do_catchsql_test 5.6.5 {
  WITH i(x) AS ( SELECT 1 UNION ALL SELECT 1, 2 )
  SELECT * FROM i;
} {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}

do_catchsql_test 5.6.6 {
  WITH i(x) AS ( SELECT 1 UNION ALL SELECT x+1, x*2 FROM i )
  SELECT * FROM i;
} {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}

do_catchsql_test 5.6.7 {
  WITH i(x) AS ( SELECT 1, 2 UNION SELECT x+1 FROM i )
  SELECT * FROM i;
} {1 {cte "i" returns 2 values for 1 columns}}

#-------------------------------------------------------------------------
#
do_execsql_test 6.1 {
  CREATE TABLE f(
      id INTEGER PRIMARY KEY, parentid REFERENCES f, name TEXT
  );