/ Check-in [25e2cde1]
Login
Home Timeline Branches Tags Tickets Wiki More...

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

Overview
Comment:Add the "queryplanner" test permutation. Continuing refinements to NGQP.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | nextgen-query-plan-exp
Files: files | file ages | folders
SHA1: 25e2cde105a19293bdb9c001b48624e5d7f8c4e5
User & Date: drh 2013-06-12 14:52:39
Context
2013-06-12
17:08
"make test" now passing. check-in: addd7f46 user: drh tags: nextgen-query-plan-exp
14:52
Add the "queryplanner" test permutation. Continuing refinements to NGQP. check-in: 25e2cde1 user: drh tags: nextgen-query-plan-exp
03:48
Continue refining the NGQP check-in: 40567fdd user: drh tags: nextgen-query-plan-exp
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c. 

600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
....
4357
4358
4359
4360
4361
4362
4363
4364
4365
4366
4367
4368
4369
4370
4371
....
4427
4428
4429
4430
4431
4432
4433
4434
4435
4436
4437
4438
4439
4440
4441
....
4472
4473
4474
4475
4476
4477
4478
4479
4480
4481
4482
4483
4484
4485
4486
4487
4488
4489
4490
4491
4492
4493
4494
4495
4496
4497
4498
4499
4500
4501
4502
4503
4504
....
4733
4734
4735
4736
4737
4738
4739

4740
4741
4742
4743
4744
4745
4746
4747
4748

4749
4750
4751
4752
4753
4754
4755
....
5323
5324
5325
5326
5327
5328
5329
5330
5331
5332
5333
5334
5335
5336
5337

    whereSplit(pWC, pExpr->pRight, op);
  }
}

/*
** Initialize an expression mask set (a WhereMaskSet object)
*/
#define initMaskSet(P)  memset(P, 0, sizeof(*P))

/*
** Return the bitmask for the given cursor number.  Return 0 if
** iCursor is not in the set.
*/
static Bitmask getMask(WhereMaskSet *pMaskSet, int iCursor){
  int i;
................................................................................
  return 0;
}

/*
** Return a bitmask where 1s indicate that the corresponding column of
** the table is used by an index.  Only the first 63 columns are considered.
*/
static Bitmask columnsUsedByIndex(Index *pIdx){
  Bitmask m = 0;
  int j;
  for(j=pIdx->nColumn-1; j>=0; j--){
    int x = pIdx->aiColumn[j];
    if( x<BMS-1 ) m |= MASKBIT(x);
  }
  return m;
................................................................................
    pProbe = &sPk;
  }
  rSize = whereCostFromInt(pSrc->pTab->nRowEst);
  rLogSize = estLog(rSize);

  /* Automatic indexes */
  if( !pBuilder->pBest
//   && pTabList->nSrc>1
   && (pWInfo->pParse->db->flags & SQLITE_AutoIndex)!=0 
   && !pSrc->viaCoroutine
   && !pSrc->notIndexed
   && !pSrc->isCorrelated
  ){
    /* Generate auto-index WhereLoops */
    WhereClause *pWC = pBuilder->pWC;
................................................................................
    if( pProbe->tnum<=0 ){
      /* Integer primary key index */
      pNew->wsFlags = WHERE_IPK;

      /* Full table scan */
      pNew->iSortIdx = b ? iSortIdx : 0;
      pNew->nOut = rSize;
      pNew->rRun = whereCostAdd(rSize,rLogSize) + 16 + b*4;
      rc = whereLoopInsert(pBuilder, pNew);
      if( rc ) break;
    }else{
      Bitmask m = pSrc->colUsed & ~columnsUsedByIndex(pProbe);
      pNew->wsFlags = (m==0) ? (WHERE_IDX_ONLY|WHERE_INDEXED) : WHERE_INDEXED;

      /* Full scan via index */
      if( (m==0 || b)
       && pProbe->bUnordered==0
       && (pWInfo->wctrlFlags & WHERE_ONEPASS_DESIRED)==0
       && sqlite3GlobalConfig.bUseCis
       && OptimizationEnabled(pWInfo->pParse->db, SQLITE_CoverIdxScan)
      ){
        pNew->iSortIdx = b ? iSortIdx : 0;
        pNew->nOut = rSize;
        pNew->rRun = whereCostAdd(rSize,rLogSize);
        if( m!=0 ) pNew->rRun += rLogSize;
        if( b ) pNew->rRun--;
        rc = whereLoopInsert(pBuilder, pNew);
        if( rc ) break;
      }
    }
    rc = whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, 0);

    /* If there was an INDEXED BY clause, then only that one index is
................................................................................
        if( sBest.maskSelf==0 ) break;
        assert( sBest.rSetup==0 );
        rTotal = whereCostAdd(rTotal, sBest.rRun);
        nRow = whereCostAdd(nRow, sBest.nOut);
        prereq |= sBest.prereq;
      }
      assert( pNew->nLSlot>=1 );

      pNew->nLTerm = 1;
      pNew->aLTerm[0] = pTerm;
      pNew->wsFlags = WHERE_MULTI_OR;
      pNew->rSetup = 0;
      pNew->rRun = rTotal;
      pNew->nOut = nRow;
      pNew->prereq = prereq;
      memset(&pNew->u, 0, sizeof(pNew->u));
      rc = whereLoopInsert(pBuilder, pNew);

      whereLoopClear(pWInfo->pParse->db, &sBest);
    }
  }
  return rc;
}

/*
................................................................................
        pTerm = findTerm(pWC, iCur, pIdx->aiColumn[j], 0, WO_EQ, pIdx);
        if( pTerm==0 ) break;
        whereLoopResize(pWInfo->pParse->db, pLoop, j);
        pLoop->aLTerm[j] = pTerm;
      }
      if( j!=pIdx->nColumn ) continue;
      pLoop->wsFlags = WHERE_COLUMN_EQ|WHERE_ONEROW|WHERE_INDEXED;
      if( (pItem->colUsed & ~columnsUsedByIndex(pIdx))==0 ){
        pLoop->wsFlags |= WHERE_IDX_ONLY;
      }
      pLoop->nLTerm = j;
      pLoop->u.btree.nEq = j;
      pLoop->u.btree.pIndex = pIdx;
      pLoop->rRun = 39;  /* 39 == whereCostFromInt(15) */
      break;








|







 







|







 







<







 







|



|












|
<







 







>
|
|
|
|
|
|
|
|
|
>







 







|








600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
....
4357
4358
4359
4360
4361
4362
4363
4364
4365
4366
4367
4368
4369
4370
4371
....
4427
4428
4429
4430
4431
4432
4433

4434
4435
4436
4437
4438
4439
4440
....
4471
4472
4473
4474
4475
4476
4477
4478
4479
4480
4481
4482
4483
4484
4485
4486
4487
4488
4489
4490
4491
4492
4493
4494
4495

4496
4497
4498
4499
4500
4501
4502
....
4731
4732
4733
4734
4735
4736
4737
4738
4739
4740
4741
4742
4743
4744
4745
4746
4747
4748
4749
4750
4751
4752
4753
4754
4755
....
5323
5324
5325
5326
5327
5328
5329
5330
5331
5332
5333
5334
5335
5336
5337

    whereSplit(pWC, pExpr->pRight, op);
  }
}

/*
** Initialize an expression mask set (a WhereMaskSet object)
*/
#define initMaskSet(P)  (P)->n=0

/*
** Return the bitmask for the given cursor number.  Return 0 if
** iCursor is not in the set.
*/
static Bitmask getMask(WhereMaskSet *pMaskSet, int iCursor){
  int i;
................................................................................
  return 0;
}

/*
** Return a bitmask where 1s indicate that the corresponding column of
** the table is used by an index.  Only the first 63 columns are considered.
*/
static Bitmask columnsInIndex(Index *pIdx){
  Bitmask m = 0;
  int j;
  for(j=pIdx->nColumn-1; j>=0; j--){
    int x = pIdx->aiColumn[j];
    if( x<BMS-1 ) m |= MASKBIT(x);
  }
  return m;
................................................................................
    pProbe = &sPk;
  }
  rSize = whereCostFromInt(pSrc->pTab->nRowEst);
  rLogSize = estLog(rSize);

  /* Automatic indexes */
  if( !pBuilder->pBest

   && (pWInfo->pParse->db->flags & SQLITE_AutoIndex)!=0 
   && !pSrc->viaCoroutine
   && !pSrc->notIndexed
   && !pSrc->isCorrelated
  ){
    /* Generate auto-index WhereLoops */
    WhereClause *pWC = pBuilder->pWC;
................................................................................
    if( pProbe->tnum<=0 ){
      /* Integer primary key index */
      pNew->wsFlags = WHERE_IPK;

      /* Full table scan */
      pNew->iSortIdx = b ? iSortIdx : 0;
      pNew->nOut = rSize;
      pNew->rRun = whereCostAdd(rSize,rLogSize) + 16 - b;
      rc = whereLoopInsert(pBuilder, pNew);
      if( rc ) break;
    }else{
      Bitmask m = pSrc->colUsed & ~columnsInIndex(pProbe);
      pNew->wsFlags = (m==0) ? (WHERE_IDX_ONLY|WHERE_INDEXED) : WHERE_INDEXED;

      /* Full scan via index */
      if( (m==0 || b)
       && pProbe->bUnordered==0
       && (pWInfo->wctrlFlags & WHERE_ONEPASS_DESIRED)==0
       && sqlite3GlobalConfig.bUseCis
       && OptimizationEnabled(pWInfo->pParse->db, SQLITE_CoverIdxScan)
      ){
        pNew->iSortIdx = b ? iSortIdx : 0;
        pNew->nOut = rSize;
        pNew->rRun = whereCostAdd(rSize,rLogSize);
        pNew->rRun += ((m!=0) ? rLogSize : 10) - b;

        rc = whereLoopInsert(pBuilder, pNew);
        if( rc ) break;
      }
    }
    rc = whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, 0);

    /* If there was an INDEXED BY clause, then only that one index is
................................................................................
        if( sBest.maskSelf==0 ) break;
        assert( sBest.rSetup==0 );
        rTotal = whereCostAdd(rTotal, sBest.rRun);
        nRow = whereCostAdd(nRow, sBest.nOut);
        prereq |= sBest.prereq;
      }
      assert( pNew->nLSlot>=1 );
      if( sBest.maskSelf ){
        pNew->nLTerm = 1;
        pNew->aLTerm[0] = pTerm;
        pNew->wsFlags = WHERE_MULTI_OR;
        pNew->rSetup = 0;
        pNew->rRun = rTotal;
        pNew->nOut = nRow;
        pNew->prereq = prereq;
        memset(&pNew->u, 0, sizeof(pNew->u));
        rc = whereLoopInsert(pBuilder, pNew);
      }
      whereLoopClear(pWInfo->pParse->db, &sBest);
    }
  }
  return rc;
}

/*
................................................................................
        pTerm = findTerm(pWC, iCur, pIdx->aiColumn[j], 0, WO_EQ, pIdx);
        if( pTerm==0 ) break;
        whereLoopResize(pWInfo->pParse->db, pLoop, j);
        pLoop->aLTerm[j] = pTerm;
      }
      if( j!=pIdx->nColumn ) continue;
      pLoop->wsFlags = WHERE_COLUMN_EQ|WHERE_ONEROW|WHERE_INDEXED;
      if( (pItem->colUsed & ~columnsInIndex(pIdx))==0 ){
        pLoop->wsFlags |= WHERE_IDX_ONLY;
      }
      pLoop->nLTerm = j;
      pLoop->u.btree.nEq = j;
      pLoop->u.btree.pIndex = pIdx;
      pLoop->rRun = 39;  /* 39 == whereCostFromInt(15) */
      break;

Changes to test/permutations.test. 

208
209
210
211
212
213
214













































































215
216
217
218
219
220
221

  sqlite3_shutdown
  install_malloc_faultsim 0
  sqlite3_initialize
  autoinstall_test_functions
} -shutdown {
  unset -nocomplain ::G(valgrind)
}














































































lappend ::testsuitelist xxx
#-------------------------------------------------------------------------
# Define the coverage related test suites:
#
#   coverage-wal
#








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








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

  sqlite3_shutdown
  install_malloc_faultsim 0
  sqlite3_initialize
  autoinstall_test_functions
} -shutdown {
  unset -nocomplain ::G(valgrind)
}

test_suite "queryplanner" -prefix "" -description {
  Tests of the query planner and query optimizer
} -files {
  alter2.test alter3.test alter4.test alter.test analyze3.test
  analyze4.test analyze5.test analyze6.test analyze7.test analyze8.test
  analyze.test attach2.test attach3.test attach4.test attachmalloc.test
  attach.test autoinc.test autoindex1.test between.test cast.test
  check.test closure01.test coalesce.test collate1.test collate2.test
  collate3.test collate4.test collate5.test collate6.test collate7.test
  collate8.test collate9.test collateA.test colmeta.test colname.test
  conflict.test count.test coveridxscan.test createtab.test cse.test
  date.test dbstatus2.test dbstatus.test default.test delete2.test
  delete3.test delete.test descidx1.test descidx2.test descidx3.test
  distinctagg.test distinct.test e_createtable.test e_delete.test
  e_droptrigger.test e_dropview.test e_expr.test e_fkey.test e_insert.test
  eqp.test e_reindex.test e_resolve.test e_select2.test e_select.test
  e_update.test exists.test expr.test fkey1.test fkey2.test fkey3.test
  fkey4.test fkey5.test fkey_malloc.test format4.test func2.test
  func3.test func.test fuzz2.test fuzz3.test fuzzer1.test fuzz-oss1.test
  fuzz.test in2.test in3.test in4.test in5.test index2.test index3.test
  index4.test index5.test indexedby.test indexfault.test index.test
  insert2.test insert3.test insert4.test insert5.test insert.test
  instr.test in.test intpkey.test join2.test join3.test join4.test
  join5.test join6.test join.test like2.test like.test limit.test
  minmax2.test minmax3.test minmax4.test minmax.test misc1.test misc2.test
  misc3.test misc4.test misc5.test misc6.test misc7.test orderby1.test
  orderby2.test orderby3.test orderby4.test randexpr1.test regexp1.test
  reindex.test rowhash.test rowid.test schema2.test schema3.test
  schema4.test schema5.test schema.test securedel2.test securedel.test
  select1.test select2.test select3.test select4.test select5.test
  select6.test select7.test select8.test select9.test selectA.test
  selectB.test selectC.test selectD.test selectE.test sidedelete.test
  sort.test spellfix.test subquery2.test subquery.test subselect.test
  substr.test tkt-02a8e81d44.test tkt1435.test tkt1443.test tkt1444.test
  tkt1449.test tkt1473.test tkt1501.test tkt1512.test tkt1514.test
  tkt1536.test tkt1537.test tkt1567.test tkt1644.test tkt1667.test
  tkt1873.test tkt2141.test tkt2192.test tkt2213.test tkt2251.test
  tkt2285.test tkt2332.test tkt2339.test tkt2391.test tkt2409.test
  tkt2450.test tkt2565.test tkt2640.test tkt2643.test tkt2686.test
  tkt-26ff0c2d1e.test tkt2767.test tkt2817.test tkt2820.test tkt2822.test
  tkt2832.test tkt2854.test tkt2920.test tkt2927.test tkt2942.test
  tkt-2a5629202f.test tkt-2d1a5c67d.test tkt-2ea2425d34.test tkt3080.test
  tkt3093.test tkt3121.test tkt-31338dca7e.test tkt-313723c356.test
  tkt3201.test tkt3292.test tkt3298.test tkt3334.test tkt3346.test
  tkt3357.test tkt3419.test tkt3424.test tkt3442.test tkt3457.test
  tkt3461.test tkt3493.test tkt3508.test tkt3522.test tkt3527.test
  tkt3541.test tkt3554.test tkt3581.test tkt35xx.test tkt3630.test
  tkt3718.test tkt3731.test tkt3757.test tkt3761.test tkt3762.test
  tkt3773.test tkt3791.test tkt3793.test tkt3810.test tkt3824.test
  tkt3832.test tkt3838.test tkt3841.test tkt-385a5b56b9.test tkt3871.test
  tkt3879.test tkt-38cb5df375.test tkt3911.test tkt3918.test tkt3922.test
  tkt3929.test tkt3935.test tkt3992.test tkt3997.test tkt-3998683a16.test
  tkt-3a77c9714e.test tkt-3fe897352e.test tkt4018.test tkt-4a03edc4c8.test
  tkt-4dd95f6943.test tkt-54844eea3f.test tkt-5d863f876e.test
  tkt-5e10420e8d.test tkt-5ee23731f.test tkt-6bfb98dfc0.test
  tkt-752e1646fc.test tkt-78e04e52ea.test tkt-7a31705a7e6.test
  tkt-7bbfb7d442.test tkt-80ba201079.test tkt-80e031a00f.test
  tkt-8454a207b9.test tkt-91e2e8ba6f.test tkt-94c04eaadb.test
  tkt-9d68c883.test tkt-a7b7803e.test tkt-b1d3a2e531.test
  tkt-b351d95f9.test tkt-b72787b1.test tkt-bd484a090c.test
  tkt-bdc6bbbb38.test tkt-c48d99d690.test tkt-cbd054fa6b.test
  tkt-d11f09d36e.test tkt-d635236375.test tkt-d82e3f3721.test
  tkt-f3e5abed55.test tkt-f777251dc7a.test tkt-f7b4edec.test
  tkt-f973c7ac31.test tkt-fa7bf5ec.test tkt-fc62af4523.test
  tkt-fc7bd6358f.test trigger1.test trigger2.test trigger3.test
  trigger4.test trigger5.test trigger6.test trigger7.test trigger8.test
  trigger9.test triggerA.test triggerB.test triggerC.test triggerD.test
  types2.test types3.test types.test unique.test unordered.test
  update.test view.test vtab1.test vtab2.test vtab3.test vtab4.test
  vtab5.test vtab6.test vtab7.test vtab8.test vtab9.test vtab_alter.test
  vtabA.test vtabB.test vtabC.test vtabD.test vtab_err.test vtabE.test
  vtabF.test where2.test where3.test where4.test where5.test where6.test
  where7.test where8m.test where8.test where9.test whereA.test whereB.test
  whereC.test whereD.test whereE.test whereF.test wherelimit.test
  where.test
}

lappend ::testsuitelist xxx
#-------------------------------------------------------------------------
# Define the coverage related test suites:
#
#   coverage-wal
#

Changes to test/where8.test. 

264
265
266
267
268
269
270
271
272
273
274
275
276
277
278

    SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND a<5 ORDER BY a
  }
} {1 1 2 2 3 3 4 2 4 4 0 0}
do_test where8-3.12 {
  execsql_status {
    SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND +a<5 ORDER BY a
  }
} {1 1 2 2 3 3 4 2 4 4 0 0}
do_test where8-3.13 {
  execsql_status {
    SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND +a<5
  }
} {1 1 2 2 3 3 4 2 4 4 9 0}

do_test where8-3.14 {








|








264
265
266
267
268
269
270
271
272
273
274
275
276
277
278

    SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND a<5 ORDER BY a
  }
} {1 1 2 2 3 3 4 2 4 4 0 0}
do_test where8-3.12 {
  execsql_status {
    SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND +a<5 ORDER BY a
  }
} {1 1 2 2 3 3 4 2 4 4 9 0}
do_test where8-3.13 {
  execsql_status {
    SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND +a<5
  }
} {1 1 2 2 3 3 4 2 4 4 9 0}

do_test where8-3.14 {

Changes to test/where9.test. 

416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
...
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
...
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
...
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
...
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
...
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
...
764
765
766
767
768
769
770
771








772
773
774
775
776
777
778
779
780
781
782
783








784
785
786
787
788
789
790

do_test where9-4.5 {
  catchsql {
    SELECT a FROM t1 INDEXED BY t1b
     WHERE +b>1000
       AND (c=31031 OR d IS NULL)
     ORDER BY +a
  }
} {1 {cannot use index: t1b}}
do_test where9-4.6 {
  count_steps {
    SELECT a FROM t1 NOT INDEXED
     WHERE b>1000
       AND (c=31031 OR d IS NULL)
     ORDER BY +a
  }
................................................................................
do_test where9-4.7 {
  catchsql {
    SELECT a FROM t1 INDEXED BY t1c
     WHERE b>1000
       AND (c=31031 OR d IS NULL)
     ORDER BY +a
  }
} {1 {cannot use index: t1c}}
do_test where9-4.8 {
  catchsql {
    SELECT a FROM t1 INDEXED BY t1d
     WHERE b>1000
       AND (c=31031 OR d IS NULL)
     ORDER BY +a
  }
} {1 {cannot use index: t1d}}

ifcapable explain {
  # The (c=31031 OR d IS NULL) clause is preferred over b>1000 because
  # the former is an equality test which is expected to return fewer rows.
  #
  do_execsql_test where9-5.1 {
    EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c=31031 OR d IS NULL)
................................................................................
  count_steps {
    BEGIN;
    DELETE FROM t1
     WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL)
        OR (b NOT NULL AND c IS NULL AND d NOT NULL)
        OR (b NOT NULL AND c NOT NULL AND d IS NULL)
  }
} {scan 98 sort 0}   ;# DELETEs rows 90 91 92 97
do_test where9-6.3.6 {
  db eval {
    SELECT count(*) FROM t1 UNION ALL
    SELECT a FROM t1 WHERE a BETWEEN 85 AND 100;
    ROLLBACK;
  }
} {95 85 86 87 88 89 93 94 95 96 98 99}
................................................................................
  count_steps {
    BEGIN;
    UPDATE t1 SET a=a+100
     WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
        OR (b NOT NULL AND +c IS NULL AND d NOT NULL)
        OR (b NOT NULL AND c NOT NULL AND d IS NULL)
  }
} {scan 98 sort 0}   ;# Add 100 to rowids 90 91 92 97
do_test where9-6.3.8 {
  db eval {
    SELECT count(*) FROM t1 UNION ALL
    SELECT a FROM t1 WHERE a BETWEEN 85 AND 100;
    ROLLBACK;
  }
} {99 85 86 87 88 89 93 94 95 96 98 99}
................................................................................
  count_steps {
    BEGIN;
    DELETE FROM t1
     WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
        OR (b NOT NULL AND +c IS NULL AND d NOT NULL)
        OR (b NOT NULL AND c NOT NULL AND d IS NULL)
  }
} {scan 98 sort 0}   ;# DELETEs rows 90 91 92 97
do_test where9-6.6.2 {
  db eval {
    SELECT count(*) FROM t1 UNION ALL
    SELECT a FROM t1 WHERE a BETWEEN 85 AND 100;
    ROLLBACK;
  }
} {95 85 86 87 88 89 93 94 95 96 98 99}
................................................................................
  count_steps {
    BEGIN;
    UPDATE t1 SET a=a+100
     WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
        OR (b NOT NULL AND +c IS NULL AND d NOT NULL)
        OR (b NOT NULL AND c NOT NULL AND d IS NULL)
  }
} {scan 98 sort 0}   ;# Add 100 to rowids 90 91 92 97
do_test where9-6.6.4 {
  db eval {
    SELECT count(*) FROM t1 UNION ALL
    SELECT a FROM t1 WHERE a BETWEEN 85 AND 200;
    ROLLBACK;
  }
} {99 85 86 87 88 89 93 94 95 96 98 99 190 191 192 197}
................................................................................
    ROLLBACK;
  }
} {99 85 86 87 88 89 93 94 95 96 98 99 190 191 192 197}

do_test where9-6.8.1 {
  catchsql {
    DELETE FROM t1 INDEXED BY t1b
     WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)








        OR (b NOT NULL AND c IS NULL AND d NOT NULL)
        OR (b NOT NULL AND c NOT NULL AND d IS NULL)
  }
} {1 {cannot use index: t1b}}
do_test where9-6.8.2 {
  catchsql {
    UPDATE t1 INDEXED BY t1b SET a=a+100
     WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
        OR (b NOT NULL AND c IS NULL AND d NOT NULL)
        OR (b NOT NULL AND c NOT NULL AND d IS NULL)
  }
} {1 {cannot use index: t1b}}









############################################################################
# Test cases where terms inside an OR series are combined with AND terms
# external to the OR clause.  In other words, cases where
#
#              x AND (y OR z)
#








|







 







|







|







 







|







 







|







 







|







 







|







 







|
>
>
>
>
>
>
>
>



|
|






|
>
>
>
>
>
>
>
>








416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
...
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
...
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
...
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
...
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
...
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
...
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806

do_test where9-4.5 {
  catchsql {
    SELECT a FROM t1 INDEXED BY t1b
     WHERE +b>1000
       AND (c=31031 OR d IS NULL)
     ORDER BY +a
  }
} {1 {no query solution}}
do_test where9-4.6 {
  count_steps {
    SELECT a FROM t1 NOT INDEXED
     WHERE b>1000
       AND (c=31031 OR d IS NULL)
     ORDER BY +a
  }
................................................................................
do_test where9-4.7 {
  catchsql {
    SELECT a FROM t1 INDEXED BY t1c
     WHERE b>1000
       AND (c=31031 OR d IS NULL)
     ORDER BY +a
  }
} {1 {no query solution}}
do_test where9-4.8 {
  catchsql {
    SELECT a FROM t1 INDEXED BY t1d
     WHERE b>1000
       AND (c=31031 OR d IS NULL)
     ORDER BY +a
  }
} {1 {no query solution}}

ifcapable explain {
  # The (c=31031 OR d IS NULL) clause is preferred over b>1000 because
  # the former is an equality test which is expected to return fewer rows.
  #
  do_execsql_test where9-5.1 {
    EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c=31031 OR d IS NULL)
................................................................................
  count_steps {
    BEGIN;
    DELETE FROM t1
     WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL)
        OR (b NOT NULL AND c IS NULL AND d NOT NULL)
        OR (b NOT NULL AND c NOT NULL AND d IS NULL)
  }
} {scan 0 sort 0}   ;# DELETEs rows 90 91 92 97
do_test where9-6.3.6 {
  db eval {
    SELECT count(*) FROM t1 UNION ALL
    SELECT a FROM t1 WHERE a BETWEEN 85 AND 100;
    ROLLBACK;
  }
} {95 85 86 87 88 89 93 94 95 96 98 99}
................................................................................
  count_steps {
    BEGIN;
    UPDATE t1 SET a=a+100
     WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
        OR (b NOT NULL AND +c IS NULL AND d NOT NULL)
        OR (b NOT NULL AND c NOT NULL AND d IS NULL)
  }
} {scan 0 sort 0}   ;# Add 100 to rowids 90 91 92 97
do_test where9-6.3.8 {
  db eval {
    SELECT count(*) FROM t1 UNION ALL
    SELECT a FROM t1 WHERE a BETWEEN 85 AND 100;
    ROLLBACK;
  }
} {99 85 86 87 88 89 93 94 95 96 98 99}
................................................................................
  count_steps {
    BEGIN;
    DELETE FROM t1
     WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
        OR (b NOT NULL AND +c IS NULL AND d NOT NULL)
        OR (b NOT NULL AND c NOT NULL AND d IS NULL)
  }
} {scan 0 sort 0}   ;# DELETEs rows 90 91 92 97
do_test where9-6.6.2 {
  db eval {
    SELECT count(*) FROM t1 UNION ALL
    SELECT a FROM t1 WHERE a BETWEEN 85 AND 100;
    ROLLBACK;
  }
} {95 85 86 87 88 89 93 94 95 96 98 99}
................................................................................
  count_steps {
    BEGIN;
    UPDATE t1 SET a=a+100
     WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
        OR (b NOT NULL AND +c IS NULL AND d NOT NULL)
        OR (b NOT NULL AND c NOT NULL AND d IS NULL)
  }
} {scan 0 sort 0}   ;# Add 100 to rowids 90 91 92 97
do_test where9-6.6.4 {
  db eval {
    SELECT count(*) FROM t1 UNION ALL
    SELECT a FROM t1 WHERE a BETWEEN 85 AND 200;
    ROLLBACK;
  }
} {99 85 86 87 88 89 93 94 95 96 98 99 190 191 192 197}
................................................................................
    ROLLBACK;
  }
} {99 85 86 87 88 89 93 94 95 96 98 99 190 191 192 197}

do_test where9-6.8.1 {
  catchsql {
    DELETE FROM t1 INDEXED BY t1b
     WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL)
        OR (b NOT NULL AND c IS NULL AND d NOT NULL)
        OR (b NOT NULL AND c NOT NULL AND d IS NULL)
  }
} {1 {no query solution}}
do_test where9-6.8.2 {
  catchsql {
    UPDATE t1 INDEXED BY t1b SET a=a+100
     WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL)
        OR (b NOT NULL AND c IS NULL AND d NOT NULL)
        OR (b NOT NULL AND c NOT NULL AND d IS NULL)
  }
} {1 {no query solution}}
do_test where9-6.8.3 {
  catchsql {
    UPDATE t1 INDEXED BY t1b SET a=a+100
     WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
        OR (b NOT NULL AND c IS NULL AND d NOT NULL)
        OR (b NOT NULL AND c NOT NULL AND d IS NULL)
  }
} {0 {}}
do_test where9-6.8.4 {
  catchsql {
    DELETE FROM t1 INDEXED BY t1b
     WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
        OR (b NOT NULL AND c IS NULL AND d NOT NULL)
        OR (b NOT NULL AND c NOT NULL AND d IS NULL)
  }
} {0 {}}

############################################################################
# Test cases where terms inside an OR series are combined with AND terms
# external to the OR clause.  In other words, cases where
#
#              x AND (y OR z)
#

Changes to test/whereF.test. 

42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
..
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
..
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
...
105
106
107
108
109
110
111
112
113
114
115

#
# In order to make them more predictable, automatic indexes are turned off for
# the tests in this file.
#

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

do_execsql_test 1.0 {
  PRAGMA automatic_index = 0;
  CREATE TABLE t1(a, b, c);
  CREATE TABLE t2(d, e, f);
  CREATE UNIQUE INDEX i1 ON t1(a);
  CREATE UNIQUE INDEX i2 ON t2(d);
................................................................................
foreach {tn sql} {
  1 "SELECT * FROM t1,           t2 WHERE t1.a=t2.e AND t2.d<t1.b AND t1.c!=10"
  2 "SELECT * FROM t2,           t1 WHERE t1.a=t2.e AND t2.d<t1.b AND t1.c!=10"
  3 "SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a=t2.e AND t2.d<t1.b AND t1.c!=10"
} {
  do_test 1.$tn {
    db eval "EXPLAIN QUERY PLAN $sql"
   } {/.*SCAN TABLE t2 .*SEARCH TABLE t1 .*/}
}

do_execsql_test 2.0 {
  DROP TABLE t1;
  DROP TABLE t2;
  CREATE TABLE t1(a, b, c);
  CREATE TABLE t2(d, e, f);
................................................................................
foreach {tn sql} {
  1 "SELECT * FROM t1,           t2 WHERE t1.a>? AND t2.d>t1.c AND t1.b=t2.e"
  2 "SELECT * FROM t2,           t1 WHERE t1.a>? AND t2.d>t1.c AND t1.b=t2.e"
  3 "SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a>? AND t2.d>t1.c AND t1.b=t2.e"
} {
  do_test 2.$tn {
    db eval "EXPLAIN QUERY PLAN $sql"
   } {/.*SCAN TABLE t2 .*SEARCH TABLE t1 .*/}
}

do_execsql_test 3.0 {
  DROP TABLE t1;
  DROP TABLE t2;
  CREATE TABLE t1(a, b, c);
  CREATE TABLE t2(d, e, f);
................................................................................
     WHERE t2.d=t1.b AND t1.a=(t2.d+1) AND t1.b = (t2.e+1)}

  3 {SELECT t1.a, t1.b, t2.d, t2.e FROM t2 CROSS JOIN t1 
     WHERE t2.d=t1.b AND t1.a=(t2.d+1) AND t1.b = (t2.e+1)}
} {
  do_test 3.$tn {
    db eval "EXPLAIN QUERY PLAN $sql"
   } {/.*SCAN TABLE t2 .*SEARCH TABLE t1 .*/}
}

finish_test








|







 







|







 







|







 







|




42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
..
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
..
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
...
105
106
107
108
109
110
111
112
113
114
115

#
# In order to make them more predictable, automatic indexes are turned off for
# the tests in this file.
#

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

do_execsql_test 1.0 {
  PRAGMA automatic_index = 0;
  CREATE TABLE t1(a, b, c);
  CREATE TABLE t2(d, e, f);
  CREATE UNIQUE INDEX i1 ON t1(a);
  CREATE UNIQUE INDEX i2 ON t2(d);
................................................................................
foreach {tn sql} {
  1 "SELECT * FROM t1,           t2 WHERE t1.a=t2.e AND t2.d<t1.b AND t1.c!=10"
  2 "SELECT * FROM t2,           t1 WHERE t1.a=t2.e AND t2.d<t1.b AND t1.c!=10"
  3 "SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a=t2.e AND t2.d<t1.b AND t1.c!=10"
} {
  do_test 1.$tn {
    db eval "EXPLAIN QUERY PLAN $sql"
   } {/.*SCAN TABLE t2\y.*SEARCH TABLE t1\y.*/}
}

do_execsql_test 2.0 {
  DROP TABLE t1;
  DROP TABLE t2;
  CREATE TABLE t1(a, b, c);
  CREATE TABLE t2(d, e, f);
................................................................................
foreach {tn sql} {
  1 "SELECT * FROM t1,           t2 WHERE t1.a>? AND t2.d>t1.c AND t1.b=t2.e"
  2 "SELECT * FROM t2,           t1 WHERE t1.a>? AND t2.d>t1.c AND t1.b=t2.e"
  3 "SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a>? AND t2.d>t1.c AND t1.b=t2.e"
} {
  do_test 2.$tn {
    db eval "EXPLAIN QUERY PLAN $sql"
   } {/.*SCAN TABLE t2\y.*SEARCH TABLE t1\y.*/}
}

do_execsql_test 3.0 {
  DROP TABLE t1;
  DROP TABLE t2;
  CREATE TABLE t1(a, b, c);
  CREATE TABLE t2(d, e, f);
................................................................................
     WHERE t2.d=t1.b AND t1.a=(t2.d+1) AND t1.b = (t2.e+1)}

  3 {SELECT t1.a, t1.b, t2.d, t2.e FROM t2 CROSS JOIN t1 
     WHERE t2.d=t1.b AND t1.a=(t2.d+1) AND t1.b = (t2.e+1)}
} {
  do_test 3.$tn {
    db eval "EXPLAIN QUERY PLAN $sql"
   } {/.*SCAN TABLE t2\y.*SEARCH TABLE t1\y.*/}
}

finish_test

This page was generated in about 0.029s by Fossil 2.10 [4c73b4afb9] 2019-07-22 19:06:28