/ Check-in [6c2adade]
Login

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

Overview
Comment:Prevent the flattening optimization from transforming a to a query with an illegal ORDER BY clause. (CVS 5372)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 6c2adade34fdbe344cf73f6bac951e0c37dd003b
User & Date: danielk1977 2008-07-08 17:43:57
Context
2008-07-08
18:05
Improved testing of the min/max optimization. (CVS 5373) check-in: fa07c360 user: drh tags: trunk
17:43
Prevent the flattening optimization from transforming a to a query with an illegal ORDER BY clause. (CVS 5372) check-in: 6c2adade user: danielk1977 tags: trunk
17:13
Test another corruption case in btree.c. And an IO error related scenario. (CVS 5371) check-in: d74fbb81 user: danielk1977 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
....
3069
3070
3071
3072
3073
3074
3075




3076
3077
3078
3079
3080
3081
3082
....
3106
3107
3108
3109
3110
3111
3112

3113
3114
3115
3116
3117
3118
3119
....
3181
3182
3183
3184
3185
3186
3187











3188
3189
3190
3191
3192
3193
3194
....
3224
3225
3226
3227
3228
3229
3230
3231
3232
3233
3234
3235
3236
3237
3238
**    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.447 2008/07/02 16:10:46 danielk1977 Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
**          * is not itself part of a compound select,
**          * is not an aggregate or DISTINCT query, and
**          * has no other tables or sub-selects in the FROM clause.
**
**        The parent and sub-query may contain WHERE clauses. Subject to
**        rules (11), (13) and (14), they may also contain ORDER BY,
**        LIMIT and OFFSET clauses.




**
** In this routine, the "p" parameter is a pointer to the outer query.
** The subquery is p->pSrc->a[iFrom].  isAgg is true if the outer query
** uses aggregates and subqueryIsAgg is true if the subquery uses aggregates.
**
** If flattening is not attempted, this routine is a no-op and returns 0.
** If flattening is attempted this routine returns 1.
................................................................................

  /* Check to see if flattening is permitted.  Return 0 if not.
  */
  if( p==0 ) return 0;
  pSrc = p->pSrc;
  assert( pSrc && iFrom>=0 && iFrom<pSrc->nSrc );
  pSubitem = &pSrc->a[iFrom];

  pSub = pSubitem->pSelect;
  assert( pSub!=0 );
  if( isAgg && subqueryIsAgg ) return 0;                 /* Restriction (1)  */
  if( subqueryIsAgg && pSrc->nSrc>1 ) return 0;          /* Restriction (2)  */
  pSubSrc = pSub->pSrc;
  assert( pSubSrc );
  /* Prior to version 3.1.2, when LIMIT and OFFSET had to be simple constants,
................................................................................
    }
    for(pSub1=pSub; pSub1; pSub1=pSub1->pPrior){
      if( pSub1->isAgg || pSub1->isDistinct 
       || (pSub1->pPrior && pSub1->op!=TK_ALL) ){
        return 0;
      }
    }











  }

  pParse->zAuthContext = pSubitem->zName;
  sqlite3AuthCheck(pParse, SQLITE_SELECT, 0, 0, 0);
  pParse->zAuthContext = zSavedAuthContext;

  /* If the sub-query is a compound SELECT statement, then it must be
................................................................................
    pNew->pRightmost = 0;
  }

  /* If we reach this point, it means flattening is permitted for the
  ** iFrom-th entry of the FROM clause in the outer query.
  */
  pSub = pSub1 = pSubitem->pSelect;
  iParent = pSubitem->iCursor;
  for(pParent=p; pParent; pParent=pParent->pPrior, pSub=pSub->pPrior){
    int nSubSrc = pSubSrc->nSrc;
    int jointype = 0;
    pSubSrc = pSub->pSrc;
    pSrc = pParent->pSrc;

    /* Move all of the FROM elements of the subquery into the







|







 







>
>
>
>







 







>







 







>
>
>
>
>
>
>
>
>
>
>







 







<







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
3069
3070
3071
3072
3073
3074
3075
3076
3077
3078
3079
3080
3081
3082
3083
3084
3085
3086
....
3110
3111
3112
3113
3114
3115
3116
3117
3118
3119
3120
3121
3122
3123
3124
....
3186
3187
3188
3189
3190
3191
3192
3193
3194
3195
3196
3197
3198
3199
3200
3201
3202
3203
3204
3205
3206
3207
3208
3209
3210
....
3240
3241
3242
3243
3244
3245
3246

3247
3248
3249
3250
3251
3252
3253
**    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.448 2008/07/08 17:43:57 danielk1977 Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
**          * is not itself part of a compound select,
**          * is not an aggregate or DISTINCT query, and
**          * has no other tables or sub-selects in the FROM clause.
**
**        The parent and sub-query may contain WHERE clauses. Subject to
**        rules (11), (13) and (14), they may also contain ORDER BY,
**        LIMIT and OFFSET clauses.
**
**  (18)  If the sub-query is a compound select, then all terms of the
**        ORDER by clause of the parent must be simple references to 
**        columns of the sub-query.
**
** In this routine, the "p" parameter is a pointer to the outer query.
** The subquery is p->pSrc->a[iFrom].  isAgg is true if the outer query
** uses aggregates and subqueryIsAgg is true if the subquery uses aggregates.
**
** If flattening is not attempted, this routine is a no-op and returns 0.
** If flattening is attempted this routine returns 1.
................................................................................

  /* Check to see if flattening is permitted.  Return 0 if not.
  */
  if( p==0 ) return 0;
  pSrc = p->pSrc;
  assert( pSrc && iFrom>=0 && iFrom<pSrc->nSrc );
  pSubitem = &pSrc->a[iFrom];
  iParent = pSubitem->iCursor;
  pSub = pSubitem->pSelect;
  assert( pSub!=0 );
  if( isAgg && subqueryIsAgg ) return 0;                 /* Restriction (1)  */
  if( subqueryIsAgg && pSrc->nSrc>1 ) return 0;          /* Restriction (2)  */
  pSubSrc = pSub->pSrc;
  assert( pSubSrc );
  /* Prior to version 3.1.2, when LIMIT and OFFSET had to be simple constants,
................................................................................
    }
    for(pSub1=pSub; pSub1; pSub1=pSub1->pPrior){
      if( pSub1->isAgg || pSub1->isDistinct 
       || (pSub1->pPrior && pSub1->op!=TK_ALL) ){
        return 0;
      }
    }

    /* Restriction 18. */
    if( p->pOrderBy ){
      int ii;
      for(ii=0; ii<p->pOrderBy->nExpr; ii++){
        Expr *pExpr = p->pOrderBy->a[ii].pExpr;
        if( pExpr->op!=TK_COLUMN || pExpr->iTable!=iParent ){ 
          return 0;
        }
      }
    }
  }

  pParse->zAuthContext = pSubitem->zName;
  sqlite3AuthCheck(pParse, SQLITE_SELECT, 0, 0, 0);
  pParse->zAuthContext = zSavedAuthContext;

  /* If the sub-query is a compound SELECT statement, then it must be
................................................................................
    pNew->pRightmost = 0;
  }

  /* If we reach this point, it means flattening is permitted for the
  ** iFrom-th entry of the FROM clause in the outer query.
  */
  pSub = pSub1 = pSubitem->pSelect;

  for(pParent=p; pParent; pParent=pParent->pPrior, pSub=pSub->pPrior){
    int nSubSrc = pSubSrc->nSrc;
    int jointype = 0;
    pSubSrc = pSub->pSrc;
    pSrc = pParent->pSrc;

    /* Move all of the FROM elements of the subquery into the

Changes to test/incrblob_err.test.

5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
...
126
127
128
129
130
131
132
133


134
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# $Id: incrblob_err.test,v 1.11 2008/07/08 12:07:33 danielk1977 Exp $
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable {!incrblob  || !memdebug || !tclvar} {
  finish_test
................................................................................
  # map page for the purposes of seeking to the end of the blob.
  #
  sqlite3 db2 test.db
  set ::blob [db2 incrblob blobs v 1]
  sqlite3_blob_write $::blob [expr 500*1020-20] 12345678900987654321
  close $::blob
}



finish_test







|







 








>
>

5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
...
126
127
128
129
130
131
132
133
134
135
136
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# $Id: incrblob_err.test,v 1.12 2008/07/08 17:43:57 danielk1977 Exp $
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable {!incrblob  || !memdebug || !tclvar} {
  finish_test
................................................................................
  # map page for the purposes of seeking to the end of the blob.
  #
  sqlite3 db2 test.db
  set ::blob [db2 incrblob blobs v 1]
  sqlite3_blob_write $::blob [expr 500*1020-20] 12345678900987654321
  close $::blob
}

db2 close

finish_test

Changes to test/selectB.test.

6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
...
353
354
355
356
357
358
359






360
361
362
363
#    May you do good and not evil.
#    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. 
#
# $Id: selectB.test,v 1.5 2008/07/01 18:26:51 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

proc test_transform {testname sql1 sql2 results} {
  set ::vdbe1 [list]
  set ::vdbe2 [list]
................................................................................
  do_test selectB-$ii.20 {
    execsql {
      SELECT DISTINCT * FROM (
        SELECT DISTINCT (a/10) FROM t1 UNION ALL SELECT DISTINCT(d%2) FROM t2
      )
    }
  } {0 1}






}

finish_test








|







 







>
>
>
>
>
>




6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
...
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
#    May you do good and not evil.
#    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. 
#
# $Id: selectB.test,v 1.6 2008/07/08 17:43:57 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

proc test_transform {testname sql1 sql2 results} {
  set ::vdbe1 [list]
  set ::vdbe2 [list]
................................................................................
  do_test selectB-$ii.20 {
    execsql {
      SELECT DISTINCT * FROM (
        SELECT DISTINCT (a/10) FROM t1 UNION ALL SELECT DISTINCT(d%2) FROM t2
      )
    }
  } {0 1}

  do_test selectB-$ii.21 {
    execsql {
      SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2) ORDER BY a+b
    }
  } {2 4 6 3 6 9 8 10 12 12 15 18 14 16 18 21 24 27}
}

finish_test