SQLite

Changes On Branch open-only-once
Login

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

Changes In Branch open-only-once Excluding Merge-Ins

This is equivalent to a diff from 9c2e7612 to 3ad687b7

2014-02-26
21:35
More efficient removal of duplicates in recursive queries using the UNION compound operator. (check-in: 06c2db87 user: drh tags: trunk)
19:05
Only run the OP_OpenRead opcodes for a correlated subquery once, on the initial iteration. Keep the cursor open for subsequent runs. This was suppose to be a performance enhancement, but it is difficult to come up with a query where is makes a significant difference. Hence, the change is getting parked in a branch. (Leaf check-in: 3ad687b7 user: drh tags: open-only-once)
13:53
In the command-line shell for CSV import, if the lines are \r\n terminated and the last field is blank, make sure an empty string and not a "\r" string is imported. (check-in: 9c2e7612 user: drh tags: trunk)
02:26
Improved handling of constants and especially constant functions in the ORDER BY clause of a query. Do not optimize out "ORDER BY random()". Fix for ticket [65bdeb9739605cc2296]. (check-in: dca1945a user: drh tags: trunk)

Changes to src/expr.c.

1864
1865
1866
1867
1868
1869
1870

1871
1872
1873
1874
1875
1876
1877
1864
1865
1866
1867
1868
1869
1870
1871
1872
1873
1874
1875
1876
1877
1878







+







        sqlite3VdbeAddOp2(v, OP_Integer, 0, dest.iSDParm);
        VdbeComment((v, "Init EXISTS result"));
      }
      sqlite3ExprDelete(pParse->db, pSel->pLimit);
      pSel->pLimit = sqlite3PExpr(pParse, TK_INTEGER, 0, 0,
                                  &sqlite3IntTokens[1]);
      pSel->iLimit = 0;
      dest.wctrlFlags |= WHERE_OPEN_ONCE;
      if( sqlite3Select(pParse, pSel, &dest) ){
        return 0;
      }
      rReg = dest.iSDParm;
      ExprSetVVAProperty(pExpr, EP_NoReduce);
      break;
    }

Changes to src/select.c.

33
34
35
36
37
38
39

40
41
42
43
44
45
46
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47







+







}

/*
** Initialize a SelectDest structure.
*/
void sqlite3SelectDestInit(SelectDest *pDest, int eDest, int iParm){
  pDest->eDest = (u8)eDest;
  pDest->wctrlFlags = 0;
  pDest->iSDParm = iParm;
  pDest->affSdst = 0;
  pDest->iSdst = 0;
  pDest->nSdst = 0;
}


1923
1924
1925
1926
1927
1928
1929

1930
1931
1932
1933
1934
1935
1936
1924
1925
1926
1927
1928
1929
1930
1931
1932
1933
1934
1935
1936
1937
1938







+







  }
  sqlite3VdbeResolveLabel(v, addrCont);

  /* Execute the recursive SELECT taking the single row in Current as
  ** the value for the recursive-table. Store the results in the Queue.
  */
  p->pPrior = 0;
  destQueue.wctrlFlags |= WHERE_OPEN_ONCE;
  sqlite3Select(pParse, p, &destQueue);
  assert( p->pPrior==0 );
  p->pPrior = pSetup;

  /* Keep running the loop until the Queue is empty */
  sqlite3VdbeAddOp2(v, OP_Goto, 0, addrTop);
  sqlite3VdbeResolveLabel(v, addrBreak);
4723
4724
4725
4726
4727
4728
4729
4730


4731
4732
4733
4734
4735
4736
4737
4725
4726
4727
4728
4729
4730
4731

4732
4733
4734
4735
4736
4737
4738
4739
4740







-
+
+







    sDistinct.eTnctType = WHERE_DISTINCT_UNORDERED;
  }else{
    sDistinct.eTnctType = WHERE_DISTINCT_NOOP;
  }

  if( !isAgg && pGroupBy==0 ){
    /* No aggregate functions and no GROUP BY clause */
    u16 wctrlFlags = (sDistinct.isTnct ? WHERE_WANT_DISTINCT : 0);
    u16 wctrlFlags = (sDistinct.isTnct ? WHERE_WANT_DISTINCT : 0) 
                     | pDest->wctrlFlags;

    /* Begin the database scan. */
    pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, pOrderBy, p->pEList,
                               wctrlFlags, 0);
    if( pWInfo==0 ) goto select_end;
    if( sqlite3WhereOutputRowCount(pWInfo) < p->nSelectRow ){
      p->nSelectRow = sqlite3WhereOutputRowCount(pWInfo);

Changes to src/sqliteInt.h.

2071
2072
2073
2074
2075
2076
2077

2078
2079
2080
2081
2082
2083
2084
2071
2072
2073
2074
2075
2076
2077
2078
2079
2080
2081
2082
2083
2084
2085







+







#define WHERE_OMIT_OPEN_CLOSE  0x0010 /* Table cursors are already open */
#define WHERE_FORCE_TABLE      0x0020 /* Do not use an index-only search */
#define WHERE_ONETABLE_ONLY    0x0040 /* Only code the 1st table in pTabList */
#define WHERE_AND_ONLY         0x0080 /* Don't use indices for OR terms */
#define WHERE_GROUPBY          0x0100 /* pOrderBy is really a GROUP BY */
#define WHERE_DISTINCTBY       0x0200 /* pOrderby is really a DISTINCT clause */
#define WHERE_WANT_DISTINCT    0x0400 /* All output needs to be distinct */
#define WHERE_OPEN_ONCE        0x0800 /* Open on first iteration only */

/* Allowed return values from sqlite3WhereIsDistinct()
*/
#define WHERE_DISTINCT_NOOP      0  /* DISTINCT keyword not used */
#define WHERE_DISTINCT_UNIQUE    1  /* No duplicates */
#define WHERE_DISTINCT_ORDERED   2  /* All duplicates are adjacent */
#define WHERE_DISTINCT_UNORDERED 3  /* Duplicates are scattered */
2265
2266
2267
2268
2269
2270
2271

2272
2273
2274
2275
2276
2277
2278
2266
2267
2268
2269
2270
2271
2272
2273
2274
2275
2276
2277
2278
2279
2280







+







/*
** An instance of this object describes where to put of the results of
** a SELECT statement.
*/
struct SelectDest {
  u8 eDest;            /* How to dispose of the results.  On of SRT_* above. */
  char affSdst;        /* Affinity used when eDest==SRT_Set */
  u16 wctrlFlags;      /* Extra flags for sqlite3WhereBegin() */
  int iSDParm;         /* A parameter used by the eDest disposal method */
  int iSdst;           /* Base register where results are written */
  int nSdst;           /* Number of registers allocated */
  ExprList *pOrderBy;  /* Key columns for SRT_Queue and SRT_DistQueue */
};

/*

Changes to src/where.c.

5669
5670
5671
5672
5673
5674
5675

5676
5677
5678
5679
5680
5681
5682
5669
5670
5671
5672
5673
5674
5675
5676
5677
5678
5679
5680
5681
5682
5683







+







  ** searching those tables.
  */
  notReady = ~(Bitmask)0;
  for(ii=0, pLevel=pWInfo->a; ii<nTabList; ii++, pLevel++){
    Table *pTab;     /* Table to open */
    int iDb;         /* Index of database containing table/index */
    struct SrcList_item *pTabItem;
    int addrOnce = 0;

    pTabItem = &pTabList->a[pLevel->iFrom];
    pTab = pTabItem->pTab;
    iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
    pLoop = pLevel->pWLoop;
    if( (pTab->tabFlags & TF_Ephemeral)!=0 || pTab->pSelect ){
      /* Do nothing */
5693
5694
5695
5696
5697
5698
5699




5700
5701
5702
5703
5704
5705
5706
5694
5695
5696
5697
5698
5699
5700
5701
5702
5703
5704
5705
5706
5707
5708
5709
5710
5711







+
+
+
+







    if( (pLoop->wsFlags & WHERE_IDX_ONLY)==0
         && (wctrlFlags & WHERE_OMIT_OPEN_CLOSE)==0 ){
      int op = OP_OpenRead;
      if( pWInfo->okOnePass ){
        op = OP_OpenWrite;
        pWInfo->aiCurOnePass[0] = pTabItem->iCursor;
      };
      if( wctrlFlags & WHERE_OPEN_ONCE ){
        addrOnce = sqlite3CodeOnce(pParse);
        VdbeCoverage(v);
      }
      sqlite3OpenTable(pParse, pTabItem->iCursor, iDb, pTab, op);
      assert( pTabItem->iCursor==pLevel->iTabCur );
      testcase( !pWInfo->okOnePass && pTab->nCol==BMS-1 );
      testcase( !pWInfo->okOnePass && pTab->nCol==BMS );
      if( !pWInfo->okOnePass && pTab->nCol<BMS && HasRowid(pTab) ){
        Bitmask b = pTabItem->colUsed;
        int n = 0;
5736
5737
5738
5739
5740
5741
5742

5743
5744
5745
5746
5747
5748
5749
5741
5742
5743
5744
5745
5746
5747
5748
5749
5750
5751
5752
5753
5754
5755







+







      pLevel->iIdxCur = iIndexCur;
      assert( pIx->pSchema==pTab->pSchema );
      assert( iIndexCur>=0 );
      sqlite3VdbeAddOp3(v, op, iIndexCur, pIx->tnum, iDb);
      sqlite3VdbeSetP4KeyInfo(pParse, pIx);
      VdbeComment((v, "%s", pIx->zName));
    }
    if( addrOnce ) sqlite3VdbeJumpHere(v, addrOnce);
    if( iDb>=0 ) sqlite3CodeVerifySchema(pParse, iDb);
    notReady &= ~getMask(&pWInfo->sMaskSet, pTabItem->iCursor);
  }
  pWInfo->iTop = sqlite3VdbeCurrentAddr(v);
  if( db->mallocFailed ) goto whereBeginError;

  /* Generate the code to do the search.  Each iteration of the for
5893
5894
5895
5896
5897
5898
5899
5900

5901
5902
5903
5904
5905
5906
5907
5899
5900
5901
5902
5903
5904
5905

5906
5907
5908
5909
5910
5911
5912
5913







-
+







    /* Close all of the cursors that were opened by sqlite3WhereBegin.
    ** Except, do not close cursors that will be reused by the OR optimization
    ** (WHERE_OMIT_OPEN_CLOSE).  And do not close the OP_OpenWrite cursors
    ** created for the ONEPASS optimization.
    */
    if( (pTab->tabFlags & TF_Ephemeral)==0
     && pTab->pSelect==0
     && (pWInfo->wctrlFlags & WHERE_OMIT_OPEN_CLOSE)==0
     && (pWInfo->wctrlFlags & (WHERE_OMIT_OPEN_CLOSE|WHERE_OPEN_ONCE))==0
    ){
      int ws = pLoop->wsFlags;
      if( !pWInfo->okOnePass && (ws & WHERE_IDX_ONLY)==0 ){
        sqlite3VdbeAddOp1(v, OP_Close, pTabItem->iCursor);
      }
      if( (ws & WHERE_INDEXED)!=0
       && (ws & (WHERE_IPK|WHERE_AUTO_INDEX))==0