SQLite

Check-in [a2303c7192]
Login

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

Overview
Comment:Add further tests. Fixes so that compilation without ENABLE_STMT_SCANSTATUS works.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | scanstatus
Files: files | file ages | folders
SHA1: a2303c719222f1effb51acc6b37930561148c00c
User & Date: dan 2014-11-03 15:33:17.869
Context
2014-11-03
16:35
Refactor the interface to make it more easily extensible. (check-in: 7955342da4 user: drh tags: scanstatus)
15:33
Add further tests. Fixes so that compilation without ENABLE_STMT_SCANSTATUS works. (check-in: a2303c7192 user: dan tags: scanstatus)
11:25
Remove unused variable from struct WhereInfo. Add some explanatory comments to new code. (check-in: f5313e0c68 user: dan tags: scanstatus)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/test_config.c.
479
480
481
482
483
484
485






486
487
488
489
490
491
492
  Tcl_SetVar2(interp, "sqlite_options", "stat4", "0", TCL_GLOBAL_ONLY);
#endif
#if defined(SQLITE_ENABLE_STAT3) && !defined(SQLITE_ENABLE_STAT4)
  Tcl_SetVar2(interp, "sqlite_options", "stat3", "1", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "stat3", "0", TCL_GLOBAL_ONLY);
#endif







#if !defined(SQLITE_ENABLE_LOCKING_STYLE)
#  if defined(__APPLE__)
#    define SQLITE_ENABLE_LOCKING_STYLE 1
#  else
#    define SQLITE_ENABLE_LOCKING_STYLE 0
#  endif







>
>
>
>
>
>







479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
  Tcl_SetVar2(interp, "sqlite_options", "stat4", "0", TCL_GLOBAL_ONLY);
#endif
#if defined(SQLITE_ENABLE_STAT3) && !defined(SQLITE_ENABLE_STAT4)
  Tcl_SetVar2(interp, "sqlite_options", "stat3", "1", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "stat3", "0", TCL_GLOBAL_ONLY);
#endif

#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
  Tcl_SetVar2(interp, "sqlite_options", "scanstatus", "1", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "scanstatus", "0", TCL_GLOBAL_ONLY);
#endif

#if !defined(SQLITE_ENABLE_LOCKING_STYLE)
#  if defined(__APPLE__)
#    define SQLITE_ENABLE_LOCKING_STYLE 1
#  else
#    define SQLITE_ENABLE_LOCKING_STYLE 0
#  endif
Changes to src/vdbe.c.
5405
5406
5407
5408
5409
5410
5411

5412

5413
5414
5415
5416
5417
5418
5419
    pFrame->apCsr = p->apCsr;
    pFrame->nCursor = p->nCursor;
    pFrame->aOp = p->aOp;
    pFrame->nOp = p->nOp;
    pFrame->token = pProgram->token;
    pFrame->aOnceFlag = p->aOnceFlag;
    pFrame->nOnceFlag = p->nOnceFlag;

    pFrame->anExec = p->anExec;


    pEnd = &VdbeFrameMem(pFrame)[pFrame->nChildMem];
    for(pMem=VdbeFrameMem(pFrame); pMem!=pEnd; pMem++){
      pMem->flags = MEM_Undefined;
      pMem->db = db;
    }
  }else{







>

>







5405
5406
5407
5408
5409
5410
5411
5412
5413
5414
5415
5416
5417
5418
5419
5420
5421
    pFrame->apCsr = p->apCsr;
    pFrame->nCursor = p->nCursor;
    pFrame->aOp = p->aOp;
    pFrame->nOp = p->nOp;
    pFrame->token = pProgram->token;
    pFrame->aOnceFlag = p->aOnceFlag;
    pFrame->nOnceFlag = p->nOnceFlag;
#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
    pFrame->anExec = p->anExec;
#endif

    pEnd = &VdbeFrameMem(pFrame)[pFrame->nChildMem];
    for(pMem=VdbeFrameMem(pFrame); pMem!=pEnd; pMem++){
      pMem->flags = MEM_Undefined;
      pMem->db = db;
    }
  }else{
5434
5435
5436
5437
5438
5439
5440

5441

5442
5443
5444
5445
5446
5447
5448
  p->nMem = pFrame->nChildMem;
  p->nCursor = (u16)pFrame->nChildCsr;
  p->apCsr = (VdbeCursor **)&aMem[p->nMem+1];
  p->aOp = aOp = pProgram->aOp;
  p->nOp = pProgram->nOp;
  p->aOnceFlag = (u8 *)&p->apCsr[p->nCursor];
  p->nOnceFlag = pProgram->nOnce;

  p->anExec = 0;

  pc = -1;
  memset(p->aOnceFlag, 0, p->nOnceFlag);

  break;
}

/* Opcode: Param P1 P2 * * *







>

>







5436
5437
5438
5439
5440
5441
5442
5443
5444
5445
5446
5447
5448
5449
5450
5451
5452
  p->nMem = pFrame->nChildMem;
  p->nCursor = (u16)pFrame->nChildCsr;
  p->apCsr = (VdbeCursor **)&aMem[p->nMem+1];
  p->aOp = aOp = pProgram->aOp;
  p->nOp = pProgram->nOp;
  p->aOnceFlag = (u8 *)&p->apCsr[p->nCursor];
  p->nOnceFlag = pProgram->nOnce;
#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
  p->anExec = 0;
#endif
  pc = -1;
  memset(p->aOnceFlag, 0, p->nOnceFlag);

  break;
}

/* Opcode: Param P1 P2 * * *
Changes to src/vdbeaux.c.
1719
1720
1721
1722
1723
1724
1725

1726

1727
1728
1729
1730
1731
1732
1733
    p->aMem = allocSpace(p->aMem, nMem*sizeof(Mem), &zCsr, zEnd, &nByte);
    p->aVar = allocSpace(p->aVar, nVar*sizeof(Mem), &zCsr, zEnd, &nByte);
    p->apArg = allocSpace(p->apArg, nArg*sizeof(Mem*), &zCsr, zEnd, &nByte);
    p->azVar = allocSpace(p->azVar, nVar*sizeof(char*), &zCsr, zEnd, &nByte);
    p->apCsr = allocSpace(p->apCsr, nCursor*sizeof(VdbeCursor*),
                          &zCsr, zEnd, &nByte);
    p->aOnceFlag = allocSpace(p->aOnceFlag, nOnce, &zCsr, zEnd, &nByte);

    p->anExec = allocSpace(p->anExec, p->nOp*sizeof(i64), &zCsr, zEnd, &nByte);

    if( nByte ){
      p->pFree = sqlite3DbMallocZero(db, nByte);
    }
    zCsr = p->pFree;
    zEnd = &zCsr[nByte];
  }while( nByte && !db->mallocFailed );








>

>







1719
1720
1721
1722
1723
1724
1725
1726
1727
1728
1729
1730
1731
1732
1733
1734
1735
    p->aMem = allocSpace(p->aMem, nMem*sizeof(Mem), &zCsr, zEnd, &nByte);
    p->aVar = allocSpace(p->aVar, nVar*sizeof(Mem), &zCsr, zEnd, &nByte);
    p->apArg = allocSpace(p->apArg, nArg*sizeof(Mem*), &zCsr, zEnd, &nByte);
    p->azVar = allocSpace(p->azVar, nVar*sizeof(char*), &zCsr, zEnd, &nByte);
    p->apCsr = allocSpace(p->apCsr, nCursor*sizeof(VdbeCursor*),
                          &zCsr, zEnd, &nByte);
    p->aOnceFlag = allocSpace(p->aOnceFlag, nOnce, &zCsr, zEnd, &nByte);
#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
    p->anExec = allocSpace(p->anExec, p->nOp*sizeof(i64), &zCsr, zEnd, &nByte);
#endif
    if( nByte ){
      p->pFree = sqlite3DbMallocZero(db, nByte);
    }
    zCsr = p->pFree;
    zEnd = &zCsr[nByte];
  }while( nByte && !db->mallocFailed );

1787
1788
1789
1790
1791
1792
1793

1794

1795
1796
1797
1798
1799
1800
1801
/*
** Copy the values stored in the VdbeFrame structure to its Vdbe. This
** is used, for example, when a trigger sub-program is halted to restore
** control to the main program.
*/
int sqlite3VdbeFrameRestore(VdbeFrame *pFrame){
  Vdbe *v = pFrame->v;

  v->anExec = pFrame->anExec;

  v->aOnceFlag = pFrame->aOnceFlag;
  v->nOnceFlag = pFrame->nOnceFlag;
  v->aOp = pFrame->aOp;
  v->nOp = pFrame->nOp;
  v->aMem = pFrame->aMem;
  v->nMem = pFrame->nMem;
  v->apCsr = pFrame->apCsr;







>

>







1789
1790
1791
1792
1793
1794
1795
1796
1797
1798
1799
1800
1801
1802
1803
1804
1805
/*
** Copy the values stored in the VdbeFrame structure to its Vdbe. This
** is used, for example, when a trigger sub-program is halted to restore
** control to the main program.
*/
int sqlite3VdbeFrameRestore(VdbeFrame *pFrame){
  Vdbe *v = pFrame->v;
#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
  v->anExec = pFrame->anExec;
#endif
  v->aOnceFlag = pFrame->aOnceFlag;
  v->nOnceFlag = pFrame->nOnceFlag;
  v->aOp = pFrame->aOp;
  v->nOp = pFrame->nOp;
  v->aMem = pFrame->aMem;
  v->nMem = pFrame->nMem;
  v->apCsr = pFrame->apCsr;
Changes to src/where.c.
2949
2950
2951
2952
2953
2954
2955
2956
2957
2958
2959
2960
2961
2962
2963
    nEst = sqlite3LogEstToInt(pLoop->nOut);
  }
  sqlite3VdbeScanStatus(
      v, addrExplain, pLvl->addrBody, pLvl->addrVisit, nEst, zObj
  );
}
#else
# define addScanStatus(a, b, c, d)
#endif



/*
** Generate code for the start of the iLevel-th loop in the WHERE clause
** implementation described by pWInfo.







|







2949
2950
2951
2952
2953
2954
2955
2956
2957
2958
2959
2960
2961
2962
2963
    nEst = sqlite3LogEstToInt(pLoop->nOut);
  }
  sqlite3VdbeScanStatus(
      v, addrExplain, pLvl->addrBody, pLvl->addrVisit, nEst, zObj
  );
}
#else
# define addScanStatus(a, b, c, d) ((void)d)
#endif



/*
** Generate code for the start of the iLevel-th loop in the WHERE clause
** implementation described by pWInfo.
Changes to test/scanstatus.test.
9
10
11
12
13
14
15





16
17
18
19
20
21
22
#
#***********************************************************************
#

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






do_execsql_test 1.0 {
  CREATE TABLE t1(a, b);
  CREATE TABLE t2(x, y);
  INSERT INTO t1 VALUES(1, 2);
  INSERT INTO t1 VALUES(3, 4);
  INSERT INTO t2 VALUES('a', 'b');







>
>
>
>
>







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
#
#***********************************************************************
#

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

ifcapable !scanstatus {
  finish_test
  return
}

do_execsql_test 1.0 {
  CREATE TABLE t1(a, b);
  CREATE TABLE t2(x, y);
  INSERT INTO t1 VALUES(1, 2);
  INSERT INTO t1 VALUES(3, 4);
  INSERT INTO t2 VALUES('a', 'b');
261
262
263
264
265
266
267
268




























































































































269
do_execsql_test    4.2.1 { DELETE FROM p1 WHERE x=4 }
do_scanstatus_test 4.2.2 { 
  nLoop 1 nVisit 1 nEst 1 zName sqlite_autoindex_p1_1 
  zExplain {SEARCH TABLE p1 USING INDEX sqlite_autoindex_p1_1 (x=?)}

  nLoop 1 nVisit 3 nEst 524288 zName c1 zExplain {SCAN TABLE c1}
}





























































































































finish_test








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

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
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
do_execsql_test    4.2.1 { DELETE FROM p1 WHERE x=4 }
do_scanstatus_test 4.2.2 { 
  nLoop 1 nVisit 1 nEst 1 zName sqlite_autoindex_p1_1 
  zExplain {SEARCH TABLE p1 USING INDEX sqlite_autoindex_p1_1 (x=?)}

  nLoop 1 nVisit 3 nEst 524288 zName c1 zExplain {SCAN TABLE c1}
}

#-------------------------------------------------------------------------
# Further tests of different scan types.
#
reset_db
proc tochar {i} {
  set alphabet {a b c d e f g h i j k l m n o p q r s t u v w x y z}
  return [lindex $alphabet [expr $i % [llength $alphabet]]]
}
db func tochar tochar
do_execsql_test 5.0 {
  CREATE TABLE t1(a PRIMARY KEY, b, c);
  INSERT INTO t1 VALUES(0, 1, 'a');
  INSERT INTO t1 VALUES(1, 0, 'b');
  INSERT INTO t1 VALUES(2, 1, 'c');
  INSERT INTO t1 VALUES(3, 0, 'd');
  INSERT INTO t1 VALUES(4, 1, 'e');
  INSERT INTO t1 VALUES(5, 0, 'a');
  INSERT INTO t1 VALUES(6, 1, 'b');
  INSERT INTO t1 VALUES(7, 0, 'c');
  INSERT INTO t1 VALUES(8, 1, 'd');
  INSERT INTO t1 VALUES(9, 0, 'e');
  CREATE INDEX t1bc ON t1(b, c);

  CREATE TABLE t2(x, y);
  CREATE INDEX t2xy ON t2(x, y);
  WITH data(i, x, y) AS (
    SELECT 0, 0, tochar(0) 
    UNION ALL
    SELECT i+1, (i+1)%2, tochar(i+1) FROM data WHERE i<500
  ) INSERT INTO t2 SELECT x, y FROM data;

  CREATE TABLE t3(x, y);
  INSERT INTO t3 SELECT * FROM t2;

  ANALYZE;
}

do_execsql_test 5.1.1 {
  SELECT count(*) FROM t1 WHERE a IN (SELECT b FROM t1 AS ii)
} {2}
do_scanstatus_test 5.1.2 { 
  nLoop 1 nVisit 10 nEst 10 zName t1bc 
  zExplain {SCAN TABLE t1 AS ii USING COVERING INDEX t1bc}
  nLoop 1 nVisit 2 nEst 8 zName sqlite_autoindex_t1_1
  zExplain {SEARCH TABLE t1 USING COVERING INDEX sqlite_autoindex_t1_1 (a=?)}
}

do_execsql_test 5.2.1 {
  SELECT count(*) FROM t1 WHERE a IN (0, 1)
} {2}
do_scanstatus_test 5.2.2 { 
  nLoop 1 nVisit 2 nEst 2 zName sqlite_autoindex_t1_1
  zExplain {SEARCH TABLE t1 USING COVERING INDEX sqlite_autoindex_t1_1 (a=?)}
}

do_eqp_test 5.3.1 {
  SELECT count(*) FROM t2 WHERE y = 'j';
} {0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)}}
do_execsql_test 5.3.2 {
  SELECT count(*) FROM t2 WHERE y = 'j';
} {19}
do_scanstatus_test 5.3.3 { 
  nLoop 1 nVisit 19 nEst 56 zName t2xy zExplain
  {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)}
}

do_eqp_test 5.4.1 {
  SELECT count(*) FROM t1, t2 WHERE y = c;
} {
  0 0 0 {SCAN TABLE t1 USING COVERING INDEX t1bc}
  0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)}
}
do_execsql_test 5.4.2 {
  SELECT count(*) FROM t1, t2 WHERE y = c;
} {200}
do_scanstatus_test 5.4.3 { 
  nLoop 1 nVisit 10 nEst 10 zName t1bc 
  zExplain {SCAN TABLE t1 USING COVERING INDEX t1bc}
  nLoop 10 nVisit 200 nEst 56 zName t2xy 
  zExplain {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)}
}

do_eqp_test 5.5.1 {
  SELECT count(*) FROM t1, t3 WHERE y = c;
} {
  0 0 1 {SCAN TABLE t3} 
  0 1 0 {SEARCH TABLE t1 USING AUTOMATIC COVERING INDEX (c=?)}
}
do_execsql_test 5.5.2 {
  SELECT count(*) FROM t1, t3 WHERE y = c;
} {200}
do_scanstatus_test 5.5.3 { 
  nLoop 1 nVisit 501 nEst 480 zName t3 zExplain {SCAN TABLE t3}
  nLoop 501 nVisit 200 nEst 20 zName auto-index zExplain
  {SEARCH TABLE t1 USING AUTOMATIC COVERING INDEX (c=?)}
}

#-------------------------------------------------------------------------
# Virtual table scans
#
ifcapable fts3 {
  do_execsql_test 6.0 {
    CREATE VIRTUAL TABLE ft1 USING fts4;
    INSERT INTO ft1 VALUES('a d c f g h e i f c');
    INSERT INTO ft1 VALUES('g c h b g b f f f g');
    INSERT INTO ft1 VALUES('h h c c h f a e d d');
    INSERT INTO ft1 VALUES('e j i j i e b c f g');
    INSERT INTO ft1 VALUES('g f b g j c h a d f');
    INSERT INTO ft1 VALUES('j i a e g f a i a c');
    INSERT INTO ft1 VALUES('f d g g j j c a h g');
    INSERT INTO ft1 VALUES('b d h a d j j j b i');
    INSERT INTO ft1 VALUES('j e a b j e c b c i');
    INSERT INTO ft1 VALUES('a d e f b j j c g d');
  }
  do_execsql_test 6.1.1 {
    SELECT count(*) FROM ft1 WHERE ft1 MATCH 'd'
  } {6}
  do_scanstatus_test 6.1.2 { 
    nLoop 1 nVisit 6 nEst 24 zName ft1 zExplain 
    {SCAN TABLE ft1 VIRTUAL TABLE INDEX 3:}
  }
}


finish_test