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 |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
1c33829c9ebcf1ff1bd21b161c73a642 |
User & Date: | danielk1977 2007-05-14 15:49:44.000 |
Context
2007-05-14
| ||
16:50 | Fix a problem with ORDER BY and compound SELECT queries. (CVS 3995) (check-in: af76928fc5 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: 1c33829c9e 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: b1d1b16e98 user: danielk1977 tags: trunk) | |
Changes
Changes to src/select.c.
︙ | ︙ | |||
8 9 10 11 12 13 14 | ** 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. ** | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | ** 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. |
︙ | ︙ | |||
2075 2076 2077 2078 2079 2080 2081 2082 2083 2084 2085 2086 2087 2088 | 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 | > | 2075 2076 2077 2078 2079 2080 2081 2082 2083 2084 2085 2086 2087 2088 2089 | 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 | # # 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. # | | | | | 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | # # 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 |
︙ | ︙ | |||
108 109 110 111 112 113 114 | {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]} } | | | 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 | {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] |
︙ | ︙ | |||
193 194 195 196 197 198 199 | {[SelectKw] * FROM ([Select])} \ {[SelectKw] * FROM [Table]} \ {[SelectKw] * FROM [Table] WHERE [Expr $::ColumnList]} \ { [SelectKw] * FROM [Table],[Table] AS t2 WHERE [Expr $::ColumnList] | | > > > > > > > | | 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 | {[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. # # If boolean parameter $isExpr is set to true, make sure the # returned SELECT statement returns a single column of data. # 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}] } |
︙ | ︙ | |||
280 281 282 283 284 285 286 | } # Return an identifier. This just chooses randomly from a fixed set # of strings. proc Identifier {} { set TemplateList { This just chooses randomly a fixed | | > < > > > | 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 | } # 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] |
︙ | ︙ | |||
447 448 449 450 451 452 453 454 455 456 457 458 459 460 | # 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 { | > > > > > > > > > > > > > > > > > > > > > > > | 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 | # 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 { |
︙ | ︙ |