/ Check-in [c7bae50b]
Login

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

Overview
Comment:Implement FROM-clause subqueries as co-routines whenever they are guaranteed to be the outer-most loop of the join.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: c7bae50bdccb5bcf3bc22e8ac5bb6725ef13db39
User & Date: drh 2016-03-15 17:52:12
Context
2016-03-15
19:10
In the Win32 VFS, use SQLITE_FCNTL_LAST_ERRNO instead of (the older) SQLITE_LAST_ERRNO. check-in: 9463762c user: mistachkin tags: trunk
17:52
Implement FROM-clause subqueries as co-routines whenever they are guaranteed to be the outer-most loop of the join. check-in: c7bae50b user: drh tags: trunk
12:37
More test-case changes so that everything works when the reserved_bytes value in the header is non-zero. check-in: 2fd095b1 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

4966
4967
4968
4969
4970
4971
4972












4973

4974

4975
4976
4977
4978
4979
4980
4981
4982
4983
        SELECTTRACE(0x100,pParse,p,("After WHERE-clause push-down:\n"));
        sqlite3TreeViewSelect(0, p, 0);
      }
#endif
    }

    /* Generate code to implement the subquery












    */

    if( pTabList->nSrc==1

     && (p->selFlags & SF_All)==0
     && OptimizationEnabled(db, SQLITE_SubqCoroutine)
    ){
      /* Implement a co-routine that will return a single row of the result
      ** set on each invocation.
      */
      int addrTop = sqlite3VdbeCurrentAddr(v)+1;
      pItem->regReturn = ++pParse->nMem;
      sqlite3VdbeAddOp3(v, OP_InitCoroutine, pItem->regReturn, 0, addrTop);







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

>
|
>
|
|







4966
4967
4968
4969
4970
4971
4972
4973
4974
4975
4976
4977
4978
4979
4980
4981
4982
4983
4984
4985
4986
4987
4988
4989
4990
4991
4992
4993
4994
4995
4996
4997
        SELECTTRACE(0x100,pParse,p,("After WHERE-clause push-down:\n"));
        sqlite3TreeViewSelect(0, p, 0);
      }
#endif
    }

    /* Generate code to implement the subquery
    **
    ** The subquery is implemented as a co-routine if all of these are true:
    **   (1)  The subquery is guaranteed to be the outer loop (so that it
    **        does not need to be computed more than once)
    **   (2)  The ALL keyword after SELECT is omitted.  (Applications are
    **        allowed to say "SELECT ALL" instead of just "SELECT" to disable
    **        the use of co-routines.)
    **   (3)  Co-routines are not disabled using sqlite3_test_control()
    **        with SQLITE_TESTCTRL_OPTIMIZATIONS.
    **
    ** TODO: Are there other reasons beside (1) to use a co-routine
    ** implementation?
    */
    if( i==0
     && (pTabList->nSrc==1
            || (pTabList->a[1].fg.jointype&(JT_LEFT|JT_CROSS))!=0)  /* (1) */
     && (p->selFlags & SF_All)==0                                   /* (2) */
     && OptimizationEnabled(db, SQLITE_SubqCoroutine)               /* (3) */
    ){
      /* Implement a co-routine that will return a single row of the result
      ** set on each invocation.
      */
      int addrTop = sqlite3VdbeCurrentAddr(v)+1;
      pItem->regReturn = ++pParse->nMem;
      sqlite3VdbeAddOp3(v, OP_InitCoroutine, pItem->regReturn, 0, addrTop);

Changes to test/select4.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
931
932
933
934
935
936
937
938




































939
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing UNION, INTERSECT and EXCEPT operators
# in SELECT statements.
#
# $Id: select4.test,v 1.30 2009/04/16 00:24:24 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Most tests in this file depend on compound-select. But there are a couple
# right at the end that test DISTINCT, so we cannot omit the entire file.
#
................................................................................
   WHERE t0.a=t1.a AND t1.a=33 AND t0.b=456
  UNION
  SELECT DISTINCT t0.id, t0.a, t0.b
    FROM tx AS t0, tx AS t1
   WHERE t0.a=t1.a AND t1.a=33 AND t0.b=789
   ORDER BY 1;
} {1 33 456 2 33 789}





































finish_test







<







 








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

8
9
10
11
12
13
14

15
16
17
18
19
20
21
...
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing UNION, INTERSECT and EXCEPT operators
# in SELECT statements.
#


set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Most tests in this file depend on compound-select. But there are a couple
# right at the end that test DISTINCT, so we cannot omit the entire file.
#
................................................................................
   WHERE t0.a=t1.a AND t1.a=33 AND t0.b=456
  UNION
  SELECT DISTINCT t0.id, t0.a, t0.b
    FROM tx AS t0, tx AS t1
   WHERE t0.a=t1.a AND t1.a=33 AND t0.b=789
   ORDER BY 1;
} {1 33 456 2 33 789}

# Enhancement (2016-03-15):  Use a co-routine for subqueries if the
# subquery is guaranteed to be the outer-most query
#
do_execsql_test select4-16.1 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,
  PRIMARY KEY(a,b DESC)) WITHOUT ROWID;

  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100)
  INSERT INTO t1(a,b,c,d)
    SELECT x%10, x/10, x, printf('xyz%dabc',x) FROM c;

  SELECT t3.c FROM 
    (SELECT a,max(b) AS m FROM t1 WHERE a>=5 GROUP BY a) AS t2
    JOIN t1 AS t3
  WHERE t2.a=t3.a AND t2.m=t3.b
  ORDER BY t3.a;
} {95 96 97 98 99}
do_execsql_test select4-16.2 {
  SELECT t3.c FROM 
    (SELECT a,max(b) AS m FROM t1 WHERE a>=5 GROUP BY a) AS t2
    CROSS JOIN t1 AS t3
  WHERE t2.a=t3.a AND t2.m=t3.b
  ORDER BY t3.a;
} {95 96 97 98 99}
do_execsql_test select4-16.3 {
  SELECT t3.c FROM 
    (SELECT a,max(b) AS m FROM t1 WHERE a>=5 GROUP BY a) AS t2
    LEFT JOIN t1 AS t3
  WHERE t2.a=t3.a AND t2.m=t3.b
  ORDER BY t3.a;
} {95 96 97 98 99}




finish_test