/ Check-in [24e4cf73]
Login

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

Overview
Comment:Fix for ticket #31: Do not attempt the flattening optimization if the subselect does not contain a FROM clause. Handle the special case where a WHERE clause is constant. (CVS 548)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:24e4cf73d22bb41d26bf3c833f1854a9c90923e8
User & Date: drh 2002-04-30 19:20:28
Context
2002-05-03
00:17
Update the change log prior to releasing version 2.4.10. (CVS 549) check-in: 67838bbc user: drh tags: trunk
2002-04-30
19:20
Fix for ticket #31: Do not attempt the flattening optimization if the subselect does not contain a FROM clause. Handle the special case where a WHERE clause is constant. (CVS 548) check-in: 24e4cf73 user: drh tags: trunk
2002-04-26
09:47
Fix for ticket #28: Export the "sqlite_changes" function in Windows DLLs. (CVS 547) check-in: f7cea463 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to VERSION.

1
2.4.9
|
1
2.4.10

Changes to src/select.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
913
914
915
916
917
918
919


920
921
922
923
924
925
926
...
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
**    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.79 2002/04/23 17:10:18 drh Exp $
*/
#include "sqliteInt.h"

/*
** Allocate a new Select structure and return a pointer to that
** structure.
*/
................................................................................
**   (4)  The subquery is not DISTINCT or the outer query is not a join.
**
**   (5)  The subquery is not DISTINCT or the outer query does not use
**        aggregates.
**
**   (6)  The subquery does not use aggregates or the outer query is not
**        DISTINCT.


**
** 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 return 0.
** If flattening is attempted this routine returns 1.
................................................................................
  assert( pSrc && iFrom>=0 && iFrom<pSrc->nId );
  pSub = pSrc->a[iFrom].pSelect;
  assert( pSub!=0 );
  if( isAgg && subqueryIsAgg ) return 0;
  if( subqueryIsAgg && pSrc->nId>1 ) 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.
  */







|







 







>
>







 







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
...
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
**    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.
*/
................................................................................
**   (4)  The subquery is not DISTINCT or the outer query is not a join.
**
**   (5)  The subquery is not DISTINCT or the outer query does not use
**        aggregates.
**
**   (6)  The subquery does not use aggregates or the outer query is not
**        DISTINCT.
**
**   (7)  The subquery has a FROM clause.
**
** 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 return 0.
** If flattening is attempted this routine returns 1.
................................................................................
  assert( pSrc && iFrom>=0 && iFrom<pSrc->nId );
  pSub = pSrc->a[iFrom].pSelect;
  assert( pSub!=0 );
  if( isAgg && subqueryIsAgg ) return 0;
  if( subqueryIsAgg && pSrc->nId>1 ) 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.
  */

Changes to src/where.c.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
189
190
191
192
193
194
195








196
197
198
199
200
201
202
...
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  Also found here are subroutines
** to generate VDBE code to evaluate expressions.
**
** $Id: where.c,v 1.40 2002/04/02 13:26:11 drh Exp $
*/
#include "sqliteInt.h"

/*
** The query generator uses an array of instances of this structure to
** help it analyze the subexpressions of the WHERE clause.  Each WHERE
** clause subexpression is separated from the others by an AND operator.
................................................................................
    sqliteFree(pWInfo);
    return 0;
  }
  pWInfo->pParse = pParse;
  pWInfo->pTabList = pTabList;
  pWInfo->base = base;
  pWInfo->peakNTab = pWInfo->savedNTab = pParse->nTab;









  /* Split the WHERE clause into as many as 32 separate subexpressions
  ** where each subexpression is separated by an AND operator.  Any additional
  ** subexpressions are attached in the aExpr[32] and will not enter
  ** into the query optimizer computations.  32 is chosen as the cutoff
  ** since that is the number of bits in an integer that we use for an
  ** expression-used mask.  
................................................................................
      sqliteVdbeChangeP3(v, -1, pWInfo->a[i].pIdx->zName, P3_STATIC);
    }
  }

  /* Generate the code to do the search
  */
  loopMask = 0;
  pWInfo->iBreak = sqliteVdbeMakeLabel(v);
  for(i=0; i<pTabList->nId; i++){
    int j, k;
    int idx = aOrder[i];
    Index *pIdx;
    WhereLevel *pLevel = &pWInfo->a[i];

    pIdx = pLevel->pIdx;







|







 







>
>
>
>
>
>
>
>







 







<







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
...
426
427
428
429
430
431
432

433
434
435
436
437
438
439
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  Also found here are subroutines
** to generate VDBE code to evaluate expressions.
**
** $Id: where.c,v 1.41 2002/04/30 19:20:29 drh Exp $
*/
#include "sqliteInt.h"

/*
** The query generator uses an array of instances of this structure to
** help it analyze the subexpressions of the WHERE clause.  Each WHERE
** clause subexpression is separated from the others by an AND operator.
................................................................................
    sqliteFree(pWInfo);
    return 0;
  }
  pWInfo->pParse = pParse;
  pWInfo->pTabList = pTabList;
  pWInfo->base = base;
  pWInfo->peakNTab = pWInfo->savedNTab = pParse->nTab;
  pWInfo->iBreak = sqliteVdbeMakeLabel(v);

  /* Special case: a WHERE clause that is constant.  Evaluate the
  ** expression and either jump over all of the code or fall thru.
  */
  if( pWhere && sqliteExprIsConstant(pWhere) ){
    sqliteExprIfFalse(pParse, pWhere, pWInfo->iBreak);
  }

  /* Split the WHERE clause into as many as 32 separate subexpressions
  ** where each subexpression is separated by an AND operator.  Any additional
  ** subexpressions are attached in the aExpr[32] and will not enter
  ** into the query optimizer computations.  32 is chosen as the cutoff
  ** since that is the number of bits in an integer that we use for an
  ** expression-used mask.  
................................................................................
      sqliteVdbeChangeP3(v, -1, pWInfo->a[i].pIdx->zName, P3_STATIC);
    }
  }

  /* Generate the code to do the search
  */
  loopMask = 0;

  for(i=0; i<pTabList->nId; i++){
    int j, k;
    int idx = aOrder[i];
    Index *pIdx;
    WhereLevel *pLevel = &pWInfo->a[i];

    pIdx = pLevel->pIdx;

Changes to test/select6.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
331
332
333
334
335
336
337
338























339
#    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 SELECT statements that contain
# subqueries in their FROM clause.
#
# $Id: select6.test,v 1.8 2002/03/23 00:31:29 drh Exp $

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

do_test select6-1.0 {
  execsql {
    BEGIN;
................................................................................
do_test select6-6.6 {
  execsql {
    SELECT * FROM (
      SELECT x AS 'a' FROM t1 EXCEPT SELECT x*2 AS 'a' FROM t1
    ) ORDER BY a;
  }
} {1 3}
























finish_test







|







 








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

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
#    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 SELECT statements that contain
# subqueries in their FROM clause.
#
# $Id: select6.test,v 1.9 2002/04/30 19:20:29 drh Exp $

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

do_test select6-1.0 {
  execsql {
    BEGIN;
................................................................................
do_test select6-6.6 {
  execsql {
    SELECT * FROM (
      SELECT x AS 'a' FROM t1 EXCEPT SELECT x*2 AS 'a' FROM t1
    ) ORDER BY a;
  }
} {1 3}

# Subselects with no FROM clause
#
do_test select6-7.1 {
  execsql {
    SELECT * FROM (SELECT 1)
  }
} {1}
do_test select6-7.2 {
  execsql {
    SELECT c,b,a,* FROM (SELECT 1 AS 'a', 2 AS 'b', 'abc' AS 'c')
  }
} {abc 2 1 1 2 abc}
do_test select6-7.3 {
  execsql {
    SELECT c,b,a,* FROM (SELECT 1 AS 'a', 2 AS 'b', 'abc' AS 'c' WHERE 0)
  }
} {}
do_test select6-7.4 {
  execsql2 {
    SELECT c,b,a,* FROM (SELECT 1 AS 'a', 2 AS 'b', 'abc' AS 'c' WHERE 1)
  }
} {c abc b 2 a 1 a 1 b 2 c abc}

finish_test

Changes to test/where.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
232
233
234
235
236
237
238
239

























240
#    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 the use of indices in WHERE clases.
#
# $Id: where.test,v 1.5 2001/12/22 14:49:26 drh Exp $

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

# Build some test data
#
do_test where-1.0 {
................................................................................
} {12 89 12 9}
do_test where-3.3 {
  count {
    SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
    WHERE A.w=15 AND B.p=C.w AND B.r=10202-A.y
  }
} {15 86 86 9}


























finish_test







|







 








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

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
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
#    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 the use of indices in WHERE clases.
#
# $Id: where.test,v 1.6 2002/04/30 19:20:29 drh Exp $

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

# Build some test data
#
do_test where-1.0 {
................................................................................
} {12 89 12 9}
do_test where-3.3 {
  count {
    SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
    WHERE A.w=15 AND B.p=C.w AND B.r=10202-A.y
  }
} {15 86 86 9}

# Test to see that the special case of a constant WHERE clause is
# handled.
#
do_test where-4.1 {
  count {
    SELECT * FROM t1 WHERE 0
  }
} {0}
do_test where-4.2 {
  count {
    SELECT * FROM t1 WHERE 1 LIMIT 1
  }
} {1 0 4 0}
do_test where-4.3 {
  execsql {
    SELECT 99 WHERE 0
  }
} {}
do_test where-4.4 {
  execsql {
    SELECT 99 WHERE 1
  }
} {99}


finish_test