/ Check-in [a29e117d]
Login

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

Overview
Comment:Using "SELECT ALL" instead of just "SELECT" on a query that uses a single unflattenable subquery or view in its FROM clause will force the subquery to be manifested into a temporary table rather than run incrementally using a co-routine. This is a stop-gap means of controlling the decision to manifest while we try to work out a better to make that decision automatically.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: a29e117d7ecec05da949348689dbfb0d3acb1280
User & Date: drh 2015-05-29 01:35:19
References
2017-09-28
17:29
Remove the (undocumented) query-planner control that prevents a "SELECT ALL" subquery in FROM clause from being implemented as a co-routine. This control was added by [a29e117d7ec], where it was called a "stop-gap". check-in: ff2f5a31 user: drh tags: trunk
Context
2015-05-29
14:47
Add support for automatic indexes on FROM-clause subqueries that are implemented via co-routine. check-in: 020b8b10 user: drh tags: trunk
13:55
An attempt to allow automatic index creation on subqueries accessed via co-routine. check-in: 521345ad user: drh tags: coroutine-autoindex
01:35
Using "SELECT ALL" instead of just "SELECT" on a query that uses a single unflattenable subquery or view in its FROM clause will force the subquery to be manifested into a temporary table rather than run incrementally using a co-routine. This is a stop-gap means of controlling the decision to manifest while we try to work out a better to make that decision automatically. check-in: a29e117d user: drh tags: trunk
2015-05-28
15:14
Added comments and testcase() macros to error cases in the btree search. check-in: 7da7dc71 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/parse.y.

523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
...
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
}

// 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.
//
................................................................................
%endif  SQLITE_OMIT_CAST
expr(A) ::= id(X) LP distinct(D) exprlist(Y) RP(E). {
  if( Y && Y->nExpr>pParse->db->aLimit[SQLITE_LIMIT_FUNCTION_ARG] ){
    sqlite3ErrorMsg(pParse, "too many arguments on function %T", &X);
  }
  A.pExpr = sqlite3ExprFunction(pParse, Y, &X);
  spanSet(&A,&X,&E);
  if( D && A.pExpr ){
    A.pExpr->flags |= EP_Distinct;
  }
}
expr(A) ::= id(X) LP STAR RP(E). {
  A.pExpr = sqlite3ExprFunction(pParse, 0, &X);
  spanSet(&A,&X,&E);
}







|







 







|







523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
...
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
}

// 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 = SF_All;}
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.
//
................................................................................
%endif  SQLITE_OMIT_CAST
expr(A) ::= id(X) LP distinct(D) exprlist(Y) RP(E). {
  if( Y && Y->nExpr>pParse->db->aLimit[SQLITE_LIMIT_FUNCTION_ARG] ){
    sqlite3ErrorMsg(pParse, "too many arguments on function %T", &X);
  }
  A.pExpr = sqlite3ExprFunction(pParse, Y, &X);
  spanSet(&A,&X,&E);
  if( D==SF_Distinct && A.pExpr ){
    A.pExpr->flags |= EP_Distinct;
  }
}
expr(A) ::= id(X) LP STAR RP(E). {
  A.pExpr = sqlite3ExprFunction(pParse, 0, &X);
  spanSet(&A,&X,&E);
}

Changes to src/select.c.

4813
4814
4815
4816
4817
4818
4819

4820
4821
4822
4823
4824
4825
4826
      /* This subquery can be absorbed into its parent. */
      if( isAggSub ){
        isAgg = 1;
        p->selFlags |= SF_Aggregate;
      }
      i = -1;
    }else if( pTabList->nSrc==1

           && 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;







>







4813
4814
4815
4816
4817
4818
4819
4820
4821
4822
4823
4824
4825
4826
4827
      /* This subquery can be absorbed into its parent. */
      if( isAggSub ){
        isAgg = 1;
        p->selFlags |= SF_Aggregate;
      }
      i = -1;
    }else 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;

Changes to src/sqliteInt.h.

2392
2393
2394
2395
2396
2397
2398

2399
2400
2401
2402
2403
2404
2405
2406
2407
2408
2409
2410

2411
2412
2413
2414
2415
2416
2417
2418
};

/*
** Allowed values for Select.selFlags.  The "SF" prefix stands for
** "Select Flag".
*/
#define SF_Distinct        0x0001  /* Output should be DISTINCT */

#define SF_Resolved        0x0002  /* Identifiers have been resolved */
#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_Compound        0x0040  /* Part of a compound query */
#define SF_Values          0x0080  /* Synthesized from VALUES clause */
#define SF_MultiValue      0x0100  /* Single VALUES term with multiple rows */
#define SF_NestedFrom      0x0200  /* Part of a parenthesized FROM clause */
#define SF_MaybeConvert    0x0400  /* Need convertCompoundSelectToSubquery() */
#define SF_Recursive       0x0800  /* The recursive part of a recursive CTE */
#define SF_MinMaxAgg       0x1000  /* Aggregate containing min() or max() */

#define SF_Converted       0x2000  /* By convertCompoundSelectToSubquery() */


/*
** The results of a SELECT can be distributed in several ways, as defined
** by one of the following macros.  The "SRT" prefix means "SELECT Result
** Type".
**







>
|
|
|
|
|
|
|
|
|
|
<

>
|







2392
2393
2394
2395
2396
2397
2398
2399
2400
2401
2402
2403
2404
2405
2406
2407
2408
2409

2410
2411
2412
2413
2414
2415
2416
2417
2418
2419
};

/*
** Allowed values for Select.selFlags.  The "SF" prefix stands for
** "Select Flag".
*/
#define SF_Distinct        0x0001  /* Output should be DISTINCT */
#define SF_All             0x0002  /* Includes the ALL keyword */
#define SF_Resolved        0x0004  /* Identifiers have been resolved */
#define SF_Aggregate       0x0008  /* Contains aggregate functions */
#define SF_UsesEphemeral   0x0010  /* Uses the OpenEphemeral opcode */
#define SF_Expanded        0x0020  /* sqlite3SelectExpand() called on this */
#define SF_HasTypeInfo     0x0040  /* FROM subqueries have Table metadata */
#define SF_Compound        0x0080  /* Part of a compound query */
#define SF_Values          0x0100  /* Synthesized from VALUES clause */
#define SF_MultiValue      0x0200  /* Single VALUES term with multiple rows */
#define SF_NestedFrom      0x0400  /* Part of a parenthesized FROM clause */
#define SF_MaybeConvert    0x0800  /* Need convertCompoundSelectToSubquery() */

#define SF_MinMaxAgg       0x1000  /* Aggregate containing min() or max() */
#define SF_Recursive       0x2000  /* The recursive part of a recursive CTE */
#define SF_Converted       0x4000  /* By convertCompoundSelectToSubquery() */


/*
** The results of a SELECT can be distributed in several ways, as defined
** by one of the following macros.  The "SRT" prefix means "SELECT Result
** Type".
**