/ Check-in [08f27cb3]
Login

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

Overview
Comment:Fix for tickets #32 and #33: Generate the names of the result set early, before doing the flattening optimization or evaluating subqueries. Otherwise, the result set column names are generated incorrectly or after they are needed. (CVS 553)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:08f27cb36805d38648274b6fe91dec43a5910057
User & Date: drh 2002-05-08 11:54:15
Context
2002-05-08
11:57
Increase the version number and update the change log in preparation for the 2.4.11 release. (CVS 554) check-in: f6e406aa user: drh tags: trunk
11:54
Fix for tickets #32 and #33: Generate the names of the result set early, before doing the flattening optimization or evaluating subqueries. Otherwise, the result set column names are generated incorrectly or after they are needed. (CVS 553) check-in: 08f27cb3 user: drh tags: trunk
2002-05-06
11:47
Added documentation about the new CASE expression. (CVS 552) check-in: cc541b10 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
927
928
929
930
931
932
933
934
935

936
937
938
939
940
941
942
943
...
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
....
1327
1328
1329
1330
1331
1332
1333
















1334
1335
1336
1337
1338
1339
1340
....
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.80 2002/04/30 19:20:29 drh Exp $
*/
#include "sqliteInt.h"

/*
** Allocate a new Select structure and return a pointer to that
** structure.
*/
................................................................................
** If flattening is not attempted, this routine is a no-op and return 0.
** If flattening is attempted this routine returns 1.
**
** All of the expression analysis must occur on both the outer query and
** the subquery before this routine runs.
*/
int flattenSubquery(Select *p, int iFrom, int isAgg, int subqueryIsAgg){
  Select *pSub;
  IdList *pSrc, *pSubSrc;

  ExprList *pList;
  int i;
  int iParent, iSub;
  Expr *pWhere;

  /* Check to see if flattening is permitted.  Return 0 if not.
  */
  if( p==0 ) return 0;
................................................................................
  pSubSrc = pSub->pSrc;
  assert( pSubSrc );
  if( pSubSrc->nId!=1 ) return 0;
  if( pSub->isDistinct && pSrc->nId>1 ) return 0;
  if( pSub->isDistinct && isAgg ) return 0;
  if( p->isDistinct && subqueryIsAgg ) return 0;

  /* If we reach this point, it means flatting is permitted for the
  ** i-th entry of the FROM clause in the outer query.
  */
  iParent = p->base + iFrom;
  iSub = pSub->base;
  substExprList(p->pEList, iParent, pSub->pEList, iSub);
  pList = p->pEList;
  for(i=0; i<pList->nExpr; i++){
................................................................................
    goto select_end;
  }

  /* Begin generating code.
  */
  v = sqliteGetVdbe(pParse);
  if( v==0 ) goto select_end;

















  /* Generate code for all sub-queries in the FROM clause
  */
  for(i=0; i<pTabList->nId; i++){
    if( pTabList->a[i].pSelect==0 ) continue;
    sqliteSelect(pParse, pTabList->a[i].pSelect, SRT_TempTable, base+i,
                 p, i, &isAgg);
................................................................................
        if( sqliteExprAnalyzeAggregates(pParse, pOrderBy->a[i].pExpr) ){
          goto select_end;
        }
      }
    }
  }

  /* Set the limiter
  */
  if( p->nLimit<=0 ){
    p->nOffset = 0;
  }else{
    if( p->nOffset<0 ) p->nOffset = 0;
    sqliteVdbeAddOp(v, OP_Limit, p->nLimit, p->nOffset);
  }
    

  /* Identify column names if we will be using in the callback.  This
  ** step is skipped if the output is going to a table or a memory cell.
  */
  if( eDest==SRT_Callback ){
    generateColumnNames(pParse, p->base, pTabList, pEList);
  }

  /* Reset the aggregator
  */
  if( isAgg ){
    sqliteVdbeAddOp(v, OP_AggReset, 0, pParse->nAgg);
    for(i=0; i<pParse->nAgg; i++){
      FuncDef *pFunc;
      if( (pFunc = pParse->aAgg[i].pFunc)!=0 && pFunc->xFinalize!=0 ){







|







 







|
|
>
|







 







|







 







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







 







<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
...
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
....
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
....
1405
1406
1407
1408
1409
1410
1411

















1412
1413
1414
1415
1416
1417
1418
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.81 2002/05/08 11:54:15 drh Exp $
*/
#include "sqliteInt.h"

/*
** Allocate a new Select structure and return a pointer to that
** structure.
*/
................................................................................
** If flattening is not attempted, this routine is a no-op and return 0.
** If flattening is attempted this routine returns 1.
**
** All of the expression analysis must occur on both the outer query and
** the subquery before this routine runs.
*/
int flattenSubquery(Select *p, int iFrom, int isAgg, int subqueryIsAgg){
  Select *pSub;       /* The inner query or "subquery" */
  IdList *pSrc;       /* The FROM clause of the outer query */
  IdList *pSubSrc;    /* The FROM clause of the subquery */
  ExprList *pList;    /* The result set of the outer query */
  int i;
  int iParent, iSub;
  Expr *pWhere;

  /* Check to see if flattening is permitted.  Return 0 if not.
  */
  if( p==0 ) return 0;
................................................................................
  pSubSrc = pSub->pSrc;
  assert( pSubSrc );
  if( pSubSrc->nId!=1 ) return 0;
  if( pSub->isDistinct && pSrc->nId>1 ) return 0;
  if( pSub->isDistinct && isAgg ) return 0;
  if( p->isDistinct && subqueryIsAgg ) return 0;

  /* If we reach this point, it means flattening is permitted for the
  ** i-th entry of the FROM clause in the outer query.
  */
  iParent = p->base + iFrom;
  iSub = pSub->base;
  substExprList(p->pEList, iParent, pSub->pEList, iSub);
  pList = p->pEList;
  for(i=0; i<pList->nExpr; i++){
................................................................................
    goto select_end;
  }

  /* Begin generating code.
  */
  v = sqliteGetVdbe(pParse);
  if( v==0 ) goto select_end;

  /* Identify column names if we will be using in the callback.  This
  ** step is skipped if the output is going to a table or a memory cell.
  */
  if( eDest==SRT_Callback ){
    generateColumnNames(pParse, p->base, pTabList, pEList);
  }

  /* Set the limiter
  */
  if( p->nLimit<=0 ){
    p->nOffset = 0;
  }else{
    if( p->nOffset<0 ) p->nOffset = 0;
    sqliteVdbeAddOp(v, OP_Limit, p->nLimit, p->nOffset);
  }

  /* Generate code for all sub-queries in the FROM clause
  */
  for(i=0; i<pTabList->nId; i++){
    if( pTabList->a[i].pSelect==0 ) continue;
    sqliteSelect(pParse, pTabList->a[i].pSelect, SRT_TempTable, base+i,
                 p, i, &isAgg);
................................................................................
        if( sqliteExprAnalyzeAggregates(pParse, pOrderBy->a[i].pExpr) ){
          goto select_end;
        }
      }
    }
  }


















  /* Reset the aggregator
  */
  if( isAgg ){
    sqliteVdbeAddOp(v, OP_AggReset, 0, pParse->nAgg);
    for(i=0; i<pParse->nAgg; i++){
      FuncDef *pFunc;
      if( (pFunc = pParse->aAgg[i].pFunc)!=0 && pFunc->xFinalize!=0 ){

Changes to test/view.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
117
118
119
120
121
122
123

124



125





















126
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing VIEW statements.
#
# $Id: view.test,v 1.3 2002/03/03 23:06:02 drh Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl

do_test view-1.0 {
  execsql {
    CREATE TABLE t1(a,b,c);
    INSERT INTO t1 VALUES(1,2,3);
................................................................................
} {7 8 9 10 11 12 13 14}
do_test view-2.6 {
  execsql {
    SELECT x FROM v2 WHERE a>10
  }
} {11}




























finish_test







|







 







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

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing VIEW statements.
#
# $Id: view.test,v 1.4 2002/05/08 11:54:16 drh Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl

do_test view-1.0 {
  execsql {
    CREATE TABLE t1(a,b,c);
    INSERT INTO t1 VALUES(1,2,3);
................................................................................
} {7 8 9 10 11 12 13 14}
do_test view-2.6 {
  execsql {
    SELECT x FROM v2 WHERE a>10
  }
} {11}

# Test that column name of views are generated correctly.
#
do_test view-3.1 {
  execsql2 {
    SELECT * FROM v1 LIMIT 1
  }
} {a 2 b 3}
do_test view-3.2 {
  execsql2 {
    SELECT * FROM v2 LIMIT 1
  }
} {x 7 a 8 b 9 c 10}
do_test view-3.3 {
  execsql2 {
    DROP VIEW v1;
    CREATE VIEW v1 AS SELECT a AS 'xyz', b+c AS 'pqr', c-b FROM t1;
    SELECT * FROM v1 LIMIT 1
  }
} {xyz 2 pqr 7 c-b 1}
do_test  view-3.4 {
  execsql2 {
    CREATE VIEW v3 AS SELECT a FROM t1 UNION SELECT b FROM t1 ORDER BY b;
    SELECT * FROM v3 LIMIT 4;
  }
} {b 2 b 3 b 5 b 6}


finish_test