/ Check-in [23f90d50]
Login

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

Overview
Comment:Disable the query flattening optimization when the subquery is a compound query with an ORDER BY clause. Ticket #3773 shows why that combination does not work. (CVS 6437)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 23f90d50737a36ebd17152dd4667948ce7049967
User & Date: drh 2009-04-02 16:59:47
Context
2009-04-02
17:22
Fix a couple of harmless nuisance warnings. (CVS 6438) check-in: 53dac0a4 user: drh tags: trunk
16:59
Disable the query flattening optimization when the subquery is a compound query with an ORDER BY clause. Ticket #3773 shows why that combination does not work. (CVS 6437) check-in: 23f90d50 user: drh tags: trunk
14:05
Change the way that the random() SQL function prevents the maximum negative integer so that it is testable. (CVS 6436) check-in: 995f2b9b 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
....
2550
2551
2552
2553
2554
2555
2556






2557
2558
2559
2560
2561
2562
2563
....
2661
2662
2663
2664
2665
2666
2667



2668
2669
2670
2671
2672
2673
2674
**    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.506 2009/03/31 03:41:57 shane Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
**
**  (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.
**
**  (19)  The subquery does not use LIMIT or the outer query does not
**        have a WHERE 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 returns 0.
** If flattening is attempted this routine returns 1.
................................................................................

  /* Restriction 17: If the sub-query is a compound SELECT, then it must
  ** use only the UNION ALL operator. And none of the simple select queries
  ** that make up the compound SELECT are allowed to be aggregate or distinct
  ** queries.
  */
  if( pSub->pPrior ){



    if( isAgg || (p->selFlags & SF_Distinct)!=0 || pSrc->nSrc!=1 ){
      return 0;
    }
    for(pSub1=pSub; pSub1; pSub1=pSub1->pPrior){
      if( (pSub1->selFlags & (SF_Distinct|SF_Aggregate))!=0
       || (pSub1->pPrior && pSub1->op!=TK_ALL) 
       || !pSub1->pSrc || pSub1->pSrc->nSrc!=1







|







 







>
>
>
>
>
>







 







>
>
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
2550
2551
2552
2553
2554
2555
2556
2557
2558
2559
2560
2561
2562
2563
2564
2565
2566
2567
2568
2569
....
2667
2668
2669
2670
2671
2672
2673
2674
2675
2676
2677
2678
2679
2680
2681
2682
2683
**    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.507 2009/04/02 16:59:47 drh Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
**
**  (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.
**
**  (19)  The subquery does not use LIMIT or the outer query does not
**        have a WHERE clause.
**
**  (20)  If the sub-query is a compound select, then it must not use
**        an ORDER BY clause.  Ticket #3773.  We could relax this constraint
**        somewhat by saying that the terms of the ORDER BY clause must
**        appear as unmodified result columns in the outer query.  But
**        have other optimizations in mind to deal with that case.
**
** 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.
................................................................................

  /* Restriction 17: If the sub-query is a compound SELECT, then it must
  ** use only the UNION ALL operator. And none of the simple select queries
  ** that make up the compound SELECT are allowed to be aggregate or distinct
  ** queries.
  */
  if( pSub->pPrior ){
    if( pSub->pOrderBy ){
      return 0;  /* Restriction 20 */
    }
    if( isAgg || (p->selFlags & SF_Distinct)!=0 || pSrc->nSrc!=1 ){
      return 0;
    }
    for(pSub1=pSub; pSub1; pSub1=pSub1->pPrior){
      if( (pSub1->selFlags & (SF_Distinct|SF_Aggregate))!=0
       || (pSub1->pPrior && pSub1->op!=TK_ALL) 
       || !pSub1->pSrc || pSub1->pSrc->nSrc!=1

Changes to test/selectB.test.

6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
...
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
...
152
153
154
155
156
157
158

159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176

177
178
179
180
181
182

183
184
185
186
187
188
189
190
191
192
#    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.9 2008/08/04 03:51:24 danielk1977 Exp $

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

ifcapable !compound {
  finish_test
  return
................................................................................
  
  test_transform selectB-$ii.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}
  
  test_transform selectB-$ii.9 {
    SELECT * FROM (
      SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
    ) 
  } {
    SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
  } {2 8 14 3 12 21 6 12 18}
................................................................................

  test_transform selectB-$ii.12 {
    SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 2)
  } {
    SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 2
  } {2 8}


  test_transform selectB-$ii.13 {
    SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a ASC)
  } {
    SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 ASC
  } {2 3 8 12 14 21}

  test_transform selectB-$ii.14 {
    SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a DESC)
  } {
    SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 DESC
  } {21 14 12 8 3 2}

  test_transform selectB-$ii.14 {
    SELECT * FROM (
      SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a DESC
    ) LIMIT 2 OFFSET 2
  } {
    SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 DESC LIMIT 2 OFFSET 2

  } {12 8}

  test_transform selectB-$ii.15 {
    SELECT * FROM (
      SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY a ASC, e DESC
    )

  } {
    SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY a ASC, e DESC
  } {2 4 3 6 8 10 12 15 14 16 21 24}
}

do_test selectB-3.0 {
  execsql {
    DROP INDEX i1;
    DROP INDEX i2;
  }







|







 







|







 







>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>
|
|
|
|
|
<
>
|
|
|







6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
...
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
...
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183

184
185
186
187
188
189
190
191
192
193
194
#    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.10 2009/04/02 16:59:47 drh Exp $

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

ifcapable !compound {
  finish_test
  return
................................................................................
  
  test_transform selectB-$ii.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}

  test_transform selectB-$ii.9 {
    SELECT * FROM (
      SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
    ) 
  } {
    SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
  } {2 8 14 3 12 21 6 12 18}
................................................................................

  test_transform selectB-$ii.12 {
    SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 2)
  } {
    SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 2
  } {2 8}

  # An ORDER BY in a compound subqueries defeats flattening.  Ticket #3773
  # test_transform selectB-$ii.13 {
  #   SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a ASC)
  # } {
  #   SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 ASC
  # } {2 3 8 12 14 21}
  # 
  # test_transform selectB-$ii.14 {
  #  SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a DESC)
  # } {
  #  SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 DESC
  # } {21 14 12 8 3 2}
  #
  # test_transform selectB-$ii.14 {
  #   SELECT * FROM (
  #     SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a DESC
  #   ) LIMIT 2 OFFSET 2
  # } {
  #   SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 DESC
  #    LIMIT 2 OFFSET 2
  # } {12 8}
  #
  # test_transform selectB-$ii.15 {
  #   SELECT * FROM (
  #     SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY a ASC, e DESC

  #  )
  # } {
  #   SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY a ASC, e DESC
  # } {2 4 3 6 8 10 12 15 14 16 21 24}
}

do_test selectB-3.0 {
  execsql {
    DROP INDEX i1;
    DROP INDEX i2;
  }

Added test/tkt3773.test.













































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
# 2009 April 2
#
# 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.
#
#***********************************************************************
#
# Ticket #3773:  Be careful not to over-optimize when a compound
# subquery contains an ORDER BY clause.
#
#
# $Id: tkt3773.test,v 1.1 2009/04/02 16:59:47 drh Exp $

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

do_test tkt3773-1.1 {
  db eval {
    CREATE TABLE t1(a,b);
    INSERT INTO t1 VALUES(2,1);
    INSERT INTO t1 VALUES(33,3);
    CREATE TABLE t2(x,y);
    INSERT INTO t2 VALUES(123,2);
    INSERT INTO t2 VALUES(4,4);
    SELECT a FROM (
      SELECT a, b FROM t1
      UNION ALL
      SELECT x, y FROM t2
      ORDER BY 2
    );
  }
} {2 123 33 4}

finish_test