/ Check-in [adc082c1]
Login

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

Overview
Comment:When checking for the WHERE-clause push-down optimization, verify that all terms of the compound inner SELECT are non-aggregate, not just the last term. Fix for ticket [f7f8c97e97597].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | push-down-backport
Files: files | file ages | folders
SHA3-256: adc082c1461e0237cd42653b529fbc136f5899baeff6ee32ee943d76184080c1
User & Date: drh 2017-07-17 19:25:10
Context
2017-07-17
19:37
Try to push relevant WHERE clause terms of the outer query down into the subquery in order to help the subquery run faster and/or use less memory. This is a cherry-pick of [6df18e949d36] with bug fixes. check-in: cd6ac078 user: drh tags: branch-3.8.9
19:25
When checking for the WHERE-clause push-down optimization, verify that all terms of the compound inner SELECT are non-aggregate, not just the last term. Fix for ticket [f7f8c97e97597]. Closed-Leaf check-in: adc082c1 user: drh tags: push-down-backport
19:14
Do not apply the WHERE-clause pushdown optimization to terms that originate in the ON or USING clause of a LEFT JOIN. Fix for ticket [c2a19d81652f40568c]. check-in: 52674f94 user: drh tags: push-down-backport
2016-04-25
02:20
When checking for the WHERE-clause push-down optimization, verify that all terms of the compound inner SELECT are non-aggregate, not just the last term. Fix for ticket [f7f8c97e97597]. check-in: ec215f94 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

3791
3792
3793
3794
3795
3796
3797

3798

3799



3800
3801

3802
3803
3804
3805
3806
3807
3808
3809
3810
  sqlite3 *db,          /* The database connection (for malloc()) */
  Select *pSubq,        /* The subquery whose WHERE clause is to be augmented */
  Expr *pWhere,         /* The WHERE clause of the outer query */
  int iCursor           /* Cursor number of the subquery */
){
  Expr *pNew;
  int nChng = 0;

  if( pWhere==0 ) return 0;

  if( (pSubq->selFlags & (SF_Aggregate|SF_Recursive))!=0 ){



     return 0; /* restrictions (1) and (2) */
  }

  if( pSubq->pLimit!=0 ){
     return 0; /* restriction (3) */
  }
  while( pWhere->op==TK_AND ){
    nChng += pushDownWhereTerms(db, pSubq, pWhere->pRight, iCursor);
    pWhere = pWhere->pLeft;
  }
  if( ExprHasProperty(pWhere,EP_FromJoin) ) return 0; /* restriction 5 */
  if( sqlite3ExprIsTableConstant(pWhere, iCursor) ){







>

>
|
>
>
>
|
|
>

|







3791
3792
3793
3794
3795
3796
3797
3798
3799
3800
3801
3802
3803
3804
3805
3806
3807
3808
3809
3810
3811
3812
3813
3814
3815
3816
  sqlite3 *db,          /* The database connection (for malloc()) */
  Select *pSubq,        /* The subquery whose WHERE clause is to be augmented */
  Expr *pWhere,         /* The WHERE clause of the outer query */
  int iCursor           /* Cursor number of the subquery */
){
  Expr *pNew;
  int nChng = 0;
  Select *pX;           /* For looping over compound SELECTs in pSubq */
  if( pWhere==0 ) return 0;
  for(pX=pSubq; pX; pX=pX->pPrior){
    if( (pX->selFlags & (SF_Aggregate|SF_Recursive))!=0 ){
      testcase( pX->selFlags & SF_Aggregate );
      testcase( pX->selFlags & SF_Recursive );
      testcase( pX!=pSubq );
      return 0; /* restrictions (1) and (2) */
    }
  }
  if( pSubq->pLimit!=0 ){
    return 0; /* restriction (3) */
  }
  while( pWhere->op==TK_AND ){
    nChng += pushDownWhereTerms(db, pSubq, pWhere->pRight, iCursor);
    pWhere = pWhere->pLeft;
  }
  if( ExprHasProperty(pWhere,EP_FromJoin) ) return 0; /* restriction 5 */
  if( sqlite3ExprIsTableConstant(pWhere, iCursor) ){

Changes to test/select4.test.

859
860
861
862
863
864
865
866





































867
} {}
do_execsql_test select4-14.8 {
  SELECT * FROM t14 EXCEPT VALUES('a','b','c') EXCEPT VALUES(4,5,6)
} {1 2 3}
do_execsql_test select4-14.9 {
  SELECT * FROM t14 UNION ALL VALUES(3,2,1),(2,3,1),(1,2,3),(2,1,3);
} {1 2 3 4 5 6 3 2 1 2 3 1 1 2 3 2 1 3}






































finish_test








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

859
860
861
862
863
864
865
866
867
868
869
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
897
898
899
900
901
902
903
904
} {}
do_execsql_test select4-14.8 {
  SELECT * FROM t14 EXCEPT VALUES('a','b','c') EXCEPT VALUES(4,5,6)
} {1 2 3}
do_execsql_test select4-14.9 {
  SELECT * FROM t14 UNION ALL VALUES(3,2,1),(2,3,1),(1,2,3),(2,1,3);
} {1 2 3 4 5 6 3 2 1 2 3 1 1 2 3 2 1 3}

# Ticket https://www.sqlite.org/src/tktview/f7f8c97e975978d45  on 2016-04-25
#
# The where push-down optimization from 2015-06-02 is suppose to disable
# on aggregate subqueries.  But if the subquery is a compound where the
# last SELECT is non-aggregate but some other SELECT is an aggregate, the
# test is incomplete and the optimization is not properly disabled.
# 
# The following test cases verify that the fix works.
#
do_execsql_test select4-17.1 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a int, b int);
  INSERT INTO t1 VALUES(1,2),(1,18),(2,19);
  SELECT x, y FROM (
    SELECT 98 AS x, 99 AS y
    UNION
    SELECT a AS x, sum(b) AS y FROM t1 GROUP BY a
  ) AS w WHERE y>=20
  ORDER BY +x;
} {1 20 98 99}
do_execsql_test select4-17.2 {
  SELECT x, y FROM (
    SELECT a AS x, sum(b) AS y FROM t1 GROUP BY a
    UNION
    SELECT 98 AS x, 99 AS y
  ) AS w WHERE y>=20
  ORDER BY +x;
} {1 20 98 99}
do_catchsql_test select4-17.3 {
  SELECT x, y FROM (
    SELECT a AS x, sum(b) AS y FROM t1 GROUP BY a LIMIT 3
    UNION
    SELECT 98 AS x, 99 AS y
  ) AS w WHERE y>=20
  ORDER BY +x;
} {1 {LIMIT clause should come after UNION not before}}

finish_test