Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Optimize the degenerate case of a FROM clause table name enclosed all by itself inside parentheses. Generate code as if the parentheses did not exist, rather than the old behavior of manifesting the parenthesized table into a transient table. Also, tag every FROM-clause SELECT subquery that is generated by a parenthesized FROM-clause expression using the SF_NestedFrom flag. The new SF_NestedFrom flag is not yet used for anything. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
7fecced466d86a66b0b751c5b5608141 |
User & Date: | drh 2012-12-18 19:36:11.944 |
References
2013-04-30
| ||
13:26 | • New ticket [28c6e830f2] Segfault while parsing/compiling an SQL statement. (artifact: a81f8ab6a7 user: drh) | |
Context
2012-12-19
| ||
16:58 | Fix the segfault problem of ticket [a7b7803e8d1e869] which involved the use of "AS" named result columns as logical terms of the WHERE clause. (check-in: b3c9e8f812 user: drh tags: trunk) | |
15:53 | Proposed fix for ticket [a7b7803e8d1e8699cd8a]. (check-in: 3d0e00c731 user: drh tags: tkt-a7b7803e) | |
02:36 | Better resolution of table and column names in joins where some of the terms of the FROM clause are parenthesized. (check-in: 7344e791b9 user: drh tags: name-resolution-fix) | |
2012-12-18
| ||
19:36 | Optimize the degenerate case of a FROM clause table name enclosed all by itself inside parentheses. Generate code as if the parentheses did not exist, rather than the old behavior of manifesting the parenthesized table into a transient table. Also, tag every FROM-clause SELECT subquery that is generated by a parenthesized FROM-clause expression using the SF_NestedFrom flag. The new SF_NestedFrom flag is not yet used for anything. (check-in: 7fecced466 user: drh tags: trunk) | |
16:07 | Resolve names in FROM-clause subqueries prior to resolving names in the result set expressions of a SELECT statement. (check-in: 9b67c633d9 user: drh tags: trunk) | |
Changes
Changes to src/parse.y.
︙ | ︙ | |||
431 432 433 434 435 436 437 | groupby_opt(P) having_opt(Q) orderby_opt(Z) limit_opt(L). { A = sqlite3SelectNew(pParse,W,X,Y,P,Q,Z,D,L.pLimit,L.pOffset); } // The "distinct" nonterminal is true (1) if the DISTINCT keyword is // present and false (0) if it is not. // | | | | 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 | groupby_opt(P) having_opt(Q) orderby_opt(Z) limit_opt(L). { A = sqlite3SelectNew(pParse,W,X,Y,P,Q,Z,D,L.pLimit,L.pOffset); } // The "distinct" nonterminal is true (1) if the DISTINCT keyword is // present and false (0) if it is not. // %type distinct {u16} distinct(A) ::= DISTINCT. {A = SF_Distinct;} distinct(A) ::= ALL. {A = 0;} distinct(A) ::= . {A = 0;} // selcollist is a list of expressions that are to become the return // values of the SELECT statement. The "*" in statements like // "SELECT * FROM ..." is encoded as a special expression with an // opcode of TK_ALL. |
︙ | ︙ | |||
509 510 511 512 513 514 515 516 517 518 | as(Z) on_opt(N) using_opt(U). { A = sqlite3SrcListAppendFromTerm(pParse,X,0,0,&Z,S,N,U); } seltablist(A) ::= stl_prefix(X) LP seltablist(F) RP as(Z) on_opt(N) using_opt(U). { if( X==0 && Z.n==0 && N==0 && U==0 ){ A = F; }else{ Select *pSubquery; sqlite3SrcListShiftJoinType(F); | > > > > > > > > > > | | 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 | as(Z) on_opt(N) using_opt(U). { A = sqlite3SrcListAppendFromTerm(pParse,X,0,0,&Z,S,N,U); } seltablist(A) ::= stl_prefix(X) LP seltablist(F) RP as(Z) on_opt(N) using_opt(U). { if( X==0 && Z.n==0 && N==0 && U==0 ){ A = F; }else if( F->nSrc==1 ){ A = sqlite3SrcListAppendFromTerm(pParse,X,0,0,&Z,0,N,U); if( A ){ struct SrcList_item *pNew = &A->a[A->nSrc-1]; struct SrcList_item *pOld = F->a; pNew->zName = pOld->zName; pNew->zDatabase = pOld->zDatabase; pOld->zName = pOld->zDatabase = 0; } sqlite3SrcListDelete(pParse->db, F); }else{ Select *pSubquery; sqlite3SrcListShiftJoinType(F); pSubquery = sqlite3SelectNew(pParse,0,F,0,0,0,0,SF_NestedFrom,0,0); A = sqlite3SrcListAppendFromTerm(pParse,X,0,0,&Z,pSubquery,N,U); } } %endif SQLITE_OMIT_SUBQUERY %type dbnm {Token} dbnm(A) ::= . {A.z=0; A.n=0;} |
︙ | ︙ |
Changes to src/select.c.
︙ | ︙ | |||
51 52 53 54 55 56 57 | Parse *pParse, /* Parsing context */ ExprList *pEList, /* which columns to include in the result */ SrcList *pSrc, /* the FROM clause -- which tables to scan */ Expr *pWhere, /* the WHERE clause */ ExprList *pGroupBy, /* the GROUP BY clause */ Expr *pHaving, /* the HAVING clause */ ExprList *pOrderBy, /* the ORDER BY clause */ | | | 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 | Parse *pParse, /* Parsing context */ ExprList *pEList, /* which columns to include in the result */ SrcList *pSrc, /* the FROM clause -- which tables to scan */ Expr *pWhere, /* the WHERE clause */ ExprList *pGroupBy, /* the GROUP BY clause */ Expr *pHaving, /* the HAVING clause */ ExprList *pOrderBy, /* the ORDER BY clause */ u16 selFlags, /* Flag parameters, such as SF_Distinct */ Expr *pLimit, /* LIMIT value. NULL means not used */ Expr *pOffset /* OFFSET value. NULL means no offset */ ){ Select *pNew; Select standin; sqlite3 *db = pParse->db; pNew = sqlite3DbMallocZero(db, sizeof(*pNew) ); |
︙ | ︙ | |||
75 76 77 78 79 80 81 | pNew->pEList = pEList; if( pSrc==0 ) pSrc = sqlite3DbMallocZero(db, sizeof(*pSrc)); pNew->pSrc = pSrc; pNew->pWhere = pWhere; pNew->pGroupBy = pGroupBy; pNew->pHaving = pHaving; pNew->pOrderBy = pOrderBy; | | | 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 | pNew->pEList = pEList; if( pSrc==0 ) pSrc = sqlite3DbMallocZero(db, sizeof(*pSrc)); pNew->pSrc = pSrc; pNew->pWhere = pWhere; pNew->pGroupBy = pGroupBy; pNew->pHaving = pHaving; pNew->pOrderBy = pOrderBy; pNew->selFlags = selFlags; pNew->op = TK_SELECT; pNew->pLimit = pLimit; pNew->pOffset = pOffset; assert( pOffset==0 || pLimit!=0 ); pNew->addrOpenEphm[0] = -1; pNew->addrOpenEphm[1] = -1; pNew->addrOpenEphm[2] = -1; |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
2098 2099 2100 2101 2102 2103 2104 2105 2106 2107 2108 2109 2110 2111 | #define SF_Aggregate 0x0004 /* Contains aggregate functions */ #define SF_UsesEphemeral 0x0008 /* Uses the OpenEphemeral opcode */ #define SF_Expanded 0x0010 /* sqlite3SelectExpand() called on this */ #define SF_HasTypeInfo 0x0020 /* FROM subqueries have Table metadata */ #define SF_UseSorter 0x0040 /* Sort using a sorter */ #define SF_Values 0x0080 /* Synthesized from VALUES clause */ #define SF_Materialize 0x0100 /* Force materialization of views */ /* ** The results of a select can be distributed in several ways. The ** "SRT" prefix means "SELECT Result Type". */ #define SRT_Union 1 /* Store result as keys in an index */ | > | 2098 2099 2100 2101 2102 2103 2104 2105 2106 2107 2108 2109 2110 2111 2112 | #define SF_Aggregate 0x0004 /* Contains aggregate functions */ #define SF_UsesEphemeral 0x0008 /* Uses the OpenEphemeral opcode */ #define SF_Expanded 0x0010 /* sqlite3SelectExpand() called on this */ #define SF_HasTypeInfo 0x0020 /* FROM subqueries have Table metadata */ #define SF_UseSorter 0x0040 /* Sort using a sorter */ #define SF_Values 0x0080 /* Synthesized from VALUES clause */ #define SF_Materialize 0x0100 /* Force materialization of views */ #define SF_NestedFrom 0x0200 /* Part of a parenthesized FROM clause */ /* ** The results of a select can be distributed in several ways. The ** "SRT" prefix means "SELECT Result Type". */ #define SRT_Union 1 /* Store result as keys in an index */ |
︙ | ︙ | |||
2810 2811 2812 2813 2814 2815 2816 | void sqlite3IdListDelete(sqlite3*, IdList*); void sqlite3SrcListDelete(sqlite3*, SrcList*); Index *sqlite3CreateIndex(Parse*,Token*,Token*,SrcList*,ExprList*,int,Token*, Token*, int, int); void sqlite3DropIndex(Parse*, SrcList*, int); int sqlite3Select(Parse*, Select*, SelectDest*); Select *sqlite3SelectNew(Parse*,ExprList*,SrcList*,Expr*,ExprList*, | | | 2811 2812 2813 2814 2815 2816 2817 2818 2819 2820 2821 2822 2823 2824 2825 | void sqlite3IdListDelete(sqlite3*, IdList*); void sqlite3SrcListDelete(sqlite3*, SrcList*); Index *sqlite3CreateIndex(Parse*,Token*,Token*,SrcList*,ExprList*,int,Token*, Token*, int, int); void sqlite3DropIndex(Parse*, SrcList*, int); int sqlite3Select(Parse*, Select*, SelectDest*); Select *sqlite3SelectNew(Parse*,ExprList*,SrcList*,Expr*,ExprList*, Expr*,ExprList*,u16,Expr*,Expr*); void sqlite3SelectDelete(sqlite3*, Select*); Table *sqlite3SrcListLookup(Parse*, SrcList*); int sqlite3IsReadOnly(Parse*, Table*, int); void sqlite3OpenTable(Parse*, int iCur, int iDb, Table*, int); #if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY) Expr *sqlite3LimitWhere(Parse *, SrcList *, Expr *, ExprList *, Expr *, Expr *, char *); #endif |
︙ | ︙ |
Changes to test/where8.test.
︙ | ︙ | |||
285 286 287 288 289 290 291 292 293 294 295 296 297 298 | execsql_status { SELECT c FROM t1, t2 WHERE a BETWEEN 1 AND 2 OR a = ( SELECT sum(e IS NULL) FROM t2 AS inner WHERE t2.d>inner.d ) ORDER BY c } } {I I I I I I I I I I II II II II II II II II II II III III III III III 9 1} #----------------------------------------------------------------------- # The following tests - where8-4.* - verify that adding or removing # indexes does not change the results returned by various queries. # do_test where8-4.1 { execsql { | > > > > > > > > > > > > > > | 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 | execsql_status { SELECT c FROM t1, t2 WHERE a BETWEEN 1 AND 2 OR a = ( SELECT sum(e IS NULL) FROM t2 AS inner WHERE t2.d>inner.d ) ORDER BY c } } {I I I I I I I I I I II II II II II II II II II II III III III III III 9 1} do_test where8-3.21 { execsql_status { SELECT a, d FROM t1, (t2) WHERE (a=d OR b=e) AND a<5 ORDER BY a } } {1 1 2 2 3 3 4 2 4 4 0 0} do_test where8-3.22 { execsql_status { SELECT a, d FROM ((((((t1))), (((t2)))))) WHERE (a=d OR b=e) AND a<5 ORDER BY a } } {1 1 2 2 3 3 4 2 4 4 0 0} #----------------------------------------------------------------------- # The following tests - where8-4.* - verify that adding or removing # indexes does not change the results returned by various queries. # do_test where8-4.1 { execsql { |
︙ | ︙ |
Changes to test/where9.test.
︙ | ︙ | |||
228 229 230 231 232 233 234 | OR (b IS NULL AND c NOT NULL AND d NOT NULL) OR (b NOT NULL AND c IS NULL AND d NOT NULL) ORDER BY a } } {90 91 92 97 scan 98 sort 0} do_test where9-1.3.4 { count_steps { | | | 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 | OR (b IS NULL AND c NOT NULL AND d NOT NULL) OR (b NOT NULL AND c IS NULL AND d NOT NULL) ORDER BY a } } {90 91 92 97 scan 98 sort 0} do_test where9-1.3.4 { count_steps { SELECT a FROM (t4) WHERE (b IS NULL AND c NOT NULL AND d NOT NULL) OR (b NOT NULL AND c NOT NULL AND d IS NULL) OR (b NOT NULL AND c IS NULL AND d NOT NULL) ORDER BY a } } {90 91 92 97 scan 98 sort 0} |
︙ | ︙ | |||
870 871 872 873 874 875 876 877 878 879 880 | INSERT INTO t82 VALUES(2,4); INSERT INTO t83 VALUES(5,55); SELECT * FROM t81 LEFT JOIN t82 ON y=b JOIN t83 WHERE c==p OR d==p ORDER BY +a; } } {2 3 4 5 {} {} 5 55 3 4 5 6 2 4 5 55} finish_test | > > > > > > > > > > > > > > > > | 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 | INSERT INTO t82 VALUES(2,4); INSERT INTO t83 VALUES(5,55); SELECT * FROM t81 LEFT JOIN t82 ON y=b JOIN t83 WHERE c==p OR d==p ORDER BY +a; } } {2 3 4 5 {} {} 5 55 3 4 5 6 2 4 5 55} do_test where9-8.2 { db eval { SELECT * FROM t81 LEFT JOIN (t82) ON y=b JOIN t83 WHERE c==p OR d==p ORDER BY +a; } } {2 3 4 5 {} {} 5 55 3 4 5 6 2 4 5 55} do_test where9-8.3 { db eval { SELECT * FROM (t81) LEFT JOIN (main.t82) ON y=b JOIN t83 WHERE c==p OR d==p ORDER BY +a; } } {2 3 4 5 {} {} 5 55 3 4 5 6 2 4 5 55} finish_test |