SQLite

Check-in [092b1c5ff5]
Login

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

Overview
Comment:Add tests and fixes for vector operations that use sub-queries with different combinations of LIMIT, OFFSET and ORDER BY clauses.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | rowvalue
Files: files | file ages | folders
SHA1: 092b1c5ff53c9f3cfed079c46e3353d93f99303e
User & Date: dan 2016-08-02 18:50:15.542
Context
2016-08-02
20:45
Add new test file rowvaluefault.test. (check-in: e496b2d639 user: dan tags: rowvalue)
18:50
Add tests and fixes for vector operations that use sub-queries with different combinations of LIMIT, OFFSET and ORDER BY clauses. (check-in: 092b1c5ff5 user: dan tags: rowvalue)
17:45
Fix SQLITE_OMIT_SUBQUERY builds. (check-in: 339f85f414 user: dan tags: rowvalue)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/select.c.
︙︙
869
870
871
872
873
874
875

876
877
878
879
880
881
882
883

    */
    case SRT_Set: {
      if( pSort ){
        /* At first glance you would think we could optimize out the
        ** ORDER BY in this case since the order of entries in the set
        ** does not matter.  But there might be a LIMIT clause, in which
        ** case the order does matter */

        pushOntoSorter(pParse, pSort, p, regResult, regResult, 1, nPrefixReg);
      }else{
        int r1 = sqlite3GetTempReg(pParse);
        assert( sqlite3Strlen30(pDest->zAffSdst)==nResultCol );
        sqlite3VdbeAddOp4(v, OP_MakeRecord, regResult, nResultCol, 
            r1, pDest->zAffSdst, nResultCol);
        sqlite3ExprCacheAffinityChange(pParse, regResult, nResultCol);
        sqlite3VdbeAddOp2(v, OP_IdxInsert, iParm, r1);








>
|








869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884

    */
    case SRT_Set: {
      if( pSort ){
        /* At first glance you would think we could optimize out the
        ** ORDER BY in this case since the order of entries in the set
        ** does not matter.  But there might be a LIMIT clause, in which
        ** case the order does matter */
        pushOntoSorter(
            pParse, pSort, p, regResult, regResult, nResultCol, nPrefixReg);
      }else{
        int r1 = sqlite3GetTempReg(pParse);
        assert( sqlite3Strlen30(pDest->zAffSdst)==nResultCol );
        sqlite3VdbeAddOp4(v, OP_MakeRecord, regResult, nResultCol, 
            r1, pDest->zAffSdst, nResultCol);
        sqlite3ExprCacheAffinityChange(pParse, regResult, nResultCol);
        sqlite3VdbeAddOp2(v, OP_IdxInsert, iParm, r1);
︙︙
1217
1218
1219
1220
1221
1222
1223




1224
1225
1226
1227
1228
1229
1230

    sqlite3VdbeResolveLabel(v, pSort->labelBkOut);
  }
  iTab = pSort->iECursor;
  if( eDest==SRT_Output || eDest==SRT_Coroutine || eDest==SRT_Mem ){
    regRowid = 0;
    regRow = pDest->iSdst;
    nSortData = nColumn;




  }else{
    regRowid = sqlite3GetTempReg(pParse);
    regRow = sqlite3GetTempReg(pParse);
    nSortData = 1;
  }
  nKey = pOrderBy->nExpr - pSort->nOBSat;
  if( pSort->sortFlags & SORTFLAG_UseSorter ){








>
>
>
>








1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235

    sqlite3VdbeResolveLabel(v, pSort->labelBkOut);
  }
  iTab = pSort->iECursor;
  if( eDest==SRT_Output || eDest==SRT_Coroutine || eDest==SRT_Mem ){
    regRowid = 0;
    regRow = pDest->iSdst;
    nSortData = nColumn;
  }else if( eDest==SRT_Set ){
    regRowid = sqlite3GetTempReg(pParse);
    regRow = sqlite3GetTempRange(pParse, nColumn);
    nSortData = nColumn;
  }else{
    regRowid = sqlite3GetTempReg(pParse);
    regRow = sqlite3GetTempReg(pParse);
    nSortData = 1;
  }
  nKey = pOrderBy->nExpr - pSort->nOBSat;
  if( pSort->sortFlags & SORTFLAG_UseSorter ){
︙︙
1281
1282
1283
1284
1285
1286
1287



1288

1289
1290
1291
1292
1293
1294
1295

      }else{
        sqlite3VdbeAddOp1(v, OP_Yield, pDest->iSDParm);
      }
      break;
    }
  }
  if( regRowid ){



    sqlite3ReleaseTempReg(pParse, regRow);

    sqlite3ReleaseTempReg(pParse, regRowid);
  }
  /* The bottom of the loop
  */
  sqlite3VdbeResolveLabel(v, addrContinue);
  if( pSort->sortFlags & SORTFLAG_UseSorter ){
    sqlite3VdbeAddOp2(v, OP_SorterNext, iTab, addr); VdbeCoverage(v);








>
>
>
|
>








1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304

      }else{
        sqlite3VdbeAddOp1(v, OP_Yield, pDest->iSDParm);
      }
      break;
    }
  }
  if( regRowid ){
    if( eDest==SRT_Set ){
      sqlite3ReleaseTempRange(pParse, regRow, nColumn);
    }else{
      sqlite3ReleaseTempReg(pParse, regRow);
    }
    sqlite3ReleaseTempReg(pParse, regRowid);
  }
  /* The bottom of the loop
  */
  sqlite3VdbeResolveLabel(v, addrContinue);
  if( pSort->sortFlags & SORTFLAG_UseSorter ){
    sqlite3VdbeAddOp2(v, OP_SorterNext, iTab, addr); VdbeCoverage(v);
︙︙
Changes to test/rowvalue4.test.
︙︙
88
89
90
91
92
93
94



95
96
97
98
99
100
101
102
103









104
105
106
107
108


























109
110

foreach {nm idx} {
  idx1 {}
  idx2 { CREATE INDEX t2abc ON t2(a, b, c); }
  idx3 { CREATE INDEX t2abc ON t2(a, b DESC, c); }
  idx4 { CREATE INDEX t2abc ON t2(a DESC, b DESC, c DESC); }
  idx5 { CREATE INDEX t2abc ON t2(a ASC, b ASC, c ASC); }
  idx6 { CREATE INDEX t2abc ON t2(a DESC, b, c); }



} {
  drop_all_indexes
  execsql $idx

  foreach {tn where res} {
    1 "(a, b, c) < (2, 2, 2)"  {1 2 3 4 5 6 7 8 9 10 11 12 13}
    2 "(a, b, c) <= (2, 2, 2)" {1 2 3 4 5 6 7 8 9 10 11 12 13 14}
    3 "(a, b, c) > (2, 2, 2)"  {15 16 17 18 19 20 21 22 23 24 25 26 27}
    4 "(a, b, c) >= (2, 2, 2)" {14 15 16 17 18 19 20 21 22 23 24 25 26 27}









  } {
    set result [db eval "SELECT d FROM t2 WHERE $where"]
    do_test 2.$nm.$tn { lsort -integer $result } $res
  }
}


























finish_test









>
>
>









>
>
>
>
>
>
>
>
>


|

|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148

foreach {nm idx} {
  idx1 {}
  idx2 { CREATE INDEX t2abc ON t2(a, b, c); }
  idx3 { CREATE INDEX t2abc ON t2(a, b DESC, c); }
  idx4 { CREATE INDEX t2abc ON t2(a DESC, b DESC, c DESC); }
  idx5 { CREATE INDEX t2abc ON t2(a ASC, b ASC, c ASC); }
  idx6 { CREATE INDEX t2abc ON t2(a DESC, b, c); }
  idx7 { CREATE INDEX t2abc ON t2(a DESC, b DESC) }
  idx8 { CREATE INDEX t2abc ON t2(c, b, a); }
  idx9 { CREATE INDEX t2d ON t2(d); }
} {
  drop_all_indexes
  execsql $idx

  foreach {tn where res} {
    1 "(a, b, c) < (2, 2, 2)"  {1 2 3 4 5 6 7 8 9 10 11 12 13}
    2 "(a, b, c) <= (2, 2, 2)" {1 2 3 4 5 6 7 8 9 10 11 12 13 14}
    3 "(a, b, c) > (2, 2, 2)"  {15 16 17 18 19 20 21 22 23 24 25 26 27}
    4 "(a, b, c) >= (2, 2, 2)" {14 15 16 17 18 19 20 21 22 23 24 25 26 27}
    5 "(a, b, c) >= (2, 2, NULL)" {16 17 18 19 20 21 22 23 24 25 26 27}
    6 "(a, b, c) <= (2, 2, NULL)" {1 2 3 4 5 6 7 8 9 10 11 12}
    7 "(a, b, c) >= (2, NULL, NULL)" {19 20 21 22 23 24 25 26 27}
    8 "(a, b, c) <= (2, NULL, NULL)" {1 2 3 4 5 6 7 8 9}

    9 "(a, b, c) < (SELECT a, b, c FROM t2 WHERE d=14)"  
      {1 2 3 4 5 6 7 8 9 10 11 12 13}

    10 "(a, b, c) = (SELECT a, b, c FROM t2 WHERE d=14)" 14
  } {
    set result [db eval "SELECT d FROM t2 WHERE $where"]
    do_test 2.1.$nm.$tn { lsort -integer $result } $res
  }

  foreach {tn e res} {
    1 "(2, 1) IN (SELECT a, b FROM t2)" 1
    2 "(2, 1) IN (SELECT a, b FROM t2 ORDER BY d)" 1
    3 "(2, 1) IN (SELECT a, b FROM t2 ORDER BY d LIMIT 9)" 0
    4 "(2, 1) IN (SELECT a, b FROM t2 ORDER BY d LIMIT 10)" 1

    5 "(3, 3) = (SELECT a, b FROM t2 ORDER BY d DESC LIMIT 1)" 1
    6 "(3, 3) = (SELECT a, b FROM t2 ORDER BY d ASC LIMIT 1)" 0
    7 "(1, NULL) = (SELECT a, b FROM t2 ORDER BY d ASC LIMIT 1)" {{}}

    8 "(3, 1) = (SELECT b, c FROM t2 ORDER BY d DESC LIMIT 1 OFFSET 2)" 1
    9 "(3, 1) = (SELECT b, c FROM t2 ORDER BY d ASC LIMIT 1 OFFSET 2)" 0
    10 "(1, NULL) = (SELECT b, c FROM t2 ORDER BY d ASC LIMIT 1 OFFSET 2)" {{}}

    11 "(3, 3) = (SELECT max(a), max(b) FROM t2)" 1
    12 "(3, 1) = (SELECT max(a), min(b) FROM t2)" 1
    13 "(NULL, NULL) = (SELECT max(a), min(b) FROM t2)" {{}}

    14 "(2, 1) IN (SELECT a, b FROM t2 ORDER BY d LIMIT 5 OFFSET 11)" 1
    15 "(2, 1) IN (SELECT a, b FROM t2 ORDER BY d LIMIT 5 OFFSET 12)" 0
  } {
    do_execsql_test 2.2.$nm.$tn "SELECT $e" $res
  }
}


finish_test