SQLite

Check-in [d768007473]
Login

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

Overview
Comment:Improvements to the algorithm that determines which SELECT in a sequence of nested SELECT statements that an aggregate function belongs to. This resolves an issue identified by dbsqlfuzz.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: d768007473f4ed40abbdf2c7e501b580b1cc37c1620c7cb90af1f208a8c35145
User & Date: drh 2019-08-31 20:13:30.510
Context
2019-08-31
20:26
Mark new VDBE branches never taken. (check-in: 83c2adffbf user: drh tags: trunk)
20:13
Improvements to the algorithm that determines which SELECT in a sequence of nested SELECT statements that an aggregate function belongs to. This resolves an issue identified by dbsqlfuzz. (check-in: d768007473 user: drh tags: trunk)
17:14
If a DELETE trigger fired by an UPDATE OR REPLACE statement deletes the row being updated, do not attempt to proceed with the original UPDATE operation. Fix for [d6a0fbc1]. (check-in: 4145b3e050 user: dan tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/expr.c.
︙︙
5293
5294
5295
5296
5297
5298
5299
5300



5301
5302
5303
5304
5305
5306
5307
5308
5309
5310
5311
5312
5313
5314
5315
5316

5317
5318
5319
5320
5321
5322
5323
5324
5325

    SrcList *pSrc = p->pSrc;
    int nSrc = pSrc ? pSrc->nSrc : 0;
    for(i=0; i<nSrc; i++){
      if( pExpr->iTable==pSrc->a[i].iCursor ) break;
    }
    if( i<nSrc ){
      p->nThis++;
    }else{



      p->nOther++;
    }
  }
  return WRC_Continue;
}

/*
** Determine if any of the arguments to the pExpr Function reference
** pSrcList.  Return true if they do.  Also return true if the function
** has no arguments or has only constant arguments.  Return false if pExpr
** references columns but not columns of tables found in pSrcList.
*/
int sqlite3FunctionUsesThisSrc(Expr *pExpr, SrcList *pSrcList){
  Walker w;
  struct SrcCount cnt;
  assert( pExpr->op==TK_AGG_FUNCTION );

  w.xExprCallback = exprSrcCount;
  w.xSelectCallback = 0;
  w.u.pSrcCount = &cnt;
  cnt.pSrc = pSrcList;
  cnt.nThis = 0;
  cnt.nOther = 0;
  sqlite3WalkExprList(&w, pExpr->x.pList);
  return cnt.nThis>0 || cnt.nOther==0;
}








|
>
>
>
















>

|








5293
5294
5295
5296
5297
5298
5299
5300
5301
5302
5303
5304
5305
5306
5307
5308
5309
5310
5311
5312
5313
5314
5315
5316
5317
5318
5319
5320
5321
5322
5323
5324
5325
5326
5327
5328
5329

    SrcList *pSrc = p->pSrc;
    int nSrc = pSrc ? pSrc->nSrc : 0;
    for(i=0; i<nSrc; i++){
      if( pExpr->iTable==pSrc->a[i].iCursor ) break;
    }
    if( i<nSrc ){
      p->nThis++;
    }else if( nSrc==0 || pExpr->iTable<pSrc->a[0].iCursor ){
      /* In a well-formed parse tree (no name resolution errors),
      /* TK_COLUMN nodes with smaller Expr.iTable values are in an
      ** outer context.  Those are the only ones to count as "other" */
      p->nOther++;
    }
  }
  return WRC_Continue;
}

/*
** Determine if any of the arguments to the pExpr Function reference
** pSrcList.  Return true if they do.  Also return true if the function
** has no arguments or has only constant arguments.  Return false if pExpr
** references columns but not columns of tables found in pSrcList.
*/
int sqlite3FunctionUsesThisSrc(Expr *pExpr, SrcList *pSrcList){
  Walker w;
  struct SrcCount cnt;
  assert( pExpr->op==TK_AGG_FUNCTION );
  memset(&w, 0, sizeof(w));
  w.xExprCallback = exprSrcCount;
  w.xSelectCallback = sqlite3SelectWalkNoop;
  w.u.pSrcCount = &cnt;
  cnt.pSrc = pSrcList;
  cnt.nThis = 0;
  cnt.nOther = 0;
  sqlite3WalkExprList(&w, pExpr->x.pList);
  return cnt.nThis>0 || cnt.nOther==0;
}
︙︙
Changes to test/aggnested.test. 
1
2
3
4
5
6
7
8

# 2012 August 23
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    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.

|








1
2
3
4
5
6
7
8

# 2012-08-23
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    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.
︙︙
228
229
230
231
232
233
234
235


236


























237

do_test aggnested-3.16 {
  db eval {
    SELECT max(value1), (SELECT sum(value2=value1) FROM t2)
      FROM t1
     GROUP BY id1;
  }
} {12 2 34 4}
 





























finish_test








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


228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265

do_test aggnested-3.16 {
  db eval {
    SELECT max(value1), (SELECT sum(value2=value1) FROM t2)
      FROM t1
     GROUP BY id1;
  }
} {12 2 34 4}

# 2019-08-31
# Problem found by dbsqlfuzz
#
do_execsql_test aggnested-4.1 {
  DROP TABLE IF EXISTS aa;
  DROP TABLE IF EXISTS bb;
  CREATE TABLE aa(x INT);  INSERT INTO aa(x) VALUES(123);
  CREATE TABLE bb(y INT);  INSERT INTO bb(y) VALUES(456);
  SELECT (SELECT sum(x+(SELECT y)) FROM bb) FROM aa;
} {579}
do_execsql_test aggnested-4.2 {
  SELECT (SELECT sum(x+y) FROM bb) FROM aa;
} {579}
do_execsql_test aggnested-4.3 {
  DROP TABLE IF EXISTS tx;
  DROP TABLE IF EXISTS ty;
  CREATE TABLE tx(x INT);
  INSERT INTO tx VALUES(1),(2),(3),(4),(5);
  CREATE TABLE ty(y INT);
  INSERT INTO ty VALUES(91),(92),(93);
  SELECT min((SELECT count(y) FROM ty)) FROM tx;
} {3}
do_execsql_test aggnested-4.4 {
  SELECT max((SELECT a FROM (SELECT count(*) AS a FROM ty) AS s)) FROM tx;
} {3}


 

finish_test