/ Check-in [a49e909c]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Enhance the processing of ORDER BY clauses on compound queries to better match terms of the order by against expressions in the result set, in order to enable better query optimization.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: a49e909c8738317c8383ce93771c0a9c4cf270bc
User & Date: drh 2012-04-27 01:09:06
References
2014-09-20
17:05 New ticket [d11a6e90] Query planner fault on three-way nested join. artifact: ce50af6b user: drh
Context
2012-04-27
16:38
Fix a minor deviation from the coding style guidelines. check-in: 1e51bffe user: drh tags: trunk
01:09
Enhance the processing of ORDER BY clauses on compound queries to better match terms of the order by against expressions in the result set, in order to enable better query optimization. check-in: a49e909c user: drh tags: trunk
01:08
Enhance the do_test proc in the test suite so that if the expected result is of the form "/.../" or "~/.../" then regular expression matching is done between result and the "..." part of the expectation. In the ~/.../ case, we expect there to be no match. check-in: c9a73440 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/resolve.c.

   879    879   */
   880    880   static int resolveOrderGroupBy(
   881    881     NameContext *pNC,     /* The name context of the SELECT statement */
   882    882     Select *pSelect,      /* The SELECT statement holding pOrderBy */
   883    883     ExprList *pOrderBy,   /* An ORDER BY or GROUP BY clause to resolve */
   884    884     const char *zType     /* Either "ORDER" or "GROUP", as appropriate */
   885    885   ){
   886         -  int i;                         /* Loop counter */
          886  +  int i, j;                      /* Loop counters */
   887    887     int iCol;                      /* Column number */
   888    888     struct ExprList_item *pItem;   /* A term of the ORDER BY clause */
   889    889     Parse *pParse;                 /* Parsing context */
   890    890     int nResult;                   /* Number of terms in the result set */
   891    891   
   892    892     if( pOrderBy==0 ) return 0;
   893    893     nResult = pSelect->pEList->nExpr;
................................................................................
   915    915         continue;
   916    916       }
   917    917   
   918    918       /* Otherwise, treat the ORDER BY term as an ordinary expression */
   919    919       pItem->iOrderByCol = 0;
   920    920       if( sqlite3ResolveExprNames(pNC, pE) ){
   921    921         return 1;
          922  +    }
          923  +    for(j=0; j<pSelect->pEList->nExpr; j++){
          924  +      if( sqlite3ExprCompare(pE, pSelect->pEList->a[j].pExpr)==0 ){
          925  +        pItem->iOrderByCol = j+1;
          926  +      }
   922    927       }
   923    928     }
   924    929     return sqlite3ResolveOrderGroupBy(pParse, pSelect, pOrderBy, zType);
   925    930   }
   926    931   
   927    932   /*
   928    933   ** Resolve names in the SELECT statement p and all of its descendents.

Changes to test/select9.test.

   410    410   do_test select9-4.X {
   411    411     execsql {
   412    412       DROP INDEX i1;
   413    413       DROP INDEX i2;
   414    414       DROP VIEW v1;
   415    415     }
   416    416   } {}
          417  +
          418  +# Testing to make sure that queries involving a view of a compound select
          419  +# are planned efficiently.  This detects a problem reported on the mailing
          420  +# list on 2012-04-26.  See
          421  +#
          422  +#  http://www.mail-archive.com/sqlite-users%40sqlite.org/msg69746.html
          423  +#
          424  +# For additional information.
          425  +#
          426  +do_test select9-5.1 {
          427  +  db eval {
          428  +    CREATE TABLE t51(x, y);
          429  +    CREATE TABLE t52(x, y);
          430  +    CREATE VIEW v5 as
          431  +       SELECT x, y FROM t51
          432  +       UNION ALL
          433  +       SELECT x, y FROM t52;
          434  +    CREATE INDEX t51x ON t51(x);
          435  +    CREATE INDEX t52x ON t52(x);
          436  +    EXPLAIN QUERY PLAN
          437  +       SELECT * FROM v5 WHERE x='12345' ORDER BY y;
          438  +  }
          439  +} {~/SCAN TABLE/}  ;# Uses indices with "*"
          440  +do_test select9-5.2 {
          441  +  db eval {
          442  +    EXPLAIN QUERY PLAN
          443  +       SELECT x, y FROM v5 WHERE x='12345' ORDER BY y;
          444  +  }
          445  +} {~/SCAN TABLE/}  ;# Uses indices with "x, y"
          446  +do_test select9-5.3 {
          447  +  db eval {
          448  +    EXPLAIN QUERY PLAN
          449  +       SELECT x, y FROM v5 WHERE +x='12345' ORDER BY y;
          450  +  }
          451  +} {/SCAN TABLE/}   ;# Full table scan if the "+x" prevents index usage.
   417    452   
   418    453   
   419    454   finish_test