/ 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 Unified Diffs Show Whitespace Changes Patch

Changes to src/resolve.c.

879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
...
915
916
917
918
919
920
921





922
923
924
925
926
927
928
*/
static int resolveOrderGroupBy(
  NameContext *pNC,     /* The name context of the SELECT statement */
  Select *pSelect,      /* The SELECT statement holding pOrderBy */
  ExprList *pOrderBy,   /* An ORDER BY or GROUP BY clause to resolve */
  const char *zType     /* Either "ORDER" or "GROUP", as appropriate */
){
  int i;                         /* Loop counter */
  int iCol;                      /* Column number */
  struct ExprList_item *pItem;   /* A term of the ORDER BY clause */
  Parse *pParse;                 /* Parsing context */
  int nResult;                   /* Number of terms in the result set */

  if( pOrderBy==0 ) return 0;
  nResult = pSelect->pEList->nExpr;
................................................................................
      continue;
    }

    /* Otherwise, treat the ORDER BY term as an ordinary expression */
    pItem->iOrderByCol = 0;
    if( sqlite3ResolveExprNames(pNC, pE) ){
      return 1;





    }
  }
  return sqlite3ResolveOrderGroupBy(pParse, pSelect, pOrderBy, zType);
}

/*
** Resolve names in the SELECT statement p and all of its descendents.







|







 







>
>
>
>
>







879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
...
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
*/
static int resolveOrderGroupBy(
  NameContext *pNC,     /* The name context of the SELECT statement */
  Select *pSelect,      /* The SELECT statement holding pOrderBy */
  ExprList *pOrderBy,   /* An ORDER BY or GROUP BY clause to resolve */
  const char *zType     /* Either "ORDER" or "GROUP", as appropriate */
){
  int i, j;                      /* Loop counters */
  int iCol;                      /* Column number */
  struct ExprList_item *pItem;   /* A term of the ORDER BY clause */
  Parse *pParse;                 /* Parsing context */
  int nResult;                   /* Number of terms in the result set */

  if( pOrderBy==0 ) return 0;
  nResult = pSelect->pEList->nExpr;
................................................................................
      continue;
    }

    /* Otherwise, treat the ORDER BY term as an ordinary expression */
    pItem->iOrderByCol = 0;
    if( sqlite3ResolveExprNames(pNC, pE) ){
      return 1;
    }
    for(j=0; j<pSelect->pEList->nExpr; j++){
      if( sqlite3ExprCompare(pE, pSelect->pEList->a[j].pExpr)==0 ){
        pItem->iOrderByCol = j+1;
      }
    }
  }
  return sqlite3ResolveOrderGroupBy(pParse, pSelect, pOrderBy, zType);
}

/*
** Resolve names in the SELECT statement p and all of its descendents.

Changes to test/select9.test.

410
411
412
413
414
415
416
417



418
































419
do_test select9-4.X {
  execsql {
    DROP INDEX i1;
    DROP INDEX i2;
    DROP VIEW v1;
  }
} {}





































finish_test








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

410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
do_test select9-4.X {
  execsql {
    DROP INDEX i1;
    DROP INDEX i2;
    DROP VIEW v1;
  }
} {}

# Testing to make sure that queries involving a view of a compound select
# are planned efficiently.  This detects a problem reported on the mailing
# list on 2012-04-26.  See
#
#  http://www.mail-archive.com/sqlite-users%40sqlite.org/msg69746.html
#
# For additional information.
#
do_test select9-5.1 {
  db eval {
    CREATE TABLE t51(x, y);
    CREATE TABLE t52(x, y);
    CREATE VIEW v5 as
       SELECT x, y FROM t51
       UNION ALL
       SELECT x, y FROM t52;
    CREATE INDEX t51x ON t51(x);
    CREATE INDEX t52x ON t52(x);
    EXPLAIN QUERY PLAN
       SELECT * FROM v5 WHERE x='12345' ORDER BY y;
  }
} {~/SCAN TABLE/}  ;# Uses indices with "*"
do_test select9-5.2 {
  db eval {
    EXPLAIN QUERY PLAN
       SELECT x, y FROM v5 WHERE x='12345' ORDER BY y;
  }
} {~/SCAN TABLE/}  ;# Uses indices with "x, y"
do_test select9-5.3 {
  db eval {
    EXPLAIN QUERY PLAN
       SELECT x, y FROM v5 WHERE +x='12345' ORDER BY y;
  }
} {/SCAN TABLE/}   ;# Full table scan if the "+x" prevents index usage.


finish_test