SQLite

Check-in [427409ae10]
Login

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

Overview
Comment:Fix an obscure problem to do with temp register allocation that could occur if more than one simple SELECT within a compound SELECT uses a partial sort.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 427409ae106cdab7892a6b50fe30c5f52de5addc
User & Date: dan 2014-05-01 20:26:48.793
Context
2014-05-02
00:09
Add a comment explaining why WhereLoop cost adjustments are omitted for skip-scan loops. (check-in: 3bc43594aa user: drh tags: trunk)
2014-05-01
20:26
Fix an obscure problem to do with temp register allocation that could occur if more than one simple SELECT within a compound SELECT uses a partial sort. (check-in: 427409ae10 user: dan tags: trunk)
20:24
Add #ifdefs for test coverage. Add a testcase(). (check-in: be2702ce35 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/select.c.
462
463
464
465
466
467
468
469
470

471
472


473
474
475
476
477
478
479
480
481
482
483
484
  Parse *pParse,         /* Parser context */
  SortCtx *pSort,        /* Information about the ORDER BY clause */
  Select *pSelect,       /* The whole SELECT statement */
  int regData            /* Register holding data to be sorted */
){
  Vdbe *v = pParse->pVdbe;
  int nExpr = pSort->pOrderBy->nExpr;
  int regBase = sqlite3GetTempRange(pParse, nExpr+2);
  int regRecord = sqlite3GetTempReg(pParse);

  int nOBSat = pSort->nOBSat;
  int op;


  sqlite3ExprCacheClear(pParse);
  sqlite3ExprCodeExprList(pParse, pSort->pOrderBy, regBase, 0);
  sqlite3VdbeAddOp2(v, OP_Sequence, pSort->iECursor, regBase+nExpr);
  sqlite3ExprCodeMove(pParse, regData, regBase+nExpr+1, 1);
  sqlite3VdbeAddOp3(v, OP_MakeRecord, regBase+nOBSat, nExpr+2-nOBSat, regRecord);
  if( nOBSat>0 ){
    int regPrevKey;   /* The first nOBSat columns of the previous row */
    int addrFirst;    /* Address of the OP_IfNot opcode */
    int addrJmp;      /* Address of the OP_Jump opcode */
    VdbeOp *pOp;      /* Opcode that opens the sorter */
    int nKey;         /* Number of sorting key columns, including OP_Sequence */
    KeyInfo *pKI;     /* Original KeyInfo on the sorter table */







<
|
>


>
>




|







462
463
464
465
466
467
468

469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
  Parse *pParse,         /* Parser context */
  SortCtx *pSort,        /* Information about the ORDER BY clause */
  Select *pSelect,       /* The whole SELECT statement */
  int regData            /* Register holding data to be sorted */
){
  Vdbe *v = pParse->pVdbe;
  int nExpr = pSort->pOrderBy->nExpr;

  int regRecord = ++pParse->nMem;
  int regBase = pParse->nMem+1;
  int nOBSat = pSort->nOBSat;
  int op;

  pParse->nMem += nExpr+2;        /* nExpr+2 registers allocated at regBase */
  sqlite3ExprCacheClear(pParse);
  sqlite3ExprCodeExprList(pParse, pSort->pOrderBy, regBase, 0);
  sqlite3VdbeAddOp2(v, OP_Sequence, pSort->iECursor, regBase+nExpr);
  sqlite3ExprCodeMove(pParse, regData, regBase+nExpr+1, 1);
  sqlite3VdbeAddOp3(v, OP_MakeRecord, regBase+nOBSat, nExpr+2-nOBSat,regRecord);
  if( nOBSat>0 ){
    int regPrevKey;   /* The first nOBSat columns of the previous row */
    int addrFirst;    /* Address of the OP_IfNot opcode */
    int addrJmp;      /* Address of the OP_Jump opcode */
    VdbeOp *pOp;      /* Opcode that opens the sorter */
    int nKey;         /* Number of sorting key columns, including OP_Sequence */
    KeyInfo *pKI;     /* Original KeyInfo on the sorter table */
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
  }
  if( pSort->sortFlags & SORTFLAG_UseSorter ){
    op = OP_SorterInsert;
  }else{
    op = OP_IdxInsert;
  }
  sqlite3VdbeAddOp2(v, op, pSort->iECursor, regRecord);
  if( nOBSat==0 ){
    sqlite3ReleaseTempReg(pParse, regRecord);
    sqlite3ReleaseTempRange(pParse, regBase, nExpr+2);
  }
  if( pSelect->iLimit ){
    int addr1, addr2;
    int iLimit;
    if( pSelect->iOffset ){
      iLimit = pSelect->iOffset+1;
    }else{
      iLimit = pSelect->iLimit;







<
<
<
<







509
510
511
512
513
514
515




516
517
518
519
520
521
522
  }
  if( pSort->sortFlags & SORTFLAG_UseSorter ){
    op = OP_SorterInsert;
  }else{
    op = OP_IdxInsert;
  }
  sqlite3VdbeAddOp2(v, op, pSort->iECursor, regRecord);




  if( pSelect->iLimit ){
    int addr1, addr2;
    int iLimit;
    if( pSelect->iOffset ){
      iLimit = pSelect->iOffset+1;
    }else{
      iLimit = pSelect->iLimit;
Changes to src/vdbe.c.
4228
4229
4230
4231
4232
4233
4234

4235
4236
4237
4238
4239
4240
4241
case OP_SorterData: {
  VdbeCursor *pC;

  pOut = &aMem[pOp->p2];
  pC = p->apCsr[pOp->p1];
  assert( isSorter(pC) );
  rc = sqlite3VdbeSorterRowkey(pC, pOut);

  break;
}

/* Opcode: RowData P1 P2 * * *
** Synopsis: r[P2]=data
**
** Write into register P2 the complete row data for cursor P1.







>







4228
4229
4230
4231
4232
4233
4234
4235
4236
4237
4238
4239
4240
4241
4242
case OP_SorterData: {
  VdbeCursor *pC;

  pOut = &aMem[pOp->p2];
  pC = p->apCsr[pOp->p1];
  assert( isSorter(pC) );
  rc = sqlite3VdbeSorterRowkey(pC, pOut);
  assert( rc!=SQLITE_OK || (pOut->flags & MEM_Blob) );
  break;
}

/* Opcode: RowData P1 P2 * * *
** Synopsis: r[P2]=data
**
** Write into register P2 the complete row data for cursor P1.
Changes to test/selectA.test.
17
18
19
20
21
22
23

24
25
26
27
28
29
30
# explicit sort order and explicit collating secquites) and
# with and without optional LIMIT and OFFSET clauses.
#
# $Id: selectA.test,v 1.6 2008/08/21 14:24:29 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl


ifcapable !compound {
  finish_test
  return
}

do_test selectA-1.0 {







>







17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
# explicit sort order and explicit collating secquites) and
# with and without optional LIMIT and OFFSET clauses.
#
# $Id: selectA.test,v 1.6 2008/08/21 14:24:29 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix selectA

ifcapable !compound {
  finish_test
  return
}

do_test selectA-1.0 {
1305
1306
1307
1308
1309
1310
1311
1312
































































1313
        UNION SELECT a,b,c FROM t3
        ORDER BY y COLLATE NOCASE DESC,x,z)))
      UNION ALL
      SELECT n || '+' FROM xyz WHERE length(n)<5
    )
  SELECT n FROM xyz ORDER BY +n;
} {MAD MAD+ MAD++}

































































finish_test








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

1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
        UNION SELECT a,b,c FROM t3
        ORDER BY y COLLATE NOCASE DESC,x,z)))
      UNION ALL
      SELECT n || '+' FROM xyz WHERE length(n)<5
    )
  SELECT n FROM xyz ORDER BY +n;
} {MAD MAD+ MAD++}

#-------------------------------------------------------------------------
# At one point the following code exposed a temp register reuse problem.
#
proc f {args} { return 1 }
db func f f

do_execsql_test 4.1.1 {
  CREATE TABLE t4(a, b);
  CREATE TABLE t5(c, d);

  INSERT INTO t5 VALUES(1, 'x');
  INSERT INTO t5 VALUES(2, 'x');
  INSERT INTO t4 VALUES(3, 'x');
  INSERT INTO t4 VALUES(4, 'x');

  CREATE INDEX i1 ON t4(a);
  CREATE INDEX i2 ON t5(c);
}

do_eqp_test 4.1.2 {
  SELECT c, d FROM t5 
  UNION ALL
  SELECT a, b FROM t4 WHERE f()==f()
  ORDER BY 1,2
} {
  1 0 0 {SCAN TABLE t5 USING INDEX i2} 
  1 0 0 {USE TEMP B-TREE FOR RIGHT PART OF ORDER BY}
  2 0 0 {SCAN TABLE t4 USING INDEX i1} 
  2 0 0 {USE TEMP B-TREE FOR RIGHT PART OF ORDER BY}
  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)}
}

do_execsql_test 4.1.3 {
  SELECT c, d FROM t5 
  UNION ALL
  SELECT a, b FROM t4 WHERE f()==f()
  ORDER BY 1,2
} {
  1 x 2 x 3 x 4 x
}

do_execsql_test 4.2.1 {
  CREATE TABLE t6(a, b);
  CREATE TABLE t7(c, d);

  INSERT INTO t7 VALUES(2, 9);
  INSERT INTO t6 VALUES(3, 0);
  INSERT INTO t6 VALUES(4, 1);
  INSERT INTO t7 VALUES(5, 6);
  INSERT INTO t6 VALUES(6, 0);
  INSERT INTO t7 VALUES(7, 6);

  CREATE INDEX i6 ON t6(a);
  CREATE INDEX i7 ON t7(c);
}

do_execsql_test 4.2.2 {
  SELECT c, f(d,c,d,c,d) FROM t7
  UNION ALL
  SELECT a, b FROM t6 
  ORDER BY 1,2
} {/2 . 3 . 4 . 5 . 6 . 7 ./}


finish_test