/ Check-in [6fcb3bff]
Login

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

Overview
Comment:Call the query flattener while processing the parent query. Previously, it was called while processing the sub-queries. (CVS 5330)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 6fcb3bffe26ae1c21c72ce9019f1db1c118094a4
User & Date: danielk1977 2008-06-30 18:12:28
Context
2008-07-01
14:09
Optimize sub-selects and views that use UNION ALL. This optimization isn't very well tested yet. (CVS 5331) check-in: 3ef468e7 user: danielk1977 tags: trunk
2008-06-30
18:12
Call the query flattener while processing the parent query. Previously, it was called while processing the sub-queries. (CVS 5330) check-in: 6fcb3bff user: danielk1977 tags: trunk
15:09
Changes to loadext.test so that it works on osx as well as linux. (CVS 5329) check-in: 189cd854 user: danielk1977 tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
3665
3666
3667
3668
3669
3670
3671

3672
3673
3674
3675
3676
3677
3678
....
3692
3693
3694
3695
3696
3697
3698
3699
3700
3701
3702
3703
3704
3705
3706
3707
3708
3709
3710
....
3730
3731
3732
3733
3734
3735
3736
3737
3738
3739
3740

3741
3742
3743
3744
3745

3746
3747

3748
3749


3750
3751
3752
3753
3754
3755
3756
3757








3758
3759

3760
3761
3762
3763
3764
3765
3766
3767
3768
3769
3770
3771




3772
3773
3774
3775
3776
3777
3778
3779
3780
3781
3782
3783
3784
3785
3786
3787
3788
3789
3790
3791
3792
3793
3794
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.439 2008/06/27 00:52:45 drh Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
           pDest->eDest==SRT_Except || pDest->eDest==SRT_Discard);
    p->isDistinct = 0;
  }
  if( sqlite3SelectResolve(pParse, p, 0) ){
    goto select_end;
  }
  p->pOrderBy = pOrderBy;


#ifndef SQLITE_OMIT_COMPOUND_SELECT
  /* If there is are a sequence of queries, do the earlier ones first.
  */
  if( p->pPrior ){
    if( p->pRightmost==0 ){
      Select *pLoop, *pRight = 0;
................................................................................
    return multiSelect(pParse, p, pDest, aff);
  }
#endif

  /* Make local copies of the parameters for this query.
  */
  pTabList = p->pSrc;
  pWhere = p->pWhere;
  pGroupBy = p->pGroupBy;
  pHaving = p->pHaving;
  isAgg = p->isAgg;
  isDistinct = p->isDistinct;
  pEList = p->pEList;
  if( pEList==0 ) goto select_end;

  /* 
  ** Do not even attempt to generate any code if we have already seen
  ** errors before this routine starts.
  */
................................................................................
  v = sqlite3GetVdbe(pParse);
  if( v==0 ) goto select_end;

  /* Generate code for all sub-queries in the FROM clause
  */
#if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
  for(i=0; i<pTabList->nSrc; i++){
    const char *zSavedAuthContext = 0;
    int needRestoreContext;
    struct SrcList_item *pItem = &pTabList->a[i];
    SelectDest dest;


    if( pItem->pSelect==0 || pItem->isPopulated ) continue;
    if( pItem->zName!=0 ){
      zSavedAuthContext = pParse->zAuthContext;
      pParse->zAuthContext = pItem->zName;

      needRestoreContext = 1;
    }else{

      needRestoreContext = 0;
    }


    /* Increment Parse.nHeight by the height of the largest expression
    ** tree refered to by this, the parent select. The child select
    ** may contain expression trees of at most
    ** (SQLITE_MAX_EXPR_DEPTH-Parse.nHeight) height. This is a bit
    ** more conservative than necessary, but much easier than enforcing
    ** an exact limit.
    */
    pParse->nHeight += sqlite3SelectExprHeight(p);








    sqlite3SelectDestInit(&dest, SRT_EphemTab, pItem->iCursor);
    sqlite3Select(pParse, pItem->pSelect, &dest, p, i, &isAgg, 0);

    if( db->mallocFailed ){
      goto select_end;
    }
    pParse->nHeight -= sqlite3SelectExprHeight(p);
    if( needRestoreContext ){
      pParse->zAuthContext = zSavedAuthContext;
    }
    pTabList = p->pSrc;
    pWhere = p->pWhere;
    if( !IgnorableOrderby(pDest) ){
      pOrderBy = p->pOrderBy;
    }




    pGroupBy = p->pGroupBy;
    pHaving = p->pHaving;
    isDistinct = p->isDistinct;
  }
#endif

  /* Check to see if this is a subquery that can be "flattened" into its parent.
  ** If flattening is a possiblity, do so and return immediately.  
  */
#ifndef SQLITE_OMIT_VIEW
  if( pParent && pParentAgg &&
      flattenSubquery(db, pParent, parentTab, *pParentAgg, isAgg) ){
    if( isAgg ) *pParentAgg = 1;
    goto select_end;
  }
#endif

  /* If possible, rewrite the query to use GROUP BY instead of DISTINCT.
  ** GROUP BY may use an index, DISTINCT never does.
  */
  if( p->isDistinct && !p->isAgg && !p->pGroupBy ){
    p->pGroupBy = sqlite3ExprListDup(db, p->pEList);
    pGroupBy = p->pGroupBy;







|







 







>







 







<
<
<

<







 







<
<


>

|
|
|

>
|
<
>
|
|
>
>








>
>
>
>
>
>
>
>
|
|
>




<
<
<

<



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







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
3665
3666
3667
3668
3669
3670
3671
3672
3673
3674
3675
3676
3677
3678
3679
....
3693
3694
3695
3696
3697
3698
3699



3700

3701
3702
3703
3704
3705
3706
3707
....
3727
3728
3729
3730
3731
3732
3733


3734
3735
3736
3737
3738
3739
3740
3741
3742
3743

3744
3745
3746
3747
3748
3749
3750
3751
3752
3753
3754
3755
3756
3757
3758
3759
3760
3761
3762
3763
3764
3765
3766
3767
3768
3769
3770
3771



3772

3773
3774
3775
3776
3777
3778
3779
3780
3781
3782













3783
3784
3785
3786
3787
3788
3789
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.440 2008/06/30 18:12:28 danielk1977 Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
           pDest->eDest==SRT_Except || pDest->eDest==SRT_Discard);
    p->isDistinct = 0;
  }
  if( sqlite3SelectResolve(pParse, p, 0) ){
    goto select_end;
  }
  p->pOrderBy = pOrderBy;


#ifndef SQLITE_OMIT_COMPOUND_SELECT
  /* If there is are a sequence of queries, do the earlier ones first.
  */
  if( p->pPrior ){
    if( p->pRightmost==0 ){
      Select *pLoop, *pRight = 0;
................................................................................
    return multiSelect(pParse, p, pDest, aff);
  }
#endif

  /* Make local copies of the parameters for this query.
  */
  pTabList = p->pSrc;



  isAgg = p->isAgg;

  pEList = p->pEList;
  if( pEList==0 ) goto select_end;

  /* 
  ** Do not even attempt to generate any code if we have already seen
  ** errors before this routine starts.
  */
................................................................................
  v = sqlite3GetVdbe(pParse);
  if( v==0 ) goto select_end;

  /* Generate code for all sub-queries in the FROM clause
  */
#if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
  for(i=0; i<pTabList->nSrc; i++){


    struct SrcList_item *pItem = &pTabList->a[i];
    SelectDest dest;
    Select *pSub = pItem->pSelect;

    if( pSub==0 || pItem->isPopulated ) continue;
    if( pItem->zName!=0 ){   /* An sql view */
      const char *zSavedAuthContext = pParse->zAuthContext;
      pParse->zAuthContext = pItem->zName;
      rc = sqlite3SelectResolve(pParse, pSub, 0);
      pParse->zAuthContext = zSavedAuthContext;

      if( rc ){
        goto select_end;
      }
    }

    /* Increment Parse.nHeight by the height of the largest expression
    ** tree refered to by this, the parent select. The child select
    ** may contain expression trees of at most
    ** (SQLITE_MAX_EXPR_DEPTH-Parse.nHeight) height. This is a bit
    ** more conservative than necessary, but much easier than enforcing
    ** an exact limit.
    */
    pParse->nHeight += sqlite3SelectExprHeight(p);

    /* Check to see if the subquery can be absorbed into the parent. */
    if( !pSub->pPrior && flattenSubquery(db, p, i, isAgg, pSub->isAgg) ){
      if( pSub->isAgg ){
        p->isAgg = isAgg = 1;
      }
      i = -1;
    }else{
      sqlite3SelectDestInit(&dest, SRT_EphemTab, pItem->iCursor);
      sqlite3Select(pParse, pSub, &dest, p, i, &isAgg, 0);
    }
    if( db->mallocFailed ){
      goto select_end;
    }
    pParse->nHeight -= sqlite3SelectExprHeight(p);



    pTabList = p->pSrc;

    if( !IgnorableOrderby(pDest) ){
      pOrderBy = p->pOrderBy;
    }
  }
  pEList = p->pEList;
#endif
  pWhere = p->pWhere;
  pGroupBy = p->pGroupBy;
  pHaving = p->pHaving;
  isDistinct = p->isDistinct;














  /* If possible, rewrite the query to use GROUP BY instead of DISTINCT.
  ** GROUP BY may use an index, DISTINCT never does.
  */
  if( p->isDistinct && !p->isAgg && !p->pGroupBy ){
    p->pGroupBy = sqlite3ExprListDup(db, p->pEList);
    pGroupBy = p->pGroupBy;

Changes to test/view.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
509
510
511
512
513
514
515


















516
517
518
#    May you find forgiveness for yourself and forgive others.
#    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 VIEW statements.
#
# $Id: view.test,v 1.35 2008/01/25 15:04:50 drh Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Omit this entire file if the library is not configured with views enabled.
ifcapable !view {
  finish_test
  return
................................................................................
  }
} {1 {no such view: nosuchview}}
do_test view-17.2 {
  catchsql {
    DROP VIEW main.nosuchview
  }
} {1 {no such view: main.nosuchview}}




















finish_test







|







 







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



7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
#    May you find forgiveness for yourself and forgive others.
#    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 VIEW statements.
#
# $Id: view.test,v 1.36 2008/06/30 18:12:28 danielk1977 Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Omit this entire file if the library is not configured with views enabled.
ifcapable !view {
  finish_test
  return
................................................................................
  }
} {1 {no such view: nosuchview}}
do_test view-17.2 {
  catchsql {
    DROP VIEW main.nosuchview
  }
} {1 {no such view: main.nosuchview}}

do_test view-18.1 {
  execsql {
    DROP VIEW t1;
    DROP TABLE t1;
    CREATE TABLE t1(a, b, c);
    INSERT INTO t1 VALUES(1, 2, 3);
    INSERT INTO t1 VALUES(4, 5, 6);

    CREATE VIEW vv1 AS SELECT * FROM t1;
    CREATE VIEW vv2 AS SELECT * FROM vv1;
    CREATE VIEW vv3 AS SELECT * FROM vv2;
    CREATE VIEW vv4 AS SELECT * FROM vv3;
    CREATE VIEW vv5 AS SELECT * FROM vv4;

    SELECT * FROM vv5;
  }
} {1 2 3 4 5 6}


finish_test