/ Check-in [1c33829c]
Login

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

Overview
Comment:Fix a bug in "flattening" optimization. Occured if the parent of the flattened sub-query is also the parent of a sub-query that uses a compound op (i.e. UNION, INTERSECT etc.). (CVS 3994)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 1c33829c9ebcf1ff1bd21b161c73a642471b613a
User & Date: danielk1977 2007-05-14 15:49:44
Context
2007-05-14
16:50
Fix a problem with ORDER BY and compound SELECT queries. (CVS 3995) check-in: af76928f user: danielk1977 tags: trunk
15:49
Fix a bug in "flattening" optimization. Occured if the parent of the flattened sub-query is also the parent of a sub-query that uses a compound op (i.e. UNION, INTERSECT etc.). (CVS 3994) check-in: 1c33829c user: danielk1977 tags: trunk
14:05
Prevent sub-queries with "LIMIT 0" from leaving an extra value on the vdbe stack. Also updates to fuzz.test. (CVS 3993) check-in: b1d1b16e 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
....
2075
2076
2077
2078
2079
2080
2081

2082
2083
2084
2085
2086
2087
2088
**    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.346 2007/05/14 14:05:00 danielk1977 Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
static void substSelect(Select *p, int iTable, ExprList *pEList){
  if( !p ) return;
  substExprList(p->pEList, iTable, pEList);
  substExprList(p->pGroupBy, iTable, pEList);
  substExprList(p->pOrderBy, iTable, pEList);
  substExpr(p->pHaving, iTable, pEList);
  substExpr(p->pWhere, iTable, pEList);

}
#endif /* !defined(SQLITE_OMIT_VIEW) */

#ifndef SQLITE_OMIT_VIEW
/*
** This routine attempts to flatten subqueries in order to speed
** execution.  It returns 1 if it makes changes and 0 if no flattening







|







 







>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
2075
2076
2077
2078
2079
2080
2081
2082
2083
2084
2085
2086
2087
2088
2089
**    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.347 2007/05/14 15:49:44 danielk1977 Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
static void substSelect(Select *p, int iTable, ExprList *pEList){
  if( !p ) return;
  substExprList(p->pEList, iTable, pEList);
  substExprList(p->pGroupBy, iTable, pEList);
  substExprList(p->pOrderBy, iTable, pEList);
  substExpr(p->pHaving, iTable, pEList);
  substExpr(p->pWhere, iTable, pEList);
  substSelect(p->pPrior, iTable, pEList);
}
#endif /* !defined(SQLITE_OMIT_VIEW) */

#ifndef SQLITE_OMIT_VIEW
/*
** This routine attempts to flatten subqueries in order to speed
** execution.  It returns 1 if it makes changes and 0 if no flattening

Changes to test/fuzz.test.

15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
...
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
...
193
194
195
196
197
198
199
200





201
202
203
204
205
206
207
...
211
212
213
214
215
216
217
218


219
220
221
222
223
224
225
...
280
281
282
283
284
285
286
287

288
289
290
291
292
293



294
295
296
297
298
299
300
...
447
448
449
450
451
452
453























454
455
456
457
458
459
460
#
# The tests in this file are really about testing fuzzily generated
# SQL parse-trees. The majority of the fuzzily generated SQL is 
# valid as far as the parser is concerned. 
#
# The most complicated trees are for SELECT statements.
#
# $Id: fuzz.test,v 1.8 2007/05/14 14:05:00 danielk1977 Exp $

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

set ::REPEATS 20
# set ::REPEATS 5000

proc fuzz {TemplateList} {
  set n [llength $TemplateList]
  set i [expr {int(rand()*$n)}]
  set r [uplevel 1 subst -novar [list [lindex $TemplateList $i]]]

  string map {"\n" " "} $r
................................................................................
      {substr([Expr],[Expr],[Expr])}                                \
      {CASE WHEN [Expr $c] THEN [Expr $c] ELSE [Expr $c] END}       \
      {[Literal]} {[Literal]} {[Literal]}                           \
      {[Literal]} {[Literal]} {[Literal]}                           \
      {[Literal]} {[Literal]} {[Literal]}                           \
      {[Literal]} {[Literal]} {[Literal]}
  }
  if {$::SelectDepth < 10} {
    lappend TemplateList \
      {([Select 1])}                       \
      {[Expr $c] IN ([Select 1])}          \
      {[Expr $c] NOT IN ([Select 1])}      \
      {EXISTS ([Select 1])}                \
  } 
  set res [fuzz $TemplateList]
................................................................................
          {[SelectKw] * FROM ([Select])}                               \
          {[SelectKw] * FROM [Table]}                                  \
          {[SelectKw] * FROM [Table] WHERE [Expr $::ColumnList]}       \
          {
             [SelectKw] * 
             FROM [Table],[Table] AS t2 
             WHERE [Expr $::ColumnList] 
          }                                                            \





    }
  } 

  fuzz $TemplateList
}

# Return a SELECT statement.
................................................................................
#
proc Select {{nMulti 0}} {
  set TemplateList {
    {[SimpleSelect $nMulti]} {[SimpleSelect $nMulti]} {[SimpleSelect $nMulti]} 
    {[SimpleSelect $nMulti]} {[SimpleSelect $nMulti]} {[SimpleSelect $nMulti]} 
    {[SimpleSelect $nMulti]} {[SimpleSelect $nMulti]} {[SimpleSelect $nMulti]} 
    {[SimpleSelect $nMulti]} {[SimpleSelect $nMulti]} {[SimpleSelect $nMulti]} 
    {[SimpleSelect $nMulti] ORDER BY [Expr]}


    {[SimpleSelect $nMulti] ORDER BY [Expr] LIMIT [Expr] OFFSET [Expr]}
  }

  if {$::SelectDepth < 4} {
    if {$nMulti == 0} {
      set nMulti [expr {(rand()*2)+1}]
    }
................................................................................
}

# Return an identifier. This just chooses randomly from a fixed set
# of strings.
proc Identifier {} {
  set TemplateList {
    This just chooses randomly a fixed 
    We would also thank the developers for their analysis Samba

  }

  fuzz $TemplateList
}

proc Check {} {



  set sd $::SelectDepth 
  set ::SelectDepth 500
  set TemplateList {
    {}
    {CHECK ([Expr])}
  }
  set res [fuzz $TemplateList]
................................................................................
  #
  execsql {
    SELECT 'A' FROM (SELECT 'B') ORDER BY EXISTS (
      SELECT 'C' FROM (SELECT 'D' LIMIT 0)
    )
  }
} {A}
























#----------------------------------------------------------------
# Test some fuzzily generated expressions.
#
do_fuzzy_test fuzz-2 -template  { SELECT [Expr] }

do_test fuzz-3.1 {







|




|
|







 







|







 







|
>
>
>
>
>







 







|
>
>







 







|
>

<




>
>
>







 







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







15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
...
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
...
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
...
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
...
287
288
289
290
291
292
293
294
295
296

297
298
299
300
301
302
303
304
305
306
307
308
309
310
...
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
#
# The tests in this file are really about testing fuzzily generated
# SQL parse-trees. The majority of the fuzzily generated SQL is 
# valid as far as the parser is concerned. 
#
# The most complicated trees are for SELECT statements.
#
# $Id: fuzz.test,v 1.9 2007/05/14 15:49:44 danielk1977 Exp $

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

# set ::REPEATS 20
set ::REPEATS 5000

proc fuzz {TemplateList} {
  set n [llength $TemplateList]
  set i [expr {int(rand()*$n)}]
  set r [uplevel 1 subst -novar [list [lindex $TemplateList $i]]]

  string map {"\n" " "} $r
................................................................................
      {substr([Expr],[Expr],[Expr])}                                \
      {CASE WHEN [Expr $c] THEN [Expr $c] ELSE [Expr $c] END}       \
      {[Literal]} {[Literal]} {[Literal]}                           \
      {[Literal]} {[Literal]} {[Literal]}                           \
      {[Literal]} {[Literal]} {[Literal]}                           \
      {[Literal]} {[Literal]} {[Literal]}
  }
  if {$::SelectDepth < 4} {
    lappend TemplateList \
      {([Select 1])}                       \
      {[Expr $c] IN ([Select 1])}          \
      {[Expr $c] NOT IN ([Select 1])}      \
      {EXISTS ([Select 1])}                \
  } 
  set res [fuzz $TemplateList]
................................................................................
          {[SelectKw] * FROM ([Select])}                               \
          {[SelectKw] * FROM [Table]}                                  \
          {[SelectKw] * FROM [Table] WHERE [Expr $::ColumnList]}       \
          {
             [SelectKw] * 
             FROM [Table],[Table] AS t2 
             WHERE [Expr $::ColumnList] 
          } {
             [SelectKw] * 
             FROM [Table] LEFT OUTER JOIN [Table] AS t2 
             ON [Expr $::ColumnList]
             WHERE [Expr $::ColumnList] 
          }
    }
  } 

  fuzz $TemplateList
}

# Return a SELECT statement.
................................................................................
#
proc Select {{nMulti 0}} {
  set TemplateList {
    {[SimpleSelect $nMulti]} {[SimpleSelect $nMulti]} {[SimpleSelect $nMulti]} 
    {[SimpleSelect $nMulti]} {[SimpleSelect $nMulti]} {[SimpleSelect $nMulti]} 
    {[SimpleSelect $nMulti]} {[SimpleSelect $nMulti]} {[SimpleSelect $nMulti]} 
    {[SimpleSelect $nMulti]} {[SimpleSelect $nMulti]} {[SimpleSelect $nMulti]} 
    {[SimpleSelect $nMulti] ORDER BY [Expr] DESC}
    {[SimpleSelect $nMulti] ORDER BY [Expr] ASC}
    {[SimpleSelect $nMulti] ORDER BY [Expr] ASC, [Expr] DESC}
    {[SimpleSelect $nMulti] ORDER BY [Expr] LIMIT [Expr] OFFSET [Expr]}
  }

  if {$::SelectDepth < 4} {
    if {$nMulti == 0} {
      set nMulti [expr {(rand()*2)+1}]
    }
................................................................................
}

# Return an identifier. This just chooses randomly from a fixed set
# of strings.
proc Identifier {} {
  set TemplateList {
    This just chooses randomly a fixed 
    We would also thank the developers 
    for their analysis Samba
  }

  fuzz $TemplateList
}

proc Check {} {
  # Use a large value for $::SelectDepth, because sub-selects are
  # not allowed in expressions used by CHECK constraints.
  #
  set sd $::SelectDepth 
  set ::SelectDepth 500
  set TemplateList {
    {}
    {CHECK ([Expr])}
  }
  set res [fuzz $TemplateList]
................................................................................
  #
  execsql {
    SELECT 'A' FROM (SELECT 'B') ORDER BY EXISTS (
      SELECT 'C' FROM (SELECT 'D' LIMIT 0)
    )
  }
} {A}

do_test fuzz-1.12.1 {
  # Create a table with a single row.
  execsql {
    CREATE TABLE abc(b);
    INSERT INTO abc VALUES('ABCDE');
  }

  # The following query was crashing. The later subquery (in the FROM)
  # clause was flattened into the parent, but the code was not repairng
  # the "b" reference in the other sub-query. When the query was executed,
  # that "b" refered to a non-existant vdbe table-cursor.
  #
  execsql {
    SELECT 1 IN ( SELECT b UNION SELECT 1 ) FROM (SELECT b FROM abc);
  }
} {1}
do_test fuzz-1.12.2 {
  # Clean up after the previous query.
  execsql {
    DROP TABLE abc;
  }
} {}

#----------------------------------------------------------------
# Test some fuzzily generated expressions.
#
do_fuzzy_test fuzz-2 -template  { SELECT [Expr] }

do_test fuzz-3.1 {