UnQL

Check-in [885cc57b2c]
Login

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

Overview
Comment:Add support for OFFSET and LIMIT on simple SELECT statements.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 885cc57b2c63de12af8b68d9996546785c616ae3
User & Date: dan 2011-07-19 17:59:06
Context
2011-07-19
19:31
Add a scalar length() function to return the length of strings. In preparation for adding aggregation. check-in: a6fb845b35 user: dan tags: trunk
17:59
Add support for OFFSET and LIMIT on simple SELECT statements. check-in: 885cc57b2c user: dan tags: trunk
13:14
Add support for ORDER BY on SELECT queries. check-in: a399717a11 user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/query.c.

169
170
171
172
173
174
175

176
177
178
179
180
181
182
183
184







185
186

187
188
189
190
191
192
193
194
195
196


















































197
198
199
200
201
202
203
204






205
206
207
208
209
210

211
212
213
214
215
216
217



218
219
220
221
222
223
224

225
226
227
228



229
230
231
232
233
234

235

236
237





238
239

240
241
242
243
244
245
246
247
248
249
250
** Rewind a query so that it is pointing at the first row.
*/
int xjd1QueryRewind(Query *p){
  if( p==0 ) return XJD1_OK;
  if( p->eQType==TK_SELECT ){
    xjd1DataSrcRewind(p->u.simple.pFrom);
    p->bUseResultList = 0;

    clearResultList(&p->result);
  }else{
    xjd1QueryRewind(p->u.compound.pLeft);
    p->u.compound.doneLeft = 0;
    xjd1QueryRewind(p->u.compound.pRight);
  }
  return XJD1_OK;
}








static int selectStepUnordered(Query *p){
  int rc;                         /* Return code */

  do{
    rc = xjd1DataSrcStep(p->u.simple.pFrom);
  }while(
    rc==XJD1_ROW
    && (p->u.simple.pWhere!=0 && !xjd1ExprTrue(p->u.simple.pWhere))
  );
  return rc;
}

/*


















































** Advance a query to the next row.  Return XDJ1_DONE if there is no
** next row, or XJD1_ROW if the step was successful.
*/
int xjd1QueryStep(Query *p){
  int rc;
  if( p==0 ) return XJD1_DONE;
  if( p->eQType==TK_SELECT ){







    if( p->u.simple.pOrderBy ){
      /* There is an ORDER BY clause. */
      if( p->bUseResultList==0 ){
        int nKey = p->u.simple.pOrderBy->nEItem + 1;
        ExprList *pOrderBy = p->u.simple.pOrderBy;
        Pool *pPool;

        JsonNode **apKey;

        p->result.nKey = nKey;
        pPool = p->result.pPool = xjd1PoolNew();
        if( !pPool ) return XJD1_NOMEM;
        apKey = xjd1PoolMallocZero(pPool, nKey * sizeof(JsonNode *));
        if( !apKey ) return XJD1_NOMEM;




        while( XJD1_ROW==(rc = selectStepUnordered(p) ) ){
          int i;
          for(i=0; i<pOrderBy->nEItem; i++){
            apKey[i] = xjd1ExprEval(pOrderBy->apEItem[i].pExpr);
          }
          apKey[i] = xjd1QueryDoc(p, 0);


          rc = addToResultList(&p->result, apKey);
          if( rc!=XJD1_OK ) break;
        }



        if( rc!=XJD1_DONE ) return rc;

        sortResultList(&p->result, p->u.simple.pOrderBy);
        p->bUseResultList = 1;
      }else{
        popResultList(&p->result);

      }


      rc = p->result.pItem ? XJD1_ROW : XJD1_DONE;





    }else{
      rc = selectStepUnordered(p);

    }

  }else{
    rc = XJD1_ROW;
    if( !p->u.compound.doneLeft ){
      rc = xjd1QueryStep(p->u.compound.pLeft);
      if( rc==XJD1_DONE ) p->u.compound.doneLeft = 1;
    }
    if( p->u.compound.doneLeft ){
      rc = xjd1QueryStep(p->u.compound.pRight);
    }







>









>
>
>
>
>
>
>


>










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




|



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

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

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

<







169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270





271
272
273


274
275
276
277
278
279
280




281

282
283


284
285
286
287
288
289
290
291


292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307

308
309
310
311
312
313
314
** Rewind a query so that it is pointing at the first row.
*/
int xjd1QueryRewind(Query *p){
  if( p==0 ) return XJD1_OK;
  if( p->eQType==TK_SELECT ){
    xjd1DataSrcRewind(p->u.simple.pFrom);
    p->bUseResultList = 0;
    p->bStarted = 0;
    clearResultList(&p->result);
  }else{
    xjd1QueryRewind(p->u.compound.pLeft);
    p->u.compound.doneLeft = 0;
    xjd1QueryRewind(p->u.compound.pRight);
  }
  return XJD1_OK;
}

/*
** Advance to the next row of the TK_SELECT query passed as the first 
** argument, disregarding any ORDER BY, OFFSET or LIMIT clause.
**
** Return XJD1_ROW if there is such a row, or XJD1_EOF if there is not. Or 
** return an error code if an error occurs.
*/
static int selectStepUnordered(Query *p){
  int rc;                         /* Return code */
  assert( p->eQType==TK_SELECT );
  do{
    rc = xjd1DataSrcStep(p->u.simple.pFrom);
  }while(
    rc==XJD1_ROW
    && (p->u.simple.pWhere!=0 && !xjd1ExprTrue(p->u.simple.pWhere))
  );
  return rc;
}

/*
** Advance to the next row of the TK_SELECT query passed as the first 
** argument, disregarding any OFFSET or LIMIT clause.
**
** Return XJD1_ROW if there is such a row, or XJD1_EOF if there is not. Or 
** return an error code if an error occurs.
*/
static int selectStepOrdered(Query *p){
  int rc;

  if( p->u.simple.pOrderBy ){
    /* There is an ORDER BY clause. */
    if( p->bUseResultList==0 ){
      int nKey = p->u.simple.pOrderBy->nEItem + 1;
      ExprList *pOrderBy = p->u.simple.pOrderBy;
      Pool *pPool;
      JsonNode **apKey;

      p->result.nKey = nKey;
      pPool = p->result.pPool = xjd1PoolNew();
      if( !pPool ) return XJD1_NOMEM;
      apKey = xjd1PoolMallocZero(pPool, nKey * sizeof(JsonNode *));
      if( !apKey ) return XJD1_NOMEM;

      while( XJD1_ROW==(rc = selectStepUnordered(p) ) ){
        int i;
        for(i=0; i<pOrderBy->nEItem; i++){
          apKey[i] = xjd1ExprEval(pOrderBy->apEItem[i].pExpr);
        }
        apKey[i] = xjd1QueryDoc(p, 0);

        rc = addToResultList(&p->result, apKey);
        if( rc!=XJD1_OK ) break;
      }
      if( rc!=XJD1_DONE ) return rc;

      sortResultList(&p->result, p->u.simple.pOrderBy);
      p->bUseResultList = 1;
    }else{
      popResultList(&p->result);
    }

    rc = p->result.pItem ? XJD1_ROW : XJD1_DONE;
  }else{
    rc = selectStepUnordered(p);
  }

  return rc;
}

/*
** Advance a query to the next row.  Return XDJ1_DONE if there is no
** next row, or XJD1_ROW if the step was successful.
*/
int xjd1QueryStep(Query *p){
  int rc = XJD1_ROW;
  if( p==0 ) return XJD1_DONE;
  if( p->eQType==TK_SELECT ){

    /* Calculate the values, if any, of the LIMIT and OFFSET clauses.
    **
    ** TBD: Should this throw an exception if the result of evaluating
    ** either of these clauses cannot be converted to a number?
    */
    if( p->bStarted==0 ){
      if( p->u.simple.pOffset ){





        double rOffset;
        JsonNode *pOffset;



        pOffset = xjd1ExprEval(p->u.simple.pOffset);
        if( 0==xjd1JsonToReal(pOffset, &rOffset) ){
          int nOffset;
          for(nOffset=(int)rOffset; nOffset>0; nOffset--){
            rc = selectStepOrdered(p);
            if( rc!=XJD1_ROW ) break;
          }




        }

        xjd1JsonFree(pOffset);
      }



      p->nLimit = -1;
      if( p->u.simple.pLimit ){
        double rLimit;
        JsonNode *pLimit;

        pLimit = xjd1ExprEval(p->u.simple.pLimit);
        if( 0==xjd1JsonToReal(pLimit, &rLimit) ){


          p->nLimit = (int)rLimit;
        }
        xjd1JsonFree(pLimit);
      }
      p->bStarted = 1;
    }

    if( rc==XJD1_ROW ){
      if( p->nLimit==0 ){
        rc = XJD1_DONE;
      }else{
        rc = selectStepOrdered(p);
        if( p->nLimit>0 ) p->nLimit--;
      }
    }
  }else{

    if( !p->u.compound.doneLeft ){
      rc = xjd1QueryStep(p->u.compound.pLeft);
      if( rc==XJD1_DONE ) p->u.compound.doneLeft = 1;
    }
    if( p->u.compound.doneLeft ){
      rc = xjd1QueryStep(p->u.compound.pRight);
    }

Changes to src/xjd1Int.h.

254
255
256
257
258
259
260


261
262
263
264
265
266
267
      Expr *pHaving;              /* The HAVING clause */
      ExprList *pOrderBy;         /* The ORDER BY clause */
      Expr *pLimit;               /* The LIMIT clause */
      Expr *pOffset;              /* The OFFSET clause */
    } simple;
  } u;



  int bUseResultList;             /* True to read results from Query.result */
  ResultList result;              /* List of query results in sorted order */
};

/* A Data Source is a representation of a term out of the FROM clause. */
struct DataSrc {
  int eDSType;              /* Source type */







>
>







254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
      Expr *pHaving;              /* The HAVING clause */
      ExprList *pOrderBy;         /* The ORDER BY clause */
      Expr *pLimit;               /* The LIMIT clause */
      Expr *pOffset;              /* The OFFSET clause */
    } simple;
  } u;

  int bStarted;                   /* Set to true after first Step() */
  int nLimit;                     /* Stop after returning this many more rows */
  int bUseResultList;             /* True to read results from Query.result */
  ResultList result;              /* List of query results in sorted order */
};

/* A Data Source is a representation of a term out of the FROM clause. */
struct DataSrc {
  int eDSType;              /* Source type */

Changes to test/base05.test.

29
30
31
32
33
34
35
36
37
38
39

















SELECT c1.i FROM c1 ORDER BY c1.z;
.result 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

.testcase 3
SELECT c1.i FROM c1 ORDER BY c1;
.result 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

.testcase 3
SELECT c1.i FROM c1 ORDER BY c1 DESC;
.result 17 16 15 14 13 12 11 10 9 8 7 6 5 4 3 2 1

























|



>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
SELECT c1.i FROM c1 ORDER BY c1.z;
.result 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

.testcase 3
SELECT c1.i FROM c1 ORDER BY c1;
.result 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

.testcase 4
SELECT c1.i FROM c1 ORDER BY c1 DESC;
.result 17 16 15 14 13 12 11 10 9 8 7 6 5 4 3 2 1

.testcase 5
SELECT c1.i FROM c1 ORDER BY c1 DESC LIMIT 16 OFFSET 1;
.result 16 15 14 13 12 11 10 9 8 7 6 5 4 3 2 1

.testcase 6
SELECT c1.i FROM c1 ORDER BY c1 DESC LIMIT 3 OFFSET 3;
.result 14 13 12

.testcase 7
SELECT c1.i FROM c1 ORDER BY c1 ASC LIMIT 3 OFFSET 3;
.result 4 5 6

.testcase 8
SELECT c1.i FROM c1 LIMIT 3 OFFSET 3;
.result 15 17 3