/ Check-in [a79786a9]
Login

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

Overview
Comment:Fix a problem with LIMIT and OFFSET clauses on the parent query when optimizing a UNION ALL sub-select. (CVS 5332)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: a79786a961dba8f4ffaddbe55e6467c14b12f7d6
User & Date: danielk1977 2008-07-01 14:39:35
References
2013-01-28
19:34 New ticket [db4d9679] LIMIT does not work with nested views containing UNION ALL. artifact: 8c39a3c9 user: drh
Context
2008-07-01
16:05
Fix a memory leak to do with the recent UNION ALL sub-select optimization. (CVS 5333) check-in: 6ee71f4d user: danielk1977 tags: trunk
14:39
Fix a problem with LIMIT and OFFSET clauses on the parent query when optimizing a UNION ALL sub-select. (CVS 5332) check-in: a79786a9 user: danielk1977 tags: trunk
14:09
Optimize sub-selects and views that use UNION ALL. This optimization isn't very well tested yet. (CVS 5331) check-in: 3ef468e7 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
....
3187
3188
3189
3190
3191
3192
3193


3194
3195
3196
3197

3198
3199
3200
3201
3202
3203


3204
3205
3206
3207
3208
3209
3210
**    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.441 2008/07/01 14:09:14 danielk1977 Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
  ** OFFSET clauses and joins them to the left-hand-side of the original
  ** using UNION ALL operators. In this case N is the number of simple
  ** select statements in the compound sub-query.
  */
  for(pSub=pSub->pPrior; pSub; pSub=pSub->pPrior){
    Select *pNew;
    ExprList *pOrderBy = p->pOrderBy;


    Select *pPrior = p->pPrior;
    p->pOrderBy = 0;
    p->pSrc = 0;
    p->pPrior = 0;

    pNew = sqlite3SelectDup(db, p);
    pNew->pPrior = pPrior;
    p->pPrior = pNew;
    p->pOrderBy = pOrderBy;
    p->op = TK_ALL;
    p->pSrc = pSrc;


    p->pRightmost = 0;
    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.
  */







|







 







>
>




>






>
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
3187
3188
3189
3190
3191
3192
3193
3194
3195
3196
3197
3198
3199
3200
3201
3202
3203
3204
3205
3206
3207
3208
3209
3210
3211
3212
3213
3214
3215
**    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.442 2008/07/01 14:39:35 danielk1977 Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
  ** OFFSET clauses and joins them to the left-hand-side of the original
  ** using UNION ALL operators. In this case N is the number of simple
  ** select statements in the compound sub-query.
  */
  for(pSub=pSub->pPrior; pSub; pSub=pSub->pPrior){
    Select *pNew;
    ExprList *pOrderBy = p->pOrderBy;
    Expr *pLimit = p->pLimit;
    Expr *pOffset = p->pOffset;
    Select *pPrior = p->pPrior;
    p->pOrderBy = 0;
    p->pSrc = 0;
    p->pPrior = 0;
    p->pLimit = 0;
    pNew = sqlite3SelectDup(db, p);
    pNew->pPrior = pPrior;
    p->pPrior = pNew;
    p->pOrderBy = pOrderBy;
    p->op = TK_ALL;
    p->pSrc = pSrc;
    p->pLimit = pLimit;
    p->pOffset = pOffset;
    p->pRightmost = 0;
    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.
  */

Changes to test/select9.test.

6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
...
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
...
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
#    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: select9.test,v 1.3 2008/06/30 07:53:10 danielk1977 Exp $

# The tests in this file are focused on test compound SELECT statements 
# that have any or all of an ORDER BY, LIMIT or OFFSET clauses. As of
# version 3.6.0, SQLite contains code to use SQL indexes where possible 
# to optimize such statements.
#

................................................................................
# can use indexes to run without doing a in-memory sort operation.
# This block of tests (select9-3.*) is used to check if the same 
# is possible with:
#
#     CREATE VIEW v1 AS SELECT a FROM t1 UNION ALL SELECT d FROM t2
#     SELECT a FROM v1 ORDER BY 1
#
# Currently it is not.
#
do_test select9-3.1 {
  cksort { SELECT a FROM t1 ORDER BY 1 }
} {1 2 3 4 5 6 7 8 9 10 sort}
do_test select9-3.2 {
  execsql { CREATE INDEX i1 ON t1(a) }
  cksort { SELECT a FROM t1 ORDER BY 1 }
................................................................................
do_test select9-3.4 {
  execsql { CREATE INDEX i2 ON t2(d) }
  cksort { SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 5 }
} {1 1 2 2 3 nosort}
do_test select9-3.5 {
  execsql { CREATE VIEW v1 AS SELECT a FROM t1 UNION ALL SELECT d FROM t2 }
  cksort { SELECT a FROM v1 ORDER BY 1 LIMIT 5 }
} {1 1 2 2 3 sort}
do_test select9-3.X {
  execsql {
    DROP INDEX i1;
    DROP INDEX i2;
    DROP VIEW v1;
  }
} {}







|







 







|







 







|







6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
...
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
...
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
#    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: select9.test,v 1.4 2008/07/01 14:39:35 danielk1977 Exp $

# The tests in this file are focused on test compound SELECT statements 
# that have any or all of an ORDER BY, LIMIT or OFFSET clauses. As of
# version 3.6.0, SQLite contains code to use SQL indexes where possible 
# to optimize such statements.
#

................................................................................
# can use indexes to run without doing a in-memory sort operation.
# This block of tests (select9-3.*) is used to check if the same 
# is possible with:
#
#     CREATE VIEW v1 AS SELECT a FROM t1 UNION ALL SELECT d FROM t2
#     SELECT a FROM v1 ORDER BY 1
#
# It turns out that it is.
#
do_test select9-3.1 {
  cksort { SELECT a FROM t1 ORDER BY 1 }
} {1 2 3 4 5 6 7 8 9 10 sort}
do_test select9-3.2 {
  execsql { CREATE INDEX i1 ON t1(a) }
  cksort { SELECT a FROM t1 ORDER BY 1 }
................................................................................
do_test select9-3.4 {
  execsql { CREATE INDEX i2 ON t2(d) }
  cksort { SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 5 }
} {1 1 2 2 3 nosort}
do_test select9-3.5 {
  execsql { CREATE VIEW v1 AS SELECT a FROM t1 UNION ALL SELECT d FROM t2 }
  cksort { SELECT a FROM v1 ORDER BY 1 LIMIT 5 }
} {1 1 2 2 3 nosort}
do_test select9-3.X {
  execsql {
    DROP INDEX i1;
    DROP INDEX i2;
    DROP VIEW v1;
  }
} {}

Changes to test/selectB.test.

6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
..
88
89
90
91
92
93
94













95
96
97
98
#    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.1 2008/07/01 14:09:14 danielk1977 Exp $

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

proc test_transform {testname sql1 sql2 results} {
  set ::vdbe1 [list]
  set ::vdbe2 [list]
................................................................................
  WHERE a>10 ORDER BY a
} {
  SELECT a FROM t1 WHERE a>10
    UNION ALL 
  SELECT d FROM t2 WHERE d>12 AND d>10
  ORDER BY a
} {14 21}















finish_test








|







 







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




6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
..
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
#    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.2 2008/07/01 14:39:35 danielk1977 Exp $

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

proc test_transform {testname sql1 sql2 results} {
  set ::vdbe1 [list]
  set ::vdbe2 [list]
................................................................................
  WHERE a>10 ORDER BY a
} {
  SELECT a FROM t1 WHERE a>10
    UNION ALL 
  SELECT d FROM t2 WHERE d>12 AND d>10
  ORDER BY a
} {14 21}

test_transform selectB-1.7 {
  SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1 LIMIT 2
} {
  SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 2
} {2 3}

test_transform selectB-1.8 {
  SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1 
  LIMIT 2 OFFSET 3
} {
  SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 2 OFFSET 3
} {12 14}


finish_test