/ Check-in [8f9d1abb]
Login

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

Overview
Comment:Fix errors in in.test. Also add a few tests to selectB.test. (CVS 5337)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 8f9d1abb315a3d4aa3a580fd5cf3ae572cc330f4
User & Date: danielk1977 2008-07-01 18:26:50
Context
2008-07-02
13:13
Call the authorizer callback the same number of times whether or not the query flattening occurs. (CVS 5338) check-in: 8b88b64b user: danielk1977 tags: trunk
2008-07-01
18:26
Fix errors in in.test. Also add a few tests to selectB.test. (CVS 5337) check-in: 8f9d1abb user: danielk1977 tags: trunk
17:39
Fix another memory leak related to UNION ALL and sub-selects. (CVS 5336) check-in: 56109b9a 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
....
3059
3060
3061
3062
3063
3064
3065
3066
3067
3068
3069
3070
3071
3072
3073
3074

3075
3076
3077
3078
3079
3080
3081
3082
....
3764
3765
3766
3767
3768
3769
3770
3771
3772
3773
3774
3775
3776
3777
3778
3779
3780
3781
3782
3783
3784
3785
3786
....
3862
3863
3864
3865
3866
3867
3868









3869
3870
3871
3872
3873
3874
3875
**    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.444 2008/07/01 17:39:27 danielk1977 Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
**        (See ticket #2339)
**
**  (16)  The outer query is not an aggregate or the subquery does
**        not contain ORDER BY.  (Ticket #2942)  This used to not matter
**        until we introduced the group_concat() function.  
**
**  (17)  The sub-query is not a compound select, or it is a UNION ALL 
**        compound without an ORDER BY, LIMIT or OFFSET clause made up
**        entirely of non-aggregate queries, and 
**        the parent query:
**
**          * 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 query may have WHERE, 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.
................................................................................

  /* 
  ** Do not even attempt to generate any code if we have already seen
  ** errors before this routine starts.
  */
  if( pParse->nErr>0 ) goto select_end;

  /* If writing to memory or generating a set
  ** only a single column may be output.
  */
#ifndef SQLITE_OMIT_SUBQUERY
  if( checkForMultiColumnSelectError(pParse, pDest, pEList->nExpr) ){
    goto select_end;
  }
#endif

  /* ORDER BY is ignored for some destinations.
  */
  if( IgnorableOrderby(pDest) ){
    pOrderBy = 0;
  }

  /* Begin generating code.
................................................................................
        sqlite3ErrorMsg(pParse, "too many terms in compound SELECT");
        return 1;
      }
    }
    return multiSelect(pParse, p, pDest, aff);
  }
#endif










  /* If possible, rewrite the query to use GROUP BY instead of DISTINCT.
  ** GROUP BY may use an index, DISTINCT never does.
  */
  if( p->isDistinct && !p->isAgg && !p->pGroupBy ){
    p->pGroupBy = sqlite3ExprListDup(db, p->pEList);
    pGroupBy = p->pGroupBy;







|







 







<
|






|
>
|







 







<
<
<
<
<
<
<
<
<







 







>
>
>
>
>
>
>
>
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
3059
3060
3061
3062
3063
3064
3065

3066
3067
3068
3069
3070
3071
3072
3073
3074
3075
3076
3077
3078
3079
3080
3081
3082
....
3764
3765
3766
3767
3768
3769
3770









3771
3772
3773
3774
3775
3776
3777
....
3853
3854
3855
3856
3857
3858
3859
3860
3861
3862
3863
3864
3865
3866
3867
3868
3869
3870
3871
3872
3873
3874
3875
**    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.445 2008/07/01 18:26:50 danielk1977 Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
**        (See ticket #2339)
**
**  (16)  The outer query is not an aggregate or the subquery does
**        not contain ORDER BY.  (Ticket #2942)  This used to not matter
**        until we introduced the group_concat() function.  
**
**  (17)  The sub-query is not a compound select, or it is a UNION ALL 

**        compound clause made up entirely of non-aggregate queries, and 
**        the parent query:
**
**          * 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.
................................................................................

  /* 
  ** Do not even attempt to generate any code if we have already seen
  ** errors before this routine starts.
  */
  if( pParse->nErr>0 ) goto select_end;










  /* ORDER BY is ignored for some destinations.
  */
  if( IgnorableOrderby(pDest) ){
    pOrderBy = 0;
  }

  /* Begin generating code.
................................................................................
        sqlite3ErrorMsg(pParse, "too many terms in compound SELECT");
        return 1;
      }
    }
    return multiSelect(pParse, p, pDest, aff);
  }
#endif

  /* If writing to memory or generating a set
  ** only a single column may be output.
  */
#ifndef SQLITE_OMIT_SUBQUERY
  if( checkForMultiColumnSelectError(pParse, pDest, pEList->nExpr) ){
    goto select_end;
  }
#endif

  /* If possible, rewrite the query to use GROUP BY instead of DISTINCT.
  ** GROUP BY may use an index, DISTINCT never does.
  */
  if( p->isDistinct && !p->isAgg && !p->pGroupBy ){
    p->pGroupBy = sqlite3ExprListDup(db, p->pEList);
    pGroupBy = p->pGroupBy;

Changes to test/selectB.test.

6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
...
140
141
142
143
144
145
146


































147
148
149
150
151
152
153
...
287
288
289
290
291
292
293
294





295



























296
297
#    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.4 2008/07/01 17:39:28 danielk1977 Exp $

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

proc test_transform {testname sql1 sql2 results} {
  set ::vdbe1 [list]
  set ::vdbe2 [list]
................................................................................
      SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
    ) WHERE a>=10 ORDER BY 1 LIMIT 3
  } {
    SELECT a FROM t1 WHERE a>=10 UNION ALL SELECT d FROM t2 WHERE d>=10
    UNION ALL SELECT c FROM t1 WHERE c>=10
    ORDER BY 1 LIMIT 3
  } {12 12 14}


































}

do_test selectB-3.0 {
  execsql {
    DROP INDEX i1;
    DROP INDEX i2;
  }
................................................................................
    execsql {
      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
      INTERSECT 
      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
      ORDER BY 1
    }
  } {6 9 15 18 24 27}
}

































finish_test








|







 







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







 







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


6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
...
140
141
142
143
144
145
146
147
148
149
150
151
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
...
321
322
323
324
325
326
327
328
329
330
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
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]
................................................................................
      SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
    ) WHERE a>=10 ORDER BY 1 LIMIT 3
  } {
    SELECT a FROM t1 WHERE a>=10 UNION ALL SELECT d FROM t2 WHERE d>=10
    UNION ALL SELECT c FROM t1 WHERE c>=10
    ORDER BY 1 LIMIT 3
  } {12 12 14}

  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;
  }
................................................................................
    execsql {
      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
      INTERSECT 
      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
      ORDER BY 1
    }
  } {6 9 15 18 24 27}

  do_test selectB-$ii.17 {
    execsql {
      SELECT * FROM (
        SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 4
      ) LIMIT 2
    }
  } {2 8}

  do_test selectB-$ii.18 {
    execsql {
      SELECT * FROM (
        SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 4 OFFSET 2
      ) LIMIT 2
    }
  } {14 3}

  do_test selectB-$ii.19 {
    execsql {
      SELECT * FROM (
        SELECT DISTINCT (a/10) FROM t1 UNION ALL SELECT DISTINCT(d%2) FROM t2
      )
    }
  } {0 1 0 1}

  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